SQLGetCol (Function)

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / WLanguage / Managing databases / SQL functions

Current row in the query
Query columns
Query without result
Using tabulations in the items
SQLCol and SQLGetCol functions
Retrieving Float items on Oracle (via ODBC)

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

SQLGetCol (Function)

In french: SQLLitCol

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

Warning This function must be used when browsing the result of a query of type "SQLFetch/SQLGetCol". For more details, see Types of SQL browse.

The SQL functions are used to handle the local databases (such as Web SQL databases). Only the SQLFetch/SQLGetCol browse mode is available. For more details, see Accessing a database in local mode (SQLite).

Example

i is int = 0
SQLExec("SELECT NOM, PRENOM, POSTE, PHOTO FROM CLIENT", "REQ1")
// Récupérer la requête ligne à ligne
WHILE SQLFetch("REQ1") = 0	// Il y a encore une ligne à lire
	i++
	// Récupérer les données
	NOM[i] = SQLGetCol("REQ1", 1)
	PRENOM[i] = SQLGetCol("REQ1", 2)
	POSTE[i] = SQLGetCol("REQ1", 3)
END
SQLClose("REQ1")

Syntax

<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.
The queries created in the query editor must be specified in the format of a character string.
Only the queries created and run by SQLExec are available.

<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 index of the columns must be specified in ascending order. For example, you must do SQLCol("QRY1", 1) then SQLCol("QRY1", 2).
Columns can be retrieved in any order.

Remarks

Current row in the query

The current line is positioned by SQLFetch.

Query columns

To read memo columns, use SQLGetMemo. To read the text memo columns, use SQLGetTextMemo.
SQLGetMemo and SQLGetTextMemo are useless.
The memo columns cannot be read.
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.
Columns can be retrieved in any order.
The same column cannot be retrieved several times in a row. The second time it is retrieved, the result is an empty string.
The same column can be retrieved several times in a row.
SQLGetCol doesn't get binary strings.

Query without result

The function SQLGetCol FUNCTION function must not be called if the query has not returned any results: you must test the SQL.Outside variable after positioning yourself in the query result and before calling the SQLGetCol FUNCTION.

For example:

// Récupération de la valeur de la 1ère colonne du 1er enregistrement
 Valeur is string
 TexteRequête is string
 TexteRequête = "SELECT NOMCLI, ADR1, ADR2, VILLE, CP FROM FACT"
 SQLExec(TexteRequête, "REQ1")
 Valeur = SQLGetCol("REQ1", 1)

Using tabulations in the items

The SQL functions are used to insert tabulations into the file items.

To get the value of the items (with Tabs), use SQLFetch/SQLGetCol.

Example: SQLGetCol and tabs in 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:

SQLGetCol(REQ, 1): retrieves John
SQLGetCol(REQ, 2): retrieves Smith

For the second record:

SQLGetCol(REQ,1 ): retrieves John + tab + Mac
SQLGetCol(REQ, 2): retrieves Doughnut

SQLCol and SQLGetCol functions

SQLCol and SQLGetCol 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:

Function SQLCol: "SQLFirst/SQLCol" type path.
Function SQLGetCol: type path "SQLAvance/SQLLitCol".

For more details, see Types of SQL browse.

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:

// Connexion à la base de données Oracle 
SQLConnect("MaBaseOracle", "User", "Passe", "", "ODBC") 
// Requête pour changer le séparateur décimal 
SQLExec("ALTER SESSION SET NLS_NUMERIC_CHARACTERS ='. '", "ReqTemp")

Component: wd300hf.dll