Linux freeTDS bash：在 Microsoft SQL 服务器中执行 sql 查询

freeTDS bash: Executing sql queries in Microsoft SQL server

提问by

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:

我能够使用 freeTSD 和命令行通过 Mint Linux VM 连接到 Microsoft SQL Server 2008 实例，以在其上执行 sql 语句。现在我想在 bash 脚本中自动执行此操作。我能够成功登录我的 bash 脚本：

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

I then have my SQL query:

然后我有我的 SQL 查询：

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.

这在通过 freeTSD 命令行手动执行时有效，但在我放入 bash 文件时无效。我关注了这篇文章：freeTSD & bash。

Here is my bash script sample:

这是我的 bash 脚本示例：

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:

bash 脚本的输出是：

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 ?

有人可以一步一步地回答我的问题吗？

采纳答案by shellter

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

我不知道你的样本是如何工作的。

Here is my bash script sample:

这是我的 bash 脚本示例：

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

更好的是，使用 shell 的“此处文档”。

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.

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

回答by

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

如果您使用的是 odbc，我建议您进行第二次试用。如果您使用“go”单词作为 sql 句子分隔符向 sql 发送命令，也许第一个更好。也许第三个......谁知道......只有反复试验才能告诉......

回答by rob

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)