insert in SQL Database

[AtifurRahman]

Hi,
I'm trying to insert NULL for a column into SQL DB through a formating a string, but not through insert ...values (?) and i don't see a NULL in the DB., the column value appears blank, but i expect a NULL.

When i explictly assign NULL to that variable, it treats the values as a string, not a NULL and isnull is not returning the ROW.


$_data will have an address of the row fetched.

Program:

  while ($_data = $_src_dbh->fetch_row())
                {
                                $i++;
                                #write to datafile
 
if (not defined $_data->{'level_3'} )
           $_data->{'level_3'} = undef;
 
my $string = "insert into ".$_db_name."..hierarchy_buffer (client_scheme_code,
                                                                 client_level_1_code,
                                                                 client_level_2_code,
                                                                 client_level_3_code,
                                                                 client_security_id)
                            values (
                                  '". $_data->{'scheme'} ."',
                                  '". $_data->{'level_1'} ."',
                                  '". $_data->{'level_2'} ."',
                                  '". $_data->{'level_3'} ."',
                                  '". $_data->{'client_security_id'}."') \n";
 
 
 

If we insert the values thru values ( ? ), then undef is working properly and has a NULL in the DB, since i'm formating the string, the undef value appears as a blank in the DB, but not NULL. So when is use isnull() it is not returning the row.

Thanks
Atif

[eWish]

Which database are you using? Here is a sample of how you can handle the undef / null values with MySQL using the DBI.

my $var1 = 'Some Name';
my $var2 = '';
 
my $check_var = $var2 eq '' ? undef : $var2;
 
my $sql = $dbh->prepare(qq{INSERT INTO test_table (col1, col2) VALUES(?,?)});
   $sql->execute($var1, $check_var);
   $sql->finish();

When you check the database it should have the 'null' in lieu of the word "undef" or the field being empty.

[AtifurRahman]

Which database are you using? Here is a sample of how you can handle the undef / null values with MySQL using the DBI.

my $var1 = 'Some Name';
my $var2 = '';
 
my $check_var = $var2 eq '' ? undef : $var2;
 
my $sql = $dbh->prepare(qq{INSERT INTO test_table (col1, col2) VALUES(?,?)});
   $sql->execute($var1, $check_var);
   $sql->finish();

When you check the database it should have the 'null' in lieu of the word "undef" or the field being empty.

Hi,
I'm using MS SQL Db. My issue is i'm not using insert...values( ? ?) syntax, if I use values along with ?, then undef is working properly. But I'm formatting a string, as shown below

my $string = "insert into ".$_db_name."..pre_bcp_hierarchy_buffer (client_scheme_code,
                                                                 client_level_1_code,
                                                                 client_level_2_code,
                                                                 client_level_3_code,
                                                                 client_security_id)
                            values (
                                  '". $_data->{'scheme'} ."',
                                  '". $_data->{'level_1'} ."',
                                  '". $_data->{'level_2'} ."',
                                  '". $_data->{'level_3'} ."',
                                  '". $_data->{'client_security_id'}."') \n";
 
print " String Val $string \n";
 
                                $allstring = $allstring.$string;
                                # Check if $i is n * 1000
                                if ( !($i % 1000 ))
                                {
                                                #write to pre_bcp_option_buffer
                                                $retval = $_trg_dbh->exec( $allstring );
 
 

When the count reaches 1000, i.e. when i've formatted an insert string of 1000 rows i'm inseritng at one shot, before formatting the insert string i'm checking whether i've any null values in any of the columns. if it is there then i've to assign NULL that is undef (per PERL) to that column in the insert statement/string i'm formatting, coz its a string the undef is assigning ' ' into the db, but not a [NULL]. I expect a [NULL] in the DB so that firther my query can use isnull() to check for NULL values.
Since my program is inserting the ' ' the isnull() couldn't identify the NULL rows.

Thanks
Atif

[AtifurRahman]

Hi,
I'm using MS SQL Db. My issue is i'm not using insert...values( ? ?) syntax, if I use values along with ?, then undef is working properly. But I'm formatting a string, as shown below

my $string = "insert into ".$_db_name."..pre_bcp_hierarchy_buffer (client_scheme_code,
                                                                 client_level_1_code,
                                                                 client_level_2_code,
                                                                 client_level_3_code,
                                                                 client_security_id)
                            values (
                                  '". $_data->{'scheme'} ."',
                                  '". $_data->{'level_1'} ."',
                                  '". $_data->{'level_2'} ."',
                                  '". $_data->{'level_3'} ."',
                                  '". $_data->{'client_security_id'}."') \n";
 
