Welcome!

By registering with us, you'll be able to discuss, share and private message with other members of our community.

SignUp Now!

Passing % character through command line to .btm

Jun
29
0
Hi

I am currently enhancing the script below that I use to access a sqlite database.

One of the things i often do in my SQL queries is modulo math operations. Unfortunately SQL (as used in sqlite) uses the percentage symbol "%" as the modulo operator.

Within sqlite I do this to get the result I want:
sqlite>
sqlite> select 123456789 % 1000000;
456789
sqlite>

At the TCC prompt I do this:
[C:\Documents and Settings\John\My Documents\Data\proj
16:31>sqlite3 locns.db3 "select 123456789 %% 1000000"
456789

However, at the command line for my script below, I need to do this:
[C:\Documents and Settings\John\My Documents\Data\projects\locations\data]
16:39>locns "select 123456789 %%%%%%%%%%%%%%%% 1000000"

inputline=: "select 123456789 %%%% 1000000"


arg=: "select 123456789 %% 1000000"

sql=: "select 123456789 % 1000000"

opt=:

outputline=: sqlite3 -cmd ".prompt 'locns> ' ' ... > '" locns.db3 "select 123456789 % 1000000"

Press any key when ready...
456789

ie. with 16 "%" symbols to get the result I want.
I know this is to do with how Windows/MSDos uses % to identify variables, but I was wondering if anyone knows how to work around this (if it can be done).

Thanks
John

[script]
@echo off
: locns.btm
: 2011nov11jmcm - open an sqlite shell on locns database from anywhere
: 2012jan03jmcm - or execute query immediately
: 2013nov14jmcm - added sqprompt
: 2014jan21jmcm - expand option processing
:

setlocal
pushd "C:\Documents and Settings\John\My Documents\Data\projects\locations\data\"

echo.
echo inputline=: %$
echo.

do while !%1! != !!
set arg=%1
echo.
echo arg=: %arg
echo.
iff %@lower[%arg] eq -tabs then
set tabs=-separator ^q^t^q
echo tabs=: %tabs
elseiff %@lower[%arg] eq -cmd then
set options=%options %arg
shift
set options=%options %1
echo opt=: %options
elseiff %@lower[%arg] eq -init then
set options=%options %arg
shift
set options=%options %1
echo opt=: %options
elseiff %@lower[%arg] eq -mmap then
set options=%options %arg
shift
set options=%options %1
echo opt=: %options
elseiff %@lower[%arg] eq -nullvalue then
set options=%options %arg
shift
set options=%options %1
echo opt=: %options
elseiff %@lower[%arg] eq -separator then
set options=%options %arg
shift
set options=%options %1
echo opt=: %options
elseiff %@lower[%arg] eq -vfs then
set options=%options %arg
shift
set options=%options %1
echo opt=: %options
elseiff %@left[1,%arg] == ^q then
set sql=%arg
echo sql=: %sql
else
set options=%options %arg
echo opt=: %options
endiff
shift
enddo

echo.
echo opt=: %options
echo.

set sqprompt=-cmd ".prompt 'locns> ' ' ... > '"

echo outputline=: sqlite3 %sqprompt %options %tabs locns.db3 %sql
echo.
pause

sqlite3 %sqprompt %options %tabs locns.db3 %sql

popd
endlocal

[/script]
 
I want to reduce the number of "%" symbols in the command line if possible.

John
Two possible approaches occur to me:

1. You could try using SETDOS /X-3 and /X+3 at appropriate points to turn variable expansion on and off. It wouldn't be pretty and it might not be possible.

2. More likely to be successful, although it might lead to a bit of confusion when you're not using your batch file, you could use a different character or characters in the command, then when parsing the command line substitute %% for your character. For example, you could call the batch file with "select 123456789 mod 1000000", then when you assemble the sql command check for "mod" and replace it with "%" or "%%" as needed.

Hope that helps, Dan.
 
Thanks Dan

I tried the setdos approach last time I played with this a few weeks back - not a success.

I have actually just succeeded in implementing approach 2 (code snippet below), still messy, but I don't have to remember/count how many "%" symbols to include in the query. Result:

[C:\Documents and Settings\John\My Documents\Data\projects\locations\data]
20:40>locns "select 123456789 mod 1000000"

inputline=: "select 123456789 mod 1000000"


arg=: "select 123456789 mod 1000000"

"select 123456789 mod 1000000"
sql=: "select 123456789 mod 1000000"
index=: 17
here
there
"select 123456789 %% 1000000"
"select 123456789 % 1000000"

opt=:

outputline=: sqlite3 -cmd ".prompt 'locns> ' ' ... > '" locns.db3 "select 123456789 % 1000000"

Press any key when ready...
456789

This will keep me going, but I am still interested in other ideas should anyone care to make them.

Now to cleanup the code.

John

[snippet]
elseiff %@left[1,%arg] == ^q then
set sql=%arg
set sql
echo sql=: %sql
echo index=: %@index[%sql, mod ]
iff %@index[%sql, mod ] ge 0 then
echo here
set sql=%@replace[ mod , %%%%%%%% ,%sql]
echo there
endiff
set sql
echo %sql
else
[/snippet]
 
Possible option: the SafeChars.dll plugin from Charles Dye

Thanks Steve for the tip and thanks Charles for the SafeChars plugin.

I have now tried with the %_pct pseudo variable from the SafeChars plugin with the following results:

[[email protected]]
[C:\Documents and Settings\John\My Documents\Data]
Wed 10:42>locns "select 123456789 mod 1000000"

linein=: "select 123456789 mod 1000000"

arg=: "select 123456789 mod 1000000"

sql1=: "select 123456789 mod 1000000"

sql2=: "select 123456789 % 1000000"

lineout=: sqlite3 -cmd ".prompt 'locns> ' ' ... > '" locns.db3 "select 123456789 % 1000000"
Press any key when ready...
456789

[[email protected]]
[C:\Documents and Settings\John\My Documents\Data]
Wed 10:43>locns "select 123456789 %_pct 1000000"

linein=: "select 123456789 % 1000000"

arg=: "select 123456789 % 1000000"

sql1=: "select 123456789 % 1000000"

sql2=: "select 123456789 % 1000000"

lineout=: sqlite3 -cmd ".prompt 'locns> ' ' ... > '" locns.db3 "select 123456789 % 1000000"
Press any key when ready...
456789

[[email protected]]
[C:\Documents and Settings\John\My Documents\Data]
Wed 10:44>locns "select cust1 from customers where cust1 like '%_pct%jones%_pct'"

linein=: "select cust1 from customers where cust1 like '%jones%'"

arg=: "select cust1 from customers where cust1 like '%jones%'"

sql1=: "select cust1 from customers where cust1 like '%jones%'"

sql2=: "select cust1 from customers where cust1 like '%jones%'"

lineout=: sqlite3 -cmd ".prompt 'locns> ' ' ... > '" locns.db3 "select cust1 from customers where cust1 like '%jones%
'"
Press any key when ready...
xxxxxxxx JONES
...

It even works as SQL wildcard, however when used as the leading wild card (as above) it needs a trailing % symbol to separate it from the following text (ie. '%_pct%jones%_pct' not '%_pctjones%_pct'). IIRC this is mentioned somewhere in the JPSoft docs, haven't read that part for a long time.

John
 

Similar threads

Back
Top