Home » Open Source » Programming Interfaces » PHP on Oracle
PHP on Oracle [message #224427] |
Wed, 14 March 2007 04:31 |
damian_one
Messages: 21 Registered: March 2007
|
Junior Member |
|
|
hello,
I am a MSc student and writing my MSc. Thesis.
I am developing an application using:
1. Oracle 10g R2
2. Windows XP Service Pack 2.
3. PHP 5.1.6
4. Apache Web Server
I am trying to execute a stored procedure (from a package) from a PHP script.
Necessary tables:
1.
create table porchetwork
(
porchetworkid number,
dbuser_id number,
wrk_role varchar2(20),
work_start date,
work_end date,
postingname varchar2(20),
contactid number,
work_mgr varchar2(20),
rate_id number,
constraint pk_porchetwork_01
primary key (porchetworkid),
constraint fk_porchetwork_01
foreign key (dbuser_id) references porchetusers (dbuser_id),
constraint fk_porchetwork_02
foreign key (contactid) references porchetcontact (contactid),
constraint fk_porchet_work_03
foreign key (rate_id) references rate_type (rate_id)
)
/
2.
create table porchetcontact
(
contactid number,
contact_type varchar2(10) not null,
address1 varchar2(50),
address2 varchar2(40),
city_town varchar2(30),
state_county varchar2(25),
postcode varchar2(12),
country varchar2(25),
telephone varchar2(30),
fax varchar2(30),
email varchar2(80),
website varchar2(80),
dbuser_id number,
constraint pk_porchetcontact_01
primary key (contactid),
constraint fk_porchetcontact_01
foreign key (dbuser_id) references porchetusers (dbuser_id),
constraint ck_porchetcontact_01
check (contact_type in ('CONSULTANT','POSTING','MANAGER'))
)
/
STORED PROCEDURE:
/*10th procedure*/
procedure prcRegisterWork(vuser in varchar2,
vcontract in varchar2,
vaddr1 in varchar2,
vaddr2 in varchar2,
vcity in varchar2,
vstate in varchar2,
vpcode in varchar2,
vcountry in varchar2,
vtel in varchar2,
vfax in varchar2,
vemail in varchar2,
vwebsite in varchar2,
vstart in varchar2,
vend in varchar2,
vwrkrole in varchar2,
vworkmgr in varchar2,
v_rate in number,
vp_type in varchar2,
vmessage out varchar2)
as
vstartdate porchet.porchetwork.work_start%type;
venddate porchet.porchetwork.work_end%type;
vuser_id number;
seq_val number;
vrate rate_type.rate_id%type;
begin
vstartdate:= to_date(vstart);
venddate:= to_date(vend);
select a.dbuser_id into vuser_id
from porchetusers a, all_users b
where a.user_id = b.user_id
and b.username = upper(vuser);
select porchetcontact_seq.nextval into seq_val
from dual;
select rate_id into vrate
from rate_type
where rate_value = v_rate
and rate_type = upper(vp_type);
if ((vuser is null)
or (vcontract is null)
or (vaddr1 is null)
or (vaddr2 is null)
or (vcity is null)
or (vstate is null)
or (vpcode is null)
or (vcountry is null)
or (vtel is null)
or (vfax is null)
or (vemail is null)
or (vwebsite is null)
or (vstart is null)
or (vend is null)
or (vwrkrole is null)
or (vworkmgr is null)
or (v_rate is null)
or (vp_type is null)) then
vmessage:='All fields required!';
else
insert into porchetcontact(contactid,contact_type,address1,address2,city_town,state_county,postcode,country,telephone,fax,email,website,dbuser_id )
values(seq_val,'POSTING',upper(vaddr1),upper(vaddr2),upper(vcity),upper(vstate),upper(vpcode),upper(vcountry),upper(vtel),upper(vfax) ,lower(vemail),lower(vwebsite),vuser_id);
insert into porchetwork(porchetworkid,dbuser_id,wrk_role,work_start,work_end,postingname,contactid,work_mgr,rate_id) values(porchetwork_seq.nextval,vuser_id,upper(vwrkrole),vstartdate,venddate,upper(vcontract),seq_val,upper(vworkmgr),vrate);
vmessage:='Details Registered';
end if;
commit;
return;
end prcRegisterWork;
PHP SCRIPT
<?php
session_start();
include('disp_func.php');
$query='BEGIN porchet.porchet_pkg.prcRegisterWork(:vuser,:vcontract,:vaddr1,:vaddr2,:vcity,:vstate,:vpcode,:vcountry,:vtel,:vfax,:vemail,:vweb,:vs_ dt,:ve_dt,:w_role,:w_mgr,:vrate,:vtype,:vmess); END;';
$vcontract=$_POST['postingname'];
$vaddr1=$_POST['addr1'];
$vaddr2=$_POST['addr2'];
$vcty=$_POST['city_town'];
$vst=$_POST['state_county'];
$vpcd=$_POST['postcode'];
$vcnty=$_POST['country'];
$vtel=$_POST['telephone'];
$vfax=$_POST['fax'];
$veml=$_POST['email'];
$vweb=$_POST['website'];
$vstday=$_POST['start_date_day'];
$vstmnth=$_POST['start_date_month'];
$vstyr=$_POST['start_date_year'];
$vstartdate=$vstday.'-'.$vstmnth.'-'.$vstyr;
$vendd=$_POST['end_date_day'];
$vendmnt=$_POST['end_date_month'];
$vendyr=$_POST['end_date_year'];
$vendate=$vendd.'-'.$vendmnt.'-'.$vendyr;
$vwrkr1=$_POST['work_role'];
$vwrkmgr=$_POST['mgr'];
$v_rate=$_POST['pay_rate'];
$vtyp=$_POST['pay_type'];
$vusername=$_SESSION['session_username'];
$vpassword=$_SESSION['session_password'];
$get_all = array(':vuser' => $vusername, ':vcontract' => $vcontract, ':vaddr1' => $vaddr1, ':vaddr2' => $vaddr2, ':vcity' => $vcty, ':vstate' => $vst, ':vpcode' => $vpcd, ':vcountry' => $vcnty, ':vtel' => $vtel, ':vfax' => $vfax, ':vemail' => $veml, ':vweb' => $vweb, ':vs_dt' => $vstartdate, ':ve_dt' => $vendate, ':w_role' => $vwrkrl, ':w_mgr' => $vwrkmgr, ':vrate' => $v_rate, ':vtype' => $vtyp, ':vmess' => $vmess);
$conn = oci_connect($vusername,$vpassword);
$stmt=oci_parse($conn, $query);
foreach ($get_all as $key => $val)
{
OCIBindByName($stmt, $key, $get_all[$key], 32);
}
oci_execute($stmt);
do_html_header($vmess, 1, '');
do_html_footer();
?>
ERROR MESSAGE:
Warning: oci_execute() [function.oci-execute]: OCI_NO_DATA in C:\Program Files\Apache Group\Apache2\test\porchet_rel\reg_work2.php on line 44
Also, I had rewritten the PHP script to call oci_bind_by_name for each stored procedure and using the database table column length as the php value but I was getting:
ERROR: ORA-01460.
Please help as I am stuck and have searched everywhere.
Will REALLY appreciate.
THANKS in advance.
Regards,
Ade.
|
|
|
|
|
Re: PHP on Oracle [message #225170 is a reply to message #224427] |
Sun, 18 March 2007 14:50 |
damian_one
Messages: 21 Registered: March 2007
|
Junior Member |
|
|
Hi,
Thanks for the response. I eventually got it to work. It had nothing to do with the PHP Database Connection. I had PHP error reporting turned to E_STRICT. I used the function 'error_reporting(E_ERROR | E_WARNING | E_PARSE);' in my script and it works fine.
Thanks.
|
|
|
Goto Forum:
Current Time: Tue Dec 03 04:49:32 CST 2024
|