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

[machineghost]

First off, let me just say that as someone with no DBA training
whatsoever, any help I can get with this issue will be very, very much
appreciated.

My company recently migrated our database from DB2 v7 to DB2 v9. We
hired a consultant to help us, and things went pretty smoothly ... up
until a few weeks after, when a co-worker tried to insert JavaScript
in to our database. That's when we learned that v9, unlike v7, has a
problem with statements such as this one:

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

Back in v7, I could run "db2 -t", copy/paste that exact code, and the
new record would be created without issue. In v9 however, I find that
the database interprets the above as three separate statements:

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

#2
doSomething2();

#3
}');

Because it recognizes the semi-colon as a termination character even
though it is part of a string literal. This new behavior was
frustrating to discover, but what was even more frustrating was that
(even after I spent several hours Googling) I was unable to find any
way at all to reverse it. In fact, the only "solution" I was able to
find was "change your termination character".

Unfortunately, changing the termination character doesn't really solve
the problem, because I can't guarantee that the character I change it
to won't someday be used in a string literal (in fact, I can pretty
much guarantee the exact opposite; we use lots of weird characters).
Thus, the only real solution I can see is if I can find a way to tell
DB2 to let quotes trump termination characters.

Does such an option, or anything even close, exist? And if not, is
there perhaps some other way of resolving this issue that I haven't
considered? Of course, answers/suggestions would be great, but even
any further background information would really help me out.

Thanks,
Jeremy

[Lennart]

On Jun 20, 3:04 am, machineghost <[e-mail]:

First off, let me just say that as someone with no DBA training
whatsoever, any help I can get with this issue will be very, very much
appreciated.

My company recently migrated our database from DB2 v7 to DB2 v9. We
hired a consultant to help us, and things went pretty smoothly ... up
until a few weeks after, when a co-worker tried to insert JavaScript
in to our database. That's when we learned that v9, unlike v7, has a
problem with statements such as this one:

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

}');

Back in v7, I could run "db2 -t", copy/paste that exact code, and the
new record would be created without issue. In v9 however, I find that
the database interprets the above as three separate statements:

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

#2
doSomething2();

#3

}');

Because it recognizes the semi-colon as a termination character even
though it is part of a string literal. This new behavior was
frustrating to discover, but what was even more frustrating was that
(even after I spent several hours Googling) I was unable to find any
way at all to reverse it. In fact, the only "solution" I was able to
find was "change your termination character".

Unfortunately, changing the termination character doesn't really solve
the problem, because I can't guarantee that the character I change it
to won't someday be used in a string literal (in fact, I can pretty
much guarantee the exact opposite; we use lots of weird characters).
Thus, the only real solution I can see is if I can find a way to tell
DB2 to let quotes trump termination characters.

Does such an option, or anything even close, exist? And if not, is
there perhaps some other way of resolving this issue that I haven't
considered? Of course, answers/suggestions would be great, but even
any further background information would really help me out.

Thanks,
Jeremy

This behavior exists in 9.5 as well. The problem seem to exists when ;
is at the end of a line. As a workaround you could try:

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

It sure looks like a bug, so I think you should open a pmr. Please do
report back here whether it is a bug or if it works as intended.

/Lennart

[Ian]

machineghost wrote:

First off, let me just say that as someone with no DBA training
whatsoever, any help I can get with this issue will be very, very much
appreciated.

My company recently migrated our database from DB2 v7 to DB2 v9. We
hired a consultant to help us, and things went pretty smoothly ... up
until a few weeks after, when a co-worker tried to insert JavaScript
in to our database. That's when we learned that v9, unlike v7, has a
problem with statements such as this one:

I suspect that the CLP (the 'db2' command line processor) probably got
a little more strict in V8 due to all of the new features.

The solution is to change your statement terminator. Use an '@' or
something else...

