0

I'm executing this query:

 SELECT psq_id, question_text 
 FROM presales_question
 WHERE psq_id IN(SELECT zz.psq_id FROM productservice_psq as zz WHERE zz.ps_id = 1)

When I execute it directly in SQL Server Management Studio, it works.
When I execute it with a standard Java Statement & Result Set, it works.
When I execute it using the Apache Commons DBUtils library (v 1.5), I get:

Exception in thread "main" java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "zz.ps_id" could not be bound. Query: SELECT psq_id, question_text FROM presales_question  WHERE psq_id IN(SELECT zz.psq_id FROM productservice_psq as zz WHERE zz.ps_id = ?)  Parameters: [1]
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:363)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:350)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:211)

It seems the problem is not with the query, but with the dbutils library. Digging into the source, I see the error is thrown when dbutils calls the getParameterMetaData() method of the Statement. I'm still unsure how to fix this.

My interim solution has been to edit the source code of the dbutils library and disable the call to getParameterMetaData() (see line 196 of AbstractQueryRunner.java). All it does is check that the param count passed in matches what's found in the query and throw an error if !=. I'm OK with skipping that check since the query will fail anyway if the params don't match.

bradvido
  • 2,743
  • 7
  • 32
  • 49

1 Answers1

1

You queries differs, check the table names and do a new try.

SELECT psq_id, text 
FROM ps_question
WHERE psq_id IN (
  SELECT zz.psq_id FROM productservice_psq as zz WHERE zz.ps_id = 1
)

and the one it fails

SELECT psq_id, question_text 
FROM presales_question  
WHERE psq_id IN (
  SELECT zz.psq_id FROM productservice_psq as zz WHERE zz.ps_id = ?
)
  1. You have text in the first and question_text in the next.
  2. Table name is ps_question in first query and presales_question in the other.

Be 100% sure that the query is ok, you don't need any alias for the table zz. You could remove this details.

Might be old jdbc drivers you use.

Download the latest drivers,

http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

Try to give 1 and run the query again, will it run? or will it fail?

change the ? to 1

SELECT psq_id, question_text FROM presales_question  
WHERE psq_id IN (
  SELECT zz.psq_id FROM productservice_psq as zz WHERE zz.ps_id = ? <-- change 1
)
MrSimpleMind
  • 7,890
  • 3
  • 40
  • 45
  • The different params were just me trying to simplify the query names when i posted it. I have the latest drivers from MS. The change 1 did not help. – bradvido Jan 12 '14 at 19:42
  • I added the zz alias to do some troubleshooting. I understand it's not necessary, but i get the same error without it. – bradvido Jan 12 '14 at 19:43
  • Yes simple queries work. Anything that is selecting from a single table (not using joins or nested selects) works. – bradvido Jan 12 '14 at 19:51
  • There was a bug (i think it has been fixed some versions back) when using aliases, thats why I asked for removing the zz, see thread https://groups.google.com/forum/#!topic/microsoft.public.sqlserver.jdbcdriver/XUGoJ_334Js – MrSimpleMind Jan 12 '14 at 19:53
  • OK. more debugging. After removing the zz alias and changing the ? to 1, it executed successfully and getParameterMetaData() worked. This is a problem though because I need (prefer) to use prepared statements with ?'s in them to prevent sql injection – bradvido Jan 12 '14 at 20:21
  • Can you try to split the query in 2 parts? first query that finds the psq_id from productservice_psq and the next query gets the text from presales_question. – MrSimpleMind Jan 12 '14 at 20:24
  • I can... but shouldn't have to. The problem is fully due to the ? in the SQL when asking for getParameterMetaData(). The alias has not caused any problems. It seems to be an issue with MS SQL JDBC driver. I haven't run into this with MySQL. – bradvido Jan 12 '14 at 20:35