JDBC DB/2 statement delimiter problem

JDBC DB/2 statement delimiter problem

Post by Julio Lop » Wed, 06 Jun 2001 23:25:55



Environment:

Windows NT 4.0
WebSphere Appserver 3.5
IBM HTTP Server 1.3.12
DB/2 UDB 7.3

Any help on this matter would be greatly appreciated.  I am trying to
write a shopping cart application.  I have a DB/2 SQL statement that
will check to see if a certain item is in the Shopping Cart table, if
it is it increments the quantity by one, if it is not it adds the item
and sets the quantity to 1.  I know this SQL works properly because I
have run it in the command line.

BEGIN ATOMIC
   FOR ROW AS
        SELECT COUNT(*) AS NUMBEROFROWS FROM THELIB.SCL0P WHERE SCLENTTYP = ?
AND SCLENTID = ? AND SCLENTBUSTYP = ? AND SCLUSRID = ? AND SCLITMCDE =
?
           DO IF (NUMBEROFROWS = 0) THEN INSERT INTO THELIB.SCL0P (SCLENTTYP,
SCLENTID, SCLENTBUSTYP, SCLUSRID, SCLITMCDE, SCLITMQTY) VALUES (?, ?,
?, ?, ?, 1) ;
           ELSE UPDATE THELIB.SCL0P SET SCLITMQTY = SCLITMQTY + 1 WHERE
SCLENTTYP = ? AND SCLENTID = ? AND SCLENTBUSTYP = ? AND SCLUSRID = ?
AND SCLITMCDE = ? ;
        END IF ;
   END FOR ;
END

The issue I am running into is JDBC in nature.  It seems that the ;
within the statement are not being passed to the DB/2 server or the ;
is not being recognized as a statement delimiter.  If the former is
the problem is there any way to escape the ; so it does not get parsed
out?  If it is the latter is there a way to tell DB/2 within the SQL
statement that the ; is a statement delimiter?  Here is the pertinent
Java code.

    // The statement must reference the connection to be used
    prepStatement = connection.prepareStatement(getSQLString());

    // Create placeholders for the parameters
    prepStatement.setString(1, entityType);
    prepStatement.setString(2, entityId);
    prepStatement.setString(3, entityBusinessType);
    prepStatement.setString(4, userId);
    prepStatement.setString(5, itemNumber);
    prepStatement.setString(6, entityType);
    prepStatement.setString(7, entityId);
    prepStatement.setString(8, entityBusinessType);
    prepStatement.setString(9, userId);
    prepStatement.setString(10, itemNumber);
    prepStatement.setString(11, entityType);
    prepStatement.setString(12, entityId);
    prepStatement.setString(13, entityBusinessType);
    prepStatement.setString(14, userId);
    prepStatement.setString(15, itemNumber);

    // Try to perform the update
    try {

        // Execute the SQL statement
        prepStatement.execute();
    }
    catch (SQLException e) {

        e.printStackTrace();
        throw e;
    }

And here is the error I am getting back.

[01.06.04 23:06:03:247 CDT] 818b8b35 WebGroup      A SRVE0091I:
[Servlet LOG]: "eMDS.CartAddItem: [eMDS.CartAddItem-error][IBM][CLI
Driver][DB2/NT] SQL0104N  An unexpected token "END-OF-STATEMENT" was
found following "S (?, ?, ?, ?, ?, 1)".  Expected tokens may include:
"<delim_semicolon>".  LINE NUMBER=1.  SQLSTATE=42601

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0104N  An
unexpected token "END-OF-STATEMENT" was found following "S (?, ?, ?,
?, ?, 1)".  Expected tokens may include:  "<delim_semicolon>".  LINE
NUMBER=1.  SQLSTATE=42601

If anybody has run into something like this or knows what I am doing
wrong I would greatly appreciate the help.

Thank you,

Julio Lopez
M-Group Systems

 
 
 

JDBC DB/2 statement delimiter problem

Post by Dirk Wollschei » Thu, 07 Jun 2001 02:20:00


Julio,

statement.

problem.

I don't think this is JDBC specific, but I'm not that good at SQL parsing so
that I could say where the problem is.

Regards, Dirk


Quote:> Environment:

> Windows NT 4.0
> WebSphere Appserver 3.5
> IBM HTTP Server 1.3.12
> DB/2 UDB 7.3

> Any help on this matter would be greatly appreciated.  I am trying to
> write a shopping cart application.  I have a DB/2 SQL statement that
> will check to see if a certain item is in the Shopping Cart table, if
> it is it increments the quantity by one, if it is not it adds the item
> and sets the quantity to 1.  I know this SQL works properly because I
> have run it in the command line.

