I have noticed that [per the API documentation] databaseManager.createDataSourceByQuery does not allow for type specification like JSDataSet.createDataSource(name,types) does. Is this intentional or perhaps am I missing how to specify types when calling databaseManager.createDataSourceByQuery?
I bring it up b/c currently I am experiencing an issue where all of my Decimal values gotten from databaseManager.createDataSourceByQuery are being cast to integers and rounded half up. I believe I would be able to avoid this if I could instruct databaseManager.createDataSourceByQuery via JSColumn.NUMBER etc.
I’ve looked at the source of databaseManager.createDataSourceByQuery() in either 6.0.x code and 6.1.x vs databaseManager.getDataSetByQuery() hoping that this would be easily patchable.
What I’ve found out is that createDataSourceByQuery() is using a totally different mechanism to create the dataSource, than the one used by JSDataSet.createDataSource() for instance.
createDataSourceByQuery() is actually creating a dataSource based on a foundset backed by a temp table created by the query you pass.
I believe the discrepancy you’ve found is coming from a bug in the creation code of that temp table which, for whatever reason, doesn’t really retain the initial field types correctly.
Unfortunately the code for that temp table creation is IDataServer.insertQueryResult() which implementation is in the (non open source) server code, so I cannot help here.
IDataServer.inserQueryResult() has also changed signature between 6.0.x to 6.1.x from accepting a SQL String to an ISQLSelect (in this case a QueryCustomSelect), but I don’t think the issue is here, I think the issue is rather in the code that build the temp table, in the server IDataServer implementation.
So adding an array of types like you do with JSDataSet.createDataSource() will not be as easy as it sounds, although I think either this or fixing the insertQueryResult() method is needed.
I would suggest opening a case to the Servoy support as this is a bug as far as I can see it.
Basically, databaseManager.createDataSourceByQuery(…) is a shortcut for databaseManager.getDataSetByQuery(…).createDataSource(…) with the difference that the latter copies the data from the server to the client and sends it all back.
If you give the type parameters to the databaseManager.getDataSetByQuery(…).createDataSource(…) call, do you get the correct values for your numbers?
Yes, Jeff already told me that getDataSetByQuery().createDataSource() works with the correct types (if you provide them), but… in the case of createDataSourceByQuery() the advantage is having a temp table which is used for foundset lazy loading. Not true with getDataSetByQuery() where the entire dataSet needs to be loaded in memory to create the dataSource. This is an issue with big dataSets…
So I agree with him that a createDataSourceByQuery(query, types) is needed.
create the datasource using databaseManager.getDataSetByQuery(…).createDataSource(typeinfo), but with a query that selects the same columns but returns no rows (add ‘AND 1=2’ to the sql), this should be quick.
use databaseManager.createDataSourceByQuery() with the original sql and the same datasource name to fill the data server-side, type info from step 1 should be reused here.