Migrated From v7 => v9 ... and Now Can't Use Semi-Colons in Strings?

[machineghost]

Re: Migrated From v7 => v9 ... and Now Can't Use Semi-Colons inStrings?

Support did call me back yesterday, and it was the same rep I'd spoken
to before. She said that the list she was given (by IBM) of supported
platforms was different than the list of platforms on the link Dave
provided; if that's true IBM really needs to keep their Support people
better informed about their own products ...

Anyhow, she then tried installing v7.2, but wasn't able to reproduce
the non-bug-state (possibly because before, when this bug wasn't
affecting us, we were using v7.1). To be honest, I'm not exactly
clear on why she needs to reproduce the non-bug-state anyway; it seems
to me that if she can reproduce the bug state, that'd be enough,
but ... *shrug*.

I'll post back when I have more to report.

Jeremy

[machineghost]

Re: Migrated From v7 => v9 ... and Now Can't Use Semi-Colons inStrings?

Well after all that, I eventually just had to give up. My boss was
pressuring me to just create a fix already, and even after numerous
email exchanges with the IBM tech the only "help" I could get was her
asking me for various information about our v7 DB (most of which I
couldn't provide, as it's long gone). Quite frankly, I don't
understand why it mattered to her what our old v7 setup was: either
the command line parser is supposed to be doing what it's doing, or
it's a bug. Either way, I shouldn't need to prove to IBM that it is
a bug by providing evidence that it didn't used to be a bug (which is
basically what she was trying to get).

So, I apologize for the unsatisfactory conclusion, but it appears that
there is no way to "fix" this issue in the command line parser other
than to change the termination character (which as I mentioned before,
reduces but does not eliminate the risk). As for me, I'm just going
to write a short Java class to fake the old command line program, as
JDBC does not appear to be affected by this bug.

Thanks again for all the help everyone; I wish I could have said
something like "IBM was great and they promised a fix would be in the
next patch", but *shrug* oh well.

Jeremy

[Ian]

Re: Migrated From v7 => v9 ... and Now Can't Use Semi-Colons inStrings?

machineghost wrote:

Well after all that, I eventually just had to give up. My boss was
pressuring me to just create a fix already, and even after numerous
email exchanges with the IBM tech the only "help" I could get was her
asking me for various information about our v7 DB (most of which I
couldn't provide, as it's long gone). Quite frankly, I don't
understand why it mattered to her what our old v7 setup was: either
the command line parser is supposed to be doing what it's doing, or
it's a bug. Either way, I shouldn't need to prove to IBM that it is
a bug by providing evidence that it didn't used to be a bug (which is
basically what she was trying to get).
>
So, I apologize for the unsatisfactory conclusion, but it appears that
there is no way to "fix" this issue in the command line parser other
than to change the termination character (which as I mentioned before,
reduces but does not eliminate the risk). As for me, I'm just going
to write a short Java class to fake the old command line program, as
JDBC does not appear to be affected by this bug.
>
Thanks again for all the help everyone; I wish I could have said
something like "IBM was great and they promised a fix would be in the
next patch", but *shrug* oh well.

If you're getting an unsatisfactory response, you can always call in and
ask for the duty manager so you can escalate your issue.

I would encourage you to follow the PMR to its conclusion -- if this is
actually a bug, at least it will get fixed (eventually) -- even if
you've already worked around it.

[machineghost]

Re: Migrated From v7 => v9 ... and Now Can't Use Semi-Colons inStrings?

If you're getting an unsatisfactory response, you can always call in and

ask for the duty manager so you can escalate your issue.
>
I would encourage you to follow the PMR to its conclusion -- if this is
actually a bug, at least it will get fixed (eventually) -- even if
you've already worked around it.

I'd really like to, but I'm doing all this on company time, and the
more time I spend helping IBM fix their bugs the less I spend fixing
ours. Also, I'm becoming more and more convinced that this isn't a
bug, at least from IBM's perspective (although IMHO it very obviously
is).

However, I did want to post back here and provide some further info
for anyone else facing with this problem. As I said in my previous
post, I was going to write a Java class so that I could use JDBC
instead of the command line tool ... but then I discovered this page
(the author uses MS SQL, but it works with any JDBC-supporting DB):

