How to write NULL values using parameterised query or stored procedure

[Gurdas]

Hi,

How can I pass NULL values when using the Parameterised SQL Query (or stored procedure) method to write values into my SQL server database?

The problem can be simulated using the "Insert Example.vi" shipped with the Database Connectivity Toolset (ver 1.0, May 2001).
LV allows for NULL when using "SQL Query" but gives an error when using "Parameterised SQL Query".
In this example, the error occurs when the "device name" string field is set to blank (not even a single white space).

I am using LV 7.1 FDS and Database Toolkit ver 1.0 on Win 2000

Thanks,
Gurdas

[Philip_Courtois]

Hi Gurdas,

This behavior has previously been reported to the LabVIEW developers. Here is the reason that you get an error with the Parameterized Query:

There is a big difference between executing an SQL statement and doing a Parameterized Query, especially when looking at the low-level ADO layer. For example, you use many different methods and properties of the ADO objects in respect to the Command object. When doing Parameterized Queries, you MUST specify all parameter values - leaving them empty results in ADO trapping errors rather than using a default (or NULL) value. Since this is defined by ADO as an error, there is nothing to do about it from the LV layer. You should continue to use SQL statements rather than parameterized queries.

Gurdas, I hope you can live with this solution...

[Gurdas]

Hello Phillip,

Your answer explains what (and why) happens if I pass an 'empty' parameter value.

However that does not fully answer my question, which I have rephrased below:

I want to know how to pass NULL values because if I do that, I can continue to use parameterised query (something which I HAVE to do since SQL query cannot handle the said tasks). In VB, saying NULL (and not 'NULL') tells the compiler that it has to pass null values and not the word null - and this works for parameterised query too. I want to know if LV has something similar.

Hope I have been able to clearly state my question.

Thanks,

Gurdas

[Philip_Courtois]

Hi Gurdas,

Well, the reason that I can't give you a solution is because there currently isn't one available to pass NULL values to a DB using the Parameterized Query VIs.

Has anyone gotten around this limitation in any way, while still using Patameterized Querys?

[Maximus43]

Can you store NULL-values in that specific column?

Than you should change your query, so that column is not mentiod

e.g

Your table1: col1, col2 and col3

col3 can store null-values

If you want to insert null in col3 insert:

insert into table1(col1, col2)
values(?, ?)

[Gurdas]

Maximus,

The said columns have 'can be null' activated in SQL server design table page.

However, the column will at some occassions have non-null values. There are 5 such columns. Each stores char data type and each can be null or non-null. The problem is NOT when I am first inserting the recordset. If the string control is 'empty' the field is set to null in the database. If the string control has a string, it goes into the target field. So far so good.

The problem occurs when I am trying to update a existing recordset. Here I use a stored procedure and paramterised query. Now, if any of the 5 string controls is passing a empty (aka null), an error is reported and the complete row (which has other columns) is not updated. Very irritating.

There are two possible workarounds (we chose the first):

1) Force the user to type some string in the control

2) Check for empty and if true substitute it with a white space.

If only LV had a null equivalent ....

Thanks,

Gurdas

[Maximus43]

In the case of the stored procedure it is easy:

You just need to set the default value of the parameter to NULL:

CREATE PROCEDURE [update_MyTable]
 (@fldID  numeric,
  @fldData  real,
  @fldText  varchar(50)= NULL)
as

UPDATE MyTable
SETfldData = @fldData,
fldText = @fldText
WHERE ( fldIDs  = @fldID)

Through the "@fldText  varchar(50)= NULL)" a default value will be used if that parameter is not specified (in this case, null is used)

[IDILIO]

First of all set the default_value of the parameter to an empty string in Parameter in of DB Tools Create Parameterized Query.vi

After that check if the string control is empty or not (case) if it's empty don't use DB Tools Set Parameter Value.vi, if the string has some value not null use the DB Tools Set Parameter Value.vi to set the value.

This works to set null values to stored procedures (tested with Oracle).

See null.jpg example attached.

Message Edited by IDILIO on 10-23-2008 07:43 AM

[slipstick]

I can't say for other databases, but for SQL Server, I found a neat way to send a NULL value to our database that works with parameterized queries. Drop a Database to Variant vi on your diagram. Right-click the output and create a CONSTANT. Don't put anything in it. Wire it in where you want to send a NULL. Lose the Database to Variant vi. Works a treat.

Tay

[David_Grucza]

I had given this a kudo to the empty variant idea, but then I checked if it worked. The parameterized query will run, but the data in the DB table is not a NULL. It is the default datatype for the field.