NULL values to MS SQL7 database from DBTools

[Spaz]

re: Windows 2000 + LV 6.02 + DBTools + MS SQL Server 7.0

I haven't had much db experience and luckily need little to use the new Database connectivity toolset, which I really like BUT...

I am having a hard time dealing with the lack of support for NULL values. I have attempted to write NaN to SQL database to force a null but the result when viewed via the SQL Server Enterprise manager>view all rows is '-1.#JNAN' (in a type = float & size = column???). Some SQL Server tools choke on rows(records) containing this 'value' in the respective column(field). Sometimes the error is reported as a value out of range and sometimes the reported error makes no sense in relation to the suspected problem (but hey that's MS SQL Serve
r). The DBTools manual states that LabView has no concept of NULL and proceeds to offer an example of filtering data from a database (that obviously wasn't written from DBTools) to insert NaN for null values.

My deliverable must include nulls for missing data. NaN chokes some Enterprise tools, so I can't expect it to reliably work for whatever application the end user is expecting to use to access this data. 0.00 IS NOT a null (I have a concept of that!)

This seems like a major oversight in the toolset.

Does anyone have any suggestions, comments or work-arounds for this

[Ben]

Hi Spaz,

Just a thought.
How about defining the field in the DB as defaulting to NULL.
Then, when updating or creating the record, omit an update statement for that field any time you have to write the NULL. Just let the DB fill in the NULL.

Ben

[Dennis_Knutson]

I don't know much about the DB connectivity toolset but to insert a NULL value into a column using SQL, the syntax is INSERT INTO dbname (columnname) VALUES (NULL). This works with the old SQL toolkit and SQL Server 7 and 2000. You should be able to replace all missing data with the NULL keyword or just not do an insert into that column. The database column must also permit the entry of nulls.

[Mark_Ireton]

I had the same problem with the Labwindows/CVI SQL toolkit and I worked around the problem by creating a string parameter with a length of zero. Not sure if the Labview version has a similar function. Mine looks like this:

DBCreateParamChar (statementHandle,"parameterName",DB_PARAM_INPUT,stringPointer,0)

where the last parameter is the string length. Note that just setting the string to NULL didn't work because expected string length includes the NULL character. So for a normal string, I have to use "strlen(stringPointer)+1". For a NULL string, that expression would evaluate to 1 of course.