http://www.phillnacelli.net/blog/index.cfm/2007/2/8/ANT-Builds-and-MS-SQL--Run-sql-scripts-in-your-build

It explains how one can use Ant to run .sql files, without even
needing to make a class . So I thought "great, I'll just swap one
command line call for another." I setup the ant task, created a
dummy .sql file, tested everything and ... got the SAME ERROR.

So, it looks like either:
A) this bug affects both the command line parser and JDBC, or
B) this isn't a bug; IBM just hates strings that contain termination
characters followed by newlines

Either way, it leaves me (and anyone facing the same issue) with no
option other than to change the termination character. I don't really
like this solution, but due to the constraints of my job and the lack
of interest from IBM, it appears to be the only one.

[machineghost]

Re: Migrated From v7 => v9 ... and Now Can't Use Semi-Colons inStrings?

One last shot in the dark before I give up (for real this time ;-) ).
I stumbled upon this link when I went to lookup the syntax for
switching the termination character:

http://bytes.com/forum/thread184118.html

In it, a guy claims that he was able to escape semi-colons (which
evidently gave him trouble even without a newline) by using this:
'opt1' CONCAT X'3B' CONCAT 'opt2' CONCAT X'3B' CONCAT 'opt3'

However as I've said I'm no DBA, and neither myself nor anyone else in
my office has the foggiest idea what the above does or even is (I
*think* it's either a crazy statement with syntax I don't know, or
some sort of string that can be passed to DB2 via a special command
line call to set environmental variables or something). I tried
Googling it but foudn nothing (besides the above link and something in
Spanish), and I'm sort of afraid to just run random code (even on our
test database). So, does anyone here:
A) know what that does?
B) know if it can somehow solve this issue?

And thanks for the upteenth time to everyone who has responded; I
really wish I could devote more time to working on this with IBM so
that I could give y'all a more satisfying resolution.

Jeremy

[Lennart]

Re: Migrated From v7 => v9 ... and Now Can't Use Semi-Colons inStrings?

On Jun 28, 3:00 am, machineghost <machinegh...@g mail.comwrote:

One last shot in the dark before I give up (for real this time ;-) ).
I stumbled upon this link when I went to lookup the syntax for
switching the termination character:http://bytes.com/forum/thread184118.html
>
In it, a guy claims that he was able to escape semi-colons (which
evidently gave him trouble even without a newline) by using this:
'opt1' CONCAT X'3B' CONCAT 'opt2' CONCAT X'3B' CONCAT 'opt3'
>
However as I've said I'm no DBA, and neither myself nor anyone else in
my office has the foggiest idea what the above does or even is (I
*think* it's either a crazy statement with syntax I don't know, or
some sort of string that can be passed to DB2 via a special command
line call to set environmental variables or something). I tried
Googling it but foudn nothing (besides the above link and something in
Spanish), and I'm sort of afraid to just run random code (even on our
test database). So, does anyone here:
A) know what that does?
B) know if it can somehow solve this issue?
>
And thanks for the upteenth time to everyone who has responded; I
really wish I could devote more time to working on this with IBM so
that I could give y'all a more satisfying resolution.
>
Jeremy

x'3B' is hexadecimal representation of ascii ; (59 decimal). I dont
think this will help you because it means that you must rewrite your
insert statement like:

