ODBC错误“表y中的列x的值超过了其最大长度或精度". [英] ODBC Error "Column x in table y has value exceeding its max length or precision"

问题描述

使用ODBC运行以下查询时，我从Progress数据库收到此错误:

I get this error from Progress database when running the following query using ODBC:

SELECT distinct Table.column,
        { fn CONVERT(SUBSTRING(Table.ProblematicColumn, 1, 60), SQL_VARCHAR)} as test
FROM PUB.Table
WHERE ( Table.id IN (
            SELECT Table.id
             FROM PUB.Table
            ) ) 

我知道可以使用DBTools对其进行修复.但是，我对多个客户端的多个Progress数据库运行查询，因此每次执行此操作都不切实际. 另外，由于某种原因，我正在使用的ODBC客户端(PHP)在发生这种情况时不会显示任何错误.而是返回一个空结果.

I know it's possible to fix it using the DBTools. However, I run queries against multiple Progress databases of multiple clients, so it's not practical to do this every time. Also, for some reason, the ODBC client I'm using (PHP), doesn't show any error when this happens. Instead, it returns an empty result.

在添加子查询之前，我对60个字符的VAR_CHAR所做的转换确实有所帮助.当子查询在那里时，我再次遇到相同的错误.

The convert I did to a VAR_CHAR of 60 character did help until I added the sub-query. When the sub-query is there, I get again the same error.

足够有趣的是，当区别"不存在时，它就起作用了.但是我确实需要与众不同.

Interestingly enough, when the 'distinct' is not there, it's working. But I do need the distinct.

问题是如何在不使用DBTool固定width列的情况下执行此查询.

The question is how can I execute this query without fixing the width column with DBTool.

推荐答案

花了几分钟才找到答案.问题似乎出在OE10 SQL代理中，未处理where子句中的子选择.这种使用内部联接到子选择的替代方案看起来与我等效.我测试了它，它确实起作用.替换SQL客户端将无济于事，该错误在OpenEdge SQL代理中发生:我使用OpenEdge JDBC驱动程序遇到相同的错误.

It took a few minutes to find an answer. The problem appears to be in the OE10 SQL broker not handling the sub select in the where clause. This alternative using an inner join to a sub select looks to be equivalent to me. I tested it, it does work. Replacing the SQL client will do nothing, the error occurs in the OpenEdge SQL broker: I get the same error using the OpenEdge JDBC driver.

SELECT distinct Table.column,
   { fn CONVERT(SUBSTRING(Table.ProblematicColumn, 1, 60), SQL_VARCHAR)} as test
FROM PUB.Table inner join (select id from PUB.Table) t2 on Table.id = t2.id

这篇关于ODBC错误“表y中的列x的值超过了其最大长度或精度".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！