Page 1 of 1
Escaping ! < and > within a SET statement that is then piped
Posted: 10 Jul 2012 23:33
by colinhay
Trying to set a variable to an SQL statement with a != in it in a DOS batch file
but am unable to escape the ! so that both the set and the pipe pass the SQL correctly. No number of ^'s prior to the ! make any difference.
Using <> instead of != is just as problematic ....
Running with enabledeleyexpansion on
set SQL=select policy_id from plcy ^
where policy_id != 0 ^
\p\g
echo %SQL% | sql database
Re: Escaping ! < and > within a SET statement that is then p
Posted: 11 Jul 2012 04:22
by Ed Dyreen
'
The characters you are refering to are 'special' in DOS, they have to be escaped to use them in a variable,
but you need to escape them twice because of the pipe, to prevent them from being 'interpreted' and 'executed'.
Code: Select all
@echo off &setlocal enableDelayedExpansion
set $lf=^
::
set ^"$sql=^
select policy_id !$lf!^
from plcy !$lf!^
where policy_id ^^^^^^^!= 0 \p\g !$lf!^
and Using ^^^^^<^^^^^> instead of ^^^^^^^!= is just as problematic ...."
echo.!$sql!
::echo.!$sql! |sql database
pause
exit
Code: Select all
select policy_id
from plcy
where policy_id ^!= 0 \p\g
and Using ^<^> instead of ^!= is just as problematic ....
Druk op een toets om door te gaan. . .
The linefeeds are just for readability, you can remove them though sql shouldn't have any problems when you leave them in.
Regards,
Re: Escaping ! < and > within a SET statement that is then p
Posted: 11 Jul 2012 07:04
by jeb
Ed, you are wasting some carets
You take seven carets instead of only six in front of the exclamation marks.
jeb
Re: Escaping ! < and > within a SET statement that is then p
Posted: 11 Jul 2012 20:59
by Ed Dyreen
[edit by Ed Dyreen on 27 Jul 2012] corrected an error at line 19; added an extra exclamation mark so all variables retract properly.It's like a bad habbit innit
I inherit this behavior from our delay algo we develop : delay ! = '^' * 2 + '^!'
Code: Select all
:: --------------------------------------------------------------------------------------------------------------------------
set "$defines=reDelay_" &set "$details="
:: --------------------------------------------------------------------------------------------------------------------------
:: last updated : 10/03/2012
:: support : naDelayed, no $lf, $cr
::
2>nul ( %macroStart_% enableDelayedExpansion )
:: (
%= =%set ^"!$defines!=!forQ_! (1,2) do if %%?==2 (%$n1c%
%= =%!n2echo_! !$defines!: '^^^!$^^^!'%$n1c%
%= =%!necho_! $notDelayedFlag: '^^^!?^^^!'%$n1c%
%= =%!forQ_! (^^^!$^^^!) do set $=^&set $=^^^!%%~?^^^!^&^&(!necho_! %%~?: '^^^!$^^^!'%$n1c%
%= =%set $=^^^!$:^^^"=""^^^!%= pre-delay =%%$n1c%
%= =%call set "$=%%^^^$:^^^!=#"#"^^^!%%"^^^!%= delay 2-times =%%$n1c%
%= =%if defined ? (set "$=^!$:^^^=^^^^^!"^&set "$=^!$:#"#"=^^^!")else set "$=^!$:^^^=^^^^^^^^^!"^&set "$=^!$:#"#"=^^^^^^^!"%$n1c%
%= =%set $=^^^!$:""=^^^"^^^!%= post-delay =%%$n1c%
%= =%!necho_! %%~?: '^^^!$^^^!'%$n1c%
%= =%!forLineR_! ("^!$^!") do endlocal^&set "%%~?=%%~r"^^^!^&setlocal enableDelayedExpansion%$n1c%
%= =%)%$n1c%
%= =%endlocal^&!rErr!^&!nErr!)else set ?=^^^!^&setlocal enableDelayedExpansion^&set $="
:: )
2>nul %macroEnd%
%EndlocalR_% (
%$%
)
But you are right, as it saves a few bites on disk, but not in memory.
Don't execute this !, I try to post a
small include file for those interested...
Re: Escaping ! < and > within a SET statement that is then p
Posted: 23 Jul 2012 20:41
by colinhay
Thanks for the reply - I'm sure I had tried that but in the end I rewrote the SQL to
where not (policy_id = 0)
