/* SQL class demonstration program --------- ------------- ------- Creates ABC Auto Part Company demo database. */ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <sqlca.h> #include <sqlda.h> #include <sqlcpp.h> long 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]; char sql_statement_text[150]; char * dummy; FILE *file; int record_qty, record_no; void check_error (SQL &status); void display_parts (void); void display_parts_with_variable_select (char *title, char *sql_statement); void display_customers_by_id (void); void display_customers_by_name (void); int main () { SQL sql, commit, update_current; 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"); /* obtain return code in case of error */ /* We will use the "check_error" function as an example to demonstrate error checking. We could have obtained the same result by setting the error action to be PRINT_ERROR and let the database system check for errors and print messages, instead of using "check_error". */ sql.set_error_action (RETURN_ERROR); /*-------------------------------------------------------------------*/ printf ("Dropping \"abc\" database."); sql = "drop database abc;" sql.execute (); sql.purge(); printf (" -- done\n"); printf ("Creating database.\n"); printf ("If this is your first database, it may take a few seconds."); sql = "create database abc;" check_error (sql); sql.execute (); check_error (sql); sql.purge(); printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* open database */ printf ("Opening \"abc\" database."); sql = "connect database abc;" check_error (sql); sql.execute (); check_error (sql); sql.purge(); printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* Construct a "commit" sql statement once. */ commit = "commit;" check_error (commit); /*-------------------------------------------------------------------*/ /* create tables */ /* create "part" table */ printf ("Creating \"part\" table."); sql = "create table part (part_no smallint not null, name asciz (21), quantity integer);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute ();; check_error (commit); printf (" -- done\n"); /* create "customer" table */ printf ("Creating \"customer\" table."); sql = "create table customer (customer_id asciz (11) not null, name asciz (21) not null, address asciz (21));" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); /* create "shipment" table */ printf ("Creating \"shipment\" table."); sql = "create table shipment (shipment_no integer not null, customer_id asciz (11) not null, date asciz (9));" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); /* create "shipment item" table */ printf ("Creating \"shipment_item\" table."); sql = "create table shipment_item (shipment_no integer not null, part_no smallint not null, quantity integer);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); printf (" -- done\n"); /* count number of tables created */ sql = "select count(*) from rdbms_table where database_name = 'abc' and owner = 'user';" check_error (sql); sql.open_cursor(); check_error (sql); sql.fetch_next (&table_count); check_error (sql); sql.close_cursor(); check_error (sql); sql.purge(); printf ("%li user tables in database.\n", table_count); /*-------------------------------------------------------------------*/ /* create indexes on tables */ printf ("Creating index on \"part\"."); sql = "create unique index part_ix on part (part_no);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); printf ("Creating index on \"customer\"."); sql = "create unique index customer_ix on customer (customer_id);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); printf ("Creating indexes on \"shipment\"."); sql = "create unique index shipment_ix1 on shipment (shipment_no);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); sql = "create index shipment_ix2 on shipment (customer_id);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); printf ("Creating indexes on \"shipment_item\"."); sql = "create unique index shipment_item_ix1 on shipment_item (shipment_no, part_no);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); sql = "create index shipment_item_ix2 on shipment_item (part_no);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* insert parts in 3 different ways */ /* 1 - create some parts with predefined values */ printf ("Inserting parts with predefined values."); sql = "insert into part (part_no, name, quantity) values (1010, 'starter', 52);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); sql = "insert into part (part_no, name, quantity) values (1021, 'alternator', 95);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (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"); sql = "disconnect database;" sql.execute (); 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 %[^|] */ sql = "insert into part (part_no, name, quantity) values (?d, ?s, ?d);" check_error (sql); 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 */ sql.execute (3, &part_no, part_name, &quantity); check_error (sql); } sql.purge(); commit.execute (); check_error (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\"."); sql = "insert into part (part_no, name, quantity) from file 'part.da2' (integer, character, integer);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* create customers */ printf ("Inserting customers from \"customer.dat\"."); sql = "insert into customer (customer_id, name, address) from file 'customer.dat' (character, character, character);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* create shipments */ printf ("Inserting shipments from \"shipment.dat\"."); sql = "insert into shipment (shipment_no, customer_id, date) from file 'shipment.dat' (integer, character, character);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); /*-------------------------------------------------------------------*/ /* create shipment items */ printf ("Inserting shipment items from \"ship_it.dat\"."); sql = "insert into shipment_item (shipment_no, part_no, quantity) from file 'ship_it.dat' (integer, integer, integer);" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); /*-------------------------------------------------------------------*/ display_customers_by_id (); display_customers_by_name (); display_parts (); /*-------------------------------------------------------------------*/ /* 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."); sql = "update customer set address = '104 Bellevue Dr.' where customer_id = 'GOO1072 ';" check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); 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"); sql = "update customer set address = ?s where customer_id = ?s;" check_error (sql); sql.execute (2, customer_address, customer_id); check_error (sql); sql.purge(); commit.execute (); check_error (commit); 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';"); sql = sql_statement_text; check_error (sql); sql.execute (); check_error (sql); sql.purge(); commit.execute (); check_error (commit); 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;"); sql = sql_statement_text; check_error (sql); strcpy (customer_address, "915 Lawrence St."); strcpy (customer_id, "TJX0983S"); sql.execute (2, customer_address, customer_id); check_error (sql); printf (" -- done\n"); /* Let's also update Alan Teng's address with the same prepared sql_update_statement */ /* This time, we put the constant strings directly in the "execute" statement */ printf ("Updating Alan Teng's address."); sql.execute (2, "303 Eastwood Road", "TEN0289C"); check_error (sql); sql.purge(); printf (" -- done\n"); /* commit changes */ commit.execute (); check_error (commit); printf ("Changes commited.\n"); display_customers_by_id (); /*-------------------------------------------------------------------*/ /* Update a part quantity by using internal variables. */ /* Reduce spark plugs stock by 50. */ printf ("Reducing spark plugs stock."); quantity = -50; part_no = 9104; sql = "update part set quantity = quantity + ?d where part_no = ?d;" check_error (sql); sql.execute (2, &quantity, &part_no); check_error (sql); sql.purge(); commit.execute (); check_error (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 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."); /* initialize random generator used in this example */ srand (2); /* do select loop */ sql = "select part_no, name, quantity from part;" check_error (sql); sql.open_cursor(); check_error (sql); update_current = "update part set quantity = ?d where current of ?d;" check_error (sql); for (;;) { sql.fetch_next(&part_no, part_name, &quantity); if(sql.status() == SQLEND) break; check_error (sql); /* update quantities only when they are not null */ if (!sql.isnull(2)) { /* 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; update_current.execute (2, &quantity, sql.cursor()); } } end_of_parts: sql.close_cursor(); sql.purge(); update_current.purge(); commit.execute (); check_error (commit); printf (" -- done\n"); display_parts_with_variable_select ("list of parts having less than 10 items:", "select part_no, name, quantity from part where quantity < 10"); /*-------------------------------------------------------------------*/ /* disconnect from database */ printf ("Disconnecting database."); sql = "disconnect database;" check_error (sql); sql.execute (); check_error (sql); sql.purge(); printf (" -- done\n"); printf ("\nYou can use the ISQL ABC command at the prompt\n"); printf ("to examine and update the ABC database.\n"); printf ("\n*********************************END OF DEMO*********************************\n"); return (0); } void check_error (SQL &sql) { /* error messages */ if(sql.status() == SQLEND) { printf ("\nrow not found"); return; }; if(sql.status() < 0) { printf ("\nerror number %i found\n", sql.status()); printf ("%s\n", sql.return_error ()); exit (1); } if(sql.status() > 0) { printf ("\nwarning number %i found\n", sql.status()); }; return; } void display_customers_by_id (void) { /* Display customer table sorted by customer id. */ SQL customers; printf ("\n"); printf ("customers list, sorted by customer id.\n"); printf ("--------------------------------------\n\n"); printf ("id: name: address:\n"); printf ("---------- -------------------- --------------------\n"); customers= "select customer_id, name, address from customer order by customer_id;" check_error (customers); customers.open_cursor(0); check_error (customers); for (;;) { customers.fetch_next(customer_id, customer_name, customer_address); if (customers.status() == SQLEND) break; check_error (customers); /* print id */ printf ("%-10.10s", customer_id); /* print name */ printf (" %-20.20s", customer_name); /* print address */ if (customers.isnull(2)) printf (" n/a\n"); else printf (" %-20.20s\n", customer_address); } printf ("----------------------------------------------------\n"); customers.close_cursor(); check_error (customers); printf ("Hit RETURN to continue.\n"); fgetc(stdin); printf ("\n"); } void display_customers_by_name (void) { /* Display customer table, sorted by name. */ SQL customers; printf ("\n"); printf ("customers list, sorted by name\n"); printf ("------------------------------\n\n"); printf ("name: id: address:\n"); printf ("-------------------- ---------- --------------------\n"); customers = "select name, customer_id, address from customer order by name" check_error (customers); customers.open_cursor(); check_error (customers); for (;;) { customers.fetch_next (customer_name, customer_id, customer_address); if (customers.status() == SQLEND) break; check_error (customers); /* print name */ printf ( "%-20.20s", customer_name); /* print id */ printf (" %-10.10s", customer_id); /* print address */ if (customers.isnull(2)) printf (" n/a\n"); else printf (" %-20.20s\n", customer_address); } printf ("----------------------------------------------------\n"); customers.close_cursor(); check_error (customers); printf ("Hit RETURN to continue.\n"); fgetc(stdin); printf ("\n"); } void display_parts (void) { /* Display part table */ /* When value is null, indicate that value is not available. */ SQL parts; printf ("\n"); printf ("part list, sorted by number\n"); printf ("---------------------------\n\n"); printf ("number: name: quantity:\n"); printf ("------- -------------------- ---------\n"); parts = "select part_no, name, quantity from part order by part_no;" check_error (parts); parts.open_cursor(); check_error (parts); for (;;) { parts.fetch_next (&part_no, part_name, &quantity); if (parts.status() == SQLEND) break; check_error (parts); printf ("%6.6hi", part_no); if (parts.isnull(1)) printf (" n/a"); else printf (" %-20.20s", part_name); if (parts.isnull(2)) printf (" n/a"); else printf (" %5d", quantity); printf ("\n"); } printf ("--------------------------------------\n"); parts.close_cursor(); check_error (parts); printf ("Hit RETURN to continue.\n"); fgetc(stdin); printf ("\n"); } void display_parts_with_variable_select (char *title, char *sql_statement) { /* 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". */ SQL parts; int i; printf ("\n"); printf ("%s\n", title); for (i = strlen (title); i; i--) printf ("-"); printf ("\n\n"); printf ("number: name: quantity:\n"); printf ("------- -------------------- ---------\n"); parts = sql_statement; check_error (parts); parts.open_cursor(); check_error (parts); for (;;) { parts.fetch_next(&part_no, part_name, &quantity); if (parts.status() == SQLEND) break; check_error (parts); printf ("%6.6hi", part_no); if (parts.isnull(1)) printf (" n/a"); else printf (" %-20.20s", part_name); if (parts.isnull(2)) printf (" n/a"); else printf (" %5d", quantity); printf ("\n"); } printf ("--------------------------------------\n"); parts.close_cursor(); check_error (parts); printf ("Hit RETURN to continue.\n"); fgetc(stdin); printf ("\n"); }
email: 71563.3370@CompuServe.com
Last modification: 4/96