in reply to OT: Oracle SQL issues

let's see what we're looking at.. 
ID   =  1
Date_Created  = SYSDATE
Date_Modified = SYSDATE
Title = 'test promotion'
Promotion_Phrase = 'phrase here'
Date_Start = NULL
Date_End = NULL
Status = 0
Trigger_Type = 0
Trigger_Value = ''
Trigger_Quantity = ''
Reward_Type = 0
Reward_Value = ''
Reward_Quantity = ''
Uses_PerCustomer = 1
Uses_Maximum = 0

[download]
hmm, all columns are accounted for, so it's not like you're letting oracle implicitly fill in default nulls for some columns excluded from your insert. what else could it be...

...and undef's for the two null values I do want

dbd::oracle is going to treat NULL's as undef coming OUT of queries, but not inversely. in other words, you need to pass the string 'NULL' in your bind params or execute instead of passing perl's undef.

hth; cheers

Replies are listed 'Best First'.
Re: Re: OT: Oracle SQL issues
by runrig (Abbot) on Sep 13, 2001 at 19:52 UTC
    dbd::oracle is going to treat NULL's as undef coming OUT of queries, but not inversely. in other words, you need to pass the string 'NULL' in your bind params or execute instead of passing perl's undef.

    This is wrong. From the DBI docs on placeholders:

    Undefined values, or "undef", can be used to indicate null values.

    [download]
    But it goes on to say that for SELECT statements, this won't work in 'WHERE field = ?' clauses because nothing is equal to NULL, and suggests an alternative in the case where you're looking for a possibly NULL field.
[reply]
[d/l]
      agreed; pre-coffee mental burp. see here.
[reply]
Re: Re: OT: Oracle SQL issues
by AidanLee (Chaplain) on Sep 13, 2001 at 18:10 UTC
    ...in other words, you need to pass the string 'NULL' in your bind params or execute instead of passing perl's undef.

    Ok, that's good to know, but I'm wondering how DBD::Oracle is supposed to be able to make the distinction between the value NULL and the string literal 'NULL' (since i am using bind values, not building it into the actual query string)?

[reply]
      so i just got out some old code, and it looks like i've sent undef's in bind_params before. hmm...
      can you see what sort of constraints are on that table ?select CONSTRAINT_NAME from all_constraints where table_name = 'PROMOTIONS'
      i'm going to dig for a bit, post any relevant constraints here if you would..
[reply]
[d/l]
[select]
        my $qs = 'insert into Promotions (' . join(',', keys %{$hash->{promoti
+on}} ) .
    ',Date_Created,Date_Modified) values (' .
    join(',', ('?') x scalar( keys %{$hash->{promotion}} ) ) .
    ',SYSDATE,SYSDATE)';

$sth = $dbh->prepare( $qs );
$sth->execute( @{$hash->{promotion}}{ keys %{$hash->{promotion}} } );

        [download]

        You'll note that my two SYSDATE entries are not part of the bind parameters because I'd expect bind to escape them as strings. When I do a dump of $hash->{promotion} (not quite the same data as I was using in my earlier example):

        $VAR1 = {
          'Title' => 'Signup PagePoints',
          'Trigger_Value' => '',
          'Uses_Maximum' => 0,
          'Reward_Type' => '5',
          'Reward_Quantity' => '50',
          'Trigger_Quantity' => '',
          'Uses_PerCustomer' => '1',
          'Promotion_Phrase' => '50 free page points at signup!',
          'Status' => 0,
          'Date_Start' => undef,
          'Trigger_Type' => '4',
          'ID' => '1',
          'Date_End' => undef,
          'Reward_Value' => ''
        };

        [download]
[reply]
[d/l]
[select]
        CONSTRAINT_NAME
------------------------------
SYS_C009553
SYS_C009539
SYS_C009540
SYS_C009541
SYS_C009542
SYS_C009543
SYS_C009544
SYS_C009545
SYS_C009546
SYS_C009547
SYS_C009548
SYS_C009549
SYS_C009550
SYS_C009551
SYS_C009552

        [download]

        I'm assuming that these are my primary key and null constraints, though i don't know how to get more information on them. seesik, it might be eaiser to work this one out on chatterbox, if you use it. :)

[reply]
[d/l]

In Section Seekers of Perl Wisdom