fetch statement with shell script

Hi we use IDS 11.5 on RHEL 5.3 environment. we have a requirement to use fetch command inside Shell scipt but we need to have a persistant connection with the database in order to continue and fetch the results one row at a time and copy them to local variables inside a shell script. Is there any way to open a persistant database connection without having to use dbaccess oas <<! followed by command to be executed ! each and every time, which is supposed to close the session with the database once the command is executed. I want the datbabase to be open throughout the entire execution of script without having to open the session each and every time. Is there a way to do this? Thanks in advance for your support

2009/3/25 LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in>: > Hi > > we use IDS 11.5 on RHEL 5.3 environment. > > we have a requirement to use fetch command inside Shell scipt but we need to > have a persistant connection with the database in order to continue and fetch > the results one row at a time and copy them to local variables inside a shell > script. > > Is there any way to open a persistant database connection without having to > use > > dbaccess oas <<! > followed by command to be executed > ! > > each and every time, which is supposed to close the session with the database > once the command is executed. > > I want the datbabase to be open throughout the entire execution of script > without having to open the session each and every time. Is there a way to do > this? > > Thanks in advance for your support > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > You are trying to do something from an operating system that can only be sensibly done from a programming language, learn C or perl or PHP or ... However you could try:- dbaccess oas <<! unload to afile select ...! cat afile | while read param1 param2 param2 do something done Keith

Not from any standard shell, no. The closest you can get would be to use Jonathan Leffler's sqlcmd in 'server' mode. In that mode sqlcmd remains open until you tell it to exit. It takes input from a named pipe and writes its output back to a pipe. You COULD then loop on a read of the pipe to get each row written out by sqlcmd. You can also use Perl with the Informix DBD/DBI module to connect to the database, open a cursor on a SELECT statement, and fetch the data one row at a time. It's far more intuitive than using a shell. Perl is also interpreted and simple to use. I wonder why not just write an ESQL/C or ODBC application in 'C'? OK, this is the REAL question: What is the task that is requiring this? It is always best to post the ultimate requirement and ask us how to best fulfill that requirement than to post some proposed implementation someone came up with but couldn't make work and ask us how to make it work! 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, Mar 25, 2009 at 11:06 AM, LAKSHMI DEVI PALANISSAMY < lakshmidevip@hcl.in> wrote: > Hi > > we use IDS 11.5 on RHEL 5.3 environment. > > we have a requirement to use fetch command inside Shell scipt but we need > to > have a persistant connection with the database in order to continue and > fetch > the results one row at a time and copy them to local variables inside a > shell > script. > > Is there any way to open a persistant database connection without having to > use > > dbaccess oas <<! > followed by command to be executed > ! > > each and every time, which is supposed to close the session with the > database > once the command is executed. > > I want the datbabase to be open throughout the entire execution of script > without having to open the session each and every time. Is there a way to > do > this? > > Thanks in advance for your support > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016364ee1388392fc0465f3a2af

LAKSHMI DEVI PALANISSAMY wrote: > Hi > > we use IDS 11.5 on RHEL 5.3 environment. > > we have a requirement to use fetch command inside Shell scipt but we need to > have a persistant connection with the database in order to continue and fetch > the results one row at a time and copy them to local variables inside a shell > script. > > Is there any way to open a persistant database connection without having to > use > > dbaccess oas <<! > followed by command to be executed > ! > > each and every time, which is supposed to close the session with the database > once the command is executed. > > I want the datbabase to be open throughout the entire execution of script > without having to open the session each and every time. Is there a way to do > this? > > Thanks in advance for your support Please have a look at sqsl (see sig) it is essentially a scripting language embedded in a dbaccess like application. the only thing that you have to change is that you script inside sqsl as opposed to within the shell process -- 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

Hi We need to get the SQL EXIT code for each one of the SQL queries and validate the same before proceeding to execute next SQL QUERY. If there is any failure in executing the SQL QUERY, then we need to quit the application. Otherwise we need to continue executing the next SQL QUERY in sequence. To illustrate our requirement better, here is an example Eg. 1: We need to execute SQL QUERY to lock one of the tables present in our database using lock table <table_name> in exclusive mode perform some operations on the table and then unlock the table using unlock table <table_name> All these transactions to the database should happen within single session before we close the database. We require this to be implemented using shell script instead of using ESQL/C or PERL. Let us know the best possible solution if any exists

