Passing % character through command line to .btm

Jun 11, 2008
29
0
Australia
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]
 
Feb 26, 2013
100
0
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.
 
Jun 11, 2008
29
0
Australia
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]
 
Jun 11, 2008
29
0
Australia
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:

[John@192.168.1.100]
[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

[John@192.168.1.100]
[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

[John@192.168.1.100]
[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
Thread starter Title Forum Replies Date
vefatica TCC passing expanded envvar to children? Support 3
fpefpe How to? Passing arg to gosub with white spaces Support 2
nickles Passing a variable to a command w/o adding it to the local environment Support 7
M Just an argument-passing "strangeness" that I didn't expect... Support 19
T Passing parameters from a file Support 4
vefatica WAD TCC: inconsistent character handling Support 11
vefatica @INT with comma as decimal character Support 2
vefatica What's this character? Support 12
rps Regex problem: \xnn not recognized as a hex character Support 0
old coot Regex problem: \xnn not recognized as a hex character Support 12
L How to? Character transfer among @BALLOC buffers, command tails, and possibly redirected standard streams Support 11
D How to? Filenames with "&" character Support 3
samintz WAD Copy/append adds a character Support 3
B Fixed FFIND doesn't find last character in file without CR/LF at EOF Support 1
M I'm unable to use @replace[] with a % character in string2 Support 12
Exolon V17 beta, debugger: STRG-S is inserting character instead of saving Support 12
M Fixed character set in dir/copy Support 3
dcantor Fixed Ctrl+Shift+Insert repeats last character Support 3
D Copy to clip changes character Support 24
S Fixed Build 48 - Non-ASCII Character mishandling Support 3
W Ascii character output Support 12
nickles Literal tab character Support 21
R One character alias prevents completion? Support 8
S Forum character encoding Support 2
M Escape-character issue... Support 7
vefatica @SUBST ... how replace character with a caret? Support 9
M Programmatically getting the console width in character Support 18
J How to pass a character like " " to a function Support 3
Jay Sage Dealing With Filenames Containing Slash Character Support 5
B A swedish character sent through keystack gets converted Support 4
nikbackm Alias expansion and switch character Support 2
D How to use multi-line DO to loop through first level directory names Support 5
L Using TCC.exe through an SSH connection Support 3
J Piping ANSI control sequences through 'type' Support 4
R How to? Launch and execute commands in tcmd.exe through MSBuild Script. Support 3
vefatica @TRUENAME not seeing through junction Support 11
M How to? How do I read a Unicode file through standard-input? Support 4
Emilio III Control characters pass through? Support 6
J Configure SendMail to go through GMAIL as SMTP server Support 10

Similar threads