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]

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

On Jun 20, 3:04 am, machineghost <machinegh...@g mail.comwrote:

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]

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

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]

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

On Jun 20, 9:02 am, Ian <ianb...@mobile audio.comwrote:

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]

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

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('SA MPLE')
>>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.i xf" 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;IMPOR T") 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]

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

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]

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

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]

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

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]

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

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]

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

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]

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

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/wikis/display/im/DB2+9.1+Recommended+L

inux+Environmen ts

[2]

http://www.ibm.com/developerworks/wikis/display/im/DB2+9.5+for+Linux+-+S

upported+Enviro nments


Cheers,

Dave.

[Dave Hughes]

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

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]

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

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]

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

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]

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

On Jun 21, 3:01 pm, Serge Rielau <srie...@ca.ibm .comwrote:

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