Oracle - wrong parameters mapping in stored function call

[serbod]

Oracle 10 / Win32 / Lasarus 1.0.4 / Zeos 7.0.SVN

Code: Select all

ZProc.StoredProcName:='ADD_DOC';
ZProc.ParamByName('l_mifare_id').AsString:=Account.MifareID;
ZProc.ExecProc();

Causes error:
EZVariantExeption - Types mismath
ZVariant.pas - 395

Backtrace show, that problem in TZOracleCallableStatement.ArrangeInParams() - it put additional InParamValues[] item and shift all items to right. Comment says {now move Returnvalue to first position}, but actually, ReturnValue goes as last parameter.

I fixed it, function called properly and even return result, but OCI can't write back some writable parameters and call Oracle exception. Maybe I can fix it, but it take many time to understand whole process.

I suggest don't separate abstract parameters (TParam) and actual Oracle parameters (TZOracleParam) in different lists. TZOracleParam can be derieved from TParam or have TParam as field - it solve many troubles with mapping.

[EgonHugeist]

serbod,

sorry for the delay.. It's vacation time currently.

It was me who made these modifications. Like you've do i rearrange the Parameter order. This i've done to have an equal behavior we have on all other drivers. Now i don't know what exactly is going wrong. Normaly your Parameter-order should be like this: In/InOut/Out-Parameters first (order by it's original sequence) and function (ResultValue) last (the reason for my changes).

Could you please attach a simple demonstration app which points me to the issues we're talking about?

[serbod]

Examples will be later, after 10 days, sorry.

I notice another flaw - when requesting metadata for function, program perform two requests: for procedure (param number >=1) and for function result (param number = 0). It not bug, but it not good feature.

[EgonHugeist]

serbod,

the only "save" way to determine a function ReturnValue is to check the Parameter-Position. AFAIK the ReturnValue has allways Pos=0.

Why do you think it's not a good idea to do this in this way?

Well to fix your issue i still need an example to work with..

[serbod]

We can just fetch all parameters by one request. If there param with pos=0, then it ReturnValue. Procedures don't have parameter with pos=0, anyway.

I don't have access to database from home, but examples very simple. Function with some parameters:

Code: Select all

FUNCTION test (
some_id IN NUMBER,
some_str OUT VARCHAR2,
some_option IN NUMBER DEFAULT 0)
RETURN NUMBER

Then I do:

Code: Select all

ZProc.StoredProcName:='test'; 
ZProc.ParamByName('some_id').AsString:=1234; 
ZProc.ExecProc();
DumpParams(ZProc); // dump all params to log
--------------------
some_id (Float) = 1234.0
some_str (String) = '' // must be not empty
some_option (Float) = 9.2345434E+22 // some garbage
ReturnValue (Float) = 1234.0 // it's OK

[EgonHugeist]

serbod,

We can just fetch all parameters by one request. If there param with pos=0, then it ReturnValue. Procedures don't have parameter with pos=0, anyway.

Well that's propably right, i know. But i wasn't able to set the ReturnValue to the last place on the list with just one fetch (An issue of the VirtualResultSet vs. Interfaces). So i decided to use two calls do determine if the StoredProcedure can be handled as Procedure or as Function.

If i understand you right then the default value of Parameter(some_option) wasn't correctly set to 0. That could be an issue on the default Values of the MetaInformations. (For Oracle currently an Alpha state since we've started to rework the TZStoredProc-Component).

Which value should 'some_str' return in you example?

[serbod]

Well that's propably right, i know. But i wasn't able to set the ReturnValue to the last place on the list with just one fetch (An issue of the VirtualResultSet vs. Interfaces). So i decided to use two calls do determine if the StoredProcedure can be handled as Procedure or as Function.

You can store ReturnValue properties to local temporary item, and then just add it to parameters list, after other parameters.

If i understand you right then the default value of Parameter(some_option) wasn't correctly set to 0.

Maybe, I don't remember and can't check now. But trouble is that OUT parameter don't return correct value. It must be non-empty string, 'OK' for example. And I suspect, that string value received by another, non-string parameter.

If string is long enough, then OCI report some error about parameters.

[EgonHugeist]

serbod,

You can store ReturnValue properties to local temporary item, and then just add it to parameters list, after other parameters.

Agree, don't hesitate to attach a patch for that.

Maybe, I don't remember and can't check now. But trouble is that OUT parameter don't return correct value. It must be non-empty string, 'OK' for example. And I suspect, that string value received by another, non-string parameter.

If string is long enough, then OCI report some error about parameters.

Ok i understand. Anyway a little example which points me to the issue directly woudl be great..

[mdaems]

Gentlemen,

Don't forget to open a ticket here : https://sourceforge.net/p/zeoslib/tickets/

serbod,
If you're used to working with oracle (and even better, their OCI), don't hesitate to give us a hand. All (valid ) patches are welcome.
This stored procedure work is still in quite an alpha state. Mainly because the developers can't know all databases equally well.

Mark

[serbod]

Example:

[EgonHugeist]

serbod,

thank you for the example. Issue is confirmed. I've added your example to our tests and going to realign the parameters right now.. I'll post again if i've solved this issue.

Next times use our bugtracker on SourceForge.Net like Mark suggest.

[EgonHugeist]

serbod,

patch done. R2126 \testing-7.1 (SVN). Can you test it?

I've simplified the Parameter-Position-Mapping a lot. What i haven't changed is the MetaInformation double request. All current test do succeed also your attached one..

Do you have some OCI expieriences?