> BEGIN ATOMIC
>    FOR ROW AS
> SELECT COUNT(*) AS NUMBEROFROWS FROM THELIB.SCL0P WHERE SCLENTTYP = ?
> AND SCLENTID = ? AND SCLENTBUSTYP = ? AND SCLUSRID = ? AND SCLITMCDE =
> ?
>    DO IF (NUMBEROFROWS = 0) THEN INSERT INTO THELIB.SCL0P (SCLENTTYP,
> SCLENTID, SCLENTBUSTYP, SCLUSRID, SCLITMCDE, SCLITMQTY) VALUES (?, ?,
> ?, ?, ?, 1) ;
>    ELSE UPDATE THELIB.SCL0P SET SCLITMQTY = SCLITMQTY + 1 WHERE
> SCLENTTYP = ? AND SCLENTID = ? AND SCLENTBUSTYP = ? AND SCLUSRID = ?
> AND SCLITMCDE = ? ;
> END IF ;
>    END FOR ;
> END

> The issue I am running into is JDBC in nature.  It seems that the ;
> within the statement are not being passed to the DB/2 server or the ;
> is not being recognized as a statement delimiter.  If the former is
> the problem is there any way to escape the ; so it does not get parsed
> out?  If it is the latter is there a way to tell DB/2 within the SQL
> statement that the ; is a statement delimiter?  Here is the pertinent
> Java code.

>     // The statement must reference the connection to be used
>     prepStatement = connection.prepareStatement(getSQLString());

>     // Create placeholders for the parameters
>     prepStatement.setString(1, entityType);
>     prepStatement.setString(2, entityId);
>     prepStatement.setString(3, entityBusinessType);
>     prepStatement.setString(4, userId);
>     prepStatement.setString(5, itemNumber);
>     prepStatement.setString(6, entityType);
>     prepStatement.setString(7, entityId);
>     prepStatement.setString(8, entityBusinessType);
>     prepStatement.setString(9, userId);
>     prepStatement.setString(10, itemNumber);
>     prepStatement.setString(11, entityType);
>     prepStatement.setString(12, entityId);
>     prepStatement.setString(13, entityBusinessType);
>     prepStatement.setString(14, userId);
>     prepStatement.setString(15, itemNumber);

>     // Try to perform the update
>     try {

> // Execute the SQL statement
> prepStatement.execute();
>     }
>     catch (SQLException e) {

> e.printStackTrace();
> throw e;
>     }

> And here is the error I am getting back.

> [01.06.04 23:06:03:247 CDT] 818b8b35 WebGroup      A SRVE0091I:
> [Servlet LOG]: "eMDS.CartAddItem: [eMDS.CartAddItem-error][IBM][CLI
> Driver][DB2/NT] SQL0104N  An unexpected token "END-OF-STATEMENT" was
> found following "S (?, ?, ?, ?, ?, 1)".  Expected tokens may include:
> "<delim_semicolon>".  LINE NUMBER=1.  SQLSTATE=42601

> COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0104N  An
> unexpected token "END-OF-STATEMENT" was found following "S (?, ?, ?,
> ?, ?, 1)".  Expected tokens may include:  "<delim_semicolon>".  LINE
> NUMBER=1.  SQLSTATE=42601

> If anybody has run into something like this or knows what I am doing
> wrong I would greatly appreciate the help.

> Thank you,

> Julio Lopez
> M-Group Systems


 
 
 

JDBC DB/2 statement delimiter problem

Post by Serge Riela » Thu, 07 Jun 2001 03:17:53


Hi Julio,

There is a problem with the "batch mode".
JDBC goes through ODBC (I think) and ODBC separates statements with ';'.
Essentially the dynamic compound gets crippled on the client.
I heard there is an escape clause for the batch mode (curly braces? - not sure)
but never tested if it works.
Either way we have a CLI fix for this (it disables the batch mode for BEGIN
ATOMIC, CREATE TRIGGER, CREATE METHOD and CREATE FUNCTION).
Please contact support to get the fix. You can have them contact me if they don't
know about the problem.

Cheers
Serge

 
 
 

JDBC DB/2 statement delimiter problem

Post by bernd hohman » Thu, 07 Jun 2001 06:56:58



> There is a problem with the "batch mode".
> JDBC goes through ODBC (I think) and ODBC separates statements with ';'.

no, yes.

JDBC has its own daemon process (as far as i can see and maybe
limited to OS/2 and NT)

the problem is simply that SQL statements in java are transferred as
a single string and delimiters aren't respected.

so a transformation in a single, big querystring is required.

hab paketaufkleber im herzen aber kein postamt nach 18:00 ...

bernd

--
the DB2 faq: http://www.harddiskcafe.de/db2faq/index.html

 
 
 

JDBC DB/2 statement delimiter problem

Post by Serge Riela » Thu, 07 Jun 2001 08:02:55


.. Either way it dies at the delimiter.
The SQL Parser never sees the second half of the statement.
I'm 95% Sure the CLI patch will help

Cheers
Serge

PS-Bernd: Kein Postamt mit 24/7 an der Tanke?  Armes D-Land ;-)

 
 
 

JDBC DB/2 statement delimiter problem

Post by bernd hohman » Thu, 07 Jun 2001 10:13:42



> ... Either way it dies at the delimiter.
> The SQL Parser never sees the second half of the statement.
> I'm 95% Sure the CLI patch will help

sure, CLI can handle it. but spawning CLI from java isn't the king of
performance.

Quote:> PS-Bernd: Kein Postamt mit 24/7 an der Tanke?  Armes D-Land ;-)

