DBI - Insert NULL value into DB

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

Got some trouble handling undef/null values from database. (The following code is some kind of pseudocode.) I'm fetching data from DB-1 table test. Table test looks like:

ID | SOMEVALUE
--------------
 1 | NULL
 2 | text
 1 | NULL
[download]

Now I copy this lines by doing an insert on a second database with the same table called DB-2 table test2:

while ( ( $id, $value ) = $hStatement->fetrow_array )
{
  $hStatement2->prepare( INSERT INTO test2 VALUES ( ?, ? ) );
  $hStatement2->execute( $id, $value );
}
[download]

The result is fine test2 looks like test table. BUT ... i get uninitialized errors when those undefined values (NULL) are used. I see two ways to solve the problem: 1) Use constants, so i handle all those possible undefs and I concat my string as insert-statement and doing

  $hStatement2->prepare( $sSQL_Statement );
  $hStatement2->execute(  );
[download]

2) Run this part of the code in a block using "no warnings". I don't like both ways because method 1 needs alot of if-code and statements are proberly less optimized for the database. But suppressing warnings is even worse imo. So I'd like to ask if any of you guys know, how to solve this problem in a good programmers way.

Comment on DBI - Insert NULL value into DB Select or Download Code

Replies are listed 'Best First'.
Re: DBI - Insert NULL value into DB by Corion (Patriarch) on Aug 24, 2016 at 10:26 UTC
Where/how do you get "uninitialized" values? That shouldn't happen if you use placeholders for all values. Please show us the exact error messages and ideally also the relevant code, as what you've shown above doesn't look like valid Perl to me.	[reply]
Re^2: DBI - Insert NULL value into DB by Yaerox (Scribe) on Aug 24, 2016 at 11:36 UTC
I gonna have to clear things up first for me. Created a test-script and cannot reproduce the warnings.	[reply]
Re: DBI - Insert NULL value into DB by hippo (Archbishop) on Aug 24, 2016 at 11:00 UTC
The following code is some kind of pseudocode. That doesn't really allow anyone else to be of much help with the diagnosis, unfortunately. `$sth->execute()` is quite happy to accept undefined variables as arguments and does not raise warnings about them. It sounds like the warnings you are seeing probably stem from elsewhere in your code. If you can provide an SSCCE someone might be able to spot the problem for you. FWIW, I wouldn't have that `$hStatement2->prepare` call inside the loop - you are losing all of the efficiency by re-preparing the exact same query over and over again.	[reply] [d/l] [select]
Re: DBI - Insert NULL value into DB by GotToBTru (Prior) on Aug 24, 2016 at 13:09 UTC
Suggested by DBI: `while (($id,$value) = $hStatement->fetchrow_arrayref) { foreach (($id,$value)) { $_ = '' unless defined }; ...` [download] Update: in Informix at least, this does not work. An empty string does not equate to NULL. Also, I don't get any warnings when updating a table using a undefined variable. But God demonstrates His own love toward us, in that while we were yet sinners, Christ died for us. Romans 5:8 (NASB)	[reply] [d/l]
Re^2: DBI - Insert NULL value into DB by hippo (Archbishop) on Aug 24, 2016 at 14:07 UTC
Won't that mean that the second table has no NULLs in it and is therefore not a real copy of the first table?	[reply]
Re^3: DBI - Insert NULL value into DB by GotToBTru (Prior) on Aug 24, 2016 at 14:42 UTC
Good point - this would be something to test, if the DB interprets an empty string as a NULL value. But God demonstrates His own love toward us, in that while we were yet sinners, Christ died for us. Romans 5:8 (NASB)	[reply]