CODEWITHSUNDEEP
javasql-serverjdbc
KevinS
KevinS

Reputation: 7882

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

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)
...

Upvotes: 14

Views: 33248

Answers (4)

kcsurapaneni
kcsurapaneni

Reputation: 832

The same issue can be thrown, if the Entity properties & SELECT statement column 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 do not need all the columns as response, then use Projections as return type or mention all the columns which are available as part of entity in the select query.

Upvotes: 1

KevinS
KevinS

Reputation: 7882

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);

Upvotes: 17

Mauricio
Mauricio

Reputation: 5853

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

Upvotes: 1

David Espart
David Espart

Reputation: 11790

Try to enclose the column name inside square brackets: [MarginCall] Just a guess.

Upvotes: 0

Related Questions