im moment ist das postamt im *tore (9-18 o'clock) and they will
cry when it moves to the next fuel station (which is opened 24hrs).

well...

bernd

--
the DB2 faq: http://www.veryComputer.com/

 
 
 

JDBC DB/2 statement delimiter problem

Post by Dirk Wollschei » Fri, 08 Jun 2001 02:48:35


I hate to admit it, you guys are right, it doesn't work in DML. I knew for a
fact that semicolons work e.g. in a create SQL procedure statement, but it
seems DML is different.

I tried this:
rs = stmt.executeQuery("BEGIN ATOMIC "+
     "select * from syscat.procedures \u003B"+
      "end");

and this:
rs = stmt.executeQuery("BEGIN ATOMIC "+
     "select * from syscat.procedures { escape ';' }"+
      "end");

and both don't work. The latter isn't a bug because JDBC only allows escape
syntax for scalar functions, date and time literals, outer joins, stored
procedures and like clauses.

Regards, Dirk



> > ... Either way it dies at the delimiter.
> > The SQL Parser never sees the second half of the statement.
> > I'm 95% Sure the CLI patch will help

> sure, CLI can handle it. but spawning CLI from java isn't the king of
> performance.

> > PS-Bernd: Kein Postamt mit 24/7 an der Tanke?  Armes D-Land ;-)

> im moment ist das postamt im *tore (9-18 o'clock) and they will
> cry when it moves to the next fuel station (which is opened 24hrs).

> well...

> bernd

> --
> the DB2 faq: http://www.veryComputer.com/

 
 
 

JDBC DB/2 statement delimiter problem

Post by Dirk Wollschei » Fri, 08 Jun 2001 03:57:50


The answer from one of the JDBC gods is:
you can't use compound statements with dynamic SQL. See the SQL Reference
under Compound SQL, it says "This statement can only be embedded in an
application program"

Regards, Dirk


> I hate to admit it, you guys are right, it doesn't work in DML. I knew for
a
> fact that semicolons work e.g. in a create SQL procedure statement, but it
> seems DML is different.

> I tried this:
> rs = stmt.executeQuery("BEGIN ATOMIC "+
>      "select * from syscat.procedures \u003B"+
>       "end");

> and this:
> rs = stmt.executeQuery("BEGIN ATOMIC "+
>      "select * from syscat.procedures { escape ';' }"+
>       "end");

> and both don't work. The latter isn't a bug because JDBC only allows
escape
> syntax for scalar functions, date and time literals, outer joins, stored
> procedures and like clauses.

> Regards, Dirk




> > > ... Either way it dies at the delimiter.
> > > The SQL Parser never sees the second half of the statement.
> > > I'm 95% Sure the CLI patch will help

> > sure, CLI can handle it. but spawning CLI from java isn't the king of
> > performance.

> > > PS-Bernd: Kein Postamt mit 24/7 an der Tanke?  Armes D-Land ;-)

> > im moment ist das postamt im *tore (9-18 o'clock) and they will
> > cry when it moves to the next fuel station (which is opened 24hrs).

> > well...

> > bernd

> > --
> > the DB2 faq: http://www.veryComputer.com/

 
 
 

JDBC DB/2 statement delimiter problem

Post by Serge Riela » Fri, 08 Jun 2001 06:49:34


BEGIN ATOMIC ... END is a "dynamic compound statement" not a
"compound statement".
It's meant to work in this scenario but it's not.
It's being fixed and the fix is available upon request today and in FP4 (ETA in
3Q).

Cheers
Serge

 
 
 

JDBC DB/2 statement delimiter problem

Post by Blair Kenneth Adamach » Fri, 08 Jun 2001 09:45:27


4Q, not 3Q. (We just tell Serge 3Q so he stops putting new stuff in :)

> BEGIN ATOMIC ... END is a "dynamic compound statement" not a
> "compound statement".
> It's meant to work in this scenario but it's not.
> It's being fixed and the fix is available upon request today and in FP4 (ETA in
> 3Q).

> Cheers
> Serge

 
 
 

JDBC DB/2 statement delimiter problem

Post by Serge Riela » Fri, 08 Jun 2001 20:32:38


So there is plenty of time for the
sysibm.oracle_hint(its_over larry, big time)

function I requested!

I have also an idea for:
oracle.propaganda(random garbage) returns datalink(URL: www.oracle.com)
it's based on the old Eliza program :-)

Cheers
Serge

 
 
 

JDBC DB/2 statement delimiter problem

Post by Larry Menar » Fri, 08 Jun 2001 21:18:54


Quote:> sysibm.oracle_hint(its_over larry, big time)

   I wish you'd qualify that a little more...


> So there is plenty of time for the
> sysibm.oracle_hint(its_over larry, big time)

> function I requested!

> I have also an idea for:
> oracle.propaganda(random garbage) returns datalink(URL: www.oracle.com)
> it's based on the old Eliza program :-)

> Cheers
> Serge

--
Larry Menard
IBM Workstation Database (DB2) Performance Team
Defender of Geese and of all things Natural