PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Running the query
  • Retrieving the query result
  • INSERT query
  • Managing text memos
  • Managing binary memos
  • SQL query (HExecuteSQLQuery or queries created in the query editor)
  • Comparison with the syntax of HExecuteSQLQuery
  • SQLExec and the threads
  • Limitations in PHP
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
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.
Note: To handle a binary memo in a query, use the WDBinaryMemo keyword in the text of your query (see remarks 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). 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). 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). See Accessing a database in local mode (SQLite) for more details.
Versions 19 and later
iPhone/iPad This function is now available for the iPhone/iPad applications (accessing the external databases via a Webservice).
Android This function is now available for the Android 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).
New in version 19
iPhone/iPad This function is now available for the iPhone/iPad applications (accessing the external databases via a Webservice).
Android This function is now available for the Android 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).
iPhone/iPad This function is now available for the iPhone/iPad applications (accessing the external databases via a Webservice).
Android This function is now available for the Android 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).
Note: From version 19, HFSQL is the new name of HyperFileSQL.
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.
WINDEVWEBDEV - Server codeReports and QueriesAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5OLE DBODBCNative Accesses
// 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")
Syntax

Running an SQL query Hide the details

<Result> = SQLExec(<Text of SQL Query> , <Query Name>)
<Result>: Boolean
  • True if the query was run,
  • False otherwise. If the query was not run, the error message can be returned by the SQL.MesError variable once SQLInfo has been run.
<Text of SQL Query>: Character string (with quotes)
SQL code of query to run.
<Query Name>: Character string (with quotes)
Name associated with the query text. Corresponds to:
  • the logical query name.
  • the name and full path of query (".WDR" file).
Java The query name corresponds to the logical query name only.
Versions 16 and later
WEBDEV - Browser code

Running a SQL query in browser code Hide the details

<Result> = SQLExec(<Text of SQL Query> , <Query Name> [, <Procedure Name>])
<Result>: Boolean
  • True if the query was run,
  • False otherwise. The query execution is asynchronous: the function requests the query execution then the current process continues to run without retrieving the query result.
    The result of SQLExec can only be used to manage the connection problems. We advise you to check the proper query execution in the <Procedure Name> procedure.
<Text of SQL Query>: Character string (with quotes)
SQL code of query to run.
<Query Name>: Character string (with quotes)
Name associated with the query text.
<Procedure Name>: Character string (with quotes)
Name of WLanguage procedure to run during the execution of the SQL query.
This procedure can be a browser procedure global to the project or a browser procedure local to the page that contains the code currently run. This procedure has the following format:
PROCEDURE Procedure Name(<Query Name>)
where <Query Name> is the name of query currently run.
This procedure is started at the end of real query execution (regardless of the query result). This browser procedure is used to:
  • check the proper execution of the query. SQLInfo is automatically run during the call to the procedure. Therefore, all the SQL variables are positioned. If an error occurred, SQL.Error will differ from "00000". The error message is returned by the SQL.MesError variable.
  • browse the query result.
If new queries are run in this procedure (to add records for example), you can:
  • use the same procedure: the parameter of this procedure is used to find out the query currently run.
  • use a different browser procedure to test the result of these new queries.
New in version 16
WEBDEV - Browser code

Running a SQL query in browser code Hide the details

<Result> = SQLExec(<Text of SQL Query> , <Query Name> [, <Procedure Name>])
<Result>: Boolean
  • True if the query was run,
  • False otherwise. The query execution is asynchronous: the function requests the query execution then the current process continues to run without retrieving the query result.
    The result of SQLExec can only be used to manage the connection problems. We advise you to check the proper query execution in the <Procedure Name> procedure.
<Text of SQL Query>: Character string (with quotes)
SQL code of query to run.
<Query Name>: Character string (with quotes)
Name associated with the query text.
<Procedure Name>: Character string (with quotes)
Name of WLanguage procedure to run during the execution of the SQL query.
This procedure can be a browser procedure global to the project or a browser procedure local to the page that contains the code currently run. This procedure has the following format:
PROCEDURE Procedure Name(<Query Name>)
where <Query Name> is the name of query currently run.
This procedure is started at the end of real query execution (regardless of the query result). This browser procedure is used to:
  • check the proper execution of the query. SQLInfo is automatically run during the call to the procedure. Therefore, all the SQL variables are positioned. If an error occurred, SQL.Error will differ from "00000". The error message is returned by the SQL.MesError variable.
  • browse the query result.
If new queries are run in this procedure (to add records for example), you can:
  • use the same procedure: the parameter of this procedure is used to find out the query currently run.
  • use a different browser procedure to test the result of these new queries.
WEBDEV - Browser code

Running a SQL query in browser code Hide the details

