PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Current line 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
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 "SQLFetch/SQLGetCol" browse of the query result. See Types of SQL browse for more details.
Versions 16 and later
WEBDEV - Browser code This function is now available in Browser code.
WEBDEV - Browser code The SQL functions are used to handle the local databases (such as Web SQL databases). Only the SQLFetch/SQLGetCol browse mode is available. See Accessing a database in local mode (SQLite) for more details.
New in version 16
WEBDEV - Browser code This function is now available in Browser code.
WEBDEV - Browser code The SQL functions are used to handle the local databases (such as Web SQL databases). Only the SQLFetch/SQLGetCol browse mode is available. See Accessing a database in local mode (SQLite) for more details.
WEBDEV - Browser code This function is now available in Browser code.
WEBDEV - Browser code The SQL functions are used to handle the local databases (such as Web SQL databases). Only the SQLFetch/SQLGetCol browse mode is available. See Accessing a database in local mode (SQLite) 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
i is int = 0
SQLExec("SELECT LASTNAME, FIRSTNAME, EXTENSION, PHOTO FROM CUSTOMER", "QRY1")
// Retrieve the query line by line
WHILE SQLFetch("QRY1") = 0        // There is another line to read
        i++
        // Retrieve the data
        LASTNAME[i] = SQLGetCol("QRY1", 1)
        FIRSTNAME[i] = SQLGetCol("QRY1", 2)
        EXTENSION[i] = SQLGetCol("QRY1", 3)
END
SQLClose("QRY1")
Syntax
<Result> = SQLGetCol(<Query Name> , <Column Number>)
<Result>: Character string
Value of the column.
<Query Name>: Character string (with or without quotes)
Name of query created and run by SQLExec (with quotes) or run by SQLExecWDR (with or without quotes).
Java The queries created in the query editor must be specified in the format of a character string with quotes.
Versions 16 and later
WEBDEV - Browser code Only the queries created and run by SQLExec are available.
New in version 16
WEBDEV - Browser code Only the queries created and run by SQLExec are available.
WEBDEV - Browser code 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 subscript of the columns must be specified in ascending order. For example, you must do SQLCol("QRY1", 1) then SQLCol("QRY1", 2).
WEBDEV - Browser codeJava The columns can be retrieved in any order.
Remarks

Current line in the query

The current line is positioned by SQLFetch.

Query columns

  • To read the memo columns, use SQLGetMemo. To read the Text Memo columns, use SQLGetTextMemo.
    PHP SQLGetMemo and SQLGetTextMemo are useless.
    Versions 16 and later
    WEBDEV - Browser code The memo columns cannot be read.
    New in version 16
    WEBDEV - Browser code The memo columns cannot be read.
    WEBDEV - Browser code 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 into a character string. However, the WLanguage allows you to assign the result returned by SQLGetCol into a numeric variable. The conversion is automatically performed by the WLanguage.
  • The columns must necessarily be retrieved in order of ascending number.
    WEBDEV - Browser codeJava The columns can be retrieved in any order.
  • The same column cannot be retrieved several times in a row. Indeed, the second time, the result will be an empty string.
    Versions 16 and later
    WEBDEV - Browser code The same column can be retrieved several times in a row.
    New in version 16
    WEBDEV - Browser code The same column can be retrieved several times in a row.
    WEBDEV - Browser code The same column can be retrieved several times in a row.
  • SQLGetCol cannot be used to retrieve the binary strings.

Query without result

SQLGetCol 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 SQLGetCol.
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 = SQLGetCol("QRY1", 1)

Using tabulations in the items

The SQL functions are used to insert tabulations into the file items.
To retrieve the value of items (with TAB characters), we recommend that you use a SQLFetch/SQLGetCol browse.
Example: SQLGetCol 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:
  • SQLGetCol(QRY, 1): retrieves John
  • SQLGetCol(QRY, 2): retrieves Smith
For the second record:
  • SQLGetCol(QRY,1 ): retrieves John + tab + Mac
  • SQLGetCol(QRY, 2): retrieves Doughnut
WINDEVWEBDEV - Server codeReports and QueriesWindowsJavaPHPAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5OLE DB

SQLCol and SQLGetCol functions

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.
WINDEVWEBDEV - Server codeReports and QueriesWindowsJavaPHPAjaxUser code (UMC)ODBC

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
WEBDEV - Browser code WDJS.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