2D Array to DB

Databases
1

What is the most efficient way of reading/writing a 2D Array to/from a database?

2

XML/Plist?

3

thanks, Kem. Pardon my ignorance, but this is not a PList, its a standard SQLite DB. (or do I misunderstand your answer)

4

I should have been wordier. I was suggesting that you convert your array to XML or Plist (an XML document laid out to store data), then store the result into Text field.

If your array is something other than string, you can also convert it to a MemoryBlock with the first four bytes reserved for the Ubound of the first dimension of the array, then store that as Text.

You could also create a related table with four fields: masterID, index, field1, field2. If you might have to store several of these, you’d need an additional field to specify which array you are defining.

Just spitballing ideas here. I haven’t needed to do this so others may have better suggestions.

5

Well, the brute force method might be to have the table set up so that each row has 3 items. The first two are the X and Y locations in the array and the third is the actual data for that array element.

Or you could write it into a MemoryBlock and save it out as a blob.

I’m sure there are more elegant ways to achieve what you need.

  • Dale
6

You could just write it as a simple list with 3 columns

  • row
  • column
  • value

loop over it & insert all the values to write to the DB
do the reverse to read it and you can even figure out how big an array you need by selecting the max from row & the max from column

7

I would say that it depends.

If your 2D array is actually a structured set of records, like for example all the order items within an order (where you would have things like order number, line number, material, quantity,requested date, confirmed quantity, confirmed date, pricing group, etc.), then creating a table that corresponds to the structure of the record is probably best. You would then have a method that writes records sequentially (you will need to thik about creating and then maintaining records. For deletion, i often use a “deletion flag”, rather than a physical deletion), and a method that reads records and feeds the array.

For less structured data, I would agree with Dale’s and Normand’s suggestion.

8

Thank you Kem, Dale, Norman and Louis. All suggestions worthy of consideration. I’ll mull this a bit then post my results.