Freetds Bash:在Microsoft SQL Server中执行SQL查询

Question

我能够使用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然后有我的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

通过freetsd命令行手动执行时，这可以正常工作，但是当我放入bash文件时不行。我关注了这篇文章：freetsd＆amp;bash。

这是我的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

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> 

然后执行我的其余脚本。

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

Answer 1

我不确定您的样本如何工作。

这是我的bash脚本样本：

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

尝试使用';'char。

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

更好的是，请使用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。

当前命令行输入：

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

Answer 2

旧线程，但这似乎有效。

printf  "use mydbnamengonselect * from mytablengonexitn"|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)

Answer 3

尝试

 echo "USE dbnamen GOn delete from schema.tableA where ID > 5n GOn delete from schema.tableB userid > 5n gon delete from schema.tableC where ID > 5n GOn exitn" 

该字符串的其余部分可能有效

尝试

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

尝试

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

如果您使用的是ODBC，我建议第二次试验。如果您将命令以" go"单词作为SQL句子分隔符发送到SQL，则第一个命令也许更好。也许第三个...谁知道...只有反复试验才能说明...