Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
4 replies Latest Post - ‏2013-09-18T14:07:04Z by amcintyre
amcintyre
amcintyre
11 Posts
ACCEPTED ANSWER

Pinned topic SQL with FOR and DO, plus node.js, oh my

‏2013-09-18T02:50:09Z |

So this code works great for a single call, muti-row INSERT:
https://gist.github.com/amcintyre99/3702bf78aabc684027e5

but when I take out just the INSERT statement to simply return the results, I can't get around this error:

'[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "with" was found following "from avgs   do".  Expected tokens may include:  "FOR".  LINE NUMBER=1.  SQLSTATE=42601\n'

The doc here:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0004238.html

says either INSERT, WITH and others are all ok after the DO.

Code does SQLPrepare initially then followed by SQLExecute, which gets the error. Environment is node, node-odbc and db2 10.5.

Thanks for any help!!

Updated on 2013-09-18T04:02:36Z at 2013-09-18T04:02:36Z by amcintyre
  • Zahni
    Zahni
    16 Posts
    ACCEPTED ANSWER

    Re: SQL with FOR and DO, plus node.js, oh my

    ‏2013-09-18T08:12:34Z  in response to amcintyre

    From Info Center:

    The compound statements can be embedded in an SQL procedure definition, SQL function definition, or SQL trigger definition. It is not an executable statement and cannot be dynamically prepared.

    You may look into CLIPLUS .

  • nivanov1
    nivanov1
    231 Posts
    ACCEPTED ANSWER

    Re: SQL with FOR and DO, plus node.js, oh my

    ‏2013-09-18T11:51:00Z  in response to amcintyre

    "to simply return the results" you need a cursor. You cannot have a query hanging in a SQL PL block by itself, you need to consume the result set somehow.

    You may be mislead by the way DB2 CLP or other clients present the results of a standalone SELECT statement. In fact, what really happens internally when you type 'SELECT * FROM SYSIBM.SYSDUMMY1' is more like this:

    String sqlStr = "SELECT * FROM SYSIBM.SYSDUMMY1";
    ResultSet rs = stmt.executeQuery(sqlStr);
    while (rs.next()) {
      // get result set metadata, determine columns
      String col1 = rs.getString(1);
      ...
      System.out.println(col1);
      ...
    }

    Updated on 2013-09-18T11:52:34Z at 2013-09-18T11:52:34Z by nivanov1