How to insert a NULL value into a table!!

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

Morning Lovely Monks

I have two tables as follows:

@Clusters = qw/    Cluster_ID INT IDENTITY(1,1) PRIMARY KEY, Cluster_N
+ame VARCHAR(50) not NULL unique, DC_ID INT not NULL CONSTRAINT Cluste
+r_2_DC FOREIGN KEY REFERENCES dbo.Data_Centers(DC_ID)/;    


@Hosts = qw/Host_ID INT IDENTITY(1,1) PRIMARY KEY, Host_Name NVARCHAR(
+50) NULL, Cluster_ID int CONSTRAINT Host_2_Cluster FOREIGN KEY REFERE
+NCES dbo.Clusters(Cluster_ID), DC_ID int not NULL CONSTRAINT Host_2_D
+C FOREIGN KEY REFERENCES dbo.Data_Centers(DC_ID)/;
[download]

and I have the following sub that I intend to use to populate the table.

sub sql_write
{
    my ($input, $id, $type) = @_;
    if ($type eq 'SRV')
    {
        printf "SERVER HOST: %s\n", $input->{name};    
        printf "ID DC: %s\n", $id->{DC};
        printf "Cluster ID: %s\n", $id->{Cluster};
        # insert data into Hosts table                                
+                
        $dbh->do("INSERT INTO Hosts (Host_Name,Cluster_ID,DC_ID) VALUE
+S ('$input->{name}','$id->{Cluster}','$id->{DC}')");                
            
    }
}
[download]

I call the above sub with this command

my $rec_id = &sql_write($host_data,$Ids,"srv")
[download]

All hosts must belong to a data center, but in the same time not all hosts are part of a cluster!

so before I call the sub I do the following to the $Ids

$Ids->{DC} = $dc_id;
$Ids->{Cluster} = $cluster_id;
[download]

If do have a value for a cluster then $ids->{Cluster} will have that value, but if I don't have a value for a cluster then I don't want to insert a value to the Cluster_ID, in other words I want the field to remain as "NULL"! However no matter what I have tried since yesterday I kept getting this error!

DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server Driver][SQL Serve
+r]The INSERT statement conflicted with the FOREIGN KEY cons
raint "Host_2_Cluster". The conflict occurred in database "DEV1111", t
+able "dbo.Clusters", column 'Cluster_ID'. (SQL-23000) [er
 was 1 now 1] [state was 23000 now 01000]
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been 
+terminated. (SQL-01000) at U:\Doccuments\test.pl line 104, <DATA> lin
+e 1.
[download]

Could kindly an enlightened monks advise where I am going wrong or how can I insert a Null value into a field that has been declared as "NULL" in an MSSQL2008 db please? Thanks

Blackadder

Comment on How to insert a NULL value into a table!! Select or Download Code

Replies are listed 'Best First'.
Re: How to insert a NULL value into a table!! by mje (Curate) on Nov 13, 2009 at 13:28 UTC
You generally insert a null into a column from perl by using a parameter of undef. You are not using parameters; you are interpolating your perl scalars into a SQL strings. You should seriously think about using parameters. If you are saying $id->{Cluster} can be undef then why didn't you get an error when you attempted to use it in your do statement - have you got "use warnings" in your script. If a column has a default value and you want the default value applied then you must omit that column from the insert. I see you are using SQL Server, but just in case you use other databases too you should be aware that some of them consider the empty string to be NULL e.g., Oracle.	[reply]
Re^2: How to insert a NULL value into a table!! by misterwhipple (Monk) on Nov 13, 2009 at 14:22 UTC
I believe keszler is correct: you will not be able to insert NULL with the `undef` mechanism using an interpolated string. You can try setting `$Ids->{Cluster}` using a string literal; `$Ids->{Cluster} = 'NULL'` but string interpolation in general is iffy for writing a SQL query. It is better to use bound parameters: they're easier to get right, and they're safer, security-wise. See DBI->bind_param() for an explanation and example. There are other problems, however. You have not shown how `$dc_id` and `$cluster_id` acquire their values. Are you completely certain they're set to what you think they are? Also, the table definition for Hosts.Cluster_ID does not explicitly say the column is nullable. Are you completely certain of the default? -- Your left-hand veeblefetzer is calibrated to the wrong Frammistan coefficient. Pass me that finklegruber.	[reply] [d/l] [select]
Re: How to insert a NULL value into a table!! by keszler (Priest) on Nov 13, 2009 at 13:29 UTC
The Hosts table constrains Cluster_ID as a FK to the Clusters table. Is NULL an acceptable value there? Can you manually insert the same row that the code is attempting?	[reply]
Re: How to insert a NULL value into a table!! by gmargo (Hermit) on Nov 13, 2009 at 13:33 UTC
You may be able to use a backslash-capital-N string (`"\\N"`). Or you could write another INSERT statement that doesn't insert `Cluster_ID`.	[reply] [d/l] [select]