SQLExpress for Xbase++

Creating an ODBC Data Source:

1. On the Start menu, point to Settings | Control Panel | Administrative Tools
2. Select ODBC Data Sources
3. Click the User DSN, System DSN, or File DSN tab, and then click Add.
4. Select driver; then click Finish.
5. Complete all steps as required for the selected driver.

oConn := SQLConnection():new('MyNewDSN', 'MyUserId', 'MyPassword')
if oConn:isConnected
   oStmt := oConn:NewStatement()
   oStmt:SQLString := 'CREATE TABLE MyTable (Field1 char(5), Field2 char(20))'
   if oStmt:Execute() != SQL_XPP_ERROR
      oStmt:SQLString := 'INSERT INTO MyTable (Field1, Field2) VALUES (?,?)'
      for i := 1 to 10
        oStmt:Execute(LTrim(Str(i)), Replicate(Chr(64+i),20))
      next
      MsgBox("New table created and 10 rows added!")
   endif
endif

Finding ODBC Drivers:

Cursor Types:

• Forward-Only (SQL_CURSOR_FORWARD_ONLY):
  This is a non-scrollable cursor where the cursor only moves in a forward direction. It is generally much faster than the scrollable cursors listed below. This cursor does not build a result set at the database, therefore rows not yet fetched will reflect changes made to them by the time they are fetched.
  
  
• Static (SQL_CURSOR_STATIC):
  This is a scrollable cursor containing a snapshot of a data set defined by the query. Static cursors do not reflect real-time changes of the underlying data by other users. To achieve this effect a result set is built at the database (probably by duplicating the rows in a temporary database) or in some cases on the client machine by the ODBC driver, making this the slowest of the scrollable cursors.
  
  
• Keyset-Driven (SQL_CURSOR_KEYSET_DRIVEN):
  A scrollable cursor where the data in the result set is dynamic, but the rows that make up the result set are static. This cursor can therefore detect rows updated and deleted by other users but it can not detect newly inserted rows. To achieve this, only a result set of key values which identify each row in the result set is built at the database, making this a fast scrollable cursor.
  
  
• Dynamic (SQL_CURSOR_DYNAMIC):
  This scrollable cursor is capable of detecting rows updated, deleted, or inserted at the database by other users. Like the forward-only cursor this cursor does not build a result set at the database, but only keeps a scrollable cursor on the data in the result set, making it a very fast scrollable cursor. Dynamic cursors open faster than static or keyset-driven cursors. Internal temporary work tables must be built when static and keyset-driven cursors are opened, but they are not required for dynamic cursors. Dynamic cursor are therefore preferred for a large result set in which the user is likely to find an answer before retrieving many of the rows.
  

• Size of the result set.
• Percentage of the data likely to be needed.
• Performance of the cursor open.
• Need for cursor operations, such as scrolling or positioned updates.
• Level of visibility to data modifications made by other users.

Cursor Concurrency:

• Read-Only (SQL_CONCUR_READ_ONLY):
  Positioned updates through the cursor are not allowed and and no write locks are held on the rows that make up the result set. Shared locks however, may still be issued depending on the transaction isolation level present on the connection. For information on how transaction isolation levels control locking behavior see: TransactionIsolation.
  
  Although this is a read-only cursor, updates to the underlying table may still be performed using the :FieldPut() and :Delete() methods since SQLExpress will generate a searched UPDATE or DELETE statement to apply the changes.
  
  
• Optimistic with Values (SQL_CONCUR_VALUES):
  Optimistic concurrency control is used in situations when there is only a slight chance that a second user may update a row in the interval between when a cursor is opened and when the row is updated. When a cursor is opened with this option, no write locks are held on the underlying rows, which helps maximize throughput. If the user attempts to modify a row, the current values in the row are compared with the values retrieved when the row was last fetched. If any of the values have changed, the server knows that someone else has already updated the row, and it returns an error. If the values are the same, the server performs the modification.
  
  Selecting this concurrency option forces the user or programmer to accept the responsibility of dealing with the occasional error indicating another user has modified the row. A typical action taken by an application that receives this error is to refresh the cursor, get the new values, and then let the user decide whether to perform the modification on the new values.
  
  
• Optimistic with Row Versioning (SQL_CONCUR_ROWVER):
  This optimistic concurrency control option is based on row versioning. With row versioning, the underlying table must have a version identifier of some type (eg. a timestamp column) that the server can use to determine whether the row has been changed after it was read into the cursor. Each time a row with a timestamp column is modified in any way, the server automatically increments the value in the timestamp column. Therefore, to apply changes to a cursor row, the server does not need to compare the values in all columns, only the timestamp column.
  
  
• Scroll Locks (SQL_CONCUR_LOCK):
  This option implements pessimistic concurrency control, where the application attempts to lock the underlying database rows at the time they are read into the cursor result set. When using server cursors, an update lock is placed on the row when it is read into the cursor. If the cursor is opened within a transaction, the transaction update lock is held until the transaction is either committed or rolled back; the cursor lock is dropped when the next row is fetched. If the cursor has been opened outside a transaction, the lock is dropped when the next row is fetched. Therefore, a cursor should be opened in a transaction whenever the user wants full pessimistic concurrency control. An update lock prevents any other task from acquiring an update or exclusive lock, which prevents any other task from updating the row. An update lock, however, does not block a shared lock, so it does not prevent other tasks from reading the row unless the second task is also requesting a read with an update lock.
  
  

Note on class usage:

/* examples of calling CLASS methods and properties */
? "List of installed ODBC drivers:"
? SQLConnection():GetDrivers() // class method
? "SQLExpress ver:", SQLConnection():Version // inspecting class property
SQLConnection():DisplayErrors := .f.// setting class property

/* examples of calling OBJECT methods and properties */
? "creating connection object"
oConn := SQLConnection:New() // instantiating class to create object
oConn:DriverConnect() // calling object method
if oConn:isConnected // inspecting object property
   ? "connect to:", oConn:DbmsName // inspecting object property
else
   ? "unable to connect"
endif
oConn:Destroy() // calling object method

Updates:

Reporting problems:

Redistributable Software Components:

• SQLXPP.DLL
• ACEXPP.DLL

FUNCTIONS:

SQLErrorHandler( oOwner, cError, nRetCode, aErrObj ) -> NIL This function is typically called from within an :OnError codeblock. It will log the error to a file, flash a brief error message on the console and, if the error is a result of a communications link failure, the function will automatically attempt to reconnect to the data source. If a connection is re-established after the communications link failure, the function will resubmit a failed SQL statement. In this way a mission critical application will continue to run even though there may be sporatic network disconnections.

The name of the errorlog file can be specified using the SQLConnection:ErrorLogFile property. It defaults to SQLERROR.LOG Parameters: oOwner A reference to the SQLExpress object responsible for the error. oOwner will typically be derived from one of the following classes: SQLConnection, SQLStatement, SQLSelect or SQLCatalogQuery. cError A text string describing the error. If the string contains multiple lines, each line should be terminated with a CRLF. nRetCode The ODBC API function return code. Eg: SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE, SQL_NO_DATA, SQL_NEED_DATA, SQL_STILL_EXECUTING aErrObj An array of one or more SQLError objects describing the error or diagnostic information. SQLErrorLog(cString, [cFile], [lAppend]) -> lSuccess Write the string cString to a file. If not specified, cFile defaults to SQLERROR.LOG. If the file does not exist, it will be created. If lAppend is set to .T. (the default), then cString will be appended to the end of the file. Otherwise the current file (if there is one) will be overwritten. The function returns .T. if the data was written successfully.

CLASSES:

SQLConnection()

---

Manages an SQL connection to an ODBC driver and a data source. A SQLConnection object is needed before any database operations can be performed.

SQLConnection: Class Methods

:new( [cDataSource], [cUserName], [cPassword], [lReadOnly] ) -> self Establishes a new connection and returns a connection object. Setting lReadOnly to .T. establishes a connection that is not required to support SQL statements that cause updates to occur. This mode can be used to optimize locking strategies, transaction management, or other areas as appropriate to the driver or data source. :LoadDriver() -> self Loads the 3.x ODBC driver manager and sets environment attributes. Note this is automatically done at class initialization, you only need to call this method if the driver manager was unloaded. :UnloadDriver() -> lSuccess Commits all transactions, closes all open connections and unloads ODBC driver manager. :GetDrivers() -> aDrivers Return an array of available ODBC drivers and their attributes as keyword-value pairs.
{ {Driver1, Attributes1}, {Driver2, Attributes2},... } :GetDataSources() -> aDataSources Return an array of ODBC data sources and drivers available
{ {DSN1, Driver1}, {DSN2, Driver2},... } :ActiveConnections() -> nConnectionsOpen Returns the total number of open connections :GetActiveConnections() -> aConnectionObjects Returns an array of objects with references to all active SQLConnection instances. :FindConnection() -> oSQLConnection | NIL Finds an existing SQLConnection object on current tread. If an active connection does not exist on the current thread, the method returns NIL :CommitAllTransactions() -> lSuccess Commits all open transactions on all open connections :RollbackAllTransactions() -> lSuccess Rolls back all open transactions on all open connections :Close() -> lSuccess Same as :UnloadDriver() :DestroyContext() -> self Kills all connections that were created in current thread. By default a codeblock that calls this method is automatically assigned to the current thread's :atEnd ivar when the ivar is empty. This ensures that all connections (and statements created on that connection) are properly destroyed at thread termination.

SQLConnection: Class Properties

:Copyright readonly / character Returns SQLXpp library copyright information :DisplayErrors exported / logical Enable or disable console display of driver error and warning messages. Setting this to .F. disables display of messages on screen. The most recent driver message is saved in the :ErrorMessage variable. :EnvHandle readonly / numeric Returns the environment handle used by the ODBC driver manager :LoginTimeout exported / numeric Number of seconds to wait for a login request to complete before returning to the application. The default is driver-dependent. If the value is set to 0, the timeout is disabled and a connection attempt will wait indefinitely. :Version readonly / character Returns SQLXpp library version number as a string

SQLConnection: Object Methods

