Leveraging SQLDataSource value transform logic

[jay.l.fisher]

Is there any way to access the logic that SQLDataSource uses to convert a field value supplied in a DSRequest to the string which is inserted into the dynamically constructed INSERT or UPDATE statement?

For example, if a field is defined in the ds.xml as type="boolean" sqlStorageStrategy="singleCharYN" we'd like to be able to call a method to convert the boolean value in the request to a string "Y" or "N". Similar with other conversions such as type="date" sqlStorageStrategy="number" sqlDateFormat="yyyyMMdd".

[Isomorphic]

There's not currently a helper method that would get you just that specific generated SQL fragment. What does exist:

1. SQLDataSource.getSQLClause() which could get you the needed info, but with some extra stuff you may not want ("VALUES (...").

2. DataSourceField.get() which would allow you to access the sqlDataFormat attribute to get the format, for use with Java's SimpleDataFormat

[jay.l.fisher]

I found a public method sqlValueForFieldValue(String, Object) in SQLDataSource which I'm sublcassing. If I call that method, passing the field name and value object, I get back the transformed value as a String surrounded with single quotes. If I remove the quotes I have exactly what I'm looking for.

Is there any danger in using that method, other than that it isn't documented so not officially supported. Would it be possible to document that method and support it's use in the future?

[Isomorphic]

That's the kind of method that, if made public, would need 2+ pages of documentation to specify it's behavior - this area is thick with workarounds and not all types are actually even transformed to SQL text per se (eg blobs, clobs).

So this is a much larger request that it would seem. Can we ask what use case is leading to the need for such fine-grained access to generated SQL?

[jay.l.fisher]

We have a legacy system built with a 4GL that encapsulates each table with an IO module that includes logic for validating the data and triggering other actions based on updates to the table. We're using SmartGWT to build a new UI over it, but we don't want to have to rewrite all of that logic. So we're using the standard SQLDataSource fetch features to retrieve data, but calling the 4GL components to handle add/update/remove requests. We need to pass the field values on those requests as strings, formatted as they would be if we were going to do the updates with SQL. In our case this would never include extended data types like blobs, clobs, etc.

Since we can describe the data types and transformations needed in the ds.xml field elements we were looking for a way to use that same meta data in the same way that SQLDataSource does when creating the SQL statements.

Since this transformation happens in only one place I think I'm ok with using sqlValueForFieldValue for now, and then replacing it with our own version in the future if we discover a problem. Ideally we'd like you to support this use of it, with the caveat that it doesn't include all data types, but I can understand your reasoning if you decline.

[Isomorphic]

Ok that's a pretty uncommon use case :)

We don't want to take on the project to make this a documented API with well-specified behavior right now, but what we can say is that there's no particular reason this API would be moved or refactored, and the most likely way it could break is if some database-specific workaround means we have to take an ordinary type (like say "datetime") and feed values directly to the JDBC driver instead of embedded in SQL strings.