0

I have a problem with SQL Server JDBC driver, I tried the Microsoft JDBC driver and also the JTDS driver, but they have both the same problem: in the ResultSet if I look for "table.field" I get the error "invalid column name", but if I look for "field" only it works. This is a problem for me, because I have an application that automatically generates queries and uses the notation "table.field", for now I have worked on MySQL and Postgresql and it worked fine, but now I need to add SQL Server support and I have found this problem. It's a problem of misconfiguration of the driver or they work only in this way? There are other drivers available?

I also found a case that I can't make work, for example I created two tables with a field with the same name, and I made the cartesian product between them.

The tables t1,t2 are identical and they have this structure

------
| id | 
------
| 1  | 
------
| 2  | 
------

If I execute the query 

SELECT * FROM t1,t2

on SQL Server the result will be

-----------
| id | id |
-----------
| 1  | 1  |
-----------
| 1  | 2  |
-----------
| 2  | 1  |
-----------
| 2  | 2  |
-----------

But from Java using the JDBC driver I can't obtain the same result, because I cannot access to t1.id, t2.id but only to id and it returns only the first column.

Here's the sample code

Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/test", "user", "pass");         
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM t1, t2");
while(rs.next()){
    System.out.println(rs.getString("id"));   //This works
    System.out.println(rs.getString("t1.id"));//This not works
}
Pirvu Georgian
  • 657
  • 1
  • 12
  • 37
Gaglia88
  • 99
  • 7
  • What should be the result of this query? You get a cartesian product. Make this sence? – Jens Apr 18 '16 at 08:53
  • you should handle this at query level and assign an alias to t1 and t2. – jmcg Apr 18 '16 at 08:59
  • 2
    I think this is what you are looking for http://stackoverflow.com/questions/7224024/jdbc-resultset-get-columns-with-table-alias – Amal Gupta Apr 18 '16 at 08:59
  • Jens the result of the query has to be the cartesian product, it's just an example to show the problem that I've had. @Amal right, it's exactly my case, so I have to add an alias to each column... I would preferred an automatic system, I mean the JDBC drivers for other systems (e.g. MySQL, Postgresql) do this automatically, but ok... Thanks! :) – Gaglia88 Apr 18 '16 at 09:14
  • JDBC only mandates retrieval by the column label, the table name (or table alias) is not part of the column label. – Mark Rotteveel Apr 18 '16 at 10:45

1 Answers1

2

Do not use SELECT *. Instead of this, Use column names . if duplicate names occur, use unique column aliases. 

Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/test", "user", "pass");         
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT t1.id as col1,t2.id as col2 FROM t1, t2");
while(rs.next()){
    System.out.println(rs.getString("col1"));  

}
Ponmani Chinnaswamy
  • 705
  • 7
  • 21