| SpSQL class |
| |
The function SpSQL() returns the class object of the SpSQL
class.
(for Xbase++ ver. 1.9, for previous versions contact to
us) |
| |
| Description |
| The SpSQL class is an abstract class that provides the mechanisms of Advantage Database Server and Advantage Local Server native support for the StreamLine SQL language. |
| |
| Class methods |
| |
| :new(<ServerPath>, [<ConnectionType>, <UserName>, <Password>],<LinkPassword>) |
| |
| Parameters |
| ServerPath |
Server name (or drive letter) to which to connect. If the application uses a server name as the parameter, it must include the share name for Windows NT/2000 and Windows 95/98/ME, or volume name for Novell NetWare as well. For example, use "\server\share" or "\server\volume". |
| ConnectionType |
Enumeration of server types to allow client connections to the given server or to a database in an Advantage Data Dictionary.
Options are ADS_REMOTECONNECTION or ADS_INTERNETCONNECTION |
| UserName |
Optional user name to verify in the data dictionary. The user name and password is validated against information stored in the data dictionary. If this parameter is not NULL, the ServerPath must specify an Advantage Data Dictionary. Otherwise, an error will be returned. |
| Password |
Optional password for the user |
| LinkPassword |
Password to link class to user application |
| |
|
| Description |
| |
Creates instances of the SpSQL class |
|
| :executeDirect(<cSQL>) |
| |
| Parameters |
| cSQL |
The SQL statement given as a string. |
| Description |
| |
Executes an SQL statement |
|
| :FindConnection25() |
| |
| Parameters |
| |
|
| Description |
| |
Finds a connection handle associated with the full path name. |
|
| :PrepareSQL(<cSQL>) |
| |
| Parameters |
| cSQL |
The SQL statement given as a string. |
| Description |
| |
Prepares an SQL statement for execution |
|
| : setString(<cFldName>,<cValue>) |
| |
| Parameters |
| cFldName |
Name of field to set. |
| cValue |
Store this data in the field. |
| Description |
| |
Sets a field value in a table to a string value |
|
| :setDate(<cFldName>,<dValue>) |
| |
| Parameters |
| cFldName |
Name of field to set. |
| dValue |
Date value |
| Description |
| |
Stores given date in the given date field or the date portion of a timestamp
field |
|
| :executeSQL() |
| |
| Parameters |
| |
|
| Description |
| |
Executes a prepared SQL statement |
|
| :closeTable() |
| |
| Parameters |
| |
|
| Description |
| |
Kills cursor |
|
| :closeSQLStatement() |
| |
| Parameters |
| |
|
| Description |
| |
Releases memory associated with a statement handle |
|
| :getFieldName(<nField>) |
| |
| Parameters |
| nField |
Field position of interest (the first field is 1). |
| Description |
| |
Retrieves the name of a field in a table |
|
| :getFieldType(<cField>) |
| |
| Parameters |
| cField |
Name of field. |
| Description |
| |
Retrieves the type of a field in a table |
|
| :getFieldLength(<cField>) |
| |
| Parameters |
| cField |
Name of field. |
| Description |
| |
Retrieves the length of a field in a table |
|
| :getFieldDecimals(<cField>) |
| |
| Parameters |
| cField |
Name of field. |
| Description |
| |
Retrieves decimals (for numerics) of a field in a table |
|
| :SQLViewSkip(nSkip) |
| |
| Parameters |
| |
|
| Description |
| |
The function which is called by a routine moving the record pointer. Typically, when using an XbpBrowse object to browse data in a cursor, :skipBlock should
be set to {|n| oSQL:SQLViewSkip (n) } . :SQLViewSkip() will be called and the XbpBrowse object will pass the number of rows which are requested by this function to skip. Then it performs the skip operations and returns the number of records actually skipped. |
|
:destroy() |
| |
| Parameters |
| |
|
| Description |
| |
Releases the system resources of the SpSQL object. |
|
:saveAsTable([<cFileName>], [<nTableType>]) |
| |
| Parameters |
| cFileName |
Name of file to save current cursor. File name can be chosen if it isn't
given. |
| nTableType |
Table type - ADS_ADT or ADS_CDX (default) |
| Description |
| |
Saves query to table |
|
:locate(<cExpres>, <lForward>) |
| |
| Parameters |
| cExpress |
Expression that defines desired records. |
| lForward |
If True, then search forward. If False, search backward. |
| Description |
| |
Performs a sequential search for a record that matches the given expression |
|
:continue() |
| |
| Parameters |
| |
|
| Description |
| |
Continues the locate command based on a previous call to AdsLocate |
|
:isFound() |
| |
| Parameters |
| |
|
| Description |
| |
Returns True if the locate function call was successful |
|
| Configuration |
| The instance variables in this group configure system resources fo SpSQL class. |
| |
| :tableType |
Sets the table type used by the statement handle. Options are ADS_CDX (default) and ADS_ADT. ADS_NTX is a valid option on a statement handle created on a database connection. |
| :tableCharType |
Sets the type of character data used in rowsets. Options are ADS_ANSI and ADS_OEM (default). This indicates the type of character data to be used in the rowset. |
| :cacheOpenCursors |
Number of cursors to cache. Provides caching of open cursors |
| :adsCacheRecords |
The number of records to be read into the cache by movement operations which use record caching. Sets the number of records to read ahead into client cache on skip operations |
| :isWarning |
If True sets visualisation of any error. |
| |
|
| Runtime (read only) |
| |
|
| :nConnect |
Connection handle if successful, otherwise 0 |
| :hCursor |
The cursor handle if executing a SELECT statement, otherwise returns 0. |
| :cQuery |
The SQL statement given as a string. |
| :errorCode |
Current error code (See Advantage Error guide) |
| :queryTime |
Time of lastest query in seconds |
| |
|
| :dataarea |
| |
The dynamic class. |
| Class methods: |
| :gotop() |
The function GoTop() moves the record pointer of a cursor to the first logical record. |
| :gobottom() |
The function GoBottom() moves the record pointer of a cursor to the last
logical record. |
|
| :skip(<nRecords>) |
| |
|
| Parameter |
| nRecords |
<nRecords> is an integer specifying the number of records to move the record pointer. If <nRecords> is less than zero, the record pointer is moved backwards, otherwise it is moved forward. The default value for <nRecords> is 1.The function works on the basis of logical records |
| Description |
| |
.The function Skip() moves the record pointer in a cursor by <nRecords> records. |
|
| :recno() |
The function RecNo() returns a value identifying the current record in a cursor. |
| :eof() |
The return value of Eof() is .T. (true) when an attempt has been made to move the record pointer in a cursor beyond the last logical data record. Otherwise .F. (false) is returned. |
| :lastrec() |
The function LastRec() returns a value identifying the last record in a cursor. |
|
| :goto(<xRecordID>) |
| |
|
| Parameter |
| xRecordID |
<xRecordID> is an expression which clearly identifies the data record in a cursor. |
| Description |
| |
The function Goto() sets the current record in a cursor. |
|
| Instance variables. |
| Instance variables of Dataarea class coincide with names of select-list items of SQL statement. |
| |
For example if we have select -
'SELECT firstname AS name, lastname FROM ourtable'
we can refer to our cursor fields - oSQL:dataarea:name, oSQL:dataarea:lastname
or if we have select -
'SELECT SUM(debit) AS oursum, clienttype FROM outtable GROUP BY clienttype '
we can refer to our cursor fields - oSQL:dataarea:oussum, oSQL:dataarea:clientype |
| |
|
| Example: |
|
#define "ace.ch"
PROCEDURE Main
LOCAL oSQL, cKey, nSum
/*
* Connect to Data Dictionary
*/ oSQL:=SpSql():new("Q: \DD.add",ADS_INTERNETCONNECTION,;
'User', 'Password', 'LinkPassword')
oSQL:tableType := ADS_NTX
oSQL:create()
/*
* Query for all data from table ourtable
*/
cKey := 'SELECT firstname, lastname, debit FROM ourtable WHERE debit >
0 '
oSQL:executeDirect(cKey)
oSQL:dataArea:gotop()
IF oSQL:dataArea:lastrec()=0
oSQL:destroy()
RETURN
ENDIF
nSum := 0
DO WHILE .NOT.oSQL:eof()
msgbox('The first record in table ourtable'+CHR(10)+CHR(13)+;
' FirstName '+oSQL:dataArea:firstname
+" "+;
' LastName '+oSQL:dataArea:lastname+"
"+STR(oSQL:dataArea:debit,12,2))
nSum := nSum + oSQL:dataArea:debit
oSQL:skip()
ENDDO
MsgBox('Sum of debits '+STR(oSQL:dataArea:debit,12,2))
oSQL:saveAsTable('C:\table.dbf')
oSQL:destroy()
RETURN
|
| |
