FairCom SQL has been designed from its core to provide as much access as possible to all existing FairCom data. For most applications, it is as simple as linking the data to the FairCom Server system tables using the Table Import Utility, ctsqlimp. Not only does this give you the ability to view and modify your tables with SQL, you also retain the ability to continue using your existing application!
/* --------------------------------------------------------------------------
ISAM to SQL Tutorial
The goal of this tutorial is to introduce the most basic c-tree Plus
ISAM API to accomplish creating and manipulating a table through the
c-tree Server.
From a functional point of view this application will perform the following:
1. Logon onto a session
2. Add 1 table with some fields
3. Populate the table with a few records
4. Display the contents of the table
Once this table has been built, it is also ready to use in c-treeSQL.
Use the c-treeSQL Import Utility, ctsqlimp, to link the table to
your c-treeSQL Server. You will then be able to query your data
through c-treeSQL statements!
This example creates and stores a UUID value for a list of names, and
stores them in a c-tree Plus data file. Several concepts are
demonstrated.
1. How to build an ISAM table compatible with c-treeSQL.
2. How to create and store a universally unique identifier
(UUID) with c-tree.
3. How to properly construct and use a CT_ARRAY field
for later import to c-treeSQL.
The table consists of 3 fields, a 'pad' field, a GUID field, and
a name field. In this example, the GUID field demonstrates how to
properly use a CT_ARRAY field as a c-treeSQL BINARY field. Notice
in particular, the added four byte length header to the field. While
this value is transparent to the c-treeSQL user, it is imperative
that this header be properly constructed with the correct value to
be imported into c-treeSQL.
-------------------------------------------------------------------------- */
/** Preprocessor definitions and includes **/
#include <stdio.h>
#include <string.h>
#include "ctreep.h "/* All necessary c-tree Plus headers */
#define END_OF_FILE INOT_ERR
/** Global declarations **/
/* Data File Number */
COUNT guid_no;
ISEG guid_seg = {
12,16,INTSEG
};
IIDX guid_idx = {
16, /* Length of index */
0, /* key type */
0, /* Dup Flag */
1, /* NULL key flag */
0, /* Empty Char */
1, /* Number of segments */
&guid_seg, /* Pointer to Segment Array */
NULL, /* Index Name */
NULL, /* Optional Index Name */
NULL, /* Alternate Collating Sequence */
NULL /* Option pointer to pad byte */
};
/* IFIL Definitions */
IFIL guid_dat = {
"GUID8", /* data file name ("dat" is always assumed)*/
-1, /* data file number */
52, /* data record length */
8192, /* data extension size */
ctSHARED, /* data file mode */
1, /* number of indices */
8192, /* index extension size */
ctSHARED, /* index file mode */
&guid_idx, /* pointer to index array */
"Delflag", /* pointer to first field name (r-tree) */
"Buffer" /* pointer to last field name (r-tree) */
};
/* Xtd8 File Definitions - we will use HUGE files in this example */
XCREblk xcreblk[2] = {
{ ctFILEPOS8 , 0, 0, 0, 0, 1048576},
{ ctFILEPOS8 , 0, 0, 0, 0, 1048576}
};
/* Data Record Definitions */
DATOBJ doda[] = {
{"pad",NULL,CT_FSTRING,8},
{"uuid",NULL,CT_ARRAY,20},
{"name",NULL,CT_FSTRING,24}
};
/* Names for records */
COUNT name_count = 6;
typedef struct {
TEXT *name;
} name_text;
name_text name_list[]= {
(pTEXT) "Craig",
(pTEXT) "Ray",
(pTEXT) "Jeff",
(pTEXT) "Jon",
(pTEXT) "Randal",
(pTEXT) "Marco"
};
/** Function declarations **/
#ifdef PROTOTYPE
VOID initialize(void), define(void), manage(void), done(void);
VOID Add_Records(void), Display_Records(void), Delete_Records(void);
VOID doError(TEXT *);
#else
VOID initialize(), define(), manage(), done();
VOID Add_Records(), Display_Records(), Delete_Records();
VOID doError();
#endif
/************************************************************************
* main() - The main() function implements the concept of *
* "Init, define, manage and you're done..." *
* *
************************************************************************/
#ifdef PROTOTYPE
NINT main (NINT argc, pTEXT argv[])
#else
NINT main (argc, argv)
NINT argc;
pTEXT argv[];
#endif
{
initialize();
define();
manage();
done();
getchar();
exit(0);
}
/************************************************************************
* initialize() - Perform the minimum requirement of logging onto *
* the c-tree Server *
* *
************************************************************************/
#ifdef PROTOTYPE
VOID initialize(VOID)
#else
VOID initialize()
#endif
{
COUNT retval=0;
#ifdef ctThrds
NINT trc;
#endif
ctrt_printf("INIT\n");
/* Initialize c-tree Plus and log on to Server */
ctrt_printf("\tLogon to Session...\n");
#ifdef ctThrds
if (trc = ctThrdInit(3, 0L, NULL))
{
ctrt_printf("\nERROR-> initialize(): ctThrdInit() \n");
ctrt_printf("\nerror = %d\n", trc);
ctrt_printf("*** Execution aborted *** \nPress Enter key to exit...");
getchar();
exit(0);
}
#endif
if (retval = InitISAMXtd(16, 16, 16, 16, 0, "ADMIN", "ADMIN", "FAIRCOMS"))
doError("initialize(): InitISAMXtd()");
}
/************************************************************************
* define() - Open the data file, if it exists, otherwise create and *
* re-Open the table. *
* *
************************************************************************/
#ifdef PROTOTYPE
VOID define(VOID)
#else
VOID define()
#endif
{
ctrt_printf("DEFINE\n");
/** Open data file **/
ctrt_printf("\tOpen Data File...\n");
if (OpenIFile(&guid_dat))
{
/** Create Data File **/
if (CreateIFileXtd8(&guid_dat, NULL, NULL, 0, NULL, NULL, xcreblk))
doError("define(); CreateIFileXtd8()");
guid_no = guid_dat.tfilno;
if (PutDODA(guid_no, doda, (UCOUNT) 3))
doError("define(); PutDODA 8()");
CloseIFile(&guid_dat);
if (OpenIFile(&guid_dat))
doError("define(); Re-OpenIFileXtd8()");
}
guid_no = guid_dat.tfilno;
}
/************************************************************************
* manage() - This function performs simple record functions of add, *
* delete, and gets *
* *
************************************************************************/
#ifdef PROTOTYPE
VOID manage(VOID)
#else
VOID manage()
#endif
{
ctrt_printf("MANAGE\n");
Delete_Records(); /* delete any existing records */
Add_Records(); /* populate the table with data */
Display_Records(); /* show contents of table */
}
/************************************************************************
* Add_Records() - This function adds records to a table in the *
* database from a static structure called *
* RECORD_DATA *
* *
************************************************************************/
#ifdef PROTOTYPE
VOID Add_Records(VOID)
#else
VOID Add_Records()
#endif
{
VRLEN offset;
TEXT inpbuf[256];
TEXT name_buf[24];
long length = 16;
COUNT i = 0;
#ifdef WIN32
GUID new_uuid;
#else
uuid_t new_uuid;
#endif
ctrt_printf("\tAdd Records...\n");
/* Add records to table */
for (i=0;i<name_count;i++) {
ctsfill(inpbuf, 0, 256);
ctsfill(name_buf, 0, 24);
strcpy(name_buf, name_list[i].name);
offset = 0;
/* Copy in the first position */
cpybuf(inpbuf, name_buf, 8);
offset += 8;
/* Create a new UUID */
#ifdef WIN32
CoCreateGuid(&new_uuid);
#else
uuid_generate (&new_uuid); */
#endif
cpybuf(inpbuf+offset, &length, 4);
offset += 4;
/* Copy the new UUID into the record buffer */
cpybuf(inpbuf+offset, &new_uuid, sizeof(new_uuid));
offset += sizeof(new_uuid);
/* Copy a name into the record buffer */
cpybuf(inpbuf+offset, name_buf, 24);
/** Add the record **/
if (AddRecord(guid_no, inpbuf))
doError("Add_Records(): AddVRecord()");
} /* End of for loop */
}
/************************************************************************
* Display_Records() - This function displays the contents of a table. *
* FirstRecord(), ctdbNextRecord() fetches a *
* record. Then each field is parsed and displayed. *
* *
************************************************************************/
#ifdef PROTOTYPE
VOID Display_Records(VOID)
#else
VOID Display_Records()
#endif
{
char sName[128];
unsigned char sUUID[16];
unsigned char buffer[256];
COUNT RetVal = 0;
int i = 0;
int j = 0;
unsigned char buf[40];
ctrt_printf("\tDisplay Records...");
ctsfill(&buffer,0,256);
ctsfill(&sName,0,128);
ctsfill(&sUUID,0,16);
/* Read the first record */
if (FirstRecord(guid_no, &buffer))
doError("Display_Records(): ctdbFirstRecord()");
while (!RetVal)
{
/* Copy the UUID field from the buffer. */
cpybuf(sUUID, buffer+12, 16);
/* Copy the name field from the buffer */
cpybuf(sName, buffer+28, 24);
/* Convert the UUID field to Windows GUID format */
memset(buf, 0, 40);
for( i=0,j=0; i<sizeof(uuid_t); i++ ) {
sprintf( buf+j, "%02X", sUUID[i] );
j += 2;
if( (i==3) || (i==5) || (i==7) || (i==9) ) {
*(buf+j) = '-';
++j;
}
}
strcat(buf, "");
/* print out the record contents */
printf("\n%s\tis associated with GUID of %s\n", sName, buf);
/* Read the next record */
RetVal = NextRecord(guid_no, &buffer);
if (RetVal == END_OF_FILE)
break; /* Last Record */
if (RetVal)
doError("Display_Records(): NextVRecord()");
}
}
/************************************************************************
* done() - This function handles the housekeeping of closing tables *
* and the freeing of associated memory. *
* *
************************************************************************/
#ifdef PROTOTYPE
VOID done(VOID)
#else
VOID done()
#endif
{
ctrt_printf("DONE\n");
/* Close data file (optional) */
ctrt_printf("\tClose table...\n");
if (CloseIFile(&guid_dat))
doError("done(): CloseIFile()");
/* Logout of session and free memory */
ctrt_printf("\tLogout...\n");
CloseISAM();
#ifdef ctThrds
ctThrdTerm();
#endif
}
/************************************************************************
* Delete_Records() - This function deletes records in a data file. *
* *
************************************************************************/
#ifdef PROTOTYPE
VOID Delete_Records(VOID)
#else
VOID Delete_Records()
#endif
{
COUNT RetVal;
NINT bRecSetEmpty;
TEXT inpbuf[256];
VRLEN len;
len = 256;
bRecSetEmpty = 0;
ctrt_printf("\n\tDelete records...\n\n");
if ((RetVal = FirstRecord(guid_no, &inpbuf)) != NO_ERROR)
{
if (RetVal == END_OF_FILE)
bRecSetEmpty = 1;
else
doError("Delete_Records(): FirstVRecord()");
}
while (!bRecSetEmpty) /* delete til table empty */
{
if ((RetVal = DeleteRecord(guid_no)) != NO_ERROR)
doError("Delete_Records(): DeleteVRecord()");
/* Read the next record */
len = 256;
if ((RetVal = NextRecord(guid_no, &inpbuf)) != NO_ERROR)
{
if (RetVal == END_OF_FILE)
bRecSetEmpty = 1;
else
doError("Delete_Records(): NextVRecord()");
}
}
}
/************************************************************************
* doError() - This function is a common bailout routine. It displays *
* an error message allowing the user to acknowledge before *
* terminating the application *
* *
************************************************************************/
#ifdef PROTOTYPE
VOID doError(TEXT mesg[])
#else
VOID doError(mesg)
TEXT mesg[];
#endif
{
ctrt_printf("\nERROR-> %s \n", mesg);
ctrt_printf("\nisam_err = %d, isam_fil = %d, sysiocod = %d\n", isam_err, isam_fil, sysiocod);
ctrt_printf("*** Execution aborted *** \nPress Enter key to exit...");
getchar();
exit(0);
}
To take advantage of the ability to co-exist with SQL, certain requirements must be met to ensure compatibility.
To use the FairCom SQL Server with existing ISAM files that do not already have a DODA resource, add a DODA to each file. This is done most easily with a developer-created utility that opens each file and calls PutDODA to insert the required resource into that file. The utility should accomplish the following tasks:
...
The following application defines a typical c-tree Plus ISAM data file and index with the proper IFIL and DODA resources necessary for use with c-treeSQL. In addition, it demonstrates the proper construction of a CT_ARRAY field to be imported into a c-treeSQL database table as a BINARY field.
See Example ISAM Application with Proper c-treeSQL Constructs.
After executing this application, run the utility to link the table to c-treeSQL:
The easiest way to avoid common problems when importing c-tree Plus data files into c-treeSQL is to copy these files into the c-treeSQL database directory, typically located in the c-treeSQL Server directory. This gives the server direct access to the files. However, it is possible to link any c-tree Plus data file from any location. c-treeSQL Server access to the file is completely relative. The most common problem encountered is an FOPN_ERR error (12) from ctsqlimp when importing the table. In most cases, this is simply the c- treeSQL Server's inability to resolve the file's relative location from the c-treeSQL dictionary files. The most straightforward way to address this issue is to specify the full pathname when specifying the data file to import.
For example, to import a c-tree Plus data file existing in another directory different from the c-treeSQL Server, execute the ctsqlimp command as follows:
This will link the existing data file in place to the c-treeSQL Server. You can now query, add, and update the data with standard c-treeSQL statements.
While you can import data from another c-tree Server location into the c-treeSQL Server, keep in mind you can only use ONE of the servers to access the data. It is not possible to access a c-tree data file from multiple c-tree Servers simultaneously. With the c-treeSQL Server this is not a problem; you can continue to use your existing ISAM application with the new c-treeSQL Server!