RE: PHP, Oracle and bind variables

From: Goulet, Dick <richard.goulet_at_capgemini.com>
Date: Wed, 30 Jul 2008 09:25:12 -0400
Message-ID: <746B47FAF6783042B256C0E7CC0795CD02D775CA@caonmastxm02.na.capgemini.com>


Jack,  

            I see two possible problems.  

  1. Since your saying that the date is going in as a varchar I have to assume that the table's definition has it as a varchar vs a date. Change the timestamp column to date & that will correct part of the problem.
  2. Wrap the datum bind variable in the to_date function. PHP sees it as a character string & binds it as such.

Dick Goulet / Capgemini
North America P&C / East Business Unit
Senior Oracle DBA / Hosting
Office: 508.573.1978 / Mobile: 508.742.5795 / www.capgemini.com Fax: 508.229.2019 / Email: richard.goulet_at_capgemini.com 45 Bartlett St. / Marlborough, MA 01752

Together: the Collaborative Business Experience



From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jack van Zanen Sent: Monday, July 28, 2008 10:20 PM
To: oracle-l
Subject: PHP, Oracle and bind variables  

Hi List    

I am trying to load data from a file into an oracle table.

the file is in matrix format (rows is timestamp and columns are sids where the value is a status) and needs to be put into the table as rows as the number of columns is variable in time

so the layout of the table is  

dbname varchar2(20)

timestamp varchar2(20) ---> this I would like to change to date

status varchar2(6)  

I have created a little code that loops through the files and the data in the files and inserts the data into a table. the first row contains the sids and should not be loaded, but just used in every insert.  

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 

function insert_OLA_data_2($file_in)
{
global $conn, $FileDir; //define variables as global
$lines = file($FileDir.$file_in,FILE_IGNORE_NEW_LINES |
FILE_SKIP_EMPTY_LINES);
$line_n=0;
$stmt = oci_parse ($conn,"insert into OLA_DATA2(DBNAME,TIMESTAMP,STATUS)
values (:dbs,:datum,:stat)");
foreach ($lines as $line_num => $line)
{
if ($line_n < 1 )
{
$a_dbs=explode(',',rtrim($line));

}
else
{
$a_line=explode (',',rtrim($line));
$cntr=1;

for ($item_num=0;$item_num < count($a_line)-1; $item_num++) {

oci_bind_by_name($stmt,"dbs",$a_dbs[$cntr]);
oci_bind_by_name($stmt,"datum",$a_line[0]);
oci_bind_by_name($stmt,"stat",$a_line[$cntr]);
oci_execute($stmt,OCI_DEFAULT);

$e = oci_error($stmt);

if(count($e) > 1)
{
log_message('Error while loading file '.$file_in.'('.$e['message'].')');
}$cntr=$cntr+1;
}
}

$line_n=$line_n + 1;

}
$commited = oci_commit($conn);

move_files($file_in);
log_message('File '.$file_in.' processed'); }//EoF insert_OLA_data_2
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I am using bind variables as there are many executions of the same query with different values.

Above code works fine, but the date is going into the database as a varchar and I would really like it to go in as a date Anyone done this and can point me in the right direction?  

format for the string containing the date is 2008:07:29:12:15:00  

-- 
J.A. van Zanen 


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 30 2008 - 08:25:12 CDT

Original text of this message