DatabaseRows()

[dige]

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

[klaver]

+1

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

Code: Select all

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

[Kiffi]

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

[bobobo]

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

[collectordave]

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.

[TI-994A]

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)

[collectordave]

Very good but an example would benefit all?

[collectordave]

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

[doctorized]

+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?

[Vadym Hromov]

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

[DeanH]

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