Can I trigger ORACLE stored procedures from VFP6? 1

[petervfp]

I have an ORACLE database that I'm hooked to via VFP6.0
I can create remote views and use SQLExec() to fire off SQL statements against the ORACLE tables. I need to trigger/execute some ORACLE stored procedures from a VFP program. I've tried this:

Note that the stored procedure is called STPROCNAM in this example:

nHandle = SQLConnect('db','userid','password')
IF nHandle > -1       * connection established
   SQLFlag = SQLExec(nHandle,'stprocnam')
ENDIF

The stored proc doesn't return a data set so I left out the cursor name but I've also tried the statement below in case this is a problem:

   SQLFlag = SQLExec(nHandle,'stprocnam','MyCursor')

In every case my connection succeeds but my attempt to fire the stored procedure returns a code of -1 for SQLFlag indicating failure. The books say that SQL Pass Through will allow execution of any valid db command. I assume this includes db stored procs.

Note that testing the statement below, I have no problems, so this is definitely related to Stored Proc execution.

SQLFlag = SQLExec(nHandle,'select * from Tblnam','MyCursor')

 

[jjgraf]

I'm not familar with Orcale but in SQL server to execute a stored procedure you do this

SQLFlag = SQLExec(nHandle,'Exec stprocnam')

'Exec' is a command word that tells the SQL server to execute the stored procedures.


you might check orcale help files to see if you need to send a command before the S.P.

hope this helps

 

[petervfp]

This helps a bit JJ. I looked into ORACLE a bit and the Stored procedure name needs to be preceeded with the EXEC command. ORACLE also accepts EXECUTE.

I've tested out running the stored proc from SQL*Plus this way and it works fine. But I still have a failure in FoxPro when running either of the command lines below:

SQLFlag = SQLExec(nHandle,'Exec stprocnam')
SQLFlag = SQLExec(nHandle,'Execute stprocnam')

Any further suggestions would be appreciated.

A buddy suggested that the new VFP 7.0 OLE DB driver may help. I've done a web search with no success. Can anyone point me to a site where I might download this new driver? I'm desparate now and will try anything. I have to make this work... and soon!


Thanks

 

[DEDMOD]

I noticed something looking at the VFP help files which might be of use. There's a SQLPREPARE() command which lets you send a SQL command and let the remote system produce a compiled version for running using SQLEXEC(). Have you tried to see if this would work for you?

Dave Dardinger

 

[jjgraf]

DEDMOD:
sqlprepare() precomplies the SQL statment so it would look like this.


cSqlStatement = 'Select * from Sometable'

sqlprepare(nConnectionHandle, cSqlStatement, 'Mycursor')

sqlexec(nConnectionHandle)


petervfp:

without the sqlprepare() the statment the Orcale server will have to complie the Statement.

It may be that the Orcale server is not complieing the SQL statement.

so go ahead try it


by the way the OLE db driver is to access Foxpro tables only. It will not help you in what you trying to do. You mite Check look at the version of you ODBC orcale driver to ensure it is the latest version.