2

I am getting this error while I am fetching value from resultset.

Error : com.microsoft.sqlserver.jdbc.SQLServerException: The column name company.short_name is not valid

CASE 1 :

select company.short_Name,location_name from company,location;

this query is executing fine on SQL Server but in my java code when I trying to retrieve value like resultset.getString("company.short_name"); that time this give the above error.

CASE 2 :

select company.short_Name short_name,location_name from company,location;

and retrieve value like resultset.getString("short_name"); than it work fine with both database MySQL and MSSQL.

I am migrating my database from MySQL to MSSQL.above case 1 is work fine in MySQL, but why it is not work in MSSQL?

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Mitul Chauhan
  • 49
  • 1
  • 2
  • 9

4 Answers4

6

resultset.getString("company.short_name"); is wrong here. No need to specifying fully qualified name while trying to fetch the data in your application. Just specify the column name like resultset.getString("short_name");.

Cause even though you say select company.short_Name ... query out the column name as short_Name since that's what defined in table schema.

In case both tables has same column which may result in ambiguity, give a alias name to the columns like

select company.short_Name as company_shortname,
       location.short_Name as location_shortname,
location.location_name from company,location;
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • what about if short_name column also available in location table ?. it will give ambiguous error. so table name is need to define. – Mitul Chauhan Aug 10 '15 at 14:08
  • In that case, define a alias name for the columns. both table may have same columns but it depends on whether you are selecting them or not. – Rahul Aug 10 '15 at 14:09
  • yes that is i know which i said in my question case 2 part but that i need to do in all classes of my application.so is there any solution which can we configure in MSSQL ? – Mitul Chauhan Aug 10 '15 at 14:11
  • There no such config setting. While you are writing the query make sure to fully qualify the column name with table name and provide column alias. See edit in answer. – Rahul Aug 10 '15 at 14:12
  • 1
    @MitulChauhan Retrieval by table.columnname doesn't work because the JDBC specification says retrieval should be done by column label (which is either the alias - if specified -, or the column name), if the MySQL driver does allow this, then that is non-standard behavior. – Mark Rotteveel Aug 11 '15 at 06:36
  • @MitulChauhan, consider accepting the answer if it helped. – Rahul Aug 11 '15 at 20:14
  • yes thank you again mark and rahul for make my concept more clear. – Mitul Chauhan Aug 13 '15 at 06:45
3

add the following to your application.properties file spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Swapnil Narwade
  • 31
  • 2
1

When you do 

select company.short_Name,location_name from company,location;

This query outs the column name short_Name and resultSet would also have short_Name

since the company.short_name doesnt exist you get an error.

ihappyk
  • 525
  • 1
  • 5
  • 16
0

the function resultset.getString(String columnLabel)

Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.

Parameters:

columnLabel the label for the column specified with the SQL AS clause. If the SQL AS clause was not specified, then the label is the name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is null Throws: SQLException - if the columnLabel is not valid; if a database access error occurs or this method is called on a closed result set

in the function resultset.getString(String columnLabel), the arg is a column name for executing sql, the statement select company.short_Name,location_name from company,location; will get a result set, which has table headers short_Name,location_name

Javy
  • 944
  • 5
  • 9