Home » Open Source » Programming Interfaces » error in php executing sp
error in php executing sp [message #173725] Wed, 24 May 2006 03:53 Go to next message
mmondragon
Messages: 7
Registered: May 2006
Junior Member
hi i'm getting error in php using oracle xe... did i forget something? thanks!

ERROR:
oci8 error: [6550: ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol "" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe The symbol "" was ignored. ORA-06550: line 2, column 56: PLS-00103: Encountered the symbol "" when expecting one of t] in EXECUTE("Array")

CODE:
private function verify_user()
{
try
{
$stmt = $this->connStr->PrepareSP(
'begin
SP_USERBUILDING(:myuserid,:mypassword,:mybuilding);
end;');
$this->connStr->InParameter($stmt,$this->user,'myuserid');
$this->connStr->InParameter($stmt,$this->pwd,'mypassword');
$this->connStr->InParameter($stmt,$this->bldg,'mybuilding');
//$this->connStr->OutParameter($stmt,$ret,'RETVAL');
$this->connStr->Execute($stmt);
}
catch (Exception $e)
{
$this->errorMsgs[0] = $e->getMessage();
$this->errorMsgs[1] = $_SERVER['REMOTE_ADDR'];
$this->errorMsgs[2] = $_SERVER['SCRIPT_NAME'];

$this->handle_error($this->errorMsgs);
exit();
}
}
Re: error in php executing sp [message #173736 is a reply to message #173725] Wed, 24 May 2006 04:56 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, you forgot to post the stored procedure code.
Re: error in php executing sp [message #173855 is a reply to message #173736] Wed, 24 May 2006 19:18 Go to previous messageGo to next message
mmondragon
Messages: 7
Registered: May 2006
Junior Member
sorry here's my stored procedure code:

CREATE OR REPLACE
PROCEDURE sp_UserBuilding
(
usrName IN VARCHAR2,
usrPassword IN VARCHAR2,
usrBldg IN VARCHAR2
)
IS
varUserName VARCHAR2 (35);
varBuilding VARCHAR2 (40);
BEGIN
SELECT
T1.UserName,
T3.BldgDesc
INTO
varUserName,
varBuilding
FROM
Users T1,
BuildingUsers T2,
Building T3
WHERE
(((T1.UserName = T2.UserName AND T2.BldgID = T3.BldgID) AND ((T1.UserName = usrName AND T1.Password = usrPassword) AND T2.BldgID = usrBldg)));
END;


--
Thanks!
Re: error in php executing sp [message #173878 is a reply to message #173855] Thu, 25 May 2006 00:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Stored procedure code seems to be valid; at least, I don't see anything strange there.

I don't speak PHP, but viewing your code and error message, could it be that the PHP procedure code should look like this:

private function verify_user()
{
try
{
-> are the next 3 lines setting parameter values? If so,
shouldn't they be written BEFORE preparing a PL/SQL block?
It seems that you transfered nothing to the procedure ...


$this->connStr->InParameter($stmt,$this->user,'myuserid');
$this->connStr->InParameter($stmt,$this->pwd,'mypassword');
$this->connStr->InParameter($stmt,$this->bldg,'mybuilding');
//$this->connStr->OutParameter($stmt,$ret,'RETVAL');

-> this line prepares PL/SQL block which should call the stored procedure
$stmt = $this->connStr->PrepareSP(
'begin
SP_USERBUILDING(:myuserid,:mypassword,:mybuilding);
end;');

-> execute the PL/SQL block
$this->connStr->Execute($stmt);

}
catch (Exception $e)
{
$this->errorMsgs[0] = $e->getMessage();
$this->errorMsgs[1] = $_SERVER['REMOTE_ADDR'];
$this->errorMsgs[2] = $_SERVER['SCRIPT_NAME'];

$this->handle_error($this->errorMsgs);
exit();
}
}
Re: error in php executing sp [message #173911 is a reply to message #173725] Thu, 25 May 2006 04:10 Go to previous message
mmondragon
Messages: 7
Registered: May 2006
Junior Member
sir based on the doc of ADODB.sorceforge.net i am correct with the syntax. oh my i tried reverting the code, but it's the same thing... thank you sir
Previous Topic: Connectivity from php to oracle database running on windows NT server
Next Topic: OCI/PHP Make Error
Goto Forum:
  


Current Time: Thu Mar 28 11:58:01 CDT 2024