[PB] dynamic SQL statements

A dynamic SQL statement is a part of the SQL statement that can be determined only when it is run. Such an SQL statement can be used to design a interface for better interaction with users. This is because it is much better to leave parameter determination to the user than to be specified by developers during programming. In addition, PowerBuilder does not support embedded execution of some SQL statements, such as Create Table and Drop Table. You need to submit these SQL statements to the DBMS for execution, dynamic SQL statements are executed by DBMS. These are the reasons for understanding dynamic SQL statements.

There are four types of dynamic SQL statements:

L no input parameter or result set;

L there are input parameters but no result set;

L columns of parameters and result sets are known during compilation;

L developers do not know the parameters and result set.

10.2.1 type 1

This type of dynamic SQL statement is often used to execute DDL or other SQL statements dedicated to the database. Syntax format:

Execute immediate SQLStatement {USING TransactionObject };

SQLStatement is a string and its content is a valid SQL statement. TransactionObject is the name of the transaction object. Braces indicate that the clause can be omitted. If this clause is omitted, SQLCA is used. The following is an example of creating and deleting a data table:

String MySQLMySQL = "drop table employee"

Execute immediate: MySQL using sqlca;

10.2.2 type 2

This type of dynamic SQL statement is used when the number of known parameters is run and no return value is returned. This type of dynamic SQL statement can also process data operation statements that need to define parameters at runtime. The syntax format is:

PREPARE DynamicStagingArea FROM SQLStatement

{USING TransactionObject };

EXECUTE DynamicStagingAreaUSING {ParameterList };

DynamicStagingArea is a DynamicStagingArea variable. The default global variable of this type is SQLSA. SQLStatement is a String type constant or variable. Its content is a valid SQL statement, in SQL statements, question marks represent the required parameters. During execution, the question mark is replaced by the value represented by the USING clause in the EXECUTE statement. TransactionObject is the transaction object name, and braces indicate that the clause can be omitted, if this parameter is omitted, use SQLCA. ParameterList is the parameter list, which can be the attributes of variables, constants, or controls. Each parameter corresponds to the question mark in SQLStatement. The dynamic policy area is used to prepare SQL statements and the number of required parameters. Its Attributes cannot be accessed by the application at runtime, and SQLSA is the default dynamic policy area variable. The following is an example of a dynamic SQL statement that deletes employee information numbered 56:

Int Emp_id_var = 56

Prepare sqlsa from "delete from employee WHERE emp_id =? ";

Execute sqlsa using: Emp_id_var;

Another example is:

Prepare SQLSA from

"Insert inTo employee (emp_id, manager_id) value (?,?) "

Execute SQLSA using: ls_EmpId,: sle_manager.text;

10.2.3 Type 3

This type of dynamic SQL statements may be used more frequently than the first type. It is often used to handle the number of parameters and the number of result sets that are known during compilation, it can be divided into two types: cursor and stored procedure. The syntax in the form of cursors and the order in the program are as follows:

DECLARE Cursor dynamic cursor for DynamicStagingArea;

PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject };

Open dynamic Cursor {USING ParameterList };

FETCH Cursor | INTO HostVariableList };

CLOSE Cursor;

The format and order of the third type of dynamic SQL statement used in the stored procedure are similar to the preceding syntax, but the EXECUTE statement is used to replace the preceding OPEN statement. The syntax format is:

DECLARE Procedure dynamic procedurefor DynamicStagingArea;

PREPARE DynamicStagingArea FROM SQLStatement

{USING TransactionObject };

Execute dynamic Procedure {USING ParameterList };

FETCH ProcedureINTO HostVariableList };

CLOSE Procedure;

Among them, Cursor and Procedure are the Cursor name and process name respectively; DynamicStagingArea is the dynamic policy zone variable, usually using the predefined global variable SQLSA; SQLStatement is a string (both constants and variables can be, A colon (:) is added before the variable name. The content is a valid SQL statement and the question mark is used to represent the parameter. ParameterList is the parameter list corresponding to the question mark in SQLStatement; hostVariableList is the main variable of PowerScript (that is, the variable of PowerScript with a colon Before it); TransactionObject is the name of the transaction object, which saves time to use SQLCA.

The DECLARE statement describes the dynamic cursor or dynamic process. The PREPARE statement prepares the dynamic policy area. The OPEN or EXECUTE statement opens the dynamic cursor or executes the dynamic process. The FETCH statement reads a row of data, if you want to read multiple rows of data, you must execute the FETCH statement repeatedly. Finally, the CLOSE statement closes the dynamic cursor or dynamic process. The usage of the FETCH and CLOSE statements is the same as that described in the previous section. The following is an application example of the third type of dynamic SQL statement, which gets the employee whose nationality is "Beijing:

DECLARE my_cursor dynamic cursor for sqlsa;

Int Emp_id_var

String SQLstatement, Emp_state_var = "Beijing"

SQLstatament = "SELECT emp_id FROM employee WHERE emp_state =? "

Prepare sqlsa from: SQLstatement;

Open dynamic my_cursor using: Emp_state_var;

FETCH my_cursor INTO: Emp_id_var;

CLOSE my_cursor;

In this example, the necessary SQL statement execution status check is omitted. After other SQL statements are executed in addition to the DECLARE statement, check the SQLCode attribute of the transaction object to determine whether the SQL statement is successfully executed.

This type of dynamic SQL statement can be used to create a common script for adding data to a list box or a drop-down list box.

//////////////////////////////////////// //////////////////////////////////////// //////////////////////////////////////

// Function name wf_AddItem (dropdownlistbox fo_obj, string fs_ SQL)

// Parameter: fo_obj is the drop-down list box, And fs_ SQL is the SQL statement.

// Return value: None

// Function: Use the SQL statement specified by the parameter to add data to the specified drop-down list box.

//////////////////////////////////////// //////////////////////////////////////// ////////////////////////////////////

String ls_item

 

Declare item_cur Dynamic Cursor For SQLSA; // defines the Dynamic Cursor

Prepare SQLSA From: fs_ SQL Using SQLCA;

Open Dynamic item_cur; // Open the Dynamic Cursor

Fetch item_cur into: ls_item; // obtain data

 

Fo_obj.setredraw (false) // disable refreshing the drop-down list box

 

Do While sqlca. sqlcode = 0

Fo_obj.additem (ls_item) // Add a project to the drop-down list box

Fetch item_cur into: ls_item;

Loop

 

Fo_obj.setredraw (true) // refresh the drop-down list box

 

Close item_cur; // Close the Dynamic Cursor

10.2.4 type 4

The fourth type of dynamic SQL statements is the most complex and has the strongest functions. It can process SQL statements that do not know the parameters and result set in programming. Similar to the third type of dynamic SQL statement, the fourth type of dynamic SQL statement has two forms: one for cursor processing and the other for stored procedures, the difference is that the OPEN statement is used for cursor processing instead of the EXECUTE statement, while the EXECUTE statement is used but not the OPEN statement when the stored procedure is used. To save space, the syntax for the fourth type of dynamic SQL statements is provided below. The Cursor is for the Cursor and Procedure is for the stored Procedure:

DECLARE Cursor | ProcedureDYNAMIC CURSOR |

PROCEDUREFOR DynamicStagingArea;

PREPARE DynamicStagingArea FROM SQLStatement

{USING TransactionObject };

DESCRIBE DynamicStagingArea INTO DynamicDescriptionArea;

Open dynamic CursorUSING DESCRIPTOR DynamicDescriptionArea };

