ESQL Dynamic Query

Hi, I have a table called 'JOB'. There are 20 columns in it. I want to write a single function which will take one or more column names and their values, at run time, and updates the database. Function prototype: int updateJob (noofrecs, char* colNames, char* colValues) colNames is a string which contains one or more column names separated by space character. Each invokation of updateJob at run time, may have diff. number of columns in colNames string. colValues is a string which will have value for each of the column name variable in colNames, separated by space character. I can parse the colNames and colValues to get the column names colName1, colName2, ... and their values, colValue1, colValue2 ... The point is that since i don't know until run time, the actual number of columns, how do i declare the ESQL host vairables, to hold these values and put in the ESQL statement? Is it possible to do something like write following func. int updateJob (noofrecs, char* colNames_inp, char* colValues_inp) { EXEC SQL begin declare section char* colNames; char* colValues; EXEC SQL end declare section /* memory allocation goes here */ /* Lets say max. number of columns user can enter is 80, and max. size of each of the column is 80 char. */ colNames = (char *) calloc (6401, siezof(char)); colValues = (char*) calloc (80, sizeof(char)); colNames_inp = (char *) calloc (6401, siezof(char)); colValues_inp = (char*) calloc (80, sizeof(char)); /* Take variable no.of column names form user, and corresponding values, I can't use sprintf, as i don't know how many column names user is going to enter. After taking input do following: */ strcpy(colNames, colNames_inp); strcpy(colValues, colValues_inp); EXEC SQL prepare stmid for 'update JOB set ? = ? where jobid = 12345678'; EXEC SQL EXECUTE stmid using :colNames, :colValues; In above sql statement :colNames hold variable no.of column names, depending on how many the user enters at run time. And :colValues hold values for these columns. } Appretiate any help. Anand Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

anandpa@my-deja.com wrote: > I have a table called 'JOB'. > There are 20 columns in it. > I want to write a single function which will take one or more column > names and their values, at run time, and updates the database. I'm not sure if I already replied to this (sorry if I'm repeating myself), but... Check out the UPDBLOB and SQLUPLOAD programs which are part of the SQLCMD package available from the IIUG archive. They both do slightly different jobs from what you're trying to do -- neither is tied to a particular table or database, for example -- but the general outlines are all there. > Function prototype: > int updateJob (noofrecs, char* colNames, char* colValues) > colNames is a string which contains one or more column > names separated by space character. > Each invokation of updateJob at run time, may have diff. number of > columns in colNames string. > colValues is a string which will have value for each > of the column name variable in colNames, separated by space character. >[...pseudo-code snipped...] -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>