From: judym@col.hp.com (Judy Miller)
Subject: Re: isql commands from shell prompt
Date: Wed, 15 Apr 1992 18:09:59 GMT
Organization: HP Colorado Springs Division

judym@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;

-----------------------------------------------------------------------------
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 cortesi@informix.com Mon Jan 13 11:16:43 1992
From: cortesi@informix.com (David Cortesi)
Subject: Re: Piping sql script into isql
Organization: Informix Software, Inc.
Newsgroups: comp.databases.informix
Reply-To: cortesi@informix.com

In article <752@bellahs.UUCP> jjamison@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 craigbr@informix.com Mon Jan 13 12:36:49 1992
From: craigbr@informix.com (Craig Browning)
Subject: Re: piping scripts into isql revisited
Organization: Informix Software, Inc.
Newsgroups: comp.databases.informix

In article <753@bellahs.UUCP> jjamison@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 :   jjamison@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 cortesi@informix.com Tue Jan 14 09:35:21 1992
From: cortesi@informix.com (David Cortesi)
Subject: Re**2: piping scripts into isql: bang escapes
Organization: Informix Software, Inc.
Newsgroups: comp.databases.informix
Reply-To: cortesi@informix.com

> first off, jjamison@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.201257.14449@cpqhou.uucp> thomasr@cpqhou.UUCP (Thomas Rush) writes:
>In article <1600@das13.snide.com> dave@das13.snide.com (Dave Snyder) writes:
>>In article <753@bellahs.UUCP>, jjamison@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%anasaz.UUCP@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.163521.29230@informix.com> cortesi@informix.com writes:
>> first off, jjamison@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%anasaz.UUCP@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.120825.24238@cis.ohio-state.edu> ackley@banana.cis.ohio-state.edu (john james ackley) writes:
>In article <1992Jan15.201722.10567@anasaz> briand@anasaz (Brian Douglass) writes:
>>In article <1992Jan14.201257.14449@cpqhou.uucp> thomasr@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 <ackley@cis.ohio-state.edu> // Disclaimers Anonymous
>
-- 
Brian Douglass			briand%anasaz.UUCP@asuvax.eas.asu.edu
				602-870-3330 X657

-----------------------------------------------------------------------------
From dave@das13.snide.com Wed Mar 18 20:34:56 1992
From: dave@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 <ksf32fINNqb0@spim.mips.com>, twong@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.Snyder@snide.com

-----------------------------------------------------------------------------
From infmx!infmuc!hermannd@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!hermannd@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!hermannd@uunet.UU.NET (Hermann Daeubler)
Subject: isql - shell prompt
To: uunet!col.hp.com!judym@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-list-owner@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: informix-list@rmy.emory.edu

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?  

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 


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 mm@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: <mm@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 judym@col.hp.com); Tue, 14 Apr 1992 13:13:30 +0100
  (5.65a+/IDA-1.4.2 for judym@col.hp.com); Tue, 14 Apr 92 10:58:24 +0100
	id AA02558; Tue, 14 Apr 92 10:58:22 +0100
To: judym@hpcsos.col.hp.com (Judy Miller)
Subject: Re: isql commands from shell prompt 
             <1992Apr13.225513.8254@col.hp.com> 
From: mm@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 flowers@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: <flowers@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: flowers@delphi.bio.caltech.edu (Dave Flowers)
To: judym@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 mikey@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: <mikey@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: mikey@tfs.COM (Mike Fischer)
To: judym@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 bobb@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: <bobb@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: bobb@netcom.com (Bob Beaulieu)
       "isql commands from shell prompt" (Apr 13, 10:55pm)
To: judym@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!hermannd@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!hermannd@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!hermannd@uunet.UU.NET (Hermann Daeubler)
Subject: isql - shell script  II
To: uunet!col.hp.com!judym@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: dranney@informix.com (David Ranney)
Newsgroups: comp.databases.informix
Subject: Re: isql commands from shell prompt
Organization: Informix Software, Inc.

In article <1992Apr13.225513.8254@col.hp.com> judym@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?  
>
>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 

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: dave@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.225513.8254@col.hp.com>, judym@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.snyder@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.m.altman@ccd.harris.com (Kristen Altman)
Newsgroups: comp.databases.informix
Subject: Re: isql commands from shell prompt
Date: 15 Apr 92 15:03:09 GMT
Reply-To: kristen.m.altman@ccd.harris.com (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