Connection Life Cycle :Connect( [cDataSource], [cUserName], [cPassword], [lReadOnly] ) -> lSuccess Establishes a new connection and returns a connection object. If a connection is already active on the current connection object it will first be disconnected. Setting lReadOnly to .T. establishes a connection that is not required to support SQL statements that cause updates to occur. This mode can be used to optimize locking strategies, transaction management, or other areas as appropriate to the driver or data source. :DriverConnect([nWindowHandle], [cConnectString], [nDriverCompletion], [lReadOnly] ) -> lSuccess Alternative method to connect to a data source. Any open connection that already exists on the current object when this method is called will be disconnected. This method can be used to:

1. Establish a connection using a connection string that contains the data source name, one or more user IDs, one or more passwords, and other information required by the data source.
2. Establish a connection using a partial connection string or no additional information; in this case, the driver can prompt the user for connection information.
3. Establish a connection to a data source that is not defined in the system information. If the application supplies a partial connection string, the driver can prompt the user for connection information.
4. Establish a connection to a data source using a connection string constructed from the information in a .dsn file.

Parameters: nWindowHandle Handle of the parent window. Only needed if the method is required to display dialog boxes. cConnectString A full, partial or empty connect string. This parameter may be passed by reference, in this case the return value will contain the completed connection string. A connection string consists of keyword-value pairs separated by a semicolon, example:
"DRIVER=SQL Server;SERVER=(local);UID=Boris;PWD=;DATABASE=Northwind"
"DSN=XYZ Corp;UID=Gomez;PWD=Sesame;UIDDBMS=JGomez;PWDDBMS=Shazam;"
"DSN=Xtreme sample data;UID=;PWD="
Please consult the documentation supplied with your ODBC driver for driver specific connection information.
nDriverCompletion Flag that indicates whether the driver manager must prompt for more connection information. If cConnectString is empty, the default is SQL_DRIVER_PROMPT, otherwise the default is SQL_DRIVER_COMPLETE_REQUIRED. The following options defined in SQLEXT.CH are available:
SQL_DRIVER_PROMPT - The driver presents a dialog box listing all data sources that have been installed. The user can choose which one to connect to.
SQL_DRIVER_COMPLETE - If the connection string cConnectString contains enough information, the driver will connect to that source; otherwise, a dialog box will be presented to prompt for information.
SQL_DRIVER_COMPLETE_REQUIRED - Behaves like SQL_DRIVER_COMPLETE, except that the driver disables the controls for any information that is not required to connect.
SQL_DRIVER_NOPROMPT - Establishes a connection only if the connection string cConnectString contains enough information
lReadOnly This is an optional parameter that can be set to .T. in order to establish a read only connection. This mode can be used to optimize locking strategies, transaction management, or other areas as appropriate to the driver or data source. Note that a read only connection may not be supported by all drivers. :BrowseConnect(cConnectString, bConnectBlock, [lReadOnly] ) -> lSuccess BrowseConnect provides an iterative method of discovering and enumerating the attributes and attribute values required to connect to a data source. A codeblock bConnectBlock is used to process the successive levels of attributes and attribute values returned by the driver. When all levels have been enumerated, a connection to the data source is completed, cConnectString will contain the complete connection string and the method will return .T. Note; if an open connection already exists on the current object when this method is called, the open connection will be disconnected. This method can be used to:

1. Build custom dialog boxes to prompt for connection information.
2. Browse the system for data sources that can be used by a particular driver, possibly in several steps. For example, the user might first browse the network for servers and, after choosing a server, browse the server for databases accessible by the driver.
Parameters: cConnectString A connection request string. When passed by reference the return value will contain the completed connection string. A connection string consists of keyword-value pairs separated by a semicolon. The initial connection string must containg either a 'DSN' or 'DRIVER' keyword-value pair. For example, if the following connection request is specified:
    "DRIVER=SQL Server"
The driver may return the following string to the bConnectBlock codeblock:
    "SERVER:Server={web,dev,sales};UID:Login ID=?;PWD:Password=?;*APP:AppName=?;*WSID:WorkStation ID=?"
The codeblock may then modify this string as follows:
    "SERVER=sales;UID=James;PWD=BlueJay;APP=;WSID="
The driver uses this information to connect to the sales server as James with the password BlueJay, and then returns the following string back to the codeblock:
    "*DATABASE:Database={Employees,History,Orders}"
The codeblock modifies the string by selecting the Orders database:
    "DATABASE=Orders"
This is the final piece of information the driver needs to connect to the data source. BrowseConnect returns .T. and cConnectString contains the completed connection string:
    "DRIVER=SQL Server;SERVER=Sales;UID=James;PWD=BlueJay;APP=;WSID=;DATABASE=Orders"
bConnectBlock Codeblock to execute for each iteration until a connection is established. The purpose of the codeblock is to process the connection string that is passed to it by reference from the driver. The codeblock must return a logical value, if it returns .F. then the connection request will be cancelled. lReadOnly This is an optional parameter that can be set to .T. in order to establish a read only connection. This mode can be used to optimize locking strategies, transaction management, or other areas as appropriate to the driver or data source. Note that a read only connection may not be supported by all drivers. Example: cConnection := "DRIVER=SQL Server"
oConn := SQLConnection():new()
oConn:BrowseConnect( @cConnection, {|cConn| GetUserInput(@cConn)} )
:Reconnect( [lDropExistingConnection], [nConnectionTimeout]) -> lSuccess This method attempts to reconnect to a data source if an existing connection has been lost. The method will not drop any SQLStatement objects that have been previously defined on this connection. If the connection is re-established, it should be theoretically possible to re-execute any failed commands and continue as if the connection was never lost. Executing this method when an active connection already exists, performs no action. If an active connection already exists and lForceReconnect is NIL or .F., then no action is performed, to force a reconnect in this case, set lForceReconnect to .T.

If supported by the driver, a connection timeout may be specified by setting nConnectionTimeout to the number of seconds to wait for a request on the connection to complete before returning to the application. The driver should return SQLSTATE HYT00 (Timeout expired) anytime that it is possible to time out in a situation not associated with query execution or login. If the value is set to 0 (the default), then there is no timeout. :Disconnect() -> lSuccess Disconnects from the data source. All open SQL statements on the current connection are automatically dropped and associated memory is released. :Destroy() -> lSuccess Kills connection object and disconnects from the data source. All open SQL statements on the current connection are automatically dropped and associated memory is released. SQL Statement :Execute( cSQLStatement [, aParameters] ) -> nRowsAffected | -1 | SQL_XPP_WARNING | SQL_XPP_ERROR Execute the specified SQL statement. Use this method if the statement is to be executed only once. The only advantage being; it requires less lines of code than creating a new SQLStatement object and subsequently destroying it. :Cursor( [cSQLStatement], [nConcurrency], [nCursorType] ) -> oSQLSelect Create a new SQLSelect cursor object on the current connection. A cursor can also be created directly as follows:
oCursor := SQLSelect():new(cSQLStatement, oConnection, nConcurrency, nCursorType)
Refer to the SQLSelect:new() method for further information on parameter values. :DataSet( [xSQLSelect], [xSQLParam], [aPrimaryKey], [nMaxRows], [abAddCols], [bFilter], [lTrim], [lDateTimeAsDate] ) -> oSQLDataSet Create an instance of the SQLDataSet class on the current connection. A dataset object can also be created directly as follows:
oDataSet := SQLDataSet():new(xSQLSelect, oConnection, xSQLParam, aPrimaryKey, nMaxRows, abAddCols, bFilter, lTrim, lDateTimeAsDate )
Refer to the SQLDataSet:new() method for further information on parameter values. :NewStatement( [cSQLStatement] ) -> oSQLStatement Create a new SQLStatement on the current connection. A statement object can also be created directly as follows:
oStmt := SQLStatement():new(cSQLStatement, oConnection) :DropStatement( oStmt ) -> self Drops a SQLStatement or SQLSelect object and releases associated memory. A SQL Statement object can also dropped as follows:
oStmt:destroy()

Warning: It is very important that all SQLStatement objects, including objects subclassed from the SQLStatement class, are explicitly destroyed when no longer needed. Otherwise the client application (and possibly server) will consume more and more memory and may eventually run out of ODBC statement handles. Transaction (may not be supported by all data sources) :BeginTransaction() -> lSuccess Turns off autocommit and initiates a transaction. Once started a transaction can be committed by calling the method :CommitTransaction() or :EndTransaction() or it can be aborted by calling the method :RollbackTransaction()

NOTE: Nested transactions are not supported. :inTransaction() -> lReturn Returns whether or not a transaction is pending. :EndTransaction() -> lSuccess Turns on autocommit and terminates a transaction. :CommitTransaction() -> lSuccess Commit a pending transaction. :RollbackTransaction() -> lSuccess Void all pending database changes made after calling :BeginTransaction() Example: oConn:BeginTransaction()
oStmt := oConn:NewStatement()
oStmt:OnError := {||Break()}
BEGIN SEQUENCE
   // move order into history tables
   oStmt:SQLString := "INSERT INTO ORD_HIST SELECT * FROM ORD WHERE ORD_NUM='12345'"
   oStmt:Execute()
   oStmt:SQLString := "INSERT INTO ORD_DETAIL_HIST SELECT * FROM ORD_DETAIL WHERE ORD_NUM='12345'"
   oStmt:Execute()
   // delete the order
   oStmt:SQLString := "DELETE FROM ORD WHERE ORD_NUM='12345'"
   oStmt:Execute()
   oStmt:SQLString := "DELETE FROM ORD_DETAIL WHERE ORD_NUM='12345'"
   oStmt:Execute()
   // if we get here, commit the transaction
   oConn:CommitTransaction()
RECOVER
   // an error occured, void transaction and log error
   oConn:RollbackTransaction()
   SQLErrorLog("ERROR: Delete order failed, transaction rolled back")
ENDSEQUENCE
oConn:EndTransaction()
oStmt:Destroy()
Status/Configuration :ActiveStatements() -> nStatements Returns total number of active SQL statements managed by current connection object. :GetActiveStatements() -> aStatementObjects Returns an array of objects with references to all SQLStatement and SQLSelect instances managed by current connection object. :GetAttribute( nAttribute [, lReturnAsCharacter] ) -> xValue Returns the current setting of a connection attribute. When lReturnAsCharacter is specified as .T., the return value will be a character string, otherwise the return value will be a numeric. Refer to the Microsoft ODBC API for SQLGetConnectAttr. :GetInfo( nInfoType ) -> cInfoString Returns general information about the driver and data source. Refer to the Microsoft ODBC API for SQLGetInfo. :NativeSQL( cSQLString [, nMaxLen] ) -> cNativeSQLString Returns the SQL string as modified by the driver. The SQL statement is not executed. The maximum length of the returned string can be specified by nMaxLen, the default is 1024 bytes. :SetAttribute( nAttribute, xValue ) -> lSuccess Sets attributes that govern aspects of connections. Refer to the Microsoft ODBC API for SQLSetConnectAttr.

SQLConnection: Object Properties

Configurable Properties :BufferSize exported / numeric Specifies the default maximum buffer size used to retrieve data from cursors created on this connection. Smaller buffer sizes may result in more calls to the driver in order to retrieve the full data, whereas larger buffer sizes will result in greater memory use by the application. It is also possible to specify this property for each cursor column individually (see SQLColumn:bufferSize). Default value is: 65536 :Cargo exported / any data type Used to attach additional information to the object as required by the programmer. :ConnectionTimeout exported / numeric Number of seconds to wait for any request on the connection to complete before returning to the application. The driver should return SQLSTATE HYT00 (Timeout expired) anytime that it is possible to time out in a situation not associated with query execution or login. If the value is set to 0 (the default), then there is no timeout. :DateTimeAsDate exported / logical This attribute can be used to force SQL datetime values (not including timestamps) retrieved on this connection to be converted to native Xbase++ date types. Since Xbase++ does not have a native datetime datatype, the default behaviour is to return datetime values as character strings with complete date and time portions intact. Setting this property to .T. will cause the time portion of a SQL datetime value to be lost in the converstion. :ErrorLogFile exported / character The name of the file where errors are logged. Default is SQLERROR.LOG. This property is used by the SQLErrorHandler function. :IdentifierQuoteChar exported / character Starting and ending delimiter of a quoted identifier in a SQL statement. Identifiers used in SQL statements must always be delimited when reserved words are used for object names or portions of object names, or when using special characters not listed as qualified identifiers (such as a blanks). The standard ANSI quoted identifier is the double quote character. Example: oCursor := oConn:Cursor('SELECT * FROM "Blanks in Table Name"') :OnError exported / codeblock Provides the ability to optionally specify a codeblock that will be evaluated when an ODBC and/or data source specific error occurs. When evaluated four parameters will be passed to the codeblock:
(1) a reference to the SQLExpress object responsible for the error,
(2) a complete text error string including call stack,
(3) the ODBC API function return code, and
(4) an array of one or more SQLError objects describing the error or diagnostic information.

Specifying an :OnError codeblock (including a NIL codeblock {||NIL}) will disable the default automatic display of error messages occurring on the target connection. Example: oConn:onError := {|oOwner, cError, nRetCode, aErrObj| SQLErrorLog(cError), MsgBox(cError)} :RetryDeadlockVictim exported / logical When this property is set to .T. (the default), statements and queries that fail due to a deadlock, will be automatically resubmitted. A deadlock occurs when there is a cyclic dependency between two or more clients for some set of resources. :TransactionIsolation exported / numeric Transaction isolation refers to the ability of a DBMS to shield transactions from the effects of updates performed by other concurrent transactions. Setting transaction isolation levels allows programmers to trade off increased risk of certain integrity problems with support for greater concurrent access to data. Each isolation level offers more isolation than the previous level, but does so by holding more restrictive locks for longer periods. The transaction isolation levels are:

SQL_TXN_READ_UNCOMMITTED
Implements a dirty read where no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This is the least restrictive of the four isolation levels. Transactions running at this level are usually read-only.

SQL_TXN_READ_COMMITTED
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data.

SQL_TXN_REPEATABLE_READ
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.

