PHP, Oracle and bind variables
- From: "Jack van Zanen" <jack@xxxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 29 Jul 2008 12:20:17 +1000
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
Other related posts:
