4

Using bq tool I have no problem escaping the > operator with a caret ^:

bq query "SELECT x FROM [presentation_dim.dim_events] WHERE event^>1"

However, when I CALL the exact same command through a bat file whe whole thing breaks down.

call bq query "SELECT x FROM [presentation_dim.dim_events] WHERE event^>1"

I understand call is the problem here. I can't remove it, since I need to run additional commands after it (bq extract and gsutil cp). I've tried adaptations of what is shown on Escape user input in windows batch file, to no avail.

What's wrong here? Thanks in advance.

Community
  • 1
  • 1
JairoM
  • 43
  • 2

2 Answers2

4

I suppose bq.cmd itself contains something like this

set param1=%1
set SQL=%~2
python bigQuery.py --%param1% "%SQL%"

So the line set SQL=%1 requires escaping the special characters.

But when you use CALL, the batch parser has an additional phase of escaping but before it also has a phase of doubling all carets!

So your string in call bq query "SELECT x FROM [presentation_dim.dim_events] WHERE event^>1" is converted to "SELECT x FROM [presentation_dim.dim_events] WHERE event^^>1"

I think there is no solution with only carets to solve this problem.

But you can avoid this by simply defining a variable containing one caret

set "caret=^"
call bq query "SELECT x FROM [presentation_dim.dim_events] WHERE event%%CARET%%>1"
jeb
  • 78,592
  • 17
  • 171
  • 225
1

Excuse me. I think there is a confusion here.

When a parameter is enclosed in quotes, it may include special Batch characters with no need to escape they; the only problematic case is when Delayed Expansion is enabled and the parameter include an exclamation-mark or caret. For example, this line works correctly:

bq query "SELECT x FROM [presentation_dim.dim_events] WHERE event>1"

This way, the same line with a CALL command also works correctly:

call bq query "SELECT x FROM [presentation_dim.dim_events] WHERE event>1"

If you escape the greater-than sign this way ^> and don't use CALL, the character passed is the same ^> that is processed as a single > in bq.bat. However, if you use CALL, then the escape is duplicated and ^^> is passed.

Conclusion: Don't escape the > character.

Aacini
  • 65,180
  • 12
  • 72
  • 108
  • Thanks, Aacini. Apparently, bq requires > to be escaped, even when enclosed in quotes: http://stackoverflow.com/questions/22356037/using-timestamp-literals-in-a-where-clause-with-bq-tool/22358001#22358001. I tried following your recomendations, but it didn't work. btw, bq is a python-based tool, so it may be a more complex file than our regular bat file. :-( – JairoM Apr 14 '15 at 04:26
  • If bq command is not a `bq.bat` file, then it does NOT require a CALL in order to return to the calling Batch file! Perhaps I am missing something? – Aacini Apr 14 '15 at 04:38
  • It's a cmd file that triggers python code. It needs CALL to return to the original bat file. I'll keep exploring on the path you kindly suggested. ````excerpt from bq.cmd @echo off rem Copyright 2013 Google Inc. SETLOCAL rem rem CLOUDSDK_ROOT_DIR rem CLOUDSDK_PYTHON rem CLOUDSDK_PYTHON_ARGS (u) python interpreter arguments rem CLOUDSDK_PYTHON_SITEPACKAGES (u) use python site packages SET CLOUDSDK_ROOT_DIR=%~dp0.. SET PATH=%CLOUDSDK_ROOT_DIR%\bin\sdk;%PATH% IF "%CLOUDSDK_PYTHON%"=="" ( SET CLOUDSDK_PYTHON=python.exe ) – JairoM Apr 14 '15 at 04:56