spl and dynamic sql and cursors

Can someone point me to howto use dynamic sql, the manual says Guide to sql syntax Documentation for IBM Informix Dynamic Server Enterprise and Workgroup Editions, v11.50.xC5 page 359 says: CREATE FUNCTION lenteDEFINE first, last VARCHAR(30); . . . DATABASE stores_demo;LET first = "select * from state"; LET lsst = "where code < ?"; PREPARE stmt_1 FROM first || last; DECLARE cursor_1 FOR stmt_1; OPEN cursor_1 . . . CLOSE cursor_1; FREE cursor_1; FREE stmt_1; ... END FUNCTION; the problem is that i can not DECLARE cursor_1 FOR stmt_1; CREATE procedure "informix".lente() DEFINE first, last VARCHAR(30); LET first = 'select * from customer '; LET last = 'where customer_num > 0'; PREPARE stmt_1 FROM first || last; DECLARE cursor_1 FOR stmt_1; END procedure; it comes back with a syntax error.... IBM Informix Dynamic Server Version 11.70.UC4IE Software Serial Number AAA#B000000 Comments are welcome Superboer

Just looking at it quickly it looks like you are missing a space between the 2 strings you are concatenating. I would think the Prepare would trap that. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Superboer Sent: Tuesday, November 29, 2011 11:11 AM To: informix-list@iiug.org Subject: spl and dynamic sql and cursors Can someone point me to howto use dynamic sql, the manual says Guide to sql syntax Documentation for IBM Informix Dynamic Server Enterprise and Workgroup Editions, v11.50.xC5 page 359 says: CREATE FUNCTION lenteDEFINE first, last VARCHAR(30); . . . DATABASE stores_demo;LET first = "select * from state"; LET lsst = "where code < ?"; PREPARE stmt_1 FROM first || last; DECLARE cursor_1 FOR stmt_1; OPEN cursor_1 . . . CLOSE cursor_1; FREE cursor_1; FREE stmt_1; ... END FUNCTION; the problem is that i can not DECLARE cursor_1 FOR stmt_1; CREATE procedure "informix".lente() DEFINE first, last VARCHAR(30); LET first = 'select * from customer '; LET last = 'where customer_num > 0'; PREPARE stmt_1 FROM first || last; DECLARE cursor_1 FOR stmt_1; END procedure; it comes back with a syntax error.... IBM Informix Dynamic Server Version 11.70.UC4IE Software Serial Number AAA#B000000 Comments are welcome Superboer _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

Hello David, thanks for the responce; the last one has a space if i put the thing into one var then the problem is still there. Superboer. On 29 Nov., 18:19, "Link, David A" <DAL...@west.com> wrote: > Just looking at it quickly it looks like you are missing a space between the 2 strings you are concatenating.  I would think the Prepare would trap that. > > -----Original Message----- > From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org] On Behalf Of Superboer > Sent: Tuesday, November 29, 2011 11:11 AM > To: informix-l...@iiug.org > Subject: spl and dynamic sql and cursors > > Can someone point me to howto use dynamic sql, the manual says > > Guide to sql syntax > Documentation for  IBM Informix Dynamic Server Enterprise and Workgroup Editions, v11.50.xC5 page 359 says: > > CREATE FUNCTION lente> DEFINE first, last VARCHAR(30); > . . . > DATABASE stores_demo;> LET first = "select * from state"; > LET lsst = "where code < ?"; > PREPARE stmt_1 FROM first || last; > DECLARE cursor_1 FOR stmt_1; > OPEN cursor_1 > . . . > CLOSE cursor_1; > FREE cursor_1; > FREE stmt_1; > ... > END FUNCTION; > > the problem is that i can not DECLARE cursor_1 FOR stmt_1; > > CREATE procedure "informix".lente() > DEFINE first, last VARCHAR(30); > LET first = 'select * from customer '; LET last = 'where customer_num > > 0'; > PREPARE stmt_1 FROM first || last; > DECLARE cursor_1 FOR stmt_1; > > END procedure; > > it comes back with a syntax error.... > > IBM Informix Dynamic Server Version 11.70.UC4IE Software Serial Number > AAA#B000000 > > Comments are welcome > > Superboer > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list

On Nov 29, 11:11 am, Superboer <superbo...@t-online.de> wrote: > Can someone point me to howto use dynamic sql, the manual says > > Guide to sql syntax > Documentation for  IBM Informix Dynamic Server Enterprise and > Workgroup Editions, v11.50.xC5 > page 359 says: > > CREATE FUNCTION lente> DEFINE first, last VARCHAR(30); > . . . > DATABASE stores_demo;> LET first = "select * from state"; > LET lsst = "where code < ?"; > PREPARE stmt_1 FROM first || last; > DECLARE cursor_1 FOR stmt_1; > OPEN cursor_1 > . . . > CLOSE cursor_1; > FREE cursor_1; > FREE stmt_1; > ... > END FUNCTION; > > the problem is that i can not DECLARE cursor_1 FOR stmt_1; > > CREATE procedure "informix".lente() > DEFINE first, last VARCHAR(30); > LET first = 'select * from customer '; LET last = 'where customer_num> 0'; > > PREPARE stmt_1 FROM first || last; > DECLARE cursor_1 FOR stmt_1; > > END procedure; > > it comes back with a syntax error.... > > IBM Informix Dynamic Server Version 11.70.UC4IE Software Serial Number > AAA#B000000 > > Comments are welcome > > Superboer It looks to be a missing key word in the example...you just need to change this line: "DECLARE cursor_1 FOR stmt_1" to "DECLARE cursor_1 cursor FOR stmt_1" Jacques Renaut IBM Informix Advanced Support APD Team

