$ ! Copyright © Oracle Corporation 1995. All Rights Reserved. $ ! $ ! SQL$ALL_RECORD_MOD_C.COM $ ! $ ! This command procedure demonstrates the use of records with SQL module $ ! language and a C host language program. This procedure does the following: $ ! $ ! - Creates the following files: $ ! - SQL$ALL_RECORD_MOD.C -- The host language program $ ! - SQL$ALL_RECORD.SQLMOD -- The SQL module $ ! $ ! - Compiles and links the host language program with the SQL module. $ ! $! Command procedure to build SQL$ALL_RECORD_MOD.C sample program: $create SQL$ALL_RECORD_MOD.C /* This program shows how to use the SQL module language with VAX C. * It also demonstrates how to pass record structures to an SQL module * program. In the SQL module program the language is specified as * GENERAL to show how to pass variable length character data, (VARCHAR) * to the SQL module program. * * This program shows how you declare C host language variables * to match a variety of data types and how you can specify those * variables in SQL statements when you store and retrieve column * or null values. The program: * * o Creates the ALL_DATATYPES database and table * o Stores a row using a reference to a host structure and * indicator array occurrences * o Retrieves that row using references to separate fields * and associated indicator variables * o Displays the stored row on the terminal * o Updates some column values in the row * o Displays the changed row on the terminal * o Deletes the database * */ /* init is a macro used to initialize a record structure.*/ #define init(n){int i;char *j;j = (char *)(&n); for(i=0;i #include #include #include struct date_str { char year_var1[2]; char year_var2[2]; char month_var[2]; char day_var[2]; char hour_var[2]; char minute_var[2]; char second_var[2]; char hundredth_var[2]; }; /*Declare all SQL module language calls */ extern long int create_database(); extern long int drop_database(); extern long int store_all_datatypes(); extern long int open_cursor(); extern long int close_cursor(); extern long int fetch_all_datatypes(); extern long int fetch_convert_all_datatypes(); extern long int update_all_datatypes(); extern long int rollback_transaction(); void SQL$SIGNAL( void ); main() { /* Declare the variable for monitoring execution status of SQL * statements. */ int SQLCODE; /* Declare a structure for row-level operations. The structure * has subordinate elementary variables for most column-level * operations. */ /* Text descriptor declaration.*/ struct dsc$descriptor_s t_dsc; /* String variable for use with SYS$BINTIM and sys$asctim routines. */ char vms_string_date[24]; /* Declare string variables for use when you want SQL to perform * conversions between DATE and char data types. In this case, * the DSRI rather than the VMS format is required. Note that only * nine characters are needed if you do not plan to store or retrieve * the time segment. */ char dup_year_var2[3]; char dup_month_var[3]; char dup_day_var[3]; char dup_hour_var[3]; char dup_minute_var[3]; /* Use indicator array for handling null values when an SQL statement * performs operations at the record or row level. */ short int indicator_item[8] = {0,0,0,0,0,0,0,0}; /* Use indicator variables for handling null values when an SQL * statement performs operations at the field or column level. */ struct ind_str { short int char_ind; short int varchar_ind; short int tinyint_ind; short int smallint_ind; short int integer_ind; short int real_ind; short int double_precision_ind; short int date_ind; } all_datatypes_ind; struct all_data_str { char char_var[10]; varchar(40) varchar_var; char tinyint_var; short int smallint_var; int integer_var; float real_var; double double_precision_var; variant_union { char bin_date_var[16]; struct date_str date_group; } unused; } all_datatypes_record; struct all_datatypes_update_str { char char_var[10]; char tinyint_var; short int smallint_var; int integer_var; } all_datatypes_update; struct all_datatypes_update_str_ind { short int char_ind; short int tinyint_ind; short int smallint_ind; short int integer_ind; } all_datatypes_update_ind; /* Use a variable for size of character string */ int char_var_size; char_var_size = sizeof(all_datatypes_record.char_var); /* Create the database and table.*/ create_database(&SQLCODE); if (SQLCODE < 0) { printf("\nCreate database failed %d", SQLCODE); SQL$SIGNAL(); } /* Move values to main variables. Usually, a program * would first initialize all main variables and indicator * variables before accepting each set of main variable * values from a terminal or data file. To keep this program * short, simply store a set of literals and rely on an * initialization of 0 for indicator variables. (A value of 0 in * an indicator variable ensures that a value in an associated * main variable is stored.) */ init(all_datatypes_record); init(all_datatypes_ind); init(all_datatypes_update); init(all_datatypes_update_ind); strncpy (all_datatypes_record.char_var, "SAM O'DELL",char_var_size); all_datatypes_record.tinyint_var = -128; all_datatypes_record.smallint_var = -32768; /*all_datatypes_record.integer_var = -2147483648;*/ all_datatypes_record.integer_var = 0X80000000; all_datatypes_record.real_var = 0.1234567; all_datatypes_record.double_precision_var = 0.123456789012345; t_dsc.dsc$b_class = DSC$K_CLASS_S; t_dsc.dsc$b_dtype = DSC$K_DTYPE_T; t_dsc.dsc$w_length = 23; t_dsc.dsc$a_pointer = (char *) (&vms_string_date); sys$bintim (&t_dsc, &all_datatypes_record.bin_date_var ); strcpy (all_datatypes_record.varchar_var.data, "This string is 38 characters in length"); all_datatypes_record.varchar_var.length = strlen(all_datatypes_record.varchar_var.data); /* The following "if" statements evaluate contents of main variables * and then set indicator variables as appropriate. The * conditional evaluation is unnecessary in this particular program, * but is appropriate for programs that store multiple rows and * permit null values for columns associated with main variables. */ if (strcmp (all_datatypes_record.char_var, " " ) == 0) indicator_item[0] = -1; if (strcmp( all_datatypes_record.varchar_var.data, " " ) == 0 ) indicator_item[1] = -1; if (all_datatypes_record.tinyint_var == 0) indicator_item[2] = -1; if (all_datatypes_record.smallint_var == 0) indicator_item[3] = -1; if (all_datatypes_record.integer_var == 0) indicator_item[4] = -1; if (all_datatypes_record.real_var == 0) indicator_item[5] = -1; if (all_datatypes_record.double_precision_var == 0) indicator_item[6] = -1; if (all_datatypes_record.bin_date_var == 0) indicator_item[7] = -1; /* The following INSERT statement transfers an entire structure * as a row. Because the INSERT statement does not list columns * in ALL_DATATYPES_TABLE in the order that they correspond to * elementary fields in ALL_DATATYPES_RECORD, the statement * assumes that the current number and order of columns in the * table correspond correctly to number and order of fields * in the structure. This is appropriate when a table definition * is stable (as is true here because the program creates the * table). */ store_all_datatypes(&SQLCODE,&all_datatypes_record,&indicator_item); if (SQLCODE < 0 ) goto sql_error_1 ; /* Initialize record */ init(all_datatypes_record); init(all_datatypes_ind); /* Now retrieve and display the row. */ open_cursor(&SQLCODE); if (SQLCODE < 0 ) goto sql_error_1 ; fetch_all_datatypes(&SQLCODE,&all_datatypes_record,&all_datatypes_ind); if (SQLCODE < 0 ) goto sql_error_1 ; /* Display the stored row. */ printf ("\nThis is the stored row."); if (all_datatypes_ind.char_ind < 0) printf ("\n\nCHAR_COL: NULL"); else printf ("\nCHAR_COL: %*.*s",char_var_size, char_var_size, all_datatypes_record.char_var); if (all_datatypes_ind.varchar_ind < 0) printf ("\nVARCHAR_COL: NULL"); else printf ("\nVARCHAR_COL: %*.*s", all_datatypes_record.varchar_var.length, all_datatypes_record.varchar_var.length, all_datatypes_record.varchar_var.data ); if (all_datatypes_ind.tinyint_ind < 0) printf ("\nTINYINT_COL: NULL"); else printf ("\nTINYINT_COL: %d", all_datatypes_record.tinyint_var); if (all_datatypes_ind.smallint_ind < 0) printf ("\nSMALLINT_COL: NULL"); else printf ("\nSMALLINT_COL: %d", all_datatypes_record.smallint_var); if (all_datatypes_ind.integer_ind < 0) printf ("\nINTEGER_COL: NULL"); else printf ("\nINTEGER_COL: %d", all_datatypes_record.integer_var); if (all_datatypes_ind.real_ind < 0) printf ("\nREAL_COL: NULL"); else printf ("\nREAL_COL: %12.7f", all_datatypes_record.real_var); if (all_datatypes_ind.double_precision_ind < 0) printf ("\nDOUBLE_PRECISION_COL: NULL"); else printf ("\nDOUBLE_PRECISION_COL: %21.14e", all_datatypes_record.double_precision_var); if (all_datatypes_ind.date_ind < 0) printf ("\nDATE_COL: NULL"); else { t_dsc.dsc$b_class = DSC$K_CLASS_S; t_dsc.dsc$b_dtype = DSC$K_DTYPE_T; t_dsc.dsc$w_length = 23; t_dsc.dsc$a_pointer = (char *) (&vms_string_date); sys$asctim( 0, &t_dsc, &all_datatypes_record.bin_date_var, 0 ); vms_string_date[23] = 0; printf ("\nDATE_COL: %s", vms_string_date); } /* Modify some columns in the fetched row. Modify all unscaled * fixed-point binary columns to have null values. Change the name * in CHAR_VAR to be mixed case. * * Set indicator variables to which you plan to refer in * an UPDATE statement. Normally, these assignment statements * would be subordinate to conditional statements that evaluate * main variable values. */ all_datatypes_update_ind.char_ind = 0; all_datatypes_update_ind.smallint_ind = -1; all_datatypes_update_ind.integer_ind = -1; /* Change the old value in char_var and tinyint_var. */ strncpy (all_datatypes_update.char_var, "Sam O'Dell", char_var_size); all_datatypes_update.tinyint_var = 127; /* Update the row.*/ update_all_datatypes( &SQLCODE, &all_datatypes_update, &all_datatypes_update_ind); /* Close and then open the cursor again to reposition the cursor on * the first row and display the changed row. This time, retrieve * DATE_COL by fetching its value and placing it into string_date_var * rather than bin_date_var. */ close_cursor(&SQLCODE); if (SQLCODE < 0 ) goto sql_error_1 ; init(all_datatypes_record); init(all_datatypes_ind); open_cursor(&SQLCODE); if (SQLCODE < 0 ) goto sql_error_1 ; fetch_convert_all_datatypes(&SQLCODE,&all_datatypes_record, &all_datatypes_ind); if (SQLCODE < 0 ) goto sql_error_1 ; /* Display the changed row. */ printf ("\n\nThis is the changed row."); if (all_datatypes_ind.char_ind < 0) printf ("\n\nCHAR_COL: NULL"); else printf ("\nCHAR_COL: %*.*s", char_var_size, char_var_size, all_datatypes_record.char_var); if (all_datatypes_ind.varchar_ind < 0) printf ("\nVARCHAR_COL: NULL"); else printf ("\nVARCHAR_COL: %*.*s", all_datatypes_record.varchar_var.length, all_datatypes_record.varchar_var.length, all_datatypes_record.varchar_var.data ); if (all_datatypes_ind.tinyint_ind < 0) printf ("\nTINYINT_COL: NULL"); else printf ("\nTINYINT_COL: %d", all_datatypes_record.tinyint_var); if (all_datatypes_ind.smallint_ind < 0) printf ("\nSMALLINT_COL: NULL"); else printf ("\nSMALLINT_COL: %d", all_datatypes_record.smallint_var); if (all_datatypes_ind.integer_ind < 0) printf ("\nINTEGER_COL: NULL"); else printf ("\nINTEGER_COL: %d", all_datatypes_record.integer_var); if (all_datatypes_ind.real_ind < 0 ) printf ("\nREAL_COL: NULL"); else printf ("\nREAL_COL: %12.7f", all_datatypes_record.real_var); if (all_datatypes_ind.double_precision_ind < 0) printf ("\nDOUBLE_PRECISION_COL: NULL"); else printf ("\nDOUBLE_PRECISION_COL: %21.14e", all_datatypes_record.double_precision_var); if (all_datatypes_ind.date_ind < 0) printf ("\nDATE_COL: NULL"); else { strncpy(dup_year_var2,all_datatypes_record.date_group.year_var2,2); dup_year_var2[2] = 0; strncpy(dup_month_var, all_datatypes_record.date_group.month_var,2); dup_month_var[2] = 0; strncpy(dup_day_var, all_datatypes_record.date_group.day_var,2); dup_day_var[2] = 0; strncpy(dup_hour_var, all_datatypes_record.date_group.hour_var,2); dup_hour_var[2] = 0; strncpy(dup_minute_var, all_datatypes_record.date_group.minute_var,2); dup_minute_var[2] = 0; printf ("\nDATE_COL: %s/%s/%s %s:%s", dup_month_var, dup_day_var, dup_year_var2, dup_hour_var, dup_minute_var); } /* End the transaction so the database can be deleted. First change * error handling so that actions are appropriate for failure of * the ROLLBACK or DROP DATABASE statement. */ rollback_transaction(&SQLCODE); /* Delete the database and stop the program with normal exit status. */ drop_database(&SQLCODE); if (SQLCODE < 0 ) goto sql_error_2 ; sys$exit(1); /* Error-handling sections */ sql_error_1: printf ("\nSQLCODE is %d", SQLCODE); printf ("\nError on attempt to create table, or on"); printf ("\nattempt to insert, update, or retrieve data."); rollback_transaction(SQLCODE); if (SQLCODE < 0) { printf ("\nROLLBACK failed. Delete database files manually."); SQL$SIGNAL(); } sql_error_2: printf ("\nSQLCODE is %d", SQLCODE); printf ("\nDatabase could not be deleted."); printf ("\nDelete database files manually."); SQL$SIGNAL(); } $create SQL$ALL_RECORD.SQLMOD -- This SQL module provides the SQL procedures needed by the -- SQL$ALL_RECORD_MOD.C program. -- -- This program demonstrates the use of data record defintions. -- -- The procedures create a database with one table, insert a row in the table, -- declare, open, and use a cursor to fetch a row, and update a row. -- -- The program also shows how to use SQL to convert a date to DSRI -- string format. -- -- The GENERAL language allow us to pass VARCHAR data to and from -- the database and the C language program. It also prevents -- strings from being null terminated or having the last character -- truncated. However, the calling C program must keep track of -- the character string length. -------------------------------------------------------------------------- -- Header Information Section -------------------------------------------------------------------------- MODULE SQL_ALL_RECORD -- Module name LANGUAGE GENERAL -- Required for CDD and RECORDS AUTHORIZATION SQL_SAMPLE -- Provides default authorization -- identifier ALIAS SQL_ALL -- Alias PARAMETER COLONS -- Parameter names require colons -------------------------------------------------------------------------- -- DECLARE Statements Section -------------------------------------------------------------------------- DECLARE CONVERSIONS CURSOR FOR SELECT * FROM ALL_DATATYPES_TABLE -------------------------------------------------------------------------- -- Procedure Section -------------------------------------------------------------------------- -- This procedure creates a database called ALL_DATATYPES and creates -- a single table called ALL_DATATYPES_TABLE. -- The columns represent some of the data types supported by -- SQL: CHAR, VARCHAR, TINYINT, SMALLINT, INTEGER, REAL and DOUBLE PRECISION PROCEDURE CREATE_DATABASE SQLCODE; CREATE DATABASE ALIAS SQL_ALL FILENAME ALL_DATATYPES CREATE TABLE ALL_DATATYPES_TABLE ( CHAR_COL CHAR(10), VARCHAR_COL VARCHAR(40), TINYINT_COL TINYINT, SMALLINT_COL SMALLINT, INTEGER_COL INTEGER, REAL_COL REAL, DOUBLE_PREC_COL DOUBLE PRECISION, DATE_COL DATE ); -- This procedure deletes a database (deletes all database files). PROCEDURE DROP_DATABASE SQLCODE; DROP DATABASE FILENAME ALL_DATATYPES; -- This procedure inserts a row in the table. PROCEDURE STORE_ALL_DATATYPES SQLCODE :ALL_DATA_REC RECORD P_CHAR_COL CHAR(10) P_VARCHAR_COL VARCHAR(40) P_TINYINT_COL TINYINT P_SMALLINT_COL SMALLINT P_INTEGER_COL INTEGER P_REAL_COL REAL P_DOUBLE_PREC_COL DOUBLE PRECISION P_DATE_COL DATE END RECORD :ALL_DATA_IND RECORD INDICATOR ARRAY OF 8 SMALLINT END RECORD; INSERT INTO ALL_DATATYPES_TABLE (CHAR_COL, VARCHAR_COL, TINYINT_COL,SMALLINT_COL,INTEGER_COL, REAL_COL, DOUBLE_PREC_COL, DATE_COL) VALUES (:ALL_DATA_REC INDICATOR :ALL_DATA_IND); -- This procedure opens the cursor that has been declared for -- ALL_DATATYPES_TABLE. PROCEDURE OPEN_CURSOR SQLCODE; OPEN CONVERSIONS; -- This procedure closes the cursor that has been declared for -- ALL_DATATYPES_TABLE. PROCEDURE CLOSE_CURSOR SQLCODE; CLOSE CONVERSIONS; -- This procedure fetches all the data from the opened cursor. PROCEDURE FETCH_ALL_DATATYPES SQLCODE :ALL_DATA_REC RECORD P_CHAR_COL CHAR(10) P_VARCHAR_COL VARCHAR(40) P_TINYINT_COL TINYINT P_SMALLINT_COL SMALLINT P_INTEGER_COL INTEGER P_REAL_COL REAL P_DOUBLE_PREC_COL DOUBLE PRECISION P_DATE_COL DATE END RECORD :ALL_DATA_IND RECORD INDICATOR ARRAY OF 8 SMALLINT END RECORD; FETCH CONVERSIONS INTO :ALL_DATA_REC INDICATOR :ALL_DATA_IND; -- This procedure fetches all of the data from the opened cursor and -- also converts DATE_COL to a text data type. PROCEDURE FETCH_CONVERT_ALL_DATATYPES SQLCODE :ALL_DATA RECORD P_CHAR_COL CHAR(10) P_VARCHAR_COL VARCHAR(40) P_TINYINT_COL TINYINT P_SMALLINT_COL SMALLINT P_INTEGER_COL INTEGER P_REAL_COL REAL P_DOUBLE_PREC_COL DOUBLE PRECISION P_STRING_DATE_DSRI CHAR(16) END RECORD :ALL_DATA_IND RECORD INDICATOR ARRAY OF 8 SMALLINT END RECORD; FETCH CONVERSIONS INTO :ALL_DATA INDICATOR :ALL_DATA_IND; -- This procedure updates a row in ALL_DATATYPES_TABLE. PROCEDURE UPDATE_ALL_DATATYPES SQLCODE :ALL_DATA RECORD P_CHAR_COL CHAR(10) P_TINYINT_COL TINYINT P_SMALLINT_COL SMALLINT P_INTEGER_COL INTEGER END RECORD :ALL_DATA_IND RECORD P_CHAR_IND SMALLINT P_TINYINT_IND SMALLINT P_SMALLINT_IND SMALLINT P_INTEGER_IND SMALLINT END RECORD; UPDATE ALL_DATATYPES_TABLE SET CHAR_COL = :ALL_DATA.P_CHAR_COL INDICATOR :ALL_DATA_IND.P_CHAR_IND, TINYINT_COL = :ALL_DATA.P_TINYINT_COL INDICATOR :ALL_DATA_IND.P_TINYINT_IND, SMALLINT_COL = :ALL_DATA.P_SMALLINT_COL INDICATOR :ALL_DATA_IND.P_SMALLINT_IND, INTEGER_COL = :ALL_DATA.P_INTEGER_COL INDICATOR :ALL_DATA_IND.P_INTEGER_IND WHERE CURRENT OF CONVERSIONS; -- This procedure rolls back the transaction. PROCEDURE ROLLBACK_TRANSACTION SQLCODE; ROLLBACK; $ write sys$output "Compiling and linking sample sql$all_record_mod program." $ sqlmod = "$sql$mod" $! Use the /alpha qualifier in the following command if compiling on a $! VMS system for an AXP system $ sqlmod sql$all_record $! The module language assumes VMS G_FLOAT datatypes for DOUBLE PRECISION $! Use the /NOG_FLOAT qualifier for D_FLOAT representation of DOUBLE PRECISION $! Use the /standard=vaxc qualifier after /noopt if compiling for an AXP system $cc/g_float sql$all_record_mod/list/noopt $define sys$library mid$, sys$sysroot:[syslib] $link sql$all_record_mod, sql$all_record, sys$input/opt sql$user/lib sql$int/share sys$library:vaxcrtlg/share $ write sys$output "Running sample sql$all_record_mod program." $run sql$all_record_mod $exit