Insert NULL into DB with Database Connectivity Toolkit

[Matthew_Kelton]

What foreign keys exist for the table?  Just because the columns are not allowed to be nulls, does not mean they can be nulls.  The error message indicates there is a foreign key setup for the table.

[Porras]

Columns 2, 3 and 4 are foreign keys of another three different tables. The strange thing is that the values you see in the table attached here have been introduced without any problem (by another DB client, not LabView). There's no NULL values in column 2, but trying to insert only NULLs in columns 3 and 4 results in the same problem.

[Porras]

Well, finally I've managed to insert all values without obtaining any error message. I had to reproduce the problem in a new DB with the same structure, and it worked (I might missed out something in the original DB). However, DB TOOLS NULL.vi doesn't insert a NULL value into the fields I want to, but a 0 value. This isn't what I intend to do.

So, finally I gave up and made SQL Query.

Thank you all for your replies.

Regards,

Francisco

[sletrab]

Hello Porras,
The explanation is that the VI you used "DB Tools Insert Data.vi" has a Sub-VI called "Cmd Create Parameters.vi".
In this Sub-VI the NULL-Values for String will be replaced. At least in MS Access it is replaced with "\00" which leads for me to Problems.
In the Screenshot you can see how you can edit the VI to insert NULL-Values.

You can edit the VI to Transform NaN also to NULL.

[rolfk]

---

@sletrab wrote:

Hello Porras,
The explanation is that the VI you used "DB Tools Insert Data.vi" has a Sub-VI called "Cmd Create Parameters.vi".
In this Sub-VI the NULL-Values for String will be replaced. At least in MS Access it is replaced with "\00" which leads for me to Problems.
In the Screenshot you can see how you can edit the VI to insert NULL-Values.

You can edit the VI to Transform NaN also to NULL.

---

This sounds pretty wrong. What if you also want to insert an empty string instead at some point? There is a very distinctive difference between an empty string or the number 0 and a NULL entry in a database.

[sletrab]

Hello rolfk,

If I can decide if an empty String in a String column leads to NULL or \00, I choose NULL. If you edit this value later you have to delete the "\00" from the beginning of the string. Everywhere! You do not need this, if it is empty.

For floating point numbers the most equivalent Value for NaN in a Database ist NULL.

You wrote: "What if you also want to insert an empty string instead at some point?" This problem is in the ni solution also unsolved.

In both solutions you can build an insert string by yourself. Normally I do not want to use this lowlevel way.

The main point was to explain why the vi ignores the NULL value and to give a work around.

Greetings

sletrab

[rolfk]

The correct solution would be to recognize the explicit VT_NULL and pass it on. As it is the NI_Database_API.lvlib:: DB Tools Parser.vi is recognizing the VT_NULL as External Data and then that gets translated into a binary datatype. That is a bit unfortunate, but it is where a fix should be applied, if you go to the trouble of modifying the Database Toolkit VIs anyways.

[sletrab]

Hello rolfk,

I agree.

I have not edited the toolkit VIs. I copied the VI rename it and made my adoptions.

I did not want to but they do not fit my needs. I absolutly agree that it is a bad Idea to edit the VIs of a Toolkit.

Greetings

[gepponline]

Hi,

 i have a similar problem.

I need to insert some NULL values in datetime fields.

If I insert an empty string, I obtain a 1900-01-01 value in DB an it's not what I want.

If I replace the empty string constant with NULL it gives me error.

If I use the DB Tools NULL VI it give sme another type of error, maybe 'cause I'm connecting a variant to a cluster of string.

If use the string you see in the label at the bottom of my diagram in SQL Server manager, it works correctly.
How can I obtain the same result with labview?

[sletrab]

Hello,
Your example is far from working. Take a look at the examples: C:\Program Files (x86)\National Instruments\LabVIEW 2013\examples\database\Database Insert.vi
Try to insert at first only one parameter. Than, if it is working add another Parameter.
Use Timestamps for time and than convert it into string.