Try using a stored procedure which is executed from within your shell script. In the stored procedure you can trap errors and handle accordingly. Anthony -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LAKSHMI DEVI PALANISSAMY Sent: Wednesday, March 25, 2009 12:36 PM To: ids@iiug.org Subject: Re: fetch statement with shell script [15335] Hi We need to get the SQL EXIT code for each one of the SQL queries and validate the same before proceeding to execute next SQL QUERY. If there is any failure in executing the SQL QUERY, then we need to quit the application. Otherwise we need to continue executing the next SQL QUERY in sequence. To illustrate our requirement better, here is an example Eg. 1: We need to execute SQL QUERY to lock one of the tables present in our database using lock table <table_name> in exclusive mode perform some operations on the table and then unlock the table using unlock table <table_name> All these transactions to the database should happen within single session before we close the database. We require this to be implemented using shell script instead of using ESQL/C or PERL. Let us know the best possible solution if any exists ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, I have issues calling anything an "application" if it does not have full error checking. That aside I believe what you are asking for is What environment variable do I need to set to cause dbaccess to exit at the first SQL error? The answer to that question is DBACCNOING setting ( and exporting ) this variable to any value will cause dbaccess to exit when the first non zero SQLCODE happens. Where I work we set it to 0 ( Zero) most of the time. The error checking then needs to be done by what ever called the dbaccess executable. Your example did not show any BEGIN WORK or COMMIT WORK, if your database has logging I would work that in around place that make logical. George. "LAKSHMI DEVI PALANISSAMY" <lakshmidevip@hcl.in> Sent by: ids-bounces@iiug.org 03/25/2009 11:37 AM Please respond to ids@iiug.org To ids@iiug.org cc Subject Re: fetch statement with shell script [15335] Hi We need to get the SQL EXIT code for each one of the SQL queries and validate the same before proceeding to execute next SQL QUERY. If there is any failure in executing the SQL QUERY, then we need to quit the application. Otherwise we need to continue executing the next SQL QUERY in sequence. To illustrate our requirement better, here is an example Eg. 1: We need to execute SQL QUERY to lock one of the tables present in our database using lock table <table_name> in exclusive mode perform some operations on the table and then unlock the table using unlock table <table_name> All these transactions to the database should happen within single session before we close the database. We require this to be implemented using shell script instead of using ESQL/C or PERL. Let us know the best possible solution if any exists ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ----------------------------------------- The information contained in this communication (including any attachments hereto) is confidential and is intended solely for the personal and confidential use of the individual or entity to whom it is addressed. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this communication in error and that any review, dissemination, copying, or unauthorized use of this information, or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. Thank you

Not possible in any UNIX shell not even using sqlcmd! You can use Marco's SQL Shell - see his post, but I suspect that if you are unwilling to use Perl then you are also unwilling to try sqls. 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, Mar 25, 2009 at 2:36 PM, LAKSHMI DEVI PALANISSAMY < lakshmidevip@hcl.in> wrote: > Hi > > We need to get the SQL EXIT code for each one of the SQL queries and > validate > the same before proceeding to execute next SQL QUERY. If there is any > failure > in executing the SQL QUERY, then we need to quit the application. Otherwise > we > need to continue executing the next SQL QUERY in sequence. > > To illustrate our requirement better, here is an example > > Eg. 1: We need to execute SQL QUERY to lock one of the tables present in > our > database using > > lock table <table_name> in exclusive mode > > perform some operations on the table and then unlock the table using > > unlock table <table_name> > > All these transactions to the database should happen within single session > before we close the database. > We require this to be implemented using shell script instead of using > ESQL/C > or PERL. > > Let us know the best possible solution if any exists > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00163683222a373eac0465f6fb01

Hi Can i get more details on how to give stored procedure as input to dbaccess. Is there a way to trap using the SQL error code returned each and every statement inside the stored procedure is executed Will that output of the execution of each and every line be traceable from within a script I would like to know more details about feeding a STORED procedure as input to dbaccess as we have no alternative left(not to use ESQLC/PERL) but to execute series of commands in one session using SHELL scripts with the output of each command to be validated against our requirement before proceeding to next statement It would be great to have more inputs to implement this with SHELL scripts.

