SQLTool question

--===============5331463710119241283==
Content-Type: multipart/alternative; boundary="000000000000177de905e6822258"

--000000000000177de905e6822258
Content-Type: text/plain; charset="UTF-8"

Hello
I am using HSQLDB v2.7.0, and attempting to use SQLTool macros for the
first time.  Below is an example where a macro parameter is changed, which
causes execution to fail.  Specifically, a space is injected prior to the
closing parenthesis in the phone number.  Wondering if I am doing something
wrong, or if there is a bug here. Please advise.
Cheers,
Andrew Richardson


SqlTool v. 6559.
JDBC Connection established to a HSQL Database Engine v. 2.7.0 database
as "BOARS" with R/W TRANSACTION_READ_COMMITTED Isolation.
SqlFile processor v. 6559.
Distribution is permitted under the terms of the HSQLDB license.
(c) 2004-2011 Blaine Simpson and the HSQL Development Group.

    \q    to Quit.
    \?    lists Special Commands.
    :?    lists Edit-Buffer/History commands.
    *?    lists PL commands.
    /?    displays help on how to set and use macros (incl. functions).

SPECIAL Commands begin with '\' and execute when you hit ENTER.
EDIT-BUFFER / HISTORY Commands begin with ':' and execute when you hit
ENTER.
PROCEDURAL LANGUAGE commands begin with '*' and end when you hit ENTER.
MACRO executions and definitions begin with '/' and end when you hit ENTER.
All other lines comprise SQL Statements (or comments).
  SQL Statements are terminated by either unquoted ';' (which executes the
  statement), or a blank line (which moves the statement into the edit
buffer
  without executing).
After turning on variable expansion with command "*" (or any other PL
command), PL variables may be used in most commands like so:  *{PLVARNAME}.
Be aware when using regular expressions on commands, that the regex.s
operate only on the command text after the * or \ prefix, if any.

sql> /=
wfres() = select * from WR where "CustomerID" in (select ID from CU where
"PhoneNumber" = *{1} )
cgres() = select * from CR where "CustomerID" in (select ID from CU where
"PhoneNumber" = *{1} )

sql> /wfres('(207) 717-5444')

sql> :b
Edit buffer contents:
"select * from WR where "CustomerID" in (select ID from CU where
"PhoneNumber" = *'(207 )* 717-5444')"
sql>

--000000000000177de905e6822258
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello</div><div>I am using HSQLDB v2.7.0, and attempt=
ing to use SQLTool macros for the first time.=C2=A0 Below is an example whe=
re a macro parameter is changed, which causes execution to fail.=C2=A0 Spec=
ifically, a space is injected prior to the closing parenthesis in the phone=
 number.=C2=A0 Wondering if I am doing something wrong, or if there is a bu=
g here. Please advise.</div><div>Cheers,</div><div>Andrew Richardson<br></d=
iv><div><br></div><div><br></div><div><span style=3D"font-family:monospace"=
>SqlTool v. 6559.<br>JDBC Connection established to a HSQL Database Engine =
v. 2.7.0 database<br>as &quot;BOARS&quot; with R/W TRANSACTION_READ_COMMITT=
ED Isolation.<br>SqlFile processor v. 6559.<br>Distribution is permitted un=
der the terms of the HSQLDB license.<br>(c) 2004-2011 Blaine Simpson and th=
e HSQL Development Group.<br><br>=C2=A0 =C2=A0 \q =C2=A0 =C2=A0to Quit.<br>=
=C2=A0 =C2=A0 \? =C2=A0 =C2=A0lists Special Commands.<br>=C2=A0 =C2=A0 :? =
=C2=A0 =C2=A0lists Edit-Buffer/History commands.<br>=C2=A0 =C2=A0 *? =C2=A0=
 =C2=A0lists PL commands.<br>=C2=A0 =C2=A0 /? =C2=A0 =C2=A0displays help on=
 how to set and use macros (incl. functions).<br><br>SPECIAL Commands begin=
 with &#39;\&#39; and execute when you hit ENTER.<br>EDIT-BUFFER / HISTORY =
Commands begin with &#39;:&#39; and execute when you hit ENTER.<br>PROCEDUR=
AL LANGUAGE commands begin with &#39;*&#39; and end when you hit ENTER.<br>=
MACRO executions and definitions begin with &#39;/&#39; and end when you hi=
t ENTER.<br>All other lines comprise SQL Statements (or comments).<br>=C2=
=A0 SQL Statements are terminated by either unquoted &#39;;&#39; (which exe=
cutes the<br>=C2=A0 statement), or a blank line (which moves the statement =
into the edit buffer<br>=C2=A0 without executing).<br>After turning on vari=
able expansion with command &quot;*&quot; (or any other PL<br>command), PL =
variables may be used in most commands like so: =C2=A0*{PLVARNAME}.<br>Be a=
ware when using regular expressions on commands, that the regex.s<br>operat=
e only on the command text after the * or \ prefix, if any.<br></span></div=
><div><span style=3D"font-family:monospace"><br></span></div><div><span sty=
le=3D"font-family:monospace">sql&gt; /=3D<br>wfres() =3D select * from WR w=
here &quot;CustomerID&quot; in (select ID from CU where &quot;PhoneNumber&q=
uot; =3D *{1} )<br>cgres() =3D select * from CR where &quot;CustomerID&quot=
; in (select ID from CU where &quot;PhoneNumber&quot; =3D *{1} )<br></span>=
</div><div><span style=3D"font-family:monospace"><br></span></div><div><spa=
n style=3D"font-family:monospace">sql&gt; /wfres(&#39;(207) 717-5444&#39;)<=
br></span></div><div><span style=3D"font-family:monospace"><br></span></div=
><div><span style=3D"font-family:monospace">sql&gt; :b<br>Edit buffer conte=
nts:<br>&quot;select * from WR where &quot;CustomerID&quot; in (select ID f=
rom CU where &quot;PhoneNumber&quot; =3D <span style=3D"color:rgb(255,0,0)"=
><b>&#39;(207 )</b></span> 717-5444&#39;)&quot;<br>sql&gt;</span></div></di=
v>

--000000000000177de905e6822258--


--===============5331463710119241283==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline


--===============5331463710119241283==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

--===============5331463710119241283==--