I am currently building a php-oracle application for
the college I work for, and as part of that application
I have cause to to create some insert/update procedures.
My best solution for this is to have an input/output
parameter representing the id of the field that needs to
updated. This works fine.
If the passed id is 0 it performs an insert instead. This
is also fine. The procedure creates a new id one higher
than the current max, and uses that instead. Except for
I would now like the procedure to return this new id back
to the php.
After searching for an appropriate way to do this, I came
across oci_bind_by_name, but all the examples in the
documentation show this being used in an inwards
direction only. I found some outwards ones eventually,
but they either didn't work, or were for special types like
BLOBS and ROWIDS. I can't find a working example
using a number or other ordinary type.
Here is the simplest one of my procedures:
create or replace procedure welfare.update_equipment
(
ue_user in varchar2,
ue_equip_id in out number,
ue_stu_code in varchar2,
ue_lsf_no in number,
ue_lsf_year in varchar2,
ue_amount in float,
ue_method in number,
ue_notes in varchar2
)
as
update_time date;
begin
if ue_equip_id = 0 then
select
max(lsf_equipment_id)+1,
sysdate
into
ue_equip_id,
update_time
from
welfare.lsf_equipment;
else
select
sysdate
into
update_time
from
dual;
update
welfare.lsf_equipment
set
lsf_end_date = update_time
where
lsf_equipment_id = ue_equip_id
and lsf_end_date is null;
end if;
insert into
welfare.lsf_equipment
(
lsf_stu_code,
lsf_no,
lsf_year,
lsf_amount,
lsf_payment_method_code,
lsf_equipment_id,
lsf_equipment_notes,
lsf_user_created,
lsf_date_created
)
values
(
ue_stu_code,
ue_lsf_no,
ue_lsf_year,
ue_amount,
ue_method,
ue_equip_id,
ue_notes,
ue_user,
update_time
);
end;
Here is the php that calls it:
$sql="begin
welfare.update_equipment('$user',:equip_id,'$stu_code',$lsf_no,'$lsf_year',$amount,$method,'$notes');
end;";
//echo $sql; //debug line
//Open Oracle Connection
$db_conn=OCI_new_connect("db","username","password");
$psql = OCI_Parse($db_conn, $sql);
OCI_bind_by_name($psql,"equip_id",&$equip_id,32);
OCI_Execute($psql) or die("Couldn't execute statement.");
// free resources and close connection
OCI_close($db_conn);
And finally, this is the error I get (including a stack trace):
An error has occurred and the page has not loaded correctly.
Please try and refresh/reload
the page. If the error
persists, then please contact
MIS and we will look into
the problem.
errno: 2
errstr: oci_bind_by_name() [<a
href='function.oci-bind-by-name'>function.oci-bind-by-name</a>]:
Unknown or unsupported datatype given: 1
errfile: D:\Apache
Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php
errline: 77
Stack Trace:
#0 UpdateEquipmentForm->renderError(An error has occurred and the
page has not loaded correctly.
Please try and refresh/reload
the page. If the error
persists, then please contact
MIS and we will look into
the problem., 2,
oci_bind_by_name() [<a
href='function.oci-bind-by-name'>function.oci-bind-by-name</a>]:
Unknown or unsupported datatype given: 1, D:\Apache
Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php, 77)
called at [D:\Apache
Group\Apache2\htdocs\includes\pages\base_page.php:148]
#1 UpdateEquipmentForm->error_handler(2, oci_bind_by_name() [<a
href='function.oci-bind-by-name'>function.oci-bind-by-name</a>]:
Unknown or unsupported datatype given: 1, D:\Apache
Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php, 77,
Array ([user] => dhills,[equip_id] => 0,[stu_code] => 1358
,[lsf_no] => 9231,[lsf_year] => 04/05,[amount] => 666,[method] =>
1,[notes] => testing...,[sql] => begin
welfare.update_equipment('dhills',:equip_id,'1358
',9231,'04/05',666,1,'testing...'); end;,[db_conn] => Resource id
#24,[psql] => Resource id #25)) called at [D:\Apache
Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php:77]
#2 oci_bind_by_name(Resource id #25, equip_id, 0, 32) called at
[D:\Apache Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php:77]
#3 UpdateEquipmentForm->updateEquipDetails() called at [D:\Apache
Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php:39]
#4 UpdateEquipmentForm->__construct() called at [D:\Apache
Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php:206]
I guess I must be doing something horribly wrong. But what?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
