运行使用OPENQUERY访问链接服务器的TSQL查询时出现java JDBC错误

Question

我正在尝试使用准备好的语句执行SQL Server查询：

PreparedStatement pst = con.prepareStatement("select * from openquery(SERVERNAME," +
                                             "'Select r.A , r.B, c.C from Y r" +
                                             "INNER JOIN X c" +
                                             "ON r.RNID = c.RNID ')" +
                                             "where  c.C in ?");

pst.setString(1, data);

ResultSet rs = pst.executeQuery();

我收到以下错误消息：

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot get the column information from OLE DB provider "MSDASQL" for linked server "SERVERNAME".

更新

修复SQL语句以添加缺少的空格后

PreparedStatement pst = con.prepareStatement("select * from openquery(SERVERNAME, " +
                                             "'Select r.A , r.B, c.C from Y r " +
                                             "INNER JOIN X c " +
                                             "ON r.RNID = c.RNID ') " +
                                             "where  c.C in ?");

我现在得到了错误

com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "c.C" could not be bound

Answer 1

看起来您需要在查询中进行简单的更改。 你应该像我在下面展示的那样尝试，然后它就会工作。 在分布式查询中使用以下由四部分组成的表名，根据Java后端的不同，需要省略数据库名和模式

    PreparedStatement pst = con.prepareStatement("Select r.A , r.B, c.C from 
    [SERVERNAME].[databaseName].[dbo].Y r" +
                                                 "INNER JOIN [SERVERNAME].[databaseName].[dbo].X c" +
                                         "ON r.RNID = c.RNID '" +
                                         "where  c.C in ?");

    pst.setString(1, data);

    ResultSet rs = pst.executeQuery();

Answer 2

在OPENQUERY函数中使用的表别名对于调用它的查询根本不可用。因此，如果“无法绑定多部分标识符“c.ID”，则此操作将失败：

SELECT * 
FROM 
    OPENQUERY(ACCDBTEST, 'SELECT c.ID, c.LastName FROM Clients c')
WHERE c.ID = 1

但这是有效的

SELECT * 
FROM 
    OPENQUERY(ACCDBTEST, 'SELECT c.ID, c.LastName FROM Clients c')
WHERE ID = 1

就像这样

SELECT * 
FROM 
    OPENQUERY(ACCDBTEST, 'SELECT c.ID, c.LastName FROM Clients c') AS x
WHERE x.ID = 1