1 SET_SQLDA Allows a programmer using Dynamic SQL to alter the way the SQLDA (and SQLDA2) and Dynamic SQL statements are processed by Oracle Rdb. 2 Environment You can use the SET SQLDA statement: o In Dynamic SQL as a statement to be dynamically executed 2 Format SET SQLDA -+-> literal ---------+-----> +-> host-variable ---+ sqlda_options = --+-> sqlda_option --+--> +----- , <---------+ sqlda_option = --+-> PADDING n CHARACTERS -----+-> +-> NOPADDING ----------------+ +-> ENABLE enable-option -----+ +-> DISABLE enable-option ----+ +-> dialect-name -------------+ enable-option = --+-> INSERT RETURNING ---+-> +-> NAMED MARKERS ------+ +-> ROWID TYPE ---------+ dialect-name = --+-> SQL99 -----------+-> +-> SQL92 -----------+ +-> SQL89 -----------+ +-> MIA -------------+ +-> SQLV40 ----------+ +-> ORACLE LEVEL1 ---+ +-> ORACLE LEVEL2 ---+ 2 Arguments 3 Literal|Host-Variable Parameter passed to the statement. Must be a literal or a host variable containing one or more SQLDA options (see sqlda_ options syntax diagram for details). If more than one option is specified, they must be separated by commas. 3 sqlda_options One or more keyword clauses. If more than one clause is specified, they must be separated by commas. 3 ENABLE The ENABLE clause activates one of the following behaviors for Dynamic SQL. - INSERT RETURNING - The default behavior of INSERT ... RETURNING when executed by dynamic SQL is to place parameters from the RETURNING INTO clause in to the INPUT SQLDA. This behavior is maintained for backward compatibility. This option allows the programmer to force different (and corrected) behavior for the non-compound use of this statement. NOTE If the INSERT RETURNING statement is included in a compound statement then the parameters are handled correctly. - NAMED MARKERS - as well as traditional parameters markers (?). Dynamic SQL will now accept named, host-variable style parameter markers. See the Usage Notes for further details and examples. - ROWID TYPE - returns DBKEY values as a special type (SQLDA_ ROWID, 455) to make processing of the DBKEY values easier. For instance, in prior releases the SQLDA name field (SQLNAME) for DBKEY entries in the SQLDA was the only way to distinguish these values from other CHAR or VARCHAR columns - it would be either DBKEY or ROWID. If a query renamed the DBKEY column, then the application had no information in the SQLDA to indicate that the CHAR or VARCHAR value was binary data. In all respects, the SQLDA_ROWID type appears as a fixed length string of octets (possibly containing octets of zero which the C language would treat as a NULL terminator for a string). 3 DISABLE The DISABLE clause deactivates one of the specified behaviors for Dynamic SQL. See ENABLE clause for a list of options. 3 ORACLE_LEVEL1|ORACLE_LEVEL2 Either of these options will set the SQLDA to supply enhanced semantics. These options are currently reserved for the use of the OCI Services for Rdb product that is part of Oracle Rdb SQL/Services component. This setting also implicitly enables NAMED MARKERS. 3 PADDING_n_CHARACTERS This option directs SQL to configure the SQLDA with larger CHARACTER VARYING strings than would normally be seen. The value of n is an unsigned numeric literal that specifies the number of characters that are added to the estimated length. Any CHARACTER (CHAR) types are converted to CHARACTER VARYING (VARCHAR). This rule is applied to comparison operators <, <=, >, >=, =, <>, and string functions (STARTING WITH, CONTAINING). 3 NOPADDING This option sets the number of padding characters to 0. This also implies that derived CHARACTER (CHAR) types are not converted to CHARACTER VARYING (VARCHAR) when PADDING CHARACTERS is used. This is the default setting. NOTE Oracle recommends that applications always check for SQLDA_ CHAR and SQLDA_VARCHAR so that the correctly formatted data is made available to SQL. 3 SQL99|SQL92|MIA|SQL89|SQLV40 Any of these options will revert to the default semantic for the SQLDA which includes disabling NAMED MARKERS. 2 Example Example 1: Using the NAMED MARKERS feature This example shows that enabling the NAMED MARKERS feature will allow SQL to promp tfor one value and the displayed Rdb strategy shows that only one variable is used. -> SET SQLDA 'ENABLE NAMED MARKERS'; -> SELECT LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME = :F_NAME AND LAST_NAME <> :F_NAME; in: [0] typ=449 len=46 out: [0] typ=453 len=14 [SQLDA - reading 1 fields] -> Alvin Tables: 0 = EMPLOYEES Conjunct: (0.FIRST_NAME = ) AND (0.LAST_NAME <> ) Get Retrieval sequentially of relation 0:EMPLOYEES 0/FIRST_NAME/Varchar(42/46): Alvin [SQLDA - displaying 1 fields] 0/LAST_NAME: Toliver [SQLDA - displaying 1 fields] 0/LAST_NAME: Dement Example 2: Using the PADDING feature The following example shows that the derived type for the named parameter MI is a SQLDA_CHAR (453) of length 1. The input data ('AA') is truncated on assignment and the incorrect results are returned. By adding a small padding the type is changed to SQLDA_ VARCHAR (449) of length 3 and a correct comparison is performed. -> ATTACH 'filename sql$database'; -> SET SQLDA 'enable named markers, nopadding'; -> SELECT LAST_NAME FROM EMPLOYEES WHERE MIDDLE_INITIAL = :MI; in: [0] typ=453 len=1 out: [0] typ=449 len=18 [SQLDA - reading 1 fields] -> AA [SQLDA - displaying 1 fields] 0/LAST_NAME: Toliver [SQLDA - displaying 1 fields] 0/LAST_NAME: Lengyel [SQLDA - displaying 1 fields] 0/LAST_NAME: Robinson [SQLDA - displaying 1 fields] 0/LAST_NAME: Ames -> SET SQLDA 'padding 2 characters'; -> SELECT LAST_NAME FROM EMPLOYEES WHERE MIDDLE_INITIAL = :MI; in: [0] typ=449 len=7 out: [0] typ=449 len=18 [SQLDA - reading 1 fields] -> AA -> EXIT; Enter statement: Note that the VARCHAR requires an extra 4 bytes for the length information in the SQLDA2 used by the Dynamic SQL testing program.