Lakshmi: OK, you CAN trap and process error codes within a stored procedure. But note several things: 1. These are STORED procedures. You create one in the database and tell the database to EXECUTE it, you cannot issue SPL language commands from a shell script. I know that Oracle allows interactive sessions to execute programming-like logic, Informix does not. It's never been neccessary since, unlike Oracle, Informix has had good front-end programming tools since the very beginning of its existence. SQL is NOT a programming language and shouldn't be forced to become one. 2. You cannot receive detailed line by line error returns from an executing SPL or even a single SQL statement from a shell script sending commands to dbaccess, sqlcmd, or any other SQL processor unless you are willing to write your own in ESQL/C, 4GL, Perl, or some other front-end host language. It does not now exist since TMK noone else has had this requirement who wasn't willing to try a different approach. You CAN do this using Marco's sqls and its scripting language, but not using any UNIX shell. 3. You CAN HOWEVER, have dbaccess execute an SQL script or SPL stored procedure that contains all of your sequence of commands and set up the dbaccess session to exit when it encounters an error. If you execute an SQL script you can begin the script with a BEGIN WORK; statement and end it with a COMMIT WORK; statement. The entire transaction will automatically rollback when dbaccess exits on error before reaching the COMMIT WORK;. If you code the work into a stored procedure, you can be a bit more intelligent and control the error exit and explicitely ROLLBACK WORK; in the exception handler block in the procedure. Since you feel the need, which I can only assume is legitimate, to control the whole transaction process in your own code, WHY are you so adamently opposed to using another language as a front-end instead of a UNIX shell? That seems arbitrary to me. It's not as if you will be tying yourself to a proprietary language. You can use open source front-ends including: TCL-SQL, Perl DBI/DBD, Marco's SQL Shell, x4GL (there is the open source Aubit 4GL and two commercial Informix 4GL clones in addition to IBM's C4GL and R4GL), any compiled programming language that can call ODBC or JDBC functions, Ruby, and many others. What's the problem? 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 Thu, Mar 26, 2009 at 9:29 AM, LAKSHMI DEVI PALANISSAMY < lakshmidevip@hcl.in> wrote: > Hi > > Can i get more details on how to give stored procedure as input to > dbaccess. > > Is there a way to trap using the SQL error code returned each and every > statement inside the stored procedure is executed > > Will that output of the execution of each and every line be traceable from > within a script > > I would like to know more details about feeding a STORED procedure as input > to > dbaccess as we have no alternative left(not to use ESQLC/PERL) but to > execute > series of commands in one session using SHELL scripts with the output of > each > command to be validated against our requirement before proceeding to next > statement > > It would be great to have more inputs to implement this with SHELL scripts. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016369891bf142bc504660606f3

Art Thanks for the detailed inputs. But i would like to tell you that we have limitations in installing any frontend application because of the following reason. Am sorry for specifying wrong environment (RHEL) that we use in the initial post. To be clear, we are in the process of breaking down the functionalities performed inside .ec file to a simple shell script and this script has to run on SCO Open Server 3.0 and the product is already in the market and it is not recommended to add/remove any packages at client site. We can just use KSH and run the shell script to carry on with database transactions and we are left with no other choice. We are struck up on finding a way how to keep the database connection open throughout the script and keep on executing only the queries and validate the response code to carry on with the next SQL statement One another important thing is, we create some temporary tables for manipulations and if we use dbccess <dbname> <<! <statement to create temp tables> ! then the tables that we created are getting cleared once this statement is executed as the session closes rite after this statement with the selected database. This is exactly where we need your help in getting a solution to find out how to keep all these temp tables alive as we would require the temp tables to process the SQL statements that follow

2009/3/26 LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in>: > Art > > Thanks for the detailed inputs. But i would like to tell you that we have > limitations in installing any frontend application because of the following > reason. > > Am sorry for specifying wrong environment (RHEL) that we use in the initial > post. > > To be clear, we are in the process of breaking down the functionalities > performed inside .ec file to a simple shell script and this script has to run > on SCO Open Server 3.0 and the product is already in the market and it is not > recommended to add/remove any packages at client site. > > We can just use KSH and run the shell script to carry on with database > transactions and we are left with no other choice. > > We are struck up on finding a way how to keep the database connection open > throughout the script and keep on executing only the queries and validate the > response code to carry on with the next SQL statement > > One another important thing is, we create some temporary tables for > manipulations and if we use > > dbccess <dbname> <<! > <statement to create temp tables> > ! > > then the tables that we created are getting cleared once this statement is > executed as the session closes rite after this statement with the selected > database. > > This is exactly where we need your help in getting a solution to find out how > to keep all these temp tables alive as we would require the temp tables > to process the SQL statements that follow > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > To coin a phrase, you do insist on 'flogging a dead horse'. What you are trying to do is NOT possible in the environment in which you are operating. You MUST use some kind of programming language that is designed to maintain open sessions. Temp tables are ALWAYS dropped with the connection. Read waht you are being told, understand you cannot do this, decide on your programming platform and then come back with queries. Keith

You just can't do it from shell. Time to talk your clients into upgrading to Linux and newer releases of Informix. 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 Thu, Mar 26, 2009 at 11:25 AM, LAKSHMI DEVI PALANISSAMY < lakshmidevip@hcl.in> wrote: > Art > > Thanks for the detailed inputs. But i would like to tell you that we have > limitations in installing any frontend application because of the following > reason. > > Am sorry for specifying wrong environment (RHEL) that we use in the initial > post. > > To be clear, we are in the process of breaking down the functionalities > performed inside .ec file to a simple shell script and this script has to > run > on SCO Open Server 3.0 and the product is already in the market and it is > not > recommended to add/remove any packages at client site. > > We can just use KSH and run the shell script to carry on with database > transactions and we are left with no other choice. > > We are struck up on finding a way how to keep the database connection open > throughout the script and keep on executing only the queries and validate > the > response code to carry on with the next SQL statement > > One another important thing is, we create some temporary tables for > manipulations and if we use > > dbccess <dbname> <<! > <statement to create temp tables> > ! > > then the tables that we created are getting cleared once this statement is > executed as the session closes rite after this statement with the selected > database. > > This is exactly where we need your help in getting a solution to find out > how > to keep all these temp tables alive as we would require the temp tables > to process the SQL statements that follow > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00163646d086324475046609a859

Stored procedures may be your best bet: - can trap for errors - do transactions with BEGIN / ROLLBACK / COMMIT - cursors - can set up (error) logs - can make "system" calls which - can "echo message > /logdir/logfile" - run an external program Bob ----- Original Message ----- From: "Keith Simmons" <smiley73@googlemail.com> To: ids@iiug.org Sent: Thursday, March 26, 2009 11:55:00 AM GMT -05:00 US/Canada Eastern Subject: Re: RE: fetch statement with shell script [15346] 2009/3/26 LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in>: > Art > > Thanks for the detailed inputs. But i would like to tell you that we have > limitations in installing any frontend application because of the following > reason. > > Am sorry for specifying wrong environment (RHEL) that we use in the initial > post. > > To be clear, we are in the process of breaking down the functionalities > performed inside .ec file to a simple shell script and this script has to run > on SCO Open Server 3.0 and the product is already in the market and it is not > recommended to add/remove any packages at client site. > > We can just use KSH and run the shell script to carry on with database > transactions and we are left with no other choice. > > We are struck up on finding a way how to keep the database connection open > throughout the script and keep on executing only the queries and validate the > response code to carry on with the next SQL statement > > One another important thing is, we create some temporary tables for > manipulations and if we use > > dbccess <dbname> <<! > <statement to create temp tables> > ! > > then the tables that we created are getting cleared once this statement is > executed as the session closes rite after this statement with the selected > database. > > This is exactly where we need your help in getting a solution to find out how > to keep all these temp tables alive as we would require the temp tables > to process the SQL statements that follow > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > To coin a phrase, you do insist on 'flogging a dead horse'. What you are trying to do is NOT possible in the environment in which you are operating. You MUST use some kind of programming language that is designed to maintain open sessions. Temp tables are ALWAYS dropped with the connection. Read waht you are being told, understand you cannot do this, decide on your programming platform and then come back with queries. Keith *******************************************************************************

Lakshmi, Regarding temp tables and dbaccess statements from command line. Your sql session and associated temp tables are maintained until the dbaccess string completes. You can string multiple sql statements together seperated by ; in the same dbaccess call. dbccess <dbname> <<! begin work; <statement to create temp tables>; <statement to update table based on temp table>; <statement to unload data from temp tables>; commit work; ! I'm not sure if this really helps your situation. However, your comment about using temp tables in subsequent sql statements lead me to believe you might find it informative. Dave Griffen

On Wed, Mar 25, 2009 at 13:02, Art Kagel <art.kagel@gmail.com> wrote: > Not possible in any UNIX shell not even using sqlcmd! You can use Marco's > SQL Shell - see his post, but I suspect that if you are unwilling to use > Perl then you are also unwilling to try sqls. SQLCMD exits on error unless it is running interactively, or unless you've told it to continue on error. And the monitor mode could be used too. Of course, that uses ESQL/C (behind the scenes). Writing a pure shell script to do it does not bear thinking about. Even pure Perl would be hard (without DBI and DBD::Informix and ESQL/C - or ODBC - underneath). > On Wed, Mar 25, 2009 at 2:36 PM, LAKSHMI DEVI PALANISSAMY < > lakshmidevip@hcl.in> wrote: > >> Hi >> >> We need to get the SQL EXIT code for each one of the SQL queries and >> validate >> the same before proceeding to execute next SQL QUERY. If there is any >> failure >> in executing the SQL QUERY, then we need to quit the application. Otherwise >> we >> need to continue executing the next SQL QUERY in sequence. >> >> To illustrate our requirement better, here is an example >> >> Eg. 1: We need to execute SQL QUERY to lock one of the tables present in >> our >> database using >> >> lock table <table_name> in exclusive mode >> >> perform some operations on the table and then unlock the table using >> >> unlock table <table_name> >> >> All these transactions to the database should happen within single session >> before we close the database. >> We require this to be implemented using shell script instead of using >> ESQL/C or PERL. >> >> Let us know the best possible solution if any exists -- 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 - "I haven't spoken to my wife in years. I didn't want to interrupt her."