Passing a variable in a stored procedure
Luke wanted a stored procedure to take a table name as a parameter and use it in a SELECT (FOREACH SELECT ROWID FROM table_name). Replies explained you can't substitute an object name directly: Art Kagel showed how to build the statement as a string and use PREPARE plus DECLARE/OPEN/FETCH cursor (FOREACH can't be used with dynamic SQL), available in IDS 11.x; Paul Watson noted the ExecIt datablade for 9.x/10.0. Since Luke's server was 7.31, neither applies, so the consensus was to do it from an external program such as Perl DBI, ESQL/C, Ruby, or SQSL. Luke went with Perl.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm trying to pass a variable in a stored procedure without any luck.
This is what I'm trying to do:
CREATE PROCEDURE update_serial(table_name char(128))
DEFINE v_rowid INT;
FOREACH SELECT ROWID INTO v_rowid FROM table_name
...
How do I pass "table_name" to my SELECT statement?
Thanks!
//Luke
You need to prepare the statement from a string
Cheers
Paul
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LUKE
SIMMONS
Sent: Wednesday, April 18, 2012 8:22 AM
To: ids@iiug.org
Subject: Passing a variable in a stored procedure [26736]
I'm trying to pass a variable in a stored procedure without any luck.
This is what I'm trying to do:
CREATE PROCEDURE update_serial(table_name char(128))
DEFINE v_rowid INT;
FOREACH SELECT ROWID INTO v_rowid FROM table_name
....
How do I pass "table_name" to my SELECT statement?
Thanks!
//Luke
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
You cannot do that directly like that. However, IFF you have Informix
version 11.xx (you really always post your version and platform information
so we don't have to do this "If you have the right version..." dance) you
can use dynamic SQL to build the SELECT statement from your argument:
CREATE PROCEDURE update_serial(table_name char(128))
DEFINE v_rowid INT;
DEFINE stmt lvarchar;
LET stmt = 'SELECT ROWID FROM ' || table_name;
PREPARE s_stmt FROM stmt;
DECLARE c_stmt CURSOR FOR s_stmt;
OPEN c_stmt;
LOOP
FETCH c_stmt INTO v_rowid;
IF (sqlcode = 100) THEN
EXIT LOOP;
END IF
....
END LOOP;
CLOSE c_stmt;
FREE c_stmt;
FREE s_stmt;
...
END PROCEDURE;
You cannot use FOREACH with a dynamic SQL statement, thus the cursor
declare, open, fetch, etc.
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, Apr 18, 2012 at 9:22 AM, LUKE SIMMONS <luke.simmons@vgregion.se>wrote:
> I'm trying to pass a variable in a stored procedure without any luck.
>
> This is what I'm trying to do:
>
> CREATE PROCEDURE update_serial(table_name char(128))>
> DEFINE v_rowid INT;
>
> FOREACH SELECT ROWID INTO v_rowid FROM table_name
> ....
>
> How do I pass "table_name" to my SELECT statement?
>
> Thanks!
>
> //Luke
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8c0c8ecf1604bdf43426
If you don't have 11.xx then you can use the ExecIt datablade to same thing
Cheers
Paul
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Wednesday, April 18, 2012 8:41 AM
To: ids@iiug.org
Subject: Re: Passing a variable in a stored procedure [26738]
You cannot do that directly like that. However, IFF you have Informix
version 11.xx (you really always post your version and platform information
so we don't have to do this "If you have the right version..." dance) you
can use dynamic SQL to build the SELECT statement from your argument:
CREATE PROCEDURE update_serial(table_name char(128))
DEFINE v_rowid INT;
DEFINE stmt lvarchar;
LET stmt = 'SELECT ROWID FROM ' || table_name;
PREPARE s_stmt FROM stmt;
DECLARE c_stmt CURSOR FOR s_stmt;
OPEN c_stmt;
LOOP
FETCH c_stmt INTO v_rowid;
IF (sqlcode = 100) THEN
EXIT LOOP;
END IF
.....
END LOOP;
CLOSE c_stmt;
FREE c_stmt;
FREE s_stmt;
....
END PROCEDURE;
You cannot use FOREACH with a dynamic SQL statement, thus the cursor
declare, open, fetch, etc.
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, Apr 18, 2012 at 9:22 AM, LUKE SIMMONS
<luke.simmons@vgregion.se>wrote:
> I'm trying to pass a variable in a stored procedure without any luck.
>
> This is what I'm trying to do:
>
> CREATE PROCEDURE update_serial(table_name char(128))>
> DEFINE v_rowid INT;
>
> FOREACH SELECT ROWID INTO v_rowid FROM table_name
> ....
>
> How do I pass "table_name" to my SELECT statement?
>
> Thanks!
>
> //Luke
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8c0c8ecf1604bdf43426
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
But different syntax. It uses function calls rather than in-code
statements. Also, Exec datablade is only for 9.xx and 10.00 that won't
help users of 7.xx or earlier
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, Apr 18, 2012 at 9:47 AM, Paul Watson <paul@oninit.com> wrote:
> If you don't have 11.xx then you can use the ExecIt datablade to same thing
>
> Cheers
> Paul
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Wednesday, April 18, 2012 8:41 AM
> To: ids@iiug.org
> Subject: Re: Passing a variable in a stored procedure [26738]
>
> You cannot do that directly like that. However, IFF you have Informix
> version 11.xx (you really always post your version and platform information
> so we don't have to do this "If you have the right version..." dance) you
> can use dynamic SQL to build the SELECT statement from your argument:
>
> CREATE PROCEDURE update_serial(table_name char(128))>
> DEFINE v_rowid INT;
> DEFINE stmt lvarchar;
>
> LET stmt = 'SELECT ROWID FROM ' || table_name;
>
> PREPARE s_stmt FROM stmt;
>
> DECLARE c_stmt CURSOR FOR s_stmt;
>
> OPEN c_stmt;
>
> LOOP
>
> FETCH c_stmt INTO v_rowid;
>
> IF (sqlcode = 100) THEN
>
> EXIT LOOP;
>
> END IF
>
> ......
>
> END LOOP;
> CLOSE c_stmt;
> FREE c_stmt;
> FREE s_stmt;
> .....
> END PROCEDURE;
>
> You cannot use FOREACH with a dynamic SQL statement, thus the cursor
> declare, open, fetch, etc.
>
> 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, Apr 18, 2012 at 9:22 AM, LUKE SIMMONS
> <luke.simmons@vgregion.se>wrote:
>
> > I'm trying to pass a variable in a stored procedure without any luck.
> >
> > This is what I'm trying to do:
> >
> > CREATE PROCEDURE update_serial(table_name char(128))> >
> > DEFINE v_rowid INT;
> >
> > FOREACH SELECT ROWID INTO v_rowid FROM table_name
> > ....
> >
> > How do I pass "table_name" to my SELECT statement?
> >
> > Thanks!
> >
> > //Luke
> >
> >
> >
> >
>
> ****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --90e6ba6e8c0c8ecf1604bdf43426
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f13ec64b7256904bdf454d3
I apologize. This is for 7.31. Our last database to migrate!! I'm assuming from what you've said there's no way to do this in 7.31?
Nope Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LUKE SIMMONS Sent: Wednesday, April 18, 2012 8:54 AM To: ids@iiug.org Subject: Re: Passing a variable in a stored procedure [26741] I apologize. This is for 7.31. Our last database to migrate!! I'm assuming from what you've said there's no way to do this in 7.31? **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Fortunately Perl comes in handy for these types of jobs :) Thanks a lot! I'm absolutely mesmerised by how fast these responses came in.
The only way, beyond doing it in a external program using Perl-DBD/DBI, ESQL/C (my personal favorite), or Ruby there is no practical way, no. 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, Apr 18, 2012 at 9:54 AM, LUKE SIMMONS <luke.simmons@vgregion.se>wrote: > I apologize. This is for 7.31. Our last database to migrate!! > > I'm assuming from what you've said there's no way to do this in 7.31? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d592dd98404bdf4ad09
On 18/04/12 15:14, Art Kagel wrote: > The only way, beyond doing it in a external program using Perl-DBD/DBI, > ESQL/C (my personal favorite), or Ruby there is no practical way, no. and SQSL (see sig)! > > 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, Apr 18, 2012 at 9:54 AM, LUKE SIMMONS<luke.simmons@vgregion.se>wrote: > >> I apologize. This is for 7.31. Our last database to migrate!! >> >> I'm assuming from what you've said there's no way to do this in 7.31? >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --14dae9340d592dd98404bdf4ad09 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm