Section Navigation

Using Coldfusion CFX Tags with InterBase

by Kevin Groves, 20th July 2000

Introduction

We've been using InterBase 4.0 under Linux and writing CGI applications with DSQL for sometime. This has now become impractical due to the number and urgency of projects on our Intranet, so to combat this we began evaluating alternative development environments. The one we finally chose was the recently released Allaire's Coldfusion 4.5 server for Linux.

After a very short while we found that InterBase 4.0 wasn't natively supported by Coldfusion, and because we have a large number of systems which use triggers and the such like, the possibility of migrating to a (limited) supported database system (e.g. Postgres or MySQL) was out of the question.

At first we were put off, but exploration of the Coldfusion API gave me a few ideas on how to implement a (albeit) basic interface between Coldfusion and InterBase. The result has now enabled us to proceed with the investment of Coldfusion and with these couple of CFX tags we can implement systems until a proper ODBC Coldfusion driver is available.

The Environment

Coldfusion:	Coldfusion Professional 4.5 for Linux (Evaluation version) installed as per the documentation.
Web Server:	Running Apache 1.3.9
Linux:	SuSE 6.3
InterBase:	InterBase 6.0 Beta
Compiler:	egcs-2.91.66

The CFX Tag Code

Basically the code is cobbled together from the example Coldfusion CFX_DIRECTORYLIST tag and parts of the InterBase DSQL examples (and live code we use) . Because I was in a hurry to see if this worked and not waste time, I've overlooked a number of possible coding issues:

Very little (or no) error handling
Slightly messy and/or inefficient coding.
Not particularly easy to write SQL in Coldfusion i.e. No query builder - it has to be hand-coded SQL.

All those points aside, is that this works with InterBase 6.0 because I couldn't get InterBase version 4.0 to compile under SuSE 6.3 due to libc version change, and well, I suspect when Interbase and Allaire get talking they will probably only support version 6.0 with the new 6.0 ODBC.

Hopefully the CFX tags will easily equate to the usual CFQUERY tags and everything will be fine.

The CFX tags that I created have the following form:

<cfx_iquery name="<queryname>"             e.g. "query1"
dbname="<interbase gdb location>"          e.g. "server:/usr/interbase/file.gdb"
sql="<sql>"                                e.g. "select * from table1"
cols="<returned column vars>"              e.g. "name,address,tel"

<cfx_iexec dbname="<interbase gdb location>" e.g. "server:/usr/interbase/file.gdb"
sql="<sql>"                                  e.g. "delete from table1"

I did come across a bit of a problem with putting SQL within double quotes and that was when I was trying to include variables on WHERE clauses for example. To get around that, instead of enclosing the SQL in double quotes, use single quotes. It messes up Studio syntax highlighting but the tags still work.

An example:

<cfx_iquery name="query1" dbname="server:/usr/interbase/file.gdb"
sql='select name,address from  mailing where county="KENT"'
cols="vname,vaddress">

The resulting query can then be made available for use by CFOUTPUT and CFQUERY.

Use cfx_iexec for database insertion, deletion and updates but keep in mind that I'm not too sure about the coding for this, especially in regard to complex transaction tracking. I've had to code a COMMIT after issuing the EXECUTE IMMEDIATE of the passed SQL as I've not had time to see if the transaction is still open on a subsequent cfx_iexec tag.

Also, I had a few problems with coding user access and seeing we don't bother with access control within the database (I always connect to the database as SYSDBA or as a user with full access to all tables) I skipped that to make life easier. This aspect does affect the compile of the objects so if you find you can't return any data do check your table access.

TCFX_IQUERY (iquery.cc)

#if defined(sun)
#define _POSIX_PTHREAD_SEMANTICS
#endif

#include <stdio.h>
#include <sys/file.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <sys/param.h>
#include <unistd.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>

#include "cfx.h"             // CFX Custom Tag API
#include <ibase.h>

