How to execute Stored Procedure in xBasic function on button click

[Narayan]

How to execute Stored Procedure in xBasic function on button click. I need to execute Oracle Stored Procedure in xBasic Function on Button click.

Below code i tried to run in Interactive window.
1. Able to connection to connection
2. Could not able to execute the stored procedure, getting false after executing the procedure. Please help to resolve this issue.

dim js as c
dim cn as sql::connection
dim args as sql::Arguments
dim flag as L
dim sqlCommand as c
dim rs as sql::ResultSet

debug(1)
flag = cn.open("::Name::shared")
if flag == .f. then
js = "alert('Couldn't connect to database.');"
validateLogin = js
exit function
end if

'sqlCommand = "exec TEST_MV_REFRESH_ST_PROC"
flag = cn.Execute("exec TEST_MV_REFRESH_ST_PROC")

if flag == .f. then
js = "alert('Couldn't execute the query.');"
cn.Close()
validateLogin = js
exit function
else
js = "alert('Executed refresh');"
exit function
end if
cn.Close()


Here is my Stored Procedure in Oracle
CREATE OR REPLACE PROCEDURE TEST_MV_REFRESH_ST_PROC
AS
BEGIN
DBMS_MVIEW.REFRESH('TEST_MV');
END TEST_MV_REFRESH_ST_PROC;
/

[Davidk]

Re: How to execute Stored Procedure in xBasic function on button click

I don't run Oracle, but after a forum search, this might help.

I do know that calling an SP is different between SQLServer and MySQL.

"exec" is SQLServer. "call" is Mysql.

According to this post... http://www.alphasoftware.com/alphafo...re-from-XBasic

Oracle may be... "call"

sql = <<%sql%
call ORACLE_STORE_PROC(:userid, :checkstat, :coy, :ReturnVal, :ErrorMsg)
%sql%

args.set("userid","FaisalY")
args.set("checkstat","check")
args.set("coy","ibs")
args.set("ReturnVal",-1,sql::ArgumentUsage::InputOutputArgument)
args.set("ErrorMsg","unknown",sql::ArgumentUsage::InputOutputArgument)
c.open("::Name::DataTransactional")
c.Execute(sql, args)

[Narayan]

Re: How to execute Stored Procedure in xBasic function on button click

Thank you David, your solution working perfectly.