ksublondie has asked for the wisdom of the Perl Monks concerning the following question:

I'm stumped and need help.

I am trying to execute a MS sql command through a perl cgi interface that works from the management console, but dies through my script. 

my $inventoryInsert=...;
my $inventoryUpdate=...;
my $statement=...;
warn $inventoryUpdate.$statement."\n";
eval{
  $db->do($inventoryUpdate.$statement);
};
if ($@){
  warn "Can't update.  Trying insert.\n\n$inventoryInsert$statement";
  eval{
    $db->do($inventoryInsert.$statement);
  };
  if ($@){
    warn "Aborted because $@";
    $db->rollback;
    return 0;
  }
}
$db->commit;

[download]
For the transactions that are failing on the update statement and running the insert statement instead, I'm getting the following error relating to the $statement command: 
Cannot insert the value NULL into column 'columnname', table 'tablenam
+e'; column does not allow nulls. UPDATE fails.

[download]
BUT if I directly copy/paste the exact generated sql command from the management console query window, it succeeds w/no errors. Why is one generating an error and the other is fine?

UPDATE: If I make my 2nd do statement to be in the 1st eval: 

eval{
  $db->do($inventoryInsert.$statement);
#  $db->do($inventoryUpdate.$statement);
};
if ($@){
  warn "Can't update.  Trying insert.\n\n$inventoryInsert$statement";
  eval{
    $db->do($inventoryInsert.$statement);
  };
  if ($@){
    warn "Aborted because $@";
    $db->rollback;
    return 0;
  }
}
$db->commit;

[download]
It works. Something wrong with my eval statements???

Replies are listed 'Best First'.
Re: SQL through perl is giving error
by roboticus (Chancellor) on Aug 31, 2011 at 21:44 UTC

    ksublondie:

    Since the database is complaining that you're trying to violate a NULL constraint, I'd guess that perhaps you're interpolating a variable into the statement, and the variable is undefined (e.g., perhaps you misspelled the variable name inside the query string or when you initialized it?) Something like:

    $foobar = 17;
$SQL = 'insert into kazam(col1) values ($fubar)';
$DB->do($SQL);

    [download]

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
      If that were the case, then my copy/pasted perl warning output wouldn't work in the management studio, but it does.
[reply]
Re: SQL through perl is giving error
by ikegami (Patriarch) on Aug 31, 2011 at 21:26 UTC
    Please show the statement, as in print "$inventoryUpdate$statement\n" or print "$inventoryInsert$statement\n" (whichever is failing).
[reply]
[d/l]
[select]
Re: SQL through perl is giving error
by choroba (Cardinal) on Aug 31, 2011 at 22:02 UTC
    Why do you use eval and not check the return value of ->do?
[reply]
[d/l]
[select]
      Because I have 3 separate update and/or insert statements per do, wanted to make sure all succeed, and wasn't sure exactly how the return value would account for all possible errors. From my testing, it looks like do returns only the last statement result rows.

      Now if I add a rollback before the 2nd eval: 

      warn $inventoryUpdate.$statement."\n";
eval{
  $result=$db->do($inventoryUpdate.$statement);
  warn "result1=$result\n";
};
if($@){
  warn "Can't update.  Trying insert.\n\n$inventoryInsert$statement";
  $db->rollback;
  eval{
    $result=$db->do($inventoryInsert.$statement);
    warn "result1=$result\n";
  };
  if ($@){
    warn "Aborted because $@";
    $db->rollback;
    return 0;
  }
}
$db->commit;

      [download]
      It works! Apparently, the rollback resets $@, but why is the $@ message different in the 2nd eval without the rollback?
[reply]
[d/l]
[select]

        You might try the trace features of DBI to see more of what is going on.

[reply]

Back to Seekers of Perl Wisdom