?
Solved

Passing params from .bat into .sql

Posted on 2003-03-06
8
Medium Priority
?
816 Views
Last Modified: 2008-02-01
Hi,

From a batch file I am using the OSQL utility to execute a .sql file.

Is there any way to pass parameters from this batch file into the .sql file?

Thanks & bye,
Jaya.
0
Comment
Question by:jayachristina
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 7

Expert Comment

by:Bruce Cadiz
ID: 8085903
If you mean "Oracle SQL" by OSQL you can do the following in your bat / cmd file:


%ORACLEHOME%\bin\sqlplus.exe scott/tiger@orcl @sqlfile.sql para1 para2 para3 ...



In the sqlfile.sql you would use syntax &1 &2 &3 ....:

SET VERIFY OFF;

SELECT * FROM EMP
 WHERE HIREDATE BETWEEN '&1' AND '&2'
   AND DEPTNO = &3;
0
 
LVL 7

Expert Comment

by:Bruce Cadiz
ID: 8085958
My bad OSQL utility... DUH!
Check out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp

Based on the above link I would expect the OSQL syntax would work as follows

SET para1 = "1/1/2003"
SET para2 = "2/1/2003"
SET para3 = 10

osql -Usa -P -Q "Select * from emp where hiredate between '%para%' and '%para2%' and deptno=%para3%"
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8086543
For user test, password testpwd with create table perm.
In YourFile.sql, you can use test.Value table columns for params.

REM START BAT FILE
@echo off
osql -Utest -Ptestpwd -q"exit(if object_id('test.Value') is not null drop table test.Value)"
osql -Utest -Ptestpwd -q"exit(select Par1=convert(nvarchar(4000),'%1'), Par2=convert(nvarchar(4000),'%2'), Par3=convert(nvarchar(4000),'%3'), Par4=convert(nvarchar(4000),'%4'), Par5=convert(nvarchar(4000),'%5'), Par6=convert(nvarchar(4000),'%6'), Par7=convert(nvarchar(4000),'%7'), Par8=convert(nvarchar(4000),'%8'), Par9=convert(nvarchar(4000),'%9') into test.Value)"
osql -Utest -Ptestpwd  -i YourFile.sql
osql -Utest -Ptestpwd -q"exit(drop table test.Value)"
REM END BAT FILE

Params are in a crosstable. You can rewrite it.

Good luck!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jayachristina
ID: 8087197
c336914 and  ispaleny, thanks a lot for your replies, but it doesn't seem to solve my problem.

ispaleny, your solution works fine, but is there a way to pass params directly without creating the temporary(test.Value) table?

Let me rephrase my question and try to be more clear.

* I have a file 'myFile.sql' whose contents are.,
insert into users(name, password) values ('anand', 'paul')

* I run myFile.sql using a batch file 'myBatch.bat' whose contents are.,
OSQL -Uadmin -PadminPass -SBERGAMO -dtempdb -n -i"C:\temp\eg\myFile.sql" -o"C:\temp\eg\myFile_out.txt"

* Instead of hard coding 'anand' and 'paul' into the insert statement, I would like to pass it as a parameter from myBatch.bat.

* I am using MS SQL 2000 running on Windows 2000 Server.

Thank you & bye,
Jaya.
0
 

Author Comment

by:jayachristina
ID: 8087528
c336914 and  ispaleny, thanks a lot for your replies, but it doesn't seem to solve my problem.

ispaleny, your solution works fine, but is there a way to pass params directly without creating the temporary(test.Value) table?

Let me rephrase my question and try to be more clear.

* I have a file 'myFile.sql' whose contents are.,
insert into users(name, password) values ('anand', 'paul')

* I run myFile.sql using a batch file 'myBatch.bat' whose contents are.,
OSQL -Uadmin -PadminPass -SBERGAMO -dtempdb -n -i"C:\temp\eg\myFile.sql" -o"C:\temp\eg\myFile_out.txt"

* Instead of hard coding 'anand' and 'paul' into the insert statement, I would like to pass it as a parameter from myBatch.bat.

* I am using MS SQL 2000 running on Windows 2000 Server.

Thank you & bye,
Jaya.
0
 
LVL 7

Expert Comment

by:Bruce Cadiz
ID: 8089101
Jaya,

Try this as a psuedo interactive batch file. It's not fancy but should work.

@cls
@echo off
if "%1"=="" goto end
if "%2"=="" goto end
set outfile="C:\temp\eg\myFile_out.txt"
echo.
echo======================================================
echo NEWUSER INSERT:
echo ADD USER '%1' PASSWORD '%2'?
echo======================================================
echo Hit Enter to continue or ^^C to Abort
echo.
pause
OSQL -Uadmin -PadminPass -SBERGAMO -dtempdb -n -Q"insert into users(name, password) values ('%1', '%2')" -o">"%outfile%"
echo.
echo.
echo ======================================================
echo %outfile% Contents:
echo.--------------------
type %outfile%
echo ======================================================
pause
goto done
:end
echo.
echo ====================================
echo Error No Parameters Passed:
echo ----------------------------
echo.
echo USAGE: myBatch {USERNAME} {PASSWORD}
echo.====================================
pause
:done

Bruce

Note: You can also change "TYPE" command to "notepad.exe" to invoke Notepad to view output file.

You can Also set user, pass in enviroment variables
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 100 total points
ID: 8090908
Dear jayachristina,
the answer is that parameters cannot be passed DIRECTLY between BAT and SQL files. You can build dynamic sql code in BAT file, but in W9x it is limited to 256 chars or less, it is OS-dependent. Store data in user table or file (tab delimited, fill #table in SQL by insert #table(Col1,..) exec master.dbo.xp_cmdshell 'type filename').

If you code is really so short, use dynamic sql code in BAT file. I used it in the 2nd osql line of my BAT solution.

Finally, you wrote "it doesn't seem to solve my problem" and "it works fine". Schizo? The question is answered when the solution works fine. You can reward the best fine tuned solution by separate FOR thread.
0
 

Author Comment

by:jayachristina
ID: 8093668
ispaleny,

Thanks a lot for your help. Now that I know for sure that params can't be passed directly from .bat to .sql, I've resorted to using your previous answer (ie., creating the temp table, populating it, and reading from it).

Thank you very much for your time and patience in explaining it to me.

Bye,
Jaya.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question