Dynamic ESQLC Question

Hi, Here is the problem i have.... 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. The Function prototype is : 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 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 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 /* mempry allocation goes here */ strcpy(colNames, colNames_inp); strcpy(colValues, colValues_inp); EXEC SQL update JOB set :colNames = :colValues where jobid = '12345678'; } I know that, the problem i have presented is more complex, than i have tried to put in the above function. 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. > > The Function prototype is : > 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 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 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 > > /* mempry allocation goes here */ > > strcpy(colNames, colNames_inp); > strcpy(colValues, colValues_inp); > > EXEC SQL update JOB set :colNames = :colValues > where jobid = '12345678'; > > } > > I know that, the problem i have presented is more complex, than i have > tried to put in the above function. Try poking around the SQLCMD code at the IIUG web site. There's a command UPDBLOB which builds UPDATE statements on the fly and deals with the hardest of the 7.x data types, BYTE and TEXT blobs. Also poke around SQLUPLOAD, which does all of the above and more. Oh, don't forget that you'll need to identify the rows to be updated, somehow. You presumably don't want the same values in all rows in the table... -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

anandpa@my-deja.com wrote: Hi anandpa, ... You can solve your problem by using PREPARE and EXECUTE. ... First parse your input-parameters, and build (sprintf()) the whole command into a string (residing in a hostvariable) You'll end with a character-string like this: "UPDATE tablename SET var1 = val1 , var2 = val2 where condition" ... Then prepare the string for execution: EXEC SQL PREPARE CMD FROM :cmdvar ; ... Then (if SQLCODE==0) execute the command: EXEC SQL EXECUTE CMD ; ... At last, free up resources: EXEC SQL FREE CMD ; ... That's all...hope this helps Regards Rainer