INSERT INTO fake.table (fakeId, fakeJavaScript) VALUES (5555, '
function fakeFunction() {
doSomething1()' || x'3B' || '
doSomething2()' || x'3B' || '

}');

this is BTW same as:

INSERT INTO fake.table (fakeId, fakeJavaScript) VALUES (5555, '
function fakeFunction() {
doSomething1()' || ';' || '
doSomething2()' || ';' || '

}');

/Lennart

[Dave Hughes]

Re: Migrated From v7 =&gt; v9 ... and Now Can't Use Semi-Colons in Strings?

machineghost wrote:

One last shot in the dark before I give up (for real this time ;-) ).
I stumbled upon this link when I went to lookup the syntax for
switching the termination character:

http://bytes.com/forum/thread184118.html

>
In it, a guy claims that he was able to escape semi-colons (which
evidently gave him trouble even without a newline) by using this:
'opt1' CONCAT X'3B' CONCAT 'opt2' CONCAT X'3B' CONCAT 'opt3'
>
However as I've said I'm no DBA, and neither myself nor anyone else in
my office has the foggiest idea what the above does or even is (I
*think* it's either a crazy statement with syntax I don't know, or
some sort of string that can be passed to DB2 via a special command
line call to set environmental variables or something). I tried
Googling it but foudn nothing (besides the above link and something in
Spanish), and I'm sort of afraid to just run random code (even on our
test database). So, does anyone here:
A) know what that does?
B) know if it can somehow solve this issue?

x'3B' is an example of a "hex-string" [1]. These are typically used
when one wishes to insert binary data into a FOR BIT DATA or BLOB
column, but can be used for escaping purposes too. CONCAT is a synonym
for || [2]. Personally I'd avoid CONCAT (despite the InfoCenter's
recommendation to use it [3]) as it's specific to DB2.

Hence, the following:

'opt1' CONCAT x'3B' CONCAT 'opt2' CONCAT x'3B' CONCAT 'opt3'

is equivalent to:

'opt1' || ';' || 'opt2' || ';' || 'opt3'

Assuming you have some code that generates the INSERT statements in
something like the following manner:

# escape single quotes within the code
code = code.replace("' ", "''");
sql = "INSERT INTO sometable VALUES ('" + code + "');";

You could replace this with:

# escape single quotes within the code
code = code.replace("' ", "''");
# escape semi-colons followed immediately by line breaks in the code
code = code.replace("; \n", "' || X'3B0A' || '");
sql = "INSERT INTO sometable VALUES ('" + code + "');";

However, by far the best solution is to use parameter markers [4]
(assuming they are available in the interface being used; they are
available with things like JDBC, ODBC, CLI, Perl, and PHP but not raw
CLP scripts, directly). In which case, the code would look something
vaguely like:

# no need to escape anything as the code isn't passed "in" the SQL
sql = "INSERT INTO sometable VALUES (?)";
# "prepare" the statement, bind the parameters, and execute it
stmt = prepare(sql);
stmt.bind(1, code);
stmt.execute();

Personally, I'd strongly recommend the parameter marker solution as it
has numerous benefits (performance and security mainly). The only
drawback being that it is not available directly in CLP scripts (which
is the reason I'd still like to see this issue pursued with support -
ultimately it /is/ a bug and should be fixed).

And thanks for the upteenth time to everyone who has responded; I
really wish I could devote more time to working on this with IBM so
that I could give y'all a more satisfying resolution.

To quote Serge's original response:

>Also if support gives you trouble on this one ask them to give me a
>shout.

Did you try this yet? I humbly suggest that it could expedite matters
considerably ;-)


[1]

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l

uw.sql.ref.doc/doc/r0000731.html (see the Character String Constants
sub-section)

[2]

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l

uw.sql.ref.doc/doc/r0000736.html (see the "operator" definition")

[3]

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l

uw.sql.ref.doc/doc/r0000718.html (see footnote 1)

[4]

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l

uw.apdv.routine s.doc/doc/c0020295.html (includes examples for CLI, C,
JDBC, and ADO.NET)


Cheers,

Dave.

[Serge Rielau]

Re: Migrated From v7 =&gt; v9 ... and Now Can't Use Semi-Colons inStrings?

machineghost wrote:

So, it looks like either:
A) this bug affects both the command line parser and JDBC, or
B) this isn't a bug; IBM just hates strings that contain termination
characters followed by newlines

Could you send me the PMR number?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[machineghost]

Re: Migrated From v7 =&gt; v9 ... and Now Can't Use Semi-Colons inStrings?

