列名无效-错误 [英] The column name is not valid - error

问题描述

从结果集中获取值时出现此错误.

错误:com.microsoft.sqlserver.jdbc.SQLServerException:列名company.short_name无效

案例1:

select company.short_Name,location_name from company,location;

此查询在SQL Server上执行良好，但是当我尝试检索类似resultset.getString("company.short_name");的值时，在我的Java代码中出现了上述错误.

案例2:

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

并检索resultset.getString("short_name");之类的值，比起它与数据库MySQL和MSSQL都可以正常工作.

我正在将数据库从MySQL迁移到MSSQL.以上情况1在MySQL中可以正常工作，但是为什么它在MSSQL中不起作用?

解决方案

resultset.getString("company.short_name");在这里是错误的.尝试在您的应用程序中获取数据时，无需指定标准名称.只需指定列名称，如resultset.getString("short_name");.

即使您说select company.short_Name ...也会将列名查询为short_Name，因为这是表模式中定义的原因.

如果两个表具有相同的列，这可能导致歧义，请为这些列命名，例如

select company.short_Name as company_shortname,
       location.short_Name as location_shortname,
location.location_name from company,location;

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?

解决方案

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;

这篇关于列名无效-错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！