Let Your Existing ISAM Applications Co-Exist With c-treeSQL!

c-treeSQL has been designed from its core to provide as much access as possible to all existing c-tree Plus data. For most applications, it is as simple as linking the data to the c-treeSQL Server system tables using the c-treeSQL Table Import Utility, ctsqlimp. Not only does this give you the ability to view and modify your tables with c-treeSQL, you also retain the ability to continue using your existing application!

• Advantages: No changes are made to the data and index file definitions, so the existing c-tree application can access the data without changes to the application.
• Considerations: Some higher-level SQL capabilities that require special internal fields, indexes, and file modes will not be supported unless the files and applications are adjusted to provide these requirements.

Table Definition Requirements

To take advantage of the ability to co-exist with c-treeSQL, certain requirements must be met to ensure compatibility.

• Tables must contain IFIL and DODA structures. These can be added after the fact for existing files and are inserted automatically for files created by c-treeDB and c-treeSQL.
• An ISAM application must use corresponding c-tree Plus data types (as defined in the DODA) as in the c-tree Plus - SQL data type mapping. For example a CT_CHAR field type is used in c-treeSQL to store a 1-byte integer.
  
  Note: There is an incompatibility between the use of CT_ARRAY in the current c-tree Plus ODBC Driver and the use of CT_ARRAY in c-treeDB and c-treeSQL, including the c-treeSQL ODBC Driver.
  
  CT_ARRAY fields are imported by default as a c-treeSQL Binary field. c-treeSQL expects the first four bytes of a binary field to specify the length of the field. When you create a table with c-treeSQL these four bytes are automatically created and maintained for you. When considering a CT_ARRAY field from c-tree Plus, you must explicitly include these four prefix bytes and assign the appropriate value. An example of how to properly handle this field is demonstrated in the example code at the end of this article. Should you have existing incompatible c-tree Plus CT_ARRAY fields to import into c-treeSQL, please contact your nearest FairCom office for suggestions and advice. We're here to help you.
  
  
• The table must have either TRNLOG or PREIMG in its file mode to use the ROLLBACK WORK and integrity constraint capabilities.
• Superfiles are not supported by c-treeSQL.
• In order to properly handle NULL, the table must contain the $NULFLD$ field, a hidden field generated by c-treeDB at creation time. Tables created with the c-treeDB interface (used with c-treeSQL) have a hidden field, $NULFLD$, which is used to determine if each user-created field in the record buffer has a NULL value. c-treeSQL requires this capability to implement constraints. c-treeDB and c-treeSQL will access tables without the $NULFLD$ field, but the table's fields will always return a non-NULL status.
• In order to properly handle JOINS referencing ROWID, the table should contain the $ROWID$ field (a hidden field generated by the c-treeDB at creation time). c-treeDB and c-treeSQL should work with tables without the $ROWID$ field, and will use the record offset as the ROWID tuple identifier. SQL statements like "select * from table where rowid > '4'" will fail because using record offset as ROWID will give us record offsets instead of sequential numbers.

Note: When c-tree updates a variable length record, the record offset for the record may change if the updated record size is larger than the original record. In this particular case, the ROWID for this ROW will not be unique, as required by the SQL standard.

Adding a DODA to an Existing Data File

To use c-treeSQL 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:

1. Include a data object definition array (DODA) which is simply an array of DATOBJ structures, as defined below.
2. Open each data file in ctEXCLUSIVE mode.
3. Call PutDODA for each file to insert the corresponding DODA resource.
4. Close the files.

A DODA is a data object definition array. Each element of the array is comprised of a structure of type DATOBJ. Only three of the first four fields of the DATOBJ are required for standard c-tree Plus data files.

DATOBJ is defined as follows:

typedef struct {

  pTEXT     fsymb;  /* ptr to symbol name            */

  pTEXT     fadr;   /* adr of field in record buffer */

  UCOUNT    ftype;  /* type indicator                */

  UCOUNT    flen;   /* field length                  */

  ...

} DATOBJ;

• fsymb points to a unique symbolic name for the field and should not be NULL.
• fadr is not used by c-tree Plus (its value is ignored).
• ftype is one of the field types specified in the "Field Types" table.
• flen is set to the field's length for fixed length fields, or the known maximum for varying length fields with a known maximum length, or zero for varying length fields without a known maximum length. If the field type has an intrinsic length, which is true for types CT_CHAR through CT_DFLOAT, a zero length is automatically replaced by the intrinsic length.

Given a data record with the structure:

struct {

  TEXT      zipcode[10]; /* Zip code           */

  LONG      ssn;         /* social security #  */

  TEXT      name[50];    /* name               */

} DATA_FORMAT;

The corresponding DODA would be defined as:

DATOBJ doda[] = {

    {"ZipCode",NULL,CT_FSTRING,10},

    {"SocialSecurity",NULL,CT_INT4},

    {"Name",NULL,CT_STRING,50}

};

Note: The two string fields show the difference between fixed-length and variable-length strings. zipcode , CT_FSTRING, takes up a fixed space in the record (10 bytes) and does not require a NULL to terminate the string. name , CT_STRING, takes up a variable amount of space up to a maximum of 50 bytes and is NULL terminated.

The field types are described in Section D.2 "Field Types" of the c-tree Plus Programmer's Reference Guide .

The PutDODA call inserts the DODA object as a resource into the data file. The function is declared as follows:

PutDODA (COUNT PUTDODA(COUNT datno,pDATOBJ doda,UCOUNT numfld))

PutDODA assigns the contents of a data object definition array (DODA) to data file datno , which must be opened in ctEXCLUSIVE mode. doda points to the beginning of the DODA as described above. The numfld parameter indicates how many data fields are in the DODA, three in the example above.

See the PutDODA function description in the c-tree Plus Function Reference Guide and Appendix D "Record Schemes" in the c-tree Plus Programmer's Reference Guide for additional details, and call FairCom for assistance if needed.

Index Definition Requirements

• If an index contains a segment consisting of a "partial field" (i.e., does not use the c-tree Plus Schema segment modes or the segment starting offset and the segment length are different from the field starting offset and the field length) c-treeSQL cannot access this index, even though the index is still properly updated by c-tree. You will need to create a new index in c-treeSQL composed of the corresponding columns.
• If there is more than one logical index in one physical index file, the DROP INDEX and the DROP TABLE commands will not work properly.
• ALTER TABLE may not work correctly if tables contain index segments that do not start at field boundaries and/or span over several fields.
  
  For example, if a field is deleted from the table, and this field is part of an index segment that spans over several fields, c-treeSQL may not know how to adjust the index segment length after the field is deleted from the table. The resulting index definition may not be correct. Tables with unusual characteristics may also not work correctly and the altered table may inherit characteristics preventing them from working in the original application.

Example

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 c-treeSQL Table Import Utility. utility to link the table to c-treeSQL:

>ctsqlimp isam_table.dat -u ADMIN -a ADMIN

Finally, run the c-treeSQL Interactive Utility, ISQL. utility to issue c-treeSQL statements against the table:

>ISQL -u ADMIN -a ADMIN ctreeSQL

ISQ>SELECT * FROM isam_table;

Troubleshooting ISAM to c-treeSQL Problems

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(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:

>ctsqlimp c:\old_data\datafile.dat -d c-treeSQL -s FAIRCOMS -u ADMIN -a ADMIN

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.

Important!

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!

Example ISAM Application with Proper c-treeSQL Constructs

Click here for the example ready to run source code file, ct_guid.c

/* --------------------------------------------------------------------------

   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);

}