SQL Server Exception: "The column name xxx is not valid" when using JDBC

Question

I'm getting a strange error from the SQL Server JDBC driver. It is telling me that a column name is invalid even though the column is present, correctly named and the same query works fine when executed in SqlServer Management Studio.

The error is:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name MarginCall is not valid.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(SQLServerResultSet.java:626)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getBigDecimal(SQLServerResultSet.java:2570)
    at org.apache.commons.dbcp.DelegatingResultSet.getBigDecimal(DelegatingResultSet.java:305)
...

Answer 1

Problem solved. It was a simple mistake by me.

My query was using an 'AS' clause in the select statement. I was trying to retrieve the column value from the ResultSet using the actual column name, instead of the column alias defined in the AS clause.

Schoolboy error. Apologies for the time wasting.

Many thanks to Steve B. for his suggestion to use ResultSet.getColumnNames(). Although the actual method call I used was ResultSet.getMetaData().getColumnName(columnIndex);

Answer 2

Check the case of your table name. if collation is set to case sensitive on ms sql server the table names are affected too.

Answer 3

The same issue can be thrown, if the Entity & SELECT statement values are mismatching.

Check the following example:

Entity

@Setter
@Getter
@NoArgsConstructor
@Entity
@Table(name = "country")
public class Country {

    @Id
    private Integer id;
    
    private String name;

    private String code;

}

Repository

public interface CountryRepository extends JpaRepository<Country, Integer> {

    @Query(nativeQuery = true, value = "SELECT id, name FROM country WHERE isActive = 'true' ORDER BY name")
    List<Country> findIdAndNameAndCodeByIsActiveTrue();
}

In this case it will throw an Exception

com.microsoft.sqlserver.jdbc.SQLServerException: The column name code is not valid.

So, if you are NOT using * in the SELECT statement, then better to use Projections.

Hope this helps who was using frameworks.