PureBasic Forums - English

Posted: **Mon Aug 11, 2008 7:41 am**

Get the amount of rows after an "select" statement. To have the possibility
to work with arrays..

Posted: **Mon May 04, 2009 12:39 am**

+1

At the moment I'm using this code, but I think DatabaseRows() would be very nice.

If DatabaseQuery(#DB, "SELECT COUNT(*) FROM tblName")
  If NextDatabaseRow(#DB)
    ReDim MyArray.MyType(GetDatabaseLong(#DB, 0))
  EndIf
  FinishDatabaseQuery(#DB)
EndIf

Posted: **Mon May 04, 2009 7:48 am**

dige wrote:Get the amount of rows after an "select" statement.

please only as an optional parameter because I think that can reduce the performance of DatabaseQuery().

Code: Select all

Result = DatabaseQuery(Database, Query, ReturnRowsAffected) ; ReturnRowsAffected = #True or #False (default: #False)

my two cents ... Kiffi

Posted: **Mon May 04, 2009 10:02 am**

there are 2 possibilities to get values from a odbc-connection .. first is a snapshot (that contents the whole Selection including the rowcount) and a
set-oriented result,(you have to step through the resultrows from the beginning) (i thing, pb preferr the second one)

but nobody disallow a construction like this (ok .. not too fast with huge resultsets)

Code: Select all

UseODBCDatabase()
OpenDatabase(0,"database","user","pw")
query.s="select * from something where thing='anything'bezirk_nummer=998"
DatabaseQuery(0,query)
Debug "columncount "+Str(DatabaseColumns(0))
While NextDatabaseRow(0) ;step forewards
r+1
Wend
Debug "rowcount"+Str(r) ; that's the rowcount
While PreviousDatabaseRow(0) ;step backwards  alternative :using firstdatabaserow sets the cursor to the first resultline
Wend
CloseDatabase(0)
End

Posted: **Sat Oct 24, 2015 7:17 am**

Could you not use something like this

Code: Select all

 
 ;Find out how many records will be returned
  GetTotal.s = "Select Count(*) FROM MyRecords " + Criteria.s ;Replace with your query
  result = DatabaseQuery(ExampleDB, GetTotal.s)
  FirstDatabaseRow(ExampleDB)
  TotalRows.i = Val(GetDatabaseString(ExampleDB,0)) ;Store this in the TotalRows variable
  FinishDatabaseQuery(ExampleDB) ;free the query

Once this is run with your query TotalRows holds the number of records your actual query will return.

Replace the Select Count(*) with your select clause and rerun the query to get your data.

The lack of previous databaserow in sqlite is a pain but for small recordsets I use the following example

Code: Select all

 
 ;No previous database row in sqlite So count forward from first Databaserow
 ;CurrentRow is the required row number
  i=1
  FirstDatabaseRow(ExampleDB)
  If CurrentRow.i = 1 ;Displaying first returned row
    SetGadgetText(str_Record,GetDatabaseString(ExampleDB,0)) ;Display or use data
  Else
    ;Look for the required display row
    While NextDatabaseRow(ExampleDB)
      ;No other processing here to keep it as quick as possible
      i=i+1
      If i = CurrentRow.i
        SetGadgetText(str_Record,GetDatabaseString(ExampleDB,0)) ;Display or use data
        Break ;Reached the row wanted so quit While...Wend
      EndIf

    Wend

  EndIf

For larger recordsets I can only see two choices.

1. Read the whole recordset into an array setting the first dimension of that array to an incrementing number which can then be used as the rowid of the record.

This could be very slow with large returned recordsets.

2. Run the query so that only a unique identifier is returned and read this into an array

Faster because less data is returned but each time a new row needs to be displayed another query needs to run on the database with this identifier. I have this for returned records from a single table but get some strange results where a query uses joins to select data from multiple tables.

If anyone has PB code to do this please post here.

Posted: **Sat Oct 24, 2015 7:58 am**

collectordave wrote:The lack of previous databaserow in sqlite is a pain...

Assuming you already know the current row number, the previous or next record could be retrieved quite simply, with this query:

Code: Select all

queryString.s = "SELECT * FROM tableName LIMIT 1 OFFSET " + Str(requiredRow)

Posted: **Sun Nov 08, 2015 8:46 pm**

Very good but an example would benefit all?

Posted: **Sat Nov 14, 2015 8:37 am**

Ok thanks to all.

Programmed example. gets number of rows and gets past the lack of previous database row.

You can see it here:-

http://www.codeinbasic.com/index.php?topic=239.0

Posted: **Sun Apr 17, 2022 10:01 pm**

+1
Those While-Wend loops do the job but I think it is better having just one command, DatabaseRows() and nothing more. Why not making life easier?

Posted: **Tue Jun 07, 2022 12:38 am**

Try this

Code: Select all

; Use database from help 
Query$ = "select * from food where weight > 7 "

; Get count of rows
Rowcount.i = 0
If DatabaseQuery(0, "select count(*) as rowscount from (" + Query$ + ") as myRowsCount")
    NextDatabaseRow(0)
    Rowscount = GetDatabaseLong(0, 0)
    FinishDatabaseQuery(0)
EndIf
Debug Rowcount

; Get data
If DatabaseQuery (0, Query$)
    For i = 1 to Rowcount
        NextDatabaseRow (0)
        Debug GetDatabaseString(0, 0)
    Next
    FinishDatabaseQuery (0)
EndIf

Work with all databases

Posted: **Tue Jun 07, 2022 11:46 pm**

Here is a procedure I use:

Code: Select all

;{ Return number of records found in a table
Procedure.i dbCountRows(dbn.i, table$, query$="", field="*")
	Protected result.i, s$
	If IsDatabase(dbn)
		s$="select count ("+field$+") from "+table$
		If query$
			s$+" where "+query$
		EndIf
		If DatabaseQuery(dbn, s$)
			If NextDatabaseRow(dbn)
				result=GetDatabaseLong(dbn, 0)
			EndIf
			FinishDatabaseQuery(dbn)
		EndIf
	EndIf
	ProcedureReturn result
EndProcedure
;}

Example pseudocode:
If OpenDatabase(0, "Data.db", "", "")
rows.i = dbCountRows(0, "Items") ;returns all the rows - e.g. records - in the table "items" in open database 0.
CloseDatabase(0)
EndIf
Debug rows

PureBasic Forums - English

DatabaseRows()

DatabaseRows()

Re: DatabaseRows()

Re: DatabaseRows()

Re: DatabaseRows()

Re: DatabaseRows()

Re: DatabaseRows()

Re: DatabaseRows()

Re: DatabaseRows()

Re: DatabaseRows()