ODBC 16.00.00.02 return wrong data

[Former Member]

Hi we are using this ODBC driver to connect to Sybase 15.

Adaptive Server Enterprise

16.00.00.02

SAP AG or an affiliated company

SYDDRVODB.DLL

4/12/2014

All works OK except money value all time returned as 0.

[dawn_kim]

Hi Vladimir,

I think you need to update your MDA odbc scripts. Try this first and update me with the results.

* Updating the metadata stored procedures required by ODBC, OLE DB and
  jConnect Drivers on Adaptive Server Enterprise

  Certain new features and bug fixes in ODBC, OLE DB and jConnect drivers
  require you to modify the metadata stored procedures in Adaptive Server.
  These stored procedures may not be up-to-date on your host ASE server due
  to various reasons.
  If the metadata stored procedures are outdated, you may not be able to use
  some of the fixes that are implemented in this SP/ESD, so you will need to
  manually install the updated metadata stored procedures.

  To update the metadata stored procedures in Adaptive Server, first determine
  the version of the meta-data scripts installed on the ASE. To do so, execute
  the following command:

  sp_version
  go

  The version number of the ODBC, OLE DB and jConnect meta-data scripts will
  be displayed.
  Review the version number column and compare it against the version of the 
  driver being used.

  If the metadata scripts are outdated, the version installed will be older
  than the driver build number or you will encounter one of the following:
  - stored procedure sp_version is not found
  - the stored procedure output does not print a row for the driver

  The updated scripts are included with the drivers and can be installed as
  follows:

  For ODBC & OLE DB:

  - Go the "sp" directory under the ODBC/OLE DB installation directory
  - Execute the install_odbc_sprocs script for the ODBC Driver and
  install_oledb_sprocs script for OLE DB Provider.

  Syntax for using the script is:

        install_[odbc/olebd]_sprocs <ServerName> <username> [<password>]
         where <ServerName> is the name of the Adaptive Server
             <username> is the username to connect to the server
             [<password>] is the password the username
             (don’t supply this for null password)

  For jConnect:

  - Go to the "sp" directory under the jConnect installation directory. Based
  on your host ASE server version, choose the appropriate SQL script.
  - Use isql or another tool of your choice to execute the selected script.
  This will install the current meta-data stored procedures.

Thanks,
Dawn Kim

[Former Member]

Hi I did what you recommended. Below is current result when we ran sp_version but we still get garbage from Sybase. Data in Sybase is 19.9900, we got "\x19\x04\x01Ü\f\x03"  which function atof convert to 0 I did some test using 3 party tools – Import table from Sybase to Microsoft Access using this ODBC – work OK. Get data using ODBC in BDE explorer – OK. We have very old C code (20 years old) which gets data using ODBC and it works OK with all ODBC except this. Was something changed/added in this ODBC which need changing C code? Thanks Script            Version                                                                                                        Status  ----------------- --------------------------------------------------------------------------------------------------------------- -------- ODBC MDA Scripts  16.0.00.02.1013/Sat Apr 12 UTC 07:15:44 2014                                                                    Complete OLEDB MDA Scripts 16.0.00.00.1013/Tue Jan 14 UTC 21:27:15 2014                                                                    Complete installcommit    16.0 GA PL01/EBF 22540 SMP/P/X64/Windows Server/ase160sp00pl01/3523/64-bit/OPT/Tue Apr 15 14:51:12 2014        Complete installjdbc      jConnect (TM) for JDBC(TM)/16.0 GA (Build 27008)/P/EBF22326/JDK 1.6.0/jdbcmain/OPT/Mon Mar 24 09:41:12 PDT 2014 Complete installjsdb      16.0 GA PL01/EBF 22540 SMP/P/X64/Windows Server/ase160sp00pl01/3523/64-bit/OPT/Tue Apr 15 14:51:12 2014        Complete installmaster    16.0 GA PL01/EBF 22540 SMP/P/X64/Windows Server/ase160sp00pl01/3523/64-bit/OPT/Tue Apr 15 14:51:12 2014        Complete installmodel      16.0 GA PL01/EBF 22540 SMP/P/X64/Windows Server/ase160sp00pl01/3523/64-bit/OPT/Tue Apr 15 14:51:12 2014        Complete montables        16.0/22540/P/X64/Windows Server/ase160sp00pl01/3523/64-bit/OPT/Tue Apr 15 21:50:31 2014                        Complete Execution time: 0.028 seconds

[dawn_kim]

Do you know the name of the older odbc driver you were using the version?

There could be a change of behavior.

Thanks,
Dawn Kim

[Former Member]

Old ODBC used SYODASE.DLL 4.20.00.67 Thanks

[dawn_kim]

Hi Vladimir,

The best suggestion I have for you right now is get SDK 16.0 PL05 and test with this. I saw an issue with smallmoney an odbc which was fixed in a later version. If upgrading the driver doesn't fix the issue. We need to get an incident started.

It could be related to how the code is calling the driver etc.

The old 4..20 version of the odbc driver was actually a ctlibs based build so this did not even use the standard odbc api which is what all odbc drivers use now.

Thanks,
Dawn Kim

[Former Member]

We are not accessing Sybase via an API.  Why the SDK could be useful? Which "driver" you are referring as "upgrading the driver". Where we can get it? Thanks

[dawn_kim]

Hi Vladimir,

You are using something with the odbc driver that is giving you the wrong results. "ODBC driver to connect to Sybase 15". The odbc driver is part of the SDK for SAP ASE. I suggest you SDK 15.7 SP130 or SP132.

You can get the free SDK the instructions are at the end of this SCN discussion: http://scn.sap.com/message/15422805#15422805

