MySQL无法使用ODBC日期参数生成正确的结果 [英] MySQL not generating correct results using ODBC date parameter

问题描述

我最近将MySQL版本升级到5.6.10(Linux上的社区版本)，随后我无法正常运行Crystal Report，该报告可以通过MySQL ODBC驱动程序3.5.1与MySQL 5.0.24a正常运行. /p>

该报告是基于SQL命令构建的，如果我手动将查询和日期ala 2013-05-15输入MySQL客户端，但是通过ODBC将参数传递给它时，该命令会正常工作查询为{d'2013-05-15'} MySQL返回随机结果，这意味着一次运行将返回没有负值的结果，另一次运行仅返回负值，而另一次运行将不返回结果.在某些情况下，我实际上得到了完整的报告，但这是不可预测的.

我已经将它专门缩小到date参数，但是在升级到Crystal Reports 2011和MySQL ODBC 5.2之后，问题仍然存在.我已经通过将"date"参数更改为"string"参数来解决此问题，但这并不理想.

MySQL 5.6.10服务器的其他所有功能似乎都运行良好，我仍在尝试确定在5.0和5.6之间是否对ODBC的处理方式进行了更改，但到目前为止我还没有运气弄清楚为什么这样做如此奇怪.

这是带有相关参数的查询的一部分...

    select sql_no_cache
    if(CCT.creditCardTypeName is not null, CCT.creditCardTypeName, if(OPBML.orderPaymentBMLID is not null, 'Bill Me Later', 'Cash/Check')) as theType,
    'orderPayment               ' as source,
    date_format(OP.authorizationDate, '%Y-%m-%d') as theDate,
    if (OPC.checkNumber is not null, OPC.checkNumber, '') as checkNum,
    if (OPWT.wireTransferDate is not null, OPWT.wireTransferNumber, '') as wtNum,
    concat(C.lastName, ', ', C.firstName) as custName,
    O.orderNumber,
    OP.amount
from
    orders O
    inner join orderPayment OP on (O.ordersID=OP.ordersID)
    inner join paymentMethod PM on (PM.paymentMethodID=OP.paymentMethodID)
    inner join customer C on (C.customerID=O.customerID)
    inner join partner P on (C.partnerID = P.partnerID and P.businessUnit = {?businessUnit})
    left outer join orderPaymentBML OPBML on (OP.orderPaymentID=OPBML.orderPaymentID)
    left outer join orderPaymentCheck OPC on (OP.orderPaymentID=OPC.orderPaymentID)
    left outer join orderPaymentCreditCard OPCC on (OP.orderPaymentID=OPCC.orderPaymentID)
    left outer join orderPaymentWireTransfer OPWT on (OP.orderPaymentID=OPWT.orderPaymentID)
    left outer join creditCardType CCT on (CCT.creditCardTypeID=OPCC.creditCardTypeID)
where
    OP.authorizationDate between date_add({?date},INTERVAL "3:0:01" HOUR_SECOND) and 
    date_add( date_add({?date}, INTERVAL 1 DAY), INTERVAL "3:0:00" HOUR_SECOND) and
    PM.paymentMethodAbbreviation in ('BM', 'CC', 'CH', 'WT')

谢谢， --SJ

解决方案

最后的解决方法是

好吧-我想我已经解决了问题.结果是5.6.4改变了关键字影响字面量的方式的副作用是必须在ODBC驱动程序中指定字符集.我指定了正确的字符集用于数据库，现在正在获得正确的结果."

猜测大多数人已经指定了它，我们的没有指定它，并且就可以了".

I recently upgraded my version of MySQL to 5.6.10 (community edition on Linux), subsequently I am unable to properly run a Crystal Report that worked fine talking to MySQL 5.0.24a via the MySQL ODBC driver 3.5.1.

The report is built off an SQL command which works fine if I manually type in the query and date ala 2013-05-15 into the MySQL client, but when passing the parameter to it via ODBC (which reformats the date in the query as {d '2013-05-15'} MySQL returns random results, meaning one run will return results without negative values, another run returns just the negative values, and another run will return no results. In some instances I actually get the full report, but it's not predictable.

I've gotten it narrowed down specifically to the date parameter, but after upgrading to both Crystal Reports 2011 as well as MySQL ODBC 5.2 - the problem persists. I've worked around it by changing the "date" parameter to a "string" parameter but that's not ideal.

Everything else with the MySQL 5.6.10 server appears to be running fine, I'm still trying to determine if there were changes made between 5.0 and 5.6 in regard to how ODBC is handled but so far I've had no luck in figuring out just why this is acting so odd.

Here's a portion of the query with the relevant parameters...

    select sql_no_cache
    if(CCT.creditCardTypeName is not null, CCT.creditCardTypeName, if(OPBML.orderPaymentBMLID is not null, 'Bill Me Later', 'Cash/Check')) as theType,
    'orderPayment               ' as source,
    date_format(OP.authorizationDate, '%Y-%m-%d') as theDate,
    if (OPC.checkNumber is not null, OPC.checkNumber, '') as checkNum,
    if (OPWT.wireTransferDate is not null, OPWT.wireTransferNumber, '') as wtNum,
    concat(C.lastName, ', ', C.firstName) as custName,
    O.orderNumber,
    OP.amount
from
    orders O
    inner join orderPayment OP on (O.ordersID=OP.ordersID)
    inner join paymentMethod PM on (PM.paymentMethodID=OP.paymentMethodID)
    inner join customer C on (C.customerID=O.customerID)
    inner join partner P on (C.partnerID = P.partnerID and P.businessUnit = {?businessUnit})
    left outer join orderPaymentBML OPBML on (OP.orderPaymentID=OPBML.orderPaymentID)
    left outer join orderPaymentCheck OPC on (OP.orderPaymentID=OPC.orderPaymentID)
    left outer join orderPaymentCreditCard OPCC on (OP.orderPaymentID=OPCC.orderPaymentID)
    left outer join orderPaymentWireTransfer OPWT on (OP.orderPaymentID=OPWT.orderPaymentID)
    left outer join creditCardType CCT on (CCT.creditCardTypeID=OPCC.creditCardTypeID)
where
    OP.authorizationDate between date_add({?date},INTERVAL "3:0:01" HOUR_SECOND) and 
    date_add( date_add({?date}, INTERVAL 1 DAY), INTERVAL "3:0:00" HOUR_SECOND) and
    PM.paymentMethodAbbreviation in ('BM', 'CC', 'CH', 'WT')

Thanks, --SJ

解决方案

Last comment is the resolution:

"Ok - I think I've resolved the problem. Turns out a side-effect of 5.6.4 changing how keywords affected literals is that the character set must be specified in the ODBC driver. I specified the correct character set for the DB and am getting correct results now."

Guessing most people already specified that, ours didn't have it specified and it 'just worked.'

这篇关于MySQL无法使用ODBC日期参数生成正确的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！