ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / Managing HFSQL Client/Server
  • Overview
  • Creating a stored procedure and a stored query
  • The stored procedures are created in the analysis.
  • Importing an existing set of procedures
  • Using a query in a stored procedure
  • WLanguage code that can be used in the stored procedures
  • Stored procedures that can be compiled in 64 bits
  • Testing a stored procedure
  • Limit
  • Deploying a stored procedure and the associated stored queries
  • Overview
  • Deploying for test
  • Deploying in real mode
  • Hot update of stored procedures on the HFSQL server
  • Running a stored procedure in your programs
  • Running a stored procedure from an SQL query
  • Managing errors in the stored procedures
  • Mechanism of sub-errors
  • The following errors can occur when running stored procedures
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
Stored procedures and queries (HFSQL Client/Server)
HFSQL Client/ServerAvailable only with this kind of connection
Overview
The stored procedures are used to simplify the development and the maintenance of your applications by factorizing the code.
Indeed, when the method used to access the data evolves (when the structure of files changes for example), all you have to do is modify the stored procedure on the server without performing any modification in the deployed applications that use this procedure.
A stored procedure is also used to limit the number of back and forth processes between the client computer and the server, and therefore increases the speed of processes.
The stored procedures can use SQL queries or queries created by the query editor. The stored procedures and the queries used by the stored procedures are created in the data model editor.
This help page presents:
Creating a stored procedure and a stored query

The stored procedures are created in the analysis.

To create a stored procedure:
  1. Create (if necessary) the set of stored procedures in the analysis:
    • click in the quick access buttons.
      • The new element window appears: click "Data" then "Set of stored procedures".
      • Type the name of the set of procedures and validate.
    • via the "Analysis" pane: select the "Stored procedures" folder. Select "New set of procedures" in the context menu. The set of stored procedures is automatically created. This set of procedures is named "Set_<Analysis Name>" by default.
    • in the ribbon of the data model editor: on the "Analysis" tab, in the "Creation" group, expand "New" and select "Set of stored procedures". The set of stored procedures is automatically created. This set of procedures is named "Set_<Analysis Name>" by default.
  2. This set is physically saved in the "Code" subdirectory of analysis directory.
  3. Create a procedure in the set of procedures. This procedure will be a stored procedure.

Importing an existing set of procedures

A set of stored procedures can correspond to an existing set of procedures (found in the current project or in another project).
To import a set of procedures:
  1. Open the data model editor if necessary.
  2. In the "Analysis" pane, select the "Stored procedures" folder.
  3. Open the context menu and select "Import a set of procedures". In the window that is opened, select the set of procedures to import and validate.
  4. The following operations are performed:
    • If the set of procedures belongs to the current project: the set of procedures found in the project is automatically copied into the "Code" directory of analysis. The logical name of this procedure is automatically renamed.
      Caution: The two sets of procedures are dissociated: a modification performed in the set of procedures found in the project will be automatically applied to the set of procedures found in the analysis (and conversely).
    • If the set of procedures does not belong to the current project: the set of procedures is automatically copied into the "Code" directory of analysis and it is defined as stored procedure.

Using a query in a stored procedure

A query created by the query editor can be used in a stored procedure. To do so, the query must have been created in the data model editor.
To create a query in the data model editor:
  • Method 1:
    1. Click in the quick access buttons.
      • The new element window appears: click "Data" then "Stored query".
      • The wizard for query creation is automatically started. The query is automatically created. The query is physically saved in the "Code" subdirectory of analysis directory.
    2. The query can be used in one of the stored procedures of analysis.
  • Method 2:
    1. Display the data model editor.
    2. Display the "Analysis" pane and select "New query" in the context menu. The wizard for query creation is automatically started. The query is automatically created. The query is physically saved in the "Code" subdirectory of analysis directory.
    3. The query can be used in one of the stored procedures of analysis.
To import a query:
  1. Open the data model editor if necessary.
  2. In the "Analysis" pane, select the "Queries"; folder.
  3. Select "Import a query" in the context menu. In the window that is opened, select the query to import and validate.
  4. The following operations are performed:
    • If the query belongs to the current project: the project query is automatically copied into the "Code" directory of analysis and re-located: a single query is used by the project and the analysis. Therefore, a modification performed in the project query will be automatically applied to the analysis query (and conversely).
    • If the query does not belong to the current project: the query is automatically copied into the "Code" directory of analysis and it is defined as query linked to the analysis.

