Bring a boolean into a FPMI dataset

Archived Topics
1

Using mysql-connector-java-5.0.4 and FPMI 3.3.2

I’m trying to pull a boolean from a MySQL database into a FPMI dataset. Using transformedBitIsBoolean=true in the connection string I can get the column to show as a boolean in the DB Browse section of Property Binding, but FPMI won’t convert this data to a boolean in a FPMI dataset, I get an error code “SQL type code ‘16’ not defined.”

Is there a datatype that FPMI will convert into a boolean in a dataset?

My end goal is to use a script to pull data from the database and assign it to the Column Attributes Data dataset of a Table, thereby allowing me to use one Table with multiple layouts.

Thanks,
Pat

2

I can’t reproduce this, can you send a copy of your MySQL table?

Both Booleans and Tinyints should convert to a bool type in FPMI.

3

Unfortunately, this is a problem with the MySQL JDBC driver. The driver cannot return a TINYINT as a Java Boolean data type. So you either get the error below or the data type comes in as a Java Short. It looks like you will have to get the data back in scripting and cast the individual values to Booleans manually.

4

With Travis’ help I was able to come up with a solution. Basically I run a query, create a new PyDataSet, populate the new PyDataSet with the query results while casting the rows that need to be boolean with java.lang.boolean. I did run into an issue with color, I’m not sure how I’m going to store color in the database so for the time being I’m just adding an arbitrary color to the dataset.

[code]def getColumnAttributesData(ID):
import fpmi
from java.lang import Boolean
from java.awt import Color

results = fpmi.db.runQuery("SELECT name, label, hidden, width, horizontalAlignment, verticalAlignment, \
							numberFormat, dateFormat, treatAsBoolean, translateMap, imageMap, prefix, \
							suffix, treatAsProgressBar, progressRange, progressBackground, progressForeground, \
							hideTextOverProgressbar, bgcolorMap, fgcolorMap, editable \
							FROM dataview_attributes WHERE dataview_id = %i" % ID)

formattedHeader = ['name', 'label', 'hidden', 'width', 'horizontalAlignment', 'verticalAlignment',
				   'numberFormat', 'dateFormat', 'treatAsBoolean', 'translateMap', 'imageMap', 'prefix',
				   'suffix', 'treatAsProgressBar', 'progressRange', 'progressBackground', 'progressForeground',
				   'hideTextOverProgressbar', 'bgcolorMap', 'fgcolorMap', 'editable']
formattedResults = []

for row in results:
	formattedResults.append([row[0], row[1], Boolean(row[2]), row[3], row[4], row[5],
						 	row[6], row[7], Boolean(row[8]), row[9], row[10], row[11],
						 	row[12], Boolean(row[13]), row[14], Color(255,255,255), Color(255,255,255),
						 	Boolean(row[17]), row[18], row[19], Boolean(row[20])])

return fpmi.db.toDataSet(formattedHeader, formattedResults)[/code]