Sql issue in shell scripting


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sql issue in shell scripting
# 1  
Old 12-11-2012
Sql issue in shell scripting

HI friends , i am also facing an issue in mysql

i ma trying to insert detail in a variable but not got success

Code:
#!/bin/sh
mysql -u<username> -p<password> <dbname> << EOF
DEV=`mysql --skip-column-names <dbname> -e "SELECT timestamp from process_record where id = 1"`
EOF
echo $DEV

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1354888190' at line 1

its means i am gettting result. but why ia m getting result
# 2  
Old 12-11-2012
You're trying to execute shell inside a here-document, when MySQL is looking for SQL.

Code:
#!/bin/sh
DEV=$(mysql -u<username> -p<password> <dbname> --skip-column-names << EOF
SELECT timestamp from process_record where id = 1;
EOF
)
 
echo $DEV

You can, of course, execute shell if you want. So long as it returns SQL, since that what your here-document is feeding to MySQL.

Code:
$ cat myScript
#!/bin/sh
DEV=$(mysql -u<username> -p<password> <dbname> --skip-column-names << EOF
$(echo "select 123 from dual");
EOF
)
 
echo $DEV
 
$ ./myScript
123

# 3  
Old 12-11-2012
Thanks scott, its working i have one more query, if i need two three more record such as
Code:
SELECT timestamp,id,status from process_record where id = 1

will i need to run separate query for each variable or we have any other option.
# 4  
Old 12-11-2012
Those are columns, not records.

One option would be to put them into an array:
Code:
$ cat myScript
X=($(mysql -u user -p pass -D mydb --skip-column-names << !
$(echo "select 123, 456, 789 from dual");
!
))
echo ${X[0]}
echo ${X[1]}
echo ${X[2]}
 
$ ./myScript
123
456
789

Not all shells support arrays (especially sh if the sh in your first line (#!/bin/sh) really is an older Bourne shell), and it's not an ideal solution if the colums contain text with spaces.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Generate sql statement using shell scripting

Can anyone please assist me? I have attached 2 input files and one output file. I need to generate the sql update statements using the above 2 input files. if inputfile2 has 5 rows, then we should generate 5 update statements because column1 is unique. inputfile1 and inputfile2 may contain more... (10 Replies)
Discussion started by: vinus
10 Replies

2. Shell Programming and Scripting

Using shell scripting for making queries on postgres sql

I have a situation where I have a list of airplanes that make a series of flights. Therefore I am looking up the different flights that each airplane makes based on a postgres sql query: select flightid from plane where airplane='DELTAx' As a result I get a series of flight numbers... (0 Replies)
Discussion started by: JSNY
0 Replies

3. UNIX for Dummies Questions & Answers

How to compare to values returned from sql in shell scripting?

hey i am using this code to connect to sql , store the value in variable and then compare it with another variable after some time by executing the same query but the desired result is not coming #!/bin/bash val=$(sqlplus -s rte/rted2@rel76d2 <<ENDOFSQL set heading off set feedback off... (11 Replies)
Discussion started by: ramsavi
11 Replies

4. Shell Programming and Scripting

Shell Scripting question with SQL

hey i have to connect to sql through shell script , then store the value of the query in a variable and then compare it after some time after running a process. i have used this code but it is not working. #!/bin/sh Val = (sqlplus -s rte/rted2@rel76d2 <<! SELECT MAX(STAT_ID) FROM CVT_STATS;... (3 Replies)
Discussion started by: ramsavi
3 Replies

5. Shell Programming and Scripting

Assigning value of SQL output to a variable in shell scripting

I am trying to assgn the output of the select statement to a variable, like this "VARIABLE_NAME=$ db2 "select COLUMN_NAME_1 from TABLE_NAME where COLUMN_NAME_2='VALUE_TO_CHECK'"; " but the value that is getting into VARIABLE_NAME is "COLUMN_NAME_1 ----------------- VALUE 1... (3 Replies)
Discussion started by: sgmini
3 Replies

6. Shell Programming and Scripting

SQL commands in a shell scripting

Hi, I need to write a shell script file that does 1)Connects to DB using SQL plus 2)Do some SQL query like select * from.... 3)Based on the output that I got from the sql query I will have to write a if else loop. Plz let me know how to write the shell scripting for this. Thanks for... (1 Reply)
Discussion started by: villain41
1 Replies

7. Shell Programming and Scripting

Sql & Shell scripting book

Hi, I am looking for some books that have examples of both Oracle SQL and Shell script being used together. For example, to insert 500 rows into a table using a script. I already have books that cover Shell scripting or SQL, but as separate subjects. I am looking for books that have both of... (3 Replies)
Discussion started by: handle123
3 Replies

8. Shell Programming and Scripting

how towrite sql in shell scripting

hi iam new of scipiting.give me the some Material of regarding sql in scripting. and i have no backup's in 3 severs.how to write script in dirctory. please give me some usefull information cheers Naveen.g (2 Replies)
Discussion started by: naveeng.81
2 Replies

9. Shell Programming and Scripting

Shell scripting and SQL

Hi, I have a file that has contents like this: SQL> select * from details; NAME REG -------------------- ---------- Mani 1 Santosh 2 Manju 3 Mukesh 4 SQL> spool off; ... (5 Replies)
Discussion started by: sendhilmani123
5 Replies

10. Shell Programming and Scripting

Sql with shell scripting

Hi, I want to extract data from database table to a flat file using shell scripting. For loading data from file to a table, I had used SQL Loader. How can I do the vice versa using shell scripting. Thanks in advance (3 Replies)
Discussion started by: sendhil
3 Replies
Login or Register to Ask a Question