SQL Tutorials
SQL for databases and ETL tools tutorials
Abstract
Tutorials to use SQL for databases and ETL tools in FairCom Edge
Tutorial | Description |
|---|---|
The tutorials, in this section, are for C developers who want to use ODBC in their C application. | |
The tutorials, in this section, are for running JDBC in a Java program. |
The tutorials, in this section, are for C developers who want to use ODBC in their C application.
Like all other tutorials in the c-tree series, each example simplifies the concepts of database programming into four simple steps:
Initialize()Define()Manage()Done()
This is known as the Initialize(), Define(), Manage(), and You're Done()! approach.
No matter which FairCom interface language you are using, FairCom follows this same high-level flow in all tutorials. This makes it easy for developers to crossover from one language interface to another as these basic concepts apply to all.
| Every language requires some form of initial |
| Database definitions (DDL), table/file schema definitions, Index definitions, table/file creation, and table/file open operations are all addressed in the |
| This stage of the program is where the database is operated on, as in managing your data. Adding/reading/updating/deleting records/rows are handled in this stage of the program. |
| When the program ends, the database session should be closed. This stage handles the necessities to |
This section provides tutorials that follow the Initialize(), Define(), Manage(), and You're Done()! approach.
To compile and run this tutorial in C go to drivers\sql.odbc\tutorials\ODBCTutorial1.c.
This tutorial will take you through the basic use of the FairCom DB SQL ODBC interface.
The following sections provide the code for the four steps.
Simple single table
This program was designed to be as simple as possible.
Program step summary :
Initialize()Connects to the FairCom Database Engine.
Define()Defines and creates a customer master (
custmast) table/file.Manage()Adds a few rows/records, reads the rows/records back from the database, displays the column/field content, and then deletes the rows/records.
Done()Disconnects from FairCom DB engine
Example 1. Simple
Main() functionIt is suggested you open the source code with your own editor.
/*
* main()
*
* The main() function implements the concept of "init, define, manage
* and you're done..."
*/
int main(int argc, char* argv[])
{
Initialize();
Define();
Manage();
Done();
printf("\nPress <ENTER> key to exit . . .\n");
getchar();
return(0);
}Init
To open a connection to a database, provide the FairCom DB engine with:
a username
a password
the database name
Example 2. Code for
Initialize()/*
* Initialize()
*
* Perform the minimum requirement of logging onto the c-tree Server
*/
void Initialize(void)
{
RETCODE rc;
printf("INIT\n");
/* allocate environment handle */
if ((rc = SQLAllocEnv(&hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLAllocEnv()");
/* allocate connection handle */
if ((rc = SQLAllocConnect(hEnv, &hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLAllocConnect()");
/* connect to server */
printf("\tLogon to server...\n");
if ((rc = SQLConnect(hDbc, MY_DSN, SQL_NTS, "admin", SQL_NTS, "ADMIN", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLConnect()");
/* allocate statement handle */
if ((rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");
}Define
The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.
Example 3. Code for
Define()/*
* Define()
*
* Create the table for containing a list of existing customers
*/
void Define(void)
{
RETCODE rc;
printf("DEFINE\n");
/* create table */
printf("\tCreate table...\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE custmast ( \
cm_custnumb CHAR(4), \
cm_custzipc CHAR(9), \
cm_custstat CHAR(2), \
cm_custrtng CHAR(1), \
cm_custname VARCHAR(47), \
cm_custaddr VARCHAR(47), \
cm_custcity VARCHAR(47))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}Manage
The manage step provides data management functionality for your application and/or process.
Example 4. Code for
Manage()/*
* Manage()
*
* This function performs simple record functions of add, delete and gets
*/
void Manage(void)
{
printf("MANAGE\n");
/* delete any existing records */
Delete_Records();
/* populate the table with data */
Add_Records();
/* display contents of table */
Display_Records();
}
/*
* Delete_Records()
*
* This function deletes all the records in the table
*/
void Delete_Records(void)
{
RETCODE rc;
printf("\tDelete records...\n");
if ((rc = SQLExecDirect(hStmt, "DELETE FROM custmast", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DELETE)");
}
/*
* Add_Records()
*
* This function adds records to a table from an array of strings
*/
void Add_Records(void)
{
RETCODE rc;
SQLINTEGER i;
SQLTCHAR sCommand[512];
SQLTCHAR *data[] = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
SQLINTEGER nRecords = sizeof(data) / sizeof(data[0]);
printf("\tAdd records...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
strcpy (sCommand, "INSERT INTO custmast VALUES ");
strcat (sCommand, data[i]);
if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
}
}
/*
* Display_Records()
*
* This function displays the contents of a table.
*/
void Display_Records(void)
{
RETCODE rc;
SQLTCHAR custnumb[4+1];
SQLTCHAR custname[47+1];
SDWORD cbData;
printf("\tDisplay records...");
rc = SQLExecDirect(hStmt, "SELECT * FROM custmast" ,SQL_NTS);
if (rc != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");
/* fetch and display each individual record */
while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)
{
SQLGetData(hStmt, 1, SQL_C_CHAR, custnumb, sizeof(custnumb), &cbData);
SQLGetData(hStmt, 5, SQL_C_CHAR, custname, sizeof(custname), &cbData);
printf("\n\t\t%-8s%10s\n",custnumb, custname);
}
SQLFreeStmt(hStmt,SQL_CLOSE);
}Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Example 5. Code for
Done()/*
* Done()
*
* This function handles the housekeeping of closing connection and
* freeing of associated memory
*/
void Done(void)
{
RETCODE rc;
printf("DONE\n");
/* free statement handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLFreeHandle(SQL_HANDLE_STMT)");
/* disconnect from server */
printf("\tLogout...\n");
if ((rc = SQLDisconnect(hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLDisconnect()");
/* free connection handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLFreeHandle(SQL_HANDLE_DBC)");
/* free environment handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLFreeHandle(SQL_HANDLE_ENV)");
}To compile and run this tutorial in C go to drivers\sql.odbc\tutorials\ODBCTutorial2.c.
We will now build table/file relationships using the FairCom DB SQL ODBC interface.
This tutorial will advance the concepts introduced in the first tutorial by expanding the number of tables. This tutorial will define key columns/fields and create specific indexes for each table to form a relational model database.
Relational model and indexing
This example will add a bit more complexity, by introducing multiple tables with related indices in order to form a simple relational database simulating an order entry system. Figure 1, “Relational model tables” is an overview of what will be created
Figure 1. Relational model tables
Example 6. Simple
Main() functionIt is suggested to open the source code with your own editor.
/*
* main()
*
* The main() function implements the concept of "init, define, manage
* and you're done..."
*/
int main(int argc, char* argv[])
{
Initialize();
Define();
Manage();
Done();
printf("\nPress <ENTER> key to exit . . .\n");
getchar();
return(0);
}Init
To open a connection to a database, provide the FairCom DB engine with:
a username
a password
the database name
Example 7. Code for
Initialize()/*
* Initialize()
*
* Perform the minimum requirement of logging onto the c-tree Server
*/
void Initialize(void)
{
RETCODE rc;
printf("INIT\n");
/* allocate environment handle */
if ((rc = SQLAllocEnv(&hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLAllocEnv()");
/* allocate connection handle */
if ((rc = SQLAllocConnect(hEnv, &hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLAllocConnect()");
/* connect to server */
printf("\tLogon to server...\n");
if ((rc = SQLConnect(hDbc, MY_DSN, SQL_NTS, "admin", SQL_NTS, "ADMIN", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLConnect()");
/* allocate statement handle */
if ((rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");
}Define
The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.
Example 8. Code for
Define()/*
* Define()
*
* Create the tables
*/
void Define(void)
{
printf("DEFINE\n");
Create_CustomerMaster_Table();
Create_CustomerOrders_Table();
Create_OrderItems_Table();
Create_ItemMaster_Table();
}
/*
* Create_CustomerMaster_Table()
*
* Create the CustomerMaster
*/
void Create_CustomerMaster_Table(void)
{
RETCODE rc;
/* define table CustomerMaster */
printf("\ttable CustomerMaster\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE custmast ( \
cm_custnumb CHAR(4), \
cm_custzipc CHAR(9), \
cm_custstat CHAR(2), \
cm_custrtng CHAR(1), \
cm_custname VARCHAR(47), \
cm_custaddr VARCHAR(47), \
cm_custcity VARCHAR(47))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
if ((rc = SQLExecDirect(hStmt,
"CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
}
/*
* Create_CustomerOrders_Table()
*
* Create the table CustomerOrders
*/
void Create_CustomerOrders_Table(void)
{
RETCODE rc;
/* define table CustomerOrders */
printf("\ttable CustomerOrders\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE custordr ( \
co_ordrdate DATE, \
co_promdate DATE, \
co_ordrnumb CHAR(6), \
co_custnumb CHAR(4))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
if ((rc = SQLExecDirect(hStmt,
"CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
if ((rc = SQLExecDirect(hStmt,
"CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
}
/*
* Create_OrderItems_Table()
*
* Create the table OrderItems
*/
void Create_OrderItems_Table(void)
{
RETCODE rc;
/* define table OrderItems */
printf("\ttable OrderItems\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE ordritem ( \
oi_sequnumb SMALLINT, \
oi_quantity SMALLINT, \
oi_ordrnumb CHAR(6), \
oi_itemnumb CHAR(5))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
if ((rc = SQLExecDirect(hStmt,
"CREATE UNIQUE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
if ((rc = SQLExecDirect(hStmt,
"CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
}
/*
* Create_ItemMaster_Table()
*
* Create the table ItemMaster
*/
void Create_ItemMaster_Table(void)
{
RETCODE rc;
/* define table ItemMaster */
printf("\ttable ItemMaster\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE itemmast ( \
im_itemwght INTEGER, \
im_itempric MONEY, \
im_itemnumb CHAR(5), \
im_itemdesc VARCHAR(47))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
if ((rc = SQLExecDirect(hStmt,
"CREATE UNIQUE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
}Manage
The manage step provides data management functionality for your application and/or process.
Example 9. Code for
Manage()/*
* Manage()
*
* Populates table and perform a simple query
*
*/
void Manage(void)
{
RETCODE rc;
SQLTCHAR custname[47+1];
SQLREAL total;
SDWORD cbData;
printf("MANAGE\n");
/* populate the tables with data */
Add_CustomerMaster_Records();
Add_CustomerOrders_Records();
Add_OrderItems_Records();
Add_ItemMaster_Records();
/* perform a query:
list customer name and total amount per order
name total
@@@@@@@@@@@@@ $xx.xx
for each order in the CustomerOrders table
fetch order number
fetch customer number
fetch name from CustomerMaster table based on customer number
for each order item in OrderItems table
fetch item quantity
fetch item number
fetch item price from ItemMaster table based on item number
next
next
*/
printf("\n\tQuery Results\n");
if ((rc = SQLExecDirect(hStmt,
"SELECT cm_custname, SUM(im_itempric * oi_quantity) "
"FROM custmast, custordr, ordritem, itemmast "
"WHERE co_custnumb = cm_custnumb AND co_ordrnumb = oi_ordrnumb AND oi_itemnumb = im_itemnumb "
"GROUP BY cm_custnumb, cm_custname",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");
/* for each order in the CustomerOrders table */
while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)
{
if ((rc = SQLGetData(hStmt, 1, SQL_C_CHAR, custname, sizeof(custname), &cbData)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLGetData()");
if ((rc = SQLGetData(hStmt, 2, SQL_C_FLOAT, &total, sizeof(total), &cbData)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLGetData()");
/* output data to stdout */
printf("\t\t%-20s %.2f\n", custname, total);
}
SQLFreeStmt(hStmt, SQL_CLOSE);
}
/*
* Add_CustomerMaster_Records()
*
* This function adds records to table CustomerMaster from an
* array of strings
*/
void Add_CustomerMaster_Records(void)
{
RETCODE rc;
SQLINTEGER i;
SQLTCHAR sCommand[512];
SQLTCHAR *data[] = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
SQLINTEGER nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records("custmast");
printf("\tAdd records in table CustomerMaster...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
strcpy (sCommand, "INSERT INTO custmast VALUES ");
strcat (sCommand, data[i]);
if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
}
}
/*
* Add_CustomerOrders_Records()
*
* This function adds records to table CustomerOrders from an
* array of strings
*/
void Add_CustomerOrders_Records(void)
{
RETCODE rc;
SQLINTEGER i;
SQLTCHAR sCommand[512];
SQLTCHAR *data[] = {
"('09/01/2002','09/05/2002','1','1001')",
"('09/02/2002','09/06/2002','2','1002')"
};
SQLINTEGER nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records("custordr");
printf("\tAdd records in table CustomerOrders...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
strcpy (sCommand, "INSERT INTO custordr VALUES ");
strcat (sCommand, data[i]);
if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
}
}
/*
* Add_OrderItems_Records()
*
* This function adds records to table OrderItems from an
* array of strings
*/
void Add_OrderItems_Records(void)
{
RETCODE rc;
SQLINTEGER i;
SQLTCHAR sCommand[512];
SQLTCHAR *data[] = {
"(1,2,'1','1')",
"(2,1,'1','2')",
"(3,1,'1','3')",
"(1,3,'2','3')"
};
SQLINTEGER nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records("ordritem");
printf("\tAdd records in table OrderItems...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
strcpy (sCommand, "INSERT INTO ordritem VALUES ");
strcat (sCommand, data[i]);
if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
}
}
/*
* Add_ItemMaster_Records()
*
* This function adds records to table ItemMaster from an
* array of strings
*/
void Add_ItemMaster_Records(void)
{
RETCODE rc;
SQLINTEGER i;
SQLTCHAR sCommand[512];
SQLTCHAR *data[] = {
"(10,19.95,'1','Hammer')",
"(3, 9.99,'2','Wrench')",
"(4, 16.59,'3','Saw')",
"(1, 3.98,'4','Pliers')"
};
SQLINTEGER nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records("itemmast");
printf("\tAdd records in table ItemMaster...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
strcpy (sCommand, "INSERT INTO itemmast VALUES ");
strcat (sCommand, data[i]);
if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
}
}
/*
* Delete_Records()
*
* This function deletes all the records in a tables
*/
void Delete_Records(SQLTCHAR* table)
{
RETCODE rc;
SQLTCHAR sCommand[512];
printf("\tDelete records...\n");
sprintf (sCommand, "DELETE FROM %s", table);
if ((rc = SQLExecDirect(hStmt, sCommand, SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DELETE)");
}Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Example 10. Code for
Done()/*
* Done()
*
* This function handles the housekeeping of closing connection and
* freeing of associated memory
*/
void Done(void)
{
RETCODE rc;
printf("DONE\n");
/* free statement handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLFreeHandle(SQL_HANDLE_STMT)");
/* disconnect from server */
printf("\tLogout...\n");
if ((rc = SQLDisconnect(hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLDisconnect()");
/* free connection handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLFreeHandle(SQL_HANDLE_DBC)");
/* free environment handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLFreeHandle(SQL_HANDLE_ENV)");
}To compile and run this tutorial in C go to drivers\sql.odbc\tutorials\ODBCTutorial3.c.
These tutorials will explore row/record locks using the FairCom DB SQL ODBC interface.
The functionality for this tutorial focuses on inserting or adding rows/records, then updating a single row/record in the customer master table under locking control. The application will pause after a lock is placed on a row/record. Another instance of this application should then be launched, which will block, waiting on the lock held by the first instance. Pressing the key will enable the first instance to proceed. This will result in removing the lock thereby allowing the second instance to continue execution. Launching two processes provides a visual demonstration of the effects of locking and a basis for experimentation on your own.
Locking
In this example, we demonstrate the enforcement of data integrity by introducing record/row locking.
Example 11. Simple Main() function
It is suggested to open the source code with your own editor.
/*
* main()
*
* The main() function implements the concept of "init, define, manage
* and you're done..."
*/
int main(int argc, char* argv[])
{
Initialize();
Define();
Manage();
Done();
printf("\nPress <ENTER> key to exit . . .\n");
getchar();
return(0);
}Init
To open a connection to a database, provide the FairCom DB engine with:
a username
a password
the database name
Example 12. Code for
Init()/*
* Initialize()
*
* Perform the minimum requirement of logging onto the c-tree Server
*/
void Initialize(void)
{
RETCODE rc;
printf("INIT\n");
/* allocate environment handle */
if ((rc = SQLAllocEnv(&hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLAllocEnv()");
/* allocate connection handle */
if ((rc = SQLAllocConnect(hEnv, &hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLAllocConnect()");
/* connect to server */
printf("\tLogon to server...\n");
if ((rc = SQLConnect(hDbc, MY_DSN, SQL_NTS, "admin", SQL_NTS, "ADMIN", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLConnect()");
/* disable commit after each single SQL statement */
if ((rc = SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLSetConnectAttr(SQL_AUTOCOMMIT_OFF)");
/* allocate statement handle */
if ((rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");
}Define
The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.
Example 13. Code for
Define()/*
* Define()
*
* Create the table for containing a list of existing customers
*/
void Define(void)
{
RETCODE rc;
printf("DEFINE\n");
/* create table */
printf("\tCreate table...\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE custmast ( \
cm_custnumb CHAR(4), \
cm_custzipc CHAR(9), \
cm_custstat CHAR(2), \
cm_custrtng CHAR(1), \
cm_custname VARCHAR(47), \
cm_custaddr VARCHAR(47), \
cm_custcity VARCHAR(47))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
if ((rc = SQLExecDirect(hStmt,
"CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE INDEX)");
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}Manage
The manage step provides data management functionality for your application and/or process.
Example 14. Code for
Manage()/*
* Manage()
*
* This function performs simple record functions of add, delete and gets
*/
void Manage(void)
{
printf("MANAGE\n");
/* delete any existing records */
Delete_Records();
/* populate the table with data */
Add_CustomerMaster_Records();
/* display contents of table */
Display_Records();
/* update a record under locking control */
Update_CustomerMaster_Record();
/* display again after update and effects of lock */
Display_Records();
}
/*
* Delete_Records()
*
* This function deletes all the records in the table
*/
void Delete_Records(void)
{
RETCODE rc;
printf("\tDelete records...\n");
if ((rc = SQLExecDirect(hStmt, "DELETE FROM custmast", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DELETE)");
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
* Add_CustomerMaster_Records()
*
* This function adds records to a table from an array of strings
*/
void Add_CustomerMaster_Records(void)
{
RETCODE rc;
SQLINTEGER i;
SQLTCHAR sCommand[512];
SQLTCHAR *data[] = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
SQLINTEGER nRecords = sizeof(data) / sizeof(data[0]);
printf("\tAdd records...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
strcpy (sCommand, "INSERT INTO custmast VALUES ");
strcat (sCommand, data[i]);
if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
}
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
* Display_Records()
*
* This function displays the contents of a table.
*/
void Display_Records(void)
{
RETCODE rc;
SQLTCHAR custnumb[4+1];
SQLTCHAR custname[47+1];
SDWORD cbData;
printf("\tDisplay records...");
rc = SQLExecDirect(hStmt, "SELECT * FROM custmast" ,SQL_NTS);
if (rc != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");
/* fetch and display each individual record */
while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)
{
SQLGetData(hStmt, 1, SQL_C_CHAR, custnumb, sizeof(custnumb), &cbData);
SQLGetData(hStmt, 5, SQL_C_CHAR, custname, sizeof(custname), &cbData);
printf("\n\t\t%-8s%10s\n", custnumb, custname);
}
SQLFreeStmt(hStmt,SQL_CLOSE);
}
/*
* Update_CustomerMaster_Records()
*
* Update one record under locking control to demonstrate the effects
* of locking
*/
void Update_CustomerMaster_Record(void)
{
RETCODE rc;
printf("\tUpdate record...\n");
rc = SQLExecDirect(hStmt, "UPDATE custmast SET cm_custname = 'KEYON DOOLING' WHERE cm_custnumb = '1003'", SQL_NTS);
if (rc != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(UPDATE)");
printf("\tPress <ENTER> key to unlock\n");
getchar();
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
SQLFreeStmt(hStmt,SQL_CLOSE);
}Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Example 15. Code for
Done()/*
* Done()
*
* This function handles the housekeeping of closing connection and
* freeing of associated memory
*/
void Done(void)
{
RETCODE rc;
printf("DONE\n");
/* free statement handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLFreeHandle(SQL_HANDLE_STMT)");
/* re-enable autocommit */
if ((rc = SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (void *)SQL_AUTOCOMMIT_ON, 0)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLSetConnectAttr(SQL_AUTOCOMMIT_ON)");
/* disconnect from server */
printf("\tLogout...\n");
if ((rc = SQLDisconnect(hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLDisconnect()");
/* free connection handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLFreeHandle(SQL_HANDLE_DBC)");
/* free environment handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLFreeHandle(SQL_HANDLE_ENV)");
}To compile and run this tutorial in C go to drivers\sql.odbc\tutorials\ODBCTutorial4.c.
This tutorial discusses transaction processing as it relates to the FairCom DB SQL ODBC interface.
Transaction processing provides a safe method by which multiple database operations spread across separate tables/files are guaranteed to be atomic. Meaning that, within a transaction, either all of the operations succeed or none of the operations succeed. This "either all or none" atomicity ensures that the integrity of the data in related tables/files is secure.
Transaction processing
This example demonstrates transaction control.
Example 16. Simple
Main() functionIt is suggested to open the source code with your own editor.
/*
* main()
*
* The main() function implements the concept of "init, define, manage
* and you're done..."
*/
int main(int argc, char* argv[])
{
Initialize();
Define();
Manage();
Done();
printf("\nPress <ENTER> key to exit . . .\n");
getchar();
return(0);
}Init
To open a connection to a database, provide the FairCom DB engine with:
a username
a password
the database name
Example 17. Code for
Initialize()/*
* Initialize()
*
* Perform the minimum requirement of logging onto the c-tree Server
*/
void Initialize(void)
{
RETCODE rc;
printf("INIT\n");
/* allocate environment handle */
if ((rc = SQLAllocEnv(&hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLAllocEnv()");
/* allocate connection handle */
if ((rc = SQLAllocConnect(hEnv, &hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLAllocConnect()");
/* connect to server */
printf("\tLogon to server...\n");
if ((rc = SQLConnect(hDbc, MY_DSN, SQL_NTS, "admin", SQL_NTS, "ADMIN", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLConnect()");
/* disable commit after each single SQL statement */
if ((rc = SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLSetConnectAttr(SQL_AUTOCOMMIT_OFF)");
/* allocate statement handle */
if ((rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");
}Define
The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.
Example 18. Code for
Define()/*
* Define()
*
* Create the tables
*/
void Define(void)
{
RETCODE rc;
printf("DEFINE\n");
/* delete tables... */
Delete_Tables();
/* ...and re-create them with constraints */
Create_CustomerMaster_Table();
Create_ItemMaster_Table();
Create_CustomerOrders_Table();
Create_OrderItems_Table();
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
* Create_CustomerMaster_Table()
*
* Create the table CustomerMaster
*/
void Create_CustomerMaster_Table(void)
{
RETCODE rc;
/* define table CustomerMaster */
printf("\ttable CustomerMaster\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE custmast ( \
cm_custnumb CHAR(4) PRIMARY KEY, \
cm_custzipc CHAR(9), \
cm_custstat CHAR(2), \
cm_custrtng CHAR(1), \
cm_custname VARCHAR(47), \
cm_custaddr VARCHAR(47), \
cm_custcity VARCHAR(47))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}
/*
* Create_CustomerOrders_Table()
*
* Create the table CustomerOrders
*/
void Create_CustomerOrders_Table(void)
{
RETCODE rc;
/* define table CustomerOrders */
printf("\ttable CustomerOrders\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE custordr ( \
co_ordrdate DATE, \
co_promdate DATE, \
co_ordrnumb CHAR(6) PRIMARY KEY, \
co_custnumb CHAR(4), \
FOREIGN KEY (co_custnumb) REFERENCES custmast)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}
/*
* Create_OrderItems_Table()
*
* Create the table OrderItems
*/
void Create_OrderItems_Table(void)
{
RETCODE rc;
/* define table OrderItems */
printf("\ttable OrderItems\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE ordritem ( \
oi_sequnumb SMALLINT, \
oi_quantity SMALLINT, \
oi_ordrnumb CHAR(6), \
oi_itemnumb CHAR(5), \
FOREIGN KEY (oi_itemnumb) REFERENCES itemmast, \
FOREIGN KEY (oi_ordrnumb) REFERENCES custordr)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}
/*
* Create_ItemMaster_Table()
*
* Create the table ItemMaster
*/
void Create_ItemMaster_Table(void)
{
RETCODE rc;
/* define table ItemMaster */
printf("\ttable ItemMaster\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE itemmast ( \
im_itemwght INTEGER, \
im_itempric MONEY, \
im_itemnumb CHAR(5) PRIMARY KEY, \
im_itemdesc VARCHAR(47))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}Manage
The manage step provides data management functionality for your application and/or process.
Example 19. Code for
Manage()/*
* Manage()
*
* Populates table and performs a simple query
*
*/
void Manage(void)
{
printf("MANAGE\n");
/* populate the tables with data */
Add_CustomerMaster_Records();
Add_ItemMaster_Records();
Add_Transactions();
/* display the orders and their items */
Display_CustomerOrders();
Display_OrderItems();
}
/*
* Delete_Tables()
*
* This function removes all existing tables
*/
void Delete_Tables(void)
{
RETCODE rc;
if ((rc = SQLExecDirect(hStmt, "DROP TABLE ordritem", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");
if ((rc = SQLExecDirect(hStmt, "DROP TABLE custordr", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");
if ((rc = SQLExecDirect(hStmt, "DROP TABLE custmast", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");
if ((rc = SQLExecDirect(hStmt, "DROP TABLE itemmast", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");
}
/*
* Add_Transactions()
*
* Add an Order and associated Items "as a transaction" to their
* respective tables. A transaction is committed or aborted if the
* customer number on the order is confirmed valid. Likewise each
* item in the order is verified to be a valid item.
*/
typedef struct {
SQLTCHAR *ordrdate, *promdate, *ordrnumb, *custnumb;
} ORDER_DATA;
typedef struct {
SQLTCHAR *ordrnumb;
SQLUSMALLINT sequnumb;
SQLUSMALLINT quantity;
SQLTCHAR *itemnumb;
} ORDERITEM_DATA;
ORDER_DATA orders[] = {
{"09/01/2002", "09/05/2002", "1", "1001"},
{"09/02/2002", "09/06/2002", "2", "9999"}, /* bad customer number */
{"09/22/2002", "09/26/2002", "3", "1003"}
};
ORDERITEM_DATA items[] = {
{"1", 1, 2, "1"},
{"1", 2, 1, "2"},
{"2", 1, 1, "3"},
{"2", 2, 3, "4"},
{"3", 1, 2, "3"},
{"3", 2, 2, "99"} /* bad item number */
};
void Add_Transactions(void)
{
RETCODE rc;
SQLINTEGER i, j = 0;
SQLINTEGER nOrders = sizeof(orders) / sizeof(ORDER_DATA);
SQLINTEGER nItems = sizeof(items) / sizeof(ORDERITEM_DATA);
SQLTCHAR sCommand[512];
printf("\tAdd transaction records... \n");
for (i = 0; i < nOrders; i++)
{
/* add order record */
sprintf(sCommand, "INSERT INTO custordr VALUES ('%s', '%s', '%s', '%s')",
orders[i].ordrdate,
orders[i].promdate,
orders[i].ordrnumb,
orders[i].custnumb);
if ((rc = SQLExecDirect(hStmt, sCommand, SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
/* process order items */
while (!(strcmp(items[j].ordrnumb, orders[i].ordrnumb)))
{
/* add item record */
sprintf(sCommand, "INSERT INTO ordritem VALUES (%d, %d, '%s', '%s')",
items[j].sequnumb,
items[j].quantity,
items[j].ordrnumb,
items[j].itemnumb);
if ((rc = SQLExecDirect(hStmt, sCommand , SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
/* bump to next item */
j++;
/* exit the while loop on last item */
if (j >= nItems)
break;
}
/* commit the transaction */
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
}
/*
* Add_CustomerMaster_Records()
*
* This function adds records to table CustomerMaster from an
* array of strings
*/
void Add_CustomerMaster_Records(void)
{
RETCODE rc;
SQLINTEGER i;
SQLTCHAR sCommand[512];
SQLTCHAR *data[] = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
SQLINTEGER nRecords = sizeof(data) / sizeof(data[0]);
printf("\tAdd records in table CustomerMaster...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
strcpy (sCommand, "INSERT INTO custmast VALUES ");
strcat (sCommand, data[i]);
if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
}
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
* Add_ItemMaster_Records()
*
* This function adds records to table ItemMaster from an
* array of strings
*/
void Add_ItemMaster_Records(void)
{
RETCODE rc;
SQLINTEGER i;
SQLTCHAR sCommand[512];
SQLTCHAR *data[] = {
"(10,19.95,'1','Hammer')",
"(3, 9.99,'2','Wrench')",
"(4, 16.59,'3','Saw')",
"(1, 3.98,'4','Pliers')"
};
SQLINTEGER nRecords = sizeof(data) / sizeof(data[0]);
printf("\tAdd records in table ItemMaster...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
strcpy (sCommand, "INSERT INTO itemmast VALUES ");
strcat (sCommand, data[i]);
if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
}
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
* Display_CustomerOrders()
*
* This function displays the contents of CustomerOrders table
*/
void Display_CustomerOrders(void)
{
RETCODE rc;
SQLTCHAR ordrnumb[6+1], custnumb[4+1];
SDWORD cbData;
printf("\n\tCustomerOrders Table...\n");
rc = SQLExecDirect(hStmt, "SELECT * FROM custordr" ,SQL_NTS);
if (rc != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");
/* fetch and display each individual record */
while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)
{
SQLGetData(hStmt, 3, SQL_C_CHAR, ordrnumb, sizeof(ordrnumb), &cbData);
SQLGetData(hStmt, 4, SQL_C_CHAR, custnumb, sizeof(custnumb), &cbData);
printf("\t %s %s\n", ordrnumb, custnumb);
}
SQLFreeStmt(hStmt,SQL_CLOSE);
}
/*
* Display_OrderItems()
*
* This function displays the contents of OrderItems table
*/
void Display_OrderItems(void)
{
RETCODE rc;
SQLTCHAR ordrnumb[6+1], itemnumb[5+1];
SDWORD cbData;
printf("\n\tOrderItems Table...\n");
rc = SQLExecDirect(hStmt, "SELECT * FROM ordritem" ,SQL_NTS);
if (rc != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");
/* fetch and display each individual record */
while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)
{
SQLGetData(hStmt, 3, SQL_C_CHAR, ordrnumb, sizeof(ordrnumb), &cbData);
SQLGetData(hStmt, 4, SQL_C_CHAR, itemnumb, sizeof(itemnumb), &cbData);
printf("\t %s %s\n", ordrnumb, itemnumb);
}
SQLFreeStmt(hStmt,SQL_CLOSE);
}Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Example 20. Code for
Done()/*
* Done()
*
* This function handles the housekeeping of closing connection and
* freeing of associated memory
*/
void Done(void)
{
RETCODE rc;
printf("DONE\n");
/* re-enable autocommit */
if ((rc = SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (void *)SQL_AUTOCOMMIT_ON, 0)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLSetConnectAttr(SQL_AUTOCOMMIT_ON)");
Delete_Tables();
/* free statement handles */
if ((rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLFreeHandle(SQL_HANDLE_STMT)");
/* disconnect from server */
printf("\tLogout...\n");
if ((rc = SQLDisconnect(hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLDisconnect()");
/* free connection handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLFreeHandle(SQL_HANDLE_DBC)");
/* free environment handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLFreeHandle(SQL_HANDLE_ENV)");
}For .NET and Java stored procedures. see the developers guide FairCom Java and .NET stored procedures, triggers, and user-defined functions.
The FairCom DB engine provides a variety of APIs — for example, low-level and the c-treeDB API for C. The entire c-treeDB API for C is available directly as function calls within the object-oriented c-treeDB API for C#. To learn more about FairCom APIs, see FairCom DB interfaces.
The tutorials, in this section, are for running JDBC in a Java program.
Like all other tutorials in the c-tree series, each example simplifies the concepts of database programming into four simple steps:
Initialize()Define()Manage()Done()
This is known as the Initialize(), Define(), Manage(), and You're Done()! approach.
No matter which FairCom interface language you are using, FairCom follows this same high-level flow in all tutorials. This makes it easy for developers to crossover from one language interface to another as these basic concepts apply to all.
| Every language requires some form of initial |
| Database definitions (DDL), table/file schema definitions, Index definitions, table/file creation, and table/file open operations are all addressed in the |
| This stage of the program is where the database is operated on, as in managing your data. Adding/reading/updating/deleting records/rows are handled in this stage of the program. |
| When the program ends, the database session should be closed. This stage handles the necessities to |
This section provides tutorials that follow the Initialize(), Define(), Manage(), and You're Done()! approach.
To compile and run this tutorial in Java go to drivers\sql.jdbc\tutorials\JDBC_Tutorial1.java.
This tutorial will detail the basic use of the FairCom DB JDBC Interface.
The following sections provide the code for the four steps.
Simple single table
This program was designed to be as simple as possible.
Program step summary :
Initialize()Connects to the FairCom Database Engine.
Define()Defines and creates a customer master (
custmast) table/file.Manage()Adds a few rows/records, reads the rows/records back from the database, displays the column/field content, and then deletes the rows/records.
Done()Disconnects from FairCom DB engine
Example 21. Simple
Main() functionIt is suggested you open the source code with your own editor.
import java.sql.*;
import java.io.*;
public class JDBC_Tutorial1 {
static Connection conn;
static Statement stmt;
//
// main()
//
// The main() function implements the concept of "init, define, manage
// and you're done..."
//
public static void main (String[] args)
throws java.io.IOException
{
Initialize();
Define();
Manage();
Done();
System.out.print("\nPress <ENTER> key to exit . . .");
System.in.read();
System.exit(0);
}Init
To open a connection to a database, provide the FairCom DB engine with:
a username
a password
the database name
Connection string
Beginning with FairCom DB V11.2 and FairCom RTG V2, the connection string is in the following format:
jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[¶m=value]...]
The valid
paramsare:characterEncodingReplace encoding with a valid Java encoding name — for example, US‑ASCII, ISO‑8859-1, UTF‑8, and so forth.
passwordUsersslValues of
basic(no peer certificate authentication) orpeerAuthentication(server certificate authentication).When
peerAuthenticationis requested, the client’s trust store must contain the server’s certificate as shown in the example.
The tutorials use a connection string that is set for the default configuration.
"jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN"
Example 22. TLS/SSL
This example enables basic SSL encryption, but does not provide any authentication.
Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=basic");
System.setProperty("javax.net.ssl.trustStore","TrustStore.key");
System.setProperty("javax.net.ssl.trustStorePassword","mypassword");
Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=peerAuthentication");
Note
For backward compatibility, the older format ("jdbc:ctree:6597@localhost:ctreeSQL", "ADMIN", "ADMIN") is still supported but should be considered deprecated.
Example 23. Code for
Initialize() //
// Initialize()
//
// Perform the minimum requirement of logging onto the c-tree Server
//
private static void Initialize ()
{
System.out.println("INIT");
try
{
// load the driver
Class.forName ("ctree.jdbc.ctreeDriver");
// connect to server
System.out.println("\tLogon to server...");
conn = DriverManager.getConnection ("jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN");
// create a statement handle
stmt = conn.createStatement();
}
catch (SQLException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}Define
The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.
Example 24. Code for
Define() //
// Define()
//
// Create the table for containing a list of existing customers
//
private static void Define ()
{
System.out.println("DEFINE");
try
{
stmt.executeUpdate("DROP TABLE ordritem");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE custordr");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE itemmast");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE custmast");
}
catch (SQLException e)
{
}
try
{
// create table
System.out.println("\tCreate table...");
stmt.executeUpdate("CREATE TABLE custmast (" +
"cm_custnumb CHAR(4), " +
"cm_custzipc CHAR(9), " +
"cm_custstat CHAR(2), " +
"cm_custrtng CHAR(1), " +
"cm_custname VARCHAR(47), " +
"cm_custaddr VARCHAR(47), " +
"cm_custcity VARCHAR(47))"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
Manage
The manage step provides data management functionality for your application and/or process.
Example 25. Code for
Manage() //
// Manage()
//
// This function performs simple record functions of add, delete and gets
//
private static void Manage ()
{
System.out.println("MANAGE");
// delete any existing records
Delete_Records();
// populate the table with data
Add_Records();
// display contents of table
Display_Records();
}
//
// Delete_Records()
//
// This function deletes all the records in the table
//
private static void Delete_Records ()
{
System.out.println("\tDelete records...");
try
{
stmt.executeUpdate("DELETE FROM custmast");
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Add_Records()
//
// This function adds records to a table in the database from an
// array of strings
//
private static void Add_Records ()
{
System.out.println("\tAdd records...");
String data[] = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
try
{
// add one record at time to table
for (int i = 0; i < data.length; i++) {
stmt.executeUpdate("INSERT INTO custmast VALUES " + data[i]);
}
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Display_Records()
//
// This function displays the contents of a table.
//
private static void Display_Records ()
{
System.out.print("\tDisplay records...");
try
{
// execute a query statement
ResultSet rs = stmt.executeQuery ("SELECT * FROM custmast");
// fetch and display each individual record
while (rs.next()) {
System.out.println("\n\t\t" + rs.getString(1) + " " + rs.getString(5));
}
rs.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Example 26. Code for
Done() //
// Done()
//
// This function handles the housekeeping of closing, freeing,
// disconnecting and logging out of the database
//
private static void Done ()
{
System.out.println("DONE");
try
{
stmt.close();
// logout
System.out.println("\tLogout...");
conn.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}To compile and run this tutorial in Java go to sql.jdbc\tutorials\JDBC_Tutorial2.java.
We will now build table/file relationships using the FairCom DB JDBC interface.
This tutorial will advance the concepts introduced in the first tutorial by expanding the number of tables. This tutorial will define key columns/fields and create specific indexes for each table to form a relational model database.
Relational model and indexing
This example will add a bit more complexity, by introducing multiple tables with related indices in order to form a simple relational database simulating an order entry system. Figure 1, “Relational model tables” is an overview of what will be created
Figure 2. Relational model tables
Example 27. Simple
Main() functionIt is suggested to open the source code with your own editor.
import java.sql.*;
import java.io.*;
public class JDBC_Tutorial2 {
static Connection conn;
static Statement stmt;
//
// main()
//
// The main() function implements the concept of "init, define, manage
// and you're done..."
//
public static void main (String[] args)
throws java.io.IOException
{
Initialize();
Define();
Manage();
Done();
System.out.print("\nPress <ENTER> key to exit . . .");
System.in.read();
System.exit(0);
}Init
To open a connection to a database, provide the FairCom DB engine with:
a username
a password
the database name
Example 28. Code for
Initialize() //
// Initialize()
//
// Perform the minimum requirement of logging onto the c-tree Server
//
private static void Initialize ()
{
System.out.println("INIT");
try
{
// load the driver
Class.forName ("ctree.jdbc.ctreeDriver");
// connect to server
System.out.println("\tLogon to server...");
conn = DriverManager.getConnection ("jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN");
// create statement handles
stmt = conn.createStatement();
}
catch (SQLException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}
Define
The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.
Example 29. Code for
Define() //
// Define()
//
// Create the tables
//
private static void Define ()
{
System.out.println("DEFINE");
try
{
stmt.executeUpdate("DROP TABLE ordritem");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE custordr");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE itemmast");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE custmast");
}
catch (SQLException e)
{
}
Create_CustomerMaster_Table();
Create_CustomerOrders_Table();
Create_OrderItems_Table();
Create_ItemMaster_Table();
}
//
// Create_CustomerMaster_Table()
//
// Create the table CustomerMaster
//
private static void Create_CustomerMaster_Table ()
{
// define table CustomerMaster
System.out.println("\ttable CustomerMaster");
try
{
stmt.executeUpdate("CREATE TABLE custmast (" +
"cm_custnumb CHAR(4), " +
"cm_custzipc CHAR(9), " +
"cm_custstat CHAR(2), " +
"cm_custrtng CHAR(1), " +
"cm_custname VARCHAR(47), " +
"cm_custaddr VARCHAR(47), " +
"cm_custcity VARCHAR(47))"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
stmt.executeUpdate("CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)");
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Create_CustomerOrders_Table()
//
// Create the table CustomerOrders
//
private static void Create_CustomerOrders_Table ()
{
// define table CustomerOrders
System.out.println("\ttable CustomerOrders");
try
{
stmt.executeUpdate("CREATE TABLE custordr (" +
"co_ordrdate DATE, " +
"co_promdate DATE, " +
"co_ordrnumb CHAR(6), " +
"co_custnumb CHAR(4))"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
stmt.executeUpdate("CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)");
stmt.executeUpdate("CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)");
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Create_OrderItems_Table()
//
// Create the table OrderItems
//
private static void Create_OrderItems_Table ()
{
// define table OrderItems
System.out.println("\ttable OrderItems");
try
{
stmt.executeUpdate("CREATE TABLE ordritem (" +
"oi_sequnumb SMALLINT, " +
"oi_quantity SMALLINT, " +
"oi_ordrnumb CHAR(6), " +
"oi_itemnumb CHAR(5))"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
stmt.executeUpdate("CREATE UNIQUE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)");
stmt.executeUpdate("CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)");
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Create_ItemMaster_Table()
//
// Create the table ItemMaster
//
private static void Create_ItemMaster_Table ()
{
// define table ItemMaster
System.out.println("\ttable ItemMaster");
try
{
stmt.executeUpdate("CREATE TABLE itemmast (" +
"im_itemwght INTEGER, " +
"im_itempric MONEY, " +
"im_itemnumb CHAR(5), " +
"im_itemdesc VARCHAR(47))"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
stmt.executeUpdate("CREATE UNIQUE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)");
}
catch (SQLException e)
{
Handle_Exception(e);
}
}Manage
The manage step provides data management functionality for your application and/or process.
Example 30. Code for
Manage() //
// Manage()
//
// Populates table and perform a simple query
//
private static void Manage ()
{
System.out.println("MANAGE");
// populate the tables with data
Add_CustomerMaster_Records();
Add_CustomerOrders_Records();
Add_OrderItems_Records();
Add_ItemMaster_Records();
// perform a query:
// list customer name and total amount per order
// name total
// @@@@@@@@@@@@@ $xx.xx
// for each order in the CustomerOrders table
// fetch order number
// fetch customer number
// fetch name from CustomerMaster table based on customer number
// for each order item in OrderItems table
// fetch item quantity
// fetch item number
// fetch item price from ItemMaster table based on item number
// next
// next
System.out.println("\n\tQuery Results");
try
{
ResultSet rs = stmt.executeQuery (
"SELECT cm_custname, SUM(im_itempric * oi_quantity) " +
"FROM custmast, custordr, ordritem, itemmast " +
"WHERE co_custnumb = cm_custnumb AND co_ordrnumb = oi_ordrnumb AND oi_itemnumb = im_itemnumb " +
"GROUP BY co_ordrnumb, cm_custname");
// read resultset
while (rs.next())
{
// fetch customer name
String custname = rs.getString(1);
// fetch item price
float total = rs.getFloat(2);
System.out.println("\t\t" + custname + "\t" + total);
}
rs.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Add_CustomerMaster_Records()
//
// This function adds records to table CustomerMaster from an
// array of strings
//
private static void Add_CustomerMaster_Records ()
{
System.out.println("\tAdd records in table CustomerMaster...");
String data[] = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
Delete_Records("custmast");
try
{
// add one record at time to table
for (int i = 0; i < data.length; i++) {
stmt.executeUpdate("INSERT INTO custmast VALUES " + data[i]);
}
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Add_CustomerOrders_Records()
//
// This function adds records to table CustomerOrders from an
// array of strings
//
private static void Add_CustomerOrders_Records ()
{
System.out.println("\tAdd records in table CustomerOrders...");
String data[] = {
"('09/01/2002','09/05/2002','1','1001')",
"('09/02/2002','09/06/2002','2','1002')"
};
Delete_Records("custordr");
try
{
// add one record at time to table
for (int i = 0; i < data.length; i++) {
stmt.executeUpdate("INSERT INTO custordr VALUES " + data[i]);
}
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Add_OrderItems_Records()
//
// This function adds records to table OrderItems from an
// array of strings
//
private static void Add_OrderItems_Records ()
{
System.out.println("\tAdd records in table OrderItems...");
String data[] = {
"(1,2,'1','1')",
"(2,1,'1','2')",
"(3,1,'1','3')",
"(1,3,'2','3')"
};
Delete_Records("ordritem");
try
{
// add one record at time to table
for (int i = 0; i < data.length; i++) {
stmt.executeUpdate("INSERT INTO ordritem VALUES " + data[i]);
}
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Add_ItemMaster_Records()
//
// This function adds records to table ItemMaster from an
// array of strings
//
private static void Add_ItemMaster_Records ()
{
System.out.println("\tAdd records in table ItemMaster...");
String data[] = {
"(10,19.95,'1','Hammer')",
"(3, 9.99,'2','Wrench')",
"(4, 16.59,'3','Saw')",
"(1, 3.98,'4','Pliers')"
};
Delete_Records("itemmast");
try
{
// add one record at time to table
for (int i = 0; i < data.length; i++) {
stmt.executeUpdate("INSERT INTO itemmast VALUES " + data[i]);
}
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Delete_Records()
//
// This function deletes all the records in a tables
//
private static void Delete_Records (String table)
{
System.out.println("\tDelete records...");
try
{
stmt.executeUpdate("DELETE FROM " + table);
}
catch (SQLException e)
{
Handle_Exception(e);
}
}Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Example 31. Code for
Done() //
// Done()
//
// This function handles the housekeeping of closing, freeing,
// disconnecting and logging out of the database
//
private static void Done ()
{
System.out.println("DONE");
try
{
stmt.close();
// logout
System.out.println("\tLogout...");
conn.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}To compile and run this tutorial in Java go to sql.jdbc\tutorials\JDBC_Tutorial3.java.
These tutorials will explore row/record locks using the FairCom DB SQL ODBC interface.
The functionality for this tutorial focuses on inserting or adding rows/records, then updating a single row/record in the customer master table under locking control. The application will pause after a lock is placed on a row/record. Another instance of this application should then be launched, which will block, waiting on the lock held by the first instance. Pressing the key will enable the first instance to proceed. This will result in removing the lock thereby allowing the second instance to continue execution. Launching two processes provides a visual demonstration of the effects of locking and a basis for experimentation on your own.
Locking
In this example, we demonstrate the enforcement of data integrity by introducing record/row locking.
Example 32. Simple Main() function
It is suggested to open the source code with your own editor.
import java.sql.*;
import java.io.*;
public class JDBC_Tutorial3 {
static Connection conn;
static Statement stmt;
//
// main()
//
// The main() function implements the concept of "init, define, manage
// and you're done..."
//
public static void main (String[] args)
throws java.io.IOException
{
Initialize();
Define();
Manage();
Done();
System.out.print("\nPress <ENTER> key to exit . . .");
System.in.read();
System.exit(0);
}Init
To open a connection to a database, provide the FairCom DB engine with:
a username
a password
the database name
Example 33. Code for
Init() //
// Initialize()
//
// Perform the minimum requirement of logging onto the c-tree Server
//
private static void Initialize ()
{
System.out.println("INIT");
try
{
// load the driver
Class.forName ("ctree.jdbc.ctreeDriver");
// connect to server
System.out.println("\tLogon to server...");
conn = DriverManager.getConnection ("jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN");
// disable commit after each single SQL statement
conn.setAutoCommit(false);
// create a statement handle
stmt = conn.createStatement();
}
catch (SQLException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}Define
The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.
Example 34. Code for
Define() //
// Define()
//
// Create the table for containing a list of existing customers
//
private static void Define ()
{
System.out.println("DEFINE");
try
{
stmt.executeUpdate("DROP TABLE ordritem");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE custordr");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE itemmast");
}
catch (SQLException e)
{
}
try
{
stmt.executeUpdate("DROP TABLE custmast");
}
catch (SQLException e)
{
}
try
{
// create table
System.out.println("\tCreate table...");
stmt.executeUpdate("CREATE TABLE custmast (" +
"cm_custnumb CHAR(4), " +
"cm_custzipc CHAR(9), " +
"cm_custstat CHAR(2), " +
"cm_custrtng CHAR(1), " +
"cm_custname VARCHAR(47), " +
"cm_custaddr VARCHAR(47), " +
"cm_custcity VARCHAR(47))"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
stmt.executeUpdate("CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)");
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}Manage
The manage step provides data management functionality for your application and/or process.
Example 35. Code for
Manage() //
// Manage()
//
// This function performs record adds and updates using locking
//
private static void Manage ()
{
System.out.println("MANAGE");
// delete any existing records
Delete_Records();
// populate the table with data
Add_CustomerMaster_Records();
// display contents of table
Display_Records();
// update a record under locking control
Update_CustomerMaster_Record();
// display again after update and effects of lock
Display_Records();
}
//
// Delete_Records()
//
// This function deletes all the records in the table
//
private static void Delete_Records ()
{
System.out.println("\tDelete records...");
try
{
stmt.executeUpdate("DELETE FROM custmast");
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Add_CustomerMaster_Records()
//
// This function adds records to table CustomerMaster from an
// array of strings
//
private static void Add_CustomerMaster_Records ()
{
System.out.println("\tAdd records...");
String data[] = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
try
{
// add one record at time to table
for (int i = 0; i < data.length; i++) {
stmt.executeUpdate("INSERT INTO custmast VALUES " + data[i]);
}
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Display_Records()
//
// This function displays the contents of a table.
//
private static void Display_Records ()
{
System.out.print("\tDisplay records...");
try
{
// execute a query statement
ResultSet rs = stmt.executeQuery ("SELECT * FROM custmast");
// fetch and display each individual record
while (rs.next()) {
System.out.println("\n\t\t" + rs.getString(1) + " " + rs.getString(5));
}
rs.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Update_CustomerMaster_Records()
//
// Update one record under locking control to demonstrate the effects
// of locking
//
private static void Update_CustomerMaster_Record()
{
System.out.println("\tUpdate record...");
try
{
stmt.executeUpdate("UPDATE custmast SET cm_custname = 'KEYON DOOLING' where cm_custnumb = '1003'");
System.out.println("\tPress <ENTER> key to unlock");
System.in.read(new byte[256]);
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Example 36. Code for
Done() //
// Done()
//
// This function handles the housekeeping of closing, freeing,
// disconnecting and logging out of the database
//
private static void Done ()
{
System.out.println("DONE");
try
{
stmt.close();
// logout
System.out.println("\tLogout...");
conn.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}To compile and run this tutorial in Java go to sql.jdbc\tutorials\JDBC_Tutorial4.java.
This tutorial discusses transaction processing as it relates to the FairCom DB JDBC interface.
Transaction processing provides a safe method by which multiple database operations spread across separate tables/files are guaranteed to be atomic. Meaning that, within a transaction, either all of the operations succeed or none of the operations succeed. This "either all or none" atomicity ensures that the integrity of the data in related tables/files is secure.
Transaction processing
This example demonstrates transaction control.
Example 37. Simple
Main() functionIt is suggested to open the source code with your own editor.
import java.sql.*;
import java.io.*;
public class JDBC_Tutorial4 {
static Connection conn;
static Statement stmt;
//
// main()
//
// The main() function implements the concept of "init, define, manage
// and you're done..."
//
public static void main (String[] args)
throws java.io.IOException
{
Initialize();
Define();
Manage();
Done();
System.out.print("\nPress <ENTER> key to exit . . .");
System.in.read();
System.exit(0);
}Init
To open a connection to a database, provide the FairCom DB engine with:
a username
a password
the database name
Example 38. Code for
Initialize() //
// Initialize()
//
// Perform the minimum requirement of logging onto the c-tree Server
//
private static void Initialize ()
{
System.out.println("INIT");
try
{
// load the driver
Class.forName ("ctree.jdbc.ctreeDriver");
// connect to server
System.out.println("\tLogon to server...");
conn = DriverManager.getConnection ("jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN");
// disable commit after each single SQL statement
conn.setAutoCommit(false);
// create statement handles
stmt = conn.createStatement();
}
catch (SQLException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}Define
The define step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.
Example 39. Code for
Define() //
// Define()
//
// Create the tables
//
private static void Define ()
{
System.out.println("DEFINE");
// delete tables ...
Delete_Tables();
// ...and re-create them with constraints
Create_CustomerMaster_Table();
Create_ItemMaster_Table();
Create_CustomerOrders_Table();
Create_OrderItems_Table();
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Delete_Tables()
//
// This function removes all existing tables
//
private static void Delete_Tables ()
{
try
{
stmt.executeUpdate("DROP TABLE ordritem");
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
stmt.executeUpdate("DROP TABLE custordr");
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
stmt.executeUpdate("DROP TABLE custmast");
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
stmt.executeUpdate("DROP TABLE itemmast");
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Create_CustomerMaster_Table()
//
// Create the table CustomerMaster
//
private static void Create_CustomerMaster_Table ()
{
// define table CustomerMaster
System.out.println("\ttable CustomerMaster");
try
{
stmt.executeUpdate("CREATE TABLE custmast (" +
"cm_custnumb CHAR(4) PRIMARY KEY, " +
"cm_custzipc CHAR(9), " +
"cm_custstat CHAR(2), " +
"cm_custrtng CHAR(1), " +
"cm_custname VARCHAR(47), " +
"cm_custaddr VARCHAR(47), " +
"cm_custcity VARCHAR(47))"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Create_CustomerOrders_Table()
//
// Create the table CustomerOrders
//
private static void Create_CustomerOrders_Table ()
{
// define table CustomerOrders
System.out.println("\ttable CustomerOrders");
try
{
stmt.executeUpdate("CREATE TABLE custordr (" +
"co_ordrdate DATE, " +
"co_promdate DATE, " +
"co_ordrnumb CHAR(6) PRIMARY KEY, " +
"co_custnumb CHAR(4), " +
"FOREIGN KEY (co_custnumb) REFERENCES custmast)"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Create_OrderItems_Table()
//
// Create the table OrderItems
//
private static void Create_OrderItems_Table ()
{
// define table OrderItems
System.out.println("\ttable OrderItems");
try
{
stmt.executeUpdate("CREATE TABLE ordritem (" +
"oi_sequnumb SMALLINT, " +
"oi_quantity SMALLINT, " +
"oi_ordrnumb CHAR(6), " +
"oi_itemnumb CHAR(5), " +
"FOREIGN KEY (oi_itemnumb) REFERENCES itemmast, " +
"FOREIGN KEY (oi_ordrnumb) REFERENCES custordr)"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Create_ItemMaster_Table()
//
// Create the table ItemMaster
//
private static void Create_ItemMaster_Table ()
{
// define table ItemMaster
System.out.println("\ttable ItemMaster");
try
{
stmt.executeUpdate("CREATE TABLE itemmast (" +
"im_itemwght INTEGER, " +
"im_itempric MONEY, " +
"im_itemnumb CHAR(5) PRIMARY KEY, " +
"im_itemdesc VARCHAR(47))"
);
}
catch (SQLException e)
{
Handle_Exception(e);
}
}Manage
The manage step provides data management functionality for your application and/or process.
Example 40. Code for
Manage() //
// Manage()
//
// Populates table and perform a simple query
//
private static void Manage ()
{
System.out.println("MANAGE");
// populate the tables with data
Add_CustomerMaster_Records();
Add_ItemMaster_Records();
Add_Transactions();
// display the orders and their items
Display_CustomerOrders();
Display_OrderItems();
}
//
// Add_CustomerMaster_Records()
//
// This function adds records to table CustomerMaster from an
// array of strings
//
private static void Add_CustomerMaster_Records ()
{
String data[] = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
System.out.println("\tAdd records in table CustomerMaster...");
try
{
// add one record at time to table
for (int i = 0; i < data.length; i++) {
stmt.executeUpdate("INSERT INTO custmast VALUES " + data[i]);
}
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Add_ItemMaster_Records()
//
// This function adds records to table ItemMaster from an
// array of strings
//
private static void Add_ItemMaster_Records ()
{
String data[] = {
"(10,19.95,'1','Hammer')",
"(3, 9.99,'2','Wrench')",
"(4, 16.59,'3','Saw')",
"(1, 3.98,'4','Pliers')"
};
System.out.println("\tAdd records in table ItemMaster...");
try
{
// add one record at time to table
for (int i = 0; i < data.length; i++) {
stmt.executeUpdate("INSERT INTO itemmast VALUES " + data[i]);
}
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Add_Transactions()
//
// Add an Order and associated Items "as a transaction" to their
// respective tables. A transaction is committed or aborted if the
// customer number on the order is confirmed valid. Likewise each
// item in the order is verified to be a valid item.
//
private static void Add_Transactions()
{
int i, j = 0;
String[][] orders = {
{ "09/01/2002", "09/05/2002", "1", "1001" },
{ "09/02/2002", "09/06/2002", "2", "9999" }, // bad customer number
{ "09/22/2002", "09/26/2002", "3", "1003" }
};
String[][] items = {
{ "1", "1", "2", "1" },
{ "1", "2", "1", "2" },
{ "2", "1", "1", "3" },
{ "2", "2", "3", "4" },
{ "3", "1", "2", "3" },
{ "3", "2", "2", "99"} // bad item number
};
System.out.println("\tAdd transaction records...");
for (i = 0; i < orders.length; i++)
{
try
{
// add order record
stmt.executeUpdate("INSERT INTO custordr VALUES (" +
"'" + orders[i][0] + "'," +
"'" + orders[i][1] + "'," +
"'" + orders[i][2] + "'," +
"'" + orders[i][3] + "')");
}
catch (SQLException e)
{
Handle_Exception(e);
}
// process order items
while (items[j][0].compareTo(orders[i][2]) == 0)
{
try
{
// add item record
stmt.executeUpdate("INSERT INTO ordritem VALUES (" +
items[j][1] + "," +
items[j][2] + "," +
"'" + items[j][0] + "'," +
"'" + items[j][3] + "')");
}
catch (SQLException e)
{
Handle_Exception(e);
}
// bump to next item
j++;
// exit the while loop on last item
if (j >= items.length)
break;
}
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
}
//
// Display_CustomerOrders()
//
// This function displays the contents of CustomerOrders table.
//
private static void Display_CustomerOrders ()
{
System.out.println("\n\tCustomerOrders Table...");
try
{
// execute a query statement
ResultSet rs = stmt.executeQuery ("SELECT * FROM custordr");
// fetch and display each individual record
while (rs.next()) {
System.out.println("\t " + rs.getString(3) + " " + rs.getString(4));
}
rs.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}
//
// Display_OrderItems()
//
// This function displays the contents of OrderItems table.
//
private static void Display_OrderItems ()
{
System.out.println("\n\tOrderItems Table...");
try
{
// execute a query statement
ResultSet rs = stmt.executeQuery ("SELECT * FROM ordritem");
// fetch and display each individual record
while (rs.next()) {
System.out.println("\t " + rs.getString(3) + " " + rs.getString(4));
}
rs.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Example 41. Code for
Done() //
// Done()
//
// This function handles the housekeeping of closing, freeing,
// disconnecting and logging out of the database
//
private static void Done ()
{
System.out.println("DONE");
Delete_Tables();
try
{
conn.commit();
}
catch (SQLException e)
{
Handle_Exception(e);
}
try
{
stmt.close();
// logout
System.out.println("\tLogout...");
conn.close();
}
catch (SQLException e)
{
Handle_Exception(e);
}
}For .NET and Java stored procedures. see the developers guide FairCom Java and .NET stored procedures, triggers, and user-defined functions.
The FairCom DB engine provides a variety of APIs — for example, low-level and the c-treeDB API for C. The entire c-treeDB API for C is available directly as function calls within the object-oriented c-treeDB API for C#. To learn more about FairCom APIs, see FairCom DB interfaces.