Page 1 of 1
DatabaseTableRowCount
Posted: Sun Oct 21, 2012 4:11 am
by Guimauve
Hello everyone,
In some occasion we would like to know how many row a table has. To Redim a Dynamic array before to load an entire table.
The first solution consist of running a 1st pass to count the row, Redim the Array then a 2nd pass to load to table inside the array.
This solution is not very effective especially if the table contains several thousand lines. After an extensive search I have found a
better solution here :
http://www.w3schools.com/sql/sql_func_count.asp
Here is the solution that I came up :
Code: Select all
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
; Project name : DatabaseTableRowCount
; File Name : DatabaseTableRowCount.pb
; File version: 1.0.0
; Programming : OK
; Programmed by : Guimauve
; Date : 20-10-2012
; Last Update : 20-10-2012
; PureBasic code : 5.00 B6
; Platform : Windows, Linux, MacOS X
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Procedure.l DatabaseTableRowCount(DataBaseID.i, TableName.s)
If DatabaseQuery(DatabaseID, "SELECT COUNT(*) FROM " + TableName)
NextDatabaseRow(DatabaseID)
Protected TableRowCount.l = GetDatabaseLong(DataBaseID, 0)
FinishDatabaseQuery(DataBaseID)
EndIf
ProcedureReturn TableRowCount
EndProcedure
; <<<<<<<<<<<<<<<<<<<<<<<
; <<<<< END OF FILE <<<<<
; <<<<<<<<<<<<<<<<<<<<<<<
If it can be useful for someone else.
Best regards
Guimauve
DatabaseTableRowCount
Posted: Sun Oct 21, 2012 8:07 am
by Wood51
Hi guimauve !
Very useful , thank a lot
Re: DatabaseTableRowCount
Posted: Mon Oct 22, 2012 11:37 pm
by Guimauve
Hello everyone,
Two more commands can be useful when working with database. For convenience I have included the "DatabaseTableRowCount()" in this file too.
Commands like this can be nice if they are being added to the standard database commands sets.
If some of you have some small but useful command like this feel free to add them here.
Edit #1 : Correction suggested by Graves.
Edit #2 : Optional "Where" parameter for DatabaseTableRowCount() suggested by Polo added
Best regards
Guimauve
Code: Select all
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
; Project name : Database Helper Commands
; File Name : Database Helper Commands.pb
; File version: 1.0.1
; Programming : OK
; Programmed by : Guimauve
; Date : 20-10-2012
; Last Update : 23-10-2012
; PureBasic code : V5.00 B6
; Platform : Windows, Linux, MacOS X
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
; We need to redim an array to the correct size
; before to load a Table inside of it.
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Procedure.l DatabaseTableRowCount(DataBaseID.i, TableName.s, Where.s = "")
If DatabaseQuery(DatabaseID, "SELECT COUNT(*) FROM " + TableName + " " + Where)
NextDatabaseRow(DatabaseID)
Protected TableRowCount.l = GetDatabaseLong(DataBaseID, 0)
FinishDatabaseQuery(DataBaseID)
EndIf
ProcedureReturn TableRowCount
EndProcedure
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
; What if we only want to delete the data inside
; the table, and not the table itself ?
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Procedure.b TruncateDatabaseTable(DataBaseID.l, TableName.s)
; Some implementation of SQL support Truncate instruction
; If it's not the case fall back on the old way to do the
; job. If the old way also fail, just give up !
If DatabaseUpdate(DatabaseID, "TRUNCATE TABLE " + TableName)
Protected Success.b = #True
Else
If DatabaseUpdate(DatabaseID, "DELETE FROM " + TableName + " WHERE 1=1")
Success = #True
Else
Success = #False
EndIf
EndIf
ProcedureReturn Success
EndProcedure
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
; After many Insert and Delete inside the database
; the Data for a single table or index is scattered
; around the database file ?
;
; If so, running VACUUM ensures that each table and
; index is largely stored contiguously within the
; database file
;
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Procedure.b VacuumizeDatabase(DatabaseID.l)
If DatabaseUpdate(DatabaseID, "VACUUM")
Protected Success.b = #True
EndIf
ProcedureReturn Success
EndProcedure
; <<<<<<<<<<<<<<<<<<<<<<<
; <<<<< END OF FILE <<<<<
; <<<<<<<<<<<<<<<<<<<<<<<
Re: DatabaseTableRowCount
Posted: Tue Oct 23, 2012 11:56 am
by graves
Hi,
Some database implementations verify the DELETE statement with no WHERE clause, and ask for confirmation.
To avoid this, I always use:
Code: Select all
If DatabaseUpdate(DatabaseID, "DELETE FROM " + TableName + " WHERE 1=1")
Re: DatabaseTableRowCount
Posted: Tue Oct 23, 2012 3:32 pm
by Polo
You should add to your count function an optional "where" parameter