Oracle - wrong parameters mapping in stored function call

The alpha/beta tester's forum for ZeosLib 7.0.x series

Report problems concerning our Delphi 2009+ version and new Zeoslib 7.0 features here.

This is a forum that will be removed once the 7.X version goes into stable!!

Moderators: gto, EgonHugeist, olehs

serbod
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 27.12.2012, 09:31

Oracle - wrong parameters mapping in stored function call

Postby serbod » 27.12.2012, 10:55

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.

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 29.12.2012, 12:48

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?
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

serbod
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 27.12.2012, 09:31

Postby serbod » 01.01.2013, 10:53

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.

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 01.01.2013, 13:24

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..
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

serbod
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 27.12.2012, 09:31

Postby serbod » 01.01.2013, 14:30

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

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 01.01.2013, 14:45

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?
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

serbod
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 27.12.2012, 09:31

Postby serbod » 01.01.2013, 14:59

EgonHugeist wrote: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.


EgonHugeist wrote: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.

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 02.01.2013, 15:42

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..
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 03.01.2013, 15:04

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
Image

serbod
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 27.12.2012, 09:31

Postby serbod » 09.01.2013, 10:04

Example:

Image

Image

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 13.01.2013, 12:23

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.
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 14.01.2013, 21:14

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?
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image


Return to “ZeosLib 7.0 Beta Forum”

Who is online

Users browsing this forum: No registered users and 1 guest