// Constants
#define TAG_ERROR_HEADER     "Error occurred in CFX_IQUERY tag"

// Forward declarations for helper functions
LPCSTR GetRequiredAttribute( CCFXRequest* pRequest, LPCSTR lpszAttribName ) ;

isc_db_handle kent ;
short nc, dtype ;
isc_tr_handle dyn ;

// Decode ISQL fields (as detailed in DSQL manual examples)

char *process_column( XSQLVAR *var ) {
struct tm times ;
static char ret[1000 ];

memset( ret, 0, sizeof( ret ) ) ;

if( ( var->sqltype & 1 ) && ( *(var->sqlind ) == -1 ) ) {
}
else {
dtype = ( var->sqltype & ~1 ) ;
switch( dtype ) {
case SQL_VARYING :
case SQL_TEXT :
return var->sqldata ;
break ;
case SQL_DOUBLE :
sprintf( ret, "%f", *(double *) var->sqldata  ) ;
return ret ;
break;
case SQL_DATE :
isc_decode_date(  ( ISC_QUAD *)var->sqldata, × ) ;
sprintf( ret, "%02d/%02d/%2d", times.tm_mday, times.tm_mon+1, times.tm_year+1900 ) ;
return ret ;
break ;
case SQL_LONG :
sprintf( ret, "%ld", *(long *) var->sqldata  ) ;
return ret ;
break;
case SQL_SHORT :
sprintf( ret, "%d", *(short *) var->sqldata  ) ;
return ret ;
break;
}
}
}


// Main code

