- Retrieving the value of a column
- Transferring the content of the query into a table
- Retrieving Float items on Oracle (via ODBC)
- Using tabulations in the items
In french: SQLAssocie
Automatically associates each column of the query result with a control or with a variable of the application. Once SQLAssociate
has been executed, it is necessary to loop through the query result: the assignment of the controls or variables will be performed for each record read.
: This function must be used during a "SQLFirst/SQLCol" browse. For more details on the different modes to loop through a query result, see Types of SQL browse
ResExec is boolean
ResExec = SQLExec("SELECT CUSTNAME,ADDR1,ADDR2,CITY,ZC FROM INV", "QRY2")
IF ResExec = True THEN
SQLAssociate("QRY2", CustName, Addr, Addr, City, ZipCode)
// Processes the error
SQLAssociate(<Query name> , <List of operators>)
<Query name>: Character string
Name of the query created and executed with SQLExec (the name of the query must be enclosed in quotation marks) or executed with SQLExecWDR (with or without quotation marks).
Only the queries created and run by SQLExec
can be used in this version.
<List of operators>: Character strings, separated by commas
List of control names or variable names that will be automatically associated with the corresponding columns of the query (up to 250). These operators are separated by commas. The order of these operators must correspond to the order of the query columns.
Retrieving the value of a column
The value of a column can be retrieved individually by SQLCol
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")
Using tabulations in the items
The SQL functions are used to insert tabulations into the items of the data files. The management of tabulations depends on the access mode used.
1st case: Executing a query with ODBC:
When executing a query with ODBC, SQLAssociate
does not return tabs. To get the value of the items (with Tabs), 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 2||John + tab + Mac||Doughnut|
For the first record:
- SQLAssociate(Qry1, Var1, Var2): retrieves John and Smith
For the second record:
- SQLAssociate(Qry1, Var1, Var2): retrieves John and Mac, but Mac does not correspond to the value of the second item
- SQLAssociate(Qry1, Var1, Var2, Var3): retrieves John, Mac, Doughnut
2nd case: Executing a query with OLEDB, HFSQL or a Native Connector
SQLAssociate automatically retrieves the tabs in the result items.
This page is also available for…