/ precompiler demonstration program ----------- ------------- ------- Creates ABC Auto Part Company demo database. */ #include#include #include #include #include EXEC SQL BEGIN DECLARE SECTION; int table_count; int part_no; char part_name[21]; long quantity; short quantity_null_indicator; char customer_id[11]; char customer_name[21]; short name_null_indicator; char customer_address[21]; short address_null_indicator; char sql_statement_text[150]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE part_cursor CURSOR FOR select part_no, name, quantity from part; EXEC SQL DECLARE part_list_cursor CURSOR FOR select part_no, name, quantity from part order by part_no; EXEC SQL DECLARE customer_id_list_cursor CURSOR FOR select customer_id, name, address from customer order by customer_id; EXEC SQL DECLARE customer_name_list_cursor CURSOR FOR select name, customer_id, address from customer order by name; EXEC SQL DECLARE sql_update_statement STATEMENT; struct sqlda *sqlda; int sqlda_size; int number_of_columns; int column; char *dummy; FILE *file; int record_qty, record_no; #ifdef NO_PROTO void display_customers_by_id (); void display_customers_by_name (); void display_parts (); void display_parts_with_variable_select (); #else void display_customers_by_id (); void display_customers_by_name (); void display_parts (); void display_parts_with_variable_select (char *title, char *sql_statement); #endif EXEC SQL WHENEVER SQLERROR GOTO sqlerror_label; EXEC SQL WHENEVER NOT FOUND GOTO not_found_label; EXEC SQL WHENEVER SQLWARNING GOTO sqlwarning_label; int main () { printf ("\n********************************START OF DEMO********************************\n\n"); printf ("\n"); printf ("This program creates the ABC Auto Part Company demo database.\n"); printf ("The database contains:\n"); printf (" a short customer table,\n"); printf (" a simple part table,\n"); printf (" a shipment table,\n"); printf (" a shipment item table.\n"); printf ("The database name is \"abc\".\n"); printf ("The program shows different ways of inserting, updating and\n"); printf ("selecting data from the database.\n\n"); /*-------------------------------------------------------------------*/ /* initialize database management system This is not necessary when there is no sql command executed before connecting to database with the dbms_connect_database function. It is needed here because we create the database first. */ /*-------------------------------------------------------------------*/ /* drop and re-create database */ EXEC SQL WHENEVER SQLERROR CONTINUE; printf ("Dropping \"abc\" database."); EXEC SQL drop database abc; printf (" -- done\n"); printf ("Creating database.\n"); printf ("If this is your first database, it may take a few seconds.\n"); EXEC SQL create database abc; EXEC SQL WHENEVER SQLERROR GOTO sqlerror_label; /*-------------------------------------------------------------------*/ /* open database */ printf ("Opening \"abc\" database."); if (DBMS_connect_database ("abc", "", 0)) { printf ("\"abc\" database cannot be opened."); return (1); } printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* create tables */ /* create "part" table */ printf ("Creating \"part\" table."); EXEC SQL create table part (part_no smallint not null, name asciz (21), quantity integer); EXEC SQL commit; printf (" -- done\n"); /* create "customer" table */ printf ("Creating \"customer\" table."); EXEC SQL create table customer (customer_id asciz (11) not null, name asciz (21) not null, address asciz (21) ); EXEC SQL commit; printf (" -- done\n"); /* create "shipment" table */ printf ("Creating \"shipment\" table."); EXEC SQL create table shipment (shipment_no integer not null, customer_id asciz (11) not null, date asciz ( 9)); EXEC SQL commit; printf (" -- done\n"); /* create "shipment item" table */ printf ("Creating \"shipment_item\" table."); EXEC SQL create table shipment_item (shipment_no integer not null, part_no smallint not null, quantity integer); EXEC SQL commit; printf (" -- done\n"); /* count number of tables created */ table_count = 9999; EXEC SQL select count(*) into :table_count from rdbms_table where database_name = 'abc' and owner = 'user'; printf ("%d user tables in database.\n", table_count); /*-------------------------------------------------------------------*/ /* create indexes on tables */ printf ("Creating index on \"part\"."); EXEC SQL create unique index part_ix on part (part_no); EXEC SQL commit; printf (" -- done\n"); printf ("Creating index on \"customer\"."); EXEC SQL create unique index customer_ix on customer (customer_id); EXEC SQL commit; printf (" -- done\n"); printf ("Creating indexes on \"shipment\"."); EXEC SQL create unique index shipment_ix1 on shipment (shipment_no); EXEC SQL commit; EXEC SQL create index shipment_ix2 on shipment (customer_id); EXEC SQL commit; printf (" -- done\n"); printf ("Creating indexes on \"shipment_item\"."); EXEC SQL create unique index shipment_item_ix1 on shipment_item (shipment_no, part_no); EXEC SQL commit; EXEC SQL create index shipment_item_ix2 on shipment_item (part_no); EXEC SQL commit; printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* insert parts in 3 different ways */ /* 1 - create some parts with predefined values */ printf ("Inserting parts with predefined values."); EXEC SQL insert into part (part_no, name, quantity) values (1010, 'starter', 52); EXEC SQL insert into part (part_no, name, quantity) values (1021, 'alternator', 95); EXEC SQL commit; printf (" -- done\n"); /* 2 - create more parts by reading an external file and using internal variables */ printf ("Inserting parts from \"part.da1\"."); /* open data file */ if ((file = fopen ("part.da1", "r")) == NULL ) { printf ("\a\nUnable to open \"parts.da1\" file.\n"); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL disconnect database; return (1); } rewind (file); /* read list size and data, the '~' is used as a delimiter */ fscanf (file, "%i\n", &record_qty); /* read part table data from file */ /* see scanf() "C" function reference for the meaning of %[^|] */ for (record_no = 0; record_no < record_qty; record_no++) { /* read part number, name and quantity */ fscanf (file, "%i|", &part_no); fscanf (file, "%[^|]|", part_name); fscanf (file, "%i\n", &quantity); /* insert into part table */ EXEC SQL insert into part (part_no, name, quantity) values (:part_no, :part_name, :quantity); } EXEC SQL commit; fclose (file); printf (" -- done\n"); /* 3 - create even more parts by having the dbms itself read an external file */ printf ("Inserting parts from \"part.da2\"."); EXEC SQL insert into part (part_no, name, quantity) from file 'part.da2' (integer, character, integer); EXEC SQL commit; printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* create customers */ printf ("Inserting customers from \"customer.dat\"."); EXEC SQL insert into customer (customer_id, name, address) from file 'customer.dat' (character, character, character); EXEC SQL commit; printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* create shipments */ printf ("Inserting shipments from \"shipment.dat\"."); EXEC SQL insert into shipment (shipment_no, customer_id, date) from file 'shipment.dat' (integer, character, character); EXEC SQL commit; printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* create shipment items */ printf ("Inserting shipment items from \"ship_it.dat\"."); EXEC SQL insert into shipment_item (shipment_no, part_no, quantity) from file 'ship_it.dat' (integer, integer, integer); EXEC SQL commit; printf (" -- done\n"); /*-------------------------------------------------------------------*/ /*-------------------------------------------------------------------*/ /* Update some customer addresses in four different ways */ /* 1 - update customer address by using a simple SQL sql_update_statement */ /* Let's update Gerry Goodhand's address. */ printf ("Updating Gerry Goodhand's address."); EXEC SQL update customer set address = '104 Bellevue Dr.' where customer_id = 'GOO1072 '; printf (" -- done\n"); /* 2 - update customer address from data in internal variables */ /* Let's update Helen Vincent's address. */ printf ("Updating Helen Vincent address."); strcpy (customer_address, "412 Alvarado Ave."); strcpy (customer_id, "VIN0781C"); EXEC SQL update customer set address = :customer_address where customer_id = :customer_id; printf (" -- done\n"); /* 3 - update customer address from a statement in an internal variable */ /* Let's update Acme's address */ printf ("Updating Acme's address."); strcpy (sql_statement_text, "update customer set address = '5137 42nd St.' where customer_id = 'ACM0588B'"); EXEC SQL PREPARE sql_update_statement FROM :sql_statement_text; EXEC SQL EXECUTE sql_update_statement; printf (" -- done\n"); /* 4 - combine the two previous examples */ /* Let's update T&J Motor's address */ printf ("Updating T&J Motor's address."); strcpy (sql_statement_text, "update customer set address = ?s where customer_id = ?s"); strcpy (customer_address, "915 Lawrence St."); strcpy (customer_id, "TJX0983S"); EXEC SQL PREPARE sql_update_statement FROM :sql_statement_text; EXEC SQL EXECUTE sql_update_statement USING :customer_address, :customer_id; printf (" -- done\n"); /* Let's also update Alan Teng's address with the same prepared sql_update_statement */ printf ("Updating Alan Teng's address."); strcpy (customer_address, "303 Eastwood Road"); strcpy (customer_id, "TEN0289C"); EXEC SQL EXECUTE sql_update_statement USING :customer_address, :customer_id; printf (" -- done\n"); /* commit changes */ EXEC SQL commit; printf ("Changes commited.\n"); /*-------------------------------------------------------------------*/ /* Update a part quantity by using internal variables. */ /* Reduce spark plugs stock by 50. */ printf ("Reducing spark plugs stock."); part_no = 9104; quantity = -50; EXEC SQL update part set quantity = quantity + :quantity where part_no = :part_no; EXEC SQL commit; printf (" -- done\n"); display_parts_with_variable_select ("spark plugs list:", "select part_no, name, quantity from part where part_no = 9104"); display_parts_with_variable_select ("list 1 of parts having less than 10 items:", "select part_no, name, quantity from part where quantity < 10"); /*-------------------------------------------------------------------*/ /* Simulate stock reception (by updating quantities at random) */ /* This is done to demonstrate how to update rows within a select loop */ printf ("Updating part quantities."); EXEC SQL WHENEVER SQLERROR GOTO end_of_parts; EXEC SQL WHENEVER SQLWARNING CONTINUE; /* initialize random generator used in this example */ srand (2); /* do select loop */ EXEC SQL OPEN part_cursor; EXEC SQL WHENEVER NOT FOUND GOTO end_of_parts; for (;;) { EXEC SQL FETCH part_cursor into :part_no, :part_name :name_null_indicator, :quantity :quantity_null_indicator; /* update quantities only when they are not null */ if (quantity_null_indicator != -1) { /* update quantity */ /* A typical program would have a more complex logic to calculate the new quantity. For this example, we simply use a random number generator. (Refer to your C compiler's manual for "srand" and "rand" functions). */ /* This "update" statement updates the last part's row that has been read. */ quantity += rand() % 10; EXEC SQL update part set quantity = :quantity where current of part_cursor; } } end_of_parts: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE part_cursor; EXEC SQL commit; printf (" -- done\n"); display_parts_with_variable_select ("list 2 of parts having less than 10 items:", "select part_no, name, quantity from part where quantity < 10"); /*-------------------------------------------------------------------*/ /* goto end of demo */ goto end; /* error messages */ sqlerror_label: printf ("\nerror number %li found\n", SQLCODE); printf ("%s\n", (char *) DBMS_read_error (-SQLCODE, RETURN_ERROR, NULL)); goto end; sqlwarning_label: printf ("\nwarning number %li found\n", SQLCODE); goto end; not_found_label: printf ("\nrow not found"); end: /* disconnect from database */ printf ("Disconnecting database."); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL disconnect database; printf (" -- done\n"); printf ("\nYou can use the ISQL ABC command at the SHELL prompt\n"); printf ("to examine and update the ABC database.\n"); printf ("\n*********************************END OF DEMO*********************************\n"); return (0); } void display_customers_by_id () { /* Display customer table sorted by customer id. */ printf ("\n"); printf ("customers list, sorted by customer id.\n"); printf ("--------------------------------------\n\n"); printf ("id: name: address:\n"); printf ("---------- -------------------- --------------------\n"); EXEC SQL WHENEVER SQLERROR GOTO end_of_customers; EXEC SQL WHENEVER SQLWARNING CONTINUE; EXEC SQL WHENEVER NOT FOUND GOTO end_of_customers; EXEC SQL OPEN customer_id_list_cursor; for (;;) { EXEC SQL FETCH customer_id_list_cursor into :customer_id, :customer_name, :customer_address :address_null_indicator; /* print id */ printf ("%-10.10s", customer_id); /* print name */ printf (" %-20.20s", customer_name); /* print address */ if (address_null_indicator == -1) printf (" n/a\n"); else printf (" %-20.20s\n", customer_address); } end_of_customers: printf ("----------------------------------------------------\n"); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE customer_id_list_cursor; printf ("Hit RETURN to continue.\n"); getchar (); printf ("\n"); } void display_customers_by_name () { /* Display customer table, sorted by name. */ printf ("\n"); printf ("customers list, sorted by name\n"); printf ("------------------------------\n\n"); printf ("name: id: address:\n"); printf ("-------------------- ---------- --------------------\n"); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL WHENEVER SQLWARNING CONTINUE; EXEC SQL WHENEVER NOT FOUND CONTINUE; EXEC SQL OPEN customer_name_list_cursor; for (;;) { EXEC SQL FETCH customer_name_list_cursor into :customer_name, :customer_id, :customer_address :address_null_indicator; if ( (SQLCODE < 0) /* test error */ || (SQLCODE == SQLEND) /* test end of query */ ) break; /* print name */ printf ( "%-20.20s", customer_name); /* print id */ printf (" %-10.10s", customer_id); /* print address */ if (address_null_indicator == -1) printf (" n/a\n"); else printf (" %-20.20s\n", customer_address); } printf ("----------------------------------------------------\n"); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE customer_name_list_cursor; printf ("Hit RETURN to continue.\n"); getchar (); printf ("\n"); } void display_parts () { /* Display part table */ /* When value is null, indicate that value is not available. */ EXEC SQL WHENEVER SQLERROR GOTO end_of_parts; EXEC SQL WHENEVER SQLWARNING CONTINUE; printf ("\n"); printf ("part list, sorted by number\n"); printf ("---------------------------\n\n"); printf ("number: name: quantity:\n"); printf ("------- -------------------- ---------\n"); EXEC SQL OPEN part_list_cursor; EXEC SQL WHENEVER NOT FOUND GOTO end_of_parts; for (;;) { EXEC SQL FETCH part_list_cursor into :part_no, :part_name :name_null_indicator, :quantity :quantity_null_indicator; printf ("%6.6hi", part_no); if (name_null_indicator == -1) printf (" n/a"); else printf (" %-20.20s", part_name); if (quantity_null_indicator == -1) printf (" n/a"); else printf (" %5d", quantity); printf ("\n"); } end_of_parts: printf ("--------------------------------------\n"); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE part_list_cursor; printf ("Hit RETURN to continue.\n"); getchar (); printf ("\n"); } #ifdef NO_PROTO void display_parts_with_variable_select (title, sql_statement) char *title; char *sql_statement; #else void display_parts_with_variable_select (char *title, char *sql_statement) #endif { /* Displays parts, using a variable sql statement. This is an example of a fixed column list dynamic select, which means that the columns selected are known at compile time, but the sql statement itself is known only at execution time. For a variable list dynamic select, see program "dyn_sql.sqc". */ int i; /* function parameter can't be in a "sql declare section", so we create a copy */ EXEC SQL BEGIN DECLARE SECTION; char *sql_statement2 = sql_statement; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE dynamic_select_part_statement STATEMENT; EXEC SQL DECLARE dynamic_select_part_cursor CURSOR FOR dynamic_select_part_statement; EXEC SQL WHENEVER SQLERROR GOTO end_of_parts; EXEC SQL WHENEVER SQLWARNING CONTINUE; printf ("\n"); printf ("%s\n", title); for (i = strlen (title); i; i--) printf ("-"); printf ("\n\n"); printf ("number: name: quantity:\n"); printf ("------- -------------------- ---------\n"); EXEC SQL PREPARE dynamic_select_part_statement FROM :sql_statement2; EXEC SQL OPEN dynamic_select_part_cursor; EXEC SQL WHENEVER NOT FOUND GOTO end_of_parts; for (;;) { EXEC SQL FETCH dynamic_select_part_cursor into :part_no, :part_name :name_null_indicator, :quantity :quantity_null_indicator; printf ("%6.6hi", part_no); if (name_null_indicator == -1) printf (" n/a"); else printf (" %-20.20s", part_name); if (quantity_null_indicator == -1) printf (" n/a"); else printf (" %5d", quantity); printf ("\n"); } end_of_parts: printf ("--------------------------------------\n"); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE dynamic_select_part_cursor; printf ("Hit RETURN to continue.\n"); getchar (); } 
Just Logic Technologies Inc.
P.O. Box 63050
40 Commerce St.,
Nun's Island, Qc, H3E 1V6
Canadaemail: 71563.3370@CompuServe.com
Last modification: 4/96