PB10.5, DB2, ODBC/OLE DB

[Russ]

Hi everyone,

I hate cross reference posts but the issue gets urgent.

I have migrated an app from PB 7 to PB 10.5.
The app is running against DB2 using ODBC connectivity.

After migration the problem #1: can't call stored proc.
Being invoked as RPC I am getting an error:
Database Interface does not support Remote Procedure Calls.

OK, I try to declare and execute the sp in a PB script and
get another error:
SQLSTATE = 42724
[IBM][CLI Driver][DB2/NT] SQL0444N Routine "*P_VALIDATOR"
(specific name "SQL080827084440780") is implemented with
code in library or path "\SP_VALIDATOR", function
"SP_VALIDATOR" which cannot be accessed. Reason code: "4".
SQLSTATE=42724

Well, no success with ODBC so I choose to use OLE DB -
IBMDADB2.

No stored proc problem ! Great ! No so fast ...

The app has to place some images into database. And that is
where UPDATEBLOB fails (which used to work perfectly with
ODBC).
The error is: An error occurred, but error text could not be
retrieved due to a failure in the error handler.

Thanks in advance for any help.

[Jason 'Bug' Fenter [TeamSybase]]

I'm not sure how to fix the problem, but I can suggest a work-around.
Why not use OLE DB for your main application execution, but open a
second connection with a new transaction object using ODBC and use that
JUST for the UPDATEBLOB calls?

[Russ]

Jason,
Thank you for a quick response.

The offered solution is considerable but to be honest - it's
at the bottom of my list of solutions because of not small
amount of changes (not just in code but
installation/configuration as well).

I blame myself I am missing something in ODBC/RPC ... I can
understand that OLE DB driver may have some issue with blob
but I can not imagine nobody runs PB 10 and stored proc
against DB2 using ODBC !

Thanks.

[Ed Van Haren]

We call db2 stored procedures. If I remember correctly, the initial error
you are getting may be caused by the settings in your pbodb100.ini file. I
don't recall which one it is, but I believe it is one of the following:

PBSupportBindSelect='NO'
PBSupportBindUpdate='NO'
PBSupportDBBind='YES'

These are the settings we use with success. I believe the initial install of
PB has different settings.

Ed

<Russ> wrote in message news:48b56ae0.b0...@sybase.com...

[Russ]

Ed, Thank you.

Unfortunately, it did not help.

[Ed Van Haren]

Just a note, make sure you changed the right file. In our case, we deploy
the PB runtime in a specified directory. However, when running from the IDE,
PB uses the ini from where you installed PB. A few developers got hit on
that when we migrated to PB 11.1.

<Russ> wrote in message news:48b7e740.7c0...@sybase.com...

[Russ]

Ed,

Thank you for keeping help me.

I made those changes in the PBODB105.INI which is
historicaly resides in C:\...\Sybase\Shared\Powerbuilder
folder. Additionally, I copyed this file (aftrer chnages, of
course) to the folder where actual app is. Still no luck.

Except of one little thing:
I installed a new version of DB2 Client (it was v.8, now
it's v.9) and I am getting different error messages (like
"SQL0104N An unexpected token "INOUT" was found ...").

This is the latest sp declaration:

DECLARE sp_next PROCEDURE FOR SP_GET_NEXT
(:ls_userid, :li_rc INOUT)
USING sqlca;

EXECUTE sp_next;

Can you find anything wrong in it ?

Thanks.