0

I have Ingres DB and I keep all my procedures files in folder, if i need to rebuild them on DB. There is a lot of procedures which related to eachohter (called one from another), so that mean ai have to run some proceudre first then run other. Right now I am using bat file in which I have to supply file name, and it taking forefer to do it by hands one by one. To do it I am using this bat file.

echo '' > sqlout
more sqlout
set /p database="Enter database:"
if "%database%"=="" set database=1507
CALL C:\"Program Files"\Ingres\..\..\bin\setingenvs.bat
set directory_name=DBProcs
:While

set /p filename="Enter Procedure Filename:"
echo %filename%
sql DBServerName::%database% -udba <D:\Php_Web_Source\..\DBProcs\%filename% > sqlout
more sqlout
set /p another="Do you have another? (Y/N)"
if "%another%"=="N" goto EndWhile
goto While
:EndWhile

I want to it just run bat file and it should run all files in folder, but not sure how to do it. I was thinking about something like for loop, which call exist bat for each file in folder, but do not know how to do it, any help appreciated

Andrey
  • 1,629
  • 13
  • 37
  • 65
  • Hi Andrey, I'm a bit curious about your usage of pathes. `..` in a path means one level up - so `C:\"Program Files"\Ingres\..\..\bin\setingenvs.bat` is the same as `C:\bin\setingenvs.bat` ! Same with `D:\Php_Web_Source\..\DBProcs\%filename%` resolves to `D:\DBProcs\%filename%` or is this a method of obfuscating the code? –  Dec 08 '16 at 23:06
  • yes, the paht is long i juzt make it shorter for here :) – Andrey Dec 08 '16 at 23:13

2 Answers2

2

If you want to iterate all the files in a directory it is a basic for loop.

for %%G IN (C:\mypath\*.*) do echo %%G

The %%G will be the path with the file name in this instance. You can then run any command with that variable.

Squashman
  • 13,649
  • 5
  • 27
  • 36
  • stil dont understand how to call my exist batch file for each file, could you please give me an example? – Andrey Dec 08 '16 at 22:58
  • @Andrey, I know nothing about databases. I assumed you were going to replace your SET /P command. `set /p filename="Enter Procedure Filename:"` – Squashman Dec 08 '16 at 23:00
  • dont care about procedure I nee to call other batch file which i have in my question, and pass there file name as parameter. but just dont understand how – Andrey Dec 08 '16 at 23:05
  • 1
    @Andrey, the code Squashman has given you essentially replaces the part of your script where you set %filename%, allowing you to run all files in a set of folders. – littlefeltfangs Dec 09 '16 at 16:59
1

If you don't often add new files which need to be run, then one option would be to simply write a single sql file which calls all of your other scripts.

For example:

\cd D:\Php_Web_Source\..\DBProcs\
\read proc1.sql
\read proc2.sql
\read proc3.sql

You can also create this script fairly quickly by getting a list of all your files and then using a tool such as Notepad++ to do a quick find and replace

dir/b *.sql > files.txt

Would give you a list of all sql files in a single directory (you can adjust the command if you need multiple directories). Then using a Regex search and replace

\r\n

with

\r\n\\read

would get you what you need.

As I said, this is a quick method if your files don't change to often or as a quick and dirty method. Squashman's answer is more correct for a regularly changing file structure.

littlefeltfangs
  • 396
  • 2
  • 17