Scalable Database Server, HiRDB Version 8 UAP Development Guide

[Contents][Index][Back][Next]

Appendix B.2 Expanding the SQL Descriptor Area

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.

Organization of this subsection
(1) Expansion format of the SQL Descriptor Area
(2) SQL Descriptor Area example
(3) SQL Descriptor Area expansion
(4) SQL Descriptor Area operation macros
(5) Expansion format of repetition columns

(1) Expansion format of the SQL Descriptor Area

(a) C

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

1 n indicates the required number (1-30000).

2 Any desired character string can be specified for the structure name (sqlda portion), except that no character string beginning with SQL is allowed.
(b) COBOL

This example shows SQL Descriptor Area expansion when COBOL is used.

01  USQLDA1
  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.

1 Any name can be specified as the name of the set item (USQLDA area); however, a character string that begins with SQL cannot be used for a data item.

2 n indicates the required number (1-30000).

(2) SQL Descriptor Area example

(a) Declaration and area allocation for using the SQL Descriptor Area

The SQL Descriptor Area is declared and allocated in the UAP.

(b) Collection of retrieval item information

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

Notes
  1. When a DESCRIBE statement is executed, binary 0 or the number of retrieval items is set in the SQLD area:
    [Figure]0 is set when the SQL statement that was preprocessed is not a SELECT statement.
    [Figure]Number of retrieval items is set when the SQL statement that was preprocessed is a SELECT statement.
  2. The data code, data length, and maximum elements count of each retrieval item are set in SQLCOD, SQLLEN, and SQLXDIM, respectively.

* In 64-bit mode, this is int.

Explanation
  1. Declares an embedded variable (XCMND) for storing the SQL statements.
  2. Sets the SQL statement in the variable (XCMND).
  3. Specifies the action to be taken if an error occurs after SQL statement execution.
  4. Preprocesses the SQL statements specified as the variable XCMND and assigns an SQL statement identifier (ST1).
  5. Collects the information of items retrieved by the SQL statements (ST1) into the SQL Descriptor Area (DAREA).
(c) Fetching retrieval results with dynamic receive area allocation

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

Notes
Before the FETCH statement is executed, the following information must be set in DAREA:
  • Size of SQLVAR array (SQLN)
  • Number of areas to receive retrieval results (SQLD): executing a DESCRIBE statement sets this value
  • Data type of receive area (SQLCOD): executing a DESCRIBE statement sets this value
  • Data length of receive area (SQLLEN): executing a DESCRIBE statement sets this value.

Explanation
  1. Sets the address of the allocated area in the SQL Descriptor Area (DAREA).
  2. Declares a cursor (CR1) for the SQL statement identifier (ST1).
  3. Opens the cursor (CR1).
  4. Specifies the action to be taken (branching to FEND) at the termination of retrieval.
  5. Advances the cursor (CR1) to the next line, and fetches that line into the area specified by the SQL Descriptor Area (DAREA).
  6. Specifies the processing to be performed on the retrieval result (e.g., editing and output).
  7. Invalidates the action at the termination of retrieval.
  8. Closes the cursor (CR1).
(d) Dynamic allocation of a data area for specifying ? parameter values

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 64-bit mode, this is int.

Explanation
  1. Declares the variable (TNAME) that stores the table name.
  2. Loads the table name from the input data into the variable (TNAME).
  3. Uses the DESCRIBE statement to set the columns count of the table specified in 2 data type, data length, and maximum elements count of each column, as the number of ? parameters, the data type, data length, and maximum elements count of the data area for each ? parameter, respectively, in the SQL Descriptor Area (DAREA).
  4. Allocates data area for each ? parameter.
  5. Sets the address of the allocated area in the SQL Descriptor Area (DAREA).
  6. Creates an INSERT statement for inserting data into the specified table.
  7. Preprocesses the INSERT statement in XCMND and assigns the SQL statement identifier (ST2).
  8. Repeats data insertion on a row basis, setting in the data area, and execution using the EXECUTE statement, as long as data to be inserted exists.
(e) Retrieving DECIMAL data using a FETCH statement

In this example, DECIMAL data is retrieved using a FETCH statement.

  1. Declares a data area and an indicator variable.
     
EXEC  SQL  BEGIN  DECLARE  SECTION ;
 
   SQL TYPE IS DECIMAL(20,0) xdec1 ;     /* Data area       */
   short                     xdec1_i ;   /* Indicator variable  */
 
EXEC  SQL  END  DECLARE  SECTION ;
  2. Sets an SQL Descriptor Area.
     
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              */

(3) SQL Descriptor Area expansion

Table B-4 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 255 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

(4) SQL Descriptor Area operation macros

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
PDUSRSQLD(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.

Legend:
usrsqlda: User-defined SQL Descriptor Area name; any name can be specified.
m: Number of ? parameters (1-30000).
n: Number of ? parameters to be specified or referenced (0-29999).

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 ;

(5) Expansion format of repetition columns

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 E. 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];
}

[Contents][Back][Next]

[Trademarks]

All Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.