DB Tools Parser

[lmtis]

I am just getting started with databases.  I am trying to use the "DB Tools Insert Data.vi".  I put my data into a cluster, and pass the cluster into the VI.  When the data comes out of "DB Tools Parser.vi" the parameter names are missing.  What am I doing wrong?

[tst]

I haven't looked at the code now, but as far as I know, the names in the cluster don't actually matter - the values are inserted into the columns named in the columns input, in the tabbing order of the controls in the cluster.

[lmtis]

Is there any way to look at the SQL query that gets passed to the execute query VI.  Is there a way to convert the command reference that it gets back into text?  The error message that I am getting is "Syntax error in query.  Incomplete query clause."  The query that is coming out of the build insert query VI (blue arrow in this picture) is:

"insert into "Test Data"("Test Stand","Product","Serial Number","Temperature","Supply Pressure","Running Pressure","GPF","Timing","Result","HP GPF") values (?,?,?,?,?,?,?,?,?,?)"

When I watch "Cmd Create Parameters.vi", it looks like it is putting the corect parameter values into the property node.

[tst]

I don't think there is a way to convert the query, but I'm not really familiar with the ADO API. Personally, I don't really like spaces in things which need to be parsed, because they can be a pain, so I would suspect something there. I would suggest a search of the error text to see what other people ran into. If not, you can always build the full SQL insert command yourself. You can also use VIs like the OpenG variant VIs or the variant VIs in vi.lib to create an API which accepts a cluster, just like the NI VI does.

[lmtis]

I tried using:

INSERT INTO Test Data (Test Stand, Product, Serial Number, Temperature, Supply Pressure, Running Pressure, GPF, Timing, Result, HP GPF) VALUES ('My Desk', 'New Product', '12345ABC', 69, 77, 35, 1.28, 2468, 'Pass', 1.15)

and I am getting:

Error:0x80040E14

Syntax error in INSERT TO statement

I cannot see an error in the above statement.  I also tried it with an ending semicolon.

[tst · Accepted solution]

I'm not sure if spaces are allowed in a query like that (hence why I don't like them). They would still be my first suspicion.

My second would be that you have a reserved keyword in there somewhere, but I don't see anything that pops up at me.

My third would be that that's not the proper syntax for an INSERT statement, but it probably is. You should verify.

As a practical suggestion, I assume that whichever DB you're working with also has some kind of front end (SQL Server Management Studio, MySQL Workbench, SQLite Manager, Access, etc.). If it does, you can usually use that to generate a valid INSERT query and see the exact structure that it uses.

[lmtis]

Well tst, you da ... wabbit?  I had the reserved word Data in my table name.  I removed the spaces from the Database name and table name and from all but one of the column names, but still got the error.  Removed the space from the last column name and it works.  Thanks for your help.

[tst]

---

@lmtis wrote:

Well tst, you da ... wabbit?

---

If this was on LAVA, where this is my account, I might have been offended :D. Maybe I should change my avatar here to be Bugs and the signature to "Eh, what's up, Doc?".

Anyway, no. Not a rabbit.

And the important lesson here is to always try to think about how you can debug a problem you're encountering. There are usually solutions to be found.

[lmtis]

Hah!  I haven't been on LAVA much the last few years.  Also I never saw that cartoon.  I thought that was your name, but forgot your LAVA avatar.  Thanks again!

[Yamaeda]

The Database toolkit uses a generic simple system that's valid for all databases, and how spaces in names is handled is different, thus not included. What you can do is generate a query yourself or modify a copy of the Insert-function to always put column names in brackets [column name] as that's how SQL server does.

Come to think about it, in this case it's easy as you have the list of columns, just modify the list. 🙂

/Y