/*
SQL C application program interface demonstration program
---------------------------------------------------------
Creates ABC Auto Part Company demo database.
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlc.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;
#ifndef NO_PROTO
void check_error (sql *sql);
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);
#else
check_error ();
void display_parts ();
void display_parts_with_variable_select ();
display_customers_by_id ();
void display_customers_by_name ();
#endif
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");
/* set environment variables */
#ifndef NO_PROTO
sql = sql_malloc ();
commit = sql_malloc ();
update_current = sql_malloc ();
#else
sql = (char *)sql_malloc ();
commit = (char *)sql_malloc ();
update_current = (char *)sql_malloc ();
#endif
/* 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 (sql, RETURN_ERROR);
/*-------------------------------------------------------------------*/
printf ("Dropping \"abc\" database.");
sql_prepare (sql, "drop database abc;");
sql_execute (sql, 0);
sql_purge (sql);
printf (" -- done\n");
printf ("Creating database.\n");
printf ("If this is your first database, it may take a few seconds.");
sql_prepare (sql, "create database abc;");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
printf (" -- done\n");
/*-------------------------------------------------------------------*/
/* open database */
printf ("Opening \"abc\" database.");
sql_prepare (sql, "connect database abc;");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
printf (" -- done\n");
/*-------------------------------------------------------------------*/
/* Construct a "commit" sql statement once. */
sql_prepare (commit, "commit;");
check_error (sql);
/*-------------------------------------------------------------------*/
/* create tables */
/* create "part" table */
printf ("Creating \"part\" table.");
sql_prepare (sql, "create table part (part_no smallint not null, name asciz (21), quantity integer);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
check_error (commit);
printf (" -- done\n");
/* create "customer" table */
printf ("Creating \"customer\" table.");
sql_prepare (sql, "create table customer (customer_id asciz (11) not null, name asciz (21) not null, address asciz (21));");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
check_error (commit);
printf (" -- done\n");
/* create "shipment" table */
printf ("Creating \"shipment\" table.");
sql_prepare (sql, "create table shipment (shipment_no integer not null, customer_id asciz (11) not null, date asciz (9));");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
check_error (commit);
printf (" -- done\n");
/* create "shipment item" table */
printf ("Creating \"shipment_item\" table.");
sql_prepare (sql, "create table shipment_item (shipment_no integer not null, part_no smallint not null, quantity integer);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
printf (" -- done\n");
/* count number of tables created */
sql_prepare (sql, "select count (*) from rdbms_table where database_name = 'abc' and owner = 'user';");
check_error (sql);
sql_open_cursor(sql, 0);
check_error (sql);
sql_fetch_next (sql, &table_count);
check_error (sql);
sql_close_cursor (sql);
check_error (sql);
sql_purge (sql);
printf ("%i user tables in database.\n", table_count);
/*-------------------------------------------------------------------*/
/* create indexes on tables */
printf ("Creating index on \"part\".");
sql_prepare (sql, "create unique index part_ix on part (part_no);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
check_error (commit);
printf (" -- done\n");
printf ("Creating index on \"customer\".");
sql_prepare (sql, "create unique index customer_ix on customer (customer_id);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
check_error (commit);
printf (" -- done\n");
printf ("Creating indexes on \"shipment\".");
sql_prepare (sql, "create unique index shipment_ix1 on shipment (shipment_no);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
check_error (commit);
sql_prepare (sql, "create index shipment_ix2 on shipment (customer_id);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
check_error (commit);
printf (" -- done\n");
printf ("Creating indexes on \"shipment_item\".");
sql_prepare (sql, "create unique index shipment_item_ix1 on shipment_item (shipment_no, part_no);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_prepare (sql, "create index shipment_item_ix2 on shipment_item (part_no);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
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_prepare (sql, "insert into part (part_no, name, quantity) values (1010, 'starter', 52);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_prepare (sql, "insert into part (part_no, name, quantity) values (1021, 'alternator', 95);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
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_prepare (sql, "disconnect database;");
check_error (sql);
sql_execute (sql, 0);
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_prepare (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 (sql, 3, &part_no, part_name, &quantity);
check_error (sql);
}
sql_purge (sql);
sql_execute (commit, 0);
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_prepare (sql, "insert into part (part_no, name, quantity) from file 'part.da2' (integer, character, integer);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
check_error (commit);
printf (" -- done\n");
/*-------------------------------------------------------------------*/
/* create customers */
printf ("Inserting customers from \"customer.dat\".");
sql_prepare (sql, "insert into customer (customer_id, name, address) from file 'customer.dat' (character, character, character);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
check_error (commit);
printf (" -- done\n");
/*-------------------------------------------------------------------*/
/* create shipments */
printf ("Inserting shipments from \"shipment.dat\".");
sql_prepare (sql, "insert into shipment (shipment_no, customer_id, date) from file 'shipment.dat' (integer, character, character);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
check_error (commit);
printf (" -- done\n");
/*-------------------------------------------------------------------*/
/* create shipment items */
printf ("Inserting shipment items from \"ship_it.dat\".");
sql_prepare (sql, "insert into shipment_item (shipment_no, part_no, quantity) from file 'ship_it.dat' (integer, integer, integer);");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
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_prepare (sql, "update customer set address = '104 Bellevue Dr.' where customer_id = 'GOO1072 ';");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
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_prepare (sql, "update customer set address = ?s where customer_id = ?s;");
check_error (sql);
sql_execute (sql, 2, customer_address, customer_id);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
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_prepare (sql, sql_statement_text);
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
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_prepare (sql, sql_statement_text);
check_error (sql);
strcpy (customer_address, "915 Lawrence St.");
strcpy (customer_id, "TJX0983S");
sql_execute (sql, 2, customer_address, customer_id);
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 (sql, 2, "303 Eastwood Road", "TEN0289C");
check_error (sql);
sql_purge (sql);
printf (" -- done\n");
/* commit changes */
sql_execute (commit, 0);
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_prepare (sql, "update part set quantity = quantity + ?d where part_no = ?d;");
check_error (sql);
sql_execute (sql, 2, &quantity, &part_no);
check_error (sql);
sql_purge (sql);
sql_execute (commit, 0);
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_prepare (sql, "select part_no, name, quantity from part;");
check_error (sql);
sql_open_cursor (sql, 0);
check_error (sql);
sql_prepare (update_current, "update part set quantity = ?d where current of ?d;");
check_error (update_current);
for (;;)
{ sql_fetch_next (sql, &part_no, part_name, &quantity);
if (sql_status (sql) == SQLEND)
break;
check_error (sql);
/* update quantities only when they are not null */
if (!sql_isnull (sql, 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;
sql_execute (update_current, 2, &quantity, sql_cursor(sql));
}
}
end_of_parts:
sql_close_cursor (sql);
sql_purge (sql);
sql_purge (update_current);
sql_execute (commit, 0);
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_prepare (sql, "disconnect database;");
check_error (sql);
sql_execute (sql, 0);
check_error (sql);
printf (" -- done\n");
sql_free (update_current);
sql_free (commit);
sql_free (sql);
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);
}
#ifndef NO_PROTO
void check_error (sql *sql)
#else
check_error (sql)
struct sql *sql;
#endif
{
/* error messages */
if (sql_status (sql) == SQLEND)
{ printf ("\nrow not found");
return;
};
if (sql_status (sql) < 0)
{ printf ("\nerror number %i found\n", sql_status (sql));
printf ("%s\n", sql_return_error (sql));
exit (1);
}
if (sql_status (sql) > 0)
{ printf ("\nwarning number %i found\n", sql_status (sql));
};
return;
}
#ifndef NO_PROTO
void display_customers_by_id (void)
#else
display_customers_by_id ()
#endif
{
/*
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 = sql_malloc ();
sql_prepare (customers, "select customer_id, name, address from customer order by customer_id;");
check_error (customers);
sql_open_cursor (customers, 0);
check_error (customers);
for (;;)
{ sql_fetch_next (customers, customer_id, customer_name, customer_address);
if (sql_status (customers) == SQLEND)
break;
check_error (customers);
/* print id */
printf ("%-10.10s", customer_id);
/* print name */
printf (" %-20.20s", customer_name);
/* print address */
if (sql_isnull(customers,2))
printf (" n/a\n");
else
printf (" %-20.20s\n", customer_address);
}
printf ("----------------------------------------------------\n");
sql_close_cursor (customers);
check_error (customers);
sql_free (customers);
printf ("Hit RETURN to continue.\n");
getchar ();
printf ("\n");
}
void display_customers_by_name ()
{
/*
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 = sql_malloc ();
sql_prepare (customers, "select name, customer_id, address from customer order by name");
check_error (customers);
sql_open_cursor (customers, 0);
check_error (customers);
for (;;)
{ sql_fetch_next (customers, customer_name, customer_id, customer_address);
if (sql_status (customers) == SQLEND)
break;
check_error (customers);
/* print name */
printf ( "%-20.20s", customer_name);
/* print id */
printf (" %-10.10s", customer_id);
/* print address */
if (sql_isnull(customers,2))
printf (" n/a\n");
else
printf (" %-20.20s\n", customer_address);
}
printf ("----------------------------------------------------\n");
sql_close_cursor (customers);
check_error (customers);
sql_free (customers);
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. */
sql *parts;
printf ("\n");
printf ("part list, sorted by number\n");
printf ("---------------------------\n\n");
printf ("number: name: quantity:\n");
printf ("------- -------------------- ---------\n");
parts = sql_malloc ();
sql_prepare (parts, "select part_no, name, quantity from part order by part_no;");
check_error (parts);
sql_open_cursor (parts, 0);
check_error (parts);
for (;;)
{ sql_fetch_next (parts, &part_no, part_name, &quantity);
if (sql_status (parts) == SQLEND)
break;
check_error (parts);
printf ("%6.6hi", part_no);
if (sql_isnull (parts, 1))
printf (" n/a");
else
printf (" %-20.20s", part_name);
if (sql_isnull (parts, 2))
printf (" n/a");
else
printf (" %5d", quantity);
printf ("\n");
}
printf ("--------------------------------------\n");
sql_close_cursor (parts);
check_error (parts);
sql_free (parts);
printf ("Hit RETURN to continue.\n");
getchar ();
printf ("\n");
}
void display_parts_with_variable_select (title,sql_statement)
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_malloc ();
sql_prepare (parts, sql_statement);
check_error (parts);
sql_open_cursor (parts, 0);
check_error (parts);
for (;;)
{ sql_fetch_next (parts, &part_no, part_name, &quantity);
if (sql_status (parts) == SQLEND)
break;
check_error (parts);
printf ("%6.6hi", part_no);
if (sql_isnull (parts, 1))
printf (" n/a");
else
printf (" %-20.20s", part_name);
if (sql_isnull (parts, 2))
printf (" n/a");
else
printf (" %5d", quantity);
printf ("\n");
}
printf ("--------------------------------------\n");
sql_close_cursor (parts);
check_error (parts);
sql_free (parts);
printf ("Hit RETURN to continue.\n");
getchar ();
printf ("\n");
}
email: 71563.3370@CompuServe.com
Last modification: 4/96