oci_bind_by_name, 'Unknown or unsupported datatype given'

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux