- Current row in the query
- Query columns
- Query without result
- Using tabulations in the items
- SQLCol and SQLGetCol functions
In french: SQLLitCol
Retrieves the content of the specified column from the query result, for the current line.
: This function must be used during a "SQLFetch/SQLGetCol" browse of the query result. For more details, see Types of SQL browse
i is int = 0
SQLExec("SELECT LASTNAME, FIRSTNAME, EXTENSION, PHOTO FROM CUSTOMER", "QRY1")
// Retrieve the query row by row
WHILE SQLFetch("QRY1") = 0 // There is another line to read
// Retrieve the data
LASTNAME[i] = SQLGetCol("QRY1", 1)
FIRSTNAME[i] = SQLGetCol("QRY1", 2)
EXTENSION[i] = SQLGetCol("QRY1", 3)
<Result> = SQLGetCol(<Query name> , <Column number>)
<Result>: Character string
Value of the column.
<Query name>: Character string
Name of the query created and executed with SQLExec, or executed with SQLExecWDR.
<Column number>: Integer
Number of the column to retrieve. This column corresponds to a query column (and not to a table column). If several columns must be retrieved, the subscript of the columns must be specified in ascending order. For example, you must do SQLCol("QRY1", 1) then SQLCol("QRY1", 2).
The current line is positioned by SQLFetch
- To read memo columns, use SQLGetMemo. To read the text memo columns, use SQLGetTextMemo.
- The number of columns found in the query result is returned by the SQL.NbCol variable (assigned by SQLInfo).
- If the column is a numeric column, SQLGetCol converts the result to a string. However, in WLanguage, you can assign the result returned by SQLGetCol in a numeric variable. WLanguage automatically performs the conversion.
- Columns have to be retrieved in ascending order.
- The same column cannot be retrieved several times in a row. Indeed, the second time, the result will be an empty string.
- SQLGetCol cannot be used to retrieve the binary strings.
SQLGetCol should not be called if the query did not return a result: the SQL.Out variable must be tested once the position has been set in the query result, before calling SQLGetCol.
// Retrieve the value of the 1st column for the 1st record
Value is string
QueryText is string
QueryText = "SELECT CUSTNAME, ADDR1, ADDR2, CITY, ZIP FROM INV"
Value = SQLGetCol("QRY1", 1)
Using tabulations in the items
The SQL functions are used to insert tabulations into the file items.
Example: SQLGetCol and tabs in items
The items of the records to retrieve are as follows:
|Item 1||Item 2|
|Record 2||John + tab + Mac||Doughnut|
For the first record:
- SQLGetCol(QRY, 1): retrieves John
- SQLGetCol(QRY, 2): retrieves Smith
For the second record:
- SQLGetCol(QRY,1 ): retrieves John + tab + Mac
- SQLGetCol(QRY, 2): retrieves Doughnut
SQLCol and SQLGetCol functions
are equivalent when accessing data via OLE DB or HFSQL. However, you must respect the type of browse associated with each one of these functions:
- SQLCol function: "SQLFirst/SQLCol" browse.
- SQLGetCol function: "SQLFetch/SQLGetCol" browse.
Remark: From version 19, HFSQL is the new name of HyperFileSQL.
This page is also available for…