How to keep an Informix database connection open in a shell script?

[Desmodromic]

Using dbaccess I am trying to keep a database connection open in a shell script until I want to close it. It may be easiest to explain what I'm trying to do with a simple shell script which works fine for DB2.

db2 -v "connect to $DATABASE"
db2 -v "set current schema dba"
db2 -v "create table test (col1 int not null) in userspace1"

while [ $i -le 10 ]
do
db2 -v "insert into test(col1) values($i)"
(( i=i+1 ))
done

db2 -v "commit work"
db2 -v "connect reset"
db2 -v "terminate"


If I try similar logic with informix, the database is repeatedly opened and closed:

echo "database $DATABASE;" |dbaccess

Database selected.

Database closed.

echo "insert into test(col1) values($i);" | dbaccess -e $DATABASE

Database selected.

insert into test(col1) values(1);
1 row(s) inserted.

Database closed.

With Informix's dbaccess, is there a way I can open the database once, perform the inserts, and then close the database, similar to the way I can for DB2?

Thanks.

[Todd Roy]

Try using the sqlcmd suite of tools available from the iiug repository.

Signature

> Date: Wed, 29 Aug 2012 00:05:43 -0700
> Subject: How to keep an Informix database connection open in a shell script?
> From: davi...@yahoo.com.au
> To: inform...@iiug.org

[loc]

In Bash you can use a "Here Document" `man bash' for more info on that.

dbaccess $DATABASE << SQL
create temp table test (col1 integer);
insert into test(col1) values(1);
SQL

I'm not sure about using the loop as you did in your example since only SQL statements would be allowed inside the `dbaccess' here document, but you can put multiple SQL lines inside it. The database won't be closed until the end of the here document is reached.

[Art Kagel]

OK, you could do something like:

(
echo "begin work;"
echo "select .... from sometable into temp fred;""
echo "insert into othertable select * from fred;"
...
...
echo "commit work;"

) | dbaccess mydatabase -

However, personally I like the sqlcmd "server mode" better.

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.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[Jonathan Leffler]

On Wed, Aug 29, 2012 at 12:05 AM, Desmodromic <davi...@yahoo.com.au> wrote:

Using dbaccess I am trying to keep a database connection open in a shell script until I want to close it. It may be easiest to explain what I'm trying to do with a simple shell script which works fine for DB2.

db2 -v "connect to $DATABASE"
db2 -v "set current schema dba"
db2 -v "create table test (col1 int not null) in userspace1"

while [ $i -le 10 ]
do
   db2 -v "insert into test(col1) values($i)"
   (( i=i+1 ))
done

db2 -v "commit work"
db2 -v "connect reset"
db2 -v "terminate"

If I try similar logic with informix, the database is repeatedly opened and closed: [...]

There are several options:

 {
echo "connect to $DATABASE;"
echo "set current schema dba;" # Not in Informix
echo "create table test (col1 int not null) in userspace1;"

while [ $i -le 10 ]
do

   echo "insert into test(col1) values($i);"

   (( i=i+1 ))
done

echo "commit work;"
echo "connect reset;"  # Not in Informix
echo "terminate;" # Not in Informix
} | dbaccess - -

Note the semi-colons; they are critical.

The commands annotated 'Not in Informix' are simply not a part of the Informix command set.  But you can substitute appropriate other commands.

Alternatively, the SQLCMD program available from the IIUG archive has a pair of scripts, sqlserver and sqlclient (normally abbreviated to just 'sql') that can be used:

sqlserver -d dbase@server

sql "begin work"
sql "create table test (col1 int not null) in userspace1"

while [ $i -le 10 ]
do

   sql "insert into test(col1) values($i)"
   (( i=i+1 ))
done

sql "commit work"
sql "connect reset"  # Not in Informix
sql "exit" # Or 'quit' or 'bye', or 'q', or 'b', or 'e'; but not 'terminate' (6 alternatives is too many already).

--
Jonathan Leffler <jonathan...@gmail.com>  #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."

[Moukouri Henri]

You can echo and append your sql instructions in an sql file and run it using

dbaccess $DATABASE $SQL_FILE

Cheers
HM

[Desmodromic]

Thanks Jonathan, that brace-grouping technique with dbaccess did exactly what I wanted.