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.

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

Signature

On Wednesday, August 29, 2012 3:05:43 AM UTC-4, Desmodromic wrote:
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 SQLstatements 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.

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.



On Thu, Aug 30, 2012 at 10:56 AM, loc <c320sky (AT) gmail (DOT) com> wrote:

On Wed, Aug 29, 2012 at 12:05 AM, Desmodromic <davies_ms (AT) yahoo (DOT) com.au>wrote:

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.leffler (AT) gmail (DOT) 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."

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

dbaccess $DATABASE $SQL_FILE

Cheers
HM

Le 29 août 2012 Ã* 08:05, Desmodromic <davies_ms (AT) yahoo (DOT) com.au> a écrit :

On Friday, August 31, 2012 1:50:40 AM UTC+10, Jonathan Leffler wrote:
Thanks Jonathan, that brace-grouping technique with dbaccess did exactly what I wanted.