Click or drag to resize

SQLSelect Class

X#
Manage a SQL select statement to select a set of rows from an ODBC data source. The rows can be read, modified, or deleted using the SQLSelect methods.
Inheritance Hierarchy
Object
  DataServer
    SQLSelect
      SQLCatalogQuery
      SQLTable

Namespace:  VO
Assembly:  VOSQLClasses (in VOSQLClasses.dll) Version: 2.19
Syntax
 CLASS SQLSelect INHERIT DataServer
Request Example View Source

The SQLSelect type exposes the following members.

Constructors
  NameDescription
Public methodSQLSelect
Construct a SQL select object.
Top
Properties
  NameDescription
Public propertyAppendData
Public propertyAppendFlag
**Missing documentation **
Public propertyBoF
A logical value indicating whether the server is positioned at the beginning of the result set, on the first row.
(Overrides BoF.)
Public propertyBoundedColumns
**Missing documentation **
Public propertyClients
**Missing documentation **
(Inherited from DataServer.)
Public propertyConcurrencyControl
A constant, identifying the mode of automatic concurrency control for this data server, determining when and how records are locked and released:
(Inherited from DataServer.)
Public propertyConnection
The SQL connection object that the SQLSelect is using to manipulate the result set.
Public propertyCursorName
A string containing the cursor name.
Public propertyCursorType
A constant, which describes the cursor type used by the SQL statement:
Public propertyDBStruct
An array containing the structure of this data server.
(Overrides DBStruct.)
Public propertyDeleted
A logical value indicating whether the current row has been deleted.
Public propertyEoF
A logical value indicating whether the server is positioned at the end of the result set, after the last row.
(Overrides EoF.)
Public propertyErrInfo
An SQLErrorInformation object.
Public propertyFCount
The number of columns in the result set. A value of -1 indicates an error.
(Overrides FCount.)
Public propertyFOUND
A logical value indicating whether the previous search operation succeeded.
Public propertyHLStatus
**Missing documentation **
(Inherited from DataServer.)
Public propertyCode exampleHyperLabel
The hyperlabel connected to this data server. From the hyperlabel, you can retrieve additional information about the data server
(Inherited from DataServer.)
Public propertyIndexColumns
Public propertyLASTREC
**Missing documentation **
Public propertyModified
Public propertyMoreResults
A logical value representing whether there are more rows to retrieve.
Public propertyName
A string representing the name of this data server.
(Inherited from DataServer.)
Public propertyNameSym
A symbol representing the name of this data server.
(Inherited from DataServer.)
Public propertyNativeSQL
A string containing the native SQL text of the statement. Level 2
Public propertyNullAsBlank
A logical value determining whether SQL NULL values are strongly typed (TRUE) or not (FALSE, default value).
Public propertyNumCols
Public propertyNumParameters
A numeric value containing the number of parameters. A value of -1 indicates an error. Level 2
Public propertyNumResultColumns
A numeric value containing the number of columns in the result set.
Public propertyNumSuccessfulRows
A numeric value containing the number of rows that were modified by a delete, append, or update operation.
Public propertyPrepFlag
A logical value that indicates if the select statement has been prepared.
Public propertyRecCount
A numeric value representing the number of rows in the data server.
(Overrides RecCount.)
Public propertyCode exampleRecno
A numeric value representing the relative position in the data server.
(Overrides RecNo.)
Public propertyRecNo
A numeric value representing the current record number, identifying the position of the record pointer in this data server.
(Inherited from DataServer.)
Public propertyRowCount
Public propertyScrollConcurrency
A constant, which describes the concurrency mode used by the SQL statement:
Public propertyScrollUpdateType
A constant, which describes the type of SQL statement to use during append, update or delete operations.
Public propertySimulateCursor
A constant, which describes the way the ODBC drivers tries to guarantee that update or delete statements only affect one single row.
Public propertySQLColumns
**Missing documentation **
Public propertySqlData
**Missing documentation **
Public propertySQLString
A string representing the SQL statement text.
Public propertyStatement
A SQL statement object.
Public propertyStatementHandle
A numeric value representing the statement handle.
Public propertyCode exampleStatus
A hyperlabel object identifying the status after the last operation.
(Overrides Status.)
Public propertyTableName
A string representing the name of the table.
Public propertyTimeStampAsDate
**Missing documentation **
Public propertyUsed
A logical value indicating whether the current query is active. It returns TRUE if the current query is active; otherwise, it returns FALSE.
Top
Methods
  NameDescription
