Shell Programming and Scripting

Hello,

I want to connect to ssh, run a query, and store that into a variable in the shell script. Also I need to pass the variable back to php to display the query results.
I have created a public/private key pair for ssh connection and that is working fine. Also I am able to run query in the script but unable to store the results into a variable.

Code snippet:

Code:

query="<query>";  # store query into a variable
ssh <user>@<ip> <<SCRIPT > Log #ssh connection password less
touch query.sql   # create file to write query
echo "$query" >> query.sql #write query into the new file
sqlplus -s usr/pass @query.sql #run query
exit
SCRIPT
cat Log    #display results from query

I am having few problems:
(1) sqlplus command runs the query correctly but also it hangs and come back to prompt only after ctrl-d
(2) Need to store the query result in a variable or an array, so that I can use that variable to display the query results on the website in user-designed tables.
(3) Need to paas the variable back to php.
Actually this shell script(conn.sh) I am calling from a php script through $out_conn = shell_exec("conn.sh");

I am using Solaris.

Does sqlplus hang every time?

You could use xigole jisql in place of sqlplus, and be rdbms vendor independen as well.

You could use a ssh tunnel to give php more direct access to Oracle.

You could have the script write the html table directly from SQL, and just include it.

I tried to store the query results in an array. Here is the code.

Code:

ssh <user>@<ip> <<SCRIPT > Log
let i=0
set -A arr
echo "<query>;
exit
" | sqlplus -s <usr>/<pass> | \
while read line
do
arr[$i]="$line"
echo ${arr[$i]}     # problematic line
let i=$i + 1
done
exit
SCRIPT

I am experiencing a weird problem. When I run the code in shell (without ssh) it works, when I run the above code (with ssh), it doesn't... Also as soon as I include the bold echo statement, script doesn't run.

Seems like a lot of trouble if it all ends up in Log anyway. This ssh is a login, did you try something like "echo script | ssh you@there ksh", which is just a command, not a login ?

Perhaps the example listed here could be of use:
https://www.unix.com/shell-programmin...variables.html

---------- Post updated at 05:14 PM ---------- Previous update was at 05:03 PM ----------

Be careful, I believe the login/password would show in the output of a ps -ef command. Could be a security issue.

Alternatively, I can save the output in Log and then parse it to store them in an array so that I can put them in table.....
As of now I have the query output in the file as:

Code:

NAME                     STAT                 COUNT(*)
------------------------ --------------- ----------
Ss ee dd       Accccccccccccc           508
Hh Hhh Sss               Fallll                  7
Wig Sss                  Fallll                 241
Aaa Sim                  Pennnnnn                611

I need to parse it first by line and then by cell (basically in matrix form), so that I can put them in a table.....
Also, this is just an example, the query is not fixed.

Thanks

A handy trick for data extraction is "Select 'dAtA AS X, t.* from table_name t where ...." so the data lines have a good hook for conversion. This was robust for almost any anount of data, too. The most robust was using <PRE> and good padding/spacing in place of tables (browser does not have to typeset every cell before sizing the columns, fixed pitch typesetting is very cheap).

Smart apps with passwords on the command line re-exec() to remove it, probably passing it in the environment or an open tmpfile(). I always did ok by echoing it into the input stream.

Since php has a database interface, why not use it?