PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US

  • 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
  • Running the test of 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 a 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/iPadApple WatchUniversal Windows 10 AppWindows Mobile
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:
Note: From version 19, HFSQL is the new name of HyperFileSQL.
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:
    • select "File .. New .. Data .. Set of stored procedures". Type the name of the set of procedures.click among the quick access buttons.
      • In the wheel that is displayed, click "Data" and select "Set of stored procedures". The window for creating a new element is displayed: 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" from the popup 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" pane, 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.
      • via "Insert .. 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" sub-directory 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. Display the popup 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. Select "File .. New .. Data .. Stored query". The wizard for query creation is automatically started. The query is automatically created. The query is physically saved in the "Code" sub-directory of analysis directory.
    2. The query can be used in one of the stored procedures of analysis.
  • Method 1:
    1. Click among the quick access buttons.
      • In the wheel that is displayed, click "Data" and select "Stored query". The window for creating a new element is displayed: 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" sub-directory 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" from the popup menu. The wizard for query creation is automatically started. The query is automatically created. The query is physically saved in the "Code" sub-directory 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" from the popup 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, any 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 in parameter. However, Serialize can be used to pass an array in 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 management of errors 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 is using a stored procedure.
    These variables are as follows:
    HRuntimeInfo.ApplicationClient application that started the current stored procedure.
    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.
    HRuntimeInfo.ComputerComputer of the user who started the current stored procedure (IP address or name of computer).
  • 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 the persistent values on the server:
    Versions 14 and later
    HDeleteParameter
    New in version 14
    HDeleteParameter
    HDeleteParameter
    Deletes a parameter that was saved beforehand by HSaveParameter.
    Versions 14 and later
    HListParameter
    New in version 14
    HListParameter
    HListParameter
    Returns the list of parameters saved from the stored procedures on the server.
    Versions 14 and later
    HLoadParameter
    New in version 14
    HLoadParameter
    HLoadParameter
    Reads a parameter that was saved from a stored procedure by HSaveParameter.
    Versions 14 and later
    HSaveParameter
    New in version 14
    HSaveParameter
    HSaveParameter
    Saves a persistent value from a stored procedure.
  • You have the ability to save temporary files in a server directory and to clear this directory. The following functions can be used:
    Versions 14 and later
    HClearWorkingDir
    New in version 14
    HClearWorkingDir
    HClearWorkingDir
    Clears and destroys the temporary directory that was created during the execution of HServerWorkingDir.
    Versions 14 and later
    HServerWorkingDir
    New in version 14
    HServerWorkingDir
    HServerWorkingDir
    Returns the path of a temporary directory on the HFSQL server.
  • To handle the data files, you must use HDeclareExternal.
    For example:
    PROCÉDURE 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 sub-directory 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
    RESULT ""

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. Display the description window of the analysis.
    • Open the data model editor if necessary.
    • Display the popup menu of analysis (right mouse click on the analysis graph) and select "Analysis description".
  2. Display the "Compilation" tab.
  3. Check "Enable the compilation errors and the 64-bit compatibility".Check "Enable the compilation errors for the 64-bit compatibility".
  4. Validate.

Running the test of 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. Display the popup menu of the stored procedure (right mouse click) and select "Run the procedure test".
  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.
      Note: 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.
  • 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 Trace of debugger 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. Select "Analysis .. HyperFileSQL Client/Server .. Update the stored elements".
    Note: you also have the ability to select "Update the stored elements" from the popup menu of the "Analysis" pane.
    In the data model editor, on the "Analysis" pane, in the "Analysis" group, expand "Synchronization" and select "Update the stored elements (HyperFileSQL Client/Server)".
    In the data model editor, on the "Analysis" pane, in the "Analysis" group, expand "Synchronization" and select "Update the 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.
Note: 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 is using stored procedures, you must:
  1. Generate the analysis containing the stored procedures ("Generate the analysis" from the popup 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 ("Workshop .. Create the setup procedure").
    Note: the automatic modification of the data files is proposed when the code of stored procedures or analysis queries are modified.
    Create the Client version and request an automatic modification of data files: on the "Project" pane, in the "Generation" group, expand "Setup procedure" and select "Create the setup procedure".
    Note: the automatic modification of the data files is proposed when the code of stored procedures or analysis queries are modified.
  3. Install the application.
Notes:
  • HRefreshSet and HDeleteSet are used to force the update and the deletion of sets of stored procedures on the server.
  • HRefreshQuery 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 HRefreshSet. 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. HRefreshSet returns False if the global variables of the set of procedures have been modified. In this case, all the users must be disconnected before the update can be performed.
    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.
Note: If a procedure found in the set of procedures is currently run, the update is locked until the end of procedure execution (HRefreshSet is a locking function). All stored procedures currently run are ended and all stored procedures called after the execution of HRefreshSet are locked 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 a 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 as follows:
SELECT NameOfStoredProcedure(NumericParameter,'StringParameter',....) [FROM DUAL]
Notes:
  • 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 in parameter")) THEN
Error("Failure calling the stored procedure ", HErrorInfo())
ELSE
IF HReadFirst(dsStoredProc) THEN
Info("Result of stored procedure: ", HRetrieveRecord(dsStoredProc))
END
END
Managing errors in the stored procedures

Mechanism of sub-errors

The management of errors in the stored procedures is using 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:
PROCÉDURE 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
PROCÉDURE 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:
PROCÉDURE 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
PROCÉDURE 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
PROCÉDURE 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, ...
Note: 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 the compilation errors and the 64-bit compatibility" ("Analysis .. Analysis description", "Compilation" tab).In order for this set of procedures to operate, you must select "Enable the compilation errors for the 64-bit compatibility" ("Analysis description" from then popup menu of the analysis, "Compilation" tab).
Minimum required version
  • Version 11
This page is also available for…
Comments
Click [Add] to post a comment