Nonstop Database, HiRDB Version 9 UAP Development Guide
The SQL Descriptor Area is allocated by means of a declaration within the UAP.
The format of the SQL Descriptor Area expanded in a source program is shown below, followed by an example.
This example shows SQL Descriptor Area expansion when C is used.
struct { char sqldaid[8]; /* Table ID */ long sqldabc; /* Table length */ short sqln; /* Elements count in SQLVAR array */ short sqld; /* ? parameters count, retrieval items count */ struct sqlvar{ /* Data information area */ unsigned char sqldim; /* Unused */ unsigned char sqlcod; /* Data code */ short sqlxdim; /* Maximum elements count */ union { short sqllen; /* Data length */ struct { unsigned char sqlprcsn; /* Precision */ unsigned char sqlscale; /* Scale */ } s_sqllen; } sqllen; short sqlsys; /* Unused */ unsigned char *sqldata; /* Data area address */ short *sqlind; /* Indicator variable address */ } SQLVAR[n];#1 } sqlda;#2
This example shows SQL Descriptor Area expansion when COBOL is used.
01 USQLDA.#1 02 USQLDAID PIC X(8) VALUE 'SQLDA'. 02 USQLDABC PIC S9(9) COMP. 02 USQLN PIC S9(4) COMP. 02 USQLD PIC S9(4) COMP. 02 USQLVAR OCCURS n TIMES.#2 03 USQLTYPE PIC S9(4) COMP. 03 FILLER REDEFINES USQLTYPE. 04 USQLDIM PIC X(1). 04 USQLCOD PIC X(1). 03 USQLXDIM PIC S9(4) COMP VALUE IS 1. 03 USQLATTR. 04 USQLLEN PIC S9(4) COMP. 04 FILLER REDEFINES USQLLEN. 05 USQLPRCSN PIC X(1). 05 USQLSCALE PIC X(1). 04 USQLSYS PIC S9(4) COMP. 03 FILLER REDEFINES USQLATTR. 04 USQLLOBLEN PIC S9(9) COMP. 03 USQLDATA USAGE IS ADDRESS. 03 USQLIND USAGE IS ADDRESS.
01 USQLDA.#1 02 USQLDAID PIC X(8) VALUE 'SQLDA'. 02 USQLDABC PIC S9(18) COMP. #3 02 USQLN PIC S9(4) COMP. 02 USQLD PIC S9(4) COMP. 02 FILLER PIC X(4). #3 02 USQLVAR OCCURS n TIMES.#2 03 USQLTYPE PIC S9(4) COMP. 03 FILLER REDEFINES USQLTYPE. 04 USQLDIM PIC X(1). 04 USQLCOD PIC X(1). 03 USQLXDIM PIC S9(4) COMP VALUE IS 1. 03 USQLATTR. 04 USQLLEN PIC S9(4) COMP. 04 FILLER REDEFINES USQLLEN. 05 USQLPRCSN PIC X(1). 05 USQLSCALE PIC X(1). 04 USQLSYS PIC S9(4) COMP. 03 FILLER REDEFINES USQLATTR. 04 USQLLOBLEN PIC S9(9) COMP. 03 USQLDATA USAGE IS ADDRESS. 03 USQLIND USAGE IS ADDRESS.
The SQL Descriptor Area is declared and allocated in the UAP.
This next example illustrates collecting retrieval item information. The items identified by numbers in the code are explained as follows.
EXEC SQL BEGIN DECLARE SECTION; .................... 1 struct{ ............................................ 1 long# cmd_len; .................................... 1 char cmd_data[1000]; .............................. 1 }XCMND; ............................................ 1 EXEC SQL END DECLARE SECTION; ...................... 1 XCMND.cmd_len=(long#)sprintf(XCMND.cmd_data, "SELECT*FROM stock WHERE GNO=1") .. 2 EXEC SQL WHENEVER SQLERROR GO TO :RERROR; .......... 3 EXEC SQL PREPARE ST1 FROM :XCMND; .................. 4 EXEC SQL DESCRIBE ST1 INTO :DAREA; ................. 5
In this example, retrieval results are fetched into areas allocated based on the information obtained using a DESCRIBE statement. The items in italics in the figure are explained as follows.
for(n=0;n<DAREA.sqld;n++){ .................... 1 DAREA.SQLVAR[n].sqldata=(unsigned char *)&(X_INT_DATA[n]); .. 1 DAREA.SQLVAR[n].sqlind=&(X_IND[n]); ....... 1 } ............................................. 1 EXEC SQL DECLARE CR1 CURSOR FROM ST1; ......... 2 EXEC SQL OPEN CR1 ............................. 3 EXEC SQL WHENEVER NOT FOUND GO TO:FEND; ....... 4 for(;;){ ...................................... 5 EXEC SQL FETCH CR1 USING DESCRIPTOR:DAREA . 5 : ......................... 5,6 } .............................................. 5 EXEC SQL WHENEVER NOT FOUND CONTINUE; ...... 7 FEND:EXEC SQL CLOSE CR1; ....................... 8
This is an example of inserting data into a dynamically specified table. The items in italics in the figure are explained as follows.
char TNAME[30]; ......................................... 1 scanf("%S",TNAME); ................................. 2 XCMND.cmd_len=(long#)sprint(XCMND.cmd_data, "SELECT * FROM %S",TNAME); ..... 3 EXEC SQL PREPARE ST1 FROM:XCMND; ................... 3 EXEC SQL DESCRIBE ST1 INTO:DAREA; .................. 3 : ......................................... 4 for(n=0;n<DAREA.sqld;n++){ ......................... 5 DAREA.SQLVAR[n].sqldata=(unsigned char *)&(X_INT_DATA[n]); ................................ 5 DAREA.SQLVAR[n].sqlind=&(X_IND[n]); ................ 5 } .................................................. 3 XCMND.cmd_len=(long#)sprit(XCMND.cmd_data, "INSERT INTO %S VALUES(?,...,?)",TNAME); ........................... 6 EXEC SQL PREPARE ST2 FROM:XCMND; .................. 7 for(;;){ ........................................... 8 [Input insertion data (branched to IEND if there is no data)]; ..... 8 [Insert data in the data area and the indicator variable area]; ..... 8 EXEC SQL EXECUTE ST2 USING DESCRIPTOR:DAREA; ..... 8 } .................................................. 8 IEND:
In this example, DECIMAL data is retrieved using a FETCH statement.
EXEC SQL BEGIN DECLARE SECTION ; SQL TYPE IS DECIMAL(20,0) xdec1 ; /* Data area */ short xdec1_i ; /* Indicator variable */ EXEC SQL END DECLARE SECTION ;
PDSQLCOD(usrsqlda, 2)=PDSQL_DECIMAL_I ; /* Sets a data code */ PDSQLPRCSN(usrsqlda, 2)=20 ; /* Sets precision */ PDSQLSCALE(usrsqlda, 2)= 0 ; /* Sets a scale */ PDSQLDATA(usrsqlda, 2)=(void*)xdec1 ; /* Embedded variable address*/ /* Setting */ PDSQLXDIM(usrsqlda, 2)=1; /* Not a repetition column */ PDSQLIND(usrsqlda, 2)=(void*)&xdec1_i ; /* Indicator variable address */ /* Setting */
The following table shows the procedure for expanding the SQL Descriptor Area.
Table B-4 SQL Descriptor Area expansion procedure
Language | When include file is used | When directly coded by user |
---|---|---|
C | #include <pdbsqlda.h> PDUSRSQLDA(n) usrsqlda; | Expansion of SQL Descriptor Area is coded directly. |
COBOL | COPY SQLDA# [ REPLACING 256 BY n ]. |
Expansion of SQL Descriptor Area is coded directly. Level 01 must always be specified first. |
Following is a COBOL coding example in which parameters are specified in the SQL Descriptor Area:
EXEC SQL BEGIN DECLARE SECTION END EXEC 01 IN-CHR1 PIC X(15). 01 IN-IND1 PIC S9(4) COMP. EXEC SQL END DECLARE SECTION END-EXEC COPY SQLDA. : COMPUTE USQLDABC=32 COMPUTE USQLN=1 COMPUTE USQLD=1 COMPUTE USQLDATA(1)=FUNCTION ADDR(IN-CHR1) MOVE SQLCNST0 TO USQLDIM(1) MOVE SQLDCOD197 TO USQLCOD(1) COMPUTE USQLXDIM(1)=1 COMPUTE USQLLEN(1)=15 COMPUTE USQLIND(1)=FUNCTION ADDR(IN-INT1) EXEC SQL EXECUTE ST1 USING DESCRIPTOR :USQLDA END-EXEC
Various macros for declaring the SQLDA and for setting/referencing values are defined in C language. These macros can be used by including the unique header file (pdbsqlda.h) in the UAP. Table B-5 shows the SQL Descriptor Area operation macros, and Table B-6 shows the macros for specifying data types.
Table B-5 SQL Descriptor Area operation macros
Macro | Function |
---|---|
PDUSRSQLDA(m) | Declares a user SQLDA. |
PDSETSIZE(usrsqlda,m) | Specifies the SQLDA size. |
PDSQLN(usrsqlda) | Specifies the ? parameter. |
PDSQLD(usrsqlda) | Specifies/references the ? parameter and the number of retrieval items. |
PDSQLCOD(usrsqlda,n) | Specifies/references the data code. |
PDSQLLEN(usrsqlda,n) | Specifies/references the data length (other than BLOB and decimal number). |
PDSQPRCSN(usrsqlda,n) | Specifies/references the precision (decimal number only). |
PDSQLSCALE(usrsqlda,n) | Specifies/references the scale (decimal number only). |
PDSQLDATA(usrsqlda,n) | Specifies the address of the data area. |
PDSQLIND(usrsqlda,n) | Specifies an indicator variable address. |
PDSQLLOBLEN(usrsqlda,n) | Specifies/references the BLOB data length. |
PDSQLDIM(usrsqldata,n) | Specifies/references the value in unused area. |
PDSQLXDIM(usrsqldata,n) | Specifies/references the maximum number of elements for the repetition structure. |
PDSQLSYS(usrsqldata,n) | Specifies the length of one element that includes the gap in variable-length character string type for the repetition structure or array structure. |
Table B-6 Macros for specifying data types
Macro | Indicator variable | Corresponding data type |
---|---|---|
PDSQL_FLOAT PDSQL_FLOAT_I |
No Yes |
FLOAT |
PDSQL_SMALLFLT PDSQL_SMALLFLT_I |
No Yes |
SMALLFLT |
PDSQL_DECIMAL PDSQL_DECIMAL_I |
No Yes |
DECIMAL |
PDSQL_INTEGER PDSQL_INTEGER_I |
No Yes |
INTEGER |
PDSQL_SMALLINT PDSQL_SMALLINT_I |
No Yes |
SMALLINT |
PDSQL_VARCHAR PDSQL_VARCHAR_I |
No Yes |
VARCHAR |
PDSQL_CHAR PDSQL_CHAR_I |
No Yes |
CHAR |
PDSQL_NVARCHAR PDSQL_NVARCHAR_I |
No Yes |
NVARCHAR |
PDSQL_NCHAR PDSQL_NCHAR_I |
No Yes |
NCHAR |
PDSQL_MVARCHAR PDSQL_MVARCHAR_I |
No Yes |
MVARCHAR |
PDSQL_MCHAR PDSQL_MCHAR_I |
No Yes |
MCHAR |
PDSQL_DATE PDSQL_DATE_I |
No Yes |
DATE |
PDSQL_TIME PDSQL_TIME_I |
No Yes |
TIME |
PDSQL_YEARTODAY PDSQL_YEARTODAY_I |
No Yes |
INTERVAL YEAR TO DAY |
PDSQL_HOURTOSEC PDSQL_HOURTOSEC_I |
No Yes |
INTERVAL HOUR TO SECOND |
PDSQL_ROW PDSQL_ROW_I |
No Yes |
ROW |
PDSQL_BLOB PDSQL_BLOB_I |
No Yes |
BLOB |
PDSQL_TIMESTAMP PDSQL_TIMESTAMP_I |
No Yes |
TIMESTAMP |
PDSQL_BINARY PDSQL_BINARY_I |
No Yes |
BINARY |
PDSQL_BLOB_LOC PDSQL_BLOB_LOC_I |
No Yes |
BLOB locator |
PDSQL_BINARY_LOC PDSQL_BINARY_LOC_I |
No Yes |
BINARY locator |
PDSQL_CVARCHAR PDSQL_CVARCHAR_I |
No Yes |
VARCHAR for C |
Following is a C coding example in which parameters are specified in the SQL Descriptor Area:
#include <pdbsqlda.h> /* Includes header file. */ EXEC SQL BEGIN DECLARE SECTION ; short xint1 ; char xchr1[16] ; EXEC SQL END DECLARE SECTION ; PDUSRSQLDA(2) usrsqlda ; /* Declares SQL Descriptor Area. */ : ClearSqlda(2); /* Clears SQL Descriptor Area */ PDSQLCOD(usrsqlda, 0)=PDSQL_SMALLINT ; /* Sets data code. */ PDSQLLEN(usrsqlda, 0)=sizeof(short) ; /* Sets data code. */ PDSQLDATA(usrsqlda, 0)=(void*)&xint ; /* Sets embedded variable address. */ PDSQLIND(usrsqlda, 0)=NULL ; /* Sets indicator variable address. */ PDSQLCOD(usrsqlda, 1)=PDSQL_CHAR ; /* Sets data code. */ PDSQLLEN(usrsqlda, 1)=sizeof(xchar)-1 ;/* Sets data code. */ PDSQLDATA(usrsqlda, 1)=(void*)xchr ; /* Sets embedded variable address. */ PDSQLIND(usrsqlda, 1)=NULL ; /* Sets indicator variable address. */ EXEC SQL EXECUTE ST1 USING DESCRIPTOR :usrsqlda ;
During compilation, embedded variables in a repetition column are expanded into the structures shown in Table B-7 based on macro definition. The explanation here applies to the C language.
The macro for manipulating a repetition column uses the members of the expanded structures to reference the elements of the repetition column.
If the user wishes to directly set an address in the SQL Descriptor Area by securing an area, the area must be assigned to a language boundary. FLOAT ARRAY explicitly includes a free area for language boundary adjustment. However, when setting an address in the SQL Descriptor Area, you must set it by taking a free area into consideration.
Specify these expansion formats only when adjusting a boundary or determining a size during this type of area allocation. When specifying a repetition column as an embedded variable, do not specify an expansion format. Instead, use the macros described in F. SQL Data Types and Data Descriptions.
Table B-7 Repetition column expansion format
SQL data type | Macro name | Expansion format |
---|---|---|
SMALL INT ARRAY[m] | PD_MV_SINT(m) | struct { long mcnt; short data[m]; } |
INTEGER ARRAY[m] | PD_MV_INT(m) | struct{ long mcnt long data[m]; } |
SMALL FLT ARRAY[m] | PD_MV_SFLT(m) | struct { long mcnt; float data[m]; } |
FLOAT ARRAY[m] | PD_MV_FLT(m) | struct union { double resv1; struct { long resv2; long mcnt; }mcnt_dmy2; } mcnt_dmy1; double data[m]; } |
CHAR(n) ARRAY[m] | PD_MV_CHAR(m, n) | struct { long mcnt; char data[m][(n)+1]; } |
NCHAR(n) ARRAY[m] | PD_MV_NCHAR(m, n) | struct { long mcnt; char data[m][2*(n)+1]; } |
VARCHAR(n) ARRAY[m] | PD_MV_VCHAR(m, n) | struct { long mcnt; struct { short len; char str[n]; } data[m]; } |
PD_MV_CVCHAR(m, n) | struct { long mcnt; char data[m][(n)+1]; } |
|
NVARCHAR(n) ARRAY[m] | PD_MV_NVCHAR(m, n) | struct { long mcnt; struct { short len; char str[2*(n)+1]; } data[m]; } |
DECIMAL [(p[,s])]ARRAY[m] |
PD_MV_DEC(m, p, s) | struct { long mcnt; unsigned char data[m][(p)/2+1]; } |
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.