How to store array of 'set of coordinates' into database table

[enthusiast4]

---

@Yamaeda wrote:

In your read you try to convert all data to strings, that won't work for a Long binary data (and it's understandable the Variant field doesn't know how to present it). I'd try to convert the data to an Array of clusters with String and Numeric array. If the binary is hard to handle, just use a Varchar field and use the Array to spreadsheet as mentioned.

/Y

---

I did not convert the data into an rray of string ('particle' column) and numeric array ('frame' column). But, rather i passed the inddividual Array using parametrized query and DB Tools Set Parameter Value.VI. But, still the string datatype is blank in the 'particle column' and also it is throwing an error- data type mismatch in criteria Expression. Is it because my string data is too long and it is going out of range or any other mistake?

PS: Please check the updated VI and image in my previous comment.

[aputman]

This VI just keeps getting worse. 

• Why do you need a 2d array of clusters of 2d arrays?  The name of the control is "Coordinates of each particle pixel in each frame".  If the outer array is the frames, I don't understand why this needs to be 2d.
• "Database connected" has a race condition.  You are setting it to false and connecting to the database with no control over which gets executed first. 
• Your for loop has a "No of frames" integer wired to the N terminal and to the stop condition.  These are doing exactly the same thing.  No need for duplicate code.
• Why do you even need the for loop to extract frames?  If you are going to select the first N frames, use the Array Subset function.
• There is no need to check for the existence of the table name in every iteration of the loop.  Check it once outside the loop if you really think it's necessary to do this in code.  I personally wouldn't rely on your code to create your database.  That should be done outside of your front end application. 

If I understand what you are trying to do, this can be greatly simplified down to a few functions.  

[enthusiast4]

---

@aputman wrote:

This VI just keeps getting worse. 

• Why do you need a 2d array of clusters of 2d arrays?  The name of the control is "Coordinates of each particle pixel in each frame".  If the outer array is the frames, I don't understand why this needs to be 2d.
• "Database connected" has a race condition.  You are setting it to false and connecting to the database with no control over which gets executed first. 
• Your for loop has a "No of frames" integer wired to the N terminal and to the stop condition.  These are doing exactly the same thing.  No need for duplicate code.
• Why do you even need the for loop to extract frames?  If you are going to select the first N frames, use the Array Subset function.
• There is no need to check for the existence of the table name in every iteration of the loop.  Check it once outside the loop if you really think it's necessary to do this in code.  I personally wouldn't rely on your code to create your database.  That should be done outside of your front end application. 

If I understand what you are trying to do, this can be greatly simplified down to a few functions.  

---

I know my VI is getting worse. May be I am trying too hard to find a solution to my problem, that I am missing the coding reusability etc. 🙂 But again, I can always rectify it later and. I will try to amend my error as you have mentioned and will let you know soon. Thank you.  

[enthusiast4]

---

@aputman wrote:

This VI just keeps getting worse. 

• Why do you need a 2d array of clusters of 2d arrays?  The name of the control is "Coordinates of each particle pixel in each frame".  If the outer array is the frames, I don't understand why this needs to be 2d.
• "Database connected" has a race condition.  You are setting it to false and connecting to the database with no control over which gets executed first. 
• Your for loop has a "No of frames" integer wired to the N terminal and to the stop condition.  These are doing exactly the same thing.  No need for duplicate code.
• Why do you even need the for loop to extract frames?  If you are going to select the first N frames, use the Array Subset function.
• There is no need to check for the existence of the table name in every iteration of the loop.  Check it once outside the loop if you really think it's necessary to do this in code.  I personally wouldn't rely on your code to create your database.  That should be done outside of your front end application. 

If I understand what you are trying to do, this can be greatly simplified down to a few functions.  

---

Below are my answers to your questions:

1. 'Coordinates of each particle Pixel in each frame' is a 2d Array because -the outer array is for 'Frames in AVI', and the next Array is for 'number of particles in AVI'. Hence it is 2D.