Could you send me the PMR number?
I suck, and I never write down those trouble ticket number things (for
any company). However, I have sent you alternate information (like
our customer #) which hopefully will serve in its place.

ultimately it /is/ a bug and should be fixed

I agree 100%, and hopefully with Serge's help it can happen. However,
every day that I fail to find a solution is another day a team at my
company (which depends on the automated deployment of SQL that
frequently contains JavaScript) has to waste a lot of their time
manually fixing/deploying their SQL. From my company's perspective,
my focus needs to be on solving things as quickly as possible, and
working with IBM's support team was not achieving that (in fact, it
was achieving the exact opposite).

On the plus side, I mentioned Serge's involvement in the matter to my
boss, and now that someone at IBM is actually interested in solving
the bug (as opposed to making us prove that it used to not be a bug)
he has cleared me to spend some (but not a lot) of time following up
on it, even after I implement a workaround.

Not to be offensive, but...did you try it?
values 'opt1' CONCAT X'3B' CONCAT 'opt2' CONCAT X'3B' CONCAT 'opt3';

Offend away :-) I'm really not strong with any DB-related stuff other
than the querying/inserting/updating (and even then, "strong" is
perhaps too generous of a word), so I didn't even know to add "values"
to the front of that text (all the original author wrote was "I could
do this:" and then he included the text).

Thanks to your, Lenart's, and especially Dave's feedback (thanks for
the links Dave), it sounds like the "fix" the guy was talking about
was just replacing the problematic ";\n" with the equivalent hex
entity. That solution actually works well for our situation, as the
relevant SQL does pass through some programming logic (which I can use
to do the replacement) before it gets run via db2 (in fact, I had
considered escaping the problematic characters before, but couldn't
figure out the syntax to do so since neither "\;" nor ";;" worked).

So, I have a strategy for a workaround that doesn't require changing
the termination character, someone at IBM interested in helping pursue
a fix, and my boss's approval to spend (some) time doing what I can to
also help pursue a fix. Things are looking up :-D

Jeremy

[machineghost]

Re: Migrated From v7 =&gt; v9 ... and Now Can't Use Semi-Colons inStrings?

Just to clarify (for anyone else as clueless as me), the hexcode
character entity stuff isn't actually necessary at all. In fact, the
actual solution to this problem is so mind-numbingly simple I feel
idiotic for not realizing it sooner; just replace:
';\n'
with:
';' || '\n'

And now that I have a solution, I can summarize this entire issue (for
anyone who later reads this thread) as ...

*** Problem ***
INSERT INTO fakeTable (fakeColumn) VALUES (';
');

results in the following (incorrect) error:

db2 =INSERT INTO fakeTable (fakeColumn) VALUES (';
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0010N The string constant beginning with "'" does not have an
ending
string delimiter. SQLSTATE=42603
db2 =')


*** SOLUTION ***
INSERT INTO faketable(fakec olumn) VALUES (';' || '
');

results in the following (correct) error (unless you really do have a
table called "fakeTable" ):

db2 =insert into faketable(fakec olumn) values (';' || '
db2 (cont.) =');
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0204N "DB2INST1.FAKET ABLE" is an undefined name. SQLSTATE=42704



WOO HOO!

[machineghost]

Re: Migrated From v7 =&gt; v9 ... and Now Can't Use Semi-Colons inStrings?

I don't know how Serge's doing with a "real" fix, but I just wanted to
post back very quickly in case anyone else with this same problem
stumbles upon this thread.

It turned out that all of the hex entity stuff was completely
unnecessary (except that it gave me the idea). All you really need to
do is change this:
INSERT INTO fakeTable (fakeColumn) VALUES ('fake;
value');

into:
INSERT INTO fakeTable (fakeColumn) VALUES ('fake;' || '
value');

Seriously, that's all it takes :-) If you're in Java-land, all you
need to do is:
yourQuery.repla ceAll(";\\n", ";'||'\\n")

Of course, it still sucks that you even have to do a workaround at
all, but since we can't help IBM's incompetence (no offense Serge; you
as an individual are clearly very competent), a super simple
workaround like this one is the next best thing.

Jeremy

[Serge Rielau]

Re: Migrated From v7 =&gt; v9 ... and Now Can't Use Semi-Colons inStrings?

Development agrees it's a bug and it will be fixed.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab