3

I am using Codeigniter to insert data into an Oracle table which contains a date field.

Sample code looks like this:

$upload_details = array("user_name" = $name,
    "age" = $age,
    "date" = 'to_date($date, 'dd/mm/yyyy')'
);
$this->Some_model->insert($upload_details);

Now the problem is, to insert date fields into Oracle, i need to use the to_date function which is executed in the database. With the above code, the to_date function comes out inside single quotes and Oracle throws out an error.

I think the above will work if I use $this-db->query in the Codeigniter Model, but am not using that. I am using $this->db->insert('table_name', $upload_details).

So how can I tell codeigniter's $this->db->insert to send to_do function as it is and not in between single quotes.

Fabian N.
  • 3,570
  • 2
  • 20
  • 44
WebNovice
  • 2,156
  • 3
  • 20
  • 39

1 Answers1

4

Try:

$this->db->set('user_name', $name);
$this->db->set('age', $age);
$this->db->set('date',"to_date('$date','dd/mm/yyyy')",false);
$this->db->insert('mytable');

Edit: Quotes in $date

Alfonso Rubalcava
  • 2,217
  • 16
  • 25
  • thanks it seems to achieve what I want. However, there is still an error. The above code produces the following SQL: `insert into table ("date") Values (16/08/2011, 'dd/mm/yyyy')` I need the 16/08/2011 to be in between quotes – WebNovice Aug 16 '11 at 17:15
  • Ok, I have figured out how to put the quotes. But, codeigniter still throws out a Database Error and spits out the SQL query. I copy the query and run it in PL/SQL developer, and it runs fine. The row gets inserted. But from codeigniter, it does not. What could be the problem? Thanks! – WebNovice Aug 16 '11 at 17:24
  • A Database Error Occurred Error Number: SQL QUERIES (This same query I copy pasted into PL/SQL and it ran and inserted the rows. But in CI it gives out error. No error number is given) Filename: *CI_DIRECTORY*\system\database\DB_driver.php Line Number: 330 – WebNovice Aug 16 '11 at 17:42
  • Sorry! I was connecting to a different host. All fixed now. Your solution worked great. Thanks a lot. – WebNovice Aug 16 '11 at 17:58