13.3. SQL API reference

13.3.1. SQLList()

SQLList() --> aAvailableDrivers

13.3.2. ConnectNew()

ConnectNew(<cRDBMS>,[<RDBMS specific>,...],[<cCharset>],[<cIsolation>])
	--> TConnect object

13.3.2.1. Parameters

<cRDBMS>

RDBMS identifier; see desired RDBMS information

<RDBMS specific>

a number of RDBMS specific parameters; see desired RDBMS information

<cCharset>

(9th parameter) backend charset

<cIsolation>

(10th parameter) default transaction isolation level; see desired RDBMS information

13.3.3. TConnect class

TConnect class constructor and member functions are listed below:

13.3.3.1. TConnect:Command()

Command(<cSQL>,[<aParameters>]) --> nAffectedRows

13.3.3.1.3. Description

Command() is used to execute non-SELECT statements. 'Non-SELECT' means any SQL statement that does not return rows. To execute a statement that returns rows use TConnect:CreateRowset() even if you are not interested in the results.

SQL statement may have parameters. Parameter names in <cSQL> must be preceded with ':' character. Parameter values are passed in two dimensional array <aParameters>.

If there is no active transaction, changes made by executing <cSQL> are committed implicitly. If you need another behavior you have to start transaction explicitly by invoking TConnect:Start().

Return value is a number of affected rows, for example number of deleted rows in the case of DELETE statement.

13.3.3.4. TConnect:CreateRowset()

CreateRowset(;
	<cSelectSQL>,;
	[<aParameters>],;
	[<cInsertSQL>],;
	[<cDeleteSQL>],;
	[<cUpdateSQL>],;
	[<cRefreshSQL>],;
	[<cIdName>],;
	[<aOrders>],;
	[<cGenIdSQL>],;
	[<lNoFetch>],;
	[<bEval>],;
	[<nEvery>];
) --> TRowset object

13.3.3.4.1. Parameters

<cSelectSQL>

a string containing SQL query to be executed

<aParameters>

an array contaning SQL parameters as a series of subarrays, one per parameter. Each subarray must contain at least two elements. The first is parameter name, the second is the parameter value. Third element is used only with Oracle (and is required), and contains a numeric data type identifier. Optional fourth element is used to indicate a binary value; if passed .T. no charset transformations take place. Parameters and their values passed in <aParameters> can be used in any of SQL statements described below

<cInsertSQL>

SQL statement to be executed when a new row is added to the set

<cDeleteSQL>

SQL statement to be executed when a row is deleted from the set

<cUpdateSQL>

SQL statement to be executed when a row is changed

<cRefreshSQL>

SQL query to be executed to refresh the current row in the set

<cIdName>

name of the 'row ID' field (only for Interbase, ODBC and DBTCP)

<aOrders>

an array with definitions of the local orders to be created as a series of subarrays, one per order. First element of subarray defines the name of the order, second - a field name or code block to be evaluated to get the key value. Third element is required only for character keys and defines the length of the key.

<cGenIdSQL>

SQL statement to be executed for getting a new unique row ID value, which will be used with <cInsertSQL> (only for Interbase)

<lNoFetch>

logical value defining the fetching mode. If passed .F. all rows are fetched immediately, if .T. - rows are fetched later on demand. Default is .F.

<bEval>

a code block to be evaluated during fetching process. TRowset object is passed to the code block as a parameter. If <bEval> returns .F. fetching process aborts. It is ignored in 'fetch on demand' mode (when <lNoFetch> is .T.)

<nEvery>

an interval of rows when <bEval> should be evaluated (if passed). Default is 1, i.e. <bEval> is evaluated after each fetched row

13.3.3.4.3. Description

CreateRowset() executes SELECT query, builds a set of resulting rows and returns a TRowset object. Thus it may be considered as a TRowset class constructor. 'SELECT query' means any valid SQL statement returning rows. Usage of any other kind of SQL statement in <cSelectSQL> will result in an error.

<cInsertSQL>, <cDeleteSQL>, <cUpdateSQL> are optional parameters that make easier modification of data in the database. If not passed, corresponding changes in the set don't reflect in the tables of database. <cInsertSQL> is executed automatically by TRowset:Append() method. In the same way <cDeleteSQL> is executed by TRowset:Delete() method, and <cUpdateSQL> is executed by TRowset:Write() method.

<cRefreshSQL> is automatically executed by TRowset:RefreshCurrent() method. It must have the same list of fields as <cSelectSQL>.

The row set intended to modifying must contain an unique field (row ID), which is used to bind rows in the database tables and rows in the set. You should explicitly include that field to the list of requesting fields in the SELECT query. There is various approach to that issue in various RDBMS's.

  • Some RDBMS's provide a 'hidden' field for that purpose (ROWID in Oracle, OID in PostgreSQL). Such field is created for any table by default, even if you did not describe it in the CREATE TABLE statement. PostgreSQL understands such syntax:

    SELECT oid,* FROM mytable

    In the contrast, Oracle does not allow such syntax. You should enumerate all fields by the name:

    SELECT rowid,fname,lname FROM mytable
  • Some RDBMS's provide autoincremental fields for that purpose. MySQL's CREATE TABLE syntax allows to create table with such field by using AUTO_INCREMENT clause in the field's definition. CLIP discerns such field and uses it as row ID. You don't need to do anything more to indicate a field as row ID except for definition a field with AUTO_INCREMENT clause in CREATE TABLE statement.

  • Some RDBMS's (Interbase) provide triggers and unique number generators. A typical approach in such case is to define a trigger BEFORE INSERT, which gets an unique ID from generator and assigns it to the 'row ID' field. But there is no way to determine 'row ID' value of newly inserted row. I.e., you can't UPDATE or DELETE just INSERTed row (a row INSERTed after TRowset object was created). You have two choices:

    1. design your application considering this limitation (never UPDATE a newly INSERTed rows);

    2. don't use trigger BEFORE INSERT, but provide <cGenIdSQL> parameter containing SQL query to the generator. That statement must return an unique ID from generator which then will be assigned to the field with name <cIdName> during executing <cInsertSQL> statement.

    Note

    Interbase's RDB$DB_KEY attribute intended to be a 'row ID' is not actually. It is rather a 'row address' which can be changed (like RECNO() which can be changed after PACK). Therefore it's not used as 'row ID'.

  • In the case of RDBMS's not providing any of features described above (ODBC, DBTCP), you should pass <cIdName> parameter with the name of 'row ID' field and should design your application so that a newly INSERTed row never is changed or deleted.

