SQL query dataset index error

Ignition
1

Hi, I’m very new to ignition and I’m having issues indexing data from a SQL query. The code below queries a single line from a database then I want this data to be in a list of lists that can be further operated on.

for i in range(3): #Gets the name, min, max and units for the appropriate parameter name and number
                SQL_statement =("SELECT fldDescription FROM %s WHERE tblControlModeParameters.fldID=%d"\
                "ORDER BY tblControlModeParameters.fldPrmtrNmbr;")%(ctrlPrmtrTbl,control_mode[i])
                temp = system.db.runQuery(SQL_statement,DBNAME)
                for a in range(9):
                	mode_prmtr_desc[i][a]=temp[a][0]

DBNAME, ctrlPrmtrTbl and control_mode are all predefined.

If I try and run this as part of a larger script I get an error stating “row index 0 out of range”. this error relates to the temp dataset being indexed (I’ve tested this with different values instead of a). the odd thing is that if I copy the same section of code to the script console it works perfectly!

in essence my question is:
why am I getting this error despite it working in the script console?
how is an SQL dataset formatted and am I going about using this dataset incorrectly?

thanks in advance, let me know if I’ve not made anything clear.

2

Try removing the semicolon from the end of your query.

What’s probably happening is that your query is coming back with an error - which means the temp dataset is empty - which is what gives you the Index Error when you try to access a specific element of the dataset.

3

thanks for the reply

unfortunately removing the colon doesn’t seem to have any effect either in the main script or in the script console. the query is returning the appropriate data (annoyingly as a column rather than a row).

4

Is “mode_prmtr_desc” a dataset? If so, what you are trying to do won’t work. Datasets are immutable – if you want to change a value in a dataset, you must replace the entire dataset with a new version with the changed element. The system.dataset.* script functions will help you do this. I generally use the toDataSet() function to create what I want from scratch.

5

“mode_prmtr_desc” is undefined up until that point the idea being that I want a list of lists rather than a dataset.

small update, I’ve tried converting the temp value from the query into a dataset using temp = system.dataset.toDataSet(temp) then using mode_prmtr_desc[i][a]=temp.getValueAt(a,0) to copy that data into “mode_prmtr_desc” but i get another indexing error. interestingly it works perfectly in the script console!