INSERT INTO fake.table (fakeId, fakeJavaScript) VALUES (5555, '
function fakeFunction() {
doSomething1();
doSomething2();
}')@
Then, run with 'db2 -td@ -f <file>'

[Lennart]

On Jun 20, 9:02 am, Ian <[e-mail]:

machineghost wrote:

First off, let me just say that as someone with no DBA training
whatsoever, any help I can get with this issue will be very, very much
appreciated.

My company recently migrated our database from DB2 v7 to DB2 v9. We
hired a consultant to help us, and things went pretty smoothly ... up
until a few weeks after, when a co-worker tried to insert JavaScript
in to our database. That's when we learned that v9, unlike v7, has a
problem with statements such as this one:

I suspect that the CLP (the 'db2' command line processor) probably got
a little more strict in V8 due to all of the new features.

The solution is to change your statement terminator. Use an '@' or
something else...

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

}')@

Then, run with 'db2 -td@ -f <file>'

I'm afraid that wont help in general. Assume a codesnippet like:

INSERT INTO fake.table (fakeId, fakeJavaScript)
VALUES (5555, 'function fakeFunction() {
doSomething1(); // jadajada @
doSomething2(); //
}') @

i.e. a line that ends with stmt terminator. To me it looks like a bug
in the lexical parser, but I'm not sure how it is supposed to work.

/Lennart

[Dave Hughes]

Lennart wrote:

[snip]

i.e. a line that ends with stmt terminator. To me it looks like a bug
in the lexical parser, but I'm not sure how it is supposed to work.

I agree this definitely looks like a bug. However, the CLP parser is a
strange beastie to say the least. Before I veer off on a long tangent
about just how weird the CLP's parser is, here's a suggestion for the
OP:

While I doubt you'll be able to work around this "properly" in the CLP,
you shouldn't have any trouble with statements including multi-line
strings with semi-colons at the end in other interfaces, e.g. ODBC,
JDBC, CLI, etc. especially as these interfaces can only execute a
single statement at a time and have no concept of a statement
terminator (the terminator is simply the end of the string containing
the statement).

For example:

$ db2 "CREATE TABLE TEST (S VARCHAR(1000) NOT NULL)"
DB20000I The SQL command completed successfully.
$ python
Python 2.5.1 (r251:54863, Oct 5 2007, 13:50:07)
[GCC 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)] on linux2
Type "help", "copyright", "credits" or "license" for more information.

>>import DB2
conn = DB2.connect('SAMPLE')
cur = conn.cursor()
cur.execute("""INSERT INTO TEST (S) VALUES ('function

fakeFunction() {
.... doSomething1();
.... doSomething2();
.... }')""")
1

>>conn.commit()
cur.close()
conn.close()
^D

$ db2 "select * from test2"

S

----------------------------------------------------------------...
function fakeFunction() {
doSomething1();
doSomething2();
}
1 record(s) selected.
So ... the weirdness of the CLP's parser ...

A while back I wrote a parser for DB2's SQL dialect, i.e. just the
statements listed under the SQL reference in the InfoCenter. This went
reasonably smoothly, with no surprises (except the sheer size of the
damned thing). Then I wanted to add the CLP commands to the parser (the
statements listed under the Commands topic of the InfoCenter before
they got moved somewhere in 9.5). This turned out to be nightmarishly
difficult as the CLP commands have fundamentally different syntax.

Consider the EXPORT command. You can use any of the following
legitimately with it:

EXPORT TO filename.ixf OF IXF ...
EXPORT TO 'filename.ixf' OF IXF ...
EXPORT TO "filename.ixf" OF IXF ...
EXPORT TO C:\filename.ixf OF IXF ...

So, what's the syntax? In SQL terms, the filename can be a string
literal, a quoted identifier, an unquoted identifier, or a sequence of
unquoted identifiers, operators (.), and characters that are generally
illegal in SQL (: and \). If you think that's weird, try the following:

$ db2 -t "EXPORT TO ;.IXF OF IXF SELECT * FROM SOMETABLE;"

It shouldn't work. The EXPORT command should be terminated at the first
semi-colon. But it /does/ work (and has for some time now - since v7 at
least, I think). You can get a hint as to how the CLP is parsing stuff
by breaking the statement above (remove the TO)

$ db2 -t "EXPORT ;.IXF OF IXF..."
SQL0104N An unexpected token ";.IXF" was found following "EXPORT".
Expected tokens may include: "TO". SQLSTATE=42601

So ;.IXF is considered a single token. For another hint, stick a space
in the filename and quote it:

