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

SQLCol and SQLGetCol functions: different path types
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

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

SQLCol (Function)

In french: SQLCol

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 SQLFirst/SQLCol query result.. For more details, see Types of SQL browse.

Example

ResExec = SQLExec("SELECT NOMCLI FROM FACT", "REQ1")
IF ResExec = True THEN
	SQLFirst("REQ1")
	// Transfert du nom dans LISTE_NOM
	WHILE NOT SQL.Out
		ListAdd(LISTE_NOM, SQLCol("REQ1", 1))
		SQLNext("REQ1")
	END
ELSE
	// TraiteErreur
END
SQLClose("REQ1")

Syntax

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

<Result>: Character string

Value of the column.

<Query name>: Character string

Name of the query:
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 path types

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:

Function SQLCol: "SQLPremier/SQLCol" type path.
Function SQLGetCol: "SQLFetch/SQLGetCol" type path.

For more details, see Types of SQL browse.

Current row in the query

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

Query without result

The function SQLCol FUNCTION function must not be called if the query has not returned any results: you must test the variable SQL.Outside variable after positioning yourself in the query result and before calling function SQLCol 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 = SQLCol("REQ1", 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.

Case 1: Executing an ODBC query:

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(Req1, 1): recovers John
SQLCol(Req1, 2): recovers Smith

For the second record:

SQLCol(REQ,1 ): recovers John
SQLCol(REQ, 2): retrieves Mac, but Mac does not match the value in the second field
SQLCol(REQ, 3): retrieves Doughnut, value of second item

Case 2: Executing 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:

// 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")

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 SQLServer, ORACLE, AS400, SyBase connectors, and access to HFSQL Client/Server or HFSQL Classic data: spaces are automatically removed at the end of the section.
Native Access, MySQl, MariaDB, Informix, DB2, Progress, PostgreSQL, SQLite, ODBC or OleDB connectors: trailing spaces are retained.

Tip To avoid keeping spaces on the right in all cases, it is possible to override the function by declaring a global procedure:

PROCEDURE SQLCol(source, indice)
RETURN NoSpace(WL.SQLCol(source, indice), sscRight)

Component: wd300hf.dll