SQL statements <cInsertSQL>, <cDeleteSQL>, <cUpdateSQL> and <cRefreshSQL> get values of current row fields through parameters with the same name as row set's field name. For example:

<cSelectSQL>  - SELECT DriverID AS id,fname,lname FROM mytable
<cInsertSQL>  - INSERT INTO mytable (fname,lname) VALUES (:fname,:lname)
<cUpdateSQL>  - UPDATE mytable SET fname=:fname,lname=:lname WHERE DriverId=:id
<cDeleteSQL>  - DELETE FROM mytable WHERE DriverId=:id
<cRefreshSQL> - SELECT DriverID,fname,lname FROM mytable WHERE DriverId=:id

There are three macros: %FIELDS, %VALUES for use in <cInsertSQL>; %LIST for use in <cUpdateSQL>. %FIELDS is expanded as list of field names; %VALUES - as parameter names; %LIST - as list of fields and corresponding parameters. For example:

INSERT INTO mytable (%FIELDS) VALUES (%VALUES)
UPDATE mytable SET %LIST

There are two modes of fetching rows controlled by <lNoFetch> parameter: full fetching and fetching on demand.

  • In the first mode (<lNoFetch>==.F.) all selected rows have been fetched before CreateRowset() terminates. You can immediately use TRowset:Lastrec() to determine the number of selected rows in this mode. You can use <bEval> code block to observe and to manage fetching process.

  • In the second mode (<lNoFetch>==.T.) rows are fetched later on demand (during the navigation through the set). This mode is more faster but Lastrec() returns actual number of selected rows only after all rows have been fetched. <bEval> parameter is ignored in this mode.

When an application completes using the row set, it should release the set by calling TRowset:Destroy().

13.3.4. TRowset class

TRowset class constructor and member functions are listed below:

TConnect:CreateRowset()TRowset constructor
  
TRowset:Append()append a new row to the row set
TRowset:Bof()determine when beginning of set is encountered
TRowset:Browse()browse rows within a window
TRowset:CreateOrder()create new local order
TRowset:Delete()delete a row from the row set
TRowset:Destroy()destroy TRowset object
TRowset:Eof()determine when end of set is encountered
TRowset:FetchAll()fetch all unfetched rows
TRowset:Fetched()determine the number of fetched rows
TRowset:FieldBinary()determine if a given field is binary
TRowset:FieldBlock()return a set/get code block for a given field
TRowset:FieldDec()determine number of decimal digits in a given field
TRowset:FieldLen()determine length of a given field
TRowset:FieldName()return the name of a field with a given position
TRowset:FieldNo()return the position of a field with a given name
TRowset:FieldNullable()determine if a given field is nullable
TRowset:FieldType()return XBase type of the field
TRowset:FieldTypeSQL()return RDBMS type of the field
TRowset:FieldUnsigned()determine if a given field is unsigned
TRowset:GetValue()retrieve the value of a field in the current row
TRowset:GoBottom()move to the last logical row
TRowset:Goto()move to the specified row
TRowset:GoTop()move to the first logical row
TRowset:KeyNo()determine logical position of the current row
TRowset:Lastrec()determine the number of rows in the set
TRowset:NFields()return the number of fields in the set
TRowset:Read()read the current row
TRowset:Recno()return the position of the current row
TRowset:RefreshAll()refresh the set by repeated executing the SELECT query
TRowset:RefreshCurrent()refresh the current row by executing <cRefreshSQL>
TRowset:Seek()move to the row having the specified key value
TRowset:SetOrder()set the controlling order
TRowset:SetValue()set the value of a field in the current row
TRowset:Skip()move relative to the current row
TRowset:Write()write the current row

13.3.4.5. TRowset:Delete()

Delete() --> NIL

13.3.4.5.1. Description

Delete() deletes the current row from the set.

If row set was created with <cDeleteSQL> parameter, the latter is executed on the SQL server with the value of 'row id' field of the current record.

If the row set was created in 'fetch on demand' mode (<lNoFetch> parameter of TConnect:CreateRowset() is .T.), all unfetched rows are fetched before processing.

Delete() decreases rows count returning by TRowset:Lastrec().

If there is no controlling order the current row position remains the same, or moves to the last row (when deleted a row with Recno()==Lastrec()).

If there is controlling order (set using TRowset:SetOrder()) the current row position moves to the row having the next key, or moves to the last logical row (when a row having biggest key value is deleted).

If the last remained row is deleted, both Bof() and Eof() status are switched to true (.T.) and TRowset:Recno() returns zero (0).