$ db2 -t "EXPORT '; .IXF' OF IXF..."
SQL0104N An unexpected token "'; .IXF'" was found following "EXPORT".
Expected tokens may include: "TO". SQLSTATE=42601

So '; .IXF' is also a single token. It looks like the CLP tokenizer is
just doing whitespace splitting, with some extra logic to handle quoted
stuff. I wouldn't be too surprised if the CLP used a regex similar to
the following to tokenize its input:

("([^"]|"")*"|'([^']|'')*'|[^ ]+)

Given this, how does it recognize a statement terminator? Are
terminators at the end of a token are counted?

$ db2 -t "EXPORT TO IXF; OF IXF SELECT * FROM TEST;"

No - that works just fine (exports to a file called "IXF;"). Are
terminators at the end of a statement counted? (i.e. where a parser
would "expect" to find them):

$ db2 -t "EXPORT TO TEST.IXF OF IXF SELECT * FROM TEST;IMPORT FROM
TEST.IXF OF IXF REPLACE INTO TEST;"

No - this fails complaining about the table name ("TEST;IMPORT") being
invalid. Given the ambiguities of SELECT in SQL, I tried reversing the
statments above, and again it complains (in a slightly different
manner) that TEST;EXPORT is an invalid table name. Adding spaces after
the terminators makes no difference either (well, it changes the errors
but they still aren't correctly recognized as terminators).

Basically, terminators are recognized if and only if they occur at the
end of a line, so there's some line-wise logic in there too. I'm quite
surprised that multi-line strings containing semi-colon at the end of a
line within the string worked in v7. Of all the ways I can imagine the
CLP's parser being written (given the behaviour above), a change that
broke this functionality is not a subtle or simple one - I /think/ it
would have to involve the removal of quite a bit of code.

At the end of the day, I would have much preferred it if the CLP had a
"real" parser, even if that meant that things like filenames (or
usernames or passwords) had to be specified as string literals. The
language would be more strict, but at least edge cases like multi-line
strings with terminators, or filenames containing terminators, or
multiple statements on a line, would behave predictably. Unfortunately
I suspect the demands of backward compatibility mean it's unlike to
happen.

Still, I'd also recommend opening a PMR for this issue. It should be
made to work (even if it does mean the CLP's "parser" gets even more
weird ;-).
Cheers,

Dave.

[Will Honea]

machineghost wrote:

First off, let me just say that as someone with no DBA training
whatsoever, any help I can get with this issue will be very, very much
appreciated.

My company recently migrated our database from DB2 v7 to DB2 v9. We
hired a consultant to help us, and things went pretty smoothly ... up
until a few weeks after, when a co-worker tried to insert JavaScript
in to our database. That's when we learned that v9, unlike v7, has a
problem with statements such as this one:

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

Back in v7, I could run "db2 -t", copy/paste that exact code, and the
new record would be created without issue. In v9 however, I find that
the database interprets the above as three separate statements:

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

#2
doSomething2();

#3
}');

Because it recognizes the semi-colon as a termination character even
though it is part of a string literal. This new behavior was
frustrating to discover, but what was even more frustrating was that
(even after I spent several hours Googling) I was unable to find any
way at all to reverse it. In fact, the only "solution" I was able to
find was "change your termination character".

Unfortunately, changing the termination character doesn't really solve
the problem, because I can't guarantee that the character I change it
to won't someday be used in a string literal (in fact, I can pretty
much guarantee the exact opposite; we use lots of weird characters).
Thus, the only real solution I can see is if I can find a way to tell
DB2 to let quotes trump termination characters.

Does such an option, or anything even close, exist? And if not, is
there perhaps some other way of resolving this issue that I haven't
considered? Of course, answers/suggestions would be great, but even
any further background information would really help me out.

I can't recall the exact syntax, but I fixed that with a tip that appeared
on this forum a couple of months back. There is a parameter you can set
that reverts the "new" parsing to that used by previous versions so that
the embedded semi-colon is interpreted the same way V7 did, as are embedded
EOL markers. I had to use it to import a bunch of FoxPro exports which
included EOL markers in text blobs enclosed inside quoted strings. I may
be wrong, but the reversion apparently applies to all quote-enclosed
special characters.

