SQLList() --> aAvailableDrivers
An array of drivers as series of subarrays, one per available driver. The first element of subarray contains short ID of driver; the second - the name of RDBMS accessed by driver; the third - driver description.
SQLList() is used to obtain the list of available SQL drivers. The driver is available when it's library is linked with an application. If there are no linked SQL drivers an empty array is returned.
The first element of subarray representing a driver contains RDBMS's ID (short CHARACTER value associated with driver), which is used as <cRDBMS> parameter of TConnect constructor ConnectNew().
$cat test.prg // test.prg procedure Main() ? SQLList()[1] ? SQLList()[2] return NIL $clip -e test.prg -lclip-mysql -lclip-postgres $./test {MS, MySQL, Generic MySQL for CLIP driver, v.1.0}, {PG, PostgreSQL, Generic PostgreSQL for CLIP driver v.1.0}
ConnectNew(<cRDBMS>,[<RDBMS specific>,...],[<cCharset>],[<cIsolation>]) --> TConnect object
TConnect object.
ConnectNew() connects to a SQL server, constructs and returns TConnect object. That object can be used to start/stop transactions, to execute SQL statements, and to obtain a set of rows by SELECT statement.
Optional parameter <cCharset> is used to indicate that server uses different from client charset. All string transformations are made then automatically. If not passed, SET("SQL_CHARSET") is used. Note, that changing SET("SQL_CHARSET") after connection to the server has no effect for this connection.
If optional parameter <cIsolation> is not passed, an appropriate SET variable is used, for example SET("OR_ISOLATION_LEVEL") for Oracle. If there is no such SET variable, the SET("SQL_ISOLATION_LEVEL") is used.
Default isolation level can be overrided by TConnect:Start() parameters. Changing value of an appropriate SET variable after connect wouldn't help. |
When an application completes access to the SQL server it should disconnect from the server and release system resources by calling TConnect:Destroy().
In this example the connection to the local PostgreSQL server is performed.
conn := ConnectNew("PG",,,,,"template1") ... conn:Destroy()
TConnect class constructor and member functions are listed below:
ConnectNew() | TConnect constructor |
TConnect:Command() | execute SQL statement |
TConnect:Commit() | commit transaction |
TConnect:Destroy() | disconnect from the server and destroy TConnect object |
TConnect:CreateRowset() | TRowset constructor |
TConnect:Rollback() | rollback transaction |
TConnect:Start() | start transaction |
Command(<cSQL>,[<aParameters>]) --> nAffectedRows
a string containing SQL statement to be executed
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
A number of affected rows.
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.
conn:Command("CREATE TABLE mytable (fname char(20),lname char(20))") conn:Command("INSERT INTO mytable VALUES (:firstname,:lastname)",; {{"firstname","John"},{"lastname","Smith"}}) ? conn:Command("DELETE FROM mytable WHERE fname=:fname",; {{"fname","John"}}) // 1
Commit() --> NIL
Commit() commit all changes made after invoking TConnect:Start() and ends transaction. If there is no active transaction an error is generated.
conn:Start() // ... // some changes // ... conn:Commit()
Destroy() --> NIL
When an application completes access to the SQL server it should disconnect from the server and release system resources by calling Destroy().
conn := ConnectNew("PG",,,,,"template1") // ... // do some work // ... conn:Destroy()
CreateRowset(; <cSelectSQL>,; [<aParameters>],; [<cInsertSQL>],; [<cDeleteSQL>],; [<cUpdateSQL>],; [<cRefreshSQL>],; [<cIdName>],; [<aOrders>],; [<cGenIdSQL>],; [<lNoFetch>],; [<bEval>],; [<nEvery>]; ) --> TRowset object
a string containing SQL query to be executed
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
SQL statement to be executed when a new row is added to the set
SQL statement to be executed when a row is deleted from the set
SQL statement to be executed when a row is changed
SQL query to be executed to refresh the current row in the set
name of the 'row ID' field (only for Interbase, ODBC and DBTCP)
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.
SQL statement to be executed for getting a new unique row ID value, which will be used with <cInsertSQL> (only for Interbase)
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.
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.)
an interval of rows when <bEval> should be evaluated (if passed). Default is 1, i.e. <bEval> is evaluated after each fetched row
TRowset object.
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:
design your application considering this limitation (never UPDATE a newly INSERTed rows);
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.
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().
Simple example which creates a row set not intended to modifying:
rs := conn:CreateRowset("SELECT * FROM mytable WHERE fname = 'John'") rs:Browse()
Creating a row set using SQL parameters in SELECT query:
rs := conn:CreateRowset("SELECT * FROM mytable WHERE fname = :par1",; {{"par1","John"}}) rs:Browse()
Creating a row set intended to modifying:
rs := conn:CreateRowset("SELECT id,fname,lname FROM mytable",NIL,; "INSERT INTO mytable (%FIELDS) VALUES (%VALUES)",; "DELETE FROM mytable WHERE id=:id",; "UPDATE mytable SET fname=:fname,lname=:lname",; "SELECT id,fname,lname FROM mytable WHERE id=:id",; "id")
Creating a row set with local orders 'id' and 'fullname':
rs := conn:CreateRowset("SELECT id,fname,lname FROM mytable",,,,,,,; {{"id","id"},; {"fullname",{|rs| rs:GetValue("fname")+rs:GetValue("lname")},40}}) rs:Browse() // show rows in natural order rs:SetOrder("id") rs:Browse() // show rows ordered by id rs:SetOrder("fullname") rs:Browse() // show rows ordered by fname and lname
Using <bEval> to observe fetching process (printing a dot every 100 rows), which can be stopped by pressing ESC:
rs := conn:CreateRowset("SELECT * FROM hugetable",,,,,,,,,,; {|| qqout("."),inkey() != K_ESC}, 100) rs:browse()
Fetching rows on demand:
rs := conn:CreateRowset("SELECT * FROM hugetable",,,,,,,,,.T.) rs:Gotop() ? rs:Fetched() // 1 ? rs:Lastrec() // 0 for i:=1 to 100 rs:Skip() ? rs:Fetched() // 2,3,...,101 next rs:FetchAll() ? rs:Lastrec() == rs:Fetched() // .T.
Rollback() --> NIL
Rollback() discards all changes made after invoking TConnect:Start() and ends transaction. If there is no active transaction an error is generated.
conn:Start() // ... // some changes // ... conn:Rollback()
Start([<cIsolation>],[<cLockTables>]) --> NIL
a string defining isolation level to be used during transaction. This parameter overrides default isolation level defined by <cIsolation> parameter of ConnectNew() function. See desired RDBMS section about what values can be passed with this parameter
this parameter is used only with Interbase and defines which tables should be locked and how
Start() starts new transaction. There must be no active transaction, otherwise an error is generated. Transaction should be sometime stopped (either must be commited or must be rolled back by invoking TConnect:Commit() or TConnect:Rollback() function).
If there is no active transaction all changes to the database are commited immediately after executing each SQL statement.
conn:Start() // ... // some changes // ... conn:Commit() // commit changes
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 |
Append(<oRow>) --> NIL
an object with fields values
Append() appends a new row to the row set and assigns <oRow> attributes values to the approprate fields of newly inserted row.
If row set was created with <cInsertSQL> parameter, the latter is executed on the SQL server with newly inserted values.
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.
Append() increases rows count returning by TRowset:Lastrec().
Current row position moves to the new row.
rs := conn:CreateRowset("SELECT id,fname,lname FROM mytable",,; "INSERT INTO mytable (fname,lname) VALUES (:fname,:lname)") obj := map() // create an empty object obj:fname := "John" // set attributes with the same names as fields obj:lname := "Smith" // ... // add new row and execute // INSERT INTO mytable (fname,lname) VALUES ('John','Smith') rs:Append(obj)
Bof() --> lBoundary
True (.T.) after an attempt to skip backward beyond the first logical row in the set; otherwise it returns false (.F.). If the set contains no rows it returns true (.T.).
Bof() is used to test for a boundary condition when you are moving the row pointer backward through a set using TRowset:Skip() function.
Once Bof() is set to true (.T.), it retains its value until there is another attempt to move the row pointer.
TRowset:Skip() is the only row movement function that can set Bof() to true (.T.).
rs := conn:CreateRowset("SELECT * FROM mytable") ? rs:Recno() // 1 ? rs:Bof() // .F. rs:Skip(-1) ? rs:Recno() // 1 ? rs:Bof() // .T.
Browse([<nTop>],[<nLeft>],[<nBottom>],[<nRight>],; [<asColumns>],[<asHeaders>],[<anWidths>]) --> NIL
define the window coordinates. If not specified, the default window coordinates are 1, 0 to MAXROW(), and MAXCOL().
an array of strings containing field names to use as column values
a parallel array of strings contaning headings for each column
a parallel array containing widths for each column
Browse() is a user interface function that provides a simple browser for rows in the set.
rs := conn:CreateRowset("SELECT id,fname,lname FROM mytable",,,,,,,,,.T.) rs:Browse(,,,,{"fname","lname"},{"First name","Last name"},{20,20})
CreateOrder(<cOrderName>,<cFieldName>|<bExpression>,[<nKeyLength>]) --> NIL
defines the name of an order to be created
a name of the field which values are used as keys
a code block used to evaluate keys; it receives TRowset object as parameter
a length of the key; it is required for character keys
CreateOrder() is used to create a local order. Under 'local order' is meant an index in memory controlling logical order of rows in the set. Once created, order can be set as controlling by TRowset:SetOrder() function. Besides, controlling order can be used to locate row having a given key by TRowset:Seek() function.
If rowset is created in 'fetch on demand' mode (<lNoFetch> parameter of TConnect:CreateRowset() is .T.), all rows are fetched implicitly before processing.
This example creates two orders ('birthdate' and 'fullname'); shows rows ordered by birth date and full name, and seeks for a person whose name begins with 'Joh':
rs := conn:CreateRowset("SELECT bdate,fname,lname FROM employee") rs:CreateOrder("birthdate","bdate") rs:CreateOrder("fullname",{|rs| rs:GetValue("fname")+rs:GetValue("lname")},40) rs:SetOrder("birthdate") rs:Browse() rs:SetOrder("fullname") rs:Browse() ? rs:Seek("Joh") // .T. row := rs:Read() ? row:fname // John
Delete() --> NIL
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).
rs := conn:CreateRowset("SELECT id,fname,lname FROM mytable",,,; "DELETE FROM mytable WHERE id=:id") ? rs:Lastrec() // 10 // delete a row and execute // DELETE FROM mytable WHERE id=... rs:Delete() ? rs:Lastrec() // 9
Destroy() --> NIL
When an application completes using the row set, it should release the set by calling TRowset:Destroy().
rs := conn:CreateRowset("SELECT * FROM mytable") // ... // do some work // ... rs:Destroy()
Eof() --> lBoundary
True (.T.) after an attempt to skip forward beyond the last logical row in the set; otherwise it returns false (.F.). If the set contains no rows it returns true (.T.).
Eof() is used to test for a boundary condition when you are moving the row pointer forward through a set using TRowset:Skip() function.
Once Eof() is set to true (.T.), it retains its value until there is another attempt to move the row pointer.
TRowset:Skip() is the only row movement function that can set Eof() to true (.T.).
Unlike standard XBase's command SKIP, TRowset:Skip() never moves beyond the last logical row. When TRowset:Eof() returns .T. the current row position is certainly the last logical row, not Lastrec()+1. |
rs := conn:CreateRowset("SELECT * FROM mytable") rs:GoBottom() ? rs:Recno() // 100 ? rs:Eof() // .F. rs:Skip() ? rs:Recno() // 100 ? rs:Eof() // .T. rs:Skip(-1) ? rs:Recno() // 99 ? rs:Eof() // .F.
FetchAll() --> nLastrec
The number of rows in the set.
FetchAll() fetches all unfetched rows. It is useful when row set has been created in 'fetch on demand' mode (<lNoFetch> parameter of TConnect:CreateRowset() is .T.). If there is no unfetched rows FetchAll() has no effect.
rs := conn:CreateRowset("SELECT * FROM mytable",,,,,,,,,.T.) ? rs:Lastrec() // 0 ? rs:Fetched() // 1 ? rs:FetchAll() // number of selected rows ? rs:Lastrec() // - " - ? rs:Fetched() // - " -
Fetched() --> nNumberOfFetchedRows
Number of already fetched rows.
Fetched() is used to determine how much rows are fetched already. It is useful in <bEval> code block parameter of TConnect:CreateRowset(), and when row set has been created in 'fetch on demand' mode.
Print the number of already fetched rows during fetching process:
rs := conn:CreateRowset("SELECT * FROM hugetable",,,,,,,,,,; {|rs| qout(rs:Fetched())},100)
Using Fetched() with row set created in 'fetch on demand' mode:
rs := conn:CreateRowset("SELECT * FROM hugetable",,,,,,,,,.T.) ? rs:Fetched() // 1 rs:Skip() ? rs:Fetched() // 2 rs:Skip(100) ? rs:Fetched() // 102
FieldBinary(<nFieldNo> | <cFieldName>) --> lBinary
the position of the field in the list of fields
the name of the field
True (.T.) if the given field is binary; false (.F.) otherwise.
FieldBinary() is used to determine if a character field is binary. Charset transformations don't take place with the values of binary field.
? rs:FieldBinary("fname") // .F.
FieldBlock(<nFieldNo> | <cFieldName>) --> bBlock
the position of the field in the list of fields
the name of the field
A code block that, when evaluated, sets (assigns) or gets (retrieves) the value of the given field.
FieldBlock() builds a set/get code block for a given field. When executed with an argument, the code block created by this function assigns the value of the argument to a given field. When executed without an argument, the code block retrieves the value of a given field.
Change the set using that code block does not cause immediate execution of <cUpdateSQL> (parameter passed to TConnect:CreateRowset()). Write() and all row movement functions (Gotop(), Gobottom(), Goto() and Skip()) are only functions that initiate reflection the change on the SQL server. |
cb := rs:FieldBlock("fname") ? eval(cb) // 'John' eval(cb,'Richard') ? eval(cb) // 'Richard' rs:Write(rs:Read()) // cause UPDATE-ing on the server
FieldDec(<nFieldNo> | <cFieldName>) --> nDec
the position of the field in the list of fields
the name of the field
A number of decimal digits used in a given numeric field.
FieldDec() is used to determine the number of decimal digits in a given field.
? rs:FieldDec("salary")
FieldLen(<nFieldNo> | <cFieldName>) --> nDec
the position of the field in the list of fields
the name of the field
A length of the field (in terms of used RDBMS).
FieldLen() is used to determine the length of a given field, in terms of used RDBMS. For example, the length of a field of type 'FLOAT' is 4.
? rs:FieldLen("salary")
FieldName(<nFieldNo>) --> cFieldName
the position of the field in the list of fields
The name of field.
FieldName() is used to determine the name of a field with a given position.
? rs:FieldName(1) // fname
FieldName(<cFieldName>) --> nFieldNo
the name of field
The position of the field.
FieldNo() is used to determine the position of a field with a given name.
? rs:FieldName('fname') // 1
FieldNullable(<nFieldNo> | <cFieldName>) --> lNullable
the position of the field in the list of fields
the name of the field
True (.T.) if the given field is nullable; false (.F.) otherwise (NOT NULL).
FieldNullable() is used to determine if a field is nullable, i.e. not 'NOT NULL'.
? rs:FieldNullable("fname") // .T.
FieldType(<nFieldNo> | <cFieldName>) --> cType
the position of the field in the list of fields
the name of the field
Type of a field (in terms of XBase).
FieldType() is used to determine the type of a given field. It returns a single character that designates the data type of the data field. FieldType() returns the following characters for the various data field types:
C - character string
N - numeric
D - date
T - datetime
L - logical
? rs:FieldType("salary") // 'N'
FieldTypeSQL(<nFieldNo> | <cFieldName>) --> nType
the position of the field in the list of fields
the name of the field
Numeric type of a field (in terms of used RDBMS).
FieldTypeSQL() is used to determine the type of a given field, in terms of used RDBMS. Specific RDBMS types are defined in corresponding *.ch files.
? rs:FieldTypeSQL("salary")
FieldUnsigned(<nFieldNo> | <cFieldName>) --> lUnsigned
the position of the field in the list of fields
the name of the field
True (.T.) if the given field is unsigned; false (.F.) otherwise.
FieldUnsigned() is used to determine if a numeric field is unsigned.
? rs:FieldUnsigned("salary") // .T.
GetValue(<nFieldNo> | <cFieldName>) --> xValue
the position of the field in the list of fields
the name of the field
A value of current row's field.
GetValue() is used to retrieve the value of a field in the current row.
? rs:GetValue('fname') // John
GoBottom() --> NIL
GoBottom() moves the row position to the last logical row.
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.
rs:CreateRowset("SELECT * FROM mytable",,,,,,,,,.T.) ? rs:Recno() // 1 ? rs:Lastrec() // 0 (there remain unfetched rows) rs:GoBottom() ? rs:Recno() // number of selected rows ? rs:Lastrec() // number of selected rows (no unfetched rows remain)
Goto(<nRowPosition>) --> nNewPosition
the position to move to
New position of current row.
Goto() is used to move to the specified physical row position. If controlling order is not physical (set by TRowset:SetOrder()), physical position can be different from logical position.
If <nRowPosition> is less than 1, Goto() moves to the first physical row, and Bof() state is set to true (.T.). If <nRowPosition> is larger than Lastrec(), Goto() moves to the last physical row, and Eof() state is set to true (.T.).
for i:=1 to rs:Lastrec() rs:goto(i) ? rs:Read() next
GoTop() --> NIL
GoTop() moves the row position to the first logical row.
rs:CreateRowset("SELECT fname,lname FROM mytable") ? rs:Recno() // 1 rs:CreateOrder("fname","fname",20) rs:SetOrder("fname") rs:GoTop() ? rs:Recno() // the position of first logical row
KeyNo() --> nPosition
The logical position of the current row.
KeyNo() is used to determine the logical number of the current row. If there is no controlling order (created by TRowset:CreateOrder() and set by TRowset:SetOrder()), KeyNo() returns the physical row position, i.e. it works just like TRowset:Recno().
rs := conn:CreateRowset("SELECT fname,lname FROM mytable") rs:CreateOrder("fname","fname",20) ? rs:KeyNo(), rs:Recno() // 1, 1 (no controlling order) rs:SetOrder("fname") // set controlling order by fname ? rs:KeyNo(), rs:Recno() // N, 1 rs:GoTop() ? rs:KeyNo(), rs:Recno() // 1, M
Lastrec() --> nLastrec
The number of rows in the set.
Lastrec() determines the number of rows in the set.
If the row set was created in 'fetch on demand' mode (<lNoFetch> parameter of TConnect:CreateRowset() is .T.), Lastrec() can return 0 (if there remain unfetched rows). The number of fetched rows can be determined using TRowset:Fetched() function. TRowset:FetchAll() can be used to fetch the remainder of unfetched rows. After that, Lastrec() will return actual number of rows.
rs := conn:CreateRowset("SELECT * FROM mytable",,,,,,,,,.T.) ? rs:Lastrec() // 0 rs:FetchAll() ? rs:Lastrec() // number of selected rows
NFields() --> nFields
The number of fields in the row set.
NFields() determines the number of fields in the row set.
rs := conn:CreateRowset("SELECT fname,lname FROM mytable") ? rs:NFields() // 2
Read() --> oRow
An object containing all fields of current row.
Read() retrieves the current row values into an object.
rs := conn:CreateRowset("SELECT fname,lname FROM mytable") ? rs:Read():fname // John ? rs:Read():lname // Smith
Recno() --> nPosition
The physical position of the current row.
Recno() used to determine the physical position of the current row. If there are no rows in the set, it returns 0.
rs := conn:CreateRowset("SELECT * FROM mytable") ? rs:Recno() // 1 rs:Skip() ? rs:Recno() // 2 rs:Goto(100) ? rs:Recno() // 100
RefreshAll() --> nLastrec
The number of rows in the set.
RefreshAll() refreshes the row set by repeatedly executing <cSelectSQL> passed to the TRowset constructor TConnect:CreateRowset() with SQL parameters passed in <aParameters>. The physical row position remains the same, or moves to the last physical row.
rs := conn:CreateRowset("SELECT * FROM mytable") rs:Browse() rs:RefreshAll() rs:Browse()
RefreshCurrent() --> NIL
RefreshCurrent() refreshes the current row of the set by executing <cRefreshSQL> passed to the TRowset constructor TConnect:CreateRowset(). If <cRefreshSQL> not passed, RefreshCurrent() has no effect.
rs := conn:CreateRowset("SELECT id,fname,lname FROM mytable",,,,,; "SELECT id,fname,lname FROM mytable WHERE id=:id") ? rs:Read() rs:RefreshCurrent() ? rs:Read()
Seek(<xKeyValue>,[<lSoft>]) --> lFound
a value of the key associated with the desired row
logical value that specifies whether a soft seek is to be performed. This determines how the work area is positioned if the specified key value is not found (see below)
True (.T.) if the specified key value was found; otherwise, false (.F.).
Seek() moves to the first logical row whose key value is equal to <xKeyValue>. If such row is found, it becomes the current row and Seek() returns true (.T.); otherwise, it returns false (.F.). For a normal (not soft) seek, the row set is positioned to the last logical row and TRowset:Eof() returns true (.T.); for a soft seek, the row set is positioned to the first row whose key value is greater than the specified key value. If no such row exists, the row set is positioned to the last logical row and TRowset:Eof() returns true (.T.).
For a row set with no controlling order (created by TRowset:CreateOrder() and prescribed controlling by TRowset:SetOrder()), Seek() has no effect.
rs := conn:CreateRowset("SELECT fname,lname FROM mytable") rs:CreateOrder("fname","fname",20) rs:SetOrder("fname") ? rs:Seek("John")
SetOrder([<cOrderName>]) --> cOrderName
the name of the order to be set controlling
The name of previously controlling order or NIL (physical order).
SetOrder() activates an order created by TRowset:CreateOrder() (makes it controlling). It returns the name of previously controlling order.
If passed an empty string, SetOrder() causes the row set to be accessed in physical (TRowset:Recno()) order.
rs := conn:CreateRowset("SELECT fname,lname FROM mytable") rs:CreateOrder("fname","fname",20) rs:Browse() // browse rows in physical order rs:SetOrder("fname") rs:Browse() // browse rows ordered by first name rs:SetOrder("") rs:Browse() // browse in physical order again
SetValue(<nFieldNo> | <cFieldName>, <xValue>) --> NIL
the position of the field in the list of fields
the name of the field
the value to be assigned to the field
SetValue() assignes <xValue> to the field in the current row.
Unlike TRowset:Write() function, SetValue() does not cause immediate reflection the change on the SQL server. One of the row movement functions must arise to cause reflection (executing <cUpdateSQL> statement, passed to the TRowset constructor TConnect:CreateRowset()). |
rs := conn:CreateRowset("SELECT fname,lname FROM mytable") ? rs:GetValue("fname") // John rs:SetValue("fname","Sean") ? rs:GetValue("fname") // Sean
Skip([<nRows>]) --> nRows
the number of logical rows to move, relatively to the current row. A positive value means to skip forward, and a negative value means to skip backward. If omitted, a value of 1 is assumed
Actual number of rows skipped.
Skip() moves either forward or backward relatively to the current row. Attempting to skip forward beyond the last logical row positions the row set to the last logical row and TRowset:Eof() returns true (.T.). Attempting to skip backward beyond the first logical row positions the row set to the first logical row and TRowset:Bof() returns true (.T.).
rs := conn:CreateRowset("SELECT * FROM mytable") while !rs:Eof() ? rs:Read() rs:Skip() enddo
Write(<oRow>) --> NIL
an object containing new field values
Write() assigns new values to the current row's fields. It tries then to reflect the changes on the SQL server by executing <cUpdateSQL> statement passed to the TRowset constructor TConnect:CreateRowset().
rs := conn:CreateRowset("SELECT id,fname,lname FROM mytable",,,,; "UPDATE mytable SET fname=:fname,lname=:lname WHERE id=:id") oRow := map() oRow:fname := "John" oRow:lname := "Smith" rs:Write(oRow) ? rs:GetValue("fname") // John ? rs:GetValue("lname") // Smith