sending a 1-D array of test data to MSSQL 2012

[bonbonbaron]

Hi. Feel free to answer any number of the below questions that you can. Thanks in advance! 

1) Being new to MSSQL, I don't know how to send a simple 1-D array of floating-type numbers over to a table in Microsoft SQL 2012. This is as far as I've gotten. I get the message on the left with the current VI, and the one on the right when I get rid of the Time string-concatenation and just have the numbers going in to the table name instead.

2A) This is my guinea pig file, being run on a guinea pig database. Eventually I'm going to want to send data from a test procedure that records a piece of data per millisecond-- an array usually ending up with with about 15,000 elements, more or less-- over to the database. I'm sadly aware of the misfortune that Labview can only send over one row at a time. So how on earth do I make a new column for every new element of data for a test that's going to run for an indefinite amount of time?

2B) Ideally I'd have ONE table with a column for each test that was run, with the title of each column being the time and date that the test was run. Is there a way to "transpose" the data once it's in MSSQL?

[bonbonbaron]

bump

[bonbonbaron]

oh btw, I fixed that error message by putting quotes around my numbered column title. But now I'm getting the below:

[Wart]

There might be a quicker way for an array, but you can write things one element at a time like this.  If you're not trying to write huge amounts of data at a time this should be plenty fast.  I think you can get by without a bundle fuction when you write a single item, but it makes it easier to add more later.

[MaggieAggie]

Hello bonbonbaron, 

You may be getting that error because you data is going out of range. You can find more information at this KnowledgeBase. 

In response to your second question, this KnowledgeBase may help because it gives the SQL command to create a new autonumbered column programmatically. 

[bonbonbaron]

This has helped me tremendously, Wart! Thanks! I'm still experiencing issues though. Is there any way I can insert two indefinitely long columns or rows? (Either way would be fine-- one row/column is for time, the other is for data.) I want to put in about 200,000 elements per trial... When I gave your code a shot with merely 1,000 elements, it took an entire 16 seconds!!

[Wart]

I would create a cluster of the two elements you want to write (Time/Data).  Then create an array of these clusters.  Then you can index the array and just write the clusters.  Be sure your array of column names matches the order of the items in the cluster.

Most of the data I write is pretty small, and I only write it occasionally, so I don't have many comments on how long it should take to write large amounts of data.  I don't think the database toolkit is especially fast though.

[bonbonbaron]

That'd work in the case of constant data input, Wart. But it wants to put my 2-D array of dynamic data in as a 1-D array. Below is what my project has come to so far, with the error message that I get shown below. If I can just get this to work...

[Wart · Accepted solution]

I used constant data because it's a better illustation of how things work.

If you need to write two elements to a row at a time then you need to bundle them together.  The error you're getting is because you've wired one double as input but listed two columns.  Notice my last example writes a cluster (which can be created with a bundle node) of two elements.  You need to use bundle to create a cluster of time and data, and then wire this cluster to your insert node.

A few other comments:

Generally using express VIs is a reasonable place to start when you're new to LabVIEW, but you should learn to use shift registers to do timing between loop executions.

When you bundle the elapsed time and data together you don't need to turn any of it into an array first.  Just put elapsed time into one input and your data into the other.  The clusters you create are a single row of data.