isql commands from shell prompt
Judy Miller
How do I issue the following command from inside a shell script?
load from "add_units.data" insert into fgistatus;
In which section is this discussed?
I've never had an overview, and I can't seem to find what I need in the
index or by browsing each section's table of contents.
Judy
Dave Snyder
> I have an Informix-SQL Version 4.00 in front of me.
>
> How do I issue the following command from inside a shell script?
>
> load from "add_units.data" insert into fgistatus;
>
> In which section is this discussed?
Judy, try this in your shell script...
Here --------- Snip Here --------- Snip Here --------- Snip Here --------- Snip
isql dbname - <<EOF
load from "add_units.data" insert into fgistatus;
EOF
Here --------- Snip Here --------- Snip Here --------- Snip Here --------- Snip
DAS
--
David A. Snyder @ Snide Inc. - Folcroft, PA Current Release
is db4glgen-3.4
UUCP: ..!uunet!das13!dave INTERNET: dave....@snide.com
Adrian FLynn
Judy
From the command line this will work.
%isql dbname < sql_file
where dbname is the name of the database containing the table fgistatus
and sql_file is a file containing SQL statements ie. the load command.
Alternatively to reduce typing at the command line prepare the script to
use a here docoment. So we have a file containing the following which we
chmod +x:-
isql dbname <<end_of_sql
load from "add_units.data" insert into fgistatus;
load from "another" insert into another_table;
end_of_sql
Substitute dbname.
I am not sure where this is discussed in the manuals.
Regards
Adrian
--
-------------------------------------------------------
#include "usual.stuff.h" Adrian Flynn ^^^^^
#include "disclaimer.h" Informix Software Ltd \O-O/
#include "big.city.h" London >
David Ranney
Hope this helps:
#!/bin/sh
isql - - <<!
database dbname;
load from "add_units.data" insert into fgistatus;
!
Kristen Altman
Regarding shell scripts with Informix, we all have a good example in the
Informix directories. The file that installs the demo database is a script
file called $INFORMIXDIR/bin/isqldemo. That file helped me out a lot.
Hope it helps!!
Kristen
--
*******************************************************
* Kristen Altman *
* k...@ccd.harris.com *
* Harris Controls and Composition Division *
* Melbourne, FL *
*******************************************************
Judy Miller
> I have an Informix-SQL Version 4.00 in front of me.
>
> How do I issue the following command from inside a shell script?
>
> load from "add_units.data" insert into fgistatus;
-----------------------------------------------------------------------------
Hi gang ...
Here's a quick summary of what I learned on executing isql from a shell prompt
followed by detailed examples that were either posted or mailed.
Judy
-----------------------------------------------------------------------------
Summary:
Syntax: isql [-s] [-|database-name] [-ansi] -q[options] [command-file-name]
Using a script:
1. isql mydb myscript; /* assumes existance of myscript.sql */
2. isql - myscript; /* if database mydb exists in myscript.sql */
3. isql -s - myscript; /* suppresses screen messages */
Using a here file:
4. isql <<EOF
database mydb;
load from "mydata" insert into mytable;
EOF
5. isql <<-EOF
database mydb;
load from "mydata" insert into mytable;
EOF
NOTE: The <<- in a here file tells the shell to ignore leading tabs
in the following lines. (that's what I've been told)
6. isql mydb - <<EOF
load from "mydata" insert into mytable;
EOF
NOTE: The hypen doesn't appear to be necessary in a script, but does
produce a ">" prompt during an interactive session.
7. isql - - <<EOF
database mydb;
load from "mydata" insert into mytable;
EOF
Using both a script and a here file:
8. cat > /tmp/myscript.sql <<EOF
load from "mydata" insert into mytable
EOF
isql mydb /tmp/isql.sql
-----------------------------------------------------------------------------
Detail:
Here are the detailed posts and email that I received.
Thanks for the input. Maybe someone else can do a better summary (and
add it to the next manual!)
-----------------------------------------------------------------------------
From cor...@informix.com Mon Jan 13 11:16:43 1992
From: cor...@informix.com (David Cortesi)
Subject: Re: Piping sql script into isql
Organization: Informix Software, Inc.
Newsgroups: comp.databases.informix
Reply-To: cor...@informix.com
In article <7...@bellahs.UUCP> jjam...@bellahs.UUCP (John Jamison) writes:
> I am told there is an undocumented flag for isql that
> allows you to pipe in an sql script into isql. It
> works either by | isql -<flag> or isqli -<flag> inputfile.
>
This ought to be in Appendix I of the ISQL manual but isn't.
The magic command is: isql - -
The first hyphen is the database argument, you could give a database name.
The second hyphen says, read stdin. A dumb example would be:
isql stores - <<eot!
select * from systables where tabid > 99;
eot!
Comparable would be
isql - - <<ZZZ
database stores;
select fname, lname from customer
where customer_num > 100
order by fname
ZZZ
-----------------------------------------------------------------------------
From cra...@informix.com Mon Jan 13 12:36:49 1992
From: cra...@informix.com (Craig Browning)
Subject: Re: piping scripts into isql revisited
Organization: Informix Software, Inc.
Newsgroups: comp.databases.informix
In article <7...@bellahs.UUCP> jjam...@bellahs.UUCP (John Jamison) writes:
>Ok so I feel stupid. yes input redirection and piping worked without
>a problem. But I am told there is an undocumented flag which presents
>you with a ">" prompt or something, sort of a back door into isql
>in which you don't have all the menus and special keys, etc. And it
>will also take input from redirected input and a pipe.
>
>Thanks for all the responses so far,
>
>John Jamison
>--
>John Jamison : jjam...@bellahs.com
>
>Disclaimer: the opinions expressed herein are those of
>the author and not of his employer
The "isql - -" command Dave Cortesi mentioned will give you the ">" prompt
to work with in an interactive session.
A session could look something like this:
% isql - -
> database stores;
Database selected.
> select * from customer;
etc.
You can use a pipe, etc. for input instead of stdin as Dave mentioned.
I hope this helps,
Craig
-----------------------------------------------------------------------------
From cor...@informix.com Tue Jan 14 09:35:21 1992
From: cor...@informix.com (David Cortesi)
Subject: Re**2: piping scripts into isql: bang escapes
Organization: Informix Software, Inc.
Newsgroups: comp.databases.informix
Reply-To: cor...@informix.com
> first off, jjam...@bellahs.UUCP (John Jamison) asked,
I am told there is an undocumented flag for isql that
allows you to pipe in an sql script into isql...Can anybody give me the name?
> I responded that "isql <database> -" would do it and gave this example:
isql stores - <<eot!
select * from systables where tabid > 99;
eot!
> Jamison wasn't satisfied...
I am told there is an undocumented flag which presents
you with a ">" prompt or something, sort of a back door into isql
in which you don't have all the menus and special keys, etc.
> and rightly so, as Craig Browning pointed out...
The "isql - -" command Dave Cortesi mentioned will give you the ">" prompt
to work with in an interactive session.
A session could look something like this:
% isql - -
> database stores;
Database selected.
> select * from customer;
etc.
That was news to us here in tech pubs [of course it came AFTER the
5.0 manuals went to press :-( ] but even more interesting news came
today. When operating in piped mode this way, ISQL will accept an
initial "!" as meaning escape to shell. Here is how it looks
using 4.1 software on a Sun:
[gort 7] isql stores -
> select avg(customer_num) from customer
where fname matches "[A-G]*";
>
(avg)
115.06
1 row(s) retrieved.
> !echo hello from the shell
hello from the shell
> ^D
-----------------------------------------------------------------------------
From briand@anasaz Wed Jan 15 13:17:22 1992
From: briand@anasaz (Brian Douglass)
Subject: Re: piping scripts into isql revisited
Organization: Anasazi, Inc. Phoenix, Az
Newsgroups: comp.databases.informix
In article <1992Jan14.2...@cpqhou.uucp> tho...@cpqhou.UUCP (Thomas Rush) writes:
>In article <16...@das13.snide.com> da...@das13.snide.com (Dave Snyder) writes:
>>In article <7...@bellahs.UUCP>, jjam...@bellahs.UUCP (John Jamison) writes:
>>> Ok so I feel stupid. yes input redirection and piping worked without
>>> a problem. But I am told there is an undocumented flag which presents
>>> you with a ">" prompt or something, sort of a back door into isql
>>> in which you don't have all the menus and special keys, etc. And it
>>> will also take input from redirected input and a pipe.
>>
>>Ok, try this...
>>
>> isql dbname -
>
>Or,
> isql - -
>
> [but you might want to pick a database first off]
Or,
isql - - >>!
> database whatever;
> select * from foo;
>!
And it runs. The ! gets set as the end of extended input character.
--
Brian Douglass briand%anasa...@asuvax.eas.asu.edu
602-870-3330 X657
-----------------------------------------------------------------------------
From briand@anasaz Wed Jan 15 13:30:54 1992
From: briand@anasaz (Brian Douglass)
Subject: Re: Re**2: piping scripts into isql: bang escapes
Organization: Anasazi, Inc. Phoenix, Az
Newsgroups: comp.databases.informix
In article <1992Jan14.1...@informix.com> cor...@informix.com writes:
>> first off, jjam...@bellahs.UUCP (John Jamison) asked,
>
>That was news to us here in tech pubs [of course it came AFTER the
>5.0 manuals went to press :-( ] but even more interesting news came
>today. When operating in piped mode this way, ISQL will accept an
>initial "!" as meaning escape to shell. Here is how it looks
>using 4.1 software on a Sun:
This is really quite funny, because this feature has been around since
version 1.10 of Informix-SQL. Hell, I've probably still got scripts around
from circa 1983 to prove it. Why does it take 8 years to get a feature
documented?
On other undocumented, or at least hard to find features, is placing a
semicolon (;) at the end of a Print line in either Ace or 4GL commands the
Print statement NOT to issue a CR/LF. The next print line resumes printing
1 character to the right of the last line.
This feature is documented (in some manual), I've been able to find it
about 3 times in 8 years (once in V1.10, V2.1, and in V4.0), but it seems
very few others have heard of it. Might it be possible to better document
this in the future?
Hey, how about a FAQ on undocumented or poorly documented features? Does
this exists yet, or should I be checking a FAQ list someplace?
--
Brian Douglass briand%anasa...@asuvax.eas.asu.edu
602-870-3330 X657
-----------------------------------------------------------------------------
From briand@anasaz Sat Jan 18 20:10:00 1992
From: briand@anasaz (Brian Douglass)
Subject: Re: Arcane bits (was Re: piping scripts into isql revisited)
Organization: Anasazi, Inc. Phoenix, Az
Newsgroups: comp.databases.informix
In article <1992Jan16.1...@cis.ohio-state.edu> ack...@banana.cis.ohio-state.edu (john james ackley) writes:
>In article <1992Jan15.201722.10567@anasaz> briand@anasaz (Brian Douglass) writes:
>>In article <1992Jan14.2...@cpqhou.uucp> tho...@cpqhou.UUCP (Thomas Rush) writes:
>>
>>Or,
>> isql - - >>!
>> > database whatever;
>> > select * from foo;
>> >!
>>
>>And it runs. The ! gets set as the end of extended input character.
>
>Watch out! It may have run, but you have a new file named "!".
>You want "<<" for the here-document operator.
Ooops! Sorry about that, hit the wrong key. Bad finger! Bad!
>Also, someone suggested a faq of little known/undocumented features:
>good idea!
So who wants to volunteer? Someone at Informix Documentation maybe?
>Regards,
>John Ackley <ack...@cis.ohio-state.edu> // Disclaimers Anonymous
>
--
Brian Douglass briand%anasa...@asuvax.eas.asu.edu
602-870-3330 X657
-----------------------------------------------------------------------------
From da...@das13.snide.com Wed Mar 18 20:34:56 1992
From: da...@das13.snide.com (Dave Snyder)
Subject: Re: how to run .sql files at unix command line
Organization: Snide Inc. - Folcroft, PA
Newsgroups: comp.databases.informix
In article <ksf32f...@spim.mips.com>, tw...@mips.com (Tony Wong) writes:
>
> I would like to know how you can run a .sql file using isql at the
> unix command line - e.g. isql <database> -flags abc.sql
If you script is called "myscript.sql", then type this...
isql dbname myscript
or if the script contains a DATABASE statement type...
isql - myscript
DAS
--
David A. Snyder @ Snide Inc. - Folcroft, PA Current Release
is db4glgen-3.4
UUCP: ..!uunet!das13!dave INTERNET: Dave....@snide.com
-----------------------------------------------------------------------------
From infmx!infmuc!herm...@uunet.UU.NET Tue Apr 14 09:30 MDT 1992
(16.6/15.5+IOS 3.14) id AA00815; Tue, 14 Apr 92 09:30:36 -0600
Return-Path: <infmx!infmuc!herm...@uunet.UU.NET>
(16.6/15.5+IOS 3.14) id AA20965; Tue, 14 Apr 92 03:12:27 -0600
(5.61/UUNET-internet-primary) id AA20745; Tue, 14 Apr 92 05:12:27 -0400
(queueing-rmail) id 051122.18537; Tue, 14 Apr 1992 05:11:22 EDT
id AA00569; Tue, 14 Apr 92 01:35:59 PDT
id AA04386; Tue, 14 Apr 92 10:28:56 +0200
From: infmx!infmuc!herm...@uunet.UU.NET (Hermann Daeubler)
Subject: isql - shell prompt
To: uunet!col.hp.com!ju...@uunet.UU.NET
>From uunet!rmy.emory.edu!informix-list-owner Tue Apr 14 10:04:27 1992
id AA03757; Tue, 14 Apr 92 10:04:17 +0200
id AA00424; Tue, 14 Apr 92 01:11:13 PDT
(5.61/UUNET-internet-primary) id AA08721; Tue, 14 Apr 92 03:02:58 -0400
rmy.rmy.emory.edu (5.65/Emory_rmy.3.2.6) via SMTP
id AA22078 ; Tue, 14 Apr 92 03:01:00 -0400
Return-Path: informix-...@rmy.emory.edu
From: uunet!col.hp.com!judym (Judy Miller)
Subject: isql commands from shell prompt
Reply-To: uunet!col.hp.com!judym (Judy Miller)
Organization: HP Colorado Springs Division
To: inform...@rmy.emory.edu
Judy
Hello Judy,
if I understand what you want - I would try :
1. put the following statements in a file with extension .sql
( example : test.sql ) :
database <database name>;
load from ... ;
close database;
2. type the following command at shell level :
isql - test.sql
Hope it helps a little bit
Hermann the German
( Support - Informix Germany )
-----------------------------------------------------------------------------
From m...@gloin.ssf.pt Tue Apr 14 09:30 MDT 1992
(16.6/15.5+IOS 3.14) id AA00539; Tue, 14 Apr 92 09:30:18 -0600
Return-Path: <m...@gloin.ssf.pt>
(16.6/15.5+IOS 3.14) id AA21364; Tue, 14 Apr 92 06:15:36 -0600
(5.65c/IDA-1.4.4 for ju...@col.hp.com); Tue, 14 Apr 1992 13:13:30 +0100
(5.65a+/IDA-1.4.2 for ju...@col.hp.com); Tue, 14 Apr 92 10:58:24 +0100
id AA02558; Tue, 14 Apr 92 10:58:22 +0100
To: ju...@hpcsos.col.hp.com (Judy Miller)
Subject: Re: isql commands from shell prompt
In-Reply-To: Your message of 13 Apr 92 22:55:13 +0000.
<1992Apr13....@col.hp.com>
From: m...@ssf.pt
Hi Judy,
You can have isql reading commands from stdin.
So try this from your shell script:
isql <<%
database your-db-name;
load from ...;
%
Perhaps you'll need to put the load-stmt inside a transaction.
Good luck!
MM
-----------------------------------------------------------------------------
From flo...@delphi.bio.caltech.edu Tue Apr 14 09:40 MDT 1992
(16.6/15.5+IOS 3.14) id AA00987; Tue, 14 Apr 92 09:40:20 -0600
Return-Path: <flo...@delphi.bio.caltech.edu>
(16.6/15.5+IOS 3.14) id AA11713; Mon, 13 Apr 92 17:40:26 -0600
id AA08818; Mon, 13 Apr 92 16:42:40 -0700
From: flo...@delphi.bio.caltech.edu (Dave Flowers)
To: ju...@hpcsos.col.hp.com
Subject: Re: isql commands from shell prompt
In comp.databases.informix you write:
>I have an Informix-SQL Version 4.00 in front of me.
>How do I issue the following command from inside a shell script?
> load from "add_units.data" insert into fgistatus;
#example
isql <name of database> - <<EOF
load from "add_units.data" insert into fgistatus;
EOF
#end of example
Also, "isql <name of database> -" will get you an interactive sql
command line (exit with ^D, of course).
I found this out on the net, and I read all the manuals I have all the
way through, so don't expect to find it there (but I don't have the C stuff
or 4gl).
From mi...@tfs.COM Tue Apr 14 10:59 MDT 1992
(16.6/15.5+IOS 3.14) id AA00199; Tue, 14 Apr 92 10:59:46 -0600
Return-Path: <mi...@tfs.COM>
(16.6/15.5+IOS 3.14) id AA23009; Tue, 14 Apr 92 10:59:41 -0600
id AA09111; Tue, 14 Apr 92 09:59:39 -0700
id AA06480; Tue, 14 Apr 92 09:59:36 PDT
From: mi...@tfs.COM (Mike Fischer)
To: ju...@hpcsos.col.hp.com
Subject: Re: isql commands from shell prompt
>> How do I issue the following command from inside a shell script?
>> load from "add_units.data" insert into fgistatus;
I can't say that I have ever seen how this would be done in a manual
but here is how I accomplish it:
cat > /tmp/isql.sql << DONE
load from "add_units.data" insert into fgistatus(your line)
DONE
isql database-name /tmp/isql.sql
That should take care of it.......mikey@tfs.com(Michael Fischer/TRW Fin. Sys.)
-----------------------------------------------------------------------------
From bo...@netcom.com Tue Apr 14 17:34 MDT 1992
(16.6/15.5+IOS 3.14) id AA01940; Tue, 14 Apr 92 17:34:55 -0600
Return-Path: <bo...@netcom.com>
(16.6/15.5+IOS 3.14) id AA02838; Tue, 14 Apr 92 17:34:53 -0600
id AA14346; Tue, 14 Apr 92 16:35:28 PDT
From: bo...@netcom.com (Bob Beaulieu)
In-Reply-To: ju...@col.hp.com (Judy Miller)
"isql commands from shell prompt" (Apr 13, 10:55pm)
To: ju...@hpcsos.col.hp.com (Judy Miller)
Subject: Re: isql commands from shell prompt
#!/bin/sh
isql dbname - <<! # open db and run script until you see a '!'
load
from "add_units.data"
insert
into fgistatus;
!
works everytime ...
bobb
-----------------------------------------------------------------------------
From infmx!infmuc!herm...@uunet.UU.NET Wed Apr 15 02:57 MDT 1992
(16.6/15.5+IOS 3.14) id AA03485; Wed, 15 Apr 92 02:57:32 -0600
Return-Path: <infmx!infmuc!herm...@uunet.UU.NET>
(16.6/15.5+IOS 3.14) id AA12275; Wed, 15 Apr 92 02:57:30 -0600
(5.61/UUNET-internet-primary) id AA16036; Wed, 15 Apr 92 04:57:27 -0400
(queueing-rmail) id 045636.1636; Wed, 15 Apr 1992 04:56:36 EDT
id AA23552; Tue, 14 Apr 92 22:54:58 PDT
id AA00623; Wed, 15 Apr 92 07:47:54 +0200
From: infmx!infmuc!herm...@uunet.UU.NET (Hermann Daeubler)
Subject: isql - shell script II
To: uunet!col.hp.com!ju...@uunet.UU.NET
> Hello Judy,
>
> if I understand what you want - I would try :
>
> 1. put the following statements in a file with extension .sql
> ( example : test.sql ) :
>
> database <database name>;
> load from ... ;
> close database;
>
> 2. type the following command at shell level :
>
> isql - test.sql
Hmmmm. so the minus (-) redirects commands from a file?
Is this syntax peculiar to Informix?
My first guess would have been a redirection (<) instead of a minus.
Thanks for the quick reply!
Judy
Hello Judy,
don't think about redirection. It is just the syntax. In my manual I have
a page in Appendixes :
isql [-s] [-|database-name] [-ansi] -q[options] [command-file-name]
so you see that you can use a "-" or ! a database name and behind the
command-file-name. If you use only a "-" it means that the database name
appears in the command file. Important is that your command file has the
extension .sql and that you must use this name without extension if you
make this isql call.
Example : command-file : test.sql
call : isql - test
if you use the parameter -s then all non-essential sreen messages are
suppressed.
isql -s - test
if you have further questions - send an email
Hermann the German
( Support - Informix Germany )
-----------------------------------------------------------------------------
From hp-col!sdd.hp.com!cs.utexas.edu!sun-barr!west.West.Sun.COM!male.EBay.Sun.COM!jethro.Corp.Sun.COM!exodus.Eng.Sun.COM!appserv.Eng.Sun.COM!sun!amdcad!weitek!pyramid!infmx!dranney 15 Apr 92 00:50:09 GMT
From: dra...@informix.com (David Ranney)
Newsgroups: comp.databases.informix
Subject: Re: isql commands from shell prompt
Organization: Informix Software, Inc.
In article <1992Apr13....@col.hp.com> ju...@col.hp.com (Judy Miller) writes:
Hope this helps:
#!/bin/sh
isql - - <<!
database dbname;
load from "add_units.data" insert into fgistatus;
!
-----------------------------------------------------------------------------
From hp-col!sdd.hp.com!spool.mu.edu!agate!stanford.edu!rutgers!ub!dsinc!das13!dave 14 Apr 92 01:11:12 GMT
From: da...@das13.snide.com (Dave Snyder)
Newsgroups: comp.databases.informix
Subject: Re: isql commands from shell prompt
Article-I.D.: das13.1654
Organization: Snide Inc. - Folcroft, PA
In article <1992Apr13....@col.hp.com>, ju...@col.hp.com (Judy Miller) writes:
> I have an Informix-SQL Version 4.00 in front of me.
>
> How do I issue the following command from inside a shell script?
>
> load from "add_units.data" insert into fgistatus;
>
> In which section is this discussed?
Judy, try this in your shell script...
Here --------- Snip Here --------- Snip Here --------- Snip Here --------- Snip
isql dbname - <<EOF
load from "add_units.data" insert into fgistatus;
EOF
Here --------- Snip Here --------- Snip Here --------- Snip Here --------- Snip
DAS
--
David A. Snyder @ Snide Inc. - Folcroft, PA Current Release
is db4glgen-3.4
UUCP: ..!uunet!das13!dave INTERNET: dave....@snide.com
-----------------------------------------------------------------------------
From hp-col!sdd.hp.com!usc!sol.ctr.columbia.edu!emory!ccd.harris.com 15 Apr 92 1
5:03:09 GMT
From: kristen....@ccd.harris.com (Kristen Altman)
Newsgroups: comp.databases.informix
Subject: Re: isql commands from shell prompt
Date: 15 Apr 92 15:03:09 GMT
Sender: wa...@mathcs.emory.edu
Reply-To: kristen....@ccd.harris.com (Kristen Altman)
Lines: 17