2

I'm using a jdbcRowSet data source and have a simple sqlQuery:

SELECT * FROM MYSCHEMA.MYTABLE WHERE KEYCOLUMN=?

I want to use this SQL with a parameter to indicate which rows I'd like to retrieve. To do so I'm trying to compute an sqlParameter, of type INTEGER. Whenever I try to do so, I end up with an error message

DB2 SQL Error: SQLCODE=-313, SQLSTATE=07004, SQLERRMC=null, DRIVER=4.7.85

Even when I put a simple single digit into the sqlParameter value property, I end up with this error.

Whenever I hardcode the parameter directly into the sqlQuery, everything works fine. What is going on here? Any help is greatly appreciated.

(IBM Lotus Domino 8.5.3, IBM DB2 10.1, latest extlib for xpages)

Gernot Hummer
  • 33
  • 5
  • Can the XPages Meets Relational Data presentation from BLUG 2012 help you? http://www.blug.be/blug.nsf/pages/Event20120322_Sessions – Per Henrik Lausten Feb 12 '13 at 13:51
  • 1
    I noticed this only happens when using a jdbcResultSet, a jdbcQuery works with parameters. Maybe I am getting something wrong here. As a sidenote, is there a javadoc for the jdbc part of extlib, would help tremendously to see what the various objects are capable of. – Gernot Hummer Feb 12 '13 at 14:35
  • @PerHenrikLausten Unfortunately, this presentation doesn't go into that much detail to figure out what is going on, thanks though. :-) – Gernot Hummer Feb 12 '13 at 14:40

2 Answers2

4

Apologies that this has been missed for so long, but you have in fact found a bug in the jdbcRowSet data source when using parameters. As has been said, it works in jdbcQuery but not jdbcRowSet.

The error code indicates this: SQLCODE = -313, ERROR: THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS

The issue is now being tracked as SPR#BGLN9N4CZU. If a fix is found for this, I will update here to let you know what build of the ExtLib it will be in

UPDATE: A fix for this issue is included in the latest release of the XPages Extension Library on OpenNTF, v901 Release 9.

Brian Gleeson - IBM
  • 2,565
  • 15
  • 21
  • Awesome, thanks for getting back to me about it. Looking forward to hear about the fix whenever it's ready. :-) – Gernot Hummer Aug 19 '14 at 20:04
  • Updated my answer, as the fix is in release 9 of the XPages Extension Library on OpenNTF: http://openntf.org/main.nsf/project.xsp?r=project/XPages%20Extension%20Library/releases/FC420BE9BF48AE3C86257D4D0033007F – Brian Gleeson - IBM Sep 08 '14 at 09:54
0

I would comment, but I don't quite have the reputation yet. I assume that you wrote the query to the console, then cut and paste into a SQL tool (like Tora).

I just recently completed a project to pull data from Oracle using JDBC. I document it on my blog including code here: http://notesspeak.blogspot.com/2013/02/notes-to-oracle-using-jdbc-end-result.html Perhaps it will help to see how I handled it, the code is all there. I don't use the RowSet, but build a dynamic SQL query as a String, and then execute the query. Note: The SELECT at the top is not dynamic, the UPDATE farther down is build dynamically.

Steve Zavocki
  • 1,840
  • 4
  • 21
  • 35
  • 1
    Well, in this case I ended up using the jdbcQuery xpages control instead of the jdbcRowSet. Somehow the RowSet seems to have trouble with parameters. In this case I only ended up needing to read data anyways so it didn't matter. For writing back to the database I created a jdbc connection using @JdbcGetConnection and handed it back into a java controller that took care of writing new data back to the database. (http://stackoverflow.com/questions/14977185/retrieving-autoincrement-value-when-using-jdbcinsert) – Gernot Hummer Feb 26 '13 at 21:01