Prev: Urgent - need help
Next: Community's water tower perhaps somewhat in questionable taste
From: Frank Swarbrick on 27 Jun 2008 23:14
>>> On 6/27/2008 at 7:00 PM, in message
<fe715a94-6138-4934-9ff7-aa4e4d2f5e7e(a)c19g2000prf.googlegroups.com>,
machineghost<machineghost(a)gmail.com> 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?
>
> 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.

Not to be offensive, but...did you try it?

values 'opt1' CONCAT X'3B' CONCAT 'opt2' CONCAT X'3B' CONCAT 'opt3';

returns:

1
--------------
opt1;opt2;opt3

1 record(s) selected.

3B is hex for the semicolon character. CONCAT concatenates the string to
the left of it with the string to the right.

I haven't been following carefully, but I Think you said you wanted a string
followed by a semicolon, a CRLF and then another string?

So how about...
values 'string1' CONCAT x'3b0d0a' CONCAT 'string2';

1
-----------------
string1;
string2

1 record(s) selected.

(Note, values is used just to display back the results, it's not part of the
expression itself.)

Frank
From: Frank Swarbrick on 2 Jul 2008 12:07
>>> On 6/30/2008 at 12:23 PM, in message
<ee1702e5-781a-469a-ae19-f70eb4c8fad2(a)u36g2000prf.googlegroups.com>,
machineghost<machineghost(a)gmail.com> wrote:
> 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!

Did you do something special to get this "(cont.)" result? I get the same
thing you were getting with your original method:

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

Are you using a scripting language to drive the CLP somehow? I was just
typing directly into the CLP.

Just wondering...

Frank

 | 
Pages: 1
Prev: Urgent - need help
Next: Community's water tower perhaps somewhat in questionable taste