SQLExec (Function)

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

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

WEBDEV

WINDEV Mobile

Others

See also

SQLExec (Function)

In french: SQLExec

Names and runs an SQL query.

Attention The function SQLExec function does not start the memory result retrieval process. The result is retrieved on the first call to one of the following functions: SQLFirst, SQLTable or SQLFetch.

Note To manipulate a binary memo in a query, use the keyword WDMemoBinary keyword in your query text (for details, see remarks).

Example

See additional examples

// Connexion à la base de données
// (Fonction SQLConnecte ou SQLConnecteWS)

// Exécuter la requête et récupérer le résultat ligne à ligne 
i is int = 0
SQLExec("SELECT NOM, PRENOM, POSTE, PHOTO FROM CLIENT", "REQ1")
WHILE SQLFetch("REQ1") = 0  // Il y a encore une ligne à lire
	i++
	// Récupérer les données
	NOM[i] = SQLGetCol("REQ1", 1)
	PRENOM[i] = SQLGetCol("REQ1", 2)
	POSTE[i] = SQLGetCol("REQ1", 3)
	{"IMAGE"+i} = SQLGetMemo("REQ1", 4)
END
SQLClose("REQ1")

// Déconnexion (fonction SQLDéconnecte)

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).

INSERT query

When running an INSERT query, the SQL.IDAuto variable contains the automatic identifier that was added during the previous INSERT.

Managing text memos

In the <Query text> parameter, a text memo is used like a text variable. For example:

// Requête avec condition sur un mémo texte dont la valeur est "Bon client" 
TexteRequête = "SELECT NOMCLI FROM CLIENT WHERE MEMOCLIENT = 'Bon client'"
SQLExec(TexteRequête, "REQ1")

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.
{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.

For example:

// Insertion du fichier C:\PHOTO\DUPONT.BMP dans un mémo binaire
TexteRequête = "INSERT INTO CLIENT VALUES ('DUPONT', 'Jean'," + ... 
	"{WDMemoBinaire('C:\PHOTO\DUPONT.BMP')})"
SQLExec(TexteRequête, "REQ1")
// Insertion du fichier C:\DOC\LETTRE.TXT dans un mémo texte
TexteRequête = "INSERT INTO CLIENT VALUES ('DUPUIS', 'Christophe'," + ... 
	"{WDMemoBinaire('C:\DOC\LETTRE.TXT', 'FichierTexte')})"
SQLExec(TexteRequête, "REQ1")

Note Inserting binary memos into an HFSQL data file using SQL functions is not possible and will result in an error. The error message is as follows: "Query cannot be initialized. Character {WDMemBinary( unexpected".

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 HFSQL functions (HDelete, HAdd or HModify) on your data files. The integrity check and the duplicate check will be automatically performed.

Remarque: The HExecuteQuery and HExecuteSQLQuery functions are used to check the integrity and duplicates of an HFSQL database.

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 Reverse: the HExecuteSQLQuery function specifies the query name first, followed by the query text (using the same standard as all HFSQL functions).

The syntax of SQLExec is kept for backward compatibility.

SQLExec and threads

When executing the SQLExec FUNCTION in a secondary thread, the connection used must be made in the same thread: the connection cannot be made in the main thread (project or window). The connection established by SQLConnect is not shared in the other threads.

Component: wd300hf.dll