0

I try to insert null values into date fields in Oracle from perl, but instead of null values it ends up inserting sysdate. When I do the same from sqlplus, it inserts the null values.

My code (just the important part)

use DBI;
use DBD::Oracle qw(ORA_RSET);

...

eval {
    $sth = $dbh->prepare(q{
         INSERT INTO MY_TABLE
             (VAL1, VAL2, VAL3, ENTRY_DATE, PROCESSED_DATE)
         VALUES (?,?,?,?,?)});
    my $rv = $sth->execute($VAL1, $VAL2, $VAL3, undef, undef);
    $insert_counter++;
};
if ( $@ ) {
    $FILE_PROCESS_STATUS = FILE_INVALID;
    logg("e", "$@");
}
if ( $FILE_PROCESS_STATUS = FILE_VALID ) {
    $dbh->commit;
}
Sandor Farkas
  • 153
  • 2
  • 11
  • I would suggest you check how PHP handles nulls values. What is done here is that PHP is adding bind variables or placeholders to the query. The use of `undef`- are you sure PHP passes `null` value for `undef` to a date field? – Rachcha Mar 24 '14 at 11:25
  • Is it same when `""` instead of `undef`? – mpapec Mar 24 '14 at 11:36
  • Rachcha: As it is mentioned in the description and tags, it is perl, not php. :) mpapec: based on the documentation of DBD::Oracle I have to use undef as it is the null. http://www.orafaq.com/wiki/Perl#DBD::Oracle_and_NULL_values – Sandor Farkas Mar 24 '14 at 12:05
  • Can you please provde the example of SQL*Plus execution (full stack of insert statement and the result of selection after this). Also it's wirth th provide table definition DDL. – Dmitry Nikiforov Mar 24 '14 at 12:26

1 Answers1

1

You can use DBI->trace to log actions and actually see the statement that is being prepared. See the answers to this question. This should show you if an incorrect SQL query is being created.

However, I would be willing to bet that the problem is something other than what you think it is. Use the debugger to verify that this piece of your code is really causing the problem, or create a simple example script to test whether this is actually the problem.

Community
  • 1
  • 1
  • This is very useful dan, I will follow your suggestions and will be back with the results. – Sandor Farkas Mar 24 '14 at 15:04
  • It's a shame, but I modified the code at the wrong place. I made a simple script, which was working, than I realized, that I am modifying the wrong branch. Sorry and thank for the help to everyone especially to you dan :) – Sandor Farkas Mar 24 '14 at 15:56