Execute dynamic ProcedureUSING DESCRIPTOR DynamicDescriptionArea;

FETCH Cursor | ProcedureUSING DESCRIPTOR DynamicDescriptionArea;

CLOSE Cursor | Procedure;

Among them, Cursor and Procedure are the Cursor name and process name respectively; DynamicStagingArea is the dynamic policy zone variable, usually using the predefined global variable SQLSA; SQLStatement is a string (both constants and variables can be, A colon (:) is added before the variable name. The content is a valid SQL statement and the question mark is used to represent the parameter. DynamicDescriptionArea is a variable in the dynamic description area, this type of variable is used to describe the input and output parameters in the forth type of dynamic SQL statements. Generally, the system predefines the global variable SQLDA. TransactionObject is the transaction object name, which saves time and uses SQLCA. The fourth type of dynamic SQL statement uses the dynamic description area object variable. The four attributes of the object variable NumInputs, InParmType, numOutputs and OutParmType can obtain information about the number of input parameters, input parameter type, output parameter number, and output parameter type. InParmType is an array, and each element corresponds to a question mark in the SQL statement in turn; outParmType is also an array. Each element corresponds to an output parameter. The Data Types of InParmType and OutParmType are Enumeration type ParmType. The value range is the values listed in the "applicable parameter types" column in table 10-6.

Table 10-6 functions that obtain the fourth type of dynamic SQL output parameters

Letter count	Applicable parameter types
Getdynamicnumber ()	TypeInteger! TypeDecimal! TypeDouble! TypeLong! TypeReal! TypeBoolean! TypeUnsignedInteger! TypeUnsignedLong!
GetDynamicString ()	TypeString!
GetDynamicDate ()	TypeDate!
GetDynamicTime ()	TypeTime!
GetDynamicDateTime ()	TypeDateTime!

 

You can use the SetDynamicParm () function of this object variable to set specific input parameter values. Use the object function in Table 10-6 to obtain the value of the output parameter (actually the data returned by the SQL statement). Each function is specific to the data type.

In the fourth type of dynamic SQL statement syntax format, the execution order of each statement is very important. The execution of the last statement makes sense only when the previous statement is successfully executed. Therefore, except for the DECLARE statement, after other statements are executed, check the SQLCode attribute of the transaction object to determine whether the execution of the current SQL statement is successful. The FETCH statement can be called multiple times to read multiple data records. After reading one data record, the choose case is usually used in the loop statement to determine

The type of the output parameter is obtained by using the object function in Table 10-6. The following is an application example of the fourth type of dynamic SQL statements. The process of checking the sqlcode attribute of the transaction object is omitted in actual programming (that is, checking whether the SQL statement execution is successful ):

String, ls_sqlstatement

Int, li_count = 0

Ls_sqlstatement = "select emp_id from employee"

Prepare sqlsa from: ls_sqlstatement;

Describe sqlsa into sqlda;

Declare my_cursor Dynamic Cursor for sqlsa;

Open dynamic my_cursor using descriptor sqlda;

Fetch my_cursor using descriptor sqlda;

If sqlca. sqlcode = 100 then

Mesasgebox ("prompt", "the specified data is not found! ")

Close my_cur;

Return

End if

Do

Li_count ++

// When the fetch statement is successfully executed, the dynamic description area sqlda contains the result set

// Execute the fetch statement repeatedly to obtain the remaining data.

// Sqlda. numoutputs contains the number of output parameters.

// The sqlda. outparmtype array contains the data types of parameters,

// For example, typeinteger !, Or typestring! And so on

// Use the choose case statement to call different object functions for different output parameter types

// Obtain the value of the corresponding parameter.

Choose case sqlda. outparmtype [1]

Case typestring!

Stringvar = getdynamicstring (sqlda, 1)

Case typeinteger!

Intvar = getdynamicnumber (sqlda, 1)

End choose

Loop while li_count <> sqlda. numoutputs

Close my_cursor;