Re: isql commands from shell prompt

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