DatabaseTableRowCount

[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

[Wood51]

Hi guimauve !
Very useful , thank a lot

[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 <<<<<
; <<<<<<<<<<<<<<<<<<<<<<<

[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")

[Polo]

You should add to your count function an optional "where" parameter