WLanguage code that can be used in the stored procedures

  • The code of the stored procedures is written in WLanguage. Several WLanguage functions can be used.
  • To find out whether a function can be used in a stored procedure, see the help about this function: if the image is displayed in the title bar of function, the function can be used in the code of stored procedures.
    Furthermore, when the code is compiled, error messages inform you that unauthorized functions have been used in the code of stored procedures.
    For example, only the simple types can be used in the code of stored procedures (strings, integers, ...). A parameter of stored procedure must be a simple parameter: no object or array can be passed as parameter. However, Serialize can be used to pass an array as parameter to a stored procedure.
  • The following elements cannot be used in the stored procedures: classes, reports, windows, pages, ...
  • The windows and pages for automatic error management are not available in the stored procedures. However, HOnError can be used to redirect the error to a procedure (this procedure must also be a stored procedure).
  • Several WLanguage variables allow you to get information about the client application that uses a stored procedure.
    These variables are as follows:
    HRuntimeInfo.ApplicationClient application that started the current stored procedure.
    HRuntimeInfo.ComputerComputer of the user who started the current stored procedure (IP address or name of computer).
    HRuntimeInfo.DatabaseDatabase used by the client application that started the stored procedure.
    HRuntimeInfo.LoginLogin of the user of the client application that started the stored procedure.
  • You have the ability to save values on the server from a stored procedure. This value can be read by another stored procedure or by a trigger for example. The following functions are used to manage persistent values on the server:
    HDeleteParameterDeletes a parameter previously saved by HSaveParameter.
    HListParameterReturns the list of parameters saved from the stored procedures on the server.
    HLoadParameterReads a parameter that was saved from a stored procedure by HSaveParameter.
    HSaveParameterSaves a persistent value on the HFSQL server.
  • You have the ability to save temporary files in a server directory and to clear this directory. The following functions can be used:
    HClearWorkingDirClears and destroys the temporary directory previously created when HServerWorkingDir was executed.
    HServerWorkingDirReturns the path of a temporary directory on the HFSQL server.
  • To handle the data files, you must use HDeclareExternal.
    For example:
    PROCEDURE NameOfStoredProcedure()
     
    // Check the existence of the logical file
    IF NOT HFileExist(ZIPCODES) THEN
    // File not known, it must be declared
    // Caution: to declare a new file in a stored HFSQL procedure,
    // you must:
    // - use NO connection: The current database
    // on which the stored procedure is found will be used
    // - specify NO full path:
    // The file will be sought in the current database.
    // You have the ability to specify a subdirectory of the database.
    // - specify the full name of the file with its extension (.FIC)
    IF NOT HDeclareExternal("ZIPCODES.FIC", "ZIPCODES") THEN
    // Error while declaring the file
    RESULT HErrorInfo()
    END
    END
     
    // Use the data file
    // FOR EACH ZIPCODES
    // //Process...
    // END
     
    // Process OK, without error
    RETURN ""

Stored procedures that can be compiled in 64 bits

To use a 32-bit executable with stored procedures run on a 64-bit server:
  1. Open the analysis description window.
    • Open the data model editor if necessary.
    • Open the context menu of the analysis (right-click the analysis schema) and select "Analysis description".
  2. Display the "Compilation" tab.
  3. Check "Enable the compilation errors for the 64-bit compatibility".
  4. Validate.

Testing a stored procedure

To run the test of a stored procedure:
  1. In the analysis pane, select the stored procedure whose test must be run.
  2. Right-click to open the context menu of the stored procedure and select "Test procedure".
  3. The window for entering the parameters of the procedure is displayed. This window allows you to:
    • Type the procedure parameters.
    • Run the procedure test.
      Remark: this window can be used to restart the execution several times by modifying the parameters.
  4. When the procedure test is run:
    • An update of stored procedure on the server is proposed if necessary.
    • The procedure is started on the server.
    • The return value of procedure is displayed if necessary.
Notes/limitations:
  • The debugging port is port 27281 by default. This port must be opened in the firewall. This port can be modified in the HFConf.ini file. For more details on how to debug stored procedures, see Debugging a connection to an HFSQL server.
  • To run the test of a stored procedure, you must have the debugging rights on the database.
  • The traces used in the stored procedures are displayed in the Debugger trace pane.
  • The code of stored procedure can contain breakpoints: the debugger will be started.
  • The elements deployed on the server are used during the test.
  • InTestMode returns True.

Limit

In this version, you cannot import a set of stored procedures found on a server.
Deploying a stored procedure and the associated stored queries

Overview

