connect to database from stored procedure

Hi everyone, i have a little doubt, i was wondering if is there a way to connect to a database from a stored procedure?? I was searching about the connect to 'dbname' statement and it seems allows multiple database connections at same time, but seems just this works in ESQLC. This is because, im thinking to advice to migrate a lot of batch programs in 4GL to stored procedure, but we have a lot of databases where these procedures have to do the same work. Thanks in advanced.

VERSION INFORMATION PLEASE! Please post your platform, IDS version, and 4Gl version information. Since a procedure executes within the server, connections are irrelevant. You can have multiple connections in an ESQL/C application, as you surmised, and by writing ESQL/C modules to manage the process, you can do the same in a 4GL application as well. Why would you move your 4GL into stored procedures? The programming logic available in 4GL is much more complete than what's available to SPL, even in 11.50 and except for procedures that are primarily processing SQL results, 4GL code will run faster! Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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, Jun 17, 2009 at 10:49 PM, LYNKZ MIKE <yellr@telecom.com.co> wrote: > Hi everyone, i have a little doubt, i was wondering if is there a way to > connect to a database from a stored procedure?? > > I was searching about the connect to 'dbname' statement and it seems allows > multiple database connections at same time, but seems just this works in > ESQLC. > > This is because, im thinking to advice to migrate a lot of batch programs > in > 4GL to stored procedure, but we have a lot of databases where these > procedures > have to do the same work. > > Thanks in advanced. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e6d27ed1a9a806046c96ae65

On Wed, Jun 17, 2009 at 19:49, LYNKZ MIKE <yellr@telecom.com.co> wrote: > Is there a way to connect to a database from a stored procedure?? > No. When you execute a stored procedure, you are already connected to a database. Having the SP connect to another database would be seriously weird. The CONNECT, DISCONNECT and SET CONNECTION statements are client-side only; they are also unpreparable. You can't prepare a CONNECT statement because PREPARE has to be done by a database server, and if you aren't connected, there isn't a server available to do the deed. SET CONNECTION isn't preparable because each connection is managed by the client, and is connected to a particular database, and again, there isn't a way for a database server to know which other databases the client is connected to. DISCONNECT is similarly non-preparable - only DISCONNECT CURRENT could be handled by a database server. > I was searching about the connect to 'dbname' statement and it seems allows > multiple database connections at same time, but seems just this works in > ESQLC. > Yes. > This is because, I'm thinking to advice to migrate a lot of batch programs > in > 4GL to stored procedure, but we have a lot of databases where these > procedures > have to do the same work. > This is tricky - AFAIK, there isn't a simple way to do it (other than the way you've implicitly rejected - of creating the same set of procedures in each database). I remember being very confused about how stored procedures work when you execute a procedure in database B while connected to database A when they (stored procedures) first came out about 20 years ago, now - in Informix OnLine version 5.00. I suppose in theory you could use dynamic SQL (available in IDS 11.50) to prepare statements that include the database name in the relevant DML statements, thus ending up executing statements like: UPDATE somedb@anotherserver:sometable SET somecolumn = hostvar; You cannot use host variables to supply the database and server names; you'd have to create a string containing the statement and then prepare and execute it, or use EXECUTE IMMEDIATE. I wouldn't recommend doing this, though. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. Rodney Dangerfield<http://www.brainyquote.com/quotes/authors/r/rodney_dangerfield.html>; - "What a dog I got, his favorite bone is in my arm." --0015174c1122095e41046c999988