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:
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 tried to store the query results in an array. Here is the code.
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 ?
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:
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.
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?
Last edited by DGPickett; 09-14-2011 at 10:03 AM..
I want to run sql query in shell script and output data save as delimited text (delimited text would be comma)
Code:
SPOOL_FILE=/pgedw/dan.txt
SQL=/pgedw/dan.sql
sqlplus -s username/password@myhost:port/servicename <<EOF
set head on
set COLSEP ,
set linesize 32767
SET TRIMSPOOL ON
SET... (8 Replies)
Hi Experts,
I have a list of Dbs.In that DBs i need to execute some sql scripts.
each sql script is unique and it should run on particular DB only.
For example. i have DBs like MDC20V,NDC20V,ODC20V and sql scripts like MD.sql,ND.sql,OD.sql.so MD.sql should run only in MDC20V and ND.sql should... (1 Reply)
Hi,
I have a query regarding execution of a sql query having long listing of parameters ..I need to execute this query inside a shell script.
The scenario is like....
Suppose I have a file abc.txt that has the card numbers..it could be in thousands..
then I need to fire a query like
... (12 Replies)
Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database.
I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :(
What should I do? I'm not sure that... (2 Replies)
I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this?
Is there a way to have a persistent connection to oracle database... (9 Replies)
hi Friends,
Please help me in writing shell script to run list of sql files.
database is Oracle 9i,
unix os is solaris
Requirement is
1. sql file must take two inputs a)feed id and b)business date
2.shell script must out put .xls or .csvfile as out put without trimming any column name and... (1 Reply)
Hi-
I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment...
I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
How do I create a K Shell which would silently (without user input) logon to Oracle and
run an SQL script?
Any help will be greatly appreciated.
Steve (1 Reply)