I'll see if I can find the notes when I get back to the server but you
should be able to track it down from this info - or maybe it will jog some
memories on the list here.

--
Will Honea
** Posted from http://www.teranews.com **

[machineghost]

Wow, thanks for the replies everyone. It seems I'm facing a genuine
bug here, and that my only real options are to:

A) Set a character that I believe to be the rarest/least used as our
termination character, modify all my SQL-generating code to use this
new termination character, and then hope/pray that we never have any
SQL which includes this character followed by a newline.

B) Re-architect our system to run the SQL code via Java, rather than
at the command line.

Both will be a pain, but option B) seems like a more stable/
predictable fix, so I guess I'd better start working on it :-)

Thanks again for all the help,
Jeremy

[machineghost]

I'll see if I can find the notes when I get back to the server but you

should be able to track it down from this info - or maybe it will jog some
memories on the list here.

I tried searching the list for the post you mentioned, but was unable
to find anything. The only parameters I am aware of are the ones you
get from db2 ? OPTIONS:

-a Display SQLCA
-c Auto-commit
-d Retrieve and display XML declarations
-e Display SQLCODE/SQLSTATE
-f Read from input file
-i Display XML data with indentation
-l Log commands in history file
-m Display the number of rows affected
-n Remove new line character
-o Display output
-p Display db2 interactive prompt
-q Preserve whitespaces and linefeeds
-r Save output report to file
-s Stop execution on command error
-t Set statement termination character
-v Echo current command
-w Display FETCH/SELECT warning messages
-x Suppress printing of column headings
-z Save all output to output file

I have tried using -q and -n, but neither resulted in the behavior you
described, and none of the other options seem appropriate.

I would be extremely grateful if you could provide the syntax for the
'revert "new" parsing' option you described, or even some suggestion
as to how I might find the post you referred to.

Jeremy

[Serge Rielau]

machineghost wrote:

Wow, thanks for the replies everyone. It seems I'm facing a genuine
bug here, and that my only real options are to:

A) Set a character that I believe to be the rarest/least used as our
termination character, modify all my SQL-generating code to use this
new termination character, and then hope/pray that we never have any
SQL which includes this character followed by a newline.

B) Re-architect our system to run the SQL code via Java, rather than
at the command line.

Both will be a pain, but option B) seems like a more stable/
predictable fix, so I guess I'd better start working on it :-)

C) Call support
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[machineghost]

C) Call support
Why didn't I think of that? I guess I use too much (often
unsupported) open source software ...

Anyhow, I did call Support as soon as I saw your thread, but it ended
up being fruitless. We're running our DB2 on Ubuntu Linux, which
evidently is not a supported distribution. I wouldn't think that the
distribution would matter, but whether it does or not is irrelevant as
far as the IBM Support is concerned. Which means I'm back to choosing
between A) and B) ...

.... unless Will responds with potential option D)?
(Please?)

Jeremy

[Dave Hughes]

machineghost wrote:

C) Call support

Why didn't I think of that? I guess I use too much (often
unsupported) open source software ...

Anyhow, I did call Support as soon as I saw your thread, but it ended
up being fruitless. We're running our DB2 on Ubuntu Linux, which
evidently is not a supported distribution. I wouldn't think that the
distribution would matter, but whether it does or not is irrelevant as
far as the IBM Support is concerned. Which means I'm back to choosing
between A) and B) ...

On the contrary: Ubuntu 6.06 (Dapper) is validated with DB2 9 [1],
Ubuntu 7.04 (Edgy) is validated with DB2 9.5, while Ubuntu 7.10
(Feisty) is supported with DB2 9.5 [2].

Are you perhaps using 8.04 (Gutsy), the latest LTS release? If so, grab
a spare box, throw Feisty and DB2 on there and reproduce the error
(obviously it's not going to change anything, but it ought to be
sufficient to satisfy the support droids).

[1]
http://www.ibm.com/developerworks/wi...+Recommended+L
inux+Environments

[2]
http://www.ibm.com/developerworks/wi...+for+Linux+-+S
upported+Environments
Cheers,

