Page 1 of 1
SQL Count function not supported?
Posted: Mon Jan 14, 2013 4:05 pm
by 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.
Re: SQL Count function not supported?
Posted: Mon Jan 14, 2013 4:12 pm
by 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)
Re: SQL Count function not supported?
Posted: Mon Jan 14, 2013 7:20 pm
by falsam
tutiplain wrote: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
Re: SQL Count function not supported?
Posted: Mon Jan 14, 2013 9:04 pm
by jassing
DatabaseQuery() doesn't return the count of results A query can be successful, but have zero results.
You have to use NextDatabaseRow()
Re: SQL Count function not supported?
Posted: Tue Jan 15, 2013 5:23 pm
by 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
Re: SQL Count function not supported?
Posted: Tue Jan 15, 2013 5:26 pm
by Fred
It's better to use NextDatabaseRow() as FirstDatabaseRow() isn't granted to work with every drivers.
Re: SQL Count function not supported?
Posted: Tue Jan 15, 2013 11:29 pm
by flaith
Good to know, thanks Fred
Re: SQL Count function not supported?
Posted: Wed Jan 16, 2013 1:35 am
by tutiplain
Thanks for your replies. Using NextDatabaseRow() solved the problem.