Always we are not so fortunate to work with only SQL server through out our whole life. Sometimes we may have to come across different databases like DB2, MySQL, Sybase etc..
In this article we are going to create a dynamic sql query where we will pass few parameters into it through command line interface at the time of execution. You all must have been questioning yourself, why has not this guy adopted a simple stored procedure instead of breaking his head on scripting so much? That’s a nice question, I agree. Think, suppose you are not the DBA in your organization and have been restricted to create stored procedure in live database but on regular task you want to fetch few data for audit and delete some older history from the table with passing some different parameters for strong condition, each time.
Recently I was informed by my manager to have a close look into a particular table of DB for few days, where I had to fetch few data each time and update them with strong conditions in it. The most important thing is that, It was not a MS SQL Server platform. It was IBM DB2 and this platform was new to me. Being a DBA I could not tell him 'I am New to this DB'. However, for couple of days I was doing this by DB2 interface. Then slowly I started feeling bored and thought of to automate this by a stored procedure, where I was instructed not to create any objects in production DB. It started playing in my mind and then the game Began.
The objective of this article is, we should not create stored procedures unnecessarily for any temporary query in our production environment. I know stored procedure is an effective option but I am gonna show you here something different. Suppose If you have no idea/permissions to create a stored procedure in production DB but still you want to achieve it then you can do it by below steps of my article.
Here we are going to ROCK with VB scripts, SQL files and batch scripting in this whole article. Just follow me..
1. Create a folder to keep all thsese files in a single domain.
Sample Folder/File names goes here:
The main folder name: NewFolder
Contents of NewFolder: Test.bat, Reset.vbs, Replace.vbs, Test.sql, RefFolder(Test.sql)
2. I have created a .sql file where I am going to put below sql scripts.
--Connection to DB2 batabase, DDL and DML commands made here
connect to MyDB user prasad using abc123;
select MyName, MyID, AccnNo, TimeQueued from PushJob where currentstatus = 'AB' and TimeQueued <= (Current Timestamp - 10 days);
delete from PushJob where currentstatus = 'AB' and timequeued <= (Current Timestamp - 10 days);
saved in C:\NewFolder\Test.sql I am going to put a copy of Test.sql in my seconadry folder RefFolder.
This above sql file will fetch my selected data which is older than 10 days and in the next DML step it will delete those records of older than 10 days.
Now my manager’s requirement comes in, we have to make that 10 days as user input variable means, a user can go ahead and delete 13 days or 15 days or any of his/her desired input in numbers of days. We can do this by creating a simple stored procedure but my Manager restricted me for creating objects in database so the only way is to depend on The Almighty here because now only he can pass the variable to my simple text formatted Test.sql file.
Us being the creation of The Almighty, we are blessed with some of his powers. So lets start the game with the powerfull scripting language VB. 2nd step goes below.
3. VB script to find and replace a word/number from a simple text file.
Our requirement is to find and replace the default value 10 from the Test.sql file.
remember to keep the original Test.sql file in C:\NewFolder\RefFolder for future use. Because here we used 10 as the default value to find and replace in our Replace.vbs script.
' Usage: cscript replace.vbs Filename "StringToFind" "stringToReplace"
' VBScript to find and replace variable in a simple text file
' Author Prasad Sahoo
' Dated 03-07-2012
'Does file exist?
if fso.FileExists(strFilename)=false then
wscript.echo "file not found!"
save this script in C:\NewFolder\Replace.vbs
now are next concern is to pass our desired string to Replace.vbs, which will find and replace the number 10 in Test.sql file.
we can pass the strings through command line by below command
cscript //Nologo Replace.vbs Test.sql "10" "%MyVar%"
don't be confused with above command line variable %MyVar%. I will give you a clear batch script at the end of this topic with integrated of our all .vbs and .sql files.
4. VB script to reset the Test.sql file with default value for our next time use.
After the execution of Replace.vbs the default value (10 days) will change to user input value. Thus we need to reset the user input value to default value (10) again for our next execution.
Frankly speaking there are so many ways to do this by VB scripting but I am so lazy to break my head on core scripting. so, adopted the simple copy and paste method in VB script.
Remember, In staring we had kept a copy of Test.sql file inside C:\NewFolder\RefFolder. So, the below VB script is developed to replace the modifed Test.sql with original copy of Test.sql from RefFolder.
You may have a question here. Why we are doing this? simpe! to get the default value (10) for replacement with user input value on each execution.
The VBS code goes below
Dim objFSO, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(".\RefFolder\Test.sql\")
objFSO.CopyFile objFile.Path, ".\NewFolder\"
5. Final Touch by batch scripting.
The batch script is a powerfull tool from Microsoft to run each process step by step instructed inside.
Lets execute all scripts in simple english language below.
Reset.vbs execution command to batch process (before each instance it will reset the value in
Test.sql for smooth run of batch).
b. Declare a variable inside batch script to take user input.
c. Handle the blank input by user in CLI.
d. After assigning the value to the varible lets display it to user once.
e. The sql file containing DML commands so take a user input again (Y/N) to proceed.
f. If user input is N, then terminate the process.
Replace.vbs execution command to batch process.
h. execute the modified
Test.sql file in database. capture the log
i. Success command to user.
j. The ehole process will create a
ExecLog.log file inside RefFolder.
Now lets write the above simple english language steps in some different languange .
set /P MyVar=Please enter days in number, you wud like to delete: %=%
if "%MyVar%"=="" goto MyVar
echo You are going to delete data older than %MyVar% day/s.
SET /P AREYOUSURE=Are you sure to delete failed jobs(Y/N)?
IF /I "%AREYOUSURE%" NEQ "Y" GOTO END
echo ------------------------------on %date% %time% !nLine!------------------------------ >> .\RefFolder\ExecLog.log"
cscript //Nologo .\NewFolder\Replace.vbs .\NewFolder\test.sql "10" "%MyVar%"
db2cmd -c "db2 -tvf .\NewFolder\Test.sql>> .\RefFolder\ExecLog.log
echo The data older than %MyVar% days. is deleted >> .\RefFolder\ExecLog.log
echo You are done!
Here our whole concept is working like a stored procedure but logic is different. The logic is passing user parameters and executing accordingly. This is nothing but the concept of stored procedure on Air. As monsoon arrived in Ahmedabad so, I have named this article "Stored Procedure On Real Cloud".
Here we concluded that, command line user input can be passed as parameter to PL/SQL programme by our above Bull method.
Feel free to mail me if you have any concern on this regard.