To run the test of a stored procedure and/or to run a stored procedure, it must be deployed on the server. This deployment can be performed:
  • for test: the stored procedures and/or the queries are deployed on the selected server but the analysis is not updated. This operation is recommended for large-sized analyses whose generation can take quite a long time.
    Caution: any application deployed on the server can no longer operate after this deployment for test. This option must be used on a test server.
  • in real mode: the analysis must be regenerated and the automatic modification of the data files must be perfomed when installing the client version.

Deploying for test

To run the test of modifications performed in a stored procedure:
  1. In the data model editor, on the "Analysis" tab, in the "Analysis" group, expand "Synchronization" and select "Update stored elements (HFSQL Client/Server)".
  2. In the window that is displayed, select:
    • the sets of procedures to update.
    • the queries to update.
    • the triggers to update.
    • the connection affected by the update. You can also directly enter the characteristics of the connection.
  3. Validate the update.
Remark: When deploying for test, you can also generate the analysis and accept the automatic data modification.

Deploying in real mode

To update a Client/Server application that uses stored procedures, you must:
  1. Generate the analysis containing the stored procedures ("Generate the analysis" in the context menu of analysis). The data files can be modified during this step.
  2. Create the Client version and request an automatic modification of data files: on the "Project" tab, in the "Generation" group, expand "Setup procedure" and select "Create setup procedure".
    Remark: the automatic modification of the data files is proposed when the code of stored procedures or analysis queries are modified.
  3. Install the application.
Remarks:
  • HUpdateSet and HDeleteSet are used to force the update and the deletion of sets of stored procedures on the server.
  • HUpdateQuery and HDeleteQuery are used to force the update and the deletion of stored queries on the server.
  • HListStoredElement is used to list the sets of procedures, the queries and the procedures installed on a server.
  • The HFSQL Control Center is used to manage the stored procedures and the queries found on the server.

Hot update of stored procedures on the HFSQL server

The hot update of stored procedures on the HFSQL server consists in updating the sets of stored procedures on the server while the application is currently used. This operation is used to perform an update without disconnecting all users.
This update can be performed by HUpdateSet. The following conditions are required to perform this update:
  • If no procedure of the set was run, the set of procedures can be updated on the server.
  • If a procedure of the set was run, the set of procedures can be updated only if the modification of the set of procedures does not affect the global variables. If the global variables of the set of procedures have been modified, HUpdateSet returns False. In this case, all the users must be disconnected to perform the update.
    Caution: In this last case, if the update was performed, the users connected before the update will use during the next execution of a stored procedure:
    • the new code of stored procedure,
    • the global variables with the values defined before the update.
Remark: If a procedure of the set of procedures is being run, the update is locked until its execution is completed (HUpdateSet is a blocking function). All the stored procedures being executed are finished, and all the stored procedures called after HUpdateSet is executed are blocked until the set is updated on the server.
Running a stored procedure in your programs
A stored procedure can be started from an application:

Running a stored procedure from an SQL query

To run a stored procedure from a SQL query, specify in the query the name of the procedure to run and the expected parameters.
You also have the ability to directly run a stored procedure and the retrieve its result in SQL. In this case, the syntax to run the stored procedure is:
SELECT NameOfStoredProcedure(NumericParameter,'StringParameter',....) [FROM DUAL]
Remarks:
  • The stored procedure must be a stored function: it must always return a value. Otherwise, a runtime error will occur.
  • If the name of the stored procedure is identical to the name of a function or procedure found in the project, its name must be prefixed by the name of the set of stored procedures.
Example 1: The following query is used to return the turnover for all the customers living in Paris (75). The turnover is calculated by a stored procedure named nReturnTO.
// Query run by the client computer
HExecuteSQLQuery("TOCustomer75", "SELECT CustomerName, ZipCode, " + ...
"nReturnTO(Customer.CustomerNumber,'20061201') " + ...
"as Total from Customer where zipcode like '34%')")
// Stored procedure run on the server
PROCEDURE nReturnTO(nCustomerNum, Deadline)
nTotal is currency
// Filter on the limit date
// Retrieve the orders from Deadline
HFilter(Orders, CustomerNum, hMinVal, hMaxVal, "OrderDate >= " + Deadline + "")
// Retrieve all the customer orders and add their amount
HReadSeekFirst(Orders, CustomerNum, nCustomerNum)
WHILE HFound(Orders)
nTotal += Orders.TotalIOT
HReadNext(Orders, CustomerNum)
END
RESULT nTotal
Example 2: Running and retrieving the result of a stored procedure:
dsStoredProc is Data Source
IF NOT HExecuteSQLQuery(dsStoredProc, HFSQLCnt, hDefaultQuery,StringBuild([
SELECT Function_StoredProcedure(%1,'%2')
FROM DUAL
], 3, "string to pass as parameter")) THEN
Error("Failure calling the stored procedure ", HErrorInfo())
ELSE
IF HReadFirst(dsStoredProc) THEN
Info("Result of stored procedure: ", HRecordToString(dsStoredProc))
END
END
Managing errors in the stored procedures