Please not it says a different version  but from the list you will get in an email to you pick the platform you will install this on.

Thanks,
Dawn Kim

[Former Member]

We are getting data from ODBC driver using Borland C.

ODBC return decimal (not money as I wrote before)  wrong.

Our code is very old but works with any ODBC drivers except driver I mentioned before.

Two questions.

1. Can we get trial version of ODBC driver you think we should use?

2. Do you have example of C code how to get data from your ODBC driver?

    Maybe your driver have different set of API what we should use.

Thanks

[Former Member]

I see one possible explanation why we get wrong data for decimal fields. We are using function SQLBindCol, not SQLGetData which I think most application is using. If ODBC changed way how decimal data stored then SQLGetData will return correct data and SQLBindCol will return something that cannot be converted to decimal. Is this correct explanation why we get error? Thanks

[Former Member]

Hi

Can you post the detail on this?  Show the two calls and what you are using for values in the arguments? Don't know if this is compatibility issue or something else.  Can you obtain the free SDK (not sure latest will help - 16.0 PL04 is highest available.)

The free SDK is not Trial - it is full SDK (Trial button is misnamed and not sure how we can change it).

I think at this point it would be great to create incident with Product Support at SAP.  If not let us know - at least provide us enough info to create test to confirm your suspicions.

Cheers,

-Paul

[Former Member]

Hi, I downloaded SDK 15.7 SP132 and SDK  16.0 PL04. Both ODBC return same wrong data for decimal fields. Our program was written long time ago (Borland C++) and uses ODBC 2.0 specification. It works OK with any ODBC driver except last version of Sybase ODBC. Here is sequence of calls in or code. I am using table pubs2, table titles  and only one column – price which is decimal. AllocStmt(void** pphstmt) SetupHstmt(void* phdbc, void* phstmt) After this code ask database is this OK asynchronous execution and set this. SetAsyncExec(void* phstmt, BOOL fAsync) After this code execute SQL "SELECT\r\npubs2.dbo.titles.price\r\nFROM\r\npubs2.dbo.titles" Check is execution done IsExecDone(void* phdbc, void* phstmt, BOOL * pfIsDone) Get column data GetColName(void* phstmt, unsigned nCol, char* pName, unsigned uBufLen) "price" GetColType(void* phstmt, unsigned nCol, unsigned* pnType) 3 #define FLD_DECIMAL        3 GetColWidth(void* phstmt, unsigned nCol, long* plWidth) 10 GetColPrecision(void* phstmt, unsigned nCol, unsigned* pnPrecision) 19 GetColScale(void* phstmt, unsigned nCol, unsigned* pnScale) 4 Database was asked should we bind column , answered YES and we create bind object BindColumn(void* phstmt, unsigned nCol, char* pField, long* plLength) It create receptior field pReceptor = new char[nPrecision + 2]; where data will be stored Now we get data GetNext(void* phstmt) Data in receptor field is "\x19\x04\x01Ü\f\x03" This is not decimal and cannot be converter to decimal I did another test – did not allow binding and could see when code tried get decimal data directly same result.

[Former Member]

Hi Vladimir,

Would you be able to collect an ODBC trace of the problem?  Also TDS trace would be great.  Your code looks proprietary, and we don't have any access to Borland C++ compiler.

We've fixed some money related issues in 15.7 and 16.0 but none look related to this one - and those fixes should be in the PL04 release.

With tracing we can possible reverse engineer and create API test to check.

ODBC Trace you can turn on with the ODBC driver manager.  For TDS you can add connection string to the application (I don't know how difficult this would be) - protocolcapture=c:\temp\moneytest

The driver will create binary traces, per connection, with tds extension.  We would need to look at these to check on the types of communication going on between client and server.

It seems you are binding the data to string buffer?

Cheers,

-Paul

[Former Member]

Hi I attached ODBC log file.

I changed connection string like this

"DSN=syb32,protocolcapture=c:\\temp\moneytest"

but did not get TDS log.

Tried

"DSN=syb32;protocolcapture=c:\\temp\moneytest"

with same result.

What I am doing wrong.

Thanks

SQL.txt.zip

[Former Member]

You are escaping first slash,
not second

I’d try c:\temp\file

or

c:\\temp\\file

Cheers,

-Paul

[Former Member]

Hi Vladimir,

I see something similar in the ODBC Test - the binary data is returned.  It might be the older ODBC API isn't understanding the concept of SQL_DECIMAL so it Binds to what ODBC calls SQL_C_DEFAULT (In newer ODBC spec/API it binds to SQL_C_NUMERIC).

I am not sure what the default display should be for SQL_C_DEFAULT.  We can certainly work an SAP incident with you to investigate it further with the Engineering team.

It does look like older driver (DDTEK) is displaying the hex ascii representations of each digit. Looks like ours reps in binary format.

Cheers,

-Paul

[Former Member]

Thanks.

It would be grate if SAP can fix this problem.

[Former Member]

Tried both - same result.

Thanks

[Former Member]

Hi Vladimir,

Try protocolcapture=file

Then file is stored in working application directory.

If it isn't working then something is wrong with the driver, but should work.

Also, can you create SAP Incident? If not, I can still report to engineering, just difficult to track since I am in Product Support and work off customers using SAP support. I have lots of stuff going on but could report this to Eng.  I'd create an API test and then have you try it or review results so we can confirm it is same issue.  I don't know if the ASYNC has anything to do with issue as I see same results with or without ASYNC enabled.

If not we can work via email (paul.vero@sap.com)

Cheers,

-Paul

[Former Member]

I put in connection string only file name which make unique.

Searched all files on computer and cannot find file.

Thanks

[Former Member]

Do you have any new information for me?

Did you opened incident?

Thanks for you help