Embedded SQL with Pro*C
by Sai Kiran Gummaraj12/07/2006
In any application software, accessing a database system is almost inevitable. Some programming languages, such as Java and C++ with custom libraries, do a very good job of providing good programmer interfaces to access the database. What if you want to access a database using C or COBOL or FORTRAN? This is usually the case in firms that use legacy applications--commonly large banks. These firms phase out systems at a much slower pace than do companies in other industries, due to the systems being extremely critical: they perform electronic funds tracking. It's difficult to introduce new technologies while rolling out the older technology just to satisfy the thirst of technology evangelists. Such systems usually rely on old ways of accessing databases, including low-level database libraries such as OCI in case of Oracle, Sybase Open Client Connect Libraries, and Pro*C. It takes plenty of planning to move to newer generation systems; it takes years of parallel and phased development in order to phase out older implementations.
Database access mechanisms such as Pro*C will stay relevant for some time to come. Pro*C is a very programmer- and reviewer-friendly way of writing code to access a database. The pre-compilation stage turns Pro*C code into low-level structures and function calls. This paradigm provides quite a few advantages. First, it shields the programmer from the underlying API calls used to access the database. Second, the actual SQL statements and the host programming language code really mix very well to produce very readable and clear code. It's not perfect, but I appreciate it.
Embedded SQL is a superset of Sybase's T-SQL or Oracle's PL/SQL that lets you place SQL statements in application programs written in languages such as C and COBOL. Pro*C allows the C programmer to write database access code fast and with less of a learning curve. For people who are familiar with both C and SQL, this is a cakewalk. Its worth noting that there are differences between implementations of Pro*C across different database vendors due to the differences between database architectures, datatypes, etc. Each new release of a database may announce certain enhancements or changes to its Embedded SQL pre-compiler. It is best to track changes on this front by referring to the vendor database websites.
This article discusses the ways and means of writing Pro*C code to access and modify a database system. My test databases are mainly Sybase and Oracle, as these are in wide use in the same environments as Pro*C. Finally, it's worth noting that some people refer to Pro*C as Embedded SQL.
Ready? What does it take to connect to a Sybase database using conventional C API calls (such as using Sybase OpenClient Connect Libraries)?
Note: This code, dbCon.c, is the sample Sybase programming code available from Sybase.
/************************************************
* *
* Filename : dbCon.c *
* *
* This is function demonstrates the series of *
* steps it involves in opening a connection *
* to the Sybase database. *
* *
* NOTE: Not all variables used are declared; *
* Such as context, connection etc. as this *
* is just a demo snippet. *
* *
***********************************************/
void dbConnect ()
{
CS_INT rc;
CS_INT *outlen;
CS_INT buf_len;
CS_INT msglimit;
CS_INT netdriver;
/*-----------------------------------------*
* Allocate a connection to the server *
*-----------------------------------------*/
rc = ct_con_alloc (context, &connection);
if (rc != CS_SUCCEED)
{
strncpy (msgstr, "CT_CONALLOC failed", \
msg_size);
no_errors_sw = FALSE ;
error_out (rc);
}
/*-----------------------------------------*
* Alter properties of the *
* connection for user-id *
*-----------------------------------------*/
buf_len = user_size;
rc = ct_con_props (connection, (long)CS_SET,\
(long)CS_USERNAME, username, buf_len,\
outlen);
if (rc != CS_SUCCEED)
{
strncpy (msgstr, "CT_CON_PROPS for \
user-id failed",msg_size);
no_errors_sw = FALSE ;
error_out (rc);
}
/*-----------------------------------------*
* Alter properties of the *
* connection for password *
*-----------------------------------------*/
buf_len = pwd_size;
rc = ct_con_props (connection, (long)CS_SET,\
(long)CS_PASSWORD, pwd, buf_len, outlen);
if (rc != CS_SUCCEED)
{
strncpy (msgstr, "CT_CON_PROPS for \
password failed", msg_size);
no_errors_sw = FALSE ;
error_out (rc);
}
/*-----------------------------------------*
* Alter properties of the *
* connection for transaction *
*-----------------------------------------*/
buf_len = tran_size;
rc = ct_con_props (connection, (long)CS_SET,\
(long)CS_TRANSACTION_NAME, tran,\
buf_len, outlen);
if (rc != CS_SUCCEED)
{
strncpy (msgstr, "CT_CON_PROPS for \
transaction failed", msg_size);
no_errors_sw = FALSE ;
error_out (rc);
}
/*-----------------------------------------*
* Alter properties of the connection *
* for network driver *
*-----------------------------------------*/
/*-----------------------------------------*
* default value for non-recognized *
* driver name *
*-----------------------------------------*/
netdriver = 9999;
/*-----------------------------------------*
* if no netdriver entered, *
* default is LU62 *
*-----------------------------------------*/
if (strncmp(driver," ",9) == 0 ?? \
strncmp(driver,"LU62",4) == 0)
netdriver = CS_LU62;
else if (strncmp(driver,"INTERLINK",8) == 0)
netdriver = CS_INTERLINK;
else if (strncmp(driver,"IBMTCPIP",8) == 0)
netdriver = CS_TCPIP;
else if (strncmp(driver,"CPIC",4) == 0)
netdriver = CS_NCPIC;
rc = ct_con_props (connection, (long)CS_SET,\
(long)CS_NET_DRIVER, (long)netdriver,\
CS_UNUSED, outlen);
if (rc != CS_SUCCEED)
{
strncpy (msgstr, "CT_CON_PROPS for \
network driver failed",msg_size);
no_errors_sw = FALSE ;
error_out (rc);
}
/*-----------------------------------------*
* Setup retrieval of All Messages *
*-----------------------------------------*/
rc = ct_diag (connection, CS_INIT, \
CS_UNUSED, CS_UNUSED, CS_NULL);
if (rc != CS_SUCCEED)
{
strncpy (msgstr, "CT_DIAG CS_INIT \
failed", msg_size);
no_errors_sw = FALSE ;
error_out (rc);
}
/*-----------------------------------------*
* Set the upper limit of number *
* of messages *
*-----------------------------------------*/
msglimit = 5 ;
rc = ct_diag (connection, CS_MSGLIMIT, \
CS_ALLMSG_TYPE, CS_UNUSED, &msglimit);
if (rc != CS_SUCCEED)
{
strncpy (msgstr, "CT_DIAG CS_MSGLIMIT \
failed", msg_size);
no_errors_sw = FALSE ;
error_out (rc);
}
/*-----------------------------------------*
* Open connection to the server *
* or CICS region *
*-----------------------------------------*/
rc = ct_connect (connection, servname, \
server_size);
if (rc != CS_SUCCEED)
{
strncpy (msgstr, "CT_CONNECT failed",\
msg_size);
no_errors_sw = FALSE ;
error_out (rc);
}
}This code invokes quite a few APIs and performs plenty of error checking. How much simpler Pro*C makes this! Note that Pro*C source files have the extensions .PC or .pc. The Pro*C pre-compiler will produce the actual .c or .cpp files.
