Oracle Number mapped to Decimal

Oracle Number mapped to Decimal

Postby labate » Fri 15 Jun 2007 13:05

Hello,

I'm using OraDirect .NET 3.55.20.0 with Enterprise Library 2.0.

I have the following Oracle sp
procedure GetPersonByPersonId(in_personId in mspak.person.personid%type, out_cursor out T_CURSOR)
returning a cursor where one of the fields correspond to a Oracle column whose type is:
personid number not null

With the DataTableReader, when I do
idx = reader.GetOrdinal("personid");
if (!reader.IsDBNull(idx)) person.PersonId = reader.ToInt32(idx);

It fails and I have to do instead
idx = reader.GetOrdinal("personid");
if (!reader.IsDBNull(idx)) person.PersonId = Convert.ToInt32(reader.GetDecimal(idx));

It seems that all Oracle integer types are mapped to a .NET Decimal type.

Is it the correct behavior, or is there a way to directly use GetInt32?

Thanks
Adriano
labate
 
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

Postby Alexey » Mon 18 Jun 2007 08:36

It fails and I have to do instead
How does it fail? Please specify. I tested your code reading data from the table dept and haven't got any error. If the problem occurs only with enterprise library, stored procedures and ref cursors, please provide me with a test project and your database objects definitions.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby labate » Mon 18 Jun 2007 09:17

Hello,

The exception stack trace is not very informative:

System.InvalidCastException: Specified cast is not valid.
à System.Data.DataTableReader.GetInt32(Int32 ordinal)
à Polyright.xxx.yyy.DatabaseAccess.GetAccountByPersonId(Int32 personId) dans ...\DatabaseAccess.cs:ligne ...


This occurs when I do:

Code: Select all
        public Account GetAccountByPersonId(int personId) {
            try {
                DbCommand dbCommand = database.GetStoredProcCommand("xxx.GetAccountByPersonId");

                database.AddParameter(dbCommand, "personId", DbType.Int32, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, personId);
                dbCommand.Parameters.Add(CreateCursorParameter("cursor"));

                IDataReader reader = database.ExecuteDataSet(dbCommand).CreateDataReader();

                Account account = null;
                int idx;

                if (reader.Read()) {
                    account = new Account();

                    // CORRECT : Use reader.GetDecimal()
                    idx = reader.GetOrdinal("accountId");
                    if (!reader.IsDBNull(idx))  account.AccountId = Convert.ToInt32(reader.GetDecimal(idx));

                    // ERROR : InvalidCastException
                    idx = reader.GetOrdinal("accountstatusid");
                    if (!reader.IsDBNull(idx))  account.AccountStatusId = reader.GetInt32(idx);

                    ...
                }

                return account;
            } catch (Exception e) {
                log.Error("GetAccountByPersonId : un problème est survenu", e);
                throw;
            }
        }


Although I use Enterprise Library 2.0, the underlying database provider is OraDirect .NET 3.55.20.0. I don't know where is the responsability of doing the data types mapping.

In the code sample above, the two fields are NUMBER columns in Oracle and I thought it makes sense to call GetInt32, but for all Oracle number columns, even for a double, I have to call GetDecimal() and then use Convert.ToInt32 or Convert.ToDouble.

Sorry, I have not the time to build a sample project, and it is not a blocking problem.

Thanks
labate
 
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

Postby Alexey » Tue 19 Jun 2007 08:15

Could you at least provide me with your database objects definitions?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby labate » Tue 19 Jun 2007 08:48

I've sent you the requested information directly to your email address.

Adriano
labate
 
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

Postby Alexey » Tue 19 Jun 2007 09:16

OK, I'll take a look.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for Oracle