Calling an Oracle Stored Procedure from VFP

[andre65]

Hello all,

I try to call an Oracle Stored Procedure through ODBC without succes. Does anyone know how to do this?

The connection handle is stored in thisform.nConnection.
Running the code will result in errorcode -1.

This is a piece of my code where TFX_PC_COMMSDK is an Oracle package, P_COMMSDK_EVENT is an Oracle Stored Procedure, P1 .. P7 are IN parameters and the last 3 are OUT parameters. All Oracle parameters have the type NUMBER or VARCHAR2.

lcBuffer = ''
lcOK = ''
lcError = ''

lcSP = "TFX_PC_COMMSDK.P_COMMSDK_EVENT(?p1,?p2,?p3,?p4,?p5,?p6,?p7,?lcBuffer,?lcOK,?lcError)"

lnResult = SQLEXEC(thisform.nConnection,lcSP)

DO CASE
CASE lnResult>0
IF lcOK='J'
WAIT WINDOW 'Succesfull'
ELSE
WAIT WINDOW 'Parse error'
ENDIF
CASE lnResult<0
=f_msg_info('Error='+f_str(lnResult))
ENDCASE

Thanks in advance for any reply,
André

 

[andre65]

Hi all,

Maybe ODBC SQLEXEC is not the solution (I think it's purpose is for select, insert, update and delete statements)

I was thinking about OLEDB. Does anyone have experience with OLEDB? Is there documentation available, samplecode, reference websites?

I tried OLEDB finding on the web, but didn't satisfy my needs.

Thanks in advance for any reply.
André

 

[badukist]

In Interbase/Firebird, something like :

lcSP = "select * from TFX_PC_COMMSDK.P_COMMSDK_EVENT(?p1,?p2,?p3,?p4,?p5,?p6,?p7)"
lnResult = SQLEXEC(thisform.nConnection,lcSP)

is valid. Maybe is possible in Oracle too.

 

[andre65]

Badukist,

I tried your suggestion ... still got resultvalue -1.

André

 

[badukist]

Have you retrieved error info using AERROR() ?

 

[andre65]

With the SELECT clause in it, I get:
Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-00933: SQL command not properly ended
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00933: SQL command not properly ended
37000

Without the SELECT clause in it, I get:
Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement
37000

BUT, when I include the user in the calling Stored Procedure (in this case TF_TFX.TFX_PC_COMMSDK...), no error is raised but I have to enter values for each parameter (p1..p7,lcBuffer,lcOK,lcError). This should not happen because I do that programmaticly. Maybe another mark than the questionmark should be used?

 

[wgcs]

Does oracle need a &quot;;&quot; on the end of the command? Maybe it needs an &quot;AS fieldname&quot; clause?

lcSP = "select P_COMMSDK_EVENT(?p1,?p2,?p3,?p4,?p5,?p6,?p7) AS result FROM TFX_PC_COMMSDK"
lnResult = SQLEXEC(thisform.nConnection,lcSP)

 

[wgcs]

This may help:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;174679

It shows the &quot;SQL&quot; format:
&quot;{call SimplePackage.Proc1(?Arg1, {resultset 3, o_id , ao_course, ao_dept})}&quot;


On this page:

http://www.datadirect-technologies.com/products/odbc/docs/oracle-driver-faq.asp

under the heading &quot;Are Stored Procedures (PL/SQL) Supported?&quot; it seems to imply that some Oracle ODBC drivers DON'T use the &quot;standard&quot; way of calling Stored procedures


Here's another page:

http://techsupt.windowware.com/TS/T000002015F9.html

recommending:
SqlTxt = '{call tstproc(&quot;ocadm&quot}'

 

[andre65]

When I use
&quot;{call tf_tfx.tfx_pc_commsdk.p_commsdk_event(?p1...)}&quot;
I get an error.

When I use
&quot;call tf_tfx.tfx_pc_commsdk.p_commsdk_event(?p1,...&quot;
it seems to work, but finally I get an error:
Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-03113: end-of-file on communication channel
[Microsoft][ODBC driver for Oracle][Oracle]ORA-03113: end-of-file on communication channel
08S01

The Stored Procedure (SP) has in and out parameters. So I tested a SP without out parameters using:
&quot;call tf_tfx.tfx_pc_commsdk.p_commsdk_test(?p1,...)&quot;
AND THIS WORKS!

So finally I have to know how to use out (returning) parameters. The way I use it now, produces above mentioned error.