DB2 SQL Error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: INSERT INTO TABLE VALUES (?,?,?);BEGIN-OF-STATEMENT;

We had this error code occuring during execution of an SQL statement in a J2EE application to DB2 in WebSphere.

A google search turned up a thin number of documents that hinted the problem was due to a malformed SQL Query. Looking at the code, everything looked ok. Connections were closed in the correct spots, the statement was being executed.

More interestingly, it was reported that this exception occurred on the SECOND invocation of the code. Usually, when something works once and then fails a second time in a piece of WebSphere that is heavily used like connecting to JDBC providers, I suspect the custom code before I point a finger at the platform.

When I looked at the code, I noticed:

String sqlString = ""
For (condition) {
sqlString.append("INSERT INTO...");
}

Basically, the developer had missed re-initializing the value of the string over subsequent iterations of the loop. The second time through the loop the SQL string would look like:

INSERT INTO .... ?,);INSERT INTO .... VALUES...?);

What annoys me is that rather than DB2 telling me simply “Hey, you’ve got two SQL statements chained together and that’s invalid”, it gives me an error code that relates to nothing. It has corrupted the problem from the developers problem domain to the DB2 problem domain. The developer would instantly recognize two SQL statements being chained together. The developer will never understand that SQLCODE -104, SQLSTATE 42601 means anything.

I’d love to see a new IBM policy of presenting exceptions in the domain of the user rather than the domain of the product developer.

Author: dan

Comments

  1. Hi Eric,

    My issue with the error code that DB2 returns is that it’s centered on the DB2 knowledge domain. As a consumer of it’s services, I’d prefer to get an error thats understandable to a non-DB2 guru

    In addition, the error code itself isn’t descriptive enough to easily tell me what the error actually was.

    Also, when I did my search for error codes on google, I never got the infocenter link.

    As for the link itself, yes, it has good information in it. Maybe IBM needs to do a better job of working with google to get searches for it’s own technologies hitting higher with it’s own sites. It seems like theres always a ton of blog and forum entries that get infront of IBMs own info.

  2. I always start with Google too — but it’s a tool, not a religion. If it’s not working for you after XX hits (pick your own pain threshhold), look somewhere else.

    DB2 v8: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp

    DB2 v9: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp

    DB2 v9.5: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp

    For v8 and v9: navigate to Reference / Messages / SQL
    For v9.5: navigate to Database Fundamentals / Troubleshooting & Support / Messages / SQL

    Oracle 10g2: http://www.oracle.com/technology/documentation/database10gr2.html
    click View Library, or go directly to: http://www.oracle.com/pls/db102/homepage

    As for convincing Google to obey its IBM overlords — hmmm, not too sure about that one. :-)

  3. Hi Eric,

    It’s not so much about Google and IBM playing nice with each other, than it is a simple request to IBM to make their infocenter more friendly to search engines. The infocenters are surprisingly good nowadays but they’re absolutely horrible to search.

    If a blog with pagerank of 2 can get ahead of IBM.com and it’s pagerank of 10, theres a problem with IBM.

    And FYI, I’m pretty sure google is a religion 😉

Leave a Reply

Your email address will not be published. Required fields are marked *