/* Copyright © Oracle Corporation 1995. All Rights Reserved. */ /* * * 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 * * This program must be linked with appropriate C support * routines. Unless you specify the NOG_FLOATING qualifier * when you precompile this program, be sure to link the program * with routines that support the G-floating interpretation of * double-precision floating-point values. */ #include #include #include #include #include main() { /* * Declare the variable for monitoring execution status of SQL * statements. If you omit this declaration, you must specify * the statement EXEC SQL INCLUDE SQLCA; and must qualify * references to SQLCODE (SQLCA.SQLCODE). */ int SQLCODE; /* Declare a structure for row-level operations. The structure * has subordinate elementary variables for most column-level * operations. For this structure, note the following points. * * Any char variables that correspond to CHAR and VARCHAR columns * are defined as the length of the longest valid column value * plus 1. (SQL expects null terminators on all values * you are storing in CHAR and VARCHAR columns, and SQL puts a * null terminator into all character strings you retrieve * from the database.) C does not directly support the varying * character data type; however, the ASCIZ strings you * manipulate for char variables contain null terminators that * SQL uses to determine string length when you are storing data * in columns defined as VARCHAR. * * C does not support fixed-point binary numbers that are * scaled, so you declare floating-point variables for any * columns that can contain fixed-point binary values with a * fractional component. * * C does not support fixed-point binary values of bigint * size, even if the values are unscaled. For columns defined as * BIGINT, you can define variables with the double data type, * but you should note that this variable can retrieve only * approximations of the larger values that are valid for * BIGINT columns. These larger bigint values might be * stored in the database by applications not written in C. */ struct { char char_var[11]; char tinyint_var; short int smallint_var; float smallint_scaled_var; int integer_var; double integer_scaled_var; double bigint_var; double bigint_scaled_var; float real_var; double double_precision_var; SQL_DATE_VMS bin_date_var; char varchar_var[41]; } all_datatypes_record; /* 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. */ union { char dsri_string_date[17]; struct { 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]; } date_group; } date_union; 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[12] = {0,0,0,0,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. */ short int char_ind = 0; short int tinyint_ind = 0; short int smallint_ind = 0; short int smallint_scaled_ind = 0; short int integer_ind = 0; short int integer_scaled_ind = 0; short int bigint_ind = 0; short int bigint_scaled_ind = 0; short int real_ind = 0; short int double_precision_ind = 0; short int date_ind = 0; short int varchar_ind = 0; /* Create the database and table.*/ EXEC SQL CREATE DATABASE FILENAME 'all_datatypes'; if (SQLCODE < 0) { printf("\nCREATE DATABASE failed %d", SQLCODE); sql_signal(); } /* The exact definition of RDB$DATABASE isn't important * because we never use any of the columns. We're just * using it to do singleton-selects for datatype * conversion */ EXEC SQL DECLARE RDB$DATABASE TABLE (A INT); /* Monitor execution of subsequent SQL statements using a * WHENEVER statement. */ EXEC SQL WHENEVER SQLERROR GO TO sql_error_handler_1; EXEC SQL CREATE TABLE ALL_DATATYPES_TABLE ( CHAR_COL CHAR(10), TINYINT_COL TINYINT, SMALLINT_COL SMALLINT, SMALLINT_SCALED_COL SMALLINT (3), INTEGER_COL INTEGER, INTEGER_SCALED_COL INTEGER (2), BIGINT_COL BIGINT, BIGINT_SCALED_COL BIGINT (5), REAL_COL REAL, DOUBLE_PREC_COL DOUBLE PRECISION, DATE_COL DATE, VARCHAR_COL VARCHAR(40) ); /* This is the date we are using: printf ("\nDATE_COL: 22-APR-1987 10:17:56.00"); */ EXEC SQL SELECT CAST('1987042210175600' AS DATE VMS) INTO :all_datatypes_record.bin_date_var FROM RDB$DATABASE LIMIT TO 1 ROW; /* 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.) */ strcpy (all_datatypes_record.char_var, "SAM O'DELL"); all_datatypes_record.tinyint_var = -128; all_datatypes_record.smallint_var = -32768; all_datatypes_record.smallint_scaled_var = 32.767; all_datatypes_record.integer_var = 0X80000000; all_datatypes_record.integer_scaled_var = 21474836.47; all_datatypes_record.bigint_var = -999999999999999999.; all_datatypes_record.bigint_scaled_var = 9999999999999.99999; all_datatypes_record.real_var = 0.1234567; all_datatypes_record.double_precision_var = 0.123456789012345; strcpy (all_datatypes_record.varchar_var, "This string is 39 characters in length"); /* 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 (all_datatypes_record.tinyint_var == 0) indicator_item[1] = -1; if (all_datatypes_record.smallint_var == 0) indicator_item[2] = -1; if (all_datatypes_record.smallint_scaled_var == 0) indicator_item[3] = -1; if (all_datatypes_record.integer_var == 0) indicator_item[4] = -1; if (all_datatypes_record.integer_scaled_var == 0) indicator_item[5] = -1; if (all_datatypes_record.bigint_var == 0) indicator_item[6] = -1; if (all_datatypes_record.bigint_scaled_var == 0) indicator_item[7] = -1; if (all_datatypes_record.real_var == 0) indicator_item[8] = -1; if (all_datatypes_record.double_precision_var == 0) indicator_item[9] = -1; indicator_item[10] = 1; if (strcmp( all_datatypes_record.varchar_var, " " ) == 0 ) indicator_item[11] = -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). */ EXEC SQL INSERT INTO ALL_DATATYPES_TABLE VALUES (:all_datatypes_record:indicator_item); /* Now retrieve and display the row. */ EXEC SQL DECLARE CURSED_CONVERSIONS CURSOR FOR SELECT * FROM ALL_DATATYPES_TABLE FOR UPDATE OF CHAR_COL, TINYINT_COL, SMALLINT_COL, INTEGER_COL, BIGINT_COL; EXEC SQL OPEN CURSED_CONVERSIONS; EXEC SQL FETCH CURSED_CONVERSIONS INTO :all_datatypes_record.char_var:char_ind, :all_datatypes_record.tinyint_var:tinyint_ind, :all_datatypes_record.smallint_var:smallint_ind, :all_datatypes_record.smallint_scaled_var:smallint_scaled_ind, :all_datatypes_record.integer_var:integer_ind, :all_datatypes_record.integer_scaled_var:integer_scaled_ind, :all_datatypes_record.bigint_var:bigint_ind, :all_datatypes_record.bigint_scaled_var:bigint_scaled_ind, :all_datatypes_record.real_var:real_ind, :all_datatypes_record.double_precision_var:double_precision_ind, :date_union.dsri_string_date:date_ind, :all_datatypes_record.varchar_var:varchar_ind; /* Display the stored row. :all_datatypes_record.bin_date_var:date_ind, */ printf ("\nThis is the stored row."); if (char_ind < 0) printf ("\n\nCHAR_COL: NULL"); else printf ("\nCHAR_COL: %s", all_datatypes_record.char_var); if (tinyint_ind < 0) printf ("\nTINYINT_COL: NULL"); else printf ("\nTINYINT_COL: %d", all_datatypes_record.tinyint_var); if (smallint_ind < 0) printf ("\nSMALLINT_COL: NULL"); else printf ("\nSMALLINT_COL: %d", all_datatypes_record.smallint_var); if (smallint_scaled_ind < 0) printf ("\nSMALLINT_SCALED_COL: NULL"); else printf ("\nSMALLINT_SCALED_COL: %e", all_datatypes_record.smallint_scaled_var); if (integer_ind < 0) printf ("\nINTEGER_COL: NULL"); else printf ("\nINTEGER_COL: %d", all_datatypes_record.integer_var); if (integer_scaled_ind < 0) printf ("\nINTEGER_SCALED_COL: NULL"); else printf ("\nINTEGER_SCALED_COL: %e", all_datatypes_record.integer_scaled_var); if (bigint_ind < 0) printf ("\nBIGINT_COL: NULL"); else printf ("\nBIGINT_COL: %e", all_datatypes_record.bigint_var); if (bigint_scaled_ind < 0) printf ("\nBIGINT_SCALED_COL: NULL"); else printf ("\nBIGINT_SCALED_COL: %e", all_datatypes_record.bigint_scaled_var); if (real_ind < 0) printf ("\nREAL_COL: NULL"); else printf ("\nREAL_COL: %e", all_datatypes_record.real_var); if (double_precision_ind < 0) printf ("\nDOUBLE_PRECISION_COL: NULL"); else printf ("\nDOUBLE_PRECISION_COL: %e", all_datatypes_record.double_precision_var); if (date_ind < 0) printf ("\nDATE_COL: NULL"); else { strncpy(dup_year_var2, date_union.date_group.year_var2,2); dup_year_var2[2] = 0; strncpy(dup_month_var, date_union.date_group.month_var,2); dup_month_var[2] = 0; strncpy(dup_day_var, date_union.date_group.day_var,2); dup_day_var[2] = 0; strncpy(dup_hour_var, date_union.date_group.hour_var,2); dup_hour_var[2] = 0; strncpy(dup_minute_var, date_union.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); } if (varchar_ind < 0) printf ("\nVARCHAR_COL: NULL"); else printf ("\nVARCHAR_COL: %s", all_datatypes_record.varchar_var); /* 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. */ char_ind = 0; smallint_ind = -1; integer_ind = -1; bigint_ind = -1; /* Change the old value in char_var and tinyint_var. */ strcpy (all_datatypes_record.char_var, "Sam O'Dell"); all_datatypes_record.tinyint_var = 127; /* Update the row. */ EXEC SQL UPDATE ALL_DATATYPES_TABLE SET CHAR_COL = :all_datatypes_record.char_var:char_ind, TINYINT_COL = :all_datatypes_record.tinyint_var:tinyint_ind, SMALLINT_COL = :all_datatypes_record.smallint_var:smallint_ind, INTEGER_COL = :all_datatypes_record.integer_var:integer_ind, BIGINT_COL = :all_datatypes_record.bigint_var:bigint_ind WHERE CURRENT OF CURSED_CONVERSIONS; /* 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. */ EXEC SQL CLOSE CURSED_CONVERSIONS; EXEC SQL OPEN CURSED_CONVERSIONS; EXEC SQL FETCH CURSED_CONVERSIONS INTO :all_datatypes_record.char_var:char_ind, :all_datatypes_record.tinyint_var:tinyint_ind, :all_datatypes_record.smallint_var:smallint_ind, :all_datatypes_record.smallint_scaled_var:smallint_scaled_ind, :all_datatypes_record.integer_var:integer_ind, :all_datatypes_record.integer_scaled_var:integer_scaled_ind, :all_datatypes_record.bigint_var:bigint_ind, :all_datatypes_record.bigint_scaled_var:bigint_scaled_ind, :all_datatypes_record.real_var:real_ind, :all_datatypes_record.double_precision_var:double_precision_ind, :date_union.dsri_string_date:date_ind, :all_datatypes_record.varchar_var:varchar_ind; /* Display the changed row. */ printf ("\n\nThis is the changed row."); if (char_ind < 0) printf ("\n\nCHAR_COL: NULL"); else printf ("\nCHAR_COL: %s", all_datatypes_record.char_var); if (tinyint_ind < 0) printf ("\nTINYINT_COL: NULL"); else printf ("\nTINYINT_COL: %d", all_datatypes_record.tinyint_var); if (smallint_ind < 0) printf ("\nSMALLINT_COL: NULL"); else printf ("\nSMALLINT_COL: %d", all_datatypes_record.smallint_var); if (smallint_scaled_ind < 0) printf ("\nSMALLINT_SCALED_COL: NULL"); else printf ("\nSMALLINT_SCALED_COL: %e", all_datatypes_record.smallint_scaled_var); if (integer_ind < 0) printf ("\nINTEGER_COL: NULL"); else printf ("\nINTEGER_COL: %d", all_datatypes_record.integer_var); if (integer_scaled_ind < 0) printf ("\nINTEGER_SCALED_COL: NULL"); else printf ("\nINTEGER_SCALED_COL: %e", all_datatypes_record.integer_scaled_var); if (bigint_ind < 0) printf ("\nBIGINT_COL: NULL"); else printf ("\nBIGINT_COL: %e", all_datatypes_record.bigint_var); if (bigint_scaled_ind < 0) printf ("\nBIGINT_SCALED_COL: NULL"); else printf ("\nBIGINT_SCALED_COL: %e", all_datatypes_record.bigint_scaled_var); if (real_ind < 0) printf ("\nREAL_COL: NULL"); else printf ("\nREAL_COL: %e", all_datatypes_record.real_var); if (double_precision_ind < 0) printf ("\nDOUBLE_PRECISION_COL: NULL"); else printf ("\nDOUBLE_PRECISION_COL: %e", all_datatypes_record.double_precision_var); if (date_ind < 0) printf ("\nDATE_COL: NULL"); else { strncpy(dup_year_var2, date_union.date_group.year_var2,2); dup_year_var2[2] = 0; strncpy(dup_month_var, date_union.date_group.month_var,2); dup_month_var[2] = 0; strncpy(dup_day_var, date_union.date_group.day_var,2); dup_day_var[2] = 0; strncpy(dup_hour_var, date_union.date_group.hour_var,2); dup_hour_var[2] = 0; strncpy(dup_minute_var, date_union.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); } if (varchar_ind < 0) printf ("\nVARCHAR_COL: NULL"); else printf ("\nVARCHAR_COL: %s", all_datatypes_record.varchar_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. */ EXEC SQL WHENEVER SQLERROR GO TO sql_error_handler_2; EXEC SQL ROLLBACK; /* Delete the database and stop the program with normal exit status. */ EXEC SQL DROP DATABASE FILENAME all_datatypes; #ifdef VMS exit(0); #endif #if defined(__osf__) || defined (_WIN32) exit(1); #endif /* Error-handling sections */ sql_error_handler_1: printf ("\nSQLCODE is %d", SQLCODE); printf ("\nError on attempt to create table, or on"); printf ("\nattempt to insert, update, or retrieve data."); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK; if (SQLCODE < 0) { printf ("\nROLLBACK failed. Delete database files manually."); sql_signal(); } sql_error_handler_2: printf ("\nSQLCODE is %d", SQLCODE); printf ("\nDatabase could not be deleted."); printf ("\nDelete database files manually."); sql_signal(); }