in reply to Re: OT: Oracle SQL issues
in thread OT: Oracle SQL issues

...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)?

Replies are listed 'Best First'.
Re: Re: Re: OT: Oracle SQL issues
by seesik (Initiate) on Sep 13, 2001 at 18:19 UTC
    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]
        i'm not sure if you're using that hash slice in the execute or not, but if you are, that'd likely be the problem area. execute wants values that correspond to the order of the bind params, and keys %{$hash->{$table}} is going to return a list of the cols (and thus the values) in some arbitrary order. that might actually result in 
        $sth->execute(undef, '','50', 0, undef, '','1',1','50 free page points
+ at signup!',....);

        [download]
        that obviously supplies undef/NULL for columns that are NOT NULL constrained.

        anyway, if that's just test code, and you're actually doing some $sth->execute(@{$hash->{$table}}{@ordered_cols}), then i'd say that something is still b0rked, and we need to keep looking..

[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