Dave.

[Dave Hughes]

Dave Hughes wrote:

On the contrary: Ubuntu 6.06 (Dapper) is validated with DB2 9 [1],
Ubuntu 7.04 (Edgy) is validated with DB2 9.5, while Ubuntu 7.10
(Feisty) is supported with DB2 9.5 [2].

Are you perhaps using 8.04 (Gutsy), the latest LTS release? If so,
grab a spare box, throw Feisty and DB2 on there and reproduce the
error (obviously it's not going to change anything, but it ought to be
sufficient to satisfy the support droids).

Ooops - got those names wrong: 7.04=Feisty, 7.10=Gutsy, 8.04=Hardy
(forgot about 6.10 which was Edgy :-)

[Will Honea]

machineghost wrote:

>I'll see if I can find the notes when I get back to the server but you
should be able to track it down from this info - or maybe it will jog
some memories on the list here.

I tried searching the list for the post you mentioned, but was unable
to find anything. The only parameters I am aware of are the ones you
get from db2 ? OPTIONS:

-a Display SQLCA
-c Auto-commit
-d Retrieve and display XML declarations
-e Display SQLCODE/SQLSTATE
-f Read from input file
-i Display XML data with indentation
-l Log commands in history file
-m Display the number of rows affected
-n Remove new line character
-o Display output
-p Display db2 interactive prompt
-q Preserve whitespaces and linefeeds
-r Save output report to file
-s Stop execution on command error
-t Set statement termination character
-v Echo current command
-w Display FETCH/SELECT warning messages
-x Suppress printing of column headings
-z Save all output to output file

I have tried using -q and -n, but neither resulted in the behavior you
described, and none of the other options seem appropriate.

I would be extremely grateful if you could provide the syntax for the
'revert "new" parsing' option you described, or even some suggestion
as to how I might find the post you referred to.

I think I may have missed exactly what you are doing, so this may not help.
I was doing imports and this causes the inport function to parse just as V7
did so that it ignored special characters enclosed within "..." strings:

db2 "import from file.csv of del modified by delprioritychar insert
into T"

What you want sounds more like the escape character. The best example of
that is a name string like O'Grady has to be written O''Grady in a query.
Look at the escape sequence info in the SQL reference.

--
Will Honea
** Posted from http://www.teranews.com **

[Serge Rielau]

machineghost wrote:

>C) Call support

Why didn't I think of that? I guess I use too much (often
unsupported) open source software ...

Anyhow, I did call Support as soon as I saw your thread, but it ended
up being fruitless. We're running our DB2 on Ubuntu Linux, which
evidently is not a supported distribution. I wouldn't think that the
distribution would matter, but whether it does or not is irrelevant as
far as the IBM Support is concerned. Which means I'm back to choosing
between A) and B) ...

... unless Will responds with potential option D)?
(Please?)

As Dave noted nothing easier to repro on a definitely supported
platform... Say your Windows laptop.
Also if support gives you trouble on this one ask them to give me a shout.

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

[machineghost]

On Jun 21, 3:01 pm, Serge Rielau <[e-mail]:

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

Serge, you rock! And so do the rest of you, thank you all for your
help. IBM Support is supposed to call me back "ASAP", and when they
do I'll post whatever I find out.

Jeremy

[machineghost]

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]

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]

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]

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/ind...-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]

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]

On Jun 28, 3:00 am, machineghost <[e-mail]:

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]

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/infoce.../com.ibm.db2.l
uw.sql.ref.doc/doc/r0000731.html (see the Character String Constants
sub-section)

[2]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.sql.ref.doc/doc/r0000736.html (see the "operator" definition")

[3]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.sql.ref.doc/doc/r0000718.html (see footnote 1)

[4]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.apdv.routines.doc/doc/c0020295.html (includes examples for CLI, C,
JDBC, and ADO.NET)
Cheers,

Dave.

[Serge Rielau]

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]

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]

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(fakecolumn) VALUES (';' || '
');

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

db2 =insert into faketable(fakecolumn) 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.FAKETABLE" is an undefined name. SQLSTATE=42704

WOO HOO!

[machineghost]

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.replaceAll(";\\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]

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

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