Oracle number parameters precision for stored proc

Posts   
 
    
Posts: 64
Joined: 30-Aug-2010
# Posted on: 15-Apr-2011 14:30:02   

Hello

I am using LLBLGen Pro 3.

I have a project that works with both Oralce and SqlServer databases. For SqlServer, for the type corespondance between database type and c# data types, I have created some type convertors, and it worked fine.

But now, when trying to set up the project also for Oracle I encounter some problems. In Oracle, for the stored procedures, there is no precision and scale information. The type resulted is some NUMBER(38, 38 ) and NUMBER(38,0).

I found an older thread on a similar issue: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14441&HighLight=1

I have copied the into: LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\Templates\OracleODPNetSpecific, the tamplates that were indicated in this thread, but nothing has changed so far. The parameters have these same precisions NUMBER(38,38 ) and NUMBER(38,0).

Could you please help me with a solution? Thank you

Posts: 64
Joined: 30-Aug-2010
# Posted on: 15-Apr-2011 15:55:20   

Also, when regeneratin the classes for the Oracle database, I noticed that the ValueConverter class, from the ActionProcedures is missing.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 15-Apr-2011 21:57:27   

The fixes you saw in that old thread is for v2.6, so please revert the changes or reinstall LLBLGen v3 because that could break your code.

As for the parameters, as you know, the scale/precision info is not available for meta data so LLBLGen couldn't decide the correct info. Please go to your store procedure call and edit it in LLBLGen Designer. There you can change the scale/precision of the parameter. Do that work for you?

David Elizondo | LLBLGen Support Team
Posts: 64
Joined: 30-Aug-2010
# Posted on: 18-Apr-2011 09:14:32   

Hello,

I have eliminated those files from the templates folder, and regenerated the project. Thank you for the warning.

As for the parameters, I could not find how to change the scale/precision. After adding the stored procedure to the project, I edited the procedure, selected the parameter that I need to change, went to Parameter mappings tab, but there I could not change anythnig. The fields are readonly.

Could you please indicate me how to do it?

Thanks

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 18-Apr-2011 09:55:17   

I guess David means to edit in the "Catlaog Explorer".

Posts: 64
Joined: 30-Aug-2010
# Posted on: 18-Apr-2011 11:52:10   

Ok, but what exactly do I have to do?

I have also tried it from the Catalog Explorer. I have selected my parameter from the SP, right click : Show Details, but there I cannot change anything.

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 18-Apr-2011 17:07:41   

Sorry my mistake.

David was right you can go to the SP call in the project Explorer and right click the SP-Call and select Edit.

Then in the opened widnow, you can double click any value to edit it.

Posts: 64
Joined: 30-Aug-2010
# Posted on: 19-Apr-2011 13:58:36   

Yes, it is correct. After editing the entity, on the Fields tab, I was able to modify it.

Thank you

Posts: 64
Joined: 30-Aug-2010
# Posted on: 19-Apr-2011 15:09:23   

But my other question remains. What happenet with the ValueConverter class from the ActionProcedures for Oracle?

MTrinder
User
Posts: 1461
Joined: 08-Oct-2008
# Posted on: 19-Apr-2011 22:11:21   

When you say it is missing, do you mean that it was there before and is not there when you regenerate now ?

Or that one is generated for SQL server, but not for Oracle ?

If you try to compile the Oracle code, does it protest about the missing file ?

Matt

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39908
Joined: 17-Aug-2003
# Posted on: 20-Apr-2011 09:52:11   

That class isn't used anymore, because the code doesn't have references to ODP.NET, it works with DbProviderFactory now. We moved the converter for values to the DQE, in the db specific creator. It works a little different now as it doesn't have any reference to odp.net types, but it tries to convert the value to the type specified. See the runtime sourcecode, OracleSpecificCreator.cs, ConvertParameterOutputValueToRealValue

Frans Bouma | Lead developer LLBLGen Pro
Posts: 64
Joined: 30-Aug-2010
# Posted on: 20-Apr-2011 11:31:35   

Thank you for the information.

So, if I have something like this:

OracleParameter[] parameters = new OracleParameter[1]; parameters[0] = new OracleParameter("vReturn", OracleDbType.Int32, 0,

// Call the stored proc. int toReturn = adapter.CallActionStoredProcedure("ADM_NET.F_HAVE_AUTHORISATION", parameters); if(parameters[0].Value != System.DBNull.Value) { //old code //RETURN_VALUE = Convert.ToInt32( ValueConverter.Convert(parameters[0])); }

I now rewrote the :

RETURN_VALUE = Convert.ToInt32( ValueConverter.Convert(parameters[0]));

to

RETURN_VALUE = Convert.ToInt32(new SD.LLBLGen.Pro.DQE.Oracle.OracleSpecificCreator().ConvertParameterOutputValueToRealValue<System.Int32>(parameters[0]));

And it works. Thank you