Re: Generic DECLARE SQL-statement ???

In article <25i2jv$4n1@track.taz.de> michel@taz.de writes: >I'm writing an abstraction layer in C++ for the INFORMIX ESQL/C interface. > I'm somewhat stuck with the problem of writing a generic DECLARE statement > with respect to the cursor name. As far as I know you cannot put a DECLARE > into a PREPARE statement, so I've found no way to use a C-string as a cursor > name in ESQL/C. The only solution I've found is to provide a fixed number > of cursors in that layer, which are used stack-like. This solution works > as far nobody wants to open more cursors than I provide at the same time, > but it is not what I wanted. > >Any suggestions ??? Yes, I have accomplished what you are seeking to do under Informix 4.10.UC1. The solution I have employed for creating a generic SQL interface to isolate all my applications from using ESQL/C directly was based on observing what the preprocessor created and utilizing the low level calls. I can not supply you with finished code as the my company the Flood Group is a for profit organization, however I can give you a few tips and pointers on how I solved a problem similar to yours. I use a handle based approach to allocate just a many SQL CURSOR structures as my applications need. My implementation for the handle for the query statment is as follows [other statements like insert and delete are similar]: #define IDS_QRY_MAXARGS 200 /* defines max data items or DB */ /* columns which can be returned */ typedef struct ids_qry_handle { struct sqlda *Udesc; /* Informix: SQL descriptor */ _SQCURSOR SQ; /* Informix: cursor structure */ int Argno; /* number of data items returned */ char *Args[IDS_QRY_MAXARGS];/* ptrs. for return data */ char *Buffer; /* Allocated for return data */ } ids_qry_handle_t ; This handle is manipulated and assigned allocated memory when I call my generic 'prep' statement and some data is also allocated during a generic 'fetch' statement. Any allocated memory and the SQL CURSOR are freed with a generic 'close' statement. Below is shown a typical query which calls the generic library: /* * Given a table (extable) with a char column (name) and an integer * column (id), prepare a select for the table to return just these two * items if the value in column (id) < 102. Also order the returned * data by the column (id). Note other columns may exist. The statement * ids_gen_qry_prep( char *, <varargs>, NULL ) must be NULL terminated as * it can be called a varible number of pointers to data elements returned * by subsequent ids_gen_qry_fetch calls. */ char name[10]; /* big enough to hold data and a '\\0' terminator */ int id; void *hdl; /* prepare a query and bind 'name' and 'id' to receive any return data */ if ((hdl = ids_gen_qry_prep( "select name, id from extable where id < 102 order by id", name, &id, NULL)) != NULL) { /* At this point the two varibles name and id are bound via the */ /* hdl as the receiving containers for any ids_gen_qry_fetch() */ /* calls - indicator varibles are also supported but not shown */ /* in this example. */ while (ids_gen_qry_fetch(hdl)) { printf("fetch returned: <name = %s, id = %d>\\n", name, id); } /* perform all required resource deallocation and cleanups */ ids_gen_qry_term(&hdl); } The trick was to utilize the low level engine calls for the $prepare, $describe $open to form a generic 'prep' statement. This also involves allocating any required memory for housekeeping and checking parameters. Note this is 'C' code it is not a '*.ec' file. /* NO PRE-PROCESS use ctrl structure - $prepare usqlobj from $slctstmt; */ _iqprepare(&(ihndl->SQ), slctstmt); /* NO PRE-PROCESS use ctrl structure - $describe usqlobj into Udesc; */ _iqdscribe(&(ihndl->SQ), &(ihndl->Udesc)); /* NO PRE-PROCESS this just assigns a name - $declare usqlcurs cursor for usqlobj; */ _iqddclcur(&(ihndl->SQ), "usqlcurs", 0); /* NO PRE-PROCESS use ctrl structure - $open usqlcurs; */ _iqcopen(&(ihndl->SQ), 0, (char *) 0, (char *) 0, (char *) 0, 0); After the statement is genericly prepared the user may now perform fetches for the desired data. Once again the low level engine calls were used to emulated the $fetch call. This code is followed by the standard return code checking to determine end of table or ISAM/ESQL errors. Call to the generic fetch can be made multiple times using an active 'generic' handle. /* NO PRE-PROCESS use ctrl structure - $fetch usqlcurs using descriptor Udesc; */ _iqnftch(&(ihndl->SQ), 0, (char *) 0, ihndl->Udesc, 1, (long) 0, 0, (char *) 0, (char *) 0, 0); Lastly, the $free statement can be emulated. However the current rev of informix has a memory leak which will cause a loss of 8K (I think this is the right value) each time a new cursor is opened and freed. To overcome this memory leak which will eventually crash any system with lots of dynamic activity. I employed an additional free() call to deallocate the 'Udesc' field [or the sqlda structure] left allocated from Informix. /* NO PRE-PROCESS use ctrl structure - $free usqlcurs; */ _iqfree(&(ihndl->SQ)); free(ihndl->Udesc); /* free the sqlda structure Informix 4.10.UC1 misses */ As I indicated earlier we have written a whole suit of routines here at the flood group. Additional possibilities you may want to consider are on the fly table creation and database creation. I have implemented generic code which creates new tables on the fly '<root_tabname>_yymmdd' based on a date context internal to a realtime data feed. This insulation library was successfully ported to use Oracle as the underlying server with the same call interface. Database/Unix Consultant, Jon A. Strabala -- #include <standard.disclaimer> #include <witty.saying> #include <fancy.pseudo.graphics>