Database: Insert Array into Database - How do I do that?

[mw42]

Hello,

I want to insert a 2D array (all strings) in my MySQL database. This array has ALWAYS 709 Columns (which is the same as the database), but there are different numbers of rows (at least 1).

How would I go about that? My first try is obviously wrong (at least when the array has more than one row).

But do I need to use "Execute SQL Query" and build something like "INSERT INTO table VALUES (%s, %s, ... , %s)"  [with 709x "%s"] ?

[Kyle97330]

First, I have to address your error wires.  Just wire them through from beginning to end, there's no need to have them come out separately and then merge them later.

As for your main problem, the documentation for the "DB tools insert data" VI specifically says about the data input:

data specifies the data you want to insert in the database. If data is a cluster, this VI inserts each item in the cluster to each element in the columns input. The item order of the cluster determines the order how this VI inserts the items to the table. For example, this VI inserts the 0^th item to the 0^th element of the columns input. If the columns input is empty, this VI inserts the 0^th item of the cluster to the first column in table.

If data is not a cluster, this VI inserts the data to the column specified by the columns input.

The general details say:

DB Tools Insert Data Details

The insert is performed on the columns in the columns array, or if the columns array is empty, all columns in table are used. The data input can either be a single element or a cluster with each cluster element corresponding to a column in the table. The order of the elements in the cluster determines which column each element is inserted into. Thus, the number of items in the cluster must equal the number of elements in the columns array, or if the columns array is empty, the number of items in the cluster must equal the number of columns in the table. In the case where data is a single element, the columns array must have one element, or there must be one column in the table.

As such, I think you need to use a FOR loop to get just one row of your data at a time, and then use the "Array to cluster" node (right-click it to set its size to 709) to convert that row into a cluster, and then wire it in.

[mw42]

Thank you for your reply.

Unfortunately, the "Array to Cluster" converter only supports up to 256 elements, which is not enough in my case.

Any idea how to circumvent this?

[mw42 · Accepted solution]

I think I figured it out, at least it is working now.

I used "DB Tools Execute Query" and build the SQL-Query-String. And since I want to insert values in ALL COLUMNS I don't need to supply the column names in my query, which makes it much shorter.

My array of values went into a FOR structure and I added [comma] and [quotation marks] for each element, so that my query string looks like this:

INSERT INTO table_name VALUES ("0","2020-07-20","10:45:13","1.234","2.333", ... "709.000")