RDarius


Hi,
I have problem when using "call some_func()" with SQLEXEC.
Is this ODBC problem or FOXPRPO 9.0SP2

nHandle=SQLSTRINGCONNECT("DSN=pcv;DBQ=192.168.0.123;UID=pcv;PWD=pcv;BTD=F;NUM=NLS;FWC=T")
id_doc=61
doc_nr='TEST0000001'
SQLEXEC(nHandle,"call pcv.mp.test_call( id_doc, doc_nr)")
=1 commited
SQLEXEC(nHandle,"update pcv.b50_headeriai set b50_op_numeris= doc_nr where id_header= id_doc")
=1 commited
SQLEXEC(nHandle,"call pcv.mp.test_call( id_doc, doc_nr)")
=-1 failed
AERROR(aOraError)
aOraError(2)
t=Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis
SQLEXEC(nHandle,"call pcv.mp.test_call(61,'TEST0000001')")
=1 commited
SQLEXEC(nHandle,"update pcv.b50_headeriai set b50_op_numeris= doc_nr where id_header= id_doc")
=1 commited
SQLEXEC(nHandle,"call pcv.mp.test_call( id_doc, doc_nr)")
=-1 failed
nHandle=SQLSTRINGCONNECT("DSN=pcv;DBQ=192.168.0.123;UID=pcv;PWD=pcv;BTD=F;NUM=NLS;FWC=T")
SQLEXEC(nHandle,"call pcv.mp.test_call( id_doc, doc_nr)")
=1 commited
*********
This is the procedure code:
PROCEDURE test_call(in_doc_id NUMBER,in_doc_nr NVARCHAR2) IS
BEGIN
UPDATE b50_headeriai SET B50_op_numeris=in_doc_id where id_header=in_doc_id;
END test_call;

Thank you.



Re: Problem when using "call some_func()" with SQLEXEC-Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthes

dni


You may check if you have the last version of oldb provider:

http://www.microsoft.com/downloads/details.aspx familyid=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displaylang=en

and you may check oracle error:

"ORA-00907: missing right parenthesis
Cause: A left parenthesis has been entered without a closing right parenthesis, or extra information was contained in the parentheses. All parentheses must be entered in pairs.
Action: Correct the syntax and retry the statement."







Re: Problem when using "call some_func()" with SQLEXEC-Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthes

RDarius

Hi,
Thank You for replay.

I think I dont need the vfpoledb.dll. I just installed this update and it was not useful for me.
I'm trying to call the procedure in Oracle database mp package .
Problem only with procedures and only in this sequence :

1) call procedure any times yuo nead - everything is ok....
2) sqlexec any other sql command - everything is ok....
3) call the same procedure with parameters ("call pcv.mp.test_call( id_doc, doc_nr)") - error...
!!!
4) call prodedure with "call pcv.mp.test_call(61,'TEST0000001')") --- ok


sorry for my english






Re: Problem when using "call some_func()" with SQLEXEC-Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthes

dni

I can not see any parameters in your procedure....






Re: Problem when using "call some_func()" with SQLEXEC-Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthes

RDarius

Smile
This is FoxPro SQLEXEC command and Oracle database procedure and very easy sample:
Parameters in foxpro code :

pcv - schema (owner)
mp - package
test_call - procedure

SQLEXEC(nHandle,"call pcv.mp.test_call( id_doc, doc_nr)")

Procedure body with parameters:

PROCEDURE test_call(in_doc_id NUMBER,in_doc_nr NVARCHAR2) IS
BEGIN
UPDATE b50_headeriai SET B50_op_numeris=in_doc_id where id_header=in_doc_id;
END test_call;

In my opinion this is 99% bug. Odbc ,FoxPro ,Oracle DB - I dont know.





Re: Problem when using "call some_func()" with SQLEXEC-Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthes

dni

May be it is necesary to use (IN,OUT,IN OUT) and "IS" when define parameters and use declaration section in order to "help" sqlexec with a full syntax. I think SP2 for vfp 9 is still a beta version....

You may also check:

http://fox.wikis.com/wc.dll Wiki~DirectODBCcalls~VFP






Re: Problem when using "call some_func()" with SQLEXEC-Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthes

RDarius

This problem is in FoxPro 9,FoxPro 9SP1 FoxPro 9SP2 , so this is not SP2 problem.
And yet, this error is on another computer with the same sample without SP1 and SP2...

PL/SQL
User's Guide and Reference
10g Release 1 (10.1)
Part No. B10807-01
Page 201. : Its parameter mode (IN, OUT, or IN OUT). If you omit the mode, the default is IN.

Where is the syntax mistake in my sample
Where is the logical mistake in my sample

Smile







Re: Problem when using "call some_func()" with SQLEXEC-Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthes

RDarius

CREATE OR REPLACE PACKAGE "HR"."MP" AS

PROCEDURE test_call(in_country IN CHAR,in_name IN VARCHAR2) ;

END MP;
/
CREATE OR REPLACE PACKAGE BODY "HR"."MP" AS

PROCEDURE test_call(in_country IN CHAR,in_name IN VARCHAR2) AS
BEGIN
update countries set country_name=in_name where country_id=in_country;
END test_call;

END MP;
/

nHandle=SQLSTRINGCONNECT("DSN=hr;DBQ=192.168.1.123;UID=hr;PWD=hr;BTD=F;NUM=NLS;FWC=T")

SQLEXEC(nHandle,"{call hr.mp.test_call( id_country, country_name)}") = 1 ok
SQLEXEC(nHandle,"update countries set country_name= country_name where country_id= id_country") =1 ok
SQLEXEC(nHandle,"{call hr.mp.test_call( id_country, country_name)}") = -1 failed
AERROR(SqlError)
SqlError(2)
=Connectivity error: [Oracle][ODBC][Ora]ORA-06550: line 1, column 22:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
( ) - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a

SQLEXEC(nHandle,"{call hr.mp.test_call('US','United States of America')}") =1 ok

Help Help Help

Thank You









Re: Problem when using "call some_func()" with SQLEXEC-Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthes

surinder singh

Before calling SQLEXEC(nHandle,"{call hr.mp.test_call( id_country, country_name)}") = -1 failed statement

see the values of id_Country and Country_name variables.

Surinder Singh






Re: Problem when using "call some_func()" with SQLEXEC-Connectivity error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthes

RDarius

Values :

id_country='US'
country_name='United States of America'