Trust ID PBCEX logo 300x102

Connecting native to SYBASE

5 years 10 months ago
Mark Vinkx
Senior Boarder
Senior Boarder
Posts: 52
More
Topic Author
Connecting native to SYBASE #6069
Hi

I want to connect to a SYBASE database. I see it is currently not supported. Can I make it work with dropping the right sybase ADO dll in the cardexchange installation directory and setting some other configuration parameters or would it require coding ?

The reason I ask is that I now user ODBC with limited succes
5 years 10 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Connecting native to SYBASE #6070
Hi Mark,

No, it is not as simple as that, unfortunately. It would require development from our site, but there are no plans to support Sybase in the near future. What problems are you experiencing with ODBC?

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)
5 years 10 months ago
Mark Vinkx
Senior Boarder
Senior Boarder
Posts: 52
More
Topic Author
Connecting native to SYBASE #6072
If I start cardexchange at first everything is working. But then after a while, usaualy after a period of inactivity, I start getting errors when clicking on a record
The error is Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed. Use the CONVERT function to run this query.
See the extract from the dataexchange log

SELECT *
FROM "dbo"."MVItest"
WHERE "loginID" = ?
? = 260000021684

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

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

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

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

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


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

-- 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.
5 years 10 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Connecting native to SYBASE #6074
Hi Mark,

Is the LoginID column of the type VARCHAR or NUMERIC? One thing you could try is to remove the quotes from the query by going into the Advanced options (Configuration menu) and unselect "Quote table and column names". Maybe the database for some reason starts interpreting "LoginID" as a string instead of a column name.

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)
5 years 10 months ago
Mark Vinkx
Senior Boarder
Senior Boarder
Posts: 52
More
Topic Author
Connecting native to SYBASE #6075
In need the "quotable names" otherwise it doesn't work at all

The loginID is numeric

Column_name Type Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity


----







loginID numeric 8 16 0 0 0 NULL NULL NULL NULL 0
naam varchar 50 NULL NULL 1 0 NULL NULL NULL NULL 0
voornaam varchar 50 NULL NULL 1 0 NULL NULL NULL NULL 0
roepnaam varchar 50 NULL NULL 1 0 NULL NULL NULL NULL 0
loginnaam varchar 50 NULL NULL 0 0 NULL NULL NULL NULL 0
geboortedatum datetime 8 NULL NULL 1 0 NULL NULL NULL NULL 0
personeelsnummer varchar 50 NULL NULL 1 0 NULL NULL NULL NULL 0
externID varchar 200 NULL NULL 1 0 NULL NULL NULL NULL 0
5 years 10 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.
5 years 10 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)
5 years 10 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
5 years 10 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)
5 years 10 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 ?