/* Copyright © 1995, 2005, Oracle Corporation. All Rights Reserved. */ #pragma module SQL_DYNAMIC "IDENT" /* **++ ** FUNCTIONAL DESCRIPTION: ** ** This module is the second of two that demonstrate the dynamic ** interface of SQL through C. Use this module with either ** sql_dynamic_driver_i.c or sql_dynamic_driver_m.c. ** sql_dynamic_driver_i.c is for interactive use or to query the user ** from a predesigned input file. sql_dynamic_driver_m.c is used to ** demonstrate infinite repeating of preestablished queries. ** ** This module processes the statement passed from the driver module. ** It calls PREPARE and DESCRIBE statements to write information about any ** parameter markers or select list items to separate SQLDA2 structures. ** If there are parameter markers, it allocates storage and prompts the ** user to supply values. If there are select list items, it allocates ** storage for them and sets up a fetch loop to display values on the ** terminal. ** ** This routine can re-execute SQL statements that were previously ** prepared. If the calling program passes the address of an SQLDA, ** then this code assumes that it has already prepared the statement ** and it will re-execute, prompting for new parameters, etc. ** ** Note that additional work may be needed to free up unused space. ** ** ** FORMAL PARAMETERS: ** ** psql_stmt = pointer to an sql statement to be prepared and executed ** ** input_sqlda = pointer to an SQLDA. If NULL then statement passed ** needs to be prepared. If non-NULL, then psql_stmt is ** ignored, and statement is re-executed. This SQLDA ** contains information about parameter markers, etc. ** ** output_sqlda = pointer to an SQLDA. If NULL then statement passed ** needs to be prepared. If non-NULL, then psql_stmt is ** ignored, and statement is re-executed. This SQLDA ** contains information and allocation for select ** statements ** ** stmt_id = supplied by the calling program, this parameter is needed ** by the Modular SQL Procedures to identify the statement ** being processed. A stmt_id and its corresponding sqlda ** structures must be passed together for the execution of ** particular statements. ** NOTE: SQL supplies a value for stmt_id in the PREPARE ** call. Because the value must be zero for the ** first call in any statement, you must initialize ** this value to zero before the PREPARE statement. ** ** IMPLICIT INPUTS: ** ** none ** ** IMPLICIT OUTPUTS: ** ** none ** ** SIDE EFFECTS: ** ** none ** **/ /* ** INCLUDE FILES **/ #include #include #include #include #include #include #include #include #include #ifdef _alpha #include #endif #define sql_success 0 #define sql_stream_eof 100 #define TRUE 1 #define FALSE 0 #define IDLEN 128 #define DATE_SIZE 23 /* Max number of parameters or select list items is 50.*/ #define MAXPARAMS 50 /* ** Declare the SQL data area (SQLDA) type to handle dynamic SQL allocations ** for parameter markers and select list items. */ typedef unsigned long vc_len; typedef short sc_len; /* This structure is created to allow saving of the DATETIME datatypes ** returned by the prepare and describe statements. All DATETIME ** datatypes will be converted to VARCHAR(100) for processing. The ** original datatypes are necessary for the proper description of the ** DATETIME column when parameter markers are used and when displaying ** the output of select statements. */ #pragma nomember_alignment typedef struct { short type; int len; int chrono_scale; int chrono_precision; } sqlda_mem_rec, *mem_ptr; /* This procedure is used to get the correct column type, leading precision, and fractional precision of DATETIME datatypes. */ char *getType(mem_ptr); /* Declare the SQLDA2. */ typedef struct { char sqldaid[8]; int sqlabc; short sqln; short sqld; struct sqlvar_struct { short sqltype; long sqllen; int sqloctet_len; char *sqldata; long *sqlind; int sqlchrono_scale; int sqlchrono_precision; short sqlname_len; char sqlname[IDLEN]; char sqlchar_set_name[IDLEN]; char sqlchar_set_schema[IDLEN]; char sqlchar_set_catalog[IDLEN]; } sqlvar[MAXPARAMS]; } sqlda_rec, *sqlda; /* Declare the SQLCA. */ struct { char SQLCAID[8]; int SQLCABC; int SQLCODE; struct { short SQLERRML; char SQLERRMC[70]; } SQLERRM; int SQLERRD[6]; struct { char SQLWARN0[1]; char SQLWARN1[1]; char SQLWARN2[1]; char SQLWARN3[1]; char SQLWARN4[1]; char SQLWARN5[1]; char SQLWARN6[1]; char SQLWARN7[1]; } SQLWARN; char SQLEXT[8]; } SQLCA = { {'S','Q','L','C','A',' ',' ',' '}, 128, 0, {0, ""}, {0,0,0,0,0,0}, {"", "", "", "", "", "", "", ""}, "" }; /* Declare type for varchar.*/ #define MAX_VARCHAR 4000 typedef struct { unsigned long length; char data[MAX_VARCHAR]; } varchar; /* External definitions of SQLMOD procedures. */ extern void prepare_stmt(); extern void describe_parm(); extern void describe_select(); extern void execute_stmt(); extern void declare_cursor(); extern void open_cursor(); extern void fetch_row(); extern void close_cursor(); extern void release_stmt(); extern void commit_transaction(); extern void rollback_transaction(); extern int sys$bintim(); extern void sys$asctim(); extern int lib$cvt_dx_dx(); extern void lib$signal(); /* Other declarations. */ /* ** Declare pointer variables for each possible data type. The GET_IN_PARAMS ** and ALLOCATE_BUFFERS procedures use the pointers to dynamically allocate ** memory to hold values for parameter markers and select list items, ** respectively. */ char *charbuf; varchar *varchar_buf; /* Local functions. */ int get_in_params(); int allocate_buffers(); int display_row(); void display_error_message(); /* Text descriptor declaration. */ struct dsc$descriptor_s t_dsc; int sql_return_status; int vms_return_status; int cursor_counter = 0; char new_statement; char cursor_name[] = {'0', '0'}; /* **---------------------------------------------------------------------------- ** Begin Main routine **---------------------------------------------------------------------------- */ int sql_dynamic (psql_stmt, input_sqlda, output_sqlda, stmt_id, is_select) char *psql_stmt; sqlda *input_sqlda; sqlda *output_sqlda; long *stmt_id; int *is_select; { sqlda sqlda_in, sqlda_out; /* declare the SQLDA structures */ int rowcount, status; int param; /* Declare arrays for storage of original datatypes and allocate memory. */ mem_ptr output_save; mem_ptr input_save; /* * If NULL sqlda's passed, then new statement is being prepared. */ if ((*input_sqlda == NULL) && (*output_sqlda == NULL)) { new_statement = TRUE; /* ** Allocate separate SQLDAs for parameter markers (SQLDA_IN) and select ** list items (SQLDA_OUT). Assign value of the constant MAXPARMS to ** the SQLN field of both SQLDA structures. SQLN specifies to SQL the ** maximum size of the SQLDA. */ if ((sqlda_in = (sqlda) calloc (1, sizeof (sqlda_rec))) == 0) { printf ("\n\n*** Error allocating memory for sqlda_in: Abort"); return (-1); } else /* set # of possible parameters */ sqlda_in->sqln = MAXPARAMS; if ((sqlda_out = (sqlda) calloc (1, sizeof (sqlda_rec))) == 0) { printf ("\n\n*** Error allocating memory for sqlda_out: Abort"); return (-1); } else /* set # of possible select list items */ sqlda_out->sqln = MAXPARAMS; /* copy name SQLDA2 to identify the SQLDA */ strncpy(&sqlda_in->sqldaid[0],"SQLDA2 ",8); strncpy(&sqlda_out->sqldaid[0],"SQLDA2 ",8); /* ** Call an SQL module language procedure, prepare_stmt and ** describe_stmt that contains a PREPARE and DESCRIBE...SELECT_LIST ** statement to prepare the dynamic statement and write ** write information about any select list items in it to SQLDA_OUT */ *stmt_id = 0; /* if <> 0 the BADPREPARE error results in the PREPARE */ prepare_stmt (&SQLCA, stmt_id, psql_stmt); if (SQLCA.SQLCODE != sql_success) { printf ("\n\nDSQL-E-PREPARE, Error %d encountered in PREPARE", SQLCA.SQLCODE); display_error_message(); if (new_statement) { free(sqlda_in); free(sqlda_out); } return (-1); } describe_select (&SQLCA, stmt_id, sqlda_out); if (SQLCA.SQLCODE != sql_success) { printf ("\n\nDSQL-E-PREPARE, Error %d encountered in PREPARE", SQLCA.SQLCODE); display_error_message(); if (new_statement) { free(sqlda_in); free(sqlda_out); } return (-1); } if (sqlda_out->sqld > 0) printf ("** There %s %d output parameter%s\n", (sqlda_out->sqld==1)?"is":"are", sqlda_out->sqld, (sqlda_out->sqld==1)?"":"s"); /* ** Call an SQL module language procedure, describe_parm, that contains a ** DESCRIBE...MARKERS statement to write information about any parameter ** markers in the dynamic statement to sqlda_in: */ describe_parm (&SQLCA, stmt_id, sqlda_in); if (SQLCA.SQLCODE != sql_success) { printf ("\n\n*** Error %d returned from describe_parm: Abort", SQLCA.SQLCODE); display_error_message(); if (new_statement) { free(sqlda_in); free(sqlda_out); } return (-1); } if (sqlda_in->sqld > 0) printf ("** There %s %d input parameter%s\n", (sqlda_in->sqld==1)?"is":"are", sqlda_in->sqld, (sqlda_in->sqld==1)?"":"s"); /* Save the value of the SQLCA.SQLERRD[1] field so that program can determine if the statement is a SELECT statement or not. If the value is 1, the statement is a SELECT statement.*/ *is_select = SQLCA.SQLERRD[1]; for (param = 0; param < sqlda_in->sqld; param++ ) sqlda_in->sqlvar[param].sqlind = malloc( 1000 + sizeof sqlda_in->sqlvar[param].sqlind ); for (param = 0; param < sqlda_in->sqld; param++ ) sqlda_in->sqlvar[param].sqldata = (char *) (sqlda_in->sqlvar[param].sqlind + sizeof sqlda_in->sqlvar[param].sqlind); } /* End IF check for new statement */ else { /* Re-executing old statement which has already been prepared */ new_statement = FALSE; sqlda_in = *input_sqlda; /* Use the SQLDA pointers passed */ sqlda_out = *output_sqlda; } /* Now move the datatype info from the sqlda_in to the input_save array ** and from the sqlda_out to the output_save array. These arrays will be ** passed to the procedure getType to pick up the correct DATETIME datatype ** with correct leading precision and fractional precision when either ** an input value is needed from the user or when displaying the results ** of a select statement. */ /* Allocate space for the structures that will save the original ** DATETIME datatypes. */ output_save = malloc( sqlda_out->sqln * sizeof( sqlda_mem_rec ) ); input_save = malloc( sqlda_in->sqln * sizeof( sqlda_mem_rec ) ); for ( param = 0; param < sqlda_in->sqld; param++ ) { input_save[param].type = sqlda_in->sqlvar[param].sqltype; input_save[param].len = sqlda_in->sqlvar[param].sqllen; input_save[param].chrono_scale = sqlda_in->sqlvar[param].sqlchrono_scale; input_save[param].chrono_precision = sqlda_in->sqlvar[param].sqlchrono_precision; /* Now change all DATETIME datatypes (type SQLDA2_DATETIME and ** SQLDA2_INTERVAL), to VARCHAR(100) (type SQLDA_VARCHAR). ** This will facilitate the necessary conversion of all ** DATETIME datatypes to TEXT. */ if ( ( sqlda_in->sqlvar[param].sqltype == SQLDA2_DATETIME ) || ( sqlda_in->sqlvar[param].sqltype == SQLDA2_INTERVAL ) ) { sqlda_in->sqlvar[param].sqltype = SQLDA_VARCHAR; sqlda_in->sqlvar[param].sqllen = 100; sqlda_in->sqlvar[param].sqloctet_len = sqlda_in->sqlvar[param].sqllen + sizeof (unsigned long); sqlda_in->sqlvar[param].sqlchrono_scale = 0; sqlda_in->sqlvar[param].sqlchrono_precision = 0; } } for ( param = 0; param < sqlda_out->sqld; param++ ) { output_save[param].type = sqlda_out->sqlvar[param].sqltype; output_save[param].len = sqlda_out->sqlvar[param].sqllen; output_save[param].chrono_scale = sqlda_out->sqlvar[param].sqlchrono_scale; output_save[param].chrono_precision = sqlda_out->sqlvar[param].sqlchrono_precision; if ( ( sqlda_out->sqlvar[param].sqltype == SQLDA2_DATETIME ) || ( sqlda_out->sqlvar[param].sqltype == SQLDA2_INTERVAL ) ) { sqlda_out->sqlvar[param].sqltype = SQLDA_VARCHAR; sqlda_out->sqlvar[param].sqllen = 100; sqlda_out->sqlvar[param].sqloctet_len = sqlda_out->sqlvar[param].sqllen + sizeof (unsigned long); sqlda_out->sqlvar[param].sqlchrono_scale = 0; sqlda_out->sqlvar[param].sqlchrono_precision = 0; } } /* ** Check to see if the prepared dynamic statement contains any parameter ** markers by looking at the SQLD field of sqlda_in. SQLD contains the ** number of parameter markers in the prepared statement. If SQLD is ** positive, the prepared statement contains parameter markers. The program ** executes a local procedure, get_in_params, that prompts the user for ** values, allocates storage for those values, and updates the SQLDATA field ** of sqlda_in: */ if (sqlda_in->sqld > 0) if ((status = get_in_params(sqlda_in,input_save)) != 0) { printf ("\nError returned from GET_IN_PARAMS. Abort"); return (-1); } /** Check to see if the prepared dynamic statement is a SELECT by looking ** at the value in is_select, which stores the value of the ** SQLCA.SQLERRD[1] field. If that value is equal to 1, the prepared ** statement is a SELECT statement. The program allocates storage for ** rows for SQL module language procedures to open and fetch from a cursor, and ** displays the rows on the terminal: */ if (*is_select) { if (new_statement == TRUE) /* allocate buffers for output */ { /* assign a unique name for the cursor */ sprintf(cursor_name,"%2d",++cursor_counter); if (allocate_buffers(sqlda_out) != 0) { printf ("\nError returned from ALLOCATE_BUFFERS. Abort"); return (-1); } /* Declare the cursor (done once only!) */ declare_cursor (&SQLCA, &cursor_name, stmt_id); if (SQLCA.SQLCODE != sql_success) { printf ("\n\nError %d returned from declare_cursor", SQLCA.SQLCODE); display_error_message(); if (new_statement) { free(sqlda_in); free(sqlda_out); } return (-1); } } /* end if new statement */ /* Open the cursor */ open_cursor (&SQLCA, &cursor_name, sqlda_in); if (SQLCA.SQLCODE != sql_success) { printf ("\n\nError %d returned from open_cursor",SQLCA.SQLCODE); display_error_message(); if (new_statement) { free(sqlda_in); free(sqlda_out); } return (-1); } /* ** Loop to fetch and display rows */ rowcount = 0; SQLCA.SQLCODE = sql_success; while (SQLCA.SQLCODE == sql_success) { /* Fetch a row, exit loop if no more */ fetch_row (&SQLCA, &cursor_name, sqlda_out); switch (SQLCA.SQLCODE) { case sql_success : rowcount++; display_row(sqlda_out, output_save); break; case sql_stream_eof : if (rowcount == 0) printf ("\n\nNo records found."); else printf ("\n\nNo more records found."); break; default: printf ("\n\nError %d returned from fetch_row", SQLCA.SQLCODE); display_error_message(); if (new_statement) { free(sqlda_in); free(sqlda_out); } return (-1); } /* end switch */ } /* end while */ /* Close the cursor */ close_cursor (&SQLCA, &cursor_name); if (SQLCA.SQLCODE != sql_success) { printf("\n\nError %d returned from close_cursor",SQLCA.SQLCODE); display_error_message(); if (new_statement) { free(sqlda_in); free(sqlda_out); } return (-1); } } /* end if SQLCA.SQLERRD[1] == 1) */ else { /* ** If the SQLCA.SQLERRD[1] field is not 1, then prepared statement is not a ** SELECT statement and only needs to be executed. Call an SQL module language ** procedure to execute the statement, using information about parameter ** markers stored in sqlda_in by the local procedure get_in_params: */ /* if (SQLCA.SQLERRD[1] != 1)*/ if (allocate_buffers(sqlda_out) != 0) { printf ("\nError returned from ALLOCATE_BUFFERS. Abort"); return (-1); } execute_stmt (&SQLCA, stmt_id, sqlda_in, sqlda_out); if (SQLCA.SQLCODE != sql_success) { printf("\n\nError %d returned from execute_stmt",SQLCA.SQLCODE); display_error_message(); if (new_statement) { free(sqlda_in); free(sqlda_out); } return (-1); } else if (sqlda_out->sqld > 0) // display any results display_row(sqlda_out, output_save); } /* Return pointers to the SQLDA's for re-execution */ *input_sqlda = sqlda_in; *output_sqlda = sqlda_out; return (0); } /* end routine sql_dynamic */ /* -------------------------------------------------------------------------- */ /* ** GET_IN_PARAMS allocates storage for parameter markers in the statement ** string supplied by the user. It also prompts the user for values to place ** in that storage, assigns the values to the storage, and places ** addresses of the storage in SQLDA_IN. */ int get_in_params(sqlda_in,saved_data) sqlda sqlda_in; mem_ptr saved_data; { char * bp; short param; long *indicator_param; int loop_cntr, status, i; char field_name[31]; char longchar[] = " "; /* temp storage of long string */ char shortchar[] = " "; /* temp storage of short string */ char floatchar[] = " "; /* temp storage of float */ char datechar[] = " "; /* for dates */ char legaldate[] = "0 "; /* for d-mmm-yyyy ... */ char typeStr [100]; static char input[100]; typeStr [0] = '\0'; status = 0; /* ** For each parameter marker indicated by SQLDA_IN.SQLD, execute a loop: ** Check the value of SQLDA_IN.SQLVAR.SQLTYPE to determine the data ** type of the parameter marker, and branch to the appropriate code ** to prompt and store the value for that data type. */ for (param = 0; param < sqlda_in->sqld; param++) { /* null terminate field name string, & prompt with the column name */ strncpy (&field_name[0], &sqlda_in->sqlvar[param].sqlname[0], sqlda_in->sqlvar[param].sqlname_len); field_name[sqlda_in->sqlvar[param].sqlname_len] = '\0'; printf ("\nEnter value for parameter: %s\n", field_name); switch (sqlda_in->sqlvar[param].sqltype) { case SQLDA_VARCHAR: /* varchar */ {// varchar *cptr = (varchar *) sqlda_in->sqlvar[param].sqldata; if ((saved_data[param].type == SQLDA2_DATETIME) || (saved_data[param].type == SQLDA2_INTERVAL)) printf( "%s: ", getType(&(saved_data[param]))); else printf( "Varchar(%d): ", sqlda_in->sqlvar[param].sqllen ); input [0] = '\0'; bp = gets(input); if (bp == NULL) return (-1); strcpy( cptr->data, input ); cptr->length = strlen( cptr->data ); cptr->data [ cptr->length ] = '\0'; break; } case SQLDA_CHAR : /* character variable */ /* * Set type to ASCIZ so that string will be char padded * when it gets to the database; else NULL padded. */ sqlda_in->sqlvar[param].sqltype = SQLDA_ASCIZ; case SQLDA_ASCIZ : /* ASCIZ - needed for proper */ /* NULL interpretation with C */ if (new_statement == TRUE) printf("\n(Maximum length is %d)",sqlda_in->sqlvar[param].sqllen); else printf("\n(Maximum length is %d)",sqlda_in->sqlvar[param].sqllen - 1); if (new_statement == TRUE) /* allocate storage for char string*/ /* extra byte for null terminator */ { sqlda_in->sqlvar[param].sqldata = (char *) malloc (++sqlda_in->sqlvar[param].sqllen); ++sqlda_in->sqlvar[param].sqloctet_len; } /* get the string */ bp = gets (sqlda_in->sqlvar[param].sqldata); if (bp == NULL) return (-1); break; /* exit the switch block */ case SQLDA_INTEGER: /* integer */ case SQLDA_SMALLINT: /* smallint */ case SQLDA_SEGSTRING: /* segmented string */ case SQLDA_TINYINT: /* tinyint */ case SQLDA_QUADWORD: /* bigint */ { struct dsc$descriptor_sd quad_dsc; $DESCRIPTOR( text_dsc, input ); sc_len *sptr = (sc_len *) &(sqlda_in->sqlvar[param].sqllen); if ( sqlda_in->sqlvar[param].sqltype == SQLDA_QUADWORD || sqlda_in->sqlvar[param].sqltype == SQLDA_SEGSTRING) printf( " Bigint" ); else if ( sqlda_in->sqlvar[param].sqltype == SQLDA_INTEGER ) printf( " Integer" ); else if ( sqlda_in->sqlvar[param].sqltype == SQLDA_SMALLINT ) printf( " Smallint" ); else if ( sqlda_in->sqlvar[param].sqltype == SQLDA_TINYINT ) printf( " Tinyint" ); /* sptr[1] contains the number of digits to the right of ** the decimal point. */ if ( sptr [1] > 0 ) printf( " (%d)", sptr [1] ); printf( ": " ); input [0] = '\0'; bp = gets(input); if (bp == NULL) return (-1); text_dsc.dsc$w_length = strlen( input ); switch (sqlda_in->sqlvar[param].sqltype) { case SQLDA_TINYINT: quad_dsc.dsc$b_dtype = DSC$K_DTYPE_B; break; case SQLDA_SMALLINT: quad_dsc.dsc$b_dtype = DSC$K_DTYPE_W; break; case SQLDA_INTEGER: quad_dsc.dsc$b_dtype = DSC$K_DTYPE_L; break; case SQLDA_SEGSTRING: case SQLDA_QUADWORD: quad_dsc.dsc$b_dtype = DSC$K_DTYPE_Q; break; default: quad_dsc.dsc$b_dtype = 0; } quad_dsc.dsc$b_class = DSC$K_CLASS_SD; quad_dsc.dsc$w_length = sptr [0]; quad_dsc.dsc$a_pointer = sqlda_in->sqlvar[param].sqldata; quad_dsc.dsc$b_scale = -1 * sptr [1]; quad_dsc.dsc$b_digits = 0; status = lib$cvt_dx_dx( &text_dsc, &quad_dsc, 0 ); if ( (status & 1) == 0 ) lib$signal ( status ); else status = 0; break; } case SQLDA_FLOAT : /* floating variable */ { float *floatbuf; double *doublebuf; /* ** allocate storage for the character string version of ** the float to be input... 15 digits should be enough */ /* get the string */ bp = gets (floatchar); if (bp == NULL) return (-1); if (new_statement == TRUE) /* allocate storage for the float */ { if (sqlda_in->sqlvar[param].sqllen == 8) doublebuf = (double *) malloc (sqlda_in->sqlvar[param].sqllen); else floatbuf = (float *) malloc (sqlda_in->sqlvar[param].sqllen); } /* convert the string */ if (sqlda_in->sqlvar[param].sqllen == 8) { *doublebuf = (double) atof (floatchar); sqlda_in->sqlvar[param].sqldata = (char *) doublebuf; } else { *floatbuf = (float) atof (floatchar); sqlda_in->sqlvar[param].sqldata = (char *) floatbuf; } break; /* exit the switch block */ } case SQLDA_DATE : /* date type variable */ { char *datebuf; printf ("\nDate/time format is 'DD-MMM-YYYY HH:MM:SS.ss'"); printf ("\ndate> "); /* get the string */ bp = gets (datechar); if (bp == NULL) return (-1); /* make sure it's in uppercase! */ for (i=0; i < strlen(datechar); i++) *(datechar+i) = toupper (*(datechar+i)); /* check for d-mmm-yyyy */ if (*(datechar+1) == '-') strcpy (legaldate+1, datechar); else strcpy (legaldate, datechar); /* Convert the date to DATE datatype format */ t_dsc.dsc$b_class = DSC$K_CLASS_S; t_dsc.dsc$b_dtype = DSC$K_DTYPE_T; t_dsc.dsc$w_length = DATE_SIZE; t_dsc.dsc$a_pointer = legaldate; if (new_statement == TRUE) /* allocate storage for date */ datebuf = (char *) malloc (sqlda_in->sqlvar[param].sqllen); if ((status = sys$bintim(&t_dsc, datebuf)) != SS$_NORMAL) { printf("\n\nInvalid date = '%s', status %d", legaldate, status ); return (-1); } else status = 0; /* assign the address to sqlda */ sqlda_in->sqlvar[param].sqldata = datebuf; break; /* exit the switch block */ } default : printf("\n\nError in get_in_params(): no data type match on %d", sqlda_in->sqlvar[param].sqltype); status = -1; break; } /* end switch */ } /* end the for loop */ return (status); } /* end get_in_params routine */ /*-------------------------------------------------------------------------- */ /* ** ALLOCATE_BUFFERS allocates storage for select list items in the ** statement string supplied by the user. It also allocates storage for ** indicator parameters associated with the select list items, which indicate ** the occurrence of a NULL value being returned for an item in the database. */ int allocate_buffers(sqlda_out) sqlda sqlda_out; { int param, status; long *indicator_param; status = 0; /* For each parameter marker indicated by SQLDA_OUT.SQLD, execute a loop */ for (param = 0; param < sqlda_out->sqld; param++) { /* Allocate storage for an indicator array, INDBUF */ indicator_param = (long *) malloc (sizeof (long)); /* Write the address of INDBUF to SQLDA_OUT.SQLVAR.SQLIND */ sqlda_out->sqlvar[param].sqlind = indicator_param; /* ** Check the value of SQLDA_OUT.SQLVAR.SQLTYPE to determine the data ** type of the select list item and branch to allocation block ** for that data type. */ switch (sqlda_out->sqlvar[param].sqltype) { case SQLDA_VARCHAR : /* variable char variable */ /* allocate storage + assign address for the character string */ varchar_buf = (varchar *) malloc (sizeof (varchar)); /* assign the address to sqlda */ sqlda_out->sqlvar[param].sqldata = (char *) varchar_buf; break; /* exit the switch block */ case SQLDA_CHAR : /* character variable */ /* allocate storage + assign address for the character string */ charbuf = (char *) malloc (sqlda_out->sqlvar[param].sqllen); /* assign the address to sqlda */ sqlda_out->sqlvar[param].sqldata = charbuf; break; /* exit the switch block */ case SQLDA_SEGSTRING : /* segmented string */ case SQLDA_QUADWORD : /* bigint */ { #ifdef _alpha int64 *quadbuf; /* allocate storage for the quadword */ quadbuf = (int64 *) malloc (sizeof (int64)); #else long *quadbuf; quadbuf = (long *) malloc (sizeof (long) * 2); #endif /* assign the address to sqlda */ sqlda_out->sqlvar[param].sqldata = (char *) quadbuf; break; /* exit the switch block */ } case SQLDA_INTEGER : /* integer variable */ case SQLDA_SMALLINT : /* short int variable */ case SQLDA_TINYINT : /* tiny int variable */ { long *longbuf; /* allocate storage for the integer (even for the smaller types) */ longbuf = (long *) malloc (sizeof (long)); /* assign the address to sqlda */ sqlda_out->sqlvar[param].sqldata = (char *) longbuf; break; /* exit the switch block */ } case SQLDA_FLOAT : /* floating variable */ { float *floatbuf; double *doublebuf; /* allocate storage for the float or double */ /* and assign the address to sqlda */ if (sqlda_out->sqlvar[param].sqllen == 8) { doublebuf = (double *) malloc (sizeof (double)); sqlda_out->sqlvar[param].sqldata = (char *) doublebuf; } else { floatbuf = (float *) malloc (sizeof (float)); sqlda_out->sqlvar[param].sqldata = (char *)floatbuf; } break; /* exit the switch block */ } case SQLDA_DATE : /* date type variable */ { char *datebuf; /* allocate storage for the date and convert to bintim */ datebuf = (char *) malloc (sqlda_out->sqlvar[param].sqllen); /* assign the address to sqlda */ sqlda_out->sqlvar[param].sqldata = datebuf; break; /* exit the switch block */ } default : printf("\n\nError in allocate_buffers(): no data type match on %d", sqlda_out->sqlvar[param].sqltype); status = -1; break; } /* end switch */ } /* end the for loop */ return (status); } /* end allocate_buffers() */ /* **-------------------------------------------------------------------------- ** DISPLAY_ROW reads from SQLDA_OUT the addresses for storage ** allocated in the ALLOCATE_BUFFERS procedure. It displays the name ** and value of each column on the terminal. **-------------------------------------------------------------------------- */ int display_row(sqlda_out, saved_data) sqlda sqlda_out; mem_ptr saved_data; { int param, status; char field_name[31]; static char output [100]; char *value; status = 0; /* For each select list item indicated in SQLDA_OUT.SQLD, execute a loop */ printf ("\n----------------------------------------"); for (param = 0; param < sqlda_out->sqld; param++) { /* Copy column name to a string with a null terminator */ strncpy (&field_name[0], &sqlda_out->sqlvar[param].sqlname[0], sqlda_out->sqlvar[param].sqlname_len); field_name[sqlda_out->sqlvar[param].sqlname_len] = '\0'; /* Display the column name */ printf("\nField %s: ", field_name); /* Check if indicator variable shows a null value; if so print null */ if (*sqlda_out->sqlvar[param].sqlind < 0) printf ("NULL"); else { /* ** Otherwise, check the value of SQLDA_OUT.SQLVAR.SQLTYPE to ** determine the data type of the select list item, branch to ** the display block for that data type, and display the value. */ switch (sqlda_out->sqlvar[param].sqltype) { case SQLDA_VARCHAR: /* varchar */ { varchar *cptr = (varchar *) sqlda_out->sqlvar[param].sqldata; cptr->data [cptr->length] = '\0'; if ((saved_data[param].type == SQLDA2_DATETIME) || (saved_data[param].type == SQLDA2_INTERVAL)) printf( "[%s] ", getType(&(saved_data[param]))); printf( "%s", cptr->data ); break; } case SQLDA_CHAR: /* character variable */ /* ** Get the address of the buffer that contains ** the column value (written by the FETCH statement) ** from SQLDA_OUT.SQLVAR.SQLDATA */ /* Display the value in the buffer */ printf ("%.*s", sqlda_out->sqlvar[param].sqllen, sqlda_out->sqlvar[param].sqldata); break; case SQLDA_INTEGER: /* integer */ case SQLDA_SMALLINT: /* smallint */ case SQLDA_SEGSTRING: /* segmented string */ case SQLDA_TINYINT: /* tinyint */ case SQLDA_QUADWORD: /* bigint */ { struct dsc$descriptor_sd quad_dsc; $DESCRIPTOR( text_dsc, output ); sc_len *sptr = (sc_len *) &(sqlda_out->sqlvar[param].sqllen); switch (sqlda_out->sqlvar[param].sqltype) { case SQLDA_TINYINT: quad_dsc.dsc$b_dtype = DSC$K_DTYPE_B; break; case SQLDA_SMALLINT: quad_dsc.dsc$b_dtype = DSC$K_DTYPE_W; break; case SQLDA_INTEGER: quad_dsc.dsc$b_dtype = DSC$K_DTYPE_L; break; case SQLDA_SEGSTRING: case SQLDA_QUADWORD: quad_dsc.dsc$b_dtype = DSC$K_DTYPE_Q; break; default: quad_dsc.dsc$b_dtype = 0; } /* Check the high order byte for a scale value indicating */ /* the number of digits to the right of the decimal point */ quad_dsc.dsc$b_class = DSC$K_CLASS_SD; quad_dsc.dsc$w_length = sptr [0]; quad_dsc.dsc$a_pointer = sqlda_out->sqlvar[param].sqldata; quad_dsc.dsc$b_scale = -1 * sptr [1]; quad_dsc.dsc$b_digits = 0; /* Use LIB$CVT_DX_DX to convert to text */ status = lib$cvt_dx_dx( &quad_dsc, &text_dsc, &text_dsc.dsc$w_length ); if ( (status & 1) == 0 ) lib$signal ( status ); output [text_dsc.dsc$w_length] = 0; printf( "%s", output); break; } case SQLDA_FLOAT: /* float variable */ if (sqlda_out->sqlvar[param].sqllen == 8) printf("%f", *(double *)sqlda_out->sqlvar[param].sqldata); else printf("%f", *(float *)sqlda_out->sqlvar[param].sqldata); break; case SQLDA_DATE: /* date variable */ value = (char *) malloc (DATE_SIZE); t_dsc.dsc$b_class = DSC$K_CLASS_S; t_dsc.dsc$b_dtype = DSC$K_DTYPE_T; t_dsc.dsc$w_length = DATE_SIZE; t_dsc.dsc$a_pointer = value; sys$asctim (0, &t_dsc, sqlda_out->sqlvar[param].sqldata); printf ("%.23s", value); free (value); break; default: printf("\n\nError in display_row(): no data type match on %d", sqlda_out->sqlvar[param].sqltype); status = -1; break; } /* end switch */ } /* end if */ } /* end for */ return (status); } /* end display_row */ char *getType( saved_data ) mem_ptr saved_data; { static char typeStr [500]; if ( saved_data->type == SQLDA2_DATETIME ) { switch ( saved_data->chrono_scale ) { case 1: /* date */ sprintf( typeStr, "Date Ansi" ); break; case 2: /* time */ sprintf( typeStr, "Time (%d)", saved_data->chrono_precision ); break; case 3: /* timestamp */ sprintf( typeStr, "Timestamp (%d)", saved_data->chrono_precision ); break; default: printf( "Unsupported Subtype, %d\n", saved_data->chrono_scale ); break; } } else { switch (saved_data->len) { case 1: /* year */ { sprintf( typeStr, "Interval Year (%d)", saved_data->chrono_scale ); break; } case 2: /* month */ { sprintf( typeStr, "Interval Month (%d)", saved_data->chrono_scale ); break; } case 3: /* day */ { sprintf( typeStr, "Interval Day (%d)", saved_data->chrono_scale ); break; } case 4: /* hour */ { sprintf( typeStr, "Interval Hour (%d)", saved_data->chrono_scale ); break; } case 5: /* minute */ { sprintf( typeStr, "Interval Minute (%d)", saved_data->chrono_scale ); break; } case 6: /* second */ { sprintf( typeStr, "Interval Second (%d,%d)", saved_data->chrono_scale, saved_data->chrono_precision ); break; } case 7: /* year to month */ { sprintf( typeStr, "Interval Year (%d) To Month", saved_data->chrono_scale ); break; } case 8: /* day to hour */ { sprintf( typeStr, "Interval Day (%d) To Hour", saved_data->chrono_scale ); break; } case 9: /* day to minute */ { sprintf( typeStr, "Interval Day (%d) To Minute", saved_data->chrono_scale ); break; } case 10: /* day to second */ { sprintf( typeStr, "Interval Day (%d) To Second (%d)", saved_data->chrono_scale, saved_data->chrono_precision ); break; } case 11: /* hour to minute */ { sprintf( typeStr, "Interval Hour (%d) To Minute", saved_data->chrono_scale ); break; } case 12: /* hour to second */ { sprintf( typeStr, "Interval Hour (%d) To Second (%d)", saved_data->chrono_scale, saved_data->chrono_precision ); break; } case 13: /* minute to second */ { sprintf( typeStr, "Interval Minute (%d) To Second (%d)", saved_data->chrono_scale, saved_data->chrono_precision ); break; } default: printf( "Unsupported Subtype, %d\n", saved_data->len ); break; } } return typeStr; } /*----------------------------------------------------------------------------- ** This function uses the sql$get_error_text function to display the ** messages returned by various facilities for unexpected error conditions ** that occur. This program continues after these unexpected errors, and ** allows the user to select the exit program option on the menu. In your ** own program, you may prefer to stop the program run automatically. **----------------------------------------------------------------------------- */ void display_error_message() { char get_error_buffer[300]; t_dsc.dsc$b_class = DSC$K_CLASS_S; t_dsc.dsc$b_dtype = DSC$K_DTYPE_T; t_dsc.dsc$w_length = 300; t_dsc.dsc$a_pointer = (char *)&get_error_buffer; sql$get_error_text (&t_dsc); printf("\n\nError message:\n"); printf("%s",get_error_buffer); printf("\n"); return; }