SQL リンケージ規約の例
診断情報をパラメーターに入れて渡し、NULL 値を許可するには、SQL リンケージ規約を指定します。
例
以下の例は、アセンブラー、C、COBOL、 または PL/I のストアード・プロシージャーが SQL リンケージ規約 を使用してパラメーターを受け取る方法を示しています。 また、これらの例は、ストアード・プロシージャーがどのように DBINFO 構造を受け取るかについても 示しています。
これらの例では、C アプリケーションに以下のようなパラメーター宣言 と CALL ステートメントがあるものとします。
/************************************************************/
/* Parameters for the SQL statement CALL */
/************************************************************/
long int v1;
char v2[10]; /* Allow an extra byte for */
/* the null terminator */
/************************************************************/
/* Indicator variables */
/************************************************************/
short int ind1;
short int ind2;
⋮
ind1 = 0; /* Remember to initialize the */
/* input parameter's indicator*/
/* variable before executing */
/* the CALL statement */
EXEC SQL CALL B (:v1 :ind1, :v2 :ind2);
⋮CREATE PROCEDURE ステートメントのパラメーターは以下のように定義されます。
IN V1 INT, OUT V2 CHAR(9)アセンブラーの例
次の図に、アセンブラー言語で書かれたストアード・プロシージャーがこれらのパラメーターを受け取る方法を示します。
*******************************************************************
* CODE FOR AN ASSEMBLER LANGUAGE STORED PROCEDURE THAT USES
*
* THE SQL LINKAGE CONVENTION. *
*******************************************************************
B CEEENTRY AUTO=PROGSIZE,MAIN=YES,PLIST=OS
USING PROGAREA,R13
*******************************************************************
* BRING UP THE LANGUAGE ENVIRONMENT.
*
*******************************************************************
⋮
*******************************************************************
* GET THE PASSED PARAMETER VALUES. THE SQL LINKAGE *
* CONVENTION IS AS FOLLOWS:
*
* ON ENTRY, REGISTER 1 POINTS TO A LIST OF POINTERS. IF N
*
* PARAMETERS ARE PASSED, THERE ARE 2N+4 POINTERS. THE FIRST
*
* N POINTERS ARE THE ADDRESSES OF THE N PARAMETERS, JUST AS
*
* WITH THE GENERAL LINKAGE CONVENTION. THE NEXT N POINTERS ARE
*
* THE ADDRESSES OF THE INDICATOR VARIABLE VALUES. THE LAST
*
* 4 POINTERS (5, IF DBINFO IS PASSED) ARE THE ADDRESSES OF
*
* INFORMATION ABOUT THE STORED PROCEDURE ENVIRONMENT AND
*
* EXECUTION RESULTS.
*
*******************************************************************
L R7,0(R1) GET POINTER TO V1
MVC LOCV1(4),0(R7) MOVE VALUE INTO LOCAL COPY OF V1
L R7,8(R1) GET POINTER TO 1ST INDICATOR VARIABLE
MVC LOCI1(2),0(R7) MOVE VALUE INTO LOCAL STORAGE
L R7,20(R1) GET POINTER TO STORED PROCEDURE
NAME
MVC LOCSPNM(20),0(R7) MOVE VALUE INTO LOCAL STORAGE
L R7,24(R1) GET POINTER TO DBINFO
MVC LOCDBINF(DBINFLN),0(R7)
* MOVE VALUE INTO LOCAL STORAGE
LH R7,LOCI1 GET INDICATOR VARIABLE FOR V1
LTR R7,R7 CHECK IF IT IS NEGATIVE
BM NULLIN IF SO, V1 IS NULL
⋮
L R7,4(R1) GET POINTER TO V2
MVC 0(9,R7),LOCV2 MOVE A VALUE INTO OUTPUT VAR V2
L R7,12(R1) GET POINTER TO INDICATOR VAR 2
MVC 0(2,R7),=H'0' MOVE ZERO TO V2'S INDICATOR VAR
L R7,16(R1) GET POINTER TO SQLSTATE
MVC 0(5,R7),=CL5'xxxxx' MOVE xxxxx TO SQLSTATE
⋮
CEETERM RC=0*******************************************************************
* VARIABLE DECLARATIONS AND EQUATES
*
*******************************************************************
R1 EQU 1 REGISTER 1
R7 EQU 7 REGISTER 7
PPA CEEPPA , CONSTANTS DESCRIBING THE CODE BLOCK
LTORG , PLACE LITERAL POOL HERE
PROGAREA DSECT
ORG *+CEEDSASZ LEAVE SPACE FOR DSA FIXED PART
LOCV1 DS F LOCAL COPY OF PARAMETER V1
LOCV2 DS CL9 LOCAL COPY OF PARAMETER V2
LOCI1 DS H LOCAL COPY OF INDICATOR 1
LOCI2 DS H LOCAL COPY OF INDICATOR 2
LOCSQST DS CL5 LOCAL COPY OF SQLSTATE
LOCSPNM DS H,CL27 LOCAL COPY OF STORED PROC NAME
LOCSPSNM DS H,CL18 LOCAL COPY OF SPECIFIC NAME
LOCDIAG DS H,CL1000 LOCAL COPY OF DIAGNOSTIC DATA
LOCDBINF DS 0H LOCAL COPY OF DBINFO DATA
DBNAMELN DS H DATABASE NAME LENGTH
DBNAME DS CL128 DATABASE NAME
AUTHIDLN DS H APPL AUTH ID LENGTH
AUTHID DS CL128 APPL AUTH ID
ASC_SBCS DS F ASCII SBCS CCSID
ASC_DBCS DS F ASCII DBCS CCSID
ASC_MIXD DS F ASCII MIXED CCSID
EBC_SBCS DS F EBCDIC SBCS CCSID
EBC_DBCS DS F EBCDIC DBCS CCSID
EBC_MIXD DS F EBCDIC MIXED CCSID
UNI_SBCS DS F UNICODE SBCS CCSID
UNI_DBCS DS F UNICODE DBCS CCSID
UNI_MIXD DS F UNICODE MIXED CCSID
ENCODE DS F PROCEDURE ENCODING SCHEME
RESERV0 DS CL20 RESERVED
TBQUALLN DS H TABLE QUALIFIER LENGTH
TBQUAL DS CL128 TABLE QUALIFIER
TBNAMELN DS H TABLE NAME LENGTH
TBNAME DS CL128 TABLE NAME
CLNAMELN DS H COLUMN NAME LENGTH
COLNAME DS CL128 COLUMN NAME
RELVER DS CL8 DBMS RELEASE AND VERSION
RESERV1 DS CL2 RESERVED
PLATFORM DS F DBMS OPERATING SYSTEM
NUMTFCOL DS H NUMBER OF TABLE FUNCTION COLS USED
RESERV2 DS CL26 RESERVED
TFCOLNUM DS A POINTER TO TABLE FUNCTION COL LIST
APPLID DS A POINTER TO APPLICATION ID
RESERV3 DS CL20 RESERVED
DBINFLN EQU *-LOCDBINF LENGTH OF DBINFO
⋮
PROGSIZE EQU *-PROGAREA
CEEDSA , MAPPING OF THE DYNAMIC SAVE AREA
CEECAA , MAPPING OF THE COMMON ANCHOR AREA
END BC の例
次の図に、C 言語の主プログラムとして書かれたストアード・プロシージャーがこれらのパラメーターを受け取る方法を示します。
#pragma runopts(plist(os))
#include <;stdlib.h>
#include <;stdio.h>
main(argc,argv)
int argc;
char *argv[];
{
int parm1;
short int ind1;
char p_proc[28];
char p_spec[19];
/***************************************************/
/* Assume that the SQL CALL statement included */
/* 3 input/output parameters in the parameter list.*/
/* The argv vector will contain these entries: */
/* argv[0] 1 contains load module */
/* argv[1-3] 3 input/output parms */
/* argv[4-6] 3 null indicators */
/* argv[7] 1 SQLSTATE variable */
/* argv[8] 1 qualified proc name */
/* argv[9] 1 specific proc name */
/* argv[10] 1 diagnostic string */
/* argv[11] + 1 dbinfo */
/* ------ */
/* 12 for the argc variable */
/***************************************************/
if argc<>12 {
⋮
/* We end up here when invoked with wrong number of parms */
} /***************************************************/
/* Assume the first parameter is an integer. */
/* The following code shows how to copy the integer*/
/* parameter into the application storage. */
/***************************************************/
parm1 = *(int *) argv[1];
/***************************************************/
/* We can access the null indicator for the first */
/* parameter on the SQL CALL as follows: */
/***************************************************/
ind1 = *(short int *) argv[4];
/***************************************************/
/* We can use the following expression */
/* to assign 'xxxxx' to the SQLSTATE returned to */
/* caller on the SQL CALL statement. */
/***************************************************/
strcpy(argv[7],"xxxxx/0");
/***************************************************/
/* We obtain the value of the qualified procedure */
/* name with this expression. */
/***************************************************/
strcpy(p_proc,argv[8]);
/***************************************************/
/* We obtain the value of the specific procedure */
/* name with this expression. */
/***************************************************/
strcpy(p_spec,argv[9]);
/***************************************************/
/* We can use the following expression to assign */
/* 'yyyyyyyy' to the diagnostic string returned */
/* in the SQLDA associated with the CALL statement.*/
/***************************************************/
strcpy(argv[10],"yyyyyyyy/0");
⋮
}次の図に、C 言語のサブプログラムとして書かれたストアード・プロシージャーがこれらのパラメーターを受け取る方法を示します。
#pragma linkage(myproc,fetchable)
#include <stdlib.h>
#include <stdio.h>
#include <sqludf.h>
void myproc(*parm1 int, /* assume INT for PARM1
*/
parm2 char[11], /* assume CHAR(10) parm2
*/
⋮
*p_ind1 short int, /* null indicator for parm1
*/
*p_ind2 short int, /* null indicator for parm2
*/
⋮
p_sqlstate char[6], /* SQLSTATE returned to DB2
*/
p_proc char[28], /* Qualified stored proc name
*/
p_spec char[19], /* Specific stored proc name
*/
p_diag char[1001], /* Diagnostic string
*/
struct sqludf_dbinfo *udf_dbinfo); /* DBINFO
*/
{
int l_p1;
char[11] l_p2;
short int l_ind1;
short int l_ind2;
char[6] l_sqlstate;
char[28] l_proc;
char[19] l_spec;
char[71] l_diag;
sqludf_dbinfo *ludf_dbinfo;
⋮
/***************************************************/
/* Copy each of the parameters in the parameter */
/* list into a local variable, just to demonstrate */
/* how the parameters can be referenced. */
/***************************************************/
l_p1 = *parm1;
strcpy(l_p2,parm2);
l_ind1 = *p_ind1;
l_ind1 = *p_ind2;
strcpy(l_sqlstate,p_sqlstate);
strcpy(l_proc,p_proc);
strcpy(l_spec,p_spec);
strcpy(l_diag,p_diag);
memcpy(&ludf_dbinfo,udf_dbinfo,sizeof(ludf_dbinfo));
⋮
}COBOL 例
次の図に、COBOL 言語で書かれたストアード・プロシージャーがこれらのパラメーターを受け取る方法を示します。
CBL RENT
IDENTIFICATION DIVISION.
⋮
DATA DIVISION.
⋮
LINKAGE SECTION.
* Declare each of the parameters
01 PARM1 …
01 PARM2 …
⋮
* Declare a null indicator for each parameter
01 P-IND1 PIC S9(4) USAGE COMP.
01 P-IND2 PIC S9(4) USAGE COMP.
⋮
* Declare the SQLSTATE that can be set by stored proc
01 P-SQLSTATE PIC X(5).
* Declare the qualified procedure name
01 P-PROC.
49 P-PROC-LEN PIC 9(4) USAGE BINARY.
49 P-PROC-TEXT PIC X(27).
* Declare the specific procedure name
01 P-SPEC.
49 P-SPEC-LEN PIC 9(4) USAGE BINARY.
49 P-SPEC-TEXT PIC X(18).
* Declare SQL diagnostic message token
01 P-DIAG.
49 P-DIAG-LEN PIC 9(4) USAGE BINARY.
49 P-DIAG-TEXT PIC X(1000).
*********************************************************
* Structure used for DBINFO *
*********************************************************
01 SQLUDF-DBINFO.
* Location name length
05 DBNAMELEN PIC 9(4) USAGE BINARY.
* Location name
05 DBNAME PIC X(128).
* authorization ID length
05 AUTHIDLEN PIC 9(4) USAGE BINARY.
* authorization ID
05 AUTHID PIC X(128).
* environment CCSID information
05 CODEPG PIC X(48).
05 CDPG-DB2 REDEFINES CODEPG.
10 DB2-CCSIDS OCCURS 3 TIMES.
15 DB2-SBCS PIC 9(9) USAGE BINARY.
15 DB2-DBCS PIC 9(9) USAGE BINARY.
15 DB2-MIXED PIC 9(9) USAGE BINARY.
10 ENCODING-SCHEME PIC 9(9) USAGE BINARY.
10 RESERVED PIC X(20).* other platform-specific
deprecated CCSID structures not included here
* schema name length
05 TBSCHEMALEN PIC 9(4) USAGE BINARY.
* schema name
05 TBSCHEMA PIC X(128).
* table name length
05 TBNAMELEN PIC 9(4) USAGE BINARY.
* table name
05 TBNAME PIC X(128).
* column name length
05 COLNAMELEN PIC 9(4) USAGE BINARY.
* column name
05 COLNAME PIC X(128).
* product information
05 VER-REL PIC X(8).
* reserved
05 RESD0 PIC X(2).
* platform type
05 PLATFORM PIC 9(9) USAGE BINARY.
* number of entries in the TF column list array (tfcolumn, below)
05 NUMTFCOL PIC 9(4) USAGE BINARY.
* reserved
05 RESD1 PIC X(26).
* tfcolumn will be allocated dynamically of it is defined
* otherwise this will be a null pointer
05 TFCOLUMN USAGE IS POINTER.
* application identifier
05 APPL-ID USAGE IS POINTER.
* reserved
05 RESD2 PIC X(20).
*
⋮
PROCEDURE DIVISION USING PARM1, PARM2,
P-IND1, P-IND2,
P-SQLSTATE, P-PROC, P-SPEC, P-DIAG,
SQLUDF-DBINFO.
⋮PL/I 例
次の図に、PL/I 言語で書かれたストアード・プロシージャーがこれらのパラメーターを受け取る方法を示します。
*PROCESS SYSTEM(MVS);
MYMAIN: PROC(PARM1, PARM2, ...,
P_IND1, P_IND2, ...,
P_SQLSTATE, P_PROC, P_SPEC, P_DIAG, DBINFO)
OPTIONS(MAIN NOEXECOPS REENTRANT);
DCL PARM1 ... /* first parameter */
DCL PARM2 ... /* second parameter */
⋮
DCL P_IND1 BIN FIXED(15);/* indicator for 1st parm */
DCL P_IND2 BIN FIXED(15);/* indicator for 2nd parm */
⋮
DCL P_SQLSTATE CHAR(5); /* SQLSTATE to return to DB2 */
DCL 01 P_PROC CHAR(27) /* Qualified procedure name */
VARYING;
DCL 01 P_SPEC CHAR(18) /* Specific stored proc */
VARYING;
DCL 01 P_DIAG CHAR(1000) /* Diagnostic string */
VARYING;
DCL DBINFO PTR;
DCL 01 SP_DBINFO BASED(DBINFO),
/* Dbinfo */
03 UDF_DBINFO_LLEN BIN FIXED(15), /* location length */
03 UDF_DBINFO_LOC CHAR(128), /* location name */
03 UDF_DBINFO_ALEN BIN FIXED(15), /* auth ID length */
03 UDF_DBINFO_AUTH CHAR(128), /* authorization ID */
03 UDF_DBINFO_CCSID, /* CCSIDs for DB2 for z/OS */
05 R1 BIN FIXED(15), /* Reserved */
05 UDF_DBINFO_ASBCS BIN FIXED(15), /* ASCII SBCS CCSID */
05 R2 BIN FIXED(15), /* Reserved */
05 UDF_DBINFO_ADBCS BIN FIXED(15), /* ASCII DBCS CCSID */
05 R3 BIN FIXED(15), /* Reserved */
05 UDF_DBINFO_AMIXED BIN FIXED(15), /* ASCII MIXED CCSID */
05 R4 BIN FIXED(15), /* Reserved */
05 UDF_DBINFO_ESBCS BIN FIXED(15), /* EBCDIC SBCS CCSID */
05 R5 BIN FIXED(15), /* Reserved */
05 UDF_DBINFO_EDBCS BIN FIXED(15), /* EBCDIC DBCS CCSID */
05 R6 BIN FIXED(15), /* Reserved */
05 UDF_DBINFO_EMIXED BIN FIXED(15), /* EBCDIC MIXED CCSID*/
05 R7 BIN FIXED(15), /* Reserved */
05 UDF_DBINFO_USBCS BIN FIXED(15), /* Unicode SBCS CCSID
*/
05 R8 BIN FIXED(15), /* Reserved */
05 UDF_DBINFO_UDBCS BIN FIXED(15), /* Unicode DBCS CCSID
*/
05 R9 BIN FIXED(15), /* Reserved */
05 UDF_DBINFO_UMIXED BIN FIXED(15), /* Unicode MIXED CCSID*/
05 UDF_DBINFO_ENCODE BIN FIXED(31), /* SP encode scheme */
05 UDF_DBINFO_RESERV0 CHAR(08), /* reserved
*/
03 UDF_DBINFO_SLEN BIN FIXED(15), /* schema length */
03 UDF_DBINFO_SCHEMA CHAR(128), /* schema name */
03 UDF_DBINFO_TLEN BIN FIXED(15), /* table length */
03 UDF_DBINFO_TABLE CHAR(128), /* table name */
03 UDF_DBINFO_CLEN BIN FIXED(15), /* column length */
03 UDF_DBINFO_COLUMN CHAR(128), /* column name */
03 UDF_DBINFO_RELVER CHAR(8), /* DB2 release level */
03 UDF_DBINFO_RESERV0 CHAR(2), /* reserved */
03 UDF_DBINFO_PLATFORM BIN FIXED(31), /* database platform*/
03 UDF_DBINFO_NUMTFCOL BIN FIXED(15), /* # of TF cols used*/
03 UDF_DBINFO_RESERV1 CHAR(26), /* reserved */
03 UDF_DBINFO_TFCOLUMN PTR, /* -> table fun col list */
03 UDF_DBINFO_APPLID PTR, /* -> application id */
03 UDF_DBINFO_RESERV2 CHAR(20); /* reserved */
⋮