SQL Server/T-SQL via JSP: "The multi-part identifier XX.YY could not be bound"

Question

I'm getting the error:

 the multi-part identifier "IC.industry" could not be bound

when making this SQL query from a JSP page via JDBC:

select C.company, C.shname, C.fullname, count(d_to_c.designer)
from companies C
     left join ind_to_c IC on C.company = IC.company
     left join d_to_c on C.company= d_to_c.company
where IC.industry = ?
group by C.company, C.shname, C.fullname
order by C.shname

and I'm trying to run it as a prepared statement, where I'm setting the parameter via (for example) stmt.setObject(1, 7) prior to running stmt.executeQuery().

Now, what's weird is: If I execute this with the ? and set the parameter as I just mentioned, I get the "could not be bound" error. If, however, I just change the query and hardcode the number 7 into the text of the query, it works!

So it has something to do with binding that parameter.

But I can't seem to figure out what.

Anybody?

UPDATE: Per request, the table definition for ind_to_c:

 industry - int(11)
 company - int(11)

(it's just a table that defines the m2m relationship between industries and companies)

UPDATE 2: Also per request, the full JSP code. I had to pull this out of a call to an abstraction of the database connection (which we use to store prepared statements, etc.

// conn has been initialized as the db connection object.

int parent_id = 7;
PreparedStatement ps = conn.prepareStatement("select C.company, C.shname, C.fullname, count(d_to_c.designer) from companies C left join ind_to_c IC on C.company = IC.company left join d_to_c on C.company = d_to_c.company where IC.industry = ? group by C.company, C.shname, C.fullname order by C.shname");
ps.setObject(1, parent_id);
ResultSet rs = null;
rs = ps.executeQuery();

Answer 1

Have you tried passing a named parameter (i.e: @industry) instead of a question mark?

Answer 2

What's the data type for industry? Does it make a difference if you use the type specific bind methods like stmt.setInt(1,7) instead?

edit: also, not related to the question, but you should probably remove C.cid from the SELECT. Some variants of T-SQL will infer that you want to group by that column since it is not the subject of an aggregation function, even though you don't specifiy it in the GROUP BY clause.

Back on topic, can you post the table definition for ind_2_c? The nature of the error would seem to indicate that it has no column called industry.

Answer 3

Maybe I am just thinking to simple here because i do not know JSP to well but would dit not just work:

int parent_id = 7;
PreparedStatement ps = conn.prepareStatement("select C.company, C.shname, C.fullname, count(d_to_c.designer) from companies C left join ind_to_c IC on C.company = IC.company left join d_to_c on C.company = d_to_c.company where IC.industry = ? group by C.company, C.shname, C.fullname order by C.shname");

ps.setInt(1, parent_id );

ResultSet rs = null;
rs = ps.executeQuery();

Answer 4

I see from your comments that you can only use SetObject.

But why do you pass an object array instead of a single object? (if I read Java correctly)

Answer 5

I upgraded to the newer v. 2.0 of the MS-SQL JDBC driver, and magically it worked.

Sigh.