Public methodAddDateVal
Public methodAddTimeString
Public methodCode exampleAppend
Create a blank row in the buffer; this blank row becomes the current position and is ready for assignment of data values. The SQLSelect:RecNo of this buffer is SQLSelect:RecCount+1.
(Overrides Append.)
Public methodAppendRow
Insert the row that has been created in the buffer since the last SQLSelect:Append() operation.
Public methodAsString
Return the descriptive label defined for this data server.
(Inherited from DataServer.)
Public methodBindColumn
Public methodClose
Close the cursor corresponding to the server. Note that the database connection is not closed, only the cursor.
(Overrides Close.)
Public methodColumn
Return a specified SQL column.
Public methodColumnAttributes
Return the SQL column attributes for a specified column.
Public methodCommit
Save the changes (updates and deletes) made since the last Commit().
(Overrides Commit.)
Public methodDataField
Return the data (DataField object) at a specified column position.
(Overrides DataField(Usual).)
Public methodDelete
Delete the current row.
(Overrides Delete.)
Public methodDirectSkip
Public methodError
Provide a method for handling error conditions raised during database processing. This is an event handler: it is automatically called by other methods; the developer does not normally need to call the error method, but might want to replace or amend it.
Public methodCode exampleExecute
Open the cursor.
Public methodExtendedFetch
Provide extended fetch capabilities, specifying type and row.
Public methodFetch
Fetch the next row.
Public methodFIELDGET
Retrieve the contents of the specified column.
(Overrides FIELDGET(Usual).)
Public methodFieldGetFormatted
Retrieve the contents of the specified column according to the formatting specifications of its field specification.
(Overrides FieldGetFormatted(Usual).)
Public methodFieldHyperLabel
Return the hyperlabel of a specified column.
(Overrides FieldHyperLabel(Usual).)
Public methodFieldInfo
Retrieve information about or set an alias for the indicated column.
Public methodFieldName
Return the name of a specified column. Note that the column's hyperlabel also contains a name.
(Overrides FieldName(Usual).)
Public methodFieldPos
Return the position of a specified column within the data server, as a number starting with 1.
(Overrides FieldPos(Usual).)
Public methodFIELDPUT
Assign a value to a column in a data server.
(Overrides FIELDPUT(Usual, Usual).)
Public methodFieldSpec
Return the field specification object for the indicated column.
(Overrides FieldSpec(Usual).)
Public methodFieldStatus
Return a hyperlabel object identifying the status after the last operation.
(Overrides FieldStatus(Usual).)
Public methodFieldSym
Return the symbolic name of a specified column. Note that the column's hyperlabel also contains a name; these two names are the same in the code generated by the SQL Editor.
(Overrides FieldSym(Usual).)
Public methodFieldValidate
Validate the provided value according to the validation rules of the indicated column's FieldSpec.
(Overrides FieldValidate(Usual, Usual).)
Public methodFLOCK
This method is provided to override the inherited functionality from the DataServer class, as it does not apply to SQLSelect.
(Overrides FLOCK.)
Public methodFreeStmt
Free the statement, according to the specified option.
Public methodGetData
Retrieve the contents of the specified column.
Public methodGetdateVal
Public methodCode exampleGetLookupTable
Retrieve a two-column array of values from two fields in the server.
Public methodGetStatementOption
Retrieve the current setting for a statement option.
Public methodGetTimeStamp
Public methodGetTimeString
Public methodCode exampleGoBottom
Position the data server on the last row.
(Overrides GoBottom.)
Public methodGoTo
Position the data server on the specified row number.
(Overrides GoTo(Usual).)
Public methodCode exampleGoTop
Position the data server on the first row.
(Overrides GoTop.)
Public methodNoIVarGet
Provide a general error interception that is automatically called (in any class) whenever an access reference is made to a non-existent exported instance variable. In the SQLSelect class, it is used to implement the virtual column variable.
Important! NoIVarGet() should not be called directly; it is called by the system for handling invalid references.
(Overrides NoIVarGet(Usual).)
Public methodNoIVarPut
Provide a general error interception that is automatically called (in any class) whenever an assignment reference is made to a non-existent exported instance variable.
Important! NoIVarPut() should not be called directly; it is called by the system for handling invalid references.
(Overrides NoIVarPut(Usual, Usual).)
Public methodNotify
Provide a method that is automatically called by the action methods of the server after completion of some significant event: a column has changed, the server position has changed, some operation has been completed. Its standard implementation is to broadcast the same notification to its attached clients.
(Overrides Notify(Usual, Usual).)
Public methodNumResultCols
Return the number of columns in the result set.
Public methodPostInit
Implement customized initialization code for the window.
(Inherited from DataServer.)
Public methodCode examplePreExecute
Return the SQL command that is generated by X#.
Public methodPreInit
Implement customized initialization code for the server.
(Inherited from DataServer.)
Public methodPrepare
Prepare the statement.
Public methodRefresh
Refresh the current row in the buffer, discarding any changes that have been made.
Public methodRegisterClient
Register an object as a client of this data server, so that it receives notification messages from the server about certain actions.
(Inherited from DataServer.)
Public methodReReadRow
Public methodResetCursor
Public methodResetNotification
Resume the broadcasting of Notify messages to the server's attached clients (after SQLSelect:SuspendNotification() has been called).
(Overrides ResetNotification.)
Public methodRLOCK
This method is provided to override the inherited functionality from the DataServer class, as it does not apply to SQLSelect.
(Overrides RLOCK(Usual).)
Public methodRLockVerify
This method is provided to override the inherited functionality from the DataServer class, as it does not apply to SQLSelect.
(Overrides RLockVerify.)
Public methodRollback (Overrides Rollback.)
Public methodSeek
Move to the next record having a specified key value in the controlling order.
(Inherited from DataServer.)
Public methodSetColumnAttributes
Public methodSetDataField (Overrides SetDataField(Usual, Usual).)
Public methodSetPos
Public methodSetPrimaryKey
Specify a column to be used as a unique index in searched updates or deletes that require a key (i.e., SQLSelect:ScrollUpdateType = SQL_SC_UPD_KEY).
Public methodSetStatementOption
Change a statement option.
Public methodSetTimeStamp
Public methodSkip
Move forward or backward relative to the current row. (Similar to the Skip() function).
(Overrides Skip(Usual).)
Public methodSuspendNotification
Suspend the broadcasting of Notify messages to the server's attached clients.
(Overrides SuspendNotification.)
Public methodUnlock
This method is provided to override the inherited functionality from the DataServer class, as it does not apply to SQLSelect.
Public methodUnLock
Remove all locks held on this data server.
(Inherited from DataServer.)
Public methodUnRegisterClient
Remove the registration of an object as a client of this data server, so that it no longer receives notification messages from the server about certain actions.
(Inherited from DataServer.)
Public methodUpdate
Update this server with data from another server or table.
(Inherited from DataServer.)
Public methodUpdate(Usual)
Update the current row or restore the original values of the row.
Public methodUpdateCursor
Public methodUpdateKey
Public methodUpdateVal
Top
Remarks
Once a SQL table is opened in a DataServer object, it can be manipulated through the methods of the SQLSelect class:
X#
1oCust:= SQLSelect{"SELECT * FROM customer"}
2oCust:Skip(3)
3? oCust:CustName
4oCust:Delete()
5oCust:Close()
The model for SQL returns rows and columns as a result set, instead of records and fields. The columns can be considered as exported instance variables of the object. They can also be accessed through the FieldGet() and FieldPut() methods, with the column identified through a symbol, a string, or a number:
X#
1oCust := SQLSelect{"SELECT * FROM customer"}
2oCust:CustNo := 1234
3? oCust:CustNo
4oCust:FieldPut(#CustNo, 1234)
5oCust:FieldPut("CustNo", 1234)
6oCust:FieldPut(1, 1234)
7? oCust:FieldGet(#CustNo)
8? oCust:FieldGet("CustNo")
9? oCust:FieldGet(1)
(The columns are, of course, not real exported variables of the SQLSelect class. See "Objects, Classes, and Methods" in the Programmer's Guide for a discussion of how these "virtual" exported variables are created using the NoIVarGet() and NoIVarPut() methods.) Unlike the DBServer class, the SQLSelect class does not position the server on the first row when an object is created. However, when a method that references the first row is called, it will automatically be positioned on the first row. For databases that support positioned operations (such as, WATCOM) using scrollable cursors, the positioning methods (GoTop(), GoBottom(), Skip(), GoTo()) use the driver. Otherwise, SQLSelect buffers the rows as it moves forward and performs the positioning methods by moving within the buffer (for backwards positioning). Refer to the SQLConnection:ScrollCsr access for additional information. To disable this buffering, use the SQLSelect:BufferData() method. It is not possible to use the SQLSelect class to create a database table; the table must exist before a server can be opened on it.
Tip Tip
The cursor type and lock mode that is used by a SQLSelect object can be controlled by the following functions. Please consult the documentation for the ODBC driver you are using to see the effects of this setting on the performance of your application. SqlSetStmtConcurrency(), SqlSetStmtCursorType(), SqlSetStmtSimulateCursor()
See Also

Reference