extern "C"
void ProcessTagRequest( CCFXRequest* pRequest )
{
try
{
// Get the NAME attribute (determines what to name the query we return)
LPCSTR lpName = GetRequiredAttribute( pRequest, "NAME" ) ;

// Get the SQL attribute (determines the SQL code to execute)
LPCSTR lpSQL = GetRequiredAttribute( pRequest, "SQL" ) ;

// Get the DBNAME attribute (determines the DB to connect to)
LPCSTR lpDBName = GetRequiredAttribute( pRequest, "DBNAME" ) ;

// Get the COLS attribute (determines the field columns to return)
LPCSTR lpCols = pRequest->GetAttribute( "COLS" ) ;

// Create a query object to return to the client
CCFXStringSet* pColumns = pRequest->CreateStringSet() ;

// Get all the column names
int fi[100], ff=0 ;
char *col = strtok( lpCols, "," ) ;
while( col != NULL ) {
fi[ff++] = pColumns->AddString(col) ;
col = strtok( NULL, "," ) ;
}

// Eval SQL
CCFXQuery* pQuery = pRequest->AddQuery( lpName, pColumns ) ;

// Setup ISQL
XSQLDA *out_sqlda ;
XSQLVAR *var ;
char str[2000];
char db[129];

kent = 0L ;
strcpy( db, lpDBName ) ;

EXEC SQL
SET DATABASE kent = COMPILETIME "/opt/kent.gdb" RUNTIME :db ;

EXEC SQL
CONNECT kent ;

int irow, a=0 ;

// Setup a dynamic SQL statement from selection options

EXEC SQL
EXECUTE IMMEDIATE "SET TRANSACTION"  ;

out_sqlda = ( XSQLDA *) malloc( XSQLDA_LENGTH( 10 ) ) ;
out_sqlda->version = SQLDA_VERSION1 ;
out_sqlda->sqln = 10 ;   // setup 10 column buffers
strcpy( str, lpSQL ) ;

EXEC SQL
PREPARE sql_stmt FROM :str ;

EXEC SQL
DESCRIBE OUTPUT sql_stmt INTO SQL DESCRIPTOR out_sqlda ;

if( out_sqlda->sqld > out_sqlda->sqln ) {
nc = out_sqlda->sqld ;
free( ( XSQLDA * ) out_sqlda ) ;
out_sqlda = ( XSQLDA *) malloc( XSQLDA_LENGTH( nc ) ) ;

out_sqlda->version = SQLDA_VERSION1 ;
out_sqlda->sqln = nc ;

EXEC SQL
DESCRIBE OUTPUT sql_stmt INTO SQL DESCRIPTOR out_sqlda ;
}

for( nc=0, var = out_sqlda->sqlvar ; nc < out_sqlda->sqld ; nc++, var++ ) {
dtype = ( var->sqltype & ~1 ) ;
switch( dtype ) {
case SQL_VARYING :
var->sqltype = SQL_TEXT ;
var->sqldata = (char *)malloc(sizeof(char)*(var->sqllen+2));
memset( var->sqldata, 0, sizeof(char)*(var->sqllen+2) ) ;

break ;
case SQL_TEXT :
var->sqldata = ( char * ) malloc(sizeof(char)*(var->sqllen+2));
memset( var->sqldata, 0, sizeof(char)*(var->sqllen+2) ) ;
break ;
case SQL_LONG :
var->sqldata = ( char * ) malloc(sizeof(long)+1) ;
break ;
case SQL_SHORT :
var->sqldata = ( char * ) malloc(sizeof(short)) ;
break ;
case SQL_DATE :
var->sqldata = (char * ) malloc( sizeof( ISC_QUAD ) + 1 ) ;
break ;
case SQL_DOUBLE :
var->sqldata = ( char * ) malloc(sizeof(double)+1) ;
break ;
}
if( var->sqltype & 1 )
var->sqlind = ( short * ) malloc(sizeof(short));
}

EXEC SQL
DECLARE dyn_cursor CURSOR FOR sql_stmt ;

EXEC SQL
EXECUTE IMMEDIATE "SET TRANSACTION"  ;

EXEC SQL
OPEN dyn_cursor ;

nc = out_sqlda->sqln ;

// Populate CFX API query interface

EXEC SQL
FETCH dyn_cursor USING SQL DESCRIPTOR out_sqlda ;

a = 1 ;
while( !SQLCODE ) {
irow = pQuery->AddRow() ;
for( nc = 0 ; nc < out_sqlda->sqld ; nc++ ) {
pQuery->SetData(irow, fi[nc], process_column( &out_sqlda->sqlvar[nc] ) );
}

EXEC SQL
FETCH dyn_cursor USING SQL DESCRIPTOR out_sqlda ;
}

EXEC SQL
CLOSE dyn_cursor ;

EXEC SQL
EXECUTE IMMEDIATE "COMMIT"  ;
}

// Catch Cold Fusion exceptions & re-raise them
catch( CCFXException* e )

{
pRequest->ReThrowException( e ) ;
}

// Catch ALL other exceptions and throw them as
// Cold Fusion exceptions (DO NOT REMOVE! --
// this prevents the server from crashing in
// case of an unexpected exception)

catch( ... )
{
pRequest->ThrowException(
TAG_ERROR_HEADER,
"Unexpected error occurred while processing tag." ) ;
}
}

// Get the value for the passed attribute (throw an exception
// if the attribute was not passed to the tag)

LPCSTR GetRequiredAttribute( CCFXRequest* pRequest, LPCSTR lpszAttribName )
{

// Verify that the attribute exists (throw an exception
// if it does not)

if ( !pRequest->AttributeExists(lpszAttribName) )
{
char Err[1024];
sprintf(Err,
"The required attribute %s was not passed to the tag. ",
lpszAttribName);
pRequest->ThrowException( TAG_ERROR_HEADER, Err ) ;
}

// Return the attribute
return pRequest->GetAttribute( lpszAttribName ) ;
}
// EOF

CFX_IEXEC (iexec.cc)

#if defined(sun)
#define _POSIX_PTHREAD_SEMANTICS
#endif

#include <stdio.h>
#include <sys/file.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <sys/param.h>
#include <unistd.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>

#include "cfx.h"             // CFX Custom Tag API
#include <ibase.h>

// Constants

