This book contains the Java developer tutorials for SQL Stored Procedures and Triggers. In a few simple steps and about 15 minutes, you’ll be navigating your data.
- Install and start up the FairCom Database Engine.
- Execute the tutorials from a command-line or the FairCom DB SQL Explorer.
Tip: To view, edit, and run the source code for the tutorials, you can go directly into the <faircom>\drivers\java.sql.storedprocs\tutorials folder that is installed with the product. Each tutorial has been thoroughly tested on each operating system to ensure you have an excellent experience.
Note: FairCom supports all actively-supported LTS versions of the Oracle JDK, and the most recent release (non-beta) version of Oracle OpenJDK.
Introduction to Stored Procedures and Triggers
SQL stored procedures and triggers are an easy and powerful way to move advanced logic into your server core, protecting investments in performance-sensitive processing while maintaining a centrally managed approach for long-term maintenance. Stored procedures fully encapsulate business logic for business rule enforcement. Triggers maintain database integrity directly at the database server level.
FairCom DB SQL has long supported stored procedures, triggers, and user defined functions with Java for cross-platform development ease. In this release, stored procedures are available for your Microsoft Windows .NET development environment as well.
You can program your procedure in Java or any .NET supported language. This document focuses on Java.
FairCom stored procedure support also includes command-line utilities for deploying stored procedures, user-defined functions, and triggers. These utilities are described in the Java and .NET Stored Procedures manual.
Quick Start - Java Development
This Quick Start for Java stored procedures includes four simple tutorials. Four SQL scripts have been included in the SQL Stored Procedures Tutorial directory, located in:
drivers\java.sql.storedprocs\tutorials
Java Requirements for Stored Procedures
Note: The FairCom DB SQL Java Stored Procedure and Trigger support requires a Java Development Kit (JDK) be installed on your computer. A Java Runtime Environment (JRE) is not sufficient. FairCom DB V11.5 (FairCom RTG and FairCom Edge V2.5) and later require JDK V1.7 or newer.
Note: For the tutorials to work correctly, the three Java-related “SETENV” lines in your <faircom>/config/ctsrvr.cfg file need to be active (the lines are not commented out with leading semicolons), the lines are all set to paths that are valid on your computer, and there is no whitespace before or after the "=" sign. Here are some typical examples, which will need to be adjusted for your machine:
Microsoft Windows
; JDK environment settings - Be sure to set the JDK to your version.
SETENV CLASSPATH=C:\Program Files\Java\jdk1.7.0_75\jre\lib\rt.jar;.\classes\ctreeSQLSP.jar
SETENV JVM_LIB=C:\Program Files\Java\jdk1.7.0_75\jre\bin\server\jvm.dll
SETENV JAVA_COMPILER=C:\Program Files\Java\jdk1.7.0_75\bin\javac.exe
Linux
; JDK environment settings - Be sure to set the JDK to your version.
SETENV CLASSPATH=/usr/java/jdk1.7.0_75/jre/lib/rt.jar:./classes/ctreeSQLSP.jar
SETENV JAVA_COMPILER=/usr/java/jdk1.7.0_75/bin/javac
SETENV JVM_LIB=/usr/java/jdk1.7.0_75/jre/lib/amd64/server/libjvm.soThe lines above all need to correctly point to your JDK installation folder before you start the c-tree server, because changes to ctsrvr.cfg take effect only when you launch the c-tree server.
All of these Java-related lines should point at files which are in the same JDK folder (“jdk1.7.0_75” in this example), and not from a JRE installation. Violating these rules can result in problems that can be difficult to track down.
In the CLASSPATH line, the path to ctreeSQLSP.jar is relative to the “server” folder. On Linux, the entries in the CLASSPATH line should be separated with colons instead of semicolons.
The purpose of these three lines is to give the c-tree server the information it needs to compile and run Java source code. This is because the stored procedures demonstrated by this tutorial are written in Java.
If you are using FairCom RTG or FairCom Edge, adjust the path to match your product.
Executing the Tutorials
These samples consist of three SQL Scripts. To execute these SQL scripts we will use the FairCom DB Interactive SQL utility (isql) or FairCom DB SQL Explorer.
Overview
The FairCom DB SQL stored procedures and triggers support provide the ability to write Java routines that contain SQL statements and store those routines with a database under the FairCom DB SQL Server. Tools and applications can then execute the procedures.
A stored procedure is a snippet of Java code embedded in a SQL CREATE PROCEDURE statement. The Java snippet can use all standard Java features as well as use the supplied Java classes for processing SQL statements.
Command Line Execution Using Interactive SQL - Windows
This section explains the steps to run the Java SQL Stored Procedures and Triggers samples from the command line on Microsoft Windows using FairCom DB SQL Interactive SQL.
- The Java JDK is required for submitting stored procedures as this requires the Java compiler. The Java runtime engine (JRE) is required to execute stored procedures and triggers. In addition, the FairCom DB SQL stored procedure class is required for accessing the necessary FairCom DB SQL Java classes, and should be a part of your CLASSPATH. For details see the note titled Java Requirements for Stored Procedures.
- If the FairCom Database Engine is not already running on your machine, start the server as explained in Start and stop the server.
- Open a command prompt window. This can be done by opening the Start menu, typing CMD, and clicking the icon for Command Prompt.
- In the command prompt window, move to the drivers\java.sql.storedprocs\tutorials\cmdline directory and execute the BuildTutorials.bat file.
- The batch file will use the Interactive SQL tool, isql.exe, to run the four tutorial files one-at-a-time. A prompt will ask you to press <Enter> before running each tutorial.
If you see a c-tree error, you can look it up in the Errors. See the Troubleshooting section below for solutions to common problems.
The first tutorial should look similar to the image below when running:
INIT
Creating procedure Define...
Creating procedure Add_Records...
Creating procedure Display_Records...
Creating procedure Delete_Records...
DEFINE
0 records returned
MANAGE
Delete records...
0 records returned
Add records...
0 records returned
0 records returned
0 records returned
0 records returned
Display records...
NUMB NAME
---- ----
1000 Bryan Williams
1001 Michael Jordan
1002 Joshua Brown
1003 Keyon Dooling
4 records returned
Delete records...
0 records returned
DONETutorial #3 ends with "Issue a COMMIT WORK to commit changes and release locks". This is because you cannot start or end (begin or commit) a transaction inside a stored procedure. Note that you can, if you want, wrap a stored procedure call inside a transaction, though. For example:
CALL PROCEDURE1();
ROLLBACK; -- This will remove any change made by PROCEDURE1 (if any)
CALL PROCEDURE2();
COMMIT; -- This will commit any change made by PROCEDURE2 (if any)Tutorial #4 will say “error(-20148): Trigger Execution Failed” four times and should end with "Nr of errors detected = 4". In this tutorial, we are using triggers to validate the referential integrity of the data we are inserting. If the trigger fails, the record is invalid. We are deliberately inserting four invalid records to demonstrate how the trigger can raise an exception and prevent record insertion.
Executing the Tutorials by Hand
You can run the tutorials individually by hand using the isql.exe tool and the SQL files located in drivers\java.sql.storedprocs\tutorials. As a short-cut, we first set the ISQL environment variable to the path of the isql.exe tool:
set ISQL=..\..\ctree.drivers\bin\isql.exe
%ISQL% -s SPTTutorial1.sql -u admin -a ADMIN ctreeSQL
%ISQL% -s SPTTutorial2.sql -u admin -a ADMIN ctreeSQL
%ISQL% -s SPTTutorial3.sql -u admin -a ADMIN ctreeSQL
%ISQL% -s SPTTutorial4.sql -u admin -a ADMIN ctreeSQLNote: Please refer to the Interactive SQL user guide for the supported command-line switches.
Troubleshooting
This section lists some of the errors that can occur when running these tutorials:
error in tcp bind 10060
error(-20212): Error in Network Daemon
The most common cause of this error is that the FairCom Database Engine is not running on your machine. Remember that the c-tree evaluation license times out after 3 hours, so the server might have exited and needs to be re-started. See Start and stop the server.
Creating procedure Define...
error(-20141): error in compiling the stored procedure
The most common cause of this error is the three Java-related “SETENV” lines in your FairCom ctsrvr.cfg file not being set correctly. Please refer to the Java Requirements for Stored Procedures note above.
error(-20160): Java SP/T Feature not supported
This can be caused by the path to ctreeSQLSP.jar being incorrect in the “SETENV CLASSPATH” line in your FairCom ctsrvr.cfg file. Please refer to the Java Requirements for Stored Procedures note above.
GUI Execution Using FairCom DB SQL Explorer - Windows
To execute the Stored Procedures and Triggers samples using FairCom DB SQL Explorer on Microsoft Windows, follow the steps below.
- The Java JDK is required for submitting stored procedures as this requires the Java compiler. The Java Runtime Engine (JRE) is required to execute stored procedures and triggers. In addition, the FairCom SQL stored procedure class is required for accessing the necessary Java classes, and should be a part of your CLASSPATH. For details see the note titled Java Requirements for Stored Procedures.
- If the FairCom Database Engine is not already running on your machine, start the server as explained in Start and stop the server..
- Start FairCom DB SQL Explorer (use the Windows Start menu > FairCom > Tools > gui > c-treeSqlExplorer.exe (or by double-clicking c-treeSqlExplorer.exe in the <faircom>\tools\gui\ folder).
- Connect to FairCom DB SQL installed on your computer:
- Once you are connected (you will see a confirmation dialog) move to the "Scripts" tab and click the Load button from the tool bar:
- Move to the drivers\java.sql.storedprocs\tutorials directory and open SPTTutorial1.sql
- Click the Run button to execute the tutorial.
Do not use the Single Step button for these tutorials, because stored procedures are not currently supported by the FairCom DB SQL Explorer single-step function.
- To run the other tutorials, repeat these steps for the other tutorials in the drivers\java.sql.storedprocs\tutorials directory.
Note that tutorial #4 will print the following error messages several times: “Error : -20148 Error Description : Trigger Execution Failed”. This is expected, correct behavior, and indicates that the system is running correctly.
Troubleshooting
This section lists some of the errors that can occur when running these tutorials:
Creating procedure Define...
error(-20141): error in compiling the stored procedure
The most common cause of this error is the three Java-related “SETENV” lines in your FairCom ctsrvr.cfg file not being set correctly. Refer to the Java Requirements for Stored Procedures note.
error(-20160): Java SP/T Feature not supported
This can be caused by the path to ctreeSQLSP.jar being incorrect in the “SETENV CLASSPATH” line in your FairCom ctsrvr.cfg file. Refer to the Java Requirements for Stored Procedures note.
Error : -30031 Error Description : error in transmission of packet
The most common cause of this runtime error is the FairCom Database Engine is not running on your machine. The most likely cause is that the c-tree evaluation license times out after 3 hours and shuts down the server. The solution is to restart the server. See Start and stop the server.
Command-Line Execution Using Interactive SQL - Linux
This section explains the steps to run the Java SQL Stored Procedures and Triggers samples from a Linux shell using FairCom Interactive SQL.
- The Java JDK is required for submitting stored procedures as this requires the Java compiler. The Java runtime engine (JRE) is required to execute stored procedures and triggers. In addition, the FairCom SQL stored procedure class is required for accessing the necessary Java classes, and should be a part of your CLASSPATH. For details see the note titled Java Requirements for Stored Procedures.
- If the FairCom Database Engine s not already running on your machine, start the server as explained in Start and stop the server.
- Open a Linux shell.
- In the shell, move to the drivers/java.sql.storedprocs/tutorials/cmdline directory and execute the following command:
make runThe batch file will use the ISQL tool to run the four tutorial files one-at-a-time. A prompt will ask you to press <Enter> before running each tutorial.
If you see a c-tree error, you can look it up in the Errors. See the Troubleshooting section below for solutions to common problems.
The output should look similar to the image below when running:
INIT
Creating procedure Define...
Creating procedure Add_Records...
Creating procedure Display_Records...
Creating procedure Delete_Records...
DEFINE
0 records returned
MANAGE
Delete records...
0 records returned
Add records...
0 records returned
0 records returned
0 records returned
0 records returned
Display records...
NUMB NAME
---- ----
1000 Bryan Williams
1001 Michael Jordan
1002 Joshua Brown
1003 Keyon Dooling
4 records returned
Delete records...
0 records returned
DONETutorial #3 ends with "Issue a COMMIT WORK to commit changes and release locks". This is because you cannot start or end (begin or commit) a transaction inside a stored procedure. Note that you can, if you want, wrap a stored procedure call inside a transaction, though. For example:
CALL PROCEDURE1();
ROLLBACK; -- This will remove any change made by PROCEDURE1 (if any)
CALL PROCEDURE2();
COMMIT; -- This will commit any change made by PROCEDURE2 (if any)Tutorial #4 will say “error(-20148): Trigger Execution Failed” four times and should end with "Nr of errors detected = 4". In this tutorial, we are using triggers to validate the referential integrity of the data we are inserting. If the trigger fails, the record is invalid. We are deliberately inserting four invalid records to demonstrate how the trigger can raise an exception and prevent record insertion.
Executing the Tutorials by Hand
You can run the tutorials individually by hand using the ISQL tool and the SQL files located in drivers/java.sql.storedprocs/tutorials. As a short-cut, we first set the ISQL variable to the path of the ISQL tool:
ISQL=../../ctree.drivers/bin/isql
$ISQL -s SPTTutorial1.sql -u admin -a ADMIN ctreeSQL
$ISQL -s SPTTutorial2.sql -u admin -a ADMIN ctreeSQL
$ISQL -s SPTTutorial3.sql -u admin -a ADMIN ctreeSQL
$ISQL -s SPTTutorial4.sql -u admin -a ADMIN ctreeSQLNote: Refer to the FairCom Interactive SQL user guide for all of the supported command-line switches.
Troubleshooting
This section lists some of the errors that can occur when running these tutorials:
error in tcp bind 111
error(-20212): Error in Network Daemon
The most common cause of this error is that the FairCom Database Engine is not running on your machine. Remember that the c-tree evaluation license times out after 3 hours, so the server might have exited and needs to be re-started. See Start and stop the server.
Creating procedure Define...
error(-20141): error in compiling the stored procedure
The most common cause of this error is the three Java-related “SETENV” lines in your FairCom ctsrvr.cfg file not being set correctly. Refer to the Java Requirements for Stored Procedures note above.
error(-20160): Java SP/T Feature not supported
This can be caused by the path to ctreeSQLSP.jar being incorrect in the “SETENV CLASSPATH” line in your FairCom ctsrvr.cfg file. Refer to the Java Requirements for Stored Procedures note above.
Tutorials
As with all other tutorials in the c-tree series, each of these database programming tutorials is implemented with four simple code procedures: Initialize(), Define(), Manage(), and Done().
No matter which FairCom interface language you use, FairCom follows this same high-level flow in all tutorials. This makes it easy for developers to "cross-over" from one language interface to another as these basic concepts apply to all.
Initialize()
Every language requires some form of initial "logon" or "connection" procedure to establish a session with the database. This is done in the Initialize() stage of the program.
Define()
Database definitions (DDL), Table/File schema definitions, Index definitions, Table/File creation, and Table/File open operations are all addressed in the Define() stage of the program.
Manage()
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.
Done()
When the program ends, the database session should be closed. This stage handles the necessities to "de-init", by closing Tables/Files and issuing any required "logoff" or "disconnect" type procedures.
Presented here are tutorials that follow the "Initialize(), Define(), Manage(), and Done()" approach.
You can also view similar tutorials for all supported languages.
Introductory Tutorial
drivers\java.sql.storedprocs\tutorials\SPTTutorial1.sql
This tutorial will take you through the basic use of the FairCom DB SQL Stored Procedures Technology.
As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done() !
Tutorial #1: Introductory - Simple Single Table
We wanted to keep this program as simple as possible. This program does the following:
- 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 Database Engine.
Note these sections in our SQL Script:
-- Initialize
ECHO INIT;
-- Define
ECHO DEFINE;
-- Manage
ECHO MANAGE;
-- Done
ECHO DONE;We suggest opening the source code with your own editor.
Continue now to review these four steps.
Init
First we need to open a connection to a database by providing the FairCom Database Engine. Notice that, unlike the tutorials for the other APIs, the user name, password, and the database name have already been supplied. Instead, the INIT section of the tutorial simply turns on AUTOCOMMIT and drops any existing copies of the stored procedures that will be created by this tutorial.
Below is the code for Initialize():
SET ECHO OFF
-- Initialize
ECHO INIT;
SET AUTOCOMMIT ON;
IF EXISTS (select proc_name from sysprocedures where proc_name = 'define')
BEGIN
DROP PROCEDURE Define;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'add_records')
BEGIN
DROP PROCEDURE Add_Records;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'display_records')
BEGIN
DROP PROCEDURE Display_Records;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'delete_records')
BEGIN
DROP PROCEDURE Delete_Records;
END
IF EXISTS (select tbl from systables where tbl = 'custmast')
BEGIN
DROP TABLE custmast;
END
Define
Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.
Below is the code for Define():
DROP PROCEDURE Define;
CREATE PROCEDURE Define()
BEGIN
SQLIStatement st = new SQLIStatement (
"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))"
);
st.execute();
END
-- Define
ECHO DEFINE;
CALL Define();
Manage
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
DROP PROCEDURE Add_Records;
DROP PROCEDURE Display_Records;
DROP PROCEDURE Delete_Records;
CREATE PROCEDURE Add_Records (
IN cm_custnumb CHAR(4),
IN cm_custzipc CHAR(9),
IN cm_custstat CHAR(2),
IN cm_custrtng CHAR(1),
IN cm_custname VARCHAR(47),
IN cm_custaddr VARCHAR(47),
IN cm_custcity VARCHAR(47)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO custmast VALUES (?,?,?,?,?,?,?) "
);
st.setParam (1, cm_custnumb);
st.setParam (2, cm_custzipc);
st.setParam (3, cm_custstat);
st.setParam (4, cm_custrtng);
st.setParam (5, cm_custname);
st.setParam (6, cm_custaddr);
st.setParam (7, cm_custcity);
st.execute();
END
CREATE PROCEDURE Display_Records ()
RESULT (
Numb CHAR(4),
Name CHAR(47)
)
BEGIN
SQLCursor cur = new SQLCursor ("SELECT cm_custnumb, cm_custname FROM custmast");
cur.open();
cur.fetch();
while (cur.found())
{
SQLResultSet.set(1, cur.getValue(1, CHAR));
SQLResultSet.set(2, cur.getValue(2, CHAR));
SQLResultSet.insert();
cur.fetch();
}
cur.close();
END
CREATE PROCEDURE Delete_Records ()
BEGIN
SQLIStatement sp_DeleteTable = new SQLIStatement ("DELETE FROM custmast");
sp_DeleteTable.execute();
END
-- Manage
ECHO MANAGE;
ECHO Delete records...;
CALL Delete_Records ();
ECHO Add records...;
CALL Add_Records('1000', '92867', 'CA', '1', 'Bryan Williams', '2999 Regency', 'Orange');
CALL Add_Records('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford');
CALL Add_Records('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta');
CALL Add_Records('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia');
ECHO Display records...;
CALL Display_Records();
ECHO Delete records...;
CALL Delete_Records ();
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.
Below is the code for Done():
-- Done
ECHO DONE;
Relational Model and Indexing
drivers\java.sql.storedprocs\tutorials\SPTTutorial2.sql
Now we will build some table/file relationships using the FairCom DB SQL Stored Procedures Technology.
This tutorial will advance the concepts introduced in the first tutorial by expanding the number of tables. We will define key columns/fields and create specific indexes for each table to form a relational model database.
Like all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done() !
Tutorial #2: Relational Model and Indexing
Here we add a bit more complexity, introducing multiple tables, with related indices in order to form a simple "relational" database simulating an Order Entry system. Here is an overview of what will be created:
- Initialize() - Connects to the FairCom Database Engine.
- Define() - Defines and creates the "custmast", "custordr", "ordritem" and the "itemmast" tables/files with related indexes.
- Manage() - Adds some related rows/records to all tables/files. Then queries the database.
- Done() - Disconnects from FairCom Database Engine.
Note these sections in our SQL Script:
-- Initialize
ECHO INIT;
-- Define
ECHO DEFINE;
-- Manage
ECHO MANAGE;
-- Done
ECHO DONE;We suggest opening the source code with your own editor.
Continue now to review these four steps.
Init
First we need to open a connection to the FairCom Database Engine.
Below is the code for Initialize():
SET ECHO OFF
-- Initialize
ECHO INIT;
SET AUTOCOMMIT ON;
IF EXISTS (select proc_name from sysprocedures where proc_name = 'define')
BEGIN
DROP PROCEDURE Define;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'add_customermaster_record')
BEGIN
DROP PROCEDURE Add_CustomerMaster_Record;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'add_customerorders_record')
BEGIN
DROP PROCEDURE Add_CustomerOrders_Record;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'add_orderitems_record')
BEGIN
DROP PROCEDURE Add_OrderItems_Record;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'add_itemmaster_record')
BEGIN
DROP PROCEDURE Add_ItemMaster_Record;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'delete_records')
BEGIN
DROP PROCEDURE Delete_Records;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'display_records')
BEGIN
DROP PROCEDURE Display_Records;
END
Define
Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.
Below is the code for Define():
DROP PROCEDURE Define;
CREATE PROCEDURE Define()
BEGIN
int TABLE_ALREADY_EXIST = -20041;
int INDEX_ALREADY_EXIST = -20028;
try
{
SQLIStatement cm = new SQLIStatement (
"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))"
);
cm.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement cm1 = new SQLIStatement (
"CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)"
);
cm1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement co = new SQLIStatement (
"CREATE TABLE custordr (" +
"co_ordrdate DATE, " +
"co_promdate DATE, " +
"co_ordrnumb CHAR(6), " +
"co_custnumb CHAR(4))"
);
co.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement co1 = new SQLIStatement (
"CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)"
);
co1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement co2 = new SQLIStatement (
"CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)"
);
co2.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement oi = new SQLIStatement (
"CREATE TABLE ordritem (" +
"oi_sequnumb SMALLINT, " +
"oi_quantity SMALLINT, " +
"oi_ordrnumb CHAR(6), " +
"oi_itemnumb CHAR(5))"
);
oi.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement oi1 = new SQLIStatement (
"CREATE UNIQUE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)"
);
oi1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement oi2 = new SQLIStatement (
"CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)"
);
oi2.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement im = new SQLIStatement (
"CREATE TABLE itemmast (" +
"im_itemwght INTEGER, " +
"im_itempric MONEY, " +
"im_itemnumb CHAR(5), " +
"im_itemdesc VARCHAR(47))"
);
im.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement im1 = new SQLIStatement (
"CREATE UNIQUE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)"
);
im1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
END
-- Define
ECHO DEFINE;
ECHO Create tables...;
CALL Define();
Manage
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
DROP PROCEDURE Add_CustomerMaster_Record;
DROP PROCEDURE Add_CustomerOrders_Record;
DROP PROCEDURE Add_OrderItems_Record;
DROP PROCEDURE Add_ItemMaster_Record;
DROP PROCEDURE Delete_Records;
DROP PROCEDURE Display_Records;
CREATE PROCEDURE Add_CustomerMaster_Record (
IN cm_custnumb CHAR(4),
IN cm_custzipc CHAR(9),
IN cm_custstat CHAR(2),
IN cm_custrtng CHAR(1),
IN cm_custname VARCHAR(47),
IN cm_custaddr VARCHAR(47),
IN cm_custcity VARCHAR(47)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO custmast VALUES (?,?,?,?,?,?,?)"
);
st.setParam (1, cm_custnumb);
st.setParam (2, cm_custzipc);
st.setParam (3, cm_custstat);
st.setParam (4, cm_custrtng);
st.setParam (5, cm_custname);
st.setParam (6, cm_custaddr);
st.setParam (7, cm_custcity);
st.execute();
END
CREATE PROCEDURE Add_CustomerOrders_Record (
IN co_ordrdate DATE,
IN co_promdate DATE,
IN co_ordrnumb CHAR(6),
IN co_custnumb CHAR(4)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO custordr VALUES (?,?,?,?)"
);
st.setParam (1, co_ordrdate);
st.setParam (2, co_promdate);
st.setParam (3, co_ordrnumb);
st.setParam (4, co_custnumb);
st.execute();
END
CREATE PROCEDURE Add_OrderItems_Record (
IN oi_sequnumb SMALLINT,
IN oi_quantity SMALLINT,
IN oi_ordrnumb CHAR(6),
IN oi_itemnumb CHAR(5)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO ordritem VALUES (?,?,?,?)"
);
st.setParam (1, oi_sequnumb);
st.setParam (2, oi_quantity);
st.setParam (3, oi_ordrnumb);
st.setParam (4, oi_itemnumb);
st.execute();
END
CREATE PROCEDURE Add_ItemMaster_Record (
IN im_itemwght INTEGER,
IN im_itempric MONEY,
IN im_itemnumb CHAR(5),
IN im_itemdesc VARCHAR(47)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO itemmast VALUES (?,?,?,?)"
);
st.setParam (1, im_itemwght);
st.setParam (2, im_itempric);
st.setParam (3, im_itemnumb);
st.setParam (4, im_itemdesc);
st.execute();
END
CREATE PROCEDURE Delete_Records (
IN tablename CHAR(256)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"DELETE FROM " + tablename
);
st.execute();
END
CREATE PROCEDURE Display_Records ()
RESULT (
Name CHAR(47),
Total FLOAT
)
BEGIN
SQLCursor cur = new SQLCursor (
"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"
);
cur.open();
cur.fetch();
while (cur.found())
{
SQLResultSet.set(1, cur.getValue(1, CHAR));
SQLResultSet.set(2, cur.getValue(2, FLOAT));
SQLResultSet.insert();
cur.fetch();
}
cur.close();
END
-- Manage
ECHO MANAGE;
ECHO Delete records...;
CALL Delete_Records('custmast');
CALL Delete_Records('custordr');
CALL Delete_Records('ordritem');
CALL Delete_Records('itemmast');
ECHO Add records...;
CALL Add_CustomerMaster_Record('1000', '92867', 'CA', '1', 'Bryan Williams', '2999 Regency', 'Orange');
CALL Add_CustomerMaster_Record('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford');
CALL Add_CustomerMaster_Record('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta');
CALL Add_CustomerMaster_Record('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia');
CALL Add_CustomerOrders_Record('9/1/2002', '9/5/2002', '1', '1001');
CALL Add_CustomerOrders_Record('9/2/2002', '9/6/2002', '2', '1002');
CALL Add_OrderItems_Record(1, 2, '1', '1');
CALL Add_OrderItems_Record(2, 1, '1', '2');
CALL Add_OrderItems_Record(3, 1, '1', '3');
CALL Add_OrderItems_Record(1, 3, '2', '3');
CALL Add_ItemMaster_Record(10, 19.95, '1', 'Hammer');
CALL Add_ItemMaster_Record(3, 9.99, '2', 'Wrench');
CALL Add_ItemMaster_Record(4, 16.59, '3', 'Saw');
CALL Add_ItemMaster_Record(1, 3.98, '4', 'Pliers');
ECHO Display records...;
CALL Display_Records();
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.
Below is the code for Done():
-- Done
ECHO DONE;
Record/Row Locking
drivers\java.sql.storedprocs\tutorials\SPTTutorial3.sql
Now we will explore row/record locks using the FairCom DB SQL Stored Procedures Technology.
The functionality for this tutorial focuses on inserting/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 <Enter> 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.
As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and you’re Done() !
Tutorial #3: Locking
Here we demonstrate the enforcement of data integrity by introducing record/row "locking".
- Initialize() - Connects to the FairCom Database Engine.
- Define() - Defines and creates a "customer master" (custmast) table/file.
- Manage() - Adds a few rows/records, manipulates them, and displays the results.
- Done() - Disconnects from FairCom Database Engine.
Note these sections in our SQL Script:
-- Initialize
ECHO INIT;
-- Define
ECHO DEFINE;
-- Manage
ECHO MANAGE;
-- Done
ECHO DONE;We suggest opening the source code with your own editor.
Continue now to review these four steps.
Init
First we need to open a connection to the FairCom Database Engine.
Below is the code for Initialize():
SET ECHO OFF
-- Initialize
ECHO INIT;
SET AUTOCOMMIT OFF;
IF EXISTS (select proc_name from sysprocedures where proc_name = 'define')
BEGIN
DROP PROCEDURE Define;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'add_customermaster_record')
BEGIN
DROP PROCEDURE Add_CustomerMaster_Record;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'delete_records')
BEGIN
DROP PROCEDURE Delete_Records;
END
IF EXISTS (select tbl from systables where tbl = 'custmast')
BEGIN
DROP TABLE custmast;
END
Define
Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.
Below is the code for Define():
DROP PROCEDURE Define;
CREATE PROCEDURE Define()
BEGIN
SQLIStatement cm = new SQLIStatement (
"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))"
);
cm.execute();
SQLIStatement cmi = new SQLIStatement (
"CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)"
);
cmi.execute();
END
-- Define
ECHO DEFINE;
CALL Define();
COMMIT WORK;
Manage
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
DROP PROCEDURE Add_CustomerMaster_Record;
DROP PROCEDURE Delete_Records;
CREATE PROCEDURE Add_CustomerMaster_Record (
IN cm_custnumb CHAR(4),
IN cm_custzipc CHAR(9),
IN cm_custstat CHAR(2)
IN cm_custrtng CHAR(1),
IN cm_custname VARCHAR(47),
IN cm_custaddr VARCHAR(47),
IN cm_custcity VARCHAR(47)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO custmast VALUES (?,?,?,?,?,?,?)"
);
st.setParam (1, cm_custnumb);
st.setParam (2, cm_custzipc);
st.setParam (3, cm_custstat);
st.setParam (4, cm_custrtng);
st.setParam (5, cm_custname);
st.setParam (6, cm_custaddr);
st.setParam (7, cm_custcity);
st.execute();
END
CREATE PROCEDURE Delete_Records (
IN tablename CHAR(256)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"DELETE FROM " + tablename
);
st.execute();
END
-- Manage
ECHO MANAGE;
ECHO Delete records...;
CALL Delete_Records('custmast');
ECHO Add records...;
CALL Add_CustomerMaster_Record('1000', '92867', 'CA', '1', 'Bryan Williams', '2999 Regency', 'Orange');
CALL Add_CustomerMaster_Record('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford');
CALL Add_CustomerMaster_Record('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta');
CALL Add_CustomerMaster_Record('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia');
COMMIT WORK;
UPDATE custmast SET cm_custname = 'KEYON DOOLING' WHERE cm_custnumb = '1003';
ECHO Issue a COMMIT WORK to commit changes and release locks
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.
Below is the code for Done():
/*
* Done()
*
* This function handles the housekeeping of closing connection and
* freeing of associated memory
*/
Transaction Processing
drivers\java.sql.storedprocs\tutorials\SPTTutorial4.sql
Now we will discuss transaction processing as it relates to the FairCom DB SQL Stored Procedures Technology.
Transaction processing provides a safe method by which multiple database operations spread across separate tables/files are guaranteed to be atomic. By atomic, we mean 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.
Like all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done()!
Tutorial #4: Transaction Processing
Here we demonstrate transaction control.
- Initialize() - Connects to the FairCom Database Engine.
- Define() - Defines and creates our four tables/files.
- Manage() - Adds rows/records to multiple tables/files under transaction control.
- Done() - Disconnects from FairCom Database Engine.
Note these sections in our SQL Script:
-- Initialize
ECHO INIT;
-- Define
ECHO DEFINE;
-- Manage
ECHO MANAGE;
-- Done
ECHO DONE;We suggest opening the source code with your own editor.
Continue now to review these four steps.
Init
First we need to open a connection to the FairCom Database Engine.
Below is the code for Initialize():
SET ECHO OFF
-- Initialize
ECHO INIT;
SET AUTOCOMMIT OFF;
IF EXISTS (select proc_name from sysprocedures where proc_name = 'define')
BEGIN
DROP PROCEDURE Define;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'add_customermaster_record')
BEGIN
DROP PROCEDURE Add_CustomerMaster_Record;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'add_customerorders_record')
BEGIN
DROP PROCEDURE Add_CustomerOrders_Record;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'add_orderitems_record')
BEGIN
DROP PROCEDURE Add_OrderItems_Record;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'add_itemmaster_record')
BEGIN
DROP PROCEDURE Add_ItemMaster_Record;
END
IF EXISTS (select proc_name from sysprocedures where proc_name = 'delete_records')
BEGIN
DROP PROCEDURE Delete_Records;
END
IF EXISTS (select triggername from systrigger where triggername = 'validate_customerorders_record')
BEGIN
DROP TRIGGER Validate_CustomerOrders_Record;
END
IF EXISTS (select triggername from systrigger where triggername = 'validate_orderitems_record')
BEGIN
DROP TRIGGER Validate_OrderItems_Record;
END
IF EXISTS (select tbl from systables where tbl = 'ordritem')
BEGIN
DROP TABLE ordritem;
END
IF EXISTS (select tbl from systables where tbl = 'custordr')
BEGIN
DROP TABLE custordr;
END
IF EXISTS (select tbl from systables where tbl = 'itemmast')
BEGIN
DROP TABLE itemmast;
END
IF EXISTS (select tbl from systables where tbl = 'custmast')
BEGIN
DROP TABLE custmast;
END
Define
Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.
Below is the code for Define():
DROP PROCEDURE Define;
CREATE PROCEDURE Define()
BEGIN
int TABLE_ALREADY_EXIST = -20041;
int INDEX_ALREADY_EXIST = -20028;
try
{
SQLIStatement cm = new SQLIStatement (
"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))"
);
cm.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement cm1 = new SQLIStatement (
"CREATE INDEX cm_custnumb_idx ON custmast (cm_custnumb)"
);
cm1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement co = new SQLIStatement (
"CREATE TABLE custordr (" +
"co_ordrdate DATE, " +
"co_promdate DATE, " +
"co_ordrnumb CHAR(6), " +
"co_custnumb CHAR(4))"
);
co.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement co1 = new SQLIStatement (
"CREATE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)"
);
co1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement co2 = new SQLIStatement (
"CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)"
);
co2.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement oi = new SQLIStatement (
"CREATE TABLE ordritem (" +
"oi_sequnumb SMALLINT, " +
"oi_quantity SMALLINT, " +
"oi_ordrnumb CHAR(6), " +
"oi_itemnumb CHAR(5))"
);
oi.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement oi1 = new SQLIStatement (
"CREATE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)"
);
oi1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement oi2 = new SQLIStatement (
"CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)"
);
oi2.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement im = new SQLIStatement (
"CREATE TABLE itemmast (" +
"im_itemwght INTEGER, " +
"im_itempric MONEY, " +
"im_itemnumb CHAR(5), " +
"im_itemdesc VARCHAR(47))"
);
im.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement im1 = new SQLIStatement (
"CREATE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)"
);
im1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
END
-- Define
ECHO DEFINE;
CALL Define();
COMMIT WORK;
Manage
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
DROP PROCEDURE Add_CustomerMaster_Record;
DROP PROCEDURE Add_CustomerOrders_Record;
DROP PROCEDURE Add_OrderItems_Record;
DROP PROCEDURE Add_ItemMaster_Record;
DROP PROCEDURE Delete_Records;
DROP TRIGGER Validate_CustomerOrders_Record;
DROP TRIGGER Validate_OrderItems_Record;
CREATE PROCEDURE Add_CustomerMaster_Record (
IN cm_custnumb CHAR(4),
IN cm_custzipc CHAR(9),
IN cm_custstat CHAR(2),
IN cm_custrtng CHAR(1),
IN cm_custname VARCHAR(47),
IN cm_custaddr VARCHAR(47),
IN cm_custcity VARCHAR(47)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO custmast VALUES (?,?,?,?,?,?,?)"
);
st.setParam (1, cm_custnumb);
st.setParam (2, cm_custzipc);
st.setParam (3, cm_custstat);
st.setParam (4, cm_custrtng);
st.setParam (5, cm_custname);
st.setParam (6, cm_custaddr);
st.setParam (7, cm_custcity);
st.execute();
END
CREATE PROCEDURE Add_CustomerOrders_Record (
IN co_ordrdate DATE,
IN co_promdate DATE,
IN co_ordrnumb CHAR(6),
IN co_custnumb CHAR(4)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO custordr VALUES (?,?,?,?)"
);
st.setParam (1, co_ordrdate);
st.setParam (2, co_promdate);
st.setParam (3, co_ordrnumb);
st.setParam (4, co_custnumb);
st.execute();
END
CREATE PROCEDURE Add_OrderItems_Record (
IN oi_sequnumb SMALLINT,
IN oi_quantity SMALLINT,
IN oi_ordrnumb CHAR(6),
IN oi_itemnumb CHAR(5)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO ordritem VALUES (?,?,?,?)"
);
st.setParam (1, oi_sequnumb);
st.setParam (2, oi_quantity);
st.setParam (3, oi_ordrnumb);
st.setParam (4, oi_itemnumb);
st.execute();
END
CREATE PROCEDURE Add_ItemMaster_Record (
IN im_itemwght INTEGER,
IN im_itempric MONEY,
IN im_itemnumb CHAR(5),
IN im_itemdesc VARCHAR(47)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO itemmast VALUES (?,?,?,?)"
);
st.setParam (1, im_itemwght);
st.setParam (2, im_itempric);
st.setParam (3, im_itemnumb);
st.setParam (4, im_itemdesc);
st.execute();
END
CREATE TRIGGER Validate_CustomerOrders_Record
BEFORE INSERT ON custordr
REFERENCING NEWROW
FOR EACH ROW
BEGIN
SQLCursor cur = new SQLCursor ("SELECT cm_custnumb FROM custmast WHERE cm_custnumb = ?");
String custnumb = (String) NEWROW.getValue(4, CHAR);
cur.setParam(1, custnumb);
cur.open();
cur.fetch();
if (!cur.found())
{
cur.close();
DhSQLException ex = new DhSQLException (666, new String("Invalid customer number"));
throw ex;
}
cur.close();
END
CREATE TRIGGER Validate_OrderItems_Record
BEFORE INSERT ON ordritem
REFERENCING NEWROW
FOR EACH ROW
BEGIN
SQLCursor cur = new SQLCursor ("SELECT co_ordrnumb FROM custordr WHERE co_ordrnumb = ?");
String ordrnumb = (String) NEWROW.getValue(3, CHAR);
cur.setParam(1, ordrnumb);
cur.open();
cur.fetch();
if (!cur.found())
{
cur.close();
DhSQLException ex = new DhSQLException (666, new String("Invalid order number"));
throw ex;
}
cur.close();
cur = new SQLCursor ("SELECT im_itemnumb FROM itemmast WHERE im_itemnumb = ?");
String itemnumb = (String) NEWROW.getValue(4, CHAR);
cur.setParam(1, itemnumb);
cur.open();
cur.fetch();
if (!cur.found())
{
cur.close();
DhSQLException ex = new DhSQLException (666, new String("Invalid item number"));
throw ex;
}
cur.close();
END
CREATE PROCEDURE Delete_Records (
IN tablename CHAR(256)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"DELETE FROM " + tablename
);
st.execute();
END
-- Manage
ECHO MANAGE;
ECHO Delete records...;
CALL Delete_Records('custmast');
CALL Delete_Records('custordr');
CALL Delete_Records('ordritem');
CALL Delete_Records('itemmast');
ECHO Add records...;
CALL Add_CustomerMaster_Record('1000', '92867', 'CA', '1', 'Bryan Williams', '2999 Regency', 'Orange');
CALL Add_CustomerMaster_Record('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford');
CALL Add_CustomerMaster_Record('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta');
CALL Add_CustomerMaster_Record('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia');
CALL Add_ItemMaster_Record(10, 19.95, '1', 'Hammer');
CALL Add_ItemMaster_Record(3, 9.99, '2', 'Wrench');
CALL Add_ItemMaster_Record(4, 16.59, '3', 'Saw');
CALL Add_ItemMaster_Record(1, 3.98, '4', 'Pliers');
COMMIT WORK;
CALL Add_CustomerOrders_Record ('09/01/2002', '09/05/2002', '1', '1001');
CALL Add_OrderItems_Record (1, 2, '1', '1');
CALL Add_OrderItems_Record (2, 1, '1', '2');
CALL Add_CustomerOrders_Record ('09/02/2002', '09/06/2002', '2', '9999');
ECHO NOTE: trigger execution failure expected
CALL Add_OrderItems_Record (1, 1, '2', '3');
ECHO NOTE: trigger execution failure expected
CALL Add_OrderItems_Record (2, 3, '2', '4');
ECHO NOTE: trigger execution failure expected
CALL Add_CustomerOrders_Record ('09/22/2002', '09/26/2002', '3', '1003');
CALL Add_OrderItems_Record (1, 2, '3', '3');
CALL Add_OrderItems_Record (2, 2, '3', '99');
ECHO NOTE: trigger execution failure expected
COMMIT WORK;
SELECT co_ordrnumb "Order", co_custnumb "Name" FROM custordr;
SELECT oi_ordrnumb "Order", oi_itemnumb "Item" FROM ordritem;
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.
Below is the code for Done():
-- Done
ECHO DONE;
DROP TRIGGER Validate_CustomerOrders_Record;
DROP TRIGGER Validate_OrderItems_Record;
COMMIT WORK;
Additional Resources and Functionality
Be sure to see the developer's guide for .NET and Java stored procedures:
Diving Deeper into the FairCom DB API "NAV" APIs
The introductory tutorials have offered a glimpse into the ease and flexibility of this interface. The FairCom Database Engine interfaces include a rich array of features for nearly any data management need. The following features can be found in the developers' guides:
- Logging in
- Databases
- Tables
- Fields
- Field Types
- Indexes
- Records
- Inserting
- Bulk Inserting
- Updating
- Deleting
- Navigating Records
- Finding
- Bookmarking
- Filtering
- By Query
- By Partial Key
- By Index Range
- By Server-Side Batch Process
- Locking
- Transactions
- Metadata resources
To learn more about this API, see the developers' guides.
Additional APIs
The FairCom Database Engine provides a variety of APIs, such as Low-Level and the FairCom DB API API for C. In fact, the entire FairCom DB API API for C is available directly as function calls within the object-oriented FairCom DB API API for C#.