Je vois pas ça (SQLSA e SQLDA) dans PB10.5, tu travaille sur quelle version ?   

mattdamon a écrit:

http://pb.besoft.com.cn/support/faq/C20.htm 

aller chercher sur un site en Chine alors que tout est dans l'aide PB

mattdamon a écrit:

Je vois pas ça (SQLSA e SQLDA) dans PB10.5, tu travaille sur quelle version ?   

décidément on ne doit pas avoir la même aide

F1 a écrit:

Using dynamic SQL

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.

Using SQL Anywhere    For information about using dynamic SQL with SQL Anywhere™, see the SQL Anywhere documentation.

Four formats

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
To handle these situations, you use:

·    The PowerBuilder dynamic SQL statements
·    The dynamic versions of CLOSE, DECLARE, FETCH, OPEN, and EXECUTE
·    The PowerBuilder datatypes DynamicStagingArea and DynamicDescriptionArea

About the examples    The examples assume that the default transaction object (SQLCA) has been assigned valid values and that a successful CONNECT has been executed. Although the examples do not show error checking, you should check the SQLCode after each SQL statement.

Dynamic SQL statements

The PowerBuilder dynamic SQL statements are:

DESCRIBE DynamicStagingArea
    INTO DynamicDescriptionArea ;

EXECUTE {IMMEDIATE} SQLStatement
    {USING TransactionObject} ;

EXECUTE DynamicStagingArea
    USING ParameterList ;

EXECUTE DYNAMIC Cursor | Procedure
    USING ParameterList ;

OPEN DYNAMIC Cursor | Procedure
    USING ParameterList ;

EXECUTE DYNAMIC Cursor | Procedure
    USING DESCRIPTOR DynamicDescriptionArea ;

OPEN DYNAMIC Cursor | Procedure
    USING DESCRIPTOR DynamicDescriptionArea ;

PREPARE DynamicStagingArea
    FROM SQLStatement {USING TransactionObject} ;

Two datatypes

DynamicStagingArea  DynamicStagingArea is a PowerBuilder datatype. PowerBuilder uses a variable of this type to store information for use in subsequent statements.
The DynamicStagingArea is the only connection between the execution of a statement and a transaction object and is used internally by PowerBuilder; you cannot access information in the DynamicStagingArea.
PowerBuilder provides a global DynamicStagingArea variable named SQLSA that you can use when you need a DynamicStagingArea variable.

If necessary, you can declare and create additional object variables of the type DynamicStagingArea. These statements declare and create the variable, which must be done before referring to it in a dynamic SQL statement:

DynamicStagingArea dsa_stage1

dsa_stage1 = CREATE DynamicStagingArea

After the EXECUTE statement is completed, SQLSA is no longer referenced.
DynamicDescriptionArea  DynamicDescriptionArea is a PowerBuilder datatype. PowerBuilder uses a variable of this type to store information about the input and output parameters used in Format 4 of dynamic SQL.
PowerBuilder provides a global DynamicDescriptionArea named SQLDA that you can use when you need a DynamicDescriptionArea variable.
If necessary, you can declare and create additional object variables of the type DynamicDescriptionArea. These statements declare and create the variable, which must be done before referring to it in a dynamic SQL statement:

DynamicDescriptionArea dda_desc1

dsa_desc1 = CREATE DynamicDescriptionArea

For more information about SQLDA, see Dynamic SQL Format 4 .

Preparing to use dynamic SQL

When you use dynamic SQL, you must:

·    Prepare the DynamicStagingArea in all formats except Format 1
·    Describe the DynamicDescriptionArea in Format 4
·    Execute the statements in the appropriate order

Preparing and describing the datatypes  Since the SQLSA staging area is the only connection between the execution of a SQL statement and a transaction object, an execution error will occur if you do not prepare the SQL statement correctly.
In addition to SQLSA and SQLDA, you can declare other variables of the DynamicStagingArea and DynamicDescriptionArea datatypes. However, this is required only when your script requires simultaneous access to two or more dynamically prepared statements.

This is a valid dynamic cursor:

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;

PREPARE SQLSA FROM "SELECT emp_id FROM employee" ;

OPEN DYNAMIC my_cursor ;

This is an invalid dynamic cursor. There is no PREPARE, and therefore an execution error will occur:

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;

OPEN DYNAMIC my_cursor ;

Statement order  Where you place the dynamic SQL statements in your scripts is unimportant, but the order of execution is important in Formats 2, 3, and 4. You must execute:

1    The DECLARE and the PREPARE before you execute any other dynamic SQL statements
2    The OPEN in Formats 3 and 4 before the FETCH
3    The CLOSE at the end

If you have multiple PREPARE statements, the order affects the contents of SQLSA.
These statements illustrate the correct ordering:

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA

string sql1, sql2

sql1 = "SELECT emp_id FROM department "&

WHERE salary > 90000"

sql2 = "SELECT emp_id FROM department "&

WHERE salary > 20000"

IF deptId = 200 then

        PREPARE SQLSA FROM :sql1 USING SQLCA ;

ELSE

        PREPARE SQLSA FROM :sql2 USING SQLCA ;

END IF

OPEN DYNAMIC my_cursor ;                                    // my_cursor maps to the

                                    // SELECT that has been

                                    // prepared.

Declaring a procedure with the SQL Native Client database interface

When you connect to Microsoft SQL Server using the PowerBuilder SQL Native Client (SNC) database interface, the syntax for declaring a procedure is:

DECLARE logical_procedure_name PROCEDURE FOR
   [@rc=]procedure_name
   {@param1 = value1 [OUTPUT], @param2 = value2 [OUTPUT], ...}
   {USING transaction_object};

[@rc=] indicates that you want to get the procedure's return value.
Use the keyword OUTPUT or OUT to indicate an output parameter if you want to get the output parameter's value.
If the BindSPInput database parameter is 0, value1, value2,... can be either PowerBuilder script variables or literal values. If BindSPInput is 1, value1, value2,... must be PowerBuilder script variables. If you specify literal values, the SNC interface returns a runtime error.

When you declare a dynamic SQL statement with a procedure, enter a question mark (?) for each IN/OUT parameter in the statement. Value substitution is positional. For examples, see Dynamic SQL Format 3 and 4.

Après avoir lu plus posément la documentation de PB relatives au SQL dynamique, j'ai
compris à quoi servait SQLDA et SQLSA.

Il existe dans PB 4 formats différents de SQL dynamique (dépendant de ce que l'on a
besoin).

Dans le format 2, il est nécessaire d'utiliser SQLSA pour le PREPARE et le EXECUTE, pour
le format 3 et 4, il est nécessaire pour le DECLARE et le PREPARE.

Le SQLDA n'est quand à lui nécessaire que dans le cas du format 4, pour les clauses
DESCRIBE, OPEN DYNAMIC, EXECUTE DYNAMIC, FETCH CURSOR.

Cependant, dans quels cas il serait nécessaire de créer un DynamicStagingArea et un
DynamicDescriptionArea supplémentaire ?

Par avance, merci.