SQL_TXN_SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. Readonly Properties :Handle readonly / numeric A reference to the ODBC connection handle. :ThreadID readonly / numeric Xbase++ ID number of thread that was used to establish connection for current connection object. :ErrorCode readonly / numeric Most recent result code returned by the ODBC driver. :ErrorMessage readonly / character Most recent warning or error message returned by the ODBC driver. :CollationSequence readonly / character Name of the default collation for the default character set for this server (for example `ISO 8859-1' or EBCDIC). If this is unknown, an empty string will be returned. :DatabaseName readonly / character Name of the current database in use as defined by the data source. :DbmsName readonly / character Name of the DBMS product accessed by the driver. :DbmsVersion readonly / character Version of the DBMS product accessed by the driver. Returned in the following form: ##.##.#### :IdentifierCase readonly / numeric Determine if identifiers such as table and column names are case sensitive. The following values are possible: SQL_IC_SENSITIVE, SQL_IC_UPPER, SQL_IC_LOWER, SQL_IC_MIXED.

SQL_IC_SENSITIVE indicates that identifiers are case sensitive, the rest indicate that they are not sensitive. The three values that are not case-sensitive further describe the case in which identifiers are stored in the system catalog. How identifiers are stored in the system catalog is relevant only for display purposes, such as when an application displays the results of a catalog function; it does not change the case-sensitivity of identifiers. :MaxActiveConnections readonly / numeric Maximum number of active connections supported by the driver. This value can reflect a limitation imposed by either the driver or the data source. If there is no specified limit or the limit is unknown, this value is set to zero. :MaxActiveStatements readonly / numeric Maximum number of active statements allowed by driver on current connection. A statement is defined as active if it has results pending, with the term "results" meaning rows from a SELECT operation or rows affected by an INSERT, UPDATE, or DELETE operation. This value can reflect a limitation imposed by either the driver or the data source. If there is no specified limit or the limit is unknown, this value is set to zero. :MaxStatementLen readonly / numeric Maximum length of a SQL statement allowed by driver on current connection. :MultipleResults readonly / logical This instance variable will be .T. if the data source currently connected to supports multiple result sets. Batched SQL statements and procedures always return multiple result sets when they include multiple result set-generating statements. :isTransactionCapable readonly / logical Indicates if driver is capable of transaction processing. :isReadOnly readonly / logical Indicates if connection has been defined as readonly. :isConnected readonly / logical Indicates if connection object is currently connected to a data source. In the event that a connection to a server is lost, it may take a few seconds before :isConnected returns FALSE. :ConnectString readonly / character Contains the final connection string when :DriverConnect() or :BrowseConnect() was used to establish connection. A connection string consists of keyword-value pairs separated by a semicolon, example:
"DRIVER=SQL Server;SERVER=(local);UID=sa;PWD=xena;DATABASE=Northwind" :DataSource readonly / character Name of ODBC data source currently connected to. :UserName readonly / character Login user name as returned by driver. :Password readonly / character Login password as returned by driver.

SQLStatement()

---

Manages an SQL statement. This class can be used for executing SQL statements that do not generate a result set (CREATE, DROP, ALTER, GRANT, UPDATE, DELETE, INSERT, ...). SQL statements that generate result sets (cursors) such as `SELECT` statements and some stored procedures must use the SQLSelect class.

SQLStatement: Class Methods

:new( [cSQLStatement], [oConnection] ) -> self Creates a new instance of the SQLStatement class. If oConnection is not specified then an existing connection (if one exists on the current thread) will be used, otherwise the user will be prompted to connect to a data source.

SQLStatement: Object Methods

Statement Life Cycle :Prepare( [cSQLStatement] ) -> lSuccess Prepares an SQL string for execution. Prepared execution is faster than direct execution for statements executed more than once. The primarily reason for this is that the statement is compiled only once; statements executed directly are compiled each time they are executed. Prepared execution should not be used for statements executed a single time. For such statements, it is slightly slower than direct execution since it requires additional function calls. For additional information on prepared execution refer to the Microsoft ODBC API for SQLPrepare.

NOTE: Parameter queries can only be implemented by using a stored query. Most servers implement stored queries using a precompiled query plan, in such cases parameter queries that contain parameters on indexed fields may not execute efficiently. Because the query engine does not know the values to be passed in a parameter in advance, it can only guess as to the most efficient query plan. In some instances, you can substantially improve performance by not preparing the SQL query containing parameters. :Execute( [aParameters] ) -> nRowsAffected | -1 | SQL_XPP_WARNING | SQL_XPP_ERROR, or;
:Execute( [xParam1] [, xParam2, …] ) -> nRowsAffected | -1 | SQL_XPP_WARNING | SQL_XPP_ERROR Execute an SQL statement. If the statement has not been prepared, it will be submitted to the data source, compiled and executed. If the SQL statement includes parameter markers (one or more question marks), then an array or list of parameter values must be specified when calling this method. A parameter value can be specified as a simple Xbase++ data type or as an SQLValue object. For information about parameters, see "Statement Parameters" in the Microsoft ODBC API documentation.

If the statement is executed successfully the method will return a count of the number of rows affected by the SQL statement. If the statement is successful but a count of the number of rows affected can not be determined, (-1) is returned. If the statement is executed successfully but with warning messages, SQL_XPP_WARNING is returned, otherwise if execution fails, SQL_XPP_ERROR is returned. :MoreResults() -> lSuccess Initiates processing of the next result set in a procedure or a batch of SQL statements. After executing a batch or procedure that includes multiple result set-generating statements, the application is positioned on the first result set. The result set may simply include a row count of the number of rows affected or it may include a complete cursor. The application may process the first or any subsequent result sets, just as it would if there were just a single result set. Once it is done with the first result set, the application calls MoreResults() to move to the next result set. If another result set or count is available, MoreResults returns .T. and initializes the result set or count for additional processing. If an error occurs or no more result sets are available, MoreResults returns .F.
Example: /* call CalculateDemand stored proc with the following input params: 'HP41CV', 4 , DATE() */
oStmt := SQLStatement():New("{call CalculateDemand(?,?,?)}", oConnect)
nRowsAffected := oStmt:Execute("HP41CV", 4, date())
if nRowsAffected != SQL_XPP_ERROR
   ? nRowsAffected, "Rows affected"
   while oStmt:MoreResults()
       ? oStmt:RecCount(), "Rows affected"
   end
endif :Cancel() -> lSuccess Cancel processing of an SQL statement. This can be either a function on a statement that needs data or a function running on a statement object in another thread. :Reset( [cSQLStatement], [lDropAttributes] ) -> self This method will reinitialize a statement object by dropping the current ODBC statement handle and then creating a new one. It is slightly faster than destroying and subsequently creating a brand new object. By default the new statement will inherit the old statement's attributes, if this is not desired then lDropAttributes must be specified as .T. :Destroy() -> self Drop the SQL statement object and release associated memory. An SQL statement object can also be dropped by passing it to it's connection :DropStatement() method, e.g.: oConnection:DropStatement( oStmt )

Warning: It is very important that all SQLStatement objects, including objects subclassed from the SQLStatement class, are explicitly destroyed when no longer needed. Otherwise the client application (and possibly server) will consume more and more memory and may eventually run out of ODBC statement handles. Status/Configuration :GetAttribute( nAttribute [, lReturnAsCharacter] ) -> xValue Returns the current setting of a statement attribute. When lReturnAsCharacter is specified as .T., the return value will be a character string, otherwise the return value will be a numeric. Refer to the Microsoft ODBC API for SQLGetStmtAttr. :RecCount() -> nRowsAffected | -1 Returns a count of the number of rows affected by the current SQL statement, or (-1) if the number of rows is not available. :SetAttribute( nAttribute, xValue ) -> lSuccess Sets attributes related to the statement object. Refer to the Microsoft ODBC API for SQLSetStmtAttr.

SQLStatement: Object Properties

Configurable Properties :Cargo exported / any data type Used to attach additional information to the object as required by the programmer. :DisplayErrors exported / logical Enable or disable console display of driver error and warning messages. Setting this to .F. disables display of messages on screen. The most recent driver message is saved in the :ErrorMessage variable. :OnError exported / codeblock Provides the ability to optionally specify a codeblock that will be evaluated when an ODBC and/or data source specific error occurs. When evaluated four parameters will be passed to the codeblock:
(1) a reference to the SQLExpress object responsible for the error,
(2) a complete text error string including call stack,
(3) the ODBC API function return code, and
(4) an array of one or more SQLError objects describing the error or diagnostic information.

Specifying an :OnError codeblock (including a NIL codeblock {||NIL}) will disable the default automatic display of error messages occurring on the target SQLStatement object. Note that the SQLStatement object will automatically inherit the :OnError property from it's parent connection object when initialized. Example: oCursor:onError := {|oOwner, cError, nRetCode, aErrObj| SQLErrorLog(cError), MsgBox(cError)} :ParamList exported / array Contains a copy of parameters passed to the :Execute() method. If parameters were passed as an array, :ParamList will contain a reference to this array (the contents of the array will not be copied). :SQLString exported / character Access existing SQL statement string or assign new SQL statement string for execution. By assigning a new SQL statement to this property, it is possible to execute the new statement by reusing an existing object. This is slightly faster than destroying and subsequently creating a brand new object. Example: oStmt := oConn:NewStatement()
oStmt:SQLString := "INSERT INTO ORD_HIST SELECT * FROM ORD WHERE ORD_NUM='12345'"
oStmt:Execute()
oStmt:SQLString := "INSERT INTO ORD_DETAIL_HIST SELECT * FROM ORD_DETAIL WHERE ORD_NUM='12345'"
oStmt:Execute()
oStmt:Destroy()
Readonly Properties :Connection readonly / SQLConnection object Reference to the parent SQLConnection object. :ErrorCode readonly / numeric Most recent result code returned by the ODBC driver. :ErrorMessage readonly / character Most recent warning or error message returned by the ODBC driver. :Handle readonly / numeric A reference to the current ODBC statement handle.

SQLSelect()   (inherited from SQLStatement)

---

This class is inherited from the SQLStatement class, it is used to manage a result set generating stored procedure or an SQL `SELECT` statement that retrieves a set of rows from an ODBC data source. The rows can be read, modified, or deleted using methods of this class. SQL statements that do not generate a result set should use the parent SQLStatement class.

SQLSelect: Class Methods

:new( [cSQLStatement], [oConnection], [nConcurrency], [nCursorType] ) -> self Creates a new instance of the SQLSelect class. If oConnection is not specified then an existing connection (if one exists on the current thread) will be used, otherwise the user will be prompted to connect to a data source.

if specified, nConcurrency should be one of the following constants defined in SQLEXT.CH:
SQL_CONCUR_READ_ONLY
SQL_CONCUR_LOCK
SQL_CONCUR_ROWVER
SQL_CONCUR_VALUES (this is the default)

if specified nCursorType should be one of the following constants defined in SQLEXT.CH:
SQL_CURSOR_FORWARD_ONLY
SQL_CURSOR_DYNAMIC (this is the default)
SQL_CURSOR_STATIC
SQL_CURSOR_KEYSET_DRIVEN

For additional information on cursor concurrency and cursor types refer to the Microsoft ODBC API documentation.

SQLSelect: Object Methods

Cursor Life Cycle :Close() -> lSuccess Close cursor and free allocated memory. Cursors remain open until they are explicitly closed, except when a transaction is committed or rolled back, in which case some data sources close the cursor. Even cursors on empty result sets (result sets created when a statement executed successfully but which returned no rows) must be explicitly closed. A cursor will also be closed when the :Destroy method is called, the only difference between :Destroy and :Close is that :Destroy will also drop the SQL statement object from the connection and release the associated ODBC statement handle. :Refresh() -> nRowsAffected | -1 | SQL_XPP_WARNING | SQL_XPP_ERROR Refresh the cursor from the data source (the SELECT statement is re-executed with the same parameters). Once the cursor is refreshed the row pointer will be positioned at the top of the result set. :SetPrimaryKey( xColumn [, cTable] ) -> lSuccess Specify a column or array of columns to be used as a unique index in searched updates and deletes.
xColumn can be provided as a character string (column name), numeric (column position) or array containing column names or column positions. When xColumn is NIL, all primary key assignments made previously for cTable are cleared. Otherwise, calling this method more than once will add the newly specified column to the primary key list for cTable.

Warning: To ensure that updates and deletes are applied to the desired row, an ODBC driver and cursor must either support positioned updates and deletes, or the SELECT statement must include a minimum number of columns required to generate a unique key. Record Movement :Bof() -> lAtBof Returns .T. when the row pointer is at top of result set. :Eof() -> lAtEof Returns .T. when the row pointer is at bottom of result set. :GoTo( [nRow] ) -> lSuccess Position the row pointer on the specified row number. The method returns .T. if the operation was successful.
NOTE: Some data providers do not support movement by row number. :GoTop() -> lSuccess Position the row pointer on the first row of the result set. The method returns .T. if the operation was successful.
NOTE: Some data providers do not support this option. :GoBottom() -> lSuccess Position the row pointer on the last row of the result set. The method returns .T. if the operation was successful.
NOTE: Some data providers do not support this option. :RecCount() -> nRows | -1 Returns a count of the number of rows in the result set, or (-1) if the number of rows is not available. This method will attempt to retrieve the row count from the driver directly, however if the driver does not provide this information, the row count will be calculated by executing a "SELECT COUNT(*) FROM..." statement based on the original SELECT statement. Consequently, the returned value may not always be exact. :RecNo() -> nRowNumber Pointer to current row position in result set. :Skip( [nRowCount] ) -> lSuccess Move forward or backward relative to the current row. nRowCount specifies the number of rows to skip. Positive forward; negative numbers move backward. If omitted, +1 is assumed. The method returns .T. if the operation was successful. If nRowCount is 0, the current row buffer will be refreshed.
NOTE: Some data providers are not capable of moving backward. :Skipper( nSkip ) -> nSkipps This method can be used to create a TBrowse or XbpBrowse :skipBlock code block that is used to move the row pointer of the cursor. nSkip specifies the number of rows to skip. Positive numbers move forward; negative backward. The method returns the number of rows actually skipped. If the number returned is different from the number requested, then either the end or beginning of the result set has been reached.
Example: oBrowse:skipBlock := {|n| oCursor:skipper(n)} :Locate( axbFind, [axColumn], [lRest], [nCount] ) -> lFound Sequentially search for axbFind within a result set. If a row matching the specified condition is found, the method returns .T. and the row pointer is positioned on this row, otherwise the row pointer is not moved.

axbFind can be specified as either a single value or an array of values representing the search expression. Alternatively, axbFind can be specified as a codeblock containing the search expression. When evaluated, the following two parameters are passed to this codeblock; (1) the SQLSelect cursor object and (2) the current row number.

When axbFind is provided as a codeblock, then axColumn is not required since the codeblock will control what columns are inspected. Otherwise, axColumn must be specified as either a single value or array of values representing the numeric (column indexes) or character (column names) to be searched.

The optional logical value lRest specifies whether all data rows are searched (the default), or only the rows from the current to the last row (lRest = .T.).

nCount is an optional numeric value indicating the number of rows searched, the default is all rows. Examples: oCursor:Locate( Date(), "DUE_DATE") // single value/field
oCursor:Locate( {"James","T","Kirk"}, {"FNAME","INITIAL","LNAME"}) // multiple values/fields
oCursor:Locate( {|o|o:FieldGet("SALARY")>=100000} ) // custom codeblock

Warning: The simple equals operator (=) is used when performing comparisons. Due to this, the search of character strings is dependent on the settings SET EXACT, SET LEXICAL and SET COLLATION. :Seek( axValue, [axColumn], [lSoftSeek] ) -> lFound Perform a fast binary search for axValue within a scrollable and sorted result set. This method requires that the result set is both scrollable and sorted by axColumn. If the result set is not sorted on the desired column, use the :Locate method instead. A result set can be sorted by specifying an ORDER BY condition in the SQL SELECT statement.

If a row matching the specified condition is found, the method returns .T. and the row pointer is positioned on this row, otherwise the row pointer is not moved. lSoftSeek is an optional logical parameter controlling the position of the row pointer in cases where axValue is not found within the result set. If this parameter is set to .T. and axValue is not found, the row pointer will be positioned on the first row whose value is greater than axValue. Alternatively, if this parameter is set to .F. (the default), the row pointer will not be moved from the current position.

axValue can be specified as either a single value or an array of values representing the search expression. Examples: oCursor := SQLSelect():new("SELECT * FROM Products ORDER BY Type,Make,Model", oMyConn)
oCursor:Execute()
oCursor:Seek( "Camera" ) // single value/field
oCursor:Seek( {"Camera","Contax","RTS"} ) // multiple values/fields

Warning: The simple equals operator (=) is used when performing comparisons. Due to this, the search of character strings is dependent on the settings SET EXACT, SET LEXICAL and SET COLLATION.

A potential problem exists if the collation sequence used for sorting differs between Xbase++ and the ODBC data source. If the sort sequence is different, then the :Seek method may fail to find a valid row. The solution is to change the collation sequence on the DBMS to match the sort sequence of the Xbase++ client (or vice-versa). If this is not possible, then use one of the following alternatives:

1. Switch to SQLDataSet, use :Sort to sort the result set on the client side and then execute :Seek.
2. Use :Locate instead of :Seek (in this case, the 'ORDER BY' in the SELECT statement will no longer be required).

Example - setting collation sequence for server and client: /* MS-SQL Server script to create table using binary collation sequence */
CREATE TABLE Products (
  ItemNo INT,
  Qty INT,
  Cost DECIMAL(6,2),
  Type VARCHAR(20) COLLATE Latin1_General_BIN,
  Make VARCHAR(20) COLLATE Latin1_General_BIN,
  Model VARCHAR(20) COLLATE Latin1_General_BIN,
  Comments VARCHAR(200))

/* Xbase++ code to set matching collation sequence on client-side */
SET COLLATION TO ASCII // matches Latin1_General_BIN collation in SQL-Server
oCursor := oMyConn:Cursor("SELECT * FROM Products ORDER BY Type")
oCursor:Execute()
oCursor:Seek("Camera")
Get/Set Data :Append() -> self Insert a new row. The new row will be physically inserted when the row pointer is moved or when the :UpdateRow() method is called. The operation may be cancelled before the row is physically inserted by executing the :Delete() or :CancelUpdate() methods. The location of the inserted row depends on the database driver. In order to get at the new row it may be necessary to refresh the cursor by executing the :Refresh() method. :Delete() -> self Mark current row for deletion. The row will be physically deleted when the row pointer is moved or when the :UpdateRow() method is called. The operation may be cancelled before the row is physically deleted by executing the :CancelUpdate() method. :Deleted() -> lDeleted Indicates if current row has been marked for deletion. The row will be physically deleted from the target data source when the row pointer is moved or when the :UpdateRow() method is called. :isRowModified() -> lModified Indicates if current row in result set has been modified. The row will be physically written to the target data source when the row pointer is moved or when the :UpdateRow() method is called. :UpdateRow() -> lSuccess Modify the physical table from values in the current row buffer. A row will also be updated automatically when the row pointer is moved. Column values that are not explicitly assigned (using :FieldPut()) are not changed by the :UpdateRow() method. In order to ensure that updates and deletes are applied to the desired row, the cursor must contain at minimum all fields required for generating a unique key. A count of the number of rows affected by the update operation will be stored in the instance variable :RowsUpdated. :CancelUpdate() -> self Cancel changes made to the current row buffer. This can only be performed if the row pointer has not been moved from the desired row or if :UpdateRow() has not been executed. :GetSQLColumn( xColumn ) -> oSQLColumn | NIL Returns a reference to the SQLColumn object for the specified cursor column. xColumn can be specified as a numeric (column index) or a character (column name). The method returns NIL if the specified column does not exist. :FCount() -> nColumns Returns a count of the number of columns in the result set. :FieldPos( xColumn ) -> nColumnIndex Return the position of a specified column within the result set. xColumn can be specified as a numeric (column index) or a character (column name). The method returns 0 if the specified column does not exist. :FieldName( xColumn ) -> cColumnName Return the name of the specified column. xColumn can be specified as a numeric (column index) or a character (column name). The method returns an empty string if the specified column does not exist. :FieldGet( xColumn ) -> xValue | NIL Retrieve the contents of the specified column. xColumn can be specified as a numeric (column index) or a character (column name). :isNull( xColumn ) -> TRUE | FALSE Returns .T. if the value for the specified column is NULL. xColumn can be specified as a numeric (column index) or a character (column name). :FieldPut( xColumn, xValue ) -> xValue | NIL Assign a value to the specified column within the result set. xColumn can be specified as a numeric (column index) or a character (column name), xValue can be specified as a simple Xbase++ data type or as an SQLValue object. If the assignment fails, an error is raised and the method returns NIL.
NOTE: If the column is nullable, it is possible to set xValue to NIL. :RecordGet() -> aRowValues | NIL Return the current cursor row as an array. :RecordPut( aRowValues ) -> aRowValues | NIL Modify the current cursor row with the contents of the passed array aRowValues.
NOTE: The structure of the specified array aRowValues must correspond exactly to the structure of a cursor record.

SQLSelect: Object Properties

Configurable Properties :Concurrency exported / numeric Get/Set cursor concurrency. This attribute can only be set before the SQL select statement is executed. The following values are possible (some data sources may not support all options): SQL_CONCUR_READ_ONLY, SQL_CONCUR_LOCK, SQL_CONCUR_ROWVER, SQL_CONCUR_VALUES

For additional information on cursor concurrency refer to the Microsoft ODBC API documentation. :CursorType exported / numeric Get/Set cursor type. This attribute can only be set before the SQL select statement is executed. The following values are possible (some data sources may not support all options): SQL_CURSOR_FORWARD_ONLY, SQL_CURSOR_DYNAMIC, SQL_CURSOR_STATIC, SQL_CURSOR_KEYSET_DRIVEN

For additional information on cursor types refer to the Microsoft ODBC API documentation. :CursorName exported / character Get/Set cursor name. This attribute can only be set before the SELECT statement is executed. :DateTimeAsDate exported / logical This attribute can be used to force SQL datetime values (this does not include timestamps) to be converted to native Xbase++ date types. Since Xbase++ does not have a native datetime datatype, the default behaviour is to return datetime values as character strings with complete date and time portions intact. Setting this property to .T. will cause the time portion of a SQL datetime value to be lost in the converstion. This property is inherited from the parent connection object and can only be set before the result set is generated. :MaxRows exported / numeric Get/Set the maximum number of rows to return to the application for a SELECT statement. If the value equals 0 (the default), then the driver returns all rows. This attribute is intended to reduce network traffic. Conceptually, it is applied when the result set is created and limits the result set to the first :MaxRows rows. If the number of rows in the result set is greater than :MaxRows, the result set is truncated. Consequently, this attribute can only be set before the SELECT statement is executed. May not be supported by all drivers. :QueryTimeout exported / integer numeric Get/Set the number of seconds to wait for an SQL statement to execute before returning to the application. If the value is equal to 0 (default), then there is no timeout. This attribute can only be set before the SELECT statement is executed. May not be supported by all drivers. :UsePositionDelete exported / logical This property indicates the method that will be used to delete cursor rows. When .T., a positioned SQL expression will be used, otherwise a searched SQL expression will be generated. By default this property will always be true if the driver and cursor support positioned deletes. A programmer may force searched expressions to be generated by setting this property to .F. :UsePositionUpdate exported / logical This property indicates the method that will be used to update cursor rows. When .T., a positioned SQL expression will be used, otherwise a searched SQL expression will be generated. By default this property will always be true if the driver and cursor support positioned updates. A programmer may force searched expressions to be generated by setting this property to .F. Readonly Properties :RowsUpdated readonly / numeric Stores a count of the number of rows affected by the last row update operation. An update can be initiated by either executing the :UpdateRow() method or by moving the row pointer to another row. If the row update is successful but a count of the number of rows affected can not be determined, the value stored will be (-1). If the update fails, the value stored will be SQL_XPP_ERROR. :SortOrder exported / array of numeric values A numeric array indicating column positions by which result set is sorted. A result set can be sorted by including an 'ORDER BY' clause in the SQL SELECT statement. A sorted result set can be quickly searched with the :Seek() method.

SQLDataSet()

---

Manages static client side cursors. When instantiated, this class will open the fastest and least expensive (read-only, forward) server side cursor, retrieve the entire result set into memory and then close the server side cursor in order to reduce server resources. Once the result set is retrieved, this class provides very fast; forward and backward scrolling through the dataset and allows relations to be built between other SQLDataSet cursors that may be connected to different data sources.

SQLDataSet: Class Methods

:new( [xSQLSelect],[oConnection],[xSQLParam],[aPrimaryKey],[nMaxRows],[abAddCols],[bFilter],[lTrim],[lDateTimeAsDate]) -> self Creates a new instance of the SQLDataSet class used for managing a client-side cursor. When instantiated, this class will open the fastest and least expensive (read-only, forward) server-side cursor, retrieve the entire result set into memory and then close the server side cursor in order to reduce server resources. Once the result set is retrieved, this class provides very fast forward and backward scrolling through the dataset and allows relations to be built between other SQLDataSet objects that may be connected to different database servers. Parameters: xSQLSelect Can be specified as a SQL 'SELECT' string or a SQLSelect object. If this parameter is not provided, an empty dataset is returned that can then be populated ad-hoc by the programmer. When a 'SELECT' string is provided, a temporary cursor is created on the DBMS to fetch the result set to the client. Once the entire result set is fetched, the temporary server-side cursor is closed. oConnection A SQLConnection object, if not specified then an existing connection (if one exists on the current thread) will be used, otherwise the user will be prompted to connect to a data source. If an SQLSelect object is passed instead of a string for xSQLSelect, then this parameter is ignored. xSQLParam This parameter is used to specify the parameter(s) that will be used for executing the SQL 'SELECT' statement. If the SELECT statement does not contain parameter markers then xSQLParam should be NIL. A parameter marker is a variable in an SQL statement that is represented by a question mark (?), example:
SELECT RowID, * FROM Orders WHERE CustId=? AND DateReq=?

xSQLParam can be specified either as a simple datatype (in the case that there is only one parameter marker), as an array containing the parameter values, or as a codeblock which when evaluated returns either a simple datatype or an array of parameter values. aPrimaryKey An array of column names or column numbers used for generating unique keys. It is very important that the dataset contain all necessary fields for generating a unique key so that updates and deletes may be applied to the desired row. nMaxRows An optional numeric value specifying the maximum number of rows to read into the dataset. If specified and the the number of rows in the result set is greater than nMaxRows, the result set is truncated and :isTruncated will be set to .T. abAddCol An optional array of codeblocks that will be used to fill additional columns in the dataset set, example:
{ {|oDb,x| iif(x==nil, oDb:FieldGet("MyField"), oDb:FieldPut("MyField",x)) },... }

These columns can later be accessed by ordinal position, example: oDataset:FieldGet(4)
It is also possible to specify column names by passing an array of subarrays in the following format:
{"Column Name", {|o| o:FieldGet("MyField")}, example:
{ {'ColX', bBlockX}, {'ColY', bBlockY}, {'ColZ', bBlockZ},... } bFilter An optional codeblock used to filter out rows of data. If specified, the codeblock will be stored in the :CustomFilter instance variable and will later be evaluated for each row of data fetched from the data source. A temporary SQLSelect object is passed as a single parameter to this codeblock, if the codeblock returns .T., the current SQLSelect row is fetched otherwise the row is skipped. Example, fetch all rows where the IP_NUMBER matches an internal list of bad IP numbers:
{|oCursor| AScan(aBadIP, oCursor:FieldGet('IP_NUMBER')) > 0} lTrim An optional logical value which when set to .T. will cause columns of character data to be stored in a trimmed format. The advantage of doing this is that it reduces memory usage on the client machine. Fixed length fields are automatically padded to their correct length when the field is accessed using the :FieldGet() method. A potential problem however arises when variable length fields are used, since it is impossible to determine if a field had any trailing blank characters. If not specified, this parameter defaults to .F. (store character data exactly as returned by the data source). lDateTimeAsDate An optional logical value which when set to .T. will cause SQL datetime values (this does not include timestamps) to be converted to native Xbase++ date types. Since Xbase++ does not have a native datetime datatype, the default behaviour is to return datetime values as character strings with complete date and time portions intact. Setting this parameter to .T. will cause the time portion of a SQL datetime value to be lost in the converstion. Default is .F.

SQLDataSet: Object Methods

Life Cycle :AsyncPriority( [nPriority] ) -> nPriority | NIL Get or set execution priority of the background fetch data thread initiated by :ExecuteAsync(). The optional parameter nPriority can be specified in order to set the priority of the background thread. If not provided, the method returns the current priority. The method returns NIL if a background thread does not exist, or if the specified priority could not be set.

Constants for nPriority are defined in the Xbase++ THREAD.CH include file. Please refer to the Xbase++ documentation for additional information. :Cancel() -> lIsCancelled This method may be used by an external thread to signal the current thread to stop fetching data. When this happens the property :isCancelled will be set to .T. :ClearRelation() -> self Clears all relations previously defined with :SetRelation. :Close() -> .T. Clear out all data in current as well as child datasets linked through a relation. Use this method to release memory allocated to data without destroying the dataset object. :Commit() -> lSuccess Save changes to source table. The method returns .T. if all changes have been successfully applied to the physical table. :Destroy() -> self Same as :Close(). :Execute() -> nRowsFetched | -1 This method is provided to maintain compatibility with the SQLSelect class, it simply calls the :FetchData method. :ExecuteAsync() -> nSuccess Fetch data from server into client side cursor using a background thread. The method returns as soon as the background thread is started, allowing the main thread to continue and possibly display a partial result set to the user. By default, the background thread will run at a low priority, however this can be changed at any time using the :AsyncPriority method. Progress of the background thread may be monitored by other threads using the :RecCount() method. As more data is fetched, the RecCount will increase. Once the entire result set is fetched, the :isFinished property will be set to .T.

The method returns one of the following numeric return values:

1 - the DBMS has returned a non-empty result set.
0 - the DBMS has returned an empty result set.
-1 - no data has been fetched.
-2 - an error occurred while trying to generate a result set on the DBMS. Use :ErrorCode and :ErrorMessage to determine the cause of the error. :FetchData() -> nRowsFetched | -1 Fetch data from server into client side cursor. This method is called implicitly whenever the object detects that it's data needs to be refreshed, such as when SQLParameters change (in cases where SQLParameters are codeblocks). If no data was fetched, the method returns -1. :MoreResults() -> .F. This method is provided to maintain compatibility with the SQLSelect class. It does nothing. :Refresh() -> nRowsFetched | -1 Signal object to refetch data the next time a data access method is called or the row pointer is moved (:RecCount, :FieldGet, :Skip, :Eof,...). Data will only be refetched if the object internally detects that a refresh is needed, such as when SQLParameters change (in cases where SQLParameters are codeblocks). If no data was fetched, the method returns -1. To force a refresh of data, call the :FetchData method. :RefreshRow( oCursor, [lRemoveFromDataset] ) -> self Import a new row or refresh an existing row in the dataset from a passed SQLSelect cursor. The source row to be imported, removed or refreshed is the current row of the passed oCursor object. This method can be used to incrementally synchronize the dataset with the physical source table. In order to maintain a link between the dataset and it's source table, a primary key must be defined using the :SetPrimaryKey method.

If the target row already exists in the dataset, it will be removed from the dataset when: lRemoveFromDataset is .T., or the source row is marked for deletion, or the source row does not meet the :CustomFilter condition (if one is specified). Otherwise, the existing row in the dataset will be refreshed. Similarly a new row will only be imported when the source row successfully passes the :CustomFilter condition (if one is specified). :SetPrimaryKey( xColumn ) -> lSuccess Specify a column or array of columns to be used as a unique index in searched updates and deletes.
xColumn can be specified as a character string (column name), numeric (column position) or array containing column names or column positions. It is very important that the dataset contain all necessary fields for generating a unique key so that updates and deletes may be applied to the desired row. :SetRelation( oSQLDataSet ) -> self Adds a relation to a child SQLDataSet object. If the child dataset is properly defined using codeblocks for SQLParameters that point to the parent, then as the row pointer is moved in the parent object, the child object will be automatically refreshed as needed. Example: oHeader := SQLDataSet():new("SELECT * FROM `Orders` WHERE `Payment Received`=0 AND `Shipped`=1 ORDER BY `Required Date`", oConn)
oDetails:= SQLDataSet():new("SELECT * FROM `Orders Detail` WHERE `Order ID`=?", oConn, {||{oHeader:FieldGet('Order ID')}})
oHeader:SetRelation( oDetails )

while !oHeader:Eof()
   // print the order header
   ? "ORDER" + oHeader:RecNo + ": "
   for i := 1 to oHeader:fieldCount
      ?? oHeader:fieldGet(i), ', '
   next
   // print the details for this order
   while !oDetails:Eof()
      ? " DETAIL: "
      for i := 1 to oDetails:fieldCount
         ?? oDetails:fieldGet(i), ', '
      next
      oDetails:skip()
   end
   ?
   inkey(0)
   oHeader:skip()
end
:Sort( [axbOrder], [lAscending] ) -> self Sort the dataset. axbOrder designates the sort order and can be specified as a numeric for column index, a character for column name, an array of multiple column names/numbers, or a codeblock for custom sorting. The codeblock in this case is used in much the same way as when calling the native Xbase++ ASort() function. If not specified axbOrder defaults to :SortOrder. lAscending is an optional logical parameter controlling whether the dataset is sorted in ascending (.T., the default) or descending format (.F.). When axbOrder is provided as a codeblock, lAscending is not required and is ignored. Examples: oData:Sort('LastName') // sort dataset by the LastName column
oData:Sort({'LastName','FirstName','DOB'}) // sort dataset by multiple columns
oData:Sort({|x,y| x[2] > y[2]}) // sort dataset by column #2 in descending order Managing Columns :GetSQLColumn( xColumn ) -> oSQLColumn | NIL Returns a reference to the base SQLColumn object. xColumn can be specified as a numeric (column index) or a character (column name). The method returns NIL if the column does not exist. :AddColumn( cName, [bDataLink] ) -> self Add a column of data to dataset. cName is the name of the new column that will be used to reference the column using :FieldGet and :FieldPut. bDataLink is an optional codeblock that will be used to fill the column with data, if not specified the new column will be empty. :DelColumn( xColumn ) -> self Remove the specified column from the dataset. xColumn can be specified as a numeric (column index) or a character (column name). :DelEmptyColumns() -> self Remove all empty columns from the dataset. An empty column is one where all of it's data is empty as defined by the Xbase++ function Empty(). This method can be used to reduce the memory occupied by the dataset, it can be especially useful in creating efficient Internet applications that generate HTML pages on the fly. :IsColumnEmpty( xColumn ) -> lEmpty Determine if the specified column within the dataset is empty as defined by the Xbase++ function Empty(). xColumn can be specified as a numeric (column index) or a character (column name). :GetColumnData( xColumn ) -> aColumnData Return an entire column from the dataset as an array. The array returned will be one dimensional with a length equal to the number of rows in the dataset. xColumn can be specified as a numeric (column index) or a character (column name). Record Movement :Bof() -> lAtBof Returns .T. when the row pointer is at top of result set. :Eof() -> lAtEof Returns .T. when the row pointer is at bottom of result set. :GoTo( [nRow] ) -> lSuccess Position the row pointer on the specified row number. The method returns .T. if the operation was successful. :GoTop() -> self Position the row pointer on the first row of the result set. :GoBottom() -> self Position the row pointer on the last row of the result set. :RecCount() -> nRows Returns a count of the number of rows in the dataset. :RecNo() -> nRowNumber Pointer to current row position in dataset. :Skip( [nRowCount] ) -> lSuccess Move forward or backward relative to the current row. nRowCount specifies the number of rows to skip. Positive forward; negative numbers move backward. If omitted, +1 is assumed. The method returns .T. if the operation was successful. :Skipper( nSkip ) -> nSkipps This method can be used to create a TBrowse or XbpBrowse :skipBlock code block that is used to move the row pointer of the cursor. nSkip specifies the number of rows to skip. Positive forward; negative numbers move backward. The method returns the number of rows actually skipped. If the number returned is different from the number requested, then either the end or beginning of the dataset has been reached. Example: oBrowse:SkipBlock := {|n| oCursor:skipper(n)} :Locate( axbFind, axColumn, [lRest], [nCount] ) -> lFound Sequentially search for axbFind within the dataset. If a row matching the specified condition is found, the method returns .T. and the row pointer is positioned on this row, otherwise the row pointer is not moved.

axbFind can be specified as either a single value or an array of values representing the search expression. Alternatively, axbFind can be specified as a codeblock containing the search expression. When evaluated, the following two parameters are passed to this codeblock; (1) an array representing a row of data from the dataset and (2) the row number being evaluated.

When axbFind is provided as a codeblock, then axColumn is not required since the codeblock will control which columns are inspected. Otherwise, axColumn must be specified as either a single value or array of values representing the numeric (column indexes) or character (column names) to be searched.

The optional logical value lRest specifies whether all data rows are searched (the default), or only the rows from the current to the last row (lRest = .T.).

nCount is an optional numeric value indicating the number of rows searched, the default is all rows.
Examples: oData:Locate( "TT Roadster", "MODEL") // single value/field
oData:Locate( {"Convertible","Chrysler","Sebring"}, {"TYPE","MAKE","MODEL"}) // multiple values/fields
oData:Locate( {|a|a[2] >= a[3] .and. a[5] < date()} ) // custom codeblock

Warning: The simple equals operator (=) is used when performing comparisons. Due to this, the search of character strings is dependent on the settings SET EXACT, SET LEXICAL and SET COLLATION. :Seek( axValue, [axColumn], [lSoftSeek] ) -> lFound Perform a fast binary search for axValue within a sorted dataset. This method requires that the dataset is sorted by axColumn. If the dataset is not sorted on the desired column, use the :Locate method instead. A dataset can be sorted on the client-side by using the :Sort method or on the server-side by specifying an ORDER BY condition in the SQL SELECT statement.

If a row matching the specified condition is found, the method returns .T. and the row pointer is positioned on this row, otherwise the row pointer is not moved. lSoftSeek is an optional logical parameter controlling the position of the row pointer in cases where axValue is not found within the dataset. If this parameter is set to .T. and axValue is not found, the row pointer will be positioned on the first row whose value is greater than axValue. Alternatively, if this parameter is set to .F. (the default), the row pointer will not be moved from the current position. axValue can be specified as either a single value or an array of values representing the search expression. Examples: oData := oMyConn:DataSet("select * from products")
oData:Sort( {"Type","Make","Model"} )
oData:Seek( "Camera" ) // single value/field
oData:Seek( {"Camera","Contax","RTS"} ) // multiple values/fields

Warning: The simple equals operator (=) is used when performing comparisons. Due to this, the search of character strings is dependent on the settings SET EXACT, SET LEXICAL and SET COLLATION. Get/Set Data :Append( [xVal] ) -> self Append one or more rows of data to the dataset. The optional parameter xVal can be specified as follows (if not specified, one blank record is appended):

1. Another SQLDataSet object with same structure - all data from new object will be appended.
2. Array of records, example: { {record1}, {record2},... }
3. Array of data, example: { field1, field2, field3,... }
4. Numeric indicating the number of blank records to append.
:Insert( [nRow], [xVal] ) -> self Insert one or more rows of data into the dataset. If nRow is not specified, the new row is inserted at the current row position. The optional parameter xVal can be specified as follows (if not specified, one blank record is inserted):

1. Another SQLDataSet object with same structure - all data from new object will be inserted.
2. Array of records, example: { {record1}, {record2},... }
3. Array of data, example: { field1, field2, field3,... }
4. Numeric indicating the number of blank records to insert.
:Delete( [nRow] ) -> self Remove the specified row from the dataset. If nRow is not specified, the current row will be deleted. :Deleted() -> .F. Method always returns .F. since rows are physically removed from the dataset as soon as the :Delete() method is called. :isRowModified() -> lModified Indicates if current row in dataset has been modified. :UpdateRow() -> lSuccess This method is provided to maintain compatibility with the SQLSelect class, it simply calls the :Commit() method. :CancelUpdate( [nRow] ) -> self Undo changes made to a specific row within the dataset. If nRow is not not specified, the current row will be used. Note that changes can be undone only if the :UpdateRow() or :Commit() methods have not been executed. :FCount() -> nColumns Returns a count of the number of columns in the dataset. :FieldPos( xColumn ) -> nColumnIndex Return the position of a specified column within the result set. xColumn can be specified as a numeric (column index) or a character (column name). The method returns 0 if the specified column does not exist. :FieldName( xColumn ) -> cColumnName Return the name of the specified column. xColumn can be specified as a numeric (column index) or a character (column name). The method returns an empty string if the specified column does not exist. :FieldGet( xColumn, [nRow] ) -> xValue | NIL Retrieve the contents of the specified column. xColumn can be specified as a numeric (column index) or a character (column name). The optional parameter nRow may be provided in order to access a specific row number within the dataset. If not specified, the current row will be used. :isNull( xColumn, [nRow] ) -> TRUE | FALSE Returns .T. if the value for the specified column is NULL. xColumn can be specified as a numeric (column index) or a character (column name). The optional parameter nRow may be provided in order to access a specific row number within the dataset. If not specified, the current row will be used. :FastFieldGet( nColumn, [nRow] ) -> xValue | NIL This is a fast version of :FieldGet, it does not check if the dataset needs to be refreshed and only accepts a numeric column index for the field to be retrieved. The optional parameter nRow may be provided in order to access a specific row number within the dataset. If not specified, the current row will be used. :FieldPut( xColumn, xValue, [nRow] ) -> xValue | NIL Assign a value to the specified column within the result set. xColumn can be specified as a numeric (column index) or a character (column name). The optional parameter nRow may be provided in order to access a specific row number within the dataset. If not specified, the current row will be used. :RecordGet( [nRow] ) -> aRowValues | NIL Return an entire row from the dataset as an array. The optional parameter nRow may be provided in order to access a specific row number within the dataset. If not specified, the current row will be used. If the specified row does not exist, the method returns NIL. :RecordPut( aRowValues, [nRow] ) -> aRowValues | NIL Modify an entire row within the dataset with the contents of the passed array aRowValues. If nRow is not specified, the current row will be modified. If the specified row does not exist, the method returns NIL.

NOTE: The structure of aRowValues must correspond exactly to the structure of a dataset record.

SQLDataSet: Object Properties

Configurable Properties :BrowseFilter exported / codeblock This property can be used to define a codeblock filter condition that determines the logical visibility of rows in the current dataset. When the codeblock returns .T. (true), the current dataset row is visible. Dataset rows where the codeblock returns .F. (false), are not logically visible, and are skipped during all row positioning operations with the exception of :GoTo() which allows access to rows that do not match the filter condition.

After a filter condition has been assigned to :BrowseFilter, the row pointer must be moved at least once to guarantee that the current row matches the filter condition. This is generally done by a call to the :GoTop() method which positions the row pointer to the first dataset row matching the filter condition. When no matching rows are available, :RecNo() will return 0 (zero), and both :Bof() and :Eof() will return .T. (true). To clear the filter condition, set :BrowseFilter to NIL.

When evaluated three parameters are passed to the codeblock:
(1) an array element referencing the current dataset row,
(2) an integer numeric value representing the current row position within the dataset, and
(3) an object reference to the SQLDataSet object.

Warning: When a filter contains a character expression and the exact equals operator (==) is not used, then the filter condition is dependant on the settings of SET EXACT, SET LEXICAL and SET COLLATION.

Example: /* display only rows with the word "BIKE" in the customer name */
/* using :FieldGet() */
oDataSet:BrowseFilter := {|aRow,nRow,oData| "BIKE" $ upper(oData:FieldGet("customer name"))}

/* using direct array access (faster) */
nField := oDataSet:FieldPos("customer name")
oDataSet:BrowseFilter := {|aRow,nRow,oData| "BIKE" $ upper(aRow[nField])}
:Cargo exported / any data type Used to attach additional information to the object as required by the programmer. :CustomFilter exported / codeblock This property is used to define a codeblock that will filter out rows of data as they are fetched from the data source. A temporary SQLSelect object is passed as a single parameter to the codeblock, if the codeblock returns .T., the current SQLSelect row is fetched otherwise the row is skipped.

Warning: When a filter contains a character expression and the exact equals operator (==) is not used, then the filter condition is dependant on the settings of SET EXACT, SET LEXICAL and SET COLLATION.

Example: /* fetch all rows where the IP_NUMBER matches an internal list of bad IP numbers */
oDataSet:CustomFilter := {|oCursor| AScan(aBadIP, oCursor:FieldGet('IP_NUMBER')) > 0} :isCancelled exported / logical Indicates if query was cancelled from an external thread. :QueryTimeout exported / integer numeric Get/Set the number of seconds to wait for an SQL query to execute before returning to the application. If the value is equal to 0 (default), then there is no timeout. If the query takes longer than the specified number of seconds, the result set is truncated and :isTruncated will be set to .T. :MaxRows exported / numeric Get/Set the maximum number of rows to read into the dataset. If the value is NIL or less than zero, then the entire result set is retrieved, otherwise if the number of rows in the result set is greater than :MaxRows, the result set is truncated and :isTruncated will be set to .T. :OnError exported / codeblock Provides the ability to optionally specify a codeblock that will be evaluated when an ODBC and/or data source specific error occurs. When evaluated four parameters will be passed to the codeblock:
(1) a reference to the SQLExpress object responsible for the error,
(2) a complete text error string including call stack,
(3) the ODBC API function return code, and
(4) an array of one or more SQLError objects describing the error or diagnostic information.

Specifying an :OnError codeblock (including a NIL codeblock {||NIL}) will disable the default automatic display of error messages occurring on the target SQLDataSet object. Note that the SQLDataSet object will automatically inherit the :OnError property from it's parent connection object when initialized.

Example: oDataSet:onError := {|oOwner, cError, nRetCode, aErrObj| SQLErrorLog(cError), MsgBox(cError)} :SortOrder exported / array of numeric values A numeric array indicating column positions by which dataset set is sorted. A dataset can be sorted on the server-side by including an 'ORDER BY' clause in the SQL SELECT statement, or it can be sorted on the client-side by using the :Sort() method. A sorted dataset can be quickly searched by using the :Seek() method. :SQLParameters exported / any data type This property designates the parameter(s) that will be used for executing the SQL 'SELECT' query. If the SQL statement does not contain parameter markers then :SQLParameters should be NIL. A parameter marker is a variable in an SQL statement that is represented by a question mark (?), example:
SELECT RowID, * FROM Orders WHERE CustId=? AND DateReq=?

:SQLParameters can be represented either as a simple datatype (in the case that there is only one parameter marker), as an array containing the parameter values, or as a codeblock which when evaluated returns either a simple datatype or an array of parameter values. Readonly Properties :Connection readonly / SQLConnection object Reference to the parent SQLConnection object. :Cursor readonly / SQLSelect object Reference to the base SQLSelect object. :DateTimeAsDate readonly / logical When this property is .T. it indicates that the dataset may contain SQL datetime values (not including timestamps) that have been converted to native Xbase++ date types. In such a case, the time portion of a SQL datetime value will be lost in the conversion. When this property is .F., it indicates that SQL datetime values are represented as character strings with complete date and time information. :ErrorCode readonly / numeric Most recent result code returned by the ODBC driver. :ErrorMessage readonly / character Most recent warning or error message returned by the ODBC driver. :isFinished readonly / logical Indicates completion of background fetch data thread initiated by the :ExecuteAsync() method. :isTrimmed readonly / logical Indicates if character fields in dataset have been stored in a trimmed format in order to conserve memory. Fixed length fields are automatically padded to their correct length when the field is accessed using the :FieldGet() method. A potential problem however arises when variable length fields are used, since it is impossible to determine if a field had any trailing blank characters. This property can only be defined when the SQLDataSet object is instantiated, see :New(... [lTrim],...) :isTruncated readonly / logical Indicates if result set was truncated as a result of: being cancelled from an external thread, or :MaxRows exceeded, or :QueryTimeout expired. :RowsUpdated readonly / numeric Stores a count of the total number of rows affected by the last :Commit() operation. :SQLString readonly / character The SQL 'SELECT' string used to build the dataset.

SQLColumn()

---

Describe an SQL column from a SQLSelect result set.

SQLColumn: Object Methods

:String2Usual( cValue ) -> xValue Converts the specified character string value cValue to the appropriate datatype for this column.

SQLColumn: Object Properties

:AliasName exported / character Alias name specified for the current column. If an alias was not specified, then :AliasName will contain the name of the column. :BufferSize exported / numeric Specifies the size of buffer used to retrieve data for this column. Buffer sizes that are too small to accomodate long data will result in additional calls to the driver in order to retrieve the full data. Default value is: min(max(:displaySize, :length)+2, :SQLConnection:bufferSize) :Cargo exported / any data type Used to attach additional information to the object as required by the programmer. :CatalogName exported / character The catalog of the table that contains this column. If the data source does not support catalogs or the catalog name cannot be determined, then :CatalogName will contain an empty string :Decimals exported / numeric Number of decimal spaces in current column. :DefValue exported / any data type Default value for current column. :DisplaySize exported / numeric Maximum number of characters required to display data from this column. :Index exported / numeric Position of current column in the result set. :isAutoUnique exported / logical Indicates if the column contains auto generated unique values. :isNullable exported / logical Indicates if the column can have NULL values. :isUpdatable exported / logical Indicates if the column in the result set can be updated. The updatability of the column in the base table on which this result set column is based may be different. Whether a column is updatable can be based on the data type, user privileges, and the definition of the result set itself. :isSearchable exported / logical Indicates if the column can be used in an SQL 'WHERE' clause. :isSigned exported / logical Indicates if the column’s data type is a signed numeric. :Length exported / numeric Size of current column. :Name exported / character Name of current column. :SQLType exported / numeric Native SQL data type for current column. :TableName exported / character The name of the table that contains this column. If the table name cannot be determined, then :TableName will contain an empty string. :ValType readonly/ character Native Xbase++ data type for current column.

SQLValue()

---

Describe an SQL data value. Used to transfer data from native Xbase++ data types to native ODBC types as required by ODBC drivers.

SQLValue: Class Methods

:new( cValue, nSQLType, nCType, nPrecision, nScale, nBytes ) -> self Creates a new instance of the SQLValue class. The prime reason for using the SQLValue class is to override the default data conversions performed by SQLExpress. This may be necessary when an ODBC driver does not support certain ODBC types. In such cases an SQLValue object can be passed as a parameter (instead of a native Xbase++ datatype) when executing an SQL statement using SQLStatement:Execute() or when modifying a cursor using SQLSelect:FieldPut(). For additional information on SQL data types refer to the Microsoft ODBC API documentation.

SQLValue: Object Properties

:Bytes exported / numeric A numeric value indicating the number of bytes used to represent the SQL value. :CType exported / numeric A numeric value representing the C type of the SQL value (such as, SQL_C_CHAR, SQL_C_SLONG, SQL_C_FLOAT, SQL_C_TYPE_TIMESTAMP). :Precision exported / numeric Size of current column. Usualy the length of the binary SQL value. :Scale exported / numeric A numeric value representing the number of decimal places in the SQL value. If the SQL value is not a numeric then the :Scale should be set to 0 (zero). :SQLType exported / numeric A numeric value representing the SQL type identifier of the SQL value (such as, SQL_CHAR, SQL_INTEGER, SQL_FLOAT, SQL_TYPE_TIMESTAMP).
NOTE: Refer to your ODBC documentation for a complete list of ODBC data types that are available. :Value exported / character Native C value. In Xbase++ a native C value can usually be represented as a binary string, for example an Xbase++ integer numeric value can be converted to a native C 32-bit integer value using the L2Bin() function. :Cargo exported / any data type Used to attach additional information to the object as required by the programmer.

SQLError()

---

Used to manage diagnostic error, warning, and status information returned by the ODBC driver manager and/or data source.

SQLError: Object Properties

:Owner readonly / object A reference to the SQLExpress object responsible for generating error. :ReturnCode readonly / numeric Return code returned by the ODBC API function that resulted in the error object. The return code may be one of the following: SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE, SQL_NO_DATA, SQL_NEED_DATA, SQL_STILL_EXECUTING :SQLState readonly / character A five-character SQLSTATE code. The first two characters indicate the class; the next three indicate the subclass. For a complete list of SQLSTATEs, see ODBC Error Codes. :NativeError readonly / numeric A driver/data source-specific native error code. If there is no native error code, the driver returns 0. :Message readonly / character An informational message on the error or warning. Diagnostic messages come from data sources and components in an ODBC connection, such as drivers, gateways, and the Driver Manager. :Thread readonly / numeric The ID number of the Xbase++ thread in which an error occurred. :Cargo exported / any data type Used to attach additional information to the object as required by the programmer.

SQLCatalogQuery()   (abstract class, inherited from SQLSelect)

---

This is an abstract class that is inherited from SQLSelect and provides a base from which all SQLExpress catalog classes are subclassed.

SQLCatalogQuery: Class Methods

:new( [oConnection] ) -> self Creates a new instance of the SQLCatalogQuery class. If oConnection is not specified then an existing connection (if one exists on the current thread) will be used, otherwise the user will be prompted to connect to a data source. By default, the SQLCatalogQuery and all inherited classes create a cursor of type SQL_CURSOR_STATIC and a concurrency of type SQL_CONCUR_READ_ONLY.

SQLCatalogQuery: Object Methods

The following methods do not apply to the SQLCatalogQuery class and are therefore provided to override the inherited functionality of the SQLSelect class:

:Append()
:CancelUpdate()
:Delete()
:FieldPut()
:Prepare()
:SetPrimaryKey()
:UpdateRow()

SQLListColumnPrivileges()   (inherited from SQLCatalogQuery)

---

List the columns and associated privileges for a specified table.

SQLListColumnPrivileges: Class Methods

:new( [oConnection] ) -> self Creates a new instance of the SQLListColumnPrivileges class. If oConnection is not specified then an existing connection (if one exists on the current thread) will be used, otherwise the user will be prompted to connect to a data source.

SQLListColumnPrivileges: Object Methods

:execute( [cCatalogName], [cSchemaName], cTableName, [cColumnName] ) -> nSuccess Execute a catalog query to obtain a list of columns and associated privileges for the specified table. If the query fails, SQL_XPP_ERROR is returned. For a description of columns returned by this query, please consult your driver documentation or the Microsoft ODBC API under SQLColumnPrivileges. Parameters: cCatalogName Catalog name. If a driver supports names for some catalogs but not for others, such as when the driver retrieves data from different DBMSs, an empty string or NIL denotes those catalogs that do not have names. cCatalogName cannot contain a string search pattern.
cSchemaName Schema or owner name. If a driver supports schemas for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string or NIL denotes those tables that do not have schemas. cSchemaName cannot contain a string search pattern.
cTableName Table name. cTableName must be specified and cannot contain a string search pattern.
cColumnName Column name. cColumnName can include wildcard characters using wildcard matching patterns of the underlying DBMS. For maximum interoperability, only the percent (%) and underscore (_) standard SQL-92 wildcard characters should be assumed. An _ will match a single character and a % will match any sequence of 0 or more characters. If not specified, all columns are returned.

SQLListColumns()   (inherited from SQLCatalogQuery)

---

List the column names in specified tables.

SQLListColumns: Class Methods

:new( [oConnection] ) -> self Creates a new instance of the SQLListColumns class. If oConnection is not specified then an existing connection (if one exists on the current thread) will be used, otherwise the user will be prompted to connect to a data source.

SQLListColumns: Object Methods

:execute( [cCatalogName], [cSchemaName], [cTableName], [cColumnName] ) -> nSuccess Execute a catalog query to obtain a list of column names in specified tables. If the query fails, SQL_XPP_ERROR is returned. For a description of columns returned by this query, please consult your driver documentation or the Microsoft ODBC API under SQLColumns. Parameters: cCatalogName Catalog name. If a driver supports catalogs for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string or NIL denotes those catalogs that do not have names. cCatalogName cannot contain a string search pattern.
cSchemaName Search string pattern for schema or owner name. If a driver supports schemas for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string or NIL denotes those tables that do not have schemas.
cTableName Search string pattern for table names. If not specified, all tables are returned.
cColumnName Search string pattern for column names. If not specified, all columns are returned.

A search string can include wildcard characters using wildcard matching patterns of the underlying DBMS. For maximum interoperability, only the percent (%) and underscore (_) standard SQL-92 wildcard characters should be assumed. An _ will match a single character and a % will match any sequence of 0 or more characters.

SQLListForeignKeys()   (inherited from SQLCatalogQuery)

---

List the foreign keys in a specified table (columns in the specified table that refer to primary keys in other tables) or the foreign keys in other tables that refer to the primary key in the specified table.

SQLListForeignKeys: Class Methods

:new( [oConnection] ) -> self Creates a new instance of the SQLListForeignKeys class. If oConnection is not specified then an existing connection (if one exists on the current thread) will be used, otherwise the user will be prompted to connect to a data source.

SQLListForeignKeys: Object Methods

:execute( [cPKCatalogName], [cPKSchemaName], [cPKTableName], [cFKCatalogName], [cFKSchemaName], [cFKTableName] ) -> nSuccess Execute a catalog q