SQL Count function not supported?

[tutiplain]

Hi all,

Can PB do a "select count(field) from table" type of query? I am trying it but it always reports 0. Here's the code:

Code: Select all

    r = DatabaseQuery(db,"select count (id)  from estudiantes;")
    If r =0
      MessageRequester("Error","Error en query: " + DatabaseError())
    EndIf
    cant_estud.i = GetDatabaseFloat(db,0)
    MessageRequester("",Str(cant_estud))
    Dim keys.i(cant_estud)

After execution of this code r =1 and cant_estud =0, and I don't understand why. The table has three records in it, so cant_estud should equal 3, not 0. Any help greatly appreciated.

[ts-soft]

not tested, but i hope it works

Code: Select all

     r = DatabaseQuery(db,"select count (id)  from estudiantes;")
    If r =0
      MessageRequester("Error","Error en query: " + DatabaseError())
    EndIf
    NextDatabaseRow(db); <--
    cant_estud.i = GetDatabaseLong(db,0); <--
    MessageRequester("",Str(cant_estud))
    Dim keys.i(cant_estud)

[falsam]

Can PB do a "select count(field) from table" type of query? I am trying it but it always reports 0.

Yes. An example with a database in memory.

Code: Select all

UseSQLiteDatabase()

If OpenDatabase(0, ":memory:", "", "")
    Debug "Connected"
    
    ;Create database 
    DatabaseUpdate(0, "CREATE TABLE estudiantes (idauto INTEGER PRIMARY KEY, score INTEGER);")       
    Debug DatabaseError()
    
    ;inserting 100 records with a score by student to illustrate 
    ;"Select count" And why not "select Sum"
    For i = 1 To 100
      DatabaseUpdate(0, "insert into estudiantes (score) values('" + Str(Random(20))+ "')")
    Next
    
    ;Control record
    DatabaseQuery(0, "SELECT * FROM estudiantes")
    While NextDatabaseRow(0)
        Debug "IdAuto : "+ GetDatabaseString(0, 0) + " Score : "+ GetDatabaseString(0, 1)
    Wend
    
    ;Select Count
    If DatabaseQuery(0, "SELECT COUNT(idauto) FROM estudiantes") <> 0
      NextDatabaseRow(0)
      Debug "Number of records 'Select Count' : " + GetDatabaseString(0, 0)
    EndIf
    
    
    ;Select Sum
    If DatabaseQuery(0, "SELECT SUM(score) FROM estudiantes") <> 0
      NextDatabaseRow(0)
      Debug "Total Score 'Select Sum ' : " + GetDatabaseString(0, 0)
    EndIf
    
  Else
    Debug DatabaseError()   
EndIf

[jassing]

DatabaseQuery() doesn't return the count of results A query can be successful, but have zero results.
You have to use NextDatabaseRow()

[flaith]

This one works too :

Code: Select all

    ;Select Count
    If DatabaseQuery(0, "Select Count(*) From estudiantes") <> 0
      FirstDatabaseRow(0)
      Debug "Number of records 'Select Count' : " + GetDatabaseString(0, 0)
    EndIf

[Fred]

It's better to use NextDatabaseRow() as FirstDatabaseRow() isn't granted to work with every drivers.

[flaith]

Good to know, thanks Fred

[tutiplain]

Thanks for your replies. Using NextDatabaseRow() solved the problem.