freeTDS bash: Executing sql queries in Microsoft S

问题:

I am able to connect to a Microsoft SQL Server 2008 instance via a Mint Linux VM using freeTSD and command line to execute sql statements on it. Now I want automate this in a bash script. I am able to successfully login in my bash script:

TDSVER=8.0 tsql -H servername -p 1433 -D dbadmin -U domain\\Administrator -P password

I then have my SQL query:

USE dbname GO delete from schema.tableA where ID > 5 GO delete from schema.tableB where ID > 5 GO delete from schema.tableC where ID > 5 GO exit

This works when doing manually via freeTSD command line, but not when I put in bash file. I followed this post: freeTSD & bash.

Here is my bash script sample:

echo "USE dbname GO delete from schema.tableA where userid > 5 go delete from schema.tableB where userid > 5 go delete from schema.tableC where ID > 5 GO exit" > tempfile | TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U domain\\Administrator -P password < tempfile

the output of the bash script is:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
Default database being set to sbdb
1> 2> 3> 4> 5> 6> 7> 8> 

and then the rest of my script is executed.

Can someone give me a step by step answer to my problem ?

回答1:

I'm not sure how your sample can work at all.

Here is my bash script sample:

echo "USE dbname .... exit" > tempfile | TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U domain\\Administrator -P password < tempfile
# ------------------------------------^^^^ ---- pipe char?

Try using a ';' char.

echo "USE dbname .... exit" > tempfile ; TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U domain\\Administrator -P password < tempfile
# ------------------------------------^^^^ ---- semi-colon

Better yet, use shell's "here documents".

TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U domain\\Administrator -P password <<EOS
     USE dbname 
     GO 
     delete from schema.tableA where userid > 5 
     go 
     delete from schema.tableB where userid > 5 
     go 
     delete from schema.tableC where ID > 5 
     GO 
     exit
  EOS

IHTH.

Current command line input:

echo "delete from table where userid > 5
go
delete from table where userid > 5
go
delete from table where ID > 5
GO
exit" < /tmp/tempfile; TDSDUMP=/tmp/freetds.log TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U Administrator -P password <<EOS

回答2:

Old thread but this seemed to work..

printf  "use mydbname\ngo\nselect * from mytable\ngo\nexit\n"|tsql -I freetds.conf -S profileName -U user -P 'password'

1> 2> 1> 2> ID  stringtest  integertest
1   test    50
2   teststring2 60
3   test3   70
(3 rows affected)

回答3:

try

 echo "USE dbname\n GO\n delete from schema.tableA where ID > 5\n GO\n delete from schema.tableB userid > 5\n go\n delete from schema.tableC where ID > 5\n GO\n exit\n" 

the rest of this string is stuff that maybe works

and try

 echo "USE dbname;\n delete from schema.tableA where ID > 5;\n delete from schema.tableB userid > 5;\n delete from schema.tableC where ID > 5;\n exit\n" 

and try

 echo "USE dbname; delete from schema.tableA where ID > 5; delete from schema.tableB userid > 5; delete from schema.tableC where ID > 5; exit" 

if you are using odbc, i recommend the second trial. if you are sending commands to sql with a "go" word as sql sentences separator, maybe the first one is better. maybe the third one... who knows... only trial and error can tell...