Case sensitiveness of datasets

Ignition
1

I’ve always thought that the PyDataSet type was just a wrapper around the DataSet, to make the getters more pythonesque.

However, I recently bumped on this discrepancy between both:

originalPyDataSet = system.db.runQuery("SELECT column FROM my_table")
newDataSet = system.dataset.toDataSet(originalPyDataSet)

print newDataSet.getValueAt(0, "column")
print newDataSet.getValueAt(0, "Column")

print originalPyDataSet[0]["column"]
print originalPyDataSet[0]["Column"] # < this crashes because "Column" is not found

Now try to guess what this script will do, will it error out (where?), or what will be printed? Pretty impossible right?

headers = ["column", "COLUMN"]
data = [["value", "VALUE"]]

dataSet = system.dataset.toDataSet(headers, data)
pyDataSet = system.dataset.toPyDataSet(dataSet)

print dataSet.getValueAt(0, "column")
print dataSet.getValueAt(0, "COLUMN")
print pyDataSet[0]["column"]
print pyDataSet[0]["COLUMN"]

Or can you guess what happens when you bind the above dataset to a table?

I wonder why this behavior was chosen. If anything, the PyDataSet should be case insensitive as it’s returned as the result of a SQL query (and most databases have some case insensitiveness). While the DataSet is more often being constructed in scripts (being what binds to tables, and PyDataSets can’t be created from scratch either).

2

Digging a bit deeper, it appears that the DataSet is following the JDBC ResultSet way to handle capitalization. But the PyDataSet isn't (while that's just the one we get back from a query).

From the JDBC ResultSet Documentation

Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

IMO, this makes queries a lot more portable.