I was looking into some discussions here about the JDBC driver for SQL Server and Ignition and I'm a little confused as to why everything doesn't play nicely together. I had read most of the posts on this topic across the forums but many of them were a few years old.
For those who haven't tried it, there will be an error generated when attempting to read a SQL Server resultset that contains a datetimeoffset type (unknown SQL type : -155).
I've done a bit of trial and error, and from what I can tell on the SQL server side, you can convert the datetimeoffset to datetime2 or varchar or a unix timestamp before selecting it in a resultset and it will read appropriately, but it means any time you pass dates back to Ignition those columns need to be converted.
If you are passing dates from Ignition into stored procedures or functions, you can specify an OffsetDateTime and pass it as a string, and that seems to convert okay.
From what I can tell, the JDBC driver has its own DateTimeOffset class which can be converted to a timestamp or other Java types, and other uses of the JDBC driver use that intermediate class to read those columns. For whatever reason, that doesn't seem to be an option with Ignition (as far as I can tell). There's also no connection settings that seem to modify how the dates get returned (again, as far as I can tell).
The conversion option will work, but it seems very strange when it seems like everything is speaking the same language already.
Microsoft, as usual, didn't implement their JDBC driver using standard JDBC data types.
Ignition supports the JDBC standard so that it can talk to many brands of database. For technical reasons (connection pooling in particular), the true JDBC connection is not exposed elsewhere in Ignition, so brand-specific datatypes and methods are not supported. Brand-specific layers in such drivers are used to lock users into a particular brand. Also the usual behavior from Microsoft.
The short term solution is as you've identified--convert your column types in views or procedures.
The long term solution is to fire Microsoft and deploy a competent, standards-compliant database technology. I generally recommend PostgreSQL.
Hi pturmel! I've certainly read a few of your posts on this topic.
I would not expect anything beyond the JDBC layer to know about anything beyond the standard Java classes. My confusion is that the Microsoft-specific class can be converted to the standard Java classes, and so nothing beyond the parts of ignition that handle JDBC connections need to know anything about Microsoft and their database classes. Like if I can convert to a timestamp or UTC or whatever in SQL, surely the Ignition database methods can handle that conversion instead?
No. Only the JDBC vanilla methods are available on the JDBC layer in Ignition. Nothing in Ignition is "Microsoft-aware" (or Oracle-aware or PosgreSQL-aware), so cannot invoke the conversions if it doesn't automatically happen in the resultsets. And it doesn't, because Microsoft.
Aha, I think I understand. Basically, the JDBC driver for Microsoft doesn't make that conversion available in a standard Java way, and Ignition isn't going to make the extra step to convert a Microsoft-specific class, so there's a logical impasse which I see as an exception.
When I'm reading folks talking about extending Java ORMs to handle datetimeoffset with the Microsoft JDBC driver, they're basically bridging that gap for themselves by handling the conversion. That said, presumably I could do the same thing even if it's not a standard Ignition feature. Hmm...
As I mentioned I read your posts before so I have considered Postgres - it isn't not an option but given the state of the project I'm working on, I don't think I could justify swapping database technologies for convenience in datetime types. Sometimes you've got to make do with what you have.
I forgot to say thank you for the help earlier, but thank you for the information - you've given me plenty to think about.
For sake of argument, I'll say it's not possible, and I'm definitely going to agree to disagree on the topic of what constitutes engineering malpractice
Anyhow, from my tinkering, I can use the gateway database connections to query a datetimeoffset, extract it in the resultset, and do things with it (convert to timestamp, etc). It looks like running it through the conversion from a JDBC resultset to the internal Ignition class(es?) like BasicStreamingDataset is where Ignition gets grumpy with it, which I guess tracks with the above discussion.
I was hoping there might be some way to get the data into an agreeable object or fall back to a string conversion - ie, if there are easy approaches to add to the list of types to map. I did see Ignition's TypeMappings class which doesn't look to be modifiable, but is definitely missing the -155 SQL type.
I imagine there are some brute force options if I really wanted to make it work, but it would be way more effort than using a convert to datetime2
