Use dynamic SQL statements (Using dynamic SQL), SQLSA, and SQLDA

Use dynamic SQL statements (using dynamic SQL), sqlsa, and sqlda

I. General information
Because database applications usually perform a specific activity, you usually know the complete SQL statement when you write and compile the script. When PowerBuilder does not support the statement in Embedded SQL (as with a DDL
Statement) or when
The parameters or the format of the statements are unknown at compile time, the application must build the SQL statements at runtime. this is called dynamic SQL. the parameters used in dynamic SQL statements can change each time
The program is executed.

PowerBuilder allows you to add embedded SQL statements to program code to support database access. However, this embedded SQL statement has some limitations in practical application.

1. only some fixed standard SQL statements are supported, that is, these SQL statements must be fixed during program code compilation and processing. For example: operations on which table fields are fixed in the program code

2. In this way, statements (DDL) defined by these databases, such as creat table and creat database, cannot be executed ).

To overcome the limitations of this method, you can use the dynamic SQL statements provided by PowerBuilder. This database access method can implement functions that cannot be implemented by embedded SQL statements.

1. database definition statements (dd) for database creation and table Creation)

2. dynamic SQL statements allow you to determine what SQL statements to execute during execution. Using dynamic SQL statements allows you to implement Parametric Design for the program, providing great flexibility and versatility.

II. Application Analysis of Dynamic SQL statements
PowerBuilder has four dynamic SQL formats. Each format handles one of the following situations at compile time:

Format When used
-----------------------------
Format 1 Non-result-set statements with no input parameters
Format 2 Non-result-set statements with input parameters
Format 3 Result-set statements in which the input parameters and result-set columns are known at compile time
Format 4 Result-set statements in which the input parameters, the result-set columns or both are unknown at compile time

PowerBuilder provides dynamic SQL statements in four formats, each of which has its own characteristics and functions. The following describes the specific usage of various formats.
(1) first format
This format can be used when you execute an SQL statement that does not contain input parameters and does not return a result set. This format is simple and has fewer operations.
1 syntax
Execute immediate SQLStatement {USING TransactionObject };
SQLStatement is the SQL statement to be executed. It can be used directly by using the SQL statement to be executed in quotation marks, or an SQL statement in the form of string variables. You can use string variables to specify the SQL statement to be executed during specific execution. TransactionObject is the transaction object used by the user. The default value is SQLCA.
2. Application Instance
① Create a database table (base). The expressions of SQL statements are enclosed by quotation marks for direct use.
Execute immediate 'create TABLE base (code char (6), name char (30) 'using sqlca;
② Execute the database record operation, insert a record in the table base, and the SQL statement passes the execution statement through the string variable.
STRING lsSQL
LsSQL = "insert into table base VALUES ('20170901', 'shizuzhong Subbranch ')"
Execute immediate: lsSQL;
(2) second format
You can use the second format to execute SQL statements that contain input parameters but do not return results. This format not only dynamically specifies the SQL statement to be executed, but also dynamically determines the parameter values required by the SQL statement.
1 syntax
PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject };
EXECUTE DynamicstagingArea {USING Parameterlist };
DynamicstagingArea is a data type provided by PowerBuilder. PowerBuilder provides a global variable named SQLSA DynamicstagingArea to save information about the dynamic SQL statements to be executed.
2. Application Instance
Delete records that meet certain conditions in the base table.
STRING lsCode
LsCode = "320101"
Prepare sqlsa from "DELETE base WHERE code = ?";
Execute sqlsa using: lsCode;
(3) third format
The third format can be used when executing an SQL statement that has input parameters and the returned result set format can be determined during compilation. This format syntax is complex, but it has better functions than the previous two methods and can return the result set. When returning results, the third format usually uses a cursor because you do not know how many records meet the filtering conditions.
1 syntax
DECLARE cursor dynamic cursor for DynamicStagingArea;
PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject };
Open dynamic cursor {USING Parameterlist };
FETCH cursor INTO VariableList;
CLOSE cursor;
The cursor is the name of the User-Defined cursor.
2. Application Instance
Read and process all the records with the code field "01" in the base table.
STRING lsSQL, lsCode, lsName, lsFilter
LsFilter = "01"
LsSQL = "SELECT code, name FROM base WHERE substring (code, 3, 2) = ?"
DECLARE cursor_base dynamic cursor for sqlsa;
Prepare sqlsa from: lsSQL;
Open dynamic cursor_base USING: lsFilter;
FETCH cursor_base INTO: lsCode,: lsName;
Do while sqlca. SQLCODE = 0
.
Records that meet the conditions are processed as required
.
FETCH cursor_base INTO: lsCode,: lsName;
LOOP
CLOSE cursor_base;
(4) fourth format
You can use the fourth format when executing an SQL statement that has input parameters and the returned result set format cannot be determined during compilation. Sometimes, when we execute some SQL statements, there are not only parameters but also the number of fields in the returned result set, and each field is of any type of data, which can only be implemented in the fourth format.
1 syntax
PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject };
DESCRIB DynamicStagingArea INTO DynamicDescriptionObject;
DECLARE cursor dynamic cursor for DynamicDescriptionObject;
Open dynamic cursor using descriptor DynamicDescriptionObject;
FETCH cursor using descriptor DynamicDescriptionObject;
CLOSE cursor;
Among them, DynamicDescriptionObject is a data type provided by PowerBuilder. in PowerBuilder, a global data type SQLDA of DynamicDescriptionObject type is provided to store Input and Output Parameters of dynamic SQL statements.
2. Application Instance
Retrieve all the fields in the records that meet the filtering conditions in a table for processing. The table name is transmitted by the string variable during the program running, and the field information is uncertain. Here we assume that the table name passed through the string variable is base.
STRING lsString, lsSQL, lsTable, lsColumn
INT liInt
DATETIME liTime
LsSQL = "SELECT * FROM base WHERE code like ?"
Prepare sqlsa from lsSQL;
Describ sqlsa into sqlda; ∥ SQLDA contains the description of input parameters
DECLARE cursor_base dynamic cursor for sqlsa;
Setdynamicparm (sqlda, 1, "32%") parameters Pass Parameter Values
Open dynamic cursor_base using descriptor sqlda;
Fetch cursor_base using descriptor sqlda;
Do While salca. sqlcode = 0
For liint = 1 to sqlda. numoutputs
Choose case sqlda. outparmtype [liint]
Case typestring!
Lsstring = getdynamicstring (sqlda, liint)
Worker processes fields of this struct type
Case typedatetime
Lsdatetime = getdynamicdatetime (sqlda, liint)
Handler processes fields of this date type
...
Worker processes other types of fields
...
End choose
Next
Then, extract all the fields of a record and process them accordingly.
Fetch cursor_base using descriptor sqlda;
Loop
Close cursor_base;

Iii. Conclusion

From the above descriptions and examples, we can see that dynamic SQL is characterized by strong functionality and flexible usage. It is completely possible to dynamically generate specific SQL statements to be executed in program code, is incomparable to embedded SQL.
Of course, dynamic SQL statements can run a little slower than embedded SQL statements, but with the increase in computer processing speed, this is no longer a problem. Therefore, using dynamic SQL statements is still a very desirable method. Based on our experience in actual programming, correct use of dynamic SQL statements can make the program code concise, flexible, and universal, not only reducing the workload of initial programming, in addition, it reduces the amount of code maintenance in the future and achieves twice the result with half the effort.