This blog guides you through the basic steps that you will have to follow to connect to database from a batch file on any windows machine.
As usual the first few lines in a batch/shell needs to start with the description on the use/purpose of batch file.
@REM Date Created : 31 Jan 2012
@REM Author : Sajeev
@REM Task : Connect to database from batch file
Next step would be to set the environmental variables which are required to establish a successful handshake with the database .
While writting my first batch file I faced some difficulties in retrirving the data from database and storing the results into batch variables .
The solution that came across was storing the data into a local temp file(this operation is done by query.sql file) later once the data extraction from db is through the value can be stored inside a variable by reading the data from the file.
Below is the line of code that would read the data from a temperoray file and store it into the variable.
sqlplus is the command that is used to connect to the database that I have and followed by it is the schema,username and password.
Once these credentials are defined , the sql query that needs to be executed needs to be present inside query.sql file as in this example , more info on the contents inside querl.sql file will be available in the later part of this post.
sqlplus ABC@scott/tiger @D:\scripts\query.sql
cd D:\scripts\
set SEPARATOR=\
set filecontent=
for /f "delims=" %%a in (result.txt) do (
set tempCnt=%%a
)
The code from line number 2-7 does the purpose of reading the temp file and storing the value in tempCnt variable.
In the above code snippet , the reason for providing the sql file in the same line as that of sqlplus command is ? Because when I used to run the batch file which would contain sqlplus ABC@scott/tiger and followed by sql query in next line then the command prompt used to display that the batch file has been connected to database but it doesn't execute the query.So by specifying the sql file in the same line as the sqlplus command made sure that the query gets executed after the connection is established.
Below is the content of the query.sql file . This file contains a query that retrievs the count from employee table.
set pagesize 0
set spool on
spool D:/scripts/result.txt
select count(1) from employee ;
spool off
exit;
where
set PAGESIZE to zero = suppresses all headings, page breaks, titles, the initial blank line, and other formatting information.
spool = Store query results in file
