ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / SQL functions
  • Running the query
  • Retrieving the query result
  • INSERT query
  • Managing text memos
  • Managing binary memos
  • SQL query (SQLExec or queries created in the query editor)
  • Comparison with the syntax of HExecuteSQLQuery
  • SQLExec and threads
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Names and runs an SQL query.
Caution: SQLExec does not start the process for retrieving the result in memory. The result is retrieved during the first call to one of the following functions: SQLFirst, SQLTable or SQLFetch.
Remark: To handle a binary memo in a query, use the WDBinaryMemo keyword in the text of your query (for more details, see remarks).
AndroidAndroid Widget iPhone/iPadIOS WidgetMac Catalyst This function is available when accessing external databases via a Webservice.
HFSQL ClassicHFSQL Client/ServerNative Connectors (Native Accesses)
// Connect to the database
// (SQLConnect or SQLConnectWS functions)
 
// Run the query and retrieve the result line by line
i is int = 0
SQLExec("SELECT LASTNAME, FIRSTNAME, EXTENSION, PHOTO FROM CUSTOMER", "QRY1")
WHILE SQLFetch("QRY1") = 0 // There is still another line to read
i++
// Retrieve the data
LASTNAME[i] = SQLGetCol("QRY1", 1)
FIRSTNAME[i] = SQLGetCol("QRY1", 2)
EXTENSION[i] = SQLGetCol("QRY1", 3)
{"IMAGE"+i} = SQLGetMemo("QRY1", 4)
END
SQLClose("QRY1")
 
// Disconnect (SQLDisconnect)
Syntax

Running an SQL query Hide the details

<Result> = SQLExec(<SQL query text> , <Query name>)
<Result>: Boolean
  • True if the query was executed,
  • False otherwise. If the query was not run, the error message can be returned by the SQL.MesError variable once SQLInfo has been run.
<SQL query text>: Character string
SQL code of query to run.
<Query name>: Character string
Name associated with the query text. Corresponds to:
  • the logical query name.
  • the name and full path of query (".WDR" file).
Remarks

Running the query

The information regarding the query execution is returned by SQLInfo. The SQL.NbCol variable contains the number of columns found in the query result.
Once it was run and processed, the query must be freed by SQLClose.

Retrieving the query result

Then, the query result can be:
  • Transferred into a table or into a list box (SQLTable).
  • Transferred into controls or variables (SQLAssociate).
  • Retrieved line by line (SQLFetch).
HFSQL ClassicHFSQL Client/Server

INSERT query

When running an INSERT query, the SQL.IDAuto variable contains the automatic identifier that was added during the previous INSERT.
HFSQL ClassicHFSQL Client/ServerNative Connectors (Native Accesses)

Managing text memos

In the <Query text> parameter, a text memo is used like a text variable. For example:
// Query with condition on a text memo whose value is "Good customer"
QueryText = "SELECT CUSTNAME FROM CUSTOMER WHERE CUSTOMERMEMO = 'Good customer'"
SQLExec(QueryText, "QRY1")
Native Connectors (Native Accesses)

Managing binary memos

To write a binary memo in a table (using an SQL UPDATE or INSERT command), the following syntax must be used in the text of the query:
  • {WDBinaryMemo('<FileName>' [, 'File'])}

    where:
    • File (default value) indicates that the memo is a binary memo (coming from a file).
    • WDBinaryMemo is a reserved word.
    • <FileName> is the physical name of a file.
      AndroidAndroid Widget iPhone/iPadIOS WidgetMac Catalyst Case of access to external databases via a Webservice: The file path corresponds to a path local to the application that runs the Webservice (i.e. a path on the mobile device).
  • {WDBinaryMemo('<FileName>' [, 'TextFile'])}
    where:
    • TextFile indicates that the memo is a text memo (coming from a file).
    • WDBinaryMemo is a reserved word.
    • <FileName> is the physical name of a file.
      AndroidAndroid Widget iPhone/iPadIOS WidgetMac Catalyst Case of access to external databases via a Webservice: The file path corresponds to a path local to the application that runs the Webservice (i.e. a path on the mobile device).
For example:
// Insert the C:\PHOTO\SMITH.BMP file into a binary memo
QueryText = "INSERT INTO CUSTOMER VALUES ('SMITH', 'John'," + ...
"{WDBinaryMemo('C:\PHOTO\SMITH.BMP')})"
SQLExec(QueryText, "QRY1")
// Insert the C:\DOC\LETTER.TXT file into a text memo
QueryText = "INSERT INTO CUSTOMER VALUES ('THOMPSON', 'Christopher'," + ...
"{WDBinaryMemo('C:\DOC\LETTER.TXT', 'TextFile')})"
SQLExec(QueryText, "QRY1")

Remark: Inserting binary memos into an HFSQL file with the SQL functions is not possible and it triggers an error. The error message is as follows: "Unable to initialize the query. Unexpected {WDBinaryMemo( character".
HFSQL ClassicHFSQL Client/Server

SQL query (SQLExec or queries created in the query editor)

When using the SQL DELETE, INSERT or UPDATE statements, no integrity check and no duplicate check are performed on an HFSQL database. This feature is not available in this version.
Solution: Use the HFSQL functions (HDelete, HAdd or HModify) on your data files. The integrity check and the duplicate check will be automatically performed.
Remark: HExecuteQuery and HExecuteSQLQuery are used to check the integrity and the duplicates on an HFSQL database.
HFSQL ClassicHFSQL Client/ServerNative Connectors (Native Accesses)

Comparison with the syntax of HExecuteSQLQuery

HExecuteSQLQuery can also be used to run an SQL query. The parameters of these two functions are identical but their order is reversed: HExecuteSQLQuery indicates the name of the query first, then the text of the query (using the same standard as all HFSQL functions).
The syntax of SQLExec is kept for backward compatibility.

SQLExec and threads

When SQLExec is run in a secondary thread, the connection must be established in the same thread: the connection cannot be established in the main thread (project or window). The connection established by SQLConnect is not shared in the other threads.
Component: wd290hf.dll
Minimum version required
  • Version 9
This page is also available for…
Comments
Exemplo de conexao em Postgresql em outra porta
nDBConnection = SQLConnect(sServerIP,sUser,sPassword,sDBName,"PostgreSQL","","Server Port = 15433")
Boller
12 Apr. 2023

Last update: 05/26/2022

Send a report | Local help