MySQL not generating correct results using ODBC da

Question

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

Answer 1

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