I need to input my data to bsqldb.

I am planning to pass my data to bsqldb from within a bash script using a variable using this command:

/usr/bin/bsqldb -S servername -U username -P password <<< ${VARIABLE}

$VARIABLE will contain data organized in this manner:

USE databasename
customsqlfunction ('param1','param2','param3','param4','param5')
customsqlfunction ('param1','param2','param3','param4','param5')
customsqlfunction ('param1','param2','param3','param4','param5')
customsqlfunction ('param1','param2','param3','param4','param5')

The sql server I'm connecting to is a MSSQL 2008 and it seems to be running TDS v 7.1 (which seems weird... read everywhere 2008 is supposed to be on 7.2 but the tds tools keep saying it's downgrading the protocol to 7.1 when i connect... but that is another issue)

  • What's the output of the command you're running? Have you tried it out? As I understand it has to be valid SQL, so I'd suggest semicolons after the expressions. – Andreas Wiese May 9 '14 at 14:05
  • The customsqlfunction command has been created on the mssql server, it has no output, its use is only to write data to the db. If you were refering to bssqldb I must admit I havent tried to pass my data to it yet cause im not sure how to structure it. I found this example billyshaw.com/2012/09/28/query-mssql-from-bash but I dont understand the deal with EOF in his scrpt. Some other examples I found that use an input file dont seem to use EOF. Also, I dont have to use the semi colon, I have confirmed it. And the bsqldb man says the last batch of cmd doesnt need to receive the GO cmd – TCZ8 May 9 '14 at 14:13

I managed to test this and figured it out.

Here is the proper way to structure the data contained in the variable you will send to bsqldb:

MyVariable="select @@servername"$'\n'"select @@language"$'\n'"select @@version"

As you see each sql commands sent to bsqldb must be on a separate line. This is where \n comes in, it represents the "newline" or "linefeed" character. The rest "select @@servername" for example, are the actuall SQL commands.

Here is what bsqldb will see when I feed it $MyVariable from above:

select @@servername
select @@language
select @@version

Usually you need to send a GO command to execute a series or batch of commands but as the Freetds userguide points out in chapter 6 Use Freetds the last batch of commands sent to bsqldb doesn't need to be followed by GO to be executed, it will run automatically. I also confirmed that at the end of your cmd list, in contrary to tsql, the EXIT command is not required to close the connection to the server. It exits automatically once it reached the end of your cmd list.

Now that we have our variable figured out, we can feed it to bsqldb from within a bash script using this syntax:

/usr/bin/bsqldb -S servername -U username -P password <<< $MyVariable


|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.