Embedded SQL support for Korn Shell ksh93

am 11.09.2007 19:12:59 von rgrieco

In order to run a SQL statement in Korn shell I have to pass the SQL
statement to the DB2 Command-line processor or in the case of Oracle
to SQL*Plus as a here-document.
Although this works it is a bit sloppy. I have not been able to find
a Korn shell solution, which would allow true embedded SQL support,
such as Perl DBI or Rexx with DB2 support.

Does anyone know of a solution (software package, function or open
source code), which would enable Korn shell to support embedded SQL?

Re: Embedded SQL support for Korn Shell ksh93

am 12.09.2007 16:05:31 von Miles

On Sep 11, 12:12 pm, rgri...@btmna.com wrote:
> In order to run a SQL statement in Korn shell I have to pass the SQL
> statement to the DB2 Command-line processor or in the case of Oracle
> to SQL*Plus as a here-document.
> Although this works it is a bit sloppy. I have not been able to find
> a Korn shell solution, which would allow true embedded SQL support,
> such as Perl DBI or Rexx with DB2 support.
>
> Does anyone know of a solution (software package, function or open
> source code), which would enable Korn shell to support embedded SQL?

Why not write your own function? This is what I do for Oracle:

############################################################ #######################
# run the sql commands
############################################################ #######################
run_command_file () {

typeset COMMAND_FILE=$1

print "\nINFORMATION: running $COMMAND_FILE..."
echo "$SEP1" | cut -c -${COLUMNS}

if [[ $TESTING -eq $FALSE ]]
then
if [[ $VERBOSE -ge $TRUE ]]
then
print "INFORMATION: running this sql file:"
cat $COMMAND_FILE
fi

[[ $DEBUG -ge $TRUE ]] && print "DEBUG: running: cat
$CONNECT_FILE $COMMAND_FILE | $SQLPLUS_COMMAND $SILENT"
cat $CONNECT_FILE $COMMAND_FILE | $SQLPLUS_COMMAND $SILENT |
egrep -vi "Session altered.|Elapsed:|^ *$"
RETCODE1=$?; if [[ $RETCODE -eq 0 ]]; then RETCODE=$RETCODE1; fi
[[ $DEBUG -ge $TRUE ]] && print "\nDEBUG: return code from
$SQLPLUS_COMMAND was: ${RETCODE1}."

else
[[ $FOREGROUND -eq $TRUE ]] && echo "$WARNINGMSG_ATTR_ON"
print "\nWARNING: testing mode is on. NO commands will actually
be run."
print " I would have ran:"
[[ $FOREGROUND -eq $TRUE ]] && echo "$GLOBAL_TURN_COLOUR_OFF"
echo "cat $CONNECT_FILE $COMMAND_FILE | $SQLPLUS_COMMAND $SILENT
| egrep -vi \"Session altered.|Elapsed:\""

fi

}

It is rather long just to run:
cat $CONNECT_FILE $COMMAND_FILE | $SQLPLUS_COMMAND $SILENT | egrep -vi
"Session altered.|Elapsed:|^ *$"

but it provides a lot on functionality and proves useful during
testing and debugging. It may not be exactly what you are after, but
avoids the here-documents.

Miles

Re: Embedded SQL support for Korn Shell ksh93

am 12.09.2007 22:00:52 von brian_hiles

On Sep 11, 10:12 am, rgri...@btmna.com wrote:
> In order to run a SQL statement in Korn shell I have to pass the SQL
> statement to the DB2 Command-line processor or in the case of Oracle
> to SQL*Plus as a here-document.
> Although this works it is a bit sloppy. I have not been able to find
> a Korn shell solution, which would allow true embedded SQL support,
> such as Perl DBI or Rexx with DB2 support.
> Does anyone know of a solution (software package, function or open
> source code), which would enable Korn shell to support embedded SQL?

As I understand your requirements, I tried to find a template
engine for use with ksh(1) to act as PRO*C does for C to
embed SQL -- which BTW DBI::* is decidedly _not_. Oddly,
I find there is none; however, since you are coding in ksh version
1993 you may wish to investigate the new "<<<" "here-word"
feature, implemented in the latter revisions of this shell version.
Because parameter subsitution and command/arithmetic
substitution occur, this may be used to implement a simple
template engine. I have not thought this through, so YMMV.

If I were to do this, I would write a function library utilizing
named pipes (or, if need be, here-files) to SQL*plus as a bridge
to Oracle. (Can't sqlplus talk to _any_ ODBC-compliant database?).
Here-words are nice, but the abstraction of a function should
sufficiently hide the klunkiness of here-files while preserving
maximal portability. Read my past post:

"Named Pipes in ksh(1)":
http://groups.google.com/group/comp.unix.shell/browse_thread /thread/278e8f6cd3e87559/5101c70322981053?lnk=st&q=&rnum=1#5 101c70322981053

Other ideas:

Incidentally, while on the subject, a method of calling
scripts directly from Oracle PL/SQL is discussed in my post
at:

"Run a PL SQL Package from a shell script":
http://groups.google.com/group/comp.unix.shell/browse_thread /thread/207defe6a7ad4064/2c6ec62ab57feaa6?lnk=st&q=&rnum=2#2 c6ec62ab57feaa6

Are you aware of the many frontends scripts written in ksh
available for Oracle (and presumable, DB2)? I have not used any
of them, but perhaps one or more will be pertainent to your
requirements, or give you ideas to develop upon:

oraenv2.ksh:
http://www.orafaq.com/scripts/unix/oraenv2.txt

"OpenDba Assistant -- Oracle DBA toolset": ODA.ksh
http://sourceforge.net/projects/opendba/

"manage and tune Oracle database": smenu.sh
http://www.geocities.com/bpolarsk/

"Adaptive Database Installation Toolkit":
http://sf.net/projects/adbit/

"produce referential constraint graph for Oracle & Informix":
http://www.tc.umn.edu/~hause011/code/ref_load_ord.ksh

"create reference constraint graph for Oracle & Informix":
http://www.tc.umn.edu/~hause011/code/ref_load_ord.ksh
^ (see also: http://www.sofbot.com/scripts.html)

rc.oracle.dtksh; rc.listener.dtksh
http://web.unixlabplus.com:8080/unix-prog/rc.oracle/

"convert imp(1) output to .SQL":
http://www.tc.umn.edu/~hause011/code/ugly_DDL.ksh

"load feed to Oracle database":
http://web.unixlabplus.com:8080/unix-prog/process_feed/proce ss_feed.ksh

"pls2html.gawk"
http://www.geocities.com/oranails/tools/pls2html.htm

"ora_backup.sh"
http://www.ronr.nl/unix-dba/en/scripts.html

For completeness, William Park has written several dynamically
loaded libraries for bash3 (not ksh93), including database
frontends -- not including DB2 or Oracle). I don't know why he
didn't write a portable interface abstraction layer so that the
same facility builtin to ksh93(1) could also be used in that
language, but here it is:

Search for: "Psql (PostgreSQL database)"
http://home.eol.ca/~parkw/index.html#bash

Good luck! Please respond back to this thread in the future
saying what success (or failure) you've had with any of the
above. It gives me the ability to discern what works and what
doesn't. Perhaps you could even post the code back to C.U.S.?

=Brian

Re: Embedded SQL support for Korn Shell ksh93

am 12.09.2007 22:04:39 von brian_hiles

Re: Embedded SQL support for Korn Shell ksh93

am 26.09.2007 03:19:47 von Roland Mainz

rgrieco@btmna.com wrote:
> In order to run a SQL statement in Korn shell I have to pass the SQL
> statement to the DB2 Command-line processor or in the case of Oracle
> to SQL*Plus as a here-document.
> Although this works it is a bit sloppy. I have not been able to find
> a Korn shell solution, which would allow true embedded SQL support,
> such as Perl DBI or Rexx with DB2 support.

ksh93 supports binary plugins which could be used to add something like
that, e.g. Oracle SQL bindings for ksh93 (a more complex example can be
found in
http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/sr c/lib/libshell/common/bltins/shiocmd_solaris.c
, function |b_poll()| which implements a "poll" builtin which works like
the poll(2) function).

----

Bye,
Roland

--
__ . . __
(o.\ \/ /.o) roland.mainz@nrubsig.org
\__\/\/__/ MPEG specialist, C&&JAVA&&Sun&&Unix programmer
/O /==\ O\ TEL +49 641 7950090
(;O/ \/ \O;)