Hi, besides the fact that this example in the manual has more than one error, this specific one is a missing keyword: DECLARE cursor_1 CURSOR for stmt_1; will do it. The keyword "cursor" is missing ... Regards Dirk Am 29.11.2011 23:08, schrieb Superboer: > Hello David, > > > thanks for the responce; the last one has a space > if i put the thing into one var then the problem is still there. > > > Superboer. > > > On 29 Nov., 18:19, "Link, David A"<DAL...@west.com> wrote: >> Just looking at it quickly it looks like you are missing a space between the 2 strings you are concatenating. I would think the Prepare would trap that. >> >> -----Original Message----- >> From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org] On Behalf Of Superboer >> Sent: Tuesday, November 29, 2011 11:11 AM >> To: informix-l...@iiug.org >> Subject: spl and dynamic sql and cursors >> >> Can someone point me to howto use dynamic sql, the manual says >> >> Guide to sql syntax >> Documentation for IBM Informix Dynamic Server Enterprise and Workgroup Editions, v11.50.xC5 page 359 says: >> >> CREATE FUNCTION lente>> DEFINE first, last VARCHAR(30); >> . . . >> DATABASE stores_demo;>> LET first = "select * from state"; >> LET lsst = "where code< ?"; >> PREPARE stmt_1 FROM first || last; >> DECLARE cursor_1 FOR stmt_1; >> OPEN cursor_1 >> . . . >> CLOSE cursor_1; >> FREE cursor_1; >> FREE stmt_1; >> ... >> END FUNCTION; >> >> the problem is that i can not DECLARE cursor_1 FOR stmt_1; >> >> CREATE procedure "informix".lente() >> DEFINE first, last VARCHAR(30); >> LET first = 'select * from customer '; LET last = 'where customer_num >>> 0'; >> PREPARE stmt_1 FROM first || last; >> DECLARE cursor_1 FOR stmt_1; >> >> END procedure; >> >> it comes back with a syntax error.... >> >> IBM Informix Dynamic Server Version 11.70.UC4IE Software Serial Number >> AAA#B000000 >> >> Comments are welcome >> >> Superboer >> _______________________________________________ >> Informix-list mailing list >> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list >

Hello All, Thanks!!!!!!!!! works now!!!!!! Superboer. On 29 nov, 23:48, "Dirk B." <toho_NOSP...@myrealbox.com> wrote: > Hi, > > besides the fact that this example in the manual has more than one > error, this specific one is a missing keyword: > > DECLARE cursor_1 CURSOR for stmt_1; > > will do it. The keyword "cursor" is missing ... > > Regards > > Dirk > > Am 29.11.2011 23:08, schrieb Superboer: > > > Hello David, > > > thanks for the responce; the last one has a space > > if i put the thing into one var then the problem is still there. > > > Superboer. > > > On 29 Nov., 18:19, "Link, David A"<DAL...@west.com>  wrote: > >> Just looking at it quickly it looks like you are missing a space between the 2 strings you are concatenating.  I would think the Prepare would trap that. > > >> -----Original Message----- > >> From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org] On Behalf Of Superboer > >> Sent: Tuesday, November 29, 2011 11:11 AM > >> To: informix-l...@iiug.org > >> Subject: spl and dynamic sql and cursors > > >> Can someone point me to howto use dynamic sql, the manual says > > >> Guide to sql syntax > >> Documentation for  IBM Informix Dynamic Server Enterprise and Workgroup Editions, v11.50.xC5 page 359 says: > > >> CREATE FUNCTION lente> >> DEFINE first, last VARCHAR(30); > >> . . . > >> DATABASE stores_demo;> >> LET first = "select * from state"; > >> LET lsst = "where code<  ?"; > >> PREPARE stmt_1 FROM first || last; > >> DECLARE cursor_1 FOR stmt_1; > >> OPEN cursor_1 > >> . . . > >> CLOSE cursor_1; > >> FREE cursor_1; > >> FREE stmt_1; > >> ... > >> END FUNCTION; > > >> the problem is that i can not DECLARE cursor_1 FOR stmt_1; > > >> CREATE procedure "informix".lente() > >> DEFINE first, last VARCHAR(30); > >> LET first = 'select * from customer '; LET last = 'where customer_num > >>> 0'; > >> PREPARE stmt_1 FROM first || last; > >> DECLARE cursor_1 FOR stmt_1; > > >> END procedure; > > >> it comes back with a syntax error.... > > >> IBM Informix Dynamic Server Version 11.70.UC4IE Software Serial Number > >> AAA#B000000 > > >> Comments are welcome > > >> Superboer > >> _______________________________________________ > >> Informix-list mailing list > >> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list