PREPARE in SPL
User asked why SPL allows PREPARE statements but not EXECUTE on prepared statements (unlike ESQL/C). They wanted dynamic SQL with parameter binding to avoid repeated parsing overhead. Solutions provided: use DECLARE CURSOR on the prepared statement, then OPEN/FETCH/CLOSE with USING and INTO clauses. For single-row results from parameterized queries, cursors work but require different syntax than desired.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Please help clear up some confusion. I see that I can use the PREPARE statement in SPL, assigning a statement_id to the PREPAREd statement. This sounds great, as it allows me to construct a SQL statement with different table names depending on whatever logic I need. The problem is, it seems that I cannot then EXECUTE statement_id in SPL. That seems to be only available in ESQL/C. Why can I PREPARE a statement but not EXECUTE it? What is the point? How can I use dynamic SQL in SPL, other than the EXECUTE IMMEDIATE statement? I know EXECUTE IMMEDIATE will work, but the problems with this are that a) I have to keep the statements in text form inside variables for the duration of the procedure; b) I can't use INTO and USING clauses, so I have to rebuild the statement each time the WHERE clause changes; and c) the statement will be reevaluated (syntax check, parse, optimized) each time that the statement is executed, negating the benefit of a PREPAREd statement. If I could do the PREPARE / EXECUTE method, I could do: let summary_tbl = "jun2014_data_summary"; let detail_tbl = "jun2014_data_detail"; let tmp_sql_stmt = "SELECT a.col1, a.col2, .... FROM " || summary_tbl || " a where summary_key = ?"; prepare sel_summary from tmp_sql_stmt; let tmp_sql_stmt = "SELECT x.col1, x.col2 ... FROM " || detail_tbl || " x where detail_key = ?; prepare sel_detail from tmp_sql_stmt; . . . let sum_key = 1332; let dtl_key = 113904; execute sel_summary into p_sum_col1, p_sum_col2, ... using sum_key; execute sel_detail into p_dtl_col1, p_dtl_col2, ... using dtl_key; With EXECUTE IMMEDIATE, I have to do: let sum_key = 1332; let sql_sel_summary = "SELECT a.col1, a.col2, ... INTO p_sum_col1, p_sum_col2, ... FROM " || summary_tbl || " a WHERE summary_key = " || sum_key; let dtl_key = 113904; let sel_sel_detail = "SELECT x.col1, x.col2 ... INTO p_dtl_col1, p_dtl_col2, ... FROM " || detail_tbl || " x where detail_key = " || dtl_key; execute immediate sql_sel_summary; execute immediate sql_sel_detail; OK, yes, I could make strings that contain sub-parts of the queries, and string them together as: execute immediate sel_summary_cols || sel_summary_into || sel_summary_where; but still, this doesn't get around the problem of the repetitive syntax checking, parsing, optimizing, freeing. Surely there has to be a better way to to dynamic SQL inside of SPL. BTW, this is IDS 11.70 on Solaris 10.
You can't EXECUTE a statement ID but you can DECLARE a cursor on the ID then OPEN then FETCH ... USING ... INTO ... and CLOSE the open cursor or use the cursor in a LOOP, WHILE, FOR, or FOREACH loop. Using your example: let summary_tbl = "jun2014_data_summary"; let detail_tbl = "jun2014_data_detail"; let tmp_sql_stmt = "SELECT a.col1, a.col2, .... FROM " || summary_tbl || " a where summary_key = ?"; prepare sel_summary from tmp_sql_stmt; let tmp_sql_stmt = "SELECT x.col1, x.col2 ... FROM " || detail_tbl || " x where detail_key = ?; prepare sel_detail from tmp_sql_stmt; .. .. .. let sum_key = 1332; let dtl_key = 113904; DECLARE sel_sum_curs CURSOR FOR sel_summary; DECLARE sel_det_curs CURSOR FOR sel_detail; OPEN sel_sum_curs USING sum_key; FETCH sel_sum_curs INTO p_sum_col1, p_sum_col2, ...; CLOSE sel_cum_curs; OPEN sel_det_curs USING dtl_key; FETCH sel_det_curs INTO p_dtl_col1, p_dtl_col2, ...; CLOSE sel_det_curs; ... Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Fri, Jun 6, 2014 at 11:10 AM, DAY BRAD <bday@sent.com> wrote: > Please help clear up some confusion. I see that I can use the PREPARE > statement in SPL, assigning a statement_id to the PREPAREd statement. This > sounds great, as it allows me to construct a SQL statement with different > table names depending on whatever logic I need. > > The problem is, it seems that I cannot then EXECUTE statement_id in SPL. > That > seems to be only available in ESQL/C. > > Why can I PREPARE a statement but not EXECUTE it? What is the point? > > How can I use dynamic SQL in SPL, other than the EXECUTE IMMEDIATE > statement? > I know EXECUTE IMMEDIATE will work, but the problems with this are that a) > I > have to keep the statements in text form inside variables for the duration > of > the procedure; b) I can't use INTO and USING clauses, so I have to rebuild > the > statement each time the WHERE clause changes; and c) the statement will be > reevaluated (syntax check, parse, optimized) each time that the statement > is > executed, negating the benefit of a PREPAREd statement. > > If I could do the PREPARE / EXECUTE method, I could do: > > let summary_tbl = "jun2014_data_summary"; > let detail_tbl = "jun2014_data_detail"; > let tmp_sql_stmt = "SELECT a.col1, a.col2, .... FROM " || summary_tbl || " > a > where summary_key = ?"; > prepare sel_summary from tmp_sql_stmt; > let tmp_sql_stmt = "SELECT x.col1, x.col2 ... FROM " || detail_tbl || " x > where detail_key = ?; > prepare sel_detail from tmp_sql_stmt; > .. > .. > .. > let sum_key = 1332; > let dtl_key = 113904; > execute sel_summary into p_sum_col1, p_sum_col2, ... using sum_key; > execute sel_detail into p_dtl_col1, p_dtl_col2, ... using dtl_key; > > With EXECUTE IMMEDIATE, I have to do: > > let sum_key = 1332; > let sql_sel_summary = "SELECT a.col1, a.col2, ... INTO p_sum_col1, > p_sum_col2, > .... FROM " || summary_tbl || " a WHERE summary_key = " || sum_key; > let dtl_key = 113904; > let sel_sel_detail = "SELECT x.col1, x.col2 ... INTO p_dtl_col1, > p_dtl_col2, > .... FROM " || detail_tbl || " x where detail_key = " || dtl_key; > execute immediate sql_sel_summary; > execute immediate sql_sel_detail; > > OK, yes, I could make strings that contain sub-parts of the queries, and > string them together as: > > execute immediate sel_summary_cols || sel_summary_into || > sel_summary_where; > > but still, this doesn't get around the problem of the repetitive syntax > checking, parsing, optimizing, freeing. Surely there has to be a better > way to > to dynamic SQL inside of SPL. > > BTW, this is IDS 11.70 on Solaris 10. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c34aa8088d0704fb2c7e4d
AFAIK you need to use EXECUTE IMMEDIATE sqlstr OR PREPARE DECLARE OPEN FETCH CLOSE In SPL > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAY > BRAD > Sent: Friday, June 06, 2014 10:11 AM > To: ids@iiug.org > Subject: PREPARE in SPL [33176] > > Please help clear up some confusion. I see that I can use the PREPARE > statement in SPL, assigning a statement_id to the PREPAREd statement. This > sounds great, as it allows me to construct a SQL statement with different > table names depending on whatever logic I need. > > The problem is, it seems that I cannot then EXECUTE statement_id in SPL. > That > seems to be only available in ESQL/C. > > Why can I PREPARE a statement but not EXECUTE it? What is the point? > > How can I use dynamic SQL in SPL, other than the EXECUTE IMMEDIATE > statement? > I know EXECUTE IMMEDIATE will work, but the problems with this are that a) > I > have to keep the statements in text form inside variables for the duration of > the procedure; b) I can't use INTO and USING clauses, so I have to rebuild the > statement each time the WHERE clause changes; and c) the statement will be > reevaluated (syntax check, parse, optimized) each time that the statement is > executed, negating the benefit of a PREPAREd statement. > > If I could do the PREPARE / EXECUTE method, I could do: > > let summary_tbl = "jun2014_data_summary"; > let detail_tbl = "jun2014_data_detail"; > let tmp_sql_stmt = "SELECT a.col1, a.col2, .... FROM " || summary_tbl || " a > where summary_key = ?"; > prepare sel_summary from tmp_sql_stmt; > let tmp_sql_stmt = "SELECT x.col1, x.col2 ... FROM " || detail_tbl || " x > where detail_key = ?; > prepare sel_detail from tmp_sql_stmt; > .. > .. > .. > let sum_key = 1332; > let dtl_key = 113904; > execute sel_summary into p_sum_col1, p_sum_col2, ... using sum_key; > execute sel_detail into p_dtl_col1, p_dtl_col2, ... using dtl_key; > > With EXECUTE IMMEDIATE, I have to do: > > let sum_key = 1332; > let sql_sel_summary = "SELECT a.col1, a.col2, ... INTO p_sum_col1, > p_sum_col2, > .... FROM " || summary_tbl || " a WHERE summary_key = " || sum_key; > let dtl_key = 113904; > let sel_sel_detail = "SELECT x.col1, x.col2 ... INTO p_dtl_col1, p_dtl_col2, > .... FROM " || detail_tbl || " x where detail_key = " || dtl_key; > execute immediate sql_sel_summary; > execute immediate sql_sel_detail; > > OK, yes, I could make strings that contain sub-parts of the queries, and > string them together as: > > execute immediate sel_summary_cols || sel_summary_into || > sel_summary_where; > > but still, this doesn't get around the problem of the repetitive syntax > checking, parsing, optimizing, freeing. Surely there has to be a better way to > to dynamic SQL inside of SPL. > > BTW, this is IDS 11.70 on Solaris 10. > > > ********************************************************** > ********************* > Forum Note: Use "Reply" to post a response in the discussion forum.
Art and Paul, Thanks. I knew I would have to use the DECLARE/OPEN/FETCH for multi-row results, but I've always used the EXECUTE in cases where I was retrieving via a primary key and knew there would be only a single row returned. Guess I'll have to make all of the SELECTs into cursors. But here's a twist - we also want to INSERT into a table, and that table will vary. So I wanted something like: let fy_summary = "FY2014_checks"; let tmp_sql_stmt = "INSERT INTO " || fy_summary || " (col1, col2 ... ) VALUES (?, ?, ...)"; prepare fye_insert from tmp_sql_stmt; execute fye_insert using fy_col1, fy_col2 ...; Can I do this via cursors as well?
Within SPL I tend to build a full string and execute immediate - just find it easier. Cheers Paul > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAY > BRAD > Sent: Friday, June 06, 2014 11:39 AM > To: ids@iiug.org > Subject: Re: RE: PREPARE in SPL [33179] > > Art and Paul, > > Thanks. I knew I would have to use the DECLARE/OPEN/FETCH for multi-row > results, but I've always used the EXECUTE in cases where I was retrieving via > a primary key and knew there would be only a single row returned. Guess I'll > have to make all of the SELECTs into cursors. > > But here's a twist - we also want to INSERT into a table, and that table will > vary. So I wanted something like: > > let fy_summary = "FY2014_checks"; > let tmp_sql_stmt = "INSERT INTO " || fy_summary || " (col1, col2 ... ) > VALUES > (?, ?, ...)"; > prepare fye_insert from tmp_sql_stmt; > execute fye_insert using fy_col1, fy_col2 ...; > > Can I do this via cursors as well? > > > ********************************************************** > ********************* > Forum Note: Use "Reply" to post a response in the discussion forum.
In ESQL you would use replaceable parameters in the VALUES clause of the INSERT statement, prepare it, declare a cursor, then use the PUT ... USING statement to insert records using the cursor. I don't remember if they extended the PUT to SPL routines or not. Can't test it now and the docs aren't clear. You can always write that module in ESQL/C and make it callable from SPL by compiling it into a shared library and creating a procedure name for it. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Fri, Jun 6, 2014 at 12:39 PM, DAY BRAD <bday@sent.com> wrote: > Art and Paul, > > Thanks. I knew I would have to use the DECLARE/OPEN/FETCH for multi-row > results, but I've always used the EXECUTE in cases where I was retrieving > via > a primary key and knew there would be only a single row returned. Guess > I'll > have to make all of the SELECTs into cursors. > > But here's a twist - we also want to INSERT into a table, and that table > will > vary. So I wanted something like: > > let fy_summary = "FY2014_checks"; > let tmp_sql_stmt = "INSERT INTO " || fy_summary || " (col1, col2 ... ) > VALUES > (?, ?, ...)"; > prepare fye_insert from tmp_sql_stmt; > execute fye_insert using fy_col1, fy_col2 ...; > > Can I do this via cursors as well? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c352daf17e6604fb2f1950