Dynamic SQL Informix 9.4
Hi,
informix 9.4 os - Sun solaris
I want to take the table name as a input parameter to a stored procedure
where it will prepare a dynamic sql.
it is giving error as "201: A syntax error has occurred."
as informix 9.4 does not supports PREPARE statment.
Please help how do I write dynamic sql or any alternative
-------------------------------------------------
I am facing problem while creating procedure.
I am getting error :
Error:
Database selected.
201: A syntax error has occurred.
Error in line 10Near character position 0
Database closed.
---------------------------------------------------
I am trying to run like this
$dbaccess abps2 <>.sql
PROCEDURE:
drop procedure rc1;
CREATE PROCEDURE rc1(t_cc char(16))DEFINE var1 varchar(12);
DEFINE cust_qry varchar(200) ;
DEFINE p_code INTEGER;
LET var1 = "CUST_DISC";
LET cust_qry = "SELECT " || var1 || "_CODE FROM " || var1 || " where " || var1
||"_NUMBER = ? ";
PREPARE stmt_id FROM cust_qry;
DECLARE cust_cur cursor FOR stmt_id;
OPEN cust_cur USING t_cc;
while (1 = 1)
FETCH cust_cur INTO p_code;
IF (SQLCODE != 100) THEN
RETURN p_cust_code WITH RESUME;
ELSE
EXIT;
END IF
END WHILE
CLOSE cust_cur;
FREE cust_cur ;
FREE stmt_id ;
END PROCEDURE;
execute procedure rc1("6011000430348098") ;
R C wrote: > Hi, > informix 9.4 os - Sun solaris > I want to take the table name as a input parameter to a stored procedure > where it will prepare a dynamic sql. > > it is giving error as "201: A syntax error has occurred." > > as informix 9.4 does not supports PREPARE statment. > > Please help how do I write dynamic sql or any alternative You need the EXEC "bladelet" or upgrade to 11.50. And very good luck with finding Exec, I'm afraid. You do know that 9.40 is way out of support, don't you? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Dynamic SQL in procedures was introduced only in 11.50.
Regards.
On Tue, Oct 27, 2009 at 1:46 PM, R C <rahul.chheda@amdocs.com> wrote:
> Hi,
> informix 9.4 os - Sun solaris
> I want to take the table name as a input parameter to a stored procedure
> where it will prepare a dynamic sql.
>
> it is giving error as "201: A syntax error has occurred."
>
> as informix 9.4 does not supports PREPARE statment.
>
> Please help how do I write dynamic sql or any alternative
>
> -------------------------------------------------
> I am facing problem while creating procedure.
>
> I am getting error :
>
> Error:
>
> Database selected.
>
> 201: A syntax error has occurred.
> Error in line 10> Near character position 0
>
> Database closed.
>
> ---------------------------------------------------
> I am trying to run like this
>
> $dbaccess abps2 <>.sql
>
> PROCEDURE:
>
> drop procedure rc1;
> CREATE PROCEDURE rc1(t_cc char(16))> DEFINE var1 varchar(12);
> DEFINE cust_qry varchar(200) ;
> DEFINE p_code INTEGER;
> LET var1 = "CUST_DISC";
>
> LET cust_qry = "SELECT " || var1 || "_CODE FROM " || var1 || " where " ||
> var1
> ||"_NUMBER = ? ";
>
> PREPARE stmt_id FROM cust_qry;
>
> DECLARE cust_cur cursor FOR stmt_id;
>
> OPEN cust_cur USING t_cc;
>
> while (1 = 1)
>
> FETCH cust_cur INTO p_code;
>
> IF (SQLCODE != 100) THEN
>
> RETURN p_cust_code WITH RESUME;
>
> ELSE
>
> EXIT;
>
> END IF
>
> END WHILE
>
> CLOSE cust_cur;
>
> FREE cust_cur ;
>
> FREE stmt_id ;
>
> END PROCEDURE;
>
> execute procedure rc1("6011000430348098") ;>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--000e0cd754eab68ad80476fcf166
I never used: http://www.iiug.org/software/index_ORDBMS.html exec_sql_udr Bladelet that provides dynamic SQL functionality within an SPL procedure [C, ORDBMS, SPL, SQL]