prepare the SQL in the sub-routine

Hi, I am tuning the application. the previous application has performance issue. I have a application, it's framework is in below. ========================== #include .... int mainfunc() { for (;;) { << some C code>> ret=subfunc(); << some C code >> } } int subfunc() { << some C code>> EXEC SQL select count(*) into :v_col1 from tb1 where col2=:v_col2; << some C code >> } I changed the application like this. int mainfunc() { EXEC sql prepare s1 from "select count(*) from tb1 where col2=?"; for (;;) { << some C code>> ret=subfunc(); << some C code >> } } int subfunc() { << some C code>> EXEC SQL execute s1 into :v_col1 using :v_col2; if (SQLCODE) { return 200; } << some C code >> } The application compiled successful. but when It execute, it report some errors, s2 is not being knowed by the subfunc() function. then I changed the application to the below. s1 is like a global variable. #include .... EXEC sql prepare s1 from "select count(*) from tb1 where col2=?"; int mainfunc() { for (;;) { << some C code>> ret=subfunc(); << some C code >> } } int subfunc() { << some C code>> EXEC SQL execute s1 into :v_col1 using :v_col2; if (SQLCODE) { return 200; } << some C code >> } When I complile the application ,it report the syntax error in the blow line. EXEC SQL execute s1 into :v_col1 using :v_col2; The routine subfunc() has more than 1000 code. and lots of application using such kind of application framework. so I don't want repliace subfunc() with it's code definition,in the calling function. Do anybody know how to tuning suck kind of application? thanks for your time.

Try this one, it uses a variable holding the prepared statement name rather than a string in-place: char s1[5] = "S1"; int mainfunc() { EXEC sql prepare :s1 from "select count(*) from tb1 where col2=?"; for (;;) { << some C code>> ret=subfunc(); << some C code >> } } int subfunc() { << some C code>> EXEC SQL execute :s1 into :v_col1 using :v_col2; if (SQLCODE) { return 200; } << some C code >> } I've made the variable s1 a global, but you could also make it local to main() and pass it into subfunc() as an argument. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 8, 2012 at 1:19 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > Hi, > > I am tuning the application. the previous application has performance > issue. > > I have a application, it's framework is in below. > > ========================== > #include .... > int mainfunc() > > { > > for (;;) > { > > << some C code>> > > ret=subfunc(); > > << some C code >> > } > } > > int subfunc() > { > > << some C code>> > > EXEC SQL select count(*) into :v_col1 from tb1 where col2=:v_col2; > > << some C code >> > } > I changed the application like this. > > int mainfunc() > > { > > EXEC sql prepare s1 from "select count(*) from tb1 where col2=?"; > > for (;;) > { > > << some C code>> > > ret=subfunc(); > > << some C code >> > } > } > > int subfunc() > { > > << some C code>> > > EXEC SQL execute s1 into :v_col1 using :v_col2; > > if (SQLCODE) > { return 200; } > > << some C code >> > } > > The application compiled successful. but when It execute, it report some > errors, s2 is not being knowed by the subfunc() function. > > then I changed the application to the below. s1 is like a global variable. > > #include .... > > EXEC sql prepare s1 from "select count(*) from tb1 where col2=?"; > > int mainfunc() > > { > > for (;;) > { > > << some C code>> > > ret=subfunc(); > > << some C code >> > } > } > > int subfunc() > { > > << some C code>> > > EXEC SQL execute s1 into :v_col1 using :v_col2; > > if (SQLCODE) > { return 200; } > > << some C code >> > } > > When I complile the application ,it report the syntax error in the blow > line. > EXEC SQL execute s1 into :v_col1 using :v_col2; > > The routine subfunc() has more than 1000 code. and lots of application > using > such kind of application framework. so I don't want repliace subfunc() with > it's code definition,in the calling function. > > Do anybody know how to tuning suck kind of application? > thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93403e18730b704c6bfbdc9