PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • SQLCol and SQLGetCol functions: different types of browse
  • Current line in the query
  • Query without result
  • Using tabulations in the items
  • Retrieving Float items on Oracle (via ODBC)
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
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. See Types of SQL browse for more details.
Versions 19 and later
Android Widget This function is now available in Android Widget mode (accessing the external databases via a Webservice).
Android This function is now available for the Android applications (accessing the external databases via a Webservice).
iPhone/iPad This function is now available for the iPhone/iPad applications (accessing the external databases via a Webservice).
New in version 19
Android Widget This function is now available in Android Widget mode (accessing the external databases via a Webservice).
Android This function is now available for the Android applications (accessing the external databases via a Webservice).
iPhone/iPad This function is now available for the iPhone/iPad applications (accessing the external databases via a Webservice).
Android Widget This function is now available in Android Widget mode (accessing the external databases via a Webservice).
Android This function is now available for the Android applications (accessing the external databases via a Webservice).
iPhone/iPad This function is now available for the iPhone/iPad applications (accessing the external databases via a Webservice).
Versions 21 and later
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
New in version 21
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
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 (with or without quotes)
Query Name:
  • created and run by SQLExec (with quotes),
  • run by SQLExecWDR (with or without quotes).
<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: "SQLFirst/SQLCol" browse
  • SQLGetCol: "SQLFetch/SQLGetCol" browse
See Types of SQL browse for more details.
Note: From version 19, HFSQL is the new name of HyperFileSQL.

Current line 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 must not be called if the query returned no result: the test of the SQL.Out variable must be run when you are positioned in the query result and before you call 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 running a query in ODBC, the tabulations are not retrieved by SQLCol. To retrieve the value of items (with TAB characters), we recommend that you use a SQLFetch/SQLGetCol browse.
  • 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 a SQLFetch/SQLGetCol browse.
Example: SQLAssociate and the tabulations in the items
The items of the records to retrieve are as follows:
Item 1Item 2
Record 1JohnSmith
Record 2John + tab + MacDoughnut
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 by OLEDB, HFSQL or Native Access
The tabulations found in the result items are automatically retrieved by SQLCol.
WINDEVWEBDEV - Server codeReports and QueriesPHPAjaxODBC

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")
Components
WINDEVWEBDEV - Server codeReports and Queries wd230hf.dll
Java wd230java.jar
Linux wd230hf.so
Android wd230android.jar
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment