Solved TD-3949: Calls to procedures does not work

Post found bugs and possible workarounds.
belitski

TD-3949: Calls to procedures does not work

Post by belitski » 24 Oct 2007, 07:46

Hi,

we have got trial version of TD and recompiled with very small changes an old project (CTD1.5.1). However, by starting application we run immediately into Oracle errors. It seems that calls to Oracle procedures do not work as before. In CTD1.5.1 to call procedure in the Oracle package it was enough to do the following:

Set lsSql = "DBMS_APPLICATION_INFO.SET_MODULE(psModuleName,psAction)"

If SqlPLSQLCommand(phSql,lsSql)

...

Now:

1. Without SYS specificator the procedure will not be found.

2. Even with SYS specificator, we get ORA-24328 or ORA-24323.

In the log file in new version from all bind variables only the first character is shown.

What are we doing wrong?

erousseau

Re: TD-3949: Calls to procedures does not work

Post by erousseau » 25 Oct 2007, 10:48

I have exactly the same problem (same procedure and same error) !



Any clue ?



Etienne

belitski

Re: TD-3949: Calls to procedures does not work

Post by belitski » 25 Oct 2007, 11:57

No, we still don't understand it.

Have you any idea what should be done in order to be able execute procedures from another schema? Grants and public synonyms are Okey, but somehow it is not enough.

I see that some post attract attention of Gupta developer, this one unfortunately not.

Jean-Marc Gemperle

Re: TD-3949: Calls to procedures does not work

Post by Jean-Marc Gemperle » 26 Oct 2007, 11:44

Hi,



"I see that some post attract attention of Gupta developer, this one unfortunately not."



It did :-) It is important especially the title of your post. So here is what I tested and found out



1)You are right there is a problem in the TD 5.1 native router with SqlPLSQLCommand

2)There is no problem it seems (at least with my testcase) with the SqlORA**() function instead

3)Thre is no problem with OLEDB neither.



So the issue is NOT that something speciall need to be done on the ORACLE side (I use 10g), since with TD 4.2 the testcase works fine with NATIVE router and SqlPLSQLCommand/SqlOra**()/and OLEDB SqlPrepareSP...and it is not in TD 5.1 for NATIVE ROUTER and SqlPLSQLCommand but the rest does...thus clearly a bug...



So here is the BUG LOG TD-3949 ...



Currently to worakround, you can use SqlORA***() functions it seems, as you probably know you cannot use DYNAMIC ARRAYS, that is the only limitation. Overall we always recommanded the uses of those functions instead of SqlPLSQLCommand() for at least performance issues. The syntax change is minimal also so there should not be to much problem switching to them. You can use also OLEDB, but you will have to test further your application and do changes ie ( SqlImmediate, SqlVarSetup not supported on OLEDB etc..)



I attach my testcase so you can confirm this is indeed the problem and like to know if for you SqlORA**() fixes this issue. If you do have problem with SqlORA***() or anything else, please consider passing a testecase if possible because it really helps us to speed up on those issues.



Thanks

Jean-Marc
You do not have the required permissions to view the files attached to this post.

belitski

Re: TD-3949: Calls to procedures does not work

Post by belitski » 26 Oct 2007, 14:17

Hi Jean-Marc,

thank you for answering.

We would use SqlOraPLSQL* functions instead of SqlPLSQLCommant, but for some reason it does not work either.

I have attached a screen short with your test case, where count is 0 (must be 1).

No error message.

What could it be?



Thank you,

Vladimir
You do not have the required permissions to view the files attached to this post.

Jean-Marc Gemperle

Re: TD-3949: Calls to procedures does not work

Post by Jean-Marc Gemperle » 26 Oct 2007, 16:28

Hi there,



First I thought I overlooked something before telling you it works, but it does actually for me, see snapshot. Also was confused for a few about SqlFetchNext() and thought was needed but obvisouly. I also set now the nCount variable to 0 before the call and got 1 as a result as it should. So for me this is working fine both SqlORA and OLEDB



Can you try OLEDB with the testcase, you just need to modify the attached UDL it might tell us more...



Also I attach process explorer DLL loaded for CBI51.EXE during the test with it you can see what I used...I used ORA 10gR2 on VISTA...



Let me know if you find something and the results wiht OLEDB..



JM
You do not have the required permissions to view the files attached to this post.

Jean-Marc Gemperle

Re: TD-3949: Calls to procedures does not work

Post by Jean-Marc Gemperle » 26 Oct 2007, 16:32

Hi Again,



Sorry forgot the process explorer file and also try with the change you did

Call SqlOraPLSQLPrepare( hSql, 'BEGIN pkgtest.test(:nCount); END;' )

In my test did not use BEGIN END... I still have 1 as return.



JM

Jean-Marc Gemperle

Re: TD-3949: Calls to procedures does not work

Post by Jean-Marc Gemperle » 26 Oct 2007, 16:37

Again as the name contained .exe...
You do not have the required permissions to view the files attached to this post.

belitski

Re: TD-3949: Calls to procedures does not work

Post by belitski » 29 Oct 2007, 09:50

Hi,

thank you very much for help.

The usage of SqlOraPLSQL* functions solves the problem with SqlPLSQLCommand. Although we have to check all calls to stored

procedures.

It works also with OLEDB.

Can you estimate,when the problem with SqlPLSQLCommand will be solved?



Vladimir

Jean-Marc Gemperle

Re: TD-3949: Calls to procedures does not work

Post by Jean-Marc Gemperle » 29 Oct 2007, 12:06

Hi Vladimir,



Yes I can imagine checking all SP and change them to SqlORA is a pain... but I guess it is worth it. Still this needs to get fixed, but cannot give you an estimate but ORACLE access is used by many and that make this issue very important. I will check non the less.



Cheers

Jean-Marc

belitski

Re: TD-3949: Calls to procedures does not work

Post by belitski » 30 Oct 2007, 15:49

Hi,

probably I should open another topic, just tell me that.

We are running into more problems.

Please, have a look in the attached file.

The log shows some query, that fills the table. After that,

some value in one of the records in the result set is changed and

update is applied. This does not work. The same happens with delete

command.

Two points are probably important:

(1) ROWID bind variable is lost;

(2) only the first character is shown for all other bind variables.

In the older version both things are different and both update and delete work. What is wrong?



Vladimir
You do not have the required permissions to view the files attached to this post.

Jean-Marc Gemperle

Re: TD-3949: Calls to procedures does not work

Post by Jean-Marc Gemperle » 31 Oct 2007, 10:22

Hi Valdimir,

I'm sure you have seen those issues already

TD-3948

TD51 Oracle router reports ORA-01410: invalid ROWID when updating a table with rowid

It seems to be related but like to be sure about it.

Jean-Marc

belitski

Re: TD-3949: Calls to procedures does not work

Post by belitski » 31 Oct 2007, 10:35

Hi Jean-Marc,

we use TD 5.1.0.5917

Should it be already fixed in this build?

If yes, then there is still a problem.



Vladimir

Jean-Marc Gemperle

Re: TD-3949: Calls to procedures does not work

Post by Jean-Marc Gemperle » 31 Oct 2007, 14:31

Hi Vladimir,



Not in yours currently, it was a recent fix and I like to check it.



Jean-Marc

belitski

Re: TD-3949: Calls to procedures does not work

Post by belitski » 01 Nov 2007, 08:51

Hi Jean-Marc,

have you got my test case for ROWID problem?



Vladimir

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 1 guest