0

My organization uses batch files to perform queries on our database, and I am presented with a situation in which I need to perform a query which would be much too large to do all at once. So what I've done is modified a batch file to loop through a text file and query each line individually and append the results to the output file.

The problem is when I echo the query variable it reads exactly as expected, but when I pass it to ogr in place of the sql string it seems to be blank. I'm not sure if I'm doing something wrong, or if what I'm trying to do just isn't possible. Can anyone clarify what's going on here?

SET Path=\\my\path\blah\gdal\bin
SET GDAL_DATA=\\my\path\blah\gdal\data

@echo off
SET "file=C:\filepath.txt"
SET /A i=0

REM put file into array line by line
for /F "usebackq delims=" %%a in ("%file%") DO (
set /A i+=1
call set array[%%i%%]=%%a
call set n=%%i%%
)
REM Loop through array entries
for /L %%i in (1,1, %n%) DO (
REM Create SQL String
call set "queryStart="SELECT * FROM _tablename WHERE _fieldname IN ('"
call set uid=%%array[%%i]%%
call set "queryEnd=')""
call set call set "fullQuery=%%queryStart%%%%uid%%%%queryEnd%%"
REM Database Request
ogr2ogr -skipfailures -update -append -s_srs EPSG:4326 -t_srs EPSG:4326 -f "FileGDB" C:\blah\MyExport.gdb PG:"dbname=instagram host=hostservername user=username password=password" -sql %fullquery% -nln "LayerName" -nlt POINT --config FGDB_BULK_LOAD yes
)
pause;
brock
  • 365
  • 1
  • 3
  • 11

1 Answers1

0
SET Path=\\my\path\blah\gdal\bin;%PATH%
SET GDAL_DATA=\\my\path\blah\gdal\data

@echo off
    SET "file=C:\filepath.txt"
    SET /A i=0

    set "queryStart=SELECT * FROM _tablename WHERE _fieldname IN ^('"
    set "queryEnd='^)"

REM put file into array line by line
    for /F "usebackq delims=" %%a in ("%file%") DO (
        ogr2ogr -skipfailures -update -append -s_srs EPSG:4326 -t_srs EPSG:4326 ^
                -f "FileGDB" C:\blah\MyExport.gdb PG:"dbname=instagram host=hostservername user=username password=password" ^
                -sql  "%queryStart%%%a%queryEnd%" ^
                -nln "LayerName" -nlt POINT --config FGDB_BULK_LOAD yes
    )
  • It is not a good idea to remove all elements from path
  • There is no need to define the start and end of the query in each iteration
  • You can have problems with the parenthesis in the query, escape them
  • There is no need for the array, so directly read the file and use the read value in the query.
  • I have splitted the lines to better read the code. I don't know if ogr2ogr will complain

Not all the changes are needed, of course, just to make it more readable.

And, after all this, if i have to bet, your problem is in the parenthesis. But i can not test it.

MC ND
  • 69,615
  • 8
  • 84
  • 126
  • This may be progress, but this only passed the contents of the file and not the beginning and end of the sql string – brock May 07 '14 at 17:35
  • @brock, how have you determined it? i see the two variables defined and used in the line. Have you tried to remove the `echo off` to see the executed line? – MC ND May 07 '14 at 18:34
  • Why would it pass the line from the text file but not pass my other variables? – brock May 07 '14 at 19:17
  • Yes, I removed the echo off to see what it's doing, and it reads `...-sql [contents of file] -nln...` – brock May 07 '14 at 19:41
  • @brock, The only reason i can see for this behaviour if is this code is included inside an outer block. Then, the assignment of the variables queryStart and queryEnd (that will be inside the outer block) will not be seen without delayed expansion as they are changed and accessed in the same block. Is this the case? – MC ND May 07 '14 at 19:57
  • No, I have included the entire contents of the file here. There is no outer block. – brock May 08 '14 at 18:21