I’m loading a CSV into the template canvas dataset, templates.
Everything looks file, however, a lot of code I have to interact w/ the templates are disabled. If I go to the template canvas templates customizer and make no changes but click “OK”, everything works again as designed.
I imagine the template canvas needs to rewrite an index or something behind the scenes, is there any way to kick off that operation from a button or in my script loading the CSV?
I was basically looking for a way to store the entire dataset into a MySQL database (BLOB) and load it back in to the template canvas. I couldn’t quite find the easiest way to pull the mysql BLOB directly in, so i used ‘temp file’ (i would love to not involve the local file system of the device, im still looking for options)
This is the code used to save the file to a blob:
component = event.source.parent.getComponent('Template Canvas')
csv = system.dataset.toCSV(component.templates
system.db.runPrepUpdate("UPDATE table SET column =? WHERE id = ?", [csv, 6])
Here is the code loading it
import os, tempfile, csv
id = 6
result = system.db.runPrepQuery("SELECT column FROM table WHERE ID = ?", [id])
blob = result
temp = tempfile.NamedTemporaryFile()
csvData = csv.reader(open(temp.name))
header = csvData.next()
dataset = system.dataset.toDataSet(header ,list(csvData))
event.source.parent.pulledData = dataset
I would suspect that your round-trip to CSV is not preserving some aspect of the encoded columns. Opening the customizer is “fixing” the discrepancy. Consider using java’s ObjectOutputStream to directly serialize the dataset, combined with a true binary blob column in your database.
I’m new to all this, i’ll start reading up on ObjectOutputStream.
What I was also thinking was since system.dataset.addRow / system.dataset.deleteRow is working flawlessly, something like this should work. I believe these rows essential recreate the entire database each time a row is added or deleted.
I’ve been trying to figure out how to system.dataset.addRow for each line in the CSV, but I’m running into countless issues, any advice on either front would be appreciated.
Does the data need to be a BLOB?
If you create a table with the same column names and data-types as the template dataset with one additional column of “ID” then you could bind your template to a Named Query with “ID” as a parameter.
It would be dynamic and fast with no coding. Maintaining discreet row, column data will be easier than managing a BLOB of CSV data.
The table/SQL doesn’t even need all of the columns that template dataset uses. I have a template working this way from a SELECT Named Query and letting a lot of the columns take the IA default values. In my case, the only columns in my SELECT statement are:
It seems that the Template Canvas Customizer has to be the last thing to touch the headers, even grabbing the current via system.dataset.getColumnHeaders and rewriting them would cause a discrepancy.
I got it working, but not sure if in the most elegant ways. Querying and deleting all rows, and running an array on the csv.reader and using system.dataset.addRow to repopulate the dataset. Not sure if you can not touch the headers with system.dataset.toDataSet, if anybody knows, please share.