Mechanism of sub-errors

The management of errors in the stored procedures uses the mechanism of sub-errors. If an error occurs when a stored procedure is run on the server, the different sub-errors are filled with the details of the errors that occurred at each execution level. The different errors are returned by ExceptionInfo.
Some examples:
1. A procedure is run on the client. This procedure calls a procedure stored on the server. This stored procedure generates an error.
Client application:
PROCEDURE Client_Procedure()
// Procedure run on the client computer
...
// Line 10: Runs a procedure on the server
HExecuteProcedure(AConnection, AStoredProcedure)
Server application:
// Procedure stored on the server
PROCEDURE AStoredProcedure
...
// Line 5: The security mechanism of WLanguage
// detects an unknown identifier
RESULT UnknownElement
The exception is displayed on the client computer only. It contains the following information:
  • Main error: information regarding the client: "Error found by the XXX server", Client_Procedure procedure, Line 10, ...
  • Sub-error: information regarding the server: "Unknown UnknownElement identifier", AProcedure procedure, Line 5, ...
2. A procedure is run on the client. This procedure calls a procedure stored on the server. This stored procedure calls a second procedure that generates an error.
Client application:
PROCEDURE Client_Procedure()
// Procedure run on the client computer
...
// Line 10: Runs a procedure on the server
HExecuteProcedure(AConnection, AStoredProcedure)
Server application:
// Procedure stored on the server
PROCEDURE AStoredProcedure
...
// Line 3: running a query
HExecuteSQLQuery(AQuery, "SELECT ProcedureCalculation(Fic.Itm) FROM Fic")
// Line 5: Browsing the query
FOR EACH AQuery
...
END
// Calculation procedure called from the query
PROCEDURE CalculationProcedure(param)
...
// Line 4: The security mechanism of WLanguage
// detects an unknown identifier
RESULT UnknownElement
The exception is displayed on the client computer only. It contains the following information:
  • Main error: information regarding the client: "Error found by the XXX server", Client_Procedure procedure, Line 10, ...
  • Sub-error 1: server information regarding the AStoredProcedure procedure: "Error while running the query", AStoredProcedure procedure, line 5, ...
  • Sub-error 2: server information regarding the CalcProcedure procedure: "Unknown UnknownElement identifier", CalcProcedure procedure, line 4, ...
Remark: To directly get the last sub-error (that is the first error that happened on the HFSQL server), use the following syntax:
// First error on the server
ExceptionInfo(errMessage, errOrigin)

The following errors can occur when running stored procedures

The following errors can occur when running stored procedures:
75000: The server cannot define whether the XXX procedure must be run
This error can occur in the following cases:
  • A stored procedure with the same name is used by several databases and by several connections. The server cannot define whether the XXX procedure must be run with the YYY connection on the ZZZ database or with the YYY1 connection on the ZZZ1 database.
    Solution: Specify the connection and the database to use.
  • Two connections to the database are available. The server cannot defined whether the XXX procedure on the ZZZ database must be run with the YYY connection or with the YYY1 connection.
    Solution: Specify the connection to use
  • The procedure is used in several sets of procedures.
75001: The database was not specified
This error can occur if several databases exist for a same connection. To run the stored procedure (XXX), specify a database in the connection used.
75002: Stored procedure not found
This error occurs when the procedure called is not found in the sets of procedures installed on the server.
75003: A WLanguage error occurred while running the stored procedure
This error occurs when the stored procedure contains WLanguage errors.
75004: The XXX element does not exist
The stored procedure or the stored query does not exist.
75005: The XXX element cannot be updated.
The stored procedure or the stored query is currently used. To update this element, disconnect the users.
75006: Set of procedures not compatible with the 64-bit servers
The set of procedures is used on a 64-bit server.
In order for this set of procedures to operate, you must select "Enable compilation errors for 64-bit compatibility" ("Analysis description" in the analysis context menu, "Compilation" tab).
Minimum version required
  • Version 11
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 06/07/2023

Send a report | Local help