We are developing a standalone application that stores its data in a Database. We use Dataset and TableAdapters for the communication with the Database. One of the basic requirements is the application to be able to use SQL Server, Oracle and MySQL. For this purpose we made the queries in the TAs vendor independent. We use ODBC provider.
It works fine for SQL Server and MySQL, but with Oracle we had problems. One of the problems we can not overcome is the casting error of decimal to Int64.
Oracle tables use NUMBER(38) for system IDs (autoincrement fields) that as it seems are mapped to decimal. However we use Int64/long in the DataSet/TAs since SQL Server and MySQL use BIGINT. (Note that the DataSEt TableAdapters where auto-generated from a SQL Server schema in the beginning.)
We get a cast exception when getting a NUMBER value from Oracle since it returns a decimal object.
E.g a code sample:
DbCommand getIDcmd = connection.CreateCommand();
getIDcmd.CommandText = "select LAST_ID from SEQUENCE_ID";
sequenceID = (Int64)getIDcmd.ExecuteScalar(); // we get a cast exception here returns decimal
please note that we've tried to lower the precision number in Oracle (e.g. NUMBER(10)) as we've seen with other forum entries but with no luck.
Is this an ODBC problem? If we move to other provider will solve the problem?