StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJava: JDBC ResultSet not populated with all query results
text
Body
copied!<p>I'm working on getting our system's java beans to be able to use the SQL IN clause when running queries down through the database, but am running into a perplexing problem.</p> <p>I am build the SQL query for the PreparedStatement in the following generic pattern:</p> <pre><code>select [column names] from [table name] where [a column name] IN (?, ? , ?, ..., ?) </code></pre> <p>The ..., represents any number of ?'s depending on the number of values the user is deciding to build into the IN clause. I run a loop to get these into the query string.</p> <p>From here, I use the PreparedStatement's setString( idx, String ) method, and iterate through the list of values and run from index 1 - # of values.</p> <p>The PreparedStatement runs the query via the executeQuery() method and the returned ResultSet seems to be incorrect.</p> <p>In a specific instance using 4 values, when I take the query in the PreparedStatement to SQL and replace each ? with the exact values in ' ', I get 3 results (as one of the values is purposely not in the DB).</p> <p>The ResultSet, on the other hand only has 1 row in its set, and that row always corresponds to the first ? parameter in the IN clause.</p> <p>I even tried faking the IN clause with ([column name] = ? OR [column name] = ? ... OR column name] = ?) but the same issue occurs here too.</p> <p>Any ideas what is going on here? Connecting to an Oracle database, by the way.</p> <p>Logs:</p> <pre><code>2010-02-10 11:16:28,505 DEBUG basic.BasicCursor - Preparing statement SELECT MERCHANT_ID, M_NAME, M_AUTHEN, M_ADMIN_AUTHEN, M_CONTACT_ADDR, M_PAYMENT_ADDR, M_HAS_MPROXY, M_DISABLED, M_FREETEXT, TXN_ID, M_TAX_NAME, M_TAX_RATE, MERCHANT_PARENT_ID, MERCHANT_ROOT_ID, RESERVED_1, RESERVED_2, RESERVED_3, RESERVED_4, EMAIL, LOGICAL_TYPE, CHANNEL_MASK FROM MERCHANT0 WHERE MERCHANT_ID IN (?, ?, ?, ?) ORDER BY MERCHANT_ID 2010-02-10 11:16:28,505 DEBUG basic.BasicCursor - Adding string to slot 1: 6172222222 2010-02-10 11:16:28,505 DEBUG basic.BasicCursor - Adding string to slot 2: 6177740603 2010-02-10 11:16:28,505 DEBUG basic.BasicCursor - Adding string to slot 3: 6177740602 2010-02-10 11:16:28,505 DEBUG basic.BasicCursor - Adding string to slot 4: 6172441111 2010-02-10 11:16:28,512 DEBUG basic.BasicCursor - scanCursor() calling... checking for next row. Current row is : 0 2010-02-10 11:16:28,512 DEBUG basic.BasicCursor - scanCursor() called, hit 2010-02-10 11:16:28,512 DEBUG basic.BasicCursor - scanCursor() got object 6172222222 2010-02-10 11:16:28,512 DEBUG basic.BasicCursor - scanCursor() calling... checking for next row. Current row is : 1 2010-02-10 11:16:28,512 DEBUG basic.BasicCursor - scanCursor() called, not hit 2010-02-10 11:16:28,505 DEBUG basic.BasicCursor - The size of variables list = 4 </code></pre> <p>EDIT: Found the issues with the PreparedStatement. I'll leave it as an exercise to those curious to figure it out. It's visible in the log statements above. Unfortunately, now my problem has cascaded to some annoying proprietary code we have that limits the rows from the now expected ResultSet to displaying only 1 record anyway. <em>sigh</em></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload