CARDEXCHANGE® COMMUNITY

Connecting native to SYBASE

6 years 9 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Connecting native to SYBASE #6087
Hi Mark,

OK, I hope the work-around with the varchar columns is solving the issue in an acceptable way. It still appears to me that Sybase should solve this problem. In any case, Sybase is a niche database that is not used often in combination with CardExchange. There is not much experience with this connection.

regards,
Rutger.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
6 years 9 months ago
Mark Vinkx
Senior Boarder
Senior Boarder
Posts: 52
More
Topic Author
Connecting native to SYBASE #6085
Hi Rutger

Thanks for the help

The only place I could add the extra parameters was the Uid. But it did not solve the problem. To bybass the problem I created an auxiliary column to store a varchar representation of the loginID.

I don't know how to get to the root of the problem. I tried the same setup using ODBC on an SQL Server and that worked



Mark
6 years 9 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Connecting native to SYBASE #6083
Hi Mark,

CardExchange uses the .NET Framework 4.0 and System.Data.Odbc to connect to ODBC. The connection string is constructed as "DSN=Database;" or "DSN=Database;Uid=UserName;Pwd=Password" where Database is whatever you enter in the Database field of the database-setup wizard. So you can add extra options to the connection string by setting the Database field to for example
Database;SuppressParamFormat=0;SuppressRowFormat=0
regards,
Rutger.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
6 years 9 months ago
Mark Vinkx
Senior Boarder
Senior Boarder
Posts: 52
More
Topic Author
Connecting native to SYBASE #6082
I found some interesting parameters I can set for the sybase ODBC connections. I should set them to 0 because they are 1 by default

SuppressParamFormat=0
SuppressRowFormat=0

Can I set them in the connection string in Cardexchange ?




You can improve the performance of prepared statements with the ODBC driver by suppressing parameter format metadata when the prepared statements are re-executed. Adaptive Server 15.7 ESD#1 and later supports parameter format metadata suppression.

You can improve the performance of repeatedly executed queries with the Adaptive Server ODBC driver by instructing Adaptive Server to suppress row format metadata (TDS_ROWFMT or TDS_ROWFMT2) for queries that are re-executed in a session. Adaptive Server 15.7 ESD#1 and later supports row format metadata suppression.
6 years 9 months ago
Mark Vinkx
Senior Boarder
Senior Boarder
Posts: 52
More
Topic Author
Connecting native to SYBASE #6081
I found some interesting parameters I can set for the sybase ODBC connections. I should set them to 0 because they are 1 by default

SuppressParamFormat=0
SuppressRowFormat=0

Can I set them in the connection string in Cardexchange ?




You can improve the performance of prepared statements with the ODBC driver by suppressing parameter format metadata when the prepared statements are re-executed. Adaptive Server 15.7 ESD#1 and later supports parameter format metadata suppression.

You can improve the performance of repeatedly executed queries with the Adaptive Server ODBC driver by instructing Adaptive Server to suppress row format metadata (TDS_ROWFMT or TDS_ROWFMT2) for queries that are re-executed in a session. Adaptive Server 15.7 ESD#1 and later supports row format metadata suppression.
6 years 9 months ago
Mark Vinkx
Senior Boarder
Senior Boarder
Posts: 52
More
Topic Author
Connecting native to SYBASE #6080
I dowload and installed the latest sybase ODBC drivers. The problem remains

According the to our dba's a possible explenation is a problem with caching of the odbc meta data. If the cached information is corrupted the ODBC layer doesn't know the loginID is nummeric anymore.

which version of .NET is used to build cardexchange and which library do you use for the odbc connections is it System.Data.Odbc or Microsoft.Data.Odbc or something else ?
6 years 9 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Connecting native to SYBASE #6079
Then there is not much we can do... Sybase needs to fix there ODBC driver. The same problem also seems to occur with the .NET Data Provider, so adding native support for Sybase is not guaranteed to solve this issue at all.

By the way, it is very weird that it works initially, suggesting that any type conversions are done fine, and then stops working at a certain point. CardExchange does not maintain open connections to the database. I would not know any difference between a query run after starting up and a query run after a period of inactivity.

regards,
Rutger.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
6 years 9 months ago
Mark Vinkx
Senior Boarder
Senior Boarder
Posts: 52
More
Topic Author
Connecting native to SYBASE #6078
I can not change the column type and do want to import data like a print count and mifare uid
6 years 9 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Connecting native to SYBASE #6077
Hi Mark,

This seems to be an issue of the Sybase ODBC provider that does not handle correctly parameterized queries for numeric and decimal types. Would it be possible to create a view un the database that applies the convert function to LoginID in order to change it into a big integer, and then link CardExchange to the view instead of the table? Doing so might make it impossible to edit your data from CardExchange, so my first question should have been: do you need to edit the data with CardExchange? Maybe changing the column type is also a possibility, but you would have to be careful with other applications that possibly use that table.

regards,
Rutger.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
6 years 9 months ago
Mark Vinkx
Senior Boarder
Senior Boarder
Posts: 52
More
Topic Author
Connecting native to SYBASE #6076
Of course i had to change the "quotable names" setting in the odbc driver too. But I still get the same error messages

-- isor:
SELECT *
FROM dbo.MVItest
WHERE loginID = ?
? = 180000006840

-- isor:
SELECT *
FROM dbo.MVItest
WHERE loginID = ?
? = 160000006242

-- isor:
SELECT *
FROM dbo.MVItest
WHERE loginID = ?
? = 13536

-- An error occurred in Adaptive Server Enterprise:
ERROR [42000] [Sybase][ODBC Driver][Adaptive Server Enterprise]Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed. Use the CONVERT function to run this query.

Sign In to Post

Welcome back! Login to your account and get access to the CardExchange® Community and start posting today.

Please read our community rules!

Account Registration

Please fill in the fields below and become a member of our CardExchange® Community today!

Please enter the name!
Please provide an username!
Please provide a valid e-mail!
Retype the e-mail!
Please enter a password!
Retype the password!
Invalid Input
After you submit your registration you will receive an email with information about your account

ISO/IEC 27001:2013 

United States

201 Sand Creek Rd, Suite L-E
Brentwood, CA 94513

Monday – Friday
9am – 5pm (EST)
+1 (925) 529 4999

Netherlands

Smallepad 32
3811 MG Amersfoort

Monday – Friday
9am – 5pm (CET)
+31 (0)20 2251 447

© 2022 CardExchange, Inc.