Embedded SQL with Pro*C
Pages: 1, 2, 3
Exception Handling
Handle errors by examining the error codes and reasons from the sqlca structure, or use the WHENEVER clause:
EXEC SQL WHENEVER <CONDITION> <ACTION>CONDITION can be:
SQLERROR, indicating that an error occurred while executing the previous SQL command.SQLWARNING, indicating that a warning occurred a result of executing the previous SQL command.NOT FOUND, indicating that the database found no data as a result of the previous SQL command.
ACTION can be:
STOP, which willexit()the program, rolling back all uncommitted transactions.CONTINUE, which will try to continue executing the program despite the error.- The
DO <function>, wherefunction, an error-handling function, which will call that function. GOTO <label>, which will transfer control to the labelled code.
Transactions
Database transactions allow you to treat a set of SQL statements as a single unit. Changes made as part of these SQL statements either get committed to the database permanently or rolled back (undone), always maintaining data integrity. Pro*C allows you to define, begin, and end transactions just like in SQL or procedural SQL. Note that, unless you wrap your SQL statements in transactions, each SQL statement occurs in an individual transaction. This code snippet illustrates the use of transaction statements in Pro*C:
EXEC SQL BEGIN TRANSACTION
cust_transaction_insert;
EXEC SQL AT "Cust_Db"
INSERT INTO Customer
VALUES (:cust_id, :cust_name,
:cust_address, :cust_discount);
if (ptr_sqlca -> sqlcode < 0)
{
printf ("Your Transaction Will be \
rolled back.\n");
printf ("Error Code: %d\n", \
ptr_sqlca->sqlerrm.sqlerrml);
printf ("Error Description: %s\n", \
ptr_sqlca->sqlerrm->sqlerrmc);
EXEC SQL ROLLBACK TRANSACTION
cust_transaction_insert;
}
/*-----------------------------------------*
* This marks a Save point in your *
* transaction. At any time ahead in your *
* program if there is a roll back, you *
* have an option of rolling back *
* completely or to this point. *
*-----------------------------------------*/
EXEC SQL SAVEPOINT start_update;
/* ... Some Program Logic goes here ... */
EXEC SQL AT "Cust_Db"
UPDATE Customer SET Customer_Discount
= :cust_discount
WHERE Customer_Id = :cust_id;
if (ptr_sqlca -> sqlcode < 0)
{
printf ("Unable to update discount
rates for the customer. %d\n",
cust_id);
printf ("Error Code: %d\n",
ptr_sqlca->sqlerrm.sqlerrml);
printf ("Error Description: %s\n",
ptr_sqlca->sqlerrm->sqlerrmc);
/*-------------------------------------*
* Your transaction will be rolled back*
* to the most recent save point *
*-------------------------------------*/
EXEC SQL ROLLBACK TRANSACTION
start_update;
/*-------------------------------------*
* If you had just said *
* EXEC SQL ROLLBACK TRANSACTION *
* The entire transaction would have *
* been rolled back. Not just *
* upto the save point. *
*-------------------------------------*/
}
EXEC SQL COMMIT cust_transaction_insert;Cursors
When a query returns multiple rows, you can process each row of data by using either "Cursors" or "Host Arrays." The Cursor refers to the current row in the set of rows returned by the query. This allows your program to process the data one row at a time.
Operating on a cursor involves using the statements:
DECLARE CURSORto associate the name of a cursor to a query.OPENto execute the query and to identify the set of rows returned by the query as an "Active Set" of the cursor.FETCHto operate on individual rows by fetching the next row from the set.CLOSEto disable the cursor and close the active set.
An example will help:
EXEC SQL BEGIN DECLARE SECTION;
char customer_name[256];
long cust_id;
char customer_address[512];
char customer_email[128];
float customer_discount;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE cust_cursor CURSOR FOR
SELECT cust_id, cust_name, cust_address,
cust_email, cust_discount
FROM customer
WHERE cust_name = :customer_name;
EXEC SQL OPEN cust_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;
while (1)
{
EXEC SQL FETCH cust_cursor INTO
:customer_id, :customer_name,
:customer_address,
:customer_email, :customer_discout;
/*-------------------------------------*
* Logic to process retrieved *
* values go here; *
*-------------------------------------*/
}
EXEC SQL CLOSE cust_cursor;Note that you cannot use the INTO clause as part of the Cursor's SELECT. Instead, use a FETCH to select columns into host variables.
You also cannot declare a cursor in one source file and try to open/use it in another source file.
Here's a simple example to demonstrate the SELECT and INSERT operations:
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
int connect (char *connection_name,
char *db_server, char *db_instance,
char *uname, char *passwd);
int error_check (char *func_name,
int line_num);
void finish_proc ();
int main (int argc, char *argv[])
{
char dataserver[128];
char database[128];
char username[128];
char password[128];
char connection[128];
int status = 0;
int choice = 0;
SQLCA *p_sqlca;
EXEC SQL BEGIN DECLARE SECTION;
CS_INT emp_id;
CS_CHAR emp_name[50];
CS_CHAR addr[50];
CS_CHAR phno[50];
CS_CHAR mgr_name[50];
short ind_mgr;
float salary;
CS_CHAR join_date[50];
EXEC SQL END DECLARE SECTION;
printf ("Enter the database \
server name: ");
scanf ("%s", dataserver);
printf ("Enter the database \
instance name: ");
scanf ("%s", database);
printf ("Enter the username: ");
scanf ("%s", username);
printf ("Enter the password: ");
scanf ("%s", password);
printf ("Enter a name for your \
connection: ");
scanf ("%s", connection);
status = connect (connection,
dataserver, database, username,
password);
if (status < 0)
{
printf ("Unable to proceed.
Exiting...\n");
exit (1);
}
atexit(finish_proc);
while (1)
{
printf ("\n\n");
printf ("\tEnter 1 for SELECT, 2 for\
INSERT and 0 to QUIT: ");
scanf ("%d", &choice);
switch (choice)
{
case 1:
EXEC SQL DECLARE emp_cursor CURSOR \
FOR SELECT employee_id, \
employee_name, address, phno,\
manager_name, salary, \
joining_date FROM Emp;
EXEC SQL OPEN emp_cursor;
printf("employee_id\temployee_name\t\
address\tphno\\tmanager_name\t\
salary\t\joining_date\n");
printf("-----------\t\-------------\t
-------\t\----\t------------\t------\
\t------------\n");
while (1)
{
EXEC SQL FETCH emp_cursor INTO
:emp_id, :emp_name,:addr, :phno,
:mgr_name:ind_mgr,:salary,
:join_date;
printf ("%d\t%s\t%s\t%s\t%s \
\t%f\t%s\n", emp_id, \
emp_name, addr, phno, \
((ind_mgr == -1) ? \
"NULL" : mgr_name),
salary, join_date);
}
break;
case 2:
printf ("Enter the employee id");
scanf ("%d", &emp_id);
printf ("Enter the employee name:");
scanf ("%s", emp_name);
printf ("Enter the employee \
address: ");
scanf ("%s", addr);
printf ("Enter the employee \
phno: ");
scanf ("%s", phno);
printf ("Enter the employee\
manager name: ");
scanf ("%s", mgr_name);
printf ("Enter the employee \
salary: ");
scanf ("%f", &salary);
printf ("Enter the employee \
joining date (MM/DD/YYYY):");
scanf ("%s", join_date);
EXEC SQL BEGIN
TRANSACTION emp_insert;
EXEC SQL INSERT INTO Emp
VALUES (:emp_id, :emp_name,
:addr, :phno, :mgr_name,
:salary, :join_date);
p_sqlca = &sqlca;
if (p_sqlca -> sqlcode < 0)
{
printf ("Error Code: %d\n",\
p_sqlca -> \
sqlerrm.sqlerrml);
printf ("Error Description:
%s\n",p_sqlca -> \
sqlerrm.sqlerrmc);
printf ("Your transaction will be
rolled back.");
EXEC SQL ROLLBACK
TRANSACTION emp_insert;
}
EXEC SQL COMMIT
TRANSACTION emp_insert;
break;
case 0:
default:
EXEC SQL DISCONNECT DEFAULT;
exit(0);
}
}
}
int connect (char *c_name, char *db_svr,
char *db_inst, char *uname, char *passwd)
{
int status = 0;
EXEC SQL BEGIN DECLARE SECTION;
char *connection_name;
char *db_server;
char *username;
char *password;
char *db_instance;
EXEC SQL END DECLARE SECTION;
connection_name = c_name;
db_server = db_svr;
db_instance = db_inst;
username = uname;
password = passwd;
EXEC SQL CONNECT :username identified
by :password AT :connection_name
using :db_server;
status = error_check("connect",
__LINE__);
if (status < 0) return status;
EXEC SQL AT :connection_name
USE :db_instance;
status = error_check("connect",
__LINE__);
if (status < 0)
return status;
return status;
}
int error_check (char *func_name,
int line_num)
{
SQLCA *p_sqlca;
p_sqlca = &sqlca;
if (p_sqlca -> sqlcode < 0)
{
printf ("Error at function: %s \
at line %d\n", func_name,
line_num);
printf ("\tError Code: %d\n",
p_sqlca -> sqlerrm.sqlerrml);
printf ("\tError Description: %s\n",
p_sqlca -> sqlerrm.sqlerrmc);
return -1;
}
else if (p_sqlca -> sqlcode == 0)
{
printf ("%s operation successful\n",
func_name);
return 0;
}
return 0;
}I wrote and compiled this program using the Sybase 10.0.1 pre-compiler tools on a Sun Solaris machine:
#
# Note: You will need to compile sybesql.c
# and link the sybesql.o with your object file.
# You can find sybesql.c under
# $SYBASE_HOME/include
#
$ cc -g -c -I/opt/sybase_10.0.1/sybembsql/include
-I/opt/sybase_10.0.1/include sybesql.c
$ /opt/sybase_10.0.1/sybembsql/bin/cpre -a -r \
-m -C ANSI_C db_delegate.PC
$ cc -g -c \
-I/opt/sybase_10.0.1/sybembsql/include \
-I/opt/sybase_10.0.1/include db_delegate.c
$ cc -g -mt sybesql.o db_delegate.o \
/opt/sybase_10.0.1/lib/libct.a \
/opt/sybase_10.0.1/lib/libcs.a \
/opt/sybase_10.0.1/lib/libcomn.a \
/opt/sybase_10.0.1/lib/libtcl.a \
/opt/sybase_10.0.1/lib/libintl.a \
/opt/sybase_10.0.1/lib/libtli.a \
-L/opt/sybase_10.0.1/lib \
-L/opt/sybase_10.0.1/sybembsql/lib \
-lsocket -lnsl -ldl -lm -o db_delegateSai Kiran Gummaraj is a software engineer with close to eight years of experience in the software industry.
Return to O'Reilly Databases
Have a question about Pro*C? Ask Sai here.
You must be logged in to the O'Reilly Network to post a talkback.
You must be logged in to the O'Reilly Network to post a talkback.
Showing messages 1 through 3 of 3.
-
Pointers In Pro*C
2008-04-10 23:29:23 Nithin Joy [Reply | View]
I like to know whether we can use character pointers to fetch value from cursor in Pro*C.
