SQLCol (Function)

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

Help

WLanguage

Managing databases

SQL functions

SQLCol

Presentation

Example

SQLCol and SQLGetCol functions: different types of browse

Current row in the query

Query without result

Using tabulations in the items

Retrieving Float items on Oracle (via ODBC)

Managing spaces at the end of text items

See also

SQLCol (Function)

In french: SQLCol

Not available

Retrieves the content of the specified column from the query result, for the current line.

Caution: This function must be used during a SQLFirst/SQLCol browse of the query result. For more details, see Types of SQL browse.

Example

ResExec = SQLExec("SELECT CUSTNAME FROM INVOICE", "QRY1")
IF ResExec = True THEN
        SQLFirst("QRY1")
        // Transfer the name into LIST_NAME
        WHILE NOT SQL.Out
                ListAdd(LIST_NAME, SQLCol("QRY1", 1))
                SQLNext("QRY1")
        END
ELSE
        // ProcessError
END
SQLClose("QRY1")

Syntax

<Result> = SQLCol(<Query name> , <Column number>)

<Result>: Character string

Value of the column.

<Query name>: Character string

Query name:
created and run by SQLExec,
run by SQLExecWDR.

<Column number>: Integer

Number of the column to retrieve. This column corresponds to a query column (and not to a table column). The order of the numbers for the columns to retrieve is not important.

Remarks

SQLCol and SQLGetCol functions: different types of browse

SQLCol and SQLGetCol are equivalent during an OLE DB or HFSQL access. 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.

For more details, see Types of SQL browse.

Current row in the query

To position on a line of the query, use one of the following functions: SQLFirst, SQLNext, SQLPrevious, SQLLast or SQLSeek.

Query without result

SQLCol 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 SQLCol.

For example:

// 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"
SQLExec(QueryText, "QRY1")
Value = SQLCol("QRY1", 1)

Using tabulations in the items

The SQL functions are used to insert tabulations into the file items. The management of tabulations depends on the access mode used.

1st case: Running a query in ODBC:

When executing a query with ODBC, SQLCol does not return tabs. To get the value of the items (with Tabs), use SQLFetch/SQLGetCol.
When running a query in ODBC, the space characters found at the end of the value of items are removed. To keep all the space characters, we recommend that you use SQLFetch/SQLGetCol.

Example: SQLAssociate and the tabulations in the items

The items of the records to retrieve are as follows:


	Item 1	Item 2
Record 1	John	Smith
Record 2	John + tab + Mac	Doughnut

For the first record:

SQLCol(Qry1, 1): retrieves John
SQLCol(Qry1, 2): retrieves Smith

For the second record:

SQLCol(QRY,1 ): retrieves John
SQLCol(QRY, 2): retrieves Mac, but Mac does not correspond to the value of the second item
SQLCol(QRY, 3): retrieves Doughnut, value of the second item

2nd case: Running a query via OLEDB, HFSQL or Native Connector
SQLCol automatically retrieves the tabs in the result items.

Retrieving Float items on Oracle (via ODBC)

By default, the decimal separator used for the Float items on Oracle is the dot. However, the ODBC driver returns the value by using the comma as decimal separator. The decimal places are lost when the value is assigned to a numeric control.

To avoid this problem, you must configure the decimal separator for the current connection:

// Connection to the Oracle database
SQLConnect("MyOracleDatabase", "User", "Pass", "", "ODBC")
// Request for changing the decimal separator
SQLExec("ALTER SESSION SET NLS_NUMERIC_CHARACTERS ='. '", "TempQry")

Managing spaces at the end of text items

The management of spaces at the end of the retrieved items depends on the access mode used:

Native Connectors for SQLServer, ORACLE, AS400, SyBase; and access to HFSQL Client/Server or HFSQL Classic data: spaces at the end of the item are automatically removed.
Native Connectors for Access, MySQl, MariaDB, Informix, DB2, Progress, PostgreSQL, SQLite, ODBC or OleDB: the spaces at the end of the item are kept.

Tip: In order not to keep the spaces on the right in all cases, it is possible to override the function by declaring a global procedure:

PROCEDURE SQLCol(source, index)
RESULT NoSpace(WL.SQLCol(source, index), sscRight)

Component: wd270hf.dll