print " String Val $string \n";
 
                                $allstring = $allstring.$string;
                                # Check if $i is n * 1000
                                if ( !($i % 1000 ))
                                {
                                                #write to pre_bcp_option_buffer
                                                $retval = $_trg_dbh->exec( $allstring );
 
 

When the count reaches 1000, i.e. when i've formatted an insert string of 1000 rows i'm inseritng at one shot, before formatting the insert string i'm checking whether i've any null values in any of the columns. if it is there then i've to assign NULL that is undef (per PERL) to that column in the insert statement/string i'm formatting, coz its a string the undef is assigning ' ' into the db, but not a [NULL]. I expect a [NULL] in the DB so that firther my query can use isnull() to check for NULL values.
Since my program is inserting the ' ' the isnull() couldn't identify the NULL rows.

Thanks
Atif

Hi,
Thanks very much for you updated code.

Here is the way i've updated my code

code:

 
 my $a = "TEST A";
    my $b = "TEST B";
    my $c = "TEST C";
    my $d = undef;
    my $e = "TEST E";
 
 
        my $string = sprintf("insert into %s..pre_bcp_hierarchy_buffer (client_scheme_code,
                                                                    client_level_1_code,
                                                                    client_level_2_code,
                                                                    client_level_3_code,
                                                                    client_security_id)
                            values (
                                  %s,
                                  %s,
                                  %s,
                                  %s,
                                  %s) \n",
                                  $_db_name,
                                  (defined($a) ? "'" . $a . "'" : 'null'),(defined($b) ? "'" . $b . "'" : 'null'),(defined($c) ? "'" .
 $c . "'" : 'null'),(defined($d) ? "'" . $d . "'" : 'null'),(defined($e) ? "'" . $e . "'" : 'null'));
 
my $retval = $_trg_dbh->exec( $string );
 

Thanks
ATIF

[numberwhun]

Hi,
Thanks very much for you updated code.

Here is the way i've updated my code

code:

 
 my $a = "TEST A";
    my $b = "TEST B";
    my $c = "TEST C";
    my $d = undef;
    my $e = "TEST E";
 
 
        my $string = sprintf("insert into %s..pre_bcp_hierarchy_buffer (client_scheme_code,
                                                                    client_level_1_code,
                                                                    client_level_2_code,
                                                                    client_level_3_code,
                                                                    client_security_id)
                            values (
                                  %s,
                                  %s,
                                  %s,
                                  %s,
                                  %s) \n",
                                  $_db_name,
                                  (defined($a) ? "'" . $a . "'" : 'null'),(defined($b) ? "'" . $b . "'" : 'null'),(defined($c) ? "'" .
 $c . "'" : 'null'),(defined($d) ? "'" . $d . "'" : 'null'),(defined($e) ? "'" . $e . "'" : 'null'));
 
my $retval = $_trg_dbh->exec( $string );
 

Thanks
ATIF

Atif,

When you are posting to TSDN, please use code tags around your code. A series of "---" do not suffice to surround your code as they do not have the same effect.

In the "REPLY GUIDELINES" box to the right of the message window when replying contains the code tags that you should use. They have a beginning and end tag structure, just like html.

I have fixed all of your posts in this thread.

Regards,

Jeff