<Result> = SQLExec(<Text of SQL Query> , <Query Name> [, <Procedure Name>])
<Result>: Boolean
  • True if the query was run,
  • False otherwise. The query execution is asynchronous: the function requests the query execution then the current process continues to run without retrieving the query result.
    The result of SQLExec can only be used to manage the connection problems. We advise you to check the proper query execution in the <Procedure Name> procedure.
<Text of SQL Query>: Character string (with quotes)
SQL code of query to run.
<Query Name>: Character string (with quotes)
Name associated with the query text.
<Procedure Name>: Character string (with quotes)
Name of WLanguage procedure to run during the execution of the SQL query.
This procedure can be a browser procedure global to the project or a browser procedure local to the page that contains the code currently run. This procedure has the following format:
PROCEDURE Procedure Name(<Query Name>)
where <Query Name> is the name of query currently run.
This procedure is started at the end of real query execution (regardless of the query result). This browser procedure is used to:
  • check the proper execution of the query. SQLInfo is automatically run during the call to the procedure. Therefore, all the SQL variables are positioned. If an error occurred, SQL.Error will differ from "00000". The error message is returned by the SQL.MesError variable.
  • browse the query result.
If new queries are run in this procedure (to add records for example), you can:
  • use the same procedure: the parameter of this procedure is used to find out the query currently run.
  • use a different browser procedure to test the result of these new queries.
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).
WINDEVWEBDEV - Server codeReports and QueriesAjaxUser code (UMC)HFSQLHFSQL Client/Server

INSERT query

When running an INSERT query, the SQL.IdAuto variable contains the automatic identifier that was added during the previous INSERT.
WINDEVWEBDEV - Server codeReports and QueriesPHPAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5OLE DBODBCNative 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")
WINDEVWEBDEV - Server codeReports and QueriesJavaUser code (UMC)ODBCNative Accesses

Managing binary memos

To write a binary memo in a table (using a 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.
    Versions 19 and later
    AndroidAndroid Widget iPhone/iPad Case of access to external databases via a Webservice: The file path corresponds to a path local to the application that runs the Webservice (which means a path on the mobile device).
    New in version 19
    AndroidAndroid Widget iPhone/iPad Case of access to external databases via a Webservice: The file path corresponds to a path local to the application that runs the Webservice (which means a path on the mobile device).
    AndroidAndroid Widget iPhone/iPad Case of access to external databases via a Webservice: The file path corresponds to a path local to the application that runs the Webservice (which means 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.
    Versions 19 and later
    AndroidAndroid Widget iPhone/iPad Case of access to external databases via a Webservice: The file path corresponds to a path local to the application that runs the Webservice (which means a path on the mobile device).
    New in version 19
    AndroidAndroid Widget iPhone/iPad Case of access to external databases via a Webservice: The file path corresponds to a path local to the application that runs the Webservice (which means a path on the mobile device).
    AndroidAndroid Widget iPhone/iPad Case of access to external databases via a Webservice: The file path corresponds to a path local to the application that runs the Webservice (which means 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")
Note: Inserting binary memos into a 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".
WINDEVWEBDEV - Server codeReports and QueriesAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5

SQL query (HExecuteSQLQuery 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 a 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.
Note: HExecuteQuery and HExecuteSQLQuery are used to check the integrity and the duplicates on a HFSQL database.
WINDEVWEBDEV - Server codeReports and QueriesJavaPHPUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5OLE DBODBCNative 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: indeed, HExecuteSQLQuery specifies the name of the query first then the text of the query (using the same standard as all the HFSQL functions).
The syntax of SQLExec is kept for backward compatibility.

SQLExec and the threads

When running SQLExec in a secondary thread, the connection used 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.
PHP

Limitations in PHP

  • The binary memos cannot be handled in this version. The query is run on a MySQL database.
  • The multiple queries are not supported. For example:
    DROP TABLE TEST ;
    CREATE TABLE TEST (`IDTEST` INTEGER  PRIMARY KEY ,
    `recipient` VARCHAR(50)  NOT NULL ,
    `testname` VARCHAR(50)  NOT NULL ,`PRODUCTNAME` VARCHAR(50)  NOT NULL );
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
Exemplo completo
ConnectionNum is int
SourceName is string
// Connection to a specific data source via ODBC MS ACCESS
SourceName = "MS Access 97 Database"
ConnectionNum = SQLConnect(SourceName, "", "", "", "ODBC") // <--------
IF ConnectionNum <> 0 THEN
// The connection was successful

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

ELSE
// The connection failed: display an error message
SQLInfo()
Error("The connection to the data source " + SourceName + " failed." + CR + ...
"Error code: " + SQL.Error + CR + SQL.MesError)

END
// In any case (connection OK or not)
SQLDisconnect() // <--------
BOLLER
Sep. 11 2018