DatabaseQuery()

Syntax

Result = DatabaseQuery(#Database, Request$ [, Flags])

Executes a SQL query on the given database. Only queries which doesn't change the database records are accepted ('SELECT' like queries). To performs database modification, use DatabaseUpdate().

Parameters

#Database	The database to use.
Request$	The SQL query to execute.
Flags (optional)	The flags to use. It can be one of the following value: #PB_Database_StaticCursor : performs the query to access the result in a sequential manner. It's not possible to rewind with PreviousDatabaseRow() or FirstDatabaseRow() on some drivers, but it is the faster way to get the data (default). #PB_Database_DynamicCursor: performs the query to access the result in a random manner using PreviousDatabaseRow() or FirstDatabaseRow(). It can be slower, or even unsupported on some drivers.

Return value

Returns nonzero if the query was successful or zero if it failed (due to a SQL error or a badly-formatted query).

Remarks

If the query has succeeded then NextDatabaseRow() can be used to list returned records (see the example below). In the event of an error, the error text can be retrieved with DatabaseError(). It is safe to use NextDatabaseRow() even if the request doesn't return any records. To get the number of columns returned by the query, use DatabaseColumns().

Once the query results aren't needed anymore, FinishDatabaseQuery() has to be called to release all the query resources.

The query can contain place holders for bind variables. Such variables must be set before calling the function using SetDatabaseString(), SetDatabaseLong() etc. After executing the query, the bound variables are cleared and have to be set again for future calls. The syntax for specifying bind variables in SQL is dependent on the database. The example below demonstrate the syntax.

Example

  ; First, connect to a database with an employee table
  ;
  If DatabaseQuery(#Database, "SELECT * FROM employee") ; Get all the records in the 'employee' table
  
    While NextDatabaseRow(#Database) ; Loop for each records
      Debug GetDatabaseString(#Database, 0) ; Display the content of the first field      
    Wend
  
    FinishDatabaseQuery(#Database)
  EndIf

Example: Bind variables with SQLite and ODBC

  ; SQLite and ODBC shares the same syntax for bind variables. It is indicated by the '?' character
  ;
  SetDatabaseString(#Database, 0, "test")  
  If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=?")    
    ; ...
  EndIf

Example: PostgreSQL

  ; PostgreSQL uses another syntax: $1, $2.. into the statement to indicate the undefined parameter
  ;
  SetDatabaseString(#Database, 0, "test")  
  If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=$1")    
    ; ...
  EndIf

See Also

DatabaseUpdate(), NextDatabaseRow() SetDatabaseString(), SetDatabaseLong(), SetDatabaseQuad(), SetDatabaseFloat(), SetDatabaseDouble() SetDatabaseBlob(), SetDatabaseNull()

Supported OS

All