2. I agree. I will correct it.

3. My for loop stop condition is wired to 'no of frames because' I want only the pixel coordinates of frames from 1-3 and not from 0-2 Frames (when 'N' of for Loop:3). 

4. I will check and modify the rest conditions.

3. 

[enthusiast4]

 Hello @Aputman

I have modified my code as you have suggested. Finally, it is not throwing any error. But, I am not getting the desired output.

  1. It has only 1 row instead of 3 rows for each of 'no of frames'.

  2.  The particle column (string datatype) is still empty.

[aputman]

---

@enthusiast4 wrote: 

1. 'Coordinates of each particle Pixel in each frame' is a 2d Array because -the outer array is for 'Frames in AVI', and the next Array is for 'number of particles in AVI'. Hence it is 2D.

---

Clearly I missed the meaning of the particles and what you are doing.  Good documentation is always important during the entire coding process. 

So in each frame of the AVI, you are getting the coordinates of N different particles, storing them in an array, and then you want to write these coordinates to a database along with the frame number?  Frames are the rows and particles are the columns?  Am I reading your code correctly?

I think your data structure may be better as a 3d array.  So by moving the Build Cluster outwards by one loop, you create a 3d array of particles.  The page index will indicate the number of particles with the inner 2d array being the coordinates of each.  Then the outer for loop (not shown below) creates a 1d array associated with the frames of the AVI.  This will simplify the process of extracting the data and writing to the dB, especially if you are only allowing one column in the dB for storing multiple particles. 

Your spreadsheet string for a 3D array will look something like this:

Spoiler

[0, 0, 0]
0 0 0
0 0 0
0 0 0

[1, 0, 0]
1 1 1
1 1 1
1 1 1

[2, 0, 0]
2 2 2
2 2 2
2 2 2

Let me know if I am way off base here.  

[enthusiast4]

Yes you are right. Sorry, I should have explained it clearly before to avoid the confusion.

My goal is basically to store the 'pixel coordinates' of each frame of a particular blob/particle. I want to store in a database table such that :

Column0 (frame)- signifies frame number(My AVI file has 868 frames. So, 868 rows)

Column1 (Particle1), Column2(Particle 2) and Column3(Particle 3) i.e i have total 3 particles

At the end, I have to extract the ' set of coordinates' (of all the blobs/particles available)for a particular row . E.g query: SELECT * FROM table_name WHERE frame= 10;

Right now, I am trying to just store the coordinates of a single particle. If I am succesful doing that, later I can insert the other blobs/particles as separate columns on the database table. Rather than using a 3D array, I can add one particle  as a column in the table one at a time. I guess, this might reduce the complexity ?

[aputman · Accepted solution]

Ok, those requirements are clearer to me now.  

I still say stick with the 3d array but that's how it makes sense to me.  Extracting out the 3 particles from the frame array is super simple.  Wire them into a cluster with the loop counter+1 as the frame number, and write it to the dB.  

[enthusiast4]

Thank you @aputman for your valauble help and Information. I agree that it is lot easier with 3 D Array. I have made changes as you said . Finally, it is not throwing any error and the coordinates as string datatype gets displayed  on the table. But, I am still not getting the desired output:

1. There are total 1246 pixel  coordinates for each frame. But , I am getting only 4 coordinates each for 3 frames (i.e 615, 297; 615, 298; 615, 299 and 615, 300).

2. Also, if I enter a bigger 'no of frames' such as 100 (actually i have a total of 868 Frames in my AVi file), it throws me an error saying 'the field is too small to accept the amount of data you attempted to add'. So, I even attempted to adjust the field size IN MS Access. But, the max number of characters allowed for 'string' field is 255, which is less than my 'no of coordinates'. 

Can you help me out in this or any valuable Information?

PS: Please check the atached Images below. Also, currently I am working with only 1 particle. I will add later the rest 2.

[aputman]

I am not very familiar with Access but there is a Memo data type.  Doing a quick search says that is stores 65k alphanumeric characters.