setting a c-shell variable to a SELECT statement

Question

i have written a c-shell script to connect to a database. This already works just fine and i now want to invoke an sql script to read and print ALL the values in a cetrain table. As of now this is how my script looks

#!/bin/csh 


set MYSQL=${MYSQL_HOME}/mysql
${MYSQL} ${CLEDBUSER}

where CLEDBUSER is set as an environment variable like so - CLEADBUSER=-uusername -ppassword -Ddatabasename

i am able to run the script and connect to the database. When i runt he script it gives me the msql pront awaiting the next command. So i added to the script a variable that contains the (SELECT) statement to query the database. Now my script looks like this

#!/bin/csh 


set MYSQL=${MYSQL_HOME}/mysql

set SELECTER="SELECT * FROM TB_EARTH_UI;"

${MYSQL} ${CLEDBUSER} ${SELECTER}

the problem is it doesnt return me all the rows and columsn but it returns me a listing of avaiable commands in mysql promt and default options and also vairables and boolean options. Why is my SELECT statement not getting read?

in other words when you have already connected to the database via a c-shell script can you also read and execute sql commands at the same time through the same c-shell script? — rambokayambo
– rambokayambo, Commented Jun 27, 2012 at 16:44
possible duplicate of Can we run a mysql query through command prompt in windows? — outis
– outis, Commented Jun 27, 2012 at 17:44

RandomSeed · Accepted Answer · 2012-06-27 21:59:01Z

1

MySQL client (mysql) expects SQL instructions on its standard input (e.g. your keyboard, when invoking from the shell).

You could do: [edit: please ignore, this one is off-topic]

${MYSQL} ${CLEDBUSER} < text_file_of_sql_statements.sql

or

${MYSQL} ${CLEDBUSER} << EOF
  ${SELECTER}
  # you can add other litteral SQL statements here, or more variables containing SQL statements
EOF

or

${MYSQL} ${CLEDBUSER} --execute="${SELECTER}"

[edit]

I totally misunderstood the OP's question. I didn't get it that he was trying to execute SQL statements from a variable. I have edited the above options (thank you outis). Here is another variation:

echo ${SELECTER} | ${MYSQL} ${CLEDBUSER}

Also, the --skip-column-names option could make it easier for you to parse the output.

edited Jun 27, 2012 at 21:59

answered Jun 27, 2012 at 17:50

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rambokayambo Over a year ago

use a text file of sql statements is there any need to declare a variable that holds an sql string? why would i have to use a .sql file to hold my select statement? and also what does SELECT 1 stand for?

rambokayambo Over a year ago

ok i defined a SELECT*FROM my table in a separate sql file and it worked fine and this worked fine

rambokayambo Over a year ago

so does this mean i cannot use direct SQL statements assigned to a vairable in my c-shell script?

RandomSeed Over a year ago

@rambokayambo I am so sorry, I totally misunderstood your question. The third option, edited by outis, is the only one that should fulfill your need.

Collectives™ on Stack Overflow

setting a c-shell variable to a SELECT statement

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related