Application programs written in C can interact with the Sybase database server using a number of different styles.
Embedded SQL (E-SQL) is the simplest way to interact with Sybase. You simply add SQL queries (prefaced by the keywords `` exec sql'') into your program. The program is then run through a pre-compiler which converts this ``shorthand'' to C code. Sybooks provides a complete introduction to E-SQL in the Open Client/Server collection, Open Client Embedded SQL/C Programmer's Guide.
A second way to interact with Sybase is through the Open Client DB-Library (DBlib); we refer to this style as ``Library SQL.'' DBlib uses a sequence of special function calls to set up and process each query. Sybooks information on this method can be found in the Open Client/Server collection, Open Client DB-Library/C Reference Manual. Additional information on the DBlib function calls used here can be found at Sweet Hall in the Sybase manual ``Open Client DB-Library Reference Manual.'' Although the manual references the previous release of Sybase, we know of no changes, and we strongly suggest that you look at this documentation. As with any program, the syntax is very specific and must be followed exactly to get the expected results.
A third way to interact with Sybase is through the use of Stored Procedures. Stored procedures are stored as special objects in the database. Stored procedures in Sybase are similar to functions in C: they can accept arguments, do some computation, and return values. All stored procedures are pre-compiled and thus execute faster than standard interactive or embedded queries (which are first compiled and then run). Therefore, they are often used to make SQL code more modular and faster. You will learn how to declare and invoke Sybase stored procedures using interactive sqsh and client-server DBlib. Information on stored procedures can be found in Sybooks, the Sybase SQL Server collection, Transact-SQL User's Guide, Chapter 14.
Currently the Sybase programming environment is set up only on the Sun workstations (elaine's, adelbert's, and epic's). You will need to use one of these machines to compile and run programs in any of the three styles.
E-SQL allows you to write SQL statements inside the code of another language (called the host language) as if the SQL statements were part of the code. Each embedded SQL statement within the host language must begin with ``exec sql''. This style of interaction is discussed in general terms in the course reader and was covered in class.
Use ``exec sql begin declare section'' and ``exec sql end declare section'' statements to declare shared variables. All variables declared between the two statements will be accessible by both the host language and SQL, and hence can be used to communicate between the host language and SQL. You will need to put a colon in front of a shared variable when using it from SQL.
Before you can execute any embedded SQL statements, you must establish a connection to the database server. First, use ``exec sql include sqlca'' to enable your program to receive status information from the SQL server. The ca stands for communication area, and is used in communication from the SQL server to the application. This communication area is also the place through which the return code is passed to the application. Then, open a connection with ``exec sql connect''. In addition, ``exec sql disconnect'' is used to disconnect from the SQL server, while ``exec sql whenever'' is used for error handling.
After the connection has been established, your program can now issue SQL commands to the server. The easiest way to fetch the results from SQL commands is via cursors. A cursor will go through the result returned by an SQL statement row by row. The steps for using a cursor are: (1) ``exec sql declare'' to declare a cursor and associate it with an SQL statement; (2) ``exec sql open'' to open the cursor; (3) ``exec sql fetch'' to fetch the result into variables in your program and move the cursor one row forward; (4) ``exec sql close'' to close the cursor.
Below is a sample program using E-SQL. You can copy all the source files and the make file to your own directory and use them as a starting point for your assignment. No change to your environment variables is necessary. Notice that this sample program must be pre-compiled using cpre (the Sybase E-SQL pre-compiler) before it can be compiled by the C compiler.
As an aside, in the sample code throughout this handout you may wonder about the variable naming convention. It's a version of ``Hungarian Notation''--a method that prefaces the name of each variable with its type(s). For example, you will see a psDbProc variable. ``p'' stands for pointer and ``s'' stands for structure. Thus, psDbProc is a pointer to a structure. Similarly, ``i'' stands for integer and ``sz'' stands for string. While this convention may seem odd at first, it is growing in popularity due to the ability to deduce from a variable's name whether, for example, it is a pointer to a structure or the structure itself.
/* Sample source file for the Embedded SQL example for CS145, Fall 96-97.
**
** This file can be found in /usr/class/cs145/code/embedded/embedded_sql.cp.
** It shows how to use Embedded SQL to connect to the server, execute
** a SQL statement "SELECT * FROM PATIENT ORDER BY Name", fetch the
** result, and disconnect from the server.
*/
#include <stdio.h>
#include "sybsqlex.h"
#define SSN_SIZE 11
#define NAME_SIZE 20
#define BIRTHDATE_SIZE 20
void get_patient_info();
void error_handler();
void warning_handler();
/* establish a "communication area" */
EXEC SQL INCLUDE SQLCA;
void main()
{
/* declared variables to be shared by both C and SQL */
EXEC SQL BEGIN DECLARE SECTION;
char szSybaseUserName[20];
char szSybasePassword[20];
EXEC SQL END DECLARE SECTION;
/* install error handling routines */
EXEC SQL WHENEVER SQLERROR CALL error_handler();
EXEC SQL WHENEVER SQLWARNING CALL warning_handler();
EXEC SQL WHENEVER NOT FOUND CONTINUE;
strcpy(szSybaseUserName, "mary");
strcpy(szSybasePassword, "MYPW");
/* connect to Sybase server */
EXEC SQL CONNECT :szSybaseUserName IDENTIFIED BY :szSybasePassword;
get_patient_info();
/* disconnect from Sybase server */
EXEC SQL DISCONNECT DEFAULT;
}
void get_patient_info() {
/* declared variables to be shared by both C and SQL */
EXEC SQL BEGIN DECLARE SECTION;
char szSSN[SSN_SIZE+1];
char szName[NAME_SIZE+1];
char szBirthdate[BIRTHDATE_SIZE+1];
EXEC SQL END DECLARE SECTION;
/* declare and open the cursor */
EXEC SQL DECLARE PatientCursor CURSOR FOR
SELECT * FROM PATIENT ORDER BY Name;
EXEC SQL OPEN PatientCursor;
printf("SSN Name birthdate\n");
printf("----------- -------------------- --------------------\n");
while (1) {
/* fetch the results into local variables, one row at a time */
EXEC SQL FETCH PatientCursor INTO :szSSN, :szName, :szBirthdate;
if (sqlca.sqlcode == 100) break;
printf("%s %s %s\n", szSSN, szName, szBirthdate);
}
/* close the cursor */
EXEC SQL CLOSE PatientCursor;
}
/* the routine for handling error messages */
void error_handler()
{
fprintf(stderr, "\n** SQLCODE=(%d)", sqlca.sqlcode);
if (sqlca.sqlerrm.sqlerrml) {
fprintf(stderr, "\n** SQL Server Error ");
fprintf(stderr, "\n** %s", sqlca.sqlerrm.sqlerrmc);
}
fprintf(stderr, "\n\n");
exit(0);
}
/* the routine for handling warning messages */
void warning_handler()
{
if (sqlca.sqlwarn[1] == 'W') fprintf(stderr, "\n** Data truncated.\n");
if (sqlca.sqlwarn[3] == 'W') {
fprintf(stderr,
"\n** Insufficient host variables to store results.\n");
}
}
The second way to interact with the Sybase server is through of DBlib. As with E-SQL, you need to establish a connection to the SQL server first. This is done in the initialization section of your program by using the following calls in this order:
dblogin(); // allocate LOGINREC structure
dbopen(); // create a DBPROCESS structure for communication by
// using the LOGINREC created with a dblogin() call.
dberrhandler(); // install the user-defined error handler for
// Sybase errors
dbmsghandler(); // install the user-defined message handler for
// Sybase messages
When your program has finished its interaction with the server, it
must disconnect using the command:
dbexit(); // disconnect from Sybase server
The DBlib function calls allow you to invoke an SQL command from within a program. The query is sent to the database server and the results are returned to the program one row at a time. dbcmd() is used to set up the query, dbsqlexec() is called once, and then the results are retrieved using dbresults(), dbbind(), and dbnextrow(). To retrieve the results, an outer loop makes calls to dbresults(), which returns batches of row results. The attributes returned by the query are bound to program variables using the command dbbind(). The rows in a batch are processed by calling dbnextrow(). When all batches have been processed, a call to dbretstatus() will return the status of the server after your query. You will then want to call dbcancel(), which cancels execution of the current command batch and flushes any pending results. You may call dbcancel() at the beginning of your queries to ensure that no results are pending in the buffer if you like.
Sybase itself provides a number of examples of DBlib code in the $SYBASE/sample/dblibrary directory:
example1.c Send queries, bind and print the results example2.c Insert data into a new table. example3.c Bind aggregate and compute results. example4.c Row buffering example8.c Making an RPC call -- read the comments carefully example9.c Text and image routines (for TEXT datatype) example10.c Inserting an image example11.c Retrieving an imageIn addition to these Sybase-provided examples, the sample code below provides a framework for invoking queries as required in your assignment. Much of what you need to know can be found within the sample code. You will find the following C functions:
main();
get_nurse_info();
get_nurse_info_sp();
err_handler();
msg_handler();
The code of function get_nurse_info_sp() is shown in the next
section instead of here, as it is primarily illustrating how to
declare and invoke stored procedures.
Here is the sample code using DBlib calls. You may copy all the source files and the make file to your own directory and make them there.
/* Sample source file for the Library SQL example for CS145, Fall 96-97.
**
** This file can be found in /usr/class/cs145/code/library/library_sql.c.
** It shows how to use DBlib calls to connect to the server, execute a SQL
** statement "SELECT EmpID, SSN FROM NURSE WHERE Name = 'Gina'" (in
** get_nurse_info()), declare and invoke a stored procedure to execute the
** same SQL command (in get_nurse_info_sp()), and disconnect from the server.
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* These three header files are required for DBlib */
#include <sybfront.h>
#include <sybdb.h>
#include "sybdbex.h"
#define SSN_SIZE 11
void get_nurse_info();
void get_nurse_info_sp();
void panic();
void main()
{
DBPROCESS *psDbProc;
LOGINREC *psLogin;
char szSybaseUserName[] = "mary";
char szSybasePassword[] = "MYPW";
/* initialize DB Library */
if (dbinit() == FAIL) panic("Can't initialize DB_Library");
/* install error handling routines */
dberrhandle(err_handler);
dbmsghandle(msg_handler);
/* get a LOGINREC */
if ((psLogin = dblogin()) == NULL) panic("Can't get a login record");
if ((DBSETLUSER(psLogin, szSybaseUserName) == FAIL) ||
(DBSETLPWD(psLogin, szSybasePassword) == FAIL)) {
panic("Can't set user or password");
}
/* get a DBPROCESS for communication with Sybase server */
if ((psDbProc = dbopen(psLogin, NULL)) == NULL) {
panic("Can't open a Sybase connection");
}
get_nurse_info(psDbProc);
get_nurse_info_sp(psDbProc, "Gina");
/* disconnect from Sybase server */
dbexit();
}
/* this function shows how to execute a SQL statement from whtin C */
void get_nurse_info(DBPROCESS *psDbProc)
{
int iRetStatus;
DBINT iRetCode;
int iEmpID;
char szSSN[SSN_SIZE+1];
/* send the SQL command to Sybase server. The following query could
be sent within a single dbcmd() call. Notice the space before
the second call; the server concatenates all the strings it
receives before a dbsqlexec() call. */
if (dbcmd(psDbProc, "SELECT EmpID, SSN") == FAIL) {
panic("dbcmd() fails");
}
if (dbcmd(psDbProc, " FROM NURSE WHERE Name = 'Gina'") == FAIL) {
panic("dbcmd() fails");
}
/* tell Sybase server to begin executing the current SQL statement */
if (dbsqlexec(psDbProc) == FAIL) panic("dbsqlexec() fails");
printf("\n");
printf("EmpID szSSN\n");
printf("---------- -----------\n");
/* the outer while-loop iterates once for each result returned by
Sybase server (in this example, only 1 result will be returned). */
while ((iRetStatus = dbresults(psDbProc)) != NO_MORE_RESULTS) {
if (iRetStatus == FAIL) panic("dbresults() fails");
/* tell DBlib where to fetch the results into */
if (dbbind(psDbProc, 1, INTBIND, (DBINT) 0, (BYTE *) &iEmpID) ==
FAIL) panic("dbbind() fails");
if (dbbind(psDbProc, 2, NTBSTRINGBIND, (DBINT) 0,
(BYTE *) szSSN) == FAIL) panic("dbbind() fails");
/* the inner while-loop iterates once for each row returned.
Results are fetched into the variables bound by dbbind(). */
while ((iRetStatus = dbnextrow(psDbProc)) != NO_MORE_ROWS) {
if (iRetStatus == FAIL) panic("dbnextrow() fails");
printf("%10i %s\n", iEmpID, szSSN);
}
}
/* clear any pending results */
if (dbcancel(psDbProc) == FAIL) panic("dbcancel() fails");
return;
}
/* the routine for handling error messages */
int err_handler(DBPROCESS *psDbProc, int severity, int dberr, int oserr,
char *dberrstr, char *oserrstr)
{
if ((psDbProc == NULL) || (DBDEAD(psDbProc))) return INT_EXIT;
fprintf(stderr, "DB-Library error:\n\t%s\n", dberrstr);
if (oserr != DBNOERR) {
fprintf(stderr, "Operating-system error:\n\t%s\n", oserrstr);
}
return INT_CANCEL;
}
/* the routine for handling warning messages */
int msg_handler(DBPROCESS *psDbProc, DBINT msgno, int msgstate, int severity,
char *msgtext, char *srvname, char *procname, int line)
{
fprintf(stderr, "Msg %ld, Level %d, State %d\n", msgno, severity,
msgstate);
if (strlen(srvname) > 0) fprintf(stderr, "Server '%s', ", srvname);
if (strlen(procname) > 0) fprintf(stderr, "Procedure '%s', ", procname);
if (line > 0) fprintf (stderr, "Line %d", line);
fprintf(stderr, "\n\t%s\n", msgtext);
return 0;
}
void panic(char *msg)
{
fprintf(stderr, "%s\n", msg);
dbexit();
exit(0);
}
Stored procedures allow you to store frequently executed operations as pre-compiled query ``objects'' in the database. Remember sp_password? It's actually a system-defined stored procedure that operates on system relations. User-defined stored procedures can be defined and invoked interactively, or by programs using the Sybase DBlib functions. Parameters to stored procedures must be supplied in either case.
The sample code below shows how to create a stored procedure called GetNurseProc that has one input parameter and a user-defined output parameter called return_code. Note that the sample is a script that is run interactively using sqsh--it is not code embedded in a program. You can use buf-load to load it into the sqsh command line. The input parameter is a character string that is the name of the nurse. The user-defined return_code is designed to detect data entry errors. For example, we might pass a numeric string as the name. If our stored procedure is designed to detect this, it could set the parameter return_code to some constant that we know means we have an invalid input name.
/* Sample SQL code for stored procedure declaration for CS145, Fall 96-97.
**
** This file can be found in /usr/class/cs145/code/sp/get_nurse_proc.sql
** It shows how to declare a stored procedure in Sybase interactive mode.
** All user-defined variables are prefaced by '@', while all system-defined
** variables are prefaced by '@@'.
*/
CREATE PROC GetNurseProc(@return_code INT OUTPUT, @nurse_name CHAR(20))
AS
/* assign 1 to return_code; doesn't generate any results that can be
fetched by DBlib; return_code=1 indicates more than zero rows returned */
SELECT @return_code = 1
/* this SELECT statement generates results that can be fetched by DBlib */
SELECT EmpID, SSN FROM NURSE WHERE Name = @nurse_name
IF @@rowcount > 0
SELECT @return_code = 0
RETURN 0
Now suppose you want to invoke this stored procedure interactively. You must pass values for all parameters (including output parameters) to the Sybase server, e.g., as follows:
1> EXEC GetNurseProc NULL, "Gina"This command invokes the GetNurseProc stored procedure with the NULL value for the first parameter and ``Gina'' for the second parameter. The stored procedure will run and return to sqsh. Since there is a select statement in this stored procedure, you can see on the screen that the results returned by this stored procedure look the same as those returned by the select statement with the variables set appropriately. The difference is that unlike select statements, you can never use the results returned by stored procedures as subqueries. Nevertheless, you can still use DBLib calls to fetch and process them.
Stored procedures are called from a C program by executing the following steps in the following order:
dbrpcinit(); // initialize the stored procedure
dbrpcparam(); // called once for each of the procedure parameters
dbrpcsend(); // signal the server to execute the stored procedure
dbsqlok(); // wait for results from the server
dbresults(); // fill the Sybase query buffer with a batch of rows
dbbind(); // used to bind query row results to program variables
dbnextrow(); // called repeatedly until there are no more rows in
// the current batch
Note that this method of retrieving results for stored procedures is
the same as that for DBlib queries, although the setup is quite
different.
Here is sample C code for declaring and invoking stored procedures. This code is actually part of library_sql.c from the previous section. This function first removes the stored procedure with name GetNurseProc if any, declares a new one (with the same contents as the above example), and makes DBlib calls to invoke it. In your assignment, certainly you don't need to declare a stored procedure each time you want to invoke it. Once you create a stored procedure, it is there until you delete it.
/* this function shows how to declare and invoke a Sybase stored procedure
from within C */
void get_nurse_info_sp(DBPROCESS *psDbProc, char *szName)
{
int iRetStatus;
DBINT iRetCode;
int iEmpID;
char szSSN[SSN_SIZE+1];
char szSQL[256];
/* drop any stored procedure with name "GetNurseProc" */
strcpy(szSQL, "IF EXISTS (SELECT * FROM sysobjects ");
strcat(szSQL, "WHERE name = 'GetNurseProc' AND type = 'P') ");
strcat(szSQL, "BEGIN DROP PROC GetNurseProc END ");
if (dbcmd(psDbProc, szSQL) == FAIL) panic("dbcmd() fails");
if (dbsqlexec(psDbProc) == FAIL) panic("dbsqlexec() fails");
while ((iRetStatus = dbresults(psDbProc)) != NO_MORE_RESULTS) {
if (iRetStatus == FAIL) panic("dbresults() fails");
}
/* declare stored procedure "GetNurseProc" */
strcpy(szSQL, "CREATE PROC GetNurseProc(@return_code INT OUTPUT, ");
strcat(szSQL, "@nurse_name CHAR(20)) AS ");
strcat(szSQL, "SELECT @return_code = 1 ");
strcat(szSQL, "SELECT EmpID, SSN FROM NURSE ");
strcat(szSQL, "WHERE Name = @nurse_name ");
strcat(szSQL, "IF @@rowcount > 0 SELECT @return_code = 0 ");
strcat(szSQL, "RETURN 0");
if (dbcmd(psDbProc, szSQL) == FAIL) panic("dbcmd() fails");
if (dbsqlexec(psDbProc) == FAIL) panic("dbsqlexec() fails");
while ((iRetStatus = dbresults(psDbProc)) != NO_MORE_RESULTS) {
if (iRetStatus == FAIL) panic("dbresults() fails");
}
/* initialize the stored procedure call */
if (dbrpcinit(psDbProc, "GetNurseProc", 0) == FAIL) {
panic("Can't initialize RPC");
}
/* add parameters to this stored procedure call */
if (dbrpcparam(psDbProc, "@return_code", DBRPCRETURN, SYBINT4, -1, -1,
(BYTE *) &iRetCode) == FAIL) panic("dbrpcparam() fails");
if (dbrpcparam(psDbProc, "@nurse_name", 0, SYBCHAR, -1, strlen(szName),
(BYTE *) szName) == FAIL) panic("dbrpcparam() fails");
/* tell Sybase server to begin executing this stored procedure */
if (dbrpcsend(psDbProc) == FAIL) panic("dbrpcsend() fails");
/* wait for results from Sybase server */
if (dbsqlok(psDbProc) == FAIL) panic("dbsqlok() fails");
printf("\n");
printf("EmpID szSSN\n");
printf("---------- -----------\n");
/* process results (similar to processing results of a SQL statement) */
while ((iRetStatus = dbresults(psDbProc)) != NO_MORE_RESULTS) {
if (iRetStatus == FAIL) panic("dbresults() fails");
dbbind(psDbProc, 1, INTBIND, (DBINT) 0, (BYTE *) &iEmpID);
dbbind(psDbProc, 2, NTBSTRINGBIND, (DBINT) 0, (BYTE *) szSSN);
while ((iRetStatus = dbnextrow(psDbProc)) != NO_MORE_ROWS) {
if (iRetStatus == FAIL) panic("dbnextrow() fails");
printf("%10i %s\n", iEmpID, szSSN);
}
}
/* clear any pending results */
if (dbcancel(psDbProc) == FAIL) panic("dbcancel() fails");
return;
}