ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Native Connectors/Native Accesses / Native Oracle Connector
  • Overview
  • How to?
  • Steps
  • Remarks
  • Example
  • 1. Declaring the data source
  • 2. Declaring variables
  • 3. Preparing the query
  • 4. Values of parameters and re-execution of the query
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
Native Oracle Connector: Running the same query several times
Native Connectors (Native Accesses)Available only with this kind of connection
Overview
In some cases, it may be useful to run the same query multiple times while modifying one or more parameters. For example, you may want to run an Insert query several times to add several records to a file.
Several solutions can be implemented:
  1. Execute the query directly (with HExecuteSQLQuery) as many times as necessary and change the desired parameter(s) each time.
  2. Prepare the query to be executed (with HPrepareSQLQuery) as well as the different parameters to be changed. Then, execute the query as many times as necessary with HExecuteSQLQuery. This solution is much faster and optimizes the time it takes to loop through the result of a query (SELECT query).
This help page explains how this second solution can be implemented.
How to?

Steps

To prepare and execute a query multiple times:
  1. Declare a data source. This data source will contain the result of the SQL query.
  2. Declare the types of query variables.
    The variables are retrieved in the type specified in WLanguage. The variables are text variables by default.
    Therefore, conversions may occur between the native type of the database and the WLanguage type, causing unexpected rounding or formatting errors (Date transformed into string for example).
    Therefore, we advise you to specify the WLanguage types of the variables with great care before running the query. To do so:
    • initialize the value of the variable (simple types: integer, string, float)
    • specify the expected type with the Type property (not supported in this version)
  3. Prepare the query with HPrepareSQLQuery.
  4. Specify the value of the different parameters to take into account and run the query with HExecuteSQLQuery. Only the name of the data source that corresponds to the query must be specified.
This last step must be repeated as many times as necessary.

Remarks

  • HPrepareSQLQuery must be used with:
    • the connection name,
    • the hQueryWithoutCorrection constant.
  • The declared variables must be identical to the ones used. Otherwise, a WLanguage error occurs.
  • In the call to the stored procedure, you must use the syntax specific to the database used, including for the syntax of parameters. Therefore, for Oracle, the parameters are specified with the :ParamName syntax.
    Caution: the ":" character must be followed by at least one letter (the syntax:1 is not allowed).
    The same parameter can be used several times. In this case, the corresponding variable will be reused.
  • To execute a query without enabling the bind option, use the hNoBind constant in HExecuteSQLQuery.
Example
An Insert query must be run several times in the PERSON table. 3 parameters are modified whenever an addition is performed:
  • the last name.
  • the first name.
  • the age.

1. Declaring the data source

The data source is used to handle the different variables that will be modified whenever the query is run:
dsInsert is Data Source

2. Declaring variables

In this example, three variables will be modified. The three variables could be declared. However, only the age_person variable will be declared.
To specify the type of a query variable, assign an existing variable of the desired type to this variable.
dsInsert.age_person = 0 // Forces the type to integer
 
// other solution:
// B is int
dsInsert.age_person = B

3. Preparing the query

The query will be prepared to be run several times with HPrepareSQLQuery.
HPrepareSQLQuery(dsInsert, Connection, hQueryWithoutCorrection, ...
"INSERT INTO PERSON VALUES (:lastname,:firstname,:age_person)")

4. Values of parameters and re-execution of the query

All you have to do now is specify the value of the parameters for each query execution.
I is int
FOR I = 1 TO 10
dsInsert.lastname = "LastName " + I
dsInsert.firstname = "FirstName " + I
dsInsert.age_person = I
HExecuteSQLQuery(dsInsert)
END
Minimum version required
  • Version 9
This page is also available for…
Comments
Example HexecuteSqlQuery with WHILE HOut() = False
numero_corrida is int = 0

MyProcedureUltimaCorrida is Data Source

IF HExecuteSQLQuery(MyProcedureUltimaCorrida, ConnNativa, hQueryWithoutCorrection,"NG0002_Procedure_UltimaCorrida") THEN

HReadFirst(MyProcedureUltimaCorrida, num_corrida)

WHILE HOut() = False

ok = HReadNext(MyProcedureUltimaCorrida, num_corrida)

numero_corrida = MyProcedureUltimaCorrida.num_corrida

END

ELSE

Error(HErrorInfo())

END

Trace(numero_corrida)
BOLLER
29 Mar. 2019

Last update: 10/27/2022

Send a report | Local help