About Peter Gale Expertise Anything on Ingres. Installing, configuring, Performance, SQL. Making best use of Ingres, Query Execution, data importing & exporting.
Experience 15 Years using Ingres. Worked with every release from 6.4 through to R3 & 2006. Mainly as DBA but also in application development. Specialist in performance tuning. Experienced Ingres trainer having delivered courses all over the world
Organizations I am Managing Director and a Principal Consultant at Comprehensive Soltions (www.comp-soln.co.uk) and a committe member of the UK Ingres Users Association.
Expert: Peter Gale Date: 8/7/2006 Subject: how to subsite dynamic values in ingres plsql procedure
Question hi peter iam actually calling a sql file via the terminal monitor and my problem is that i have to pass substitute some values in the query which i pass throught the command line so how can i code in the sql file like declaring variables and substituting the values of the variables in the query
suppose for oracle i have a sql file which selets whether the employee with name peter is there or not i write it as
in file user.sql
define user=&1
select empname from emp where empname='&user';
and in command prompt i shall call as
c:>sqlplus system/manager @user.sql peter
now i want to achieve the same functionality in ingres like how should i validate the variables declaration and usage in the file user.sql
hope this is sufficient if u need more i can give more elobarately
Answer Hi Ravi,
Windows batch file make this a little bit tricky as they dont suppport redirection too well. Ingres does not have variables in the basic SQL syntax so you need to substitute the windows batch file parameters into the SQL.
Something along these lines will work.
Create a .bat file with this code
echo select empname from emp where empname='%1'\g | sql mydb
Run the file
mysql.bat peter
Not ideal but about as good as my limited batch file knowledge will allow. Now if you were on Unix.....
