- 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
Stored procedures and queries (HFSQL Client/Server)
Available only with this kind of connection
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: - Create (if necessary) the set of stored procedures in the analysis:
- click
in the quick access buttons. - The window for creating a new element appears: click on "Data" then on "Stored procedure collection".
- Type the name of the set of procedures and validate.
- or 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 data model editor ribbon: under "Analysis", in the "Creation" group, pull down "New" and select "Stored procedure collection". The set of stored procedures is automatically created. This set of procedures is named "Set_<Analysis Name>" by default.
- This set is physically saved in the "Code" subdirectory of analysis directory.
- 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: - Open the data model editor if necessary.
- In the "Analysis" pane, select the "Stored procedures" folder.
- Open the context menu and select "Import a set of procedures". In the window that opens, select the set of procedures to import and validate.
- The following operations are performed:
- If the procedure collection belongs to the current project If the procedure collection belongs to the current project: the project's procedure collection is automatically copied into the "Code" directory of the analysis. The logical name of this procedure is automatically renamed.
Caution: The two procedure collections are dissociated: a change made in the project procedure collection will not be carried over to the analysis procedure collection, and vice versa. - If the procedure collection does not belong to the current project the procedure collection is automatically copied into the "Code" directory of the analysis, and defined as a 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:
- Click
in the quick access buttons.- The window for creating a new element appears: click on "Data" then on "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.
- The query can be used in one of the stored procedures of analysis.
- Method 2:
- Open the data model editor.
- 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.
- The query can be used in one of the stored procedures of analysis.
To import a query: - Open the data model editor if necessary.
- In the "Analysis" pane, select the "Queries"; folder.
- Select "Import a query" in the context menu. In the window that opens, select the query to import and validate.
- The following operations are performed:
- If the query belongs to the current project the project query is automatically copied into the "Code" directory of the analysis, and relocated: 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 vice versa.
- If the query does not belong to the current project the query is automatically copied into the "Code" directory of the analysis, and defined as a 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 or not a function can be used in a stored procedure, simply consult the function's help: if the image
is displayed in the function's title bar, the function can be used in stored procedure code. 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 stored procedure parameter must be of a simple type: an object or array cannot be passed as a parameter. However, Serialize can be used to pass an array as parameter to a stored procedure. - Classes, states, windows, pages, etc. cannot be used in stored procedures.
- 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.Application | Client application that started the current stored procedure. | HRuntimeInfo.Computer | Computer of the user who started the current stored procedure (IP address or name of computer). | HRuntimeInfo.Database | Database used by the client application that started the stored procedure. | HRuntimeInfo.Login | Login 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:
- You have the ability to save temporary files in a server directory and to clear this directory. The following functions can be used:
- To handle the data files, you must use HDeclareExternal.
For example:
PROCEDURE NameOfStoredProcedure()
IF NOT HFileExist(ZIPCODES) THEN
IF NOT HDeclareExternal("ZIPCODES.FIC", "ZIPCODES") THEN
RETURN HErrorInfo()
END
END
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: - 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".
- Go to the "Compilation" tab.
- Check "Enable the compilation errors for the 64-bit compatibility".
- Validate.
Testing a stored procedure To test a stored procedure: - In the analysis pane, select the stored procedure whose test must be run.
- Right-click to open the context menu of the stored procedure and select "Test procedure".
- 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 run several times by modifying the parameters.
- 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 test a stored procedure, you must have debugging rights on the database.
- The traces used in the stored procedures are displayed in the Debugger trace pane.
- The code of the stored procedure may contain breakpoints: the debugger will then be launched.
- 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: stored procedures and/or queries are deployed on the chosen server, but the analysis is not updated. This operation is recommended for large-sized analyses whose generation can take quite a long time.
Warning: if an application is deployed on the server, it may no longer function after this test deployment. This option must be used on a test server. - réellement: the analysis must be re-generated and the data files automatically modified when the client version is installed.
Deploying for test To run the test of modifications performed in a stored procedure: - In the data model editor, on the "Analysis" tab, in the "Analysis" group, expand "Synchronization" and select "Update stored elements (HFSQL Client/Server)".
- 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.
- Validate the update.
Note To deploy for tests, you can also generate the analysis and accept automatic data modification. Deploying in real mode To update a Client/Server application that uses stored procedures, you must: - 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.
- Create the Customer version by requesting automatic modification of data files: in the "Project" pane, in the "Generation" group, scroll down to "Setup procedure" and select "Create installation procedure".
Note: by simply modifying the code of stored procedures or analysis queries, automatic modification of data files is proposed. - 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.
Attention: In the latter case, if the update has been performed, users logged in before the update will use the next time a stored procedure is run:- the new code of stored procedure,
- the global variables with the values defined before the update.
Note If a procedure from the procedure collection is running, the update is blocked until the procedure has finished running (function HUpdateSet is blocking). 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 an 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 obtain sales figures for all customers in department 34. The turnover is calculated by a stored procedure named nReturnTO.
HExecuteSQLQuery("TOCustomer75", "SELECT CustomerName, ZipCode, " + ...
"nReturnTO(Customer.CustomerNumber,'20061201') " + ...
"as Total from Customer where zipcode like '75%')")
PROCEDURE nReturnTO(nCustomerNum, Deadline)
nTotal is currency
HFilter(Orders, CustomerNum, hMinVal, hMaxVal, "OrderDate >= " + Deadline + "")
HReadSeekFirst(Orders, CustomerNum, nCustomerNum)
WHILE HFound(Orders)
nTotal += Orders.TotalIOT
HReadNext(Orders, CustomerNum)
END
RETURN nTotal
Example 2: Executing 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(sdProcStock))
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()
...
HExecuteProcedure(AConnection, AStoredProcedure)
Server application:
PROCEDURE AStoredProcedure
...
RETURN UnknownElement
The exception is displayed on the client computer only. It contains the following information: - Main error: information concerning the client: "Error reported by the XXX server", Client_Procedure, Line 10, ...
- Sub-Erreur: information concerning the server part: "Identificateur ElementInconnu inconnu", Procédure UneProcédure, Ligne 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()
...
HExecuteProcedure(AConnection, AStoredProcedure)
Server application:
PROCEDURE AStoredProcedure
...
HExecuteSQLQuery(AQuery, "SELECT ProcedureCalculation(Fic.Itm) FROM Fic")
FOR EACH AQuery
...
END
PROCEDURE CalculationProcedure(param)
...
RETURN UnknownElement
The exception is displayed on the client computer only. It contains the following information: - Main error: information concerning the client: "Error reported by the XXX server", Client_Procedure, Line 10, ...
- Sub-Error 1: information from the server side concerning the StoredOneProcedure: "Error during query execution", StoredOneProcedure, line 5, ...
- Sub-Error 2: information from the server side concerning the ProcedureCalculation procedure: "Identifier ElementUnknown unknown", ProcedureCalculation procedure, line 4, ...
Note: To obtain the last sub-error (i.e. the first error triggered on the HFSQL server) directly, use the following syntax:
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 determine whether the XXX procedure should be executed 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 database to be used. - 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 be used - The procedure is used in several sets of procedures.
75001: Database 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 during execution of stored procedure This error occurs when the stored procedure contains WLanguage errors. 75004: Element XXX does not exist The stored procedure or the stored query does not exist. 75005: Element XXX cannot be updated. The stored procedure or the stored query is currently used. To update this element, disconnect the users. 75006: Collection of procedures incompatible with 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).
This page is also available for…
|
|
|