Re: How to insert a NULL value into a table!!

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.

Comment on Re: How to insert a NULL value into a table!!

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

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]