#define TAG_ERROR_HEADER     "Error occurred in CFX_IEXEC tag"

// Forward declarations for helper functions

LPCSTR GetRequiredAttribute( CCFXRequest* pRequest, LPCSTR lpszAttribName ) ;

isc_db_handle kent ;
short nc, dtype ;
isc_tr_handle dyn ;

// Main code

extern "C"
void ProcessTagRequest( CCFXRequest* pRequest )
{
try
{
// Get the SQL attribute (determines the SQL code to execute)
LPCSTR lpSQL = GetRequiredAttribute( pRequest, "SQL" ) ;

// Get the DBNAME attribute (determines the DB to connect to)
LPCSTR lpDBName = GetRequiredAttribute( pRequest, "DBNAME" ) ;

// Setup ISQL

XSQLDA *out_sqlda ;
XSQLVAR *var ;
char str[2000];
char db[129];
kent = 0L ;
strcpy( db, lpDBName ) ;

EXEC SQL
SET DATABASE kent = "/opt/kent.gdb" RUNTIME :db ;

EXEC SQL
CONNECT kent ;

int irow, a=0 ;

// Setup a dynamic SQL statement from selection options
EXEC SQL
EXECUTE IMMEDIATE "SET TRANSACTION" ;

EXEC SQL
EXECUTE IMMEDIATE :lpSQL ;

EXEC SQL
EXECUTE IMMEDIATE "COMMIT" ;
}

// Catch Cold Fusion exceptions & re-raise them
catch( CCFXException* e )
{
pRequest->ReThrowException( e ) ;
}

// Catch ALL other exceptions and throw them as
// Cold Fusion exceptions (DO NOT REMOVE! --
// this prevents the server from crashing in
// case of an unexpected exception)

catch( ... )
{
pRequest->ThrowException(
TAG_ERROR_HEADER,
"Unexpected error occurred while processing tag." ) ;
}
}

// Get the value for the passed attribute (throw an exception
// if the attribute was not passed to the tag)
LPCSTR GetRequiredAttribute( CCFXRequest* pRequest, LPCSTR lpszAttribName )
{
// Verify that the attribute exists (throw an exception
// if it does not)
if ( !pRequest->AttributeExists(lpszAttribName) )
{
char Err[1024];
sprintf(Err,
"The required attribute %s was not passed to the tag. ",
lpszAttribName);
pRequest->ThrowException( TAG_ERROR_HEADER, Err ) ;
}

// Return the attribute
return pRequest->GetAttribute( lpszAttribName ) ;
}
// EOF

Compilation and Installation

Either construct a Makefile or compile by hand with:

Makefile for cfx_iexec

INCLUDE = -I../../include
CXX = g++
LD = g++

iexec.so:    iexecout.o
$(LD) -shared -lgds -ldl -lcrypt -o iexec.so iexecout.o

iexecout.o:  iexecout.cc
$(CXX) $(INCLUDE) -c iexecout.cc

iexecout.cc: iexec.cc
gpre -cxx -manual -user sysdba -password password iexec.cc iexecout.cc

Makefile for cfx_iquery

INCLUDE = -I../../include
CXX = g++
LD = g++

iquery.so:   iqueryout.o
$(LD) -shared -lgds -ldl -lcrypt -o iquery.so iqueryout.o

iqueryout.o:         iqueryout.cc
$(CXX) $(INCLUDE) -c iqueryout.cc

iqueryout.cc:        iquery.cc
gpre -cxx -manual -user sysdba -password password iquery.cc iqueryout.cc

Installation

Assuming your Coldfusion is installed in the default location of /opt/coldfusion. Run the Cold Fusion Administrator and select the "Tags" screen.

Tag Name:	CFX_IQUERY
Server Library:	/opt/coldfusion/cfx/iquery.so
Procedure:	ProcessTagRequest

Add the new tag:

Do the same for cfx_iexec.

Everything should then be OK to use. Might be worth shutting down the Coldfusion services first though.

Sign In