DBI: How do I set a variable to NULL in SQL Server? 2

[sloppyhack]

I have an script that inserts data into a SQL Server table and I'm having some trouble with zero-length vs. null values. Everything appends just fine, however SQL Server is interpreting all undefined variables as zero-length instead of null. I want the field left as null. Does anyone have information regarding how to do this or know where I can find some? Here's a sample of the SQL statement.

INSERT INTO stage.dbo.test_table (

CreateDate,
ProjectID,
JobNumber,
ResItNum,
CustItNum,
FacilityID,
VenName,
NormVenName
)

VALUES (

'$date',
'$proddata[$prodrow]->{'PROJID'}',
'$proddata[$prodrow]->{'JOBNUMBER'}',
'$proddata[$prodrow]->{'RESITNUM'}',
'$proddata[$prodrow]->{'CUSTITNUM'}',
'$proddata[$prodrow]->{'FACID'}',
'$proddata[$prodrow]->{'VENNAME'}',
'$proddata[$prodrow]->{'NORMVENNAME'}'

)

Cheers,

Sloppyhack

 

[philote]

You can either not specify that column in the insert statement or you can give it the value NULL with no quotes.

 

[sloppyhack]

Yeah. I was hoping wouldn't have to go that route. The SQL statement requires that the variables be in quotes. Looks like I may have to set it up to generate a different SQL statement every time. Fun. Thanks.

Cheers,

Sloppyhack

 

[philote]

Just found out something useful if you're using DBI. The database handle's quote method returns NULL (unquoted) if it's passed an undefined variable. So if you send all your values through this method you should be fine without having to generate a different SQL statement every time.

 

[sloppyhack]

That's it. I knew there had to be something out there to address that problem.

THANKS!!!!

Cheers,

Sloppyhack