/* Copyright © 1995, 2002, Oracle Corporation. All Rights Reserved. */ /* * This program shows how to declare C host language variables * to match a variety of data types, including the date-time * data types. The program: * * o Creates the ALL_DATATYPES database and table * o Inserts a record * o Provides examples of date-time data manipulation * o Displays results * o Deletes the database * * Although this program shows how you can use a predeclared table, * RDB$RELATIONS, to cast date-time values into host language variables, * you may use any table in your database. * */ #include #include #include #include #include main() { int sqlcode; struct { char char_var[11]; char tinyint_var; short int smallint_var; float smallint_scaled_var; int integer_var; double integer_scaled_var; double quadword_var; double quadword_scaled_var; float real_var; double double_precision_var; SQL_DATE_VMS sql_date_var; SQL_DATE_ANSI sql_date_ansi_var; SQL_TIME sql_time_var; SQL_TIMESTAMP(2) sql_delivery_date; SQL_TIMESTAMP(2) sql_order_date; SQL_TIMESTAMP(2) sql_time_stamp1; SQL_TIMESTAMP(2) sql_time_stamp2; SQL_INTERVAL(YEAR) year_interval; SQL_INTERVAL(YEAR TO MONTH) year_to_month_interval; SQL_INTERVAL(MONTH) month_interval; SQL_INTERVAL(DAY ) day_interval; SQL_INTERVAL(DAY TO HOUR) day_to_hour; SQL_INTERVAL(DAY TO MINUTE) day_to_minute_interval; SQL_INTERVAL(DAY TO SECOND) day_to_second_interval; SQL_INTERVAL(HOUR) hour_interval; SQL_INTERVAL(HOUR TO MINUTE) hour_to_minute; SQL_INTERVAL(HOUR TO SECOND) hour_to_second; SQL_INTERVAL(MINUTE) minute_interval; SQL_INTERVAL(MINUTE TO SECOND) minute_to_second; SQL_INTERVAL(SECOND(3,2)) second_interval; } all_datatypes_record; char vms_string_date[24]; int year_var,day_var,hour_var,minute_var,second_var; int week_day,month_var; SQL_INTERVAL(HOUR) hours; SQL_INTERVAL (DAY TO HOUR) day_and_hour; /* Create the database and table. */ EXEC SQL INCLUDE SQLCA; EXEC SQL CREATE DATABASE FILENAME 'all_datatypes'; EXEC SQL DECLARE RDB$RELATIONS TABLE (FOOC INTEGER); 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), quadword_col bigint, quadword_scaled_col bigint, real_col real, double_prec_col double precision, tbl_date_var DATE VMS, tbl_date_ansi DATE ANSI, tbl_time_var TIME, tbl_delivery_date TIMESTAMP(2), tbl_order_date TIMESTAMP(2), tbl_timestamp TIMESTAMP(2), tbl_timestamp1 TIMESTAMP(2), tbl_int_year INTERVAL YEAR, tbl_int_year_to_month INTERVAL YEAR TO MONTH, tbl_int_month INTERVAL MONTH, tbl_int_day INTERVAL DAY, tbl_int_day_to_hour INTERVAL DAY TO HOUR, tbl_int_day_to_minute INTERVAL DAY TO MINUTE, tbl_int_day_to_second INTERVAL DAY TO SECOND, tbl_int_hour INTERVAL HOUR, tbl_int_hour_to_minute INTERVAL HOUR TO MINUTE, tbl_int_hour_to_second INTERVAL HOUR TO SECOND, tbl_int_minute INTERVAL MINUTE, tbl_int_minute_to_second INTERVAL MINUTE TO SECOND, tbl_int_second INTERVAL SECOND(3,2)); /* * Store values in three columns in a row. You can use the CAST function * to convert a character string to a DATE data type. The following example * shows how to cast a character string as the DATE ANSI data type. */ EXEC SQL INSERT INTO ALL_DATATYPES_TABLE (CHAR_COL, TINYINT_COL, TBL_DATE_ANSI) VALUES ('Test', 10, CAST( '1992-4-19' AS DATE ANSI)); /* Delete the row. */ EXEC SQL DELETE FROM ALL_DATATYPES_TABLE; /* Move data into variables. */ strcpy (all_datatypes_record.char_var, "CHARACTERS"); 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.quadword_var = -999999999999999999.; all_datatypes_record.quadword_scaled_var = 9999999999999.99999; all_datatypes_record.real_var = 0.1234567; all_datatypes_record.double_precision_var = 0.123456789012345; /* * Use the CAST function to convert the CHAR host variable * vms_string_date into various date-time data types. * Note the difference between DATE VMS and DATE ANSI formats. */ strcpy (vms_string_date, "1991012410121354"); EXEC SQL SELECT CAST(:vms_string_date AS DATE VMS) INTO :all_datatypes_record.sql_date_var FROM RDB$RELATIONS LIMIT TO 1 ROW; strcpy (vms_string_date, "1995-01-4"); EXEC SQL SELECT CAST(:vms_string_date AS DATE ANSI) INTO :all_datatypes_record.sql_date_ansi_var FROM RDB$RELATIONS LIMIT TO 1 ROW ; strcpy (vms_string_date,"12:14:54"); EXEC SQL SELECT CAST(:vms_string_date AS TIME) INTO :all_datatypes_record.sql_time_var FROM RDB$RELATIONS LIMIT TO 1 ROW; /* * You can also pass values as literals. */ EXEC SQL SELECT CAST('1990-2-13:11:54:30.0' AS TIMESTAMP) INTO :all_datatypes_record.sql_delivery_date FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('1990-2-12:11:44:22.0' AS TIMESTAMP) INTO :all_datatypes_record.sql_order_date FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('1990-2-13:11:59:00.0' AS TIMESTAMP) INTO :all_datatypes_record.sql_time_stamp1 FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('1990-2-13:11:54:54.0' AS TIMESTAMP) INTO :all_datatypes_record.sql_time_stamp2 FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('-23' AS INTERVAL YEAR) INTO :all_datatypes_record.year_interval FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('12-6' AS INTERVAL YEAR TO MONTH) INTO :all_datatypes_record.year_to_month_interval FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('11' AS INTERVAL MONTH) INTO :all_datatypes_record.month_interval FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('-29' AS INTERVAL DAY) INTO :all_datatypes_record.day_interval FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('-29:11' AS INTERVAL DAY TO HOUR) INTO :all_datatypes_record.day_to_hour FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('-14:10:12' AS INTERVAL DAY TO MINUTE) INTO :all_datatypes_record.day_to_minute_interval FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('-14:22:12:55.00' AS INTERVAL DAY TO SECOND) INTO :all_datatypes_record.day_to_second_interval FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('22' AS INTERVAL HOUR) INTO :all_datatypes_record.hour_interval FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('22:59' AS INTERVAL HOUR TO MINUTE) INTO :all_datatypes_record.hour_to_minute FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('-22:57:22.00' AS INTERVAL HOUR TO SECOND) INTO :all_datatypes_record.hour_to_second FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('-55' AS INTERVAL MINUTE) INTO :all_datatypes_record.minute_interval FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('-55:22.0' AS INTERVAL MINUTE TO SECOND) INTO :all_datatypes_record.minute_to_second FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL SELECT CAST('52.00' AS INTERVAL SECOND) INTO :all_datatypes_record.second_interval FROM RDB$RELATIONS LIMIT TO 1 ROW; EXEC SQL INSERT INTO ALL_DATATYPES_TABLE VALUES (:all_datatypes_record); if (SQLCA.SQLCODE != 0) { printf("Error inserting record \n"); EXEC SQL ROLLBACK; sql_signal(); } /* * The following example shows how to extract subtypes of a date-time datatype. */ EXEC SQL DECLARE A_CURSOR CURSOR FOR SELECT EXTRACT (DAY FROM TBL_INT_DAY_TO_SECOND), EXTRACT (SECOND FROM TBL_INT_DAY_TO_SECOND), EXTRACT (HOUR FROM TBL_INT_HOUR_TO_MINUTE), EXTRACT(MINUTE FROM TBL_INT_HOUR_TO_MINUTE) FROM ALL_DATATYPES_TABLE; if(SQLCA.SQLCODE != 0) { printf("Error creating cursor a_cursor\n"); sql_signal(); EXEC SQL ROLLBACK; } EXEC SQL OPEN A_CURSOR; EXEC SQL FETCH A_CURSOR into :day_var,:second_var, :hour_var,:minute_var; if(SQLCA.SQLCODE != 0) { printf(" Error at fetch cursor \n"); sql_signal(); EXEC SQL ROLLBACK; } else { printf("Stored - 14 days and retrieved %d\n",day_var); printf("Stored - 22 hours and retrieved %d\n",hour_var); printf("Stored 59 minutes and retrieved %d\n",minute_var); printf("Stored - 55 seconds and retrieved %d\n",second_var); } /* * The following example shows how to use the CAST function to convert * a binary value into a variable character string. */ EXEC SQL SELECT CAST(TBL_DATE_VAR AS VARCHAR(40)) INTO :vms_string_date FROM ALL_DATATYPES_TABLE; if(SQLCA.SQLCODE != 0) { printf("Error in VARCHAR example\n"); sql_signal(); } else printf("String expected 1991012410121354, %s\n",vms_string_date); /* * The following example subtracts one date from another, extracts the YEAR * portion of the date, and prints it. Note that the DATE VMS data * must be cast as DATE ANSI before you can use it to perform date arithmetic. */ EXEC SQL SELECT EXTRACT (YEAR FROM (TBL_DATE_ANSI - CAST(TBL_DATE_VAR AS DATE ANSI)) YEAR) INTO :year_var from ALL_DATATYPES_TABLE ; if (SQLCA.SQLCODE != 0) { printf("Error at extract YEAR\n "); sql_signal(); } else printf("Difference in YEARS should be: 4 %d \n ", year_var); /* * The following example shows how to extract the day of the week, the day, * and the year from a date variable. */ EXEC SQL SELECT EXTRACT(WEEKDAY FROM TBL_DATE_VAR), EXTRACT(DAY FROM TBL_DATE_VAR), EXTRACT(MONTH FROM TBL_DATE_VAR), EXTRACT(YEAR FROM TBL_DATE_VAR) into :week_day, :day_var, :month_var,:year_var FROM ALL_DATATYPES_TABLE; if (SQLCA.SQLCODE != 0) { printf(" Error selecting items\n"); sql_signal(); EXEC SQL ROLLBACK; } else { printf("WEEKDAY expected 4, got %d \n", week_day); printf("DAY expected 24, got %d \n", day_var); printf("MONTH expected 1, got %d \n", month_var); printf("YEAR expected 1991, got %d \n", year_var); }; /* * The following example uses the CAST function to update the database column * TBL_INT_HOUR with a value from a character string stored in vms_string_date. * The example then adds 10 days 11 hours to the field TBL_INT_DAY_TO_HOUR, * and displays the results. */ strcpy(vms_string_date,"4"); EXEC SQL DECLARE UPDATE_CURSOR CURSOR FOR SELECT TBL_INT_DAY_TO_HOUR,TBL_INT_HOUR FROM ALL_DATATYPES_TABLE; EXEC SQL OPEN UPDATE_CURSOR; EXEC SQL FETCH UPDATE_CURSOR INTO :day_and_hour,:hours; EXEC SQL UPDATE ALL_DATATYPES_TABLE SET TBL_INT_DAY_TO_HOUR = :day_and_hour + CAST('10:11' AS INTERVAL DAY TO HOUR), TBL_INT_HOUR = :hours - CAST(:vms_string_date AS INTERVAL HOUR) WHERE CURRENT OF UPDATE_CURSOR ; if (SQLCA.SQLCODE != 0) { printf(" Error updating database \n"); sql_signal(); } EXEC SQL CLOSE UPDATE_CURSOR; if(SQLCA.SQLCODE != 0) { printf("Error closing update_cursor\n"); sql_signal(); EXEC SQL ROLLBACK; } EXEC SQL SELECT CAST (TBL_INT_DAY_TO_HOUR AS VARCHAR(40)) INTO :vms_string_date FROM ALL_DATATYPES_TABLE; if(SQLCA.SQLCODE != 0) { printf("Error extracting value as varchar \n"); sql_signal(); EXEC SQL ROLLBACK; } else printf("Expected data is -19 days, got :%s\n",vms_string_date); EXEC SQL SELECT CAST (TBL_INT_HOUR AS VARCHAR(40)) INTO :vms_string_date FROM ALL_DATATYPES_TABLE; if(SQLCA.SQLCODE != 0) { printf("Error retrieving update interval hour\n"); sql_signal(); EXEC SQL ROLLBACK; } else { printf ("Expected data is 18 hrs, got %s\n",vms_string_date); EXEC SQL ROLLBACK; }; printf("Dropping the database \n"); EXEC SQL DROP DATABASE FILENAME all_datatypes; if(SQLCA.SQLCODE != 0 ) { printf("Error deleting database \n"); EXEC SQL ROLLBACK; } exit(1); }