Sql issue in shell scripting

Shell Programming and Scripting
1

HI friends , i am also facing an issue in mysql

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

#!/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

You're trying to execute shell inside a here-document, when MySQL is looking for SQL.

#!/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.

$ 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

Thanks scott, its working i have one more query, if i need two three more record such as

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

Those are columns, not records.

One option would be to put them into an array:

$ 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.