i am using sql server and microsoft jdbc connector 2005 to connect to sql server.
I got Invalid column name when i executed the qurery with multiple table having same column name and retrieved by giving table alias dot column name.
But it works well with mysql Connector/j for mysql database.
Did any one noticed it.
Message was edited by:
dinesh
The Below is my sample code tested using sql server and msSql jdbc Driver 2005
I have two tables "Employee_Details" and "Employee" The Employee_Details table has id,EmployeeId and other fields
The Employee table has Id,Name,Address
I have Executed the query
sql = "SELECT ed.Id,ed.EmployeId,e.Id,e.Name FROM Employee_Details ed , Employee e WHERE e.Id=ed.Id";
After Executing the query using executeQuery() Method and I tried to take the result from ResultSet as below
int Id = rs.getInt("e.Id");
I got Exception as java.sql.Exception :Invalid Column Exception
But the same code works fine in Mysql with Mysql Connector/j
Message was edited by:
dinesh
I don't know which column name you're using which is a reserved word in mssql, but one or more of them are, or you mistyped a column name somewhere (the EmployeId one looks suspicious). This is not a Java-specific problem.
To get around it, try enclosing each column name in brackets.
sql = "SELECT ed.[Id],ed.[EmployeId],e.[Id],e.[Name] FROM Employee_Details ed, Employee e WHERE e.[Id]=ed.[Id]";
> I don't know which column name you're using which is
> a reserved word in mssql, but one or more of them
> are, or you mistyped a column name somewhere (the
> EmployeId one looks suspicious). This is not a
> Java-specific problem.
>
> To get around it, try enclosing each column name in
> brackets.
>
> sql = "SELECT ed.[Id],ed.[EmployeId],e.[Id],e.[Name]
> FROM Employee_Details ed, Employee e WHERE
> e.[Id]=ed.[Id]";
I think it sounds like a driver bug since executing the SQL works, but retreiving a value using column name doesn't work.
@OP. I guess that the column name isn't what you expect. Try to get the column names from the meta data, or give the column a name using AS.
Kaj
> I think it sounds like a driver bug since executing
> the SQL works, but retreiving a value using column
> name doesn't work.
That's not my impression, given that in one situation I think he is using MS SQL Server, and in the other he is using MySQL. Different databases, different reserved words.
> > I think it sounds like a driver bug since
> executing
> > the SQL works, but retreiving a value using column
> > name doesn't work.
> That's not my impression, given that in one situation
> I think he is using MS SQL Server, and in the other
> he is using MySQL. Different databases, different
> reserved words.
From his sample, it looks like the query ran OK but when he try to get the value of a specific column back he got the error.
I wonder what would happen if he aliased the column name or used the column number instead.
> > I think it sounds like a driver bug since
> executing
> > the SQL works, but retreiving a value using column
> > name doesn't work.
> That's not my impression, given that in one situation
> I think he is using MS SQL Server, and in the other
> he is using MySQL. Different databases, different
> reserved words.
Yes but everyhing works on MySQL, and executing the query works on SQL Server as well, but retreiving data from RS fails on SQL Server when using the name "e.id", so I would guess that the SQL Server driver doesn't "name" the column as "e.id" in the RS.... but I might be 100% wrong. It was just my interpretation of the post.
> > > I think it sounds like a driver bug since
> > executing
> > > the SQL works, but retreiving a value using
> column
> > > name doesn't work.
> > That's not my impression, given that in one
> situation
> > I think he is using MS SQL Server, and in the
> other
> > he is using MySQL. Different databases, different
> > reserved words.
>
> From his sample, it looks like the query ran OK but
> when he try to get the value of a specific column
> back he got the error.
>
> I wonder what would happen if he aliased the column
> name or used the column number instead.
Ah, I see.
> int Id = rs.getInt("e.Id");
Yes, that's not the column name in the resultset.
He'll need to alias them AS something else, since he is including 2 columns named "Id" in the result.
e.Id AS eId ... ed.Id AS edId ...
Then get the value from the column named "eId"
rs.getInt("eId");
Although it's pointless to include both Id fields, since they're going to contain the same value (from WHERE e.Id=ed.Id), so you could also leave one of those out of the SELECT statement. Then the column in the resultset would just be named "Id", not "e.Id".
Message was edited by:
warnerja
> Ah, I see.
> > int Id = rs.getInt("e.Id");
> Yes, that's not the column name in the resultset.
> He'll need to alias them AS something else, since he
> is including 2 columns named "Id" in the result.
Normally, I would consider "e.Id" as a valid column name in this query if I was just coding SQL; however, I was not sure how the driver would react to this - hence my wondering.
> > Ah, I see.
> > > int Id = rs.getInt("e.Id");
> > Yes, that's not the column name in the resultset.
> > He'll need to alias them AS something else, since
> he
> > is including 2 columns named "Id" in the result.
>
> Normally, I would consider "e.Id" as a valid column
> name in this query if I was just coding SQL; however,
> I was not sure how the driver would react to this -
> hence my wondering.
No, if you were to perform the query, the returned resultset (whether in a query tool (not specifically Java) or not) would contain a column name based on the source column name, not on the made-up "tableNameAlias.columnName" used in the query.
Hi All
I have mistaken in the query where Condition i have to map employee table id field with employeeid field in the employee table
WHERE e.Id = ed.EmployeeId
sorry for my mistake in the query
But this gives only different result in the resultset
if i try to get getxxx("TableAlias.columnName") Invalid column ,Exception is thrown
It works fine when i give the alias name for the column name in the query.
If the table has some 30 fields and while i am selecting the fields from that tables by joining two or more tables how can i give alias name for each field. If i use * to select the fields in the tables in that case if the column name is duplicated then how to differentiate the column from one table to other column in other table.
But this exception is not thrown in MYSQL database with same table and same code.
It means that i have to use alias for all the column name even if the table has many fields in the select Method
I asking this because i am trying to migrate from Mysql to SQL server
Since Mysql connector supported tablealias.Column
i am facing lot of difficulties during migration to sql server since it's connector does not tablealias.columnname
can i get any solution
> No, if you were to perform the query, the returned
> resultset (whether in a query tool (not specifically
> Java) or not) would contain a column name based on
> the source column name, not on the made-up
> "tableNameAlias.columnName" used in the query.
I meant that throughout the SQL I can use the alias.columnName as a valid column reference and was unsure how the driver (a.k.a. query tool - Java or not) would react -but, thanks for the clarification.
