Escaping semicolon embedded in string values in db2sql92

[Mark Hansen]

Friends,

I'm using db2sql92 to execute an SQL script that is performing
an insert operation on a table in a DB2, version 8.2 database server.

The string value contains embedded semicolon characters, and I can't
figure out how to escape these. Here is a sample statement:

INSERT INTO mytable VALUES(1, 'opt1;opt2;opt3');

This fails with an error that the String 'opt1' is not properly
terminated:

Statement # 1 :
insert into mytable VALUES (1, 'opt1

SQL0010N The string constant beginning with "'opt1" does not have an ending
string delimiter. SQLSTATE=42603

I've tried various ways of escaping the semicolons, but can't find the
magic rule. I've looked in the DB2 SQL reference manuals, as well as
the documentation on the db2sql92 utility, but no clues.

How do I do this?

By the way, I'm using db2sql92 only because it is the only tool
I've found to execute SQL scripts. Is there a better command-line
utility that I should be using? I've found a number of silly
problems with this tool (like the one above) that make me think
it may be old and no longer kept up to date.
--
Mark Hansen
Sacramento, CA

[Mark A]

"Mark Hansen" <me*@NOSPAMunify.com> wrote in message
news:11*************@corp.supernews.com...

Friends,

I'm using db2sql92 to execute an SQL script that is performing
an insert operation on a table in a DB2, version 8.2 database server.

The string value contains embedded semicolon characters, and I can't
figure out how to escape these. Here is a sample statement:

INSERT INTO mytable VALUES(1, 'opt1;opt2;opt3');

This fails with an error that the String 'opt1' is not properly
terminated:

Statement # 1 :
insert into mytable VALUES (1, 'opt1

SQL0010N The string constant beginning with "'opt1" does not have an ending string delimiter. SQLSTATE=42603

I've tried various ways of escaping the semicolons, but can't find the
magic rule. I've looked in the DB2 SQL reference manuals, as well as
the documentation on the db2sql92 utility, but no clues.

How do I do this?

By the way, I'm using db2sql92 only because it is the only tool
I've found to execute SQL scripts. Is there a better command-line
utility that I should be using? I've found a number of silly
problems with this tool (like the one above) that make me think
it may be old and no longer kept up to date.
--
Mark Hansen
Sacramento, CA

I am not sure about db2sql92 but when using the DB2 command line, most
people use:

db2 -td@ -vf file-name

You just replace the legitimate semi-colons with an "@".

[Mark Hansen]

On 2/18/2005 14:32, Mark Hansen wrote:

Friends,

I'm using db2sql92 to execute an SQL script that is performing
an insert operation on a table in a DB2, version 8.2 database server.

The string value contains embedded semicolon characters, and I can't
figure out how to escape these. Here is a sample statement:

INSERT INTO mytable VALUES(1, 'opt1;opt2;opt3');

This fails with an error that the String 'opt1' is not properly
terminated:

Statement # 1 :
insert into mytable VALUES (1, 'opt1

SQL0010N The string constant beginning with "'opt1" does not have an ending
string delimiter. SQLSTATE=42603
I've tried various ways of escaping the semicolons, but can't find the
magic rule. I've looked in the DB2 SQL reference manuals, as well as
the documentation on the db2sql92 utility, but no clues.

How do I do this?

Well, after reading the SQL 92 ANSI specification, I found that
I could do this:

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

which worked.

I'm still interested in a better command-line tool than db2sql92.

Thanks,


By the way, I'm using db2sql92 only because it is the only tool
I've found to execute SQL scripts. Is there a better command-line
utility that I should be using? I've found a number of silly
problems with this tool (like the one above) that make me think
it may be old and no longer kept up to date.

--
Mark Hansen, PP-ASEL
Sacramento, CA