Problem checking for DBNull.Value in return value from Oracle stored proc. using ODP.NET 10.2.0.2.20
Joined: 13-Oct-2005
Hmm. So oracle decided in 10.2 for oracle to convert the value to an oracle type class anyway and set a property on that, instead of keeping it simple and return DBNull.Value.. 
I could move up the valueconvert so the result of that is used in the test.
You're moving from 9i to 10g ODP.NET. Did you convert the .lgp file to the 10g driver first? If not, the templates used are still the ones of 9i.
After conversion with the project converter (see 2.0 extras section in customer area) you're using the 10g driver and thus also the 10g templates.
Could you test for me if this works: (first create copy of the template) in the templates\Oracle10gSpecific\Net2.x\C# folder, open actionProcecduresAdapter.template in a texteditor, for example notepad.
Starting at line 64, you'll find this snippet:
<[Foreach OutputParameter CrLf]> if(parameters[<[ParameterIndex]>].Value!=System.DBNull.Value)
{
<[CaseCamel CurrentParameterName]> = (<[If IsNullable]><[If IsValueType]>Nullable<<[TypeOfParameter]>><[Else]><[ TypeOfParameter]><[EndIf]><[Else]><[TypeOfParameter]><[EndIf]>)ValueConverter.Convert(parameters[<[ParameterIndex]>]);
}<[NextForeach]>
which generates the faulty piece of code.
If you change that into:
<[Foreach OutputParameter CrLf]> object parameterValue = ValueConverter.Convert(parameters[<[ParameterIndex]>]);
if((parameterValue!=null) && (parameterValue!=System.DBNull.Value))
{
<[CaseCamel CurrentParameterName]> = (<[If IsNullable]><[If IsValueType]>Nullable<<[TypeOfParameter]>><[Else]><[TypeOfParameter]><[EndIf]><[Else]><[TypeOfParameter]><[EndIf]>)parameterValue;
}<[NextForeach]>
and starting at line 160, you'll have this:
case "Oracle.DataAccess.Types.OracleString":
toReturn = ((OracleString)parameter.Value).Value;
break;
If you change that into:
case "Oracle.DataAccess.Types.OracleString":
if(((OracleString)parameter.Value).IsNull)
{
toReturn = null;
}
else
{
toReturn = ((OracleString)parameter.Value).Value;
}
break;
and generate code again, it should work.
What's sad is that Oracle did implement their types with an interface INullable, so nulltesting COULD be done, however... their smart programmers made that interface... internal..
Joined: 13-Oct-2005
KAF wrote:
Thanks for the fast reply!
I converted the LLBLGen project file and altered the template as you described. It all seems to work fine now. As long as there are no other breaking changes when migrating to ODP.NET 10.2.0.2.20 we should be ok.
One thing though: when changing the template code to
<[Foreach OutputParameter CrLf]> object parameterValue = ValueConverter.Convert(parameters[<[ParameterIndex]>]); etc...you will get the "object parameterValue" declaration repeated for each parameter in the generated code when using several parameters. I guess you could just move the declaration of the "parameterValue" outside the "Foreach" loop.
yes, that's indeed a mistake from my part, thanks for that 
so the code becomes:
object parameterValue = null;
<[Foreach OutputParameter CrLf]> parameterValue = ValueConverter.Convert(parameters[<[ParameterIndex]>]);
if((parameterValue!=null) && (parameterValue!=System.DBNull.Value))
{
<[CaseCamel CurrentParameterName]> = (<[If IsNullable]><[If IsValueType]>Nullable<<[TypeOfParameter]>><[Else]><[TypeOfParameter]><[EndIf]><[ Else]><[TypeOfParameter]><[EndIf]>)parameterValue;
}<[NextForeach]>
Is there going to be a fix released anywhere in the nearby future?
Yes. Our main office is currently being redecorated/restuctured so we're currently located in a temporary office and I can't reach the oracle testbox from here via Wifi, it has low or no reception from my devbox (which isn't a laptop unfortunately). So I expect to test it in-house in about a week or so.
I hope you can fix your own project with the template fix. Please let me know if this template fix indeed doesn't or does work. Sorry for the inconvenience.
Joined: 13-Oct-2005
Yes, that's better indeed .
thanks for testing. I'll make sure the soonest I get access to our oracle box again I'll make the fix and release it.
Joined: 12-Nov-2006
Joined: 13-Oct-2005
That is correct, we modified the template as you described only a couple of days ago when we ran into the same problem with the OracleDecimal type. It works fine after the fix. Was about to post a note here about it 
.
Hopefully it will be included in a future release.
Kjell-Arne
Joined: 27-Jun-2007