Can .bat file execute an sql query and return a value?

Question

How can I call a query from a .bat file? (say my query is: select version from system).

Can my .bat file save the output that this query returns? I wanna use this output in my NSIS script.

Answer 1

EDIT: This answer is for SQL Server, not Oracle. (It wasn't immediately clear when the question was posted).

You may want to check the sqlcmd utility.

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt. The following example executes a query when sqlcmd starts and then exits immediately. Multiple-semicolon-delimited queries can be executed:

sqlcmd -d AdventureWorks -Q "SELECT FirstName, LastName FROM Person.Contact WHERE LastName LIKE 'Whi%';"

To save the output of the query to a file, you can use the -o C:\< filename> option.

Answer 2

If you have a command-line SQL utility, that most probably it allows command-line operations. According to your SQL-version you can check it.

Answer 3

This depends on what tool you use to execute queries. For example, for the mysql command line tool, you'd do:

mysql -u<user> -p<password> -h<host> -Nrs -e"SELECT version() FROM system" > out.txt

Here, the piece that goes mysql -u<user> -p<password> -h<host> are the standard options to connect to the server. -Nrs are a set of options that will cause the client to not output all the ascii art for the results. The bit that goes -e"SELECT version() FROM system" actually specifies the command that should be executed. See http://dev.mysql.com/doc/refman/5.1/en/mysql-command-options.html for more info on those mysql-specific options. Look for similar options in the client tool you want to use.

The last bit, > out.txt is a standard operating system-level redirect. It will cause the output coming from the mysql program to be stored in a file out.txt, but perhaps you can redirect directly to your NSIS program as well.

Answer 4

Try this:

http://www.dbforums.com/oracle/1044496-sqlplus-return-value-help.html

Answer 5

Try

sqlplus user/pwd@mydb @query.sql > result.txt

where query.sql contains the database query and result.txt will be filled with the query output.

Depending on the query, the output file may not be as you expect it (column headers etc as typical of sqlplus), so your query.sql should contain some SET commands to adjust the output to your needs.

Another method is to use the Oracle SPOOL command instead of piping the result to a file.

Answer 6

For Oracle:

http://stackoverflow.com/questions/638705/how-can-i-issue-a-single-command-from-the-command-line-through-sql-plus/639373#639373

@echo select version from system; | sqlplus username/password@database 

You can either pipe the output to a file and use that, or wrap this in a for command to parse the output within your batch file.