I posted this on the Slack channel, but I thought this was worth posting here as well.
Has anyone tried using the Progress DataDirect JDBC drivers with Lucee? (ACF uses the DataDirect drivers). If so, how did they work and what was the cost? (I can’t seem to find cost information anywhere online for the drivers, which leads me to believe they’re probably extremely cost prohibited for us).One of the issues we’re running into w/our migration to Lucee is that we’re on SQL Server 2019, but our database highly leverages datetime datatypes, instead of the newer datetime2 datatype. In the DataDirect drivers, you can add DateTimeInputParameterType=datetime to the datasource connection string to have the driver send all timestamps using the datetime datatype. Unfortunately, the Microsoft SQL Server JDBC drivers do not have a similar configuration option. They send timestamps based on the compatibility level of the database. If the database is using SQL Server 2014 or lower compatibility, then the JDBC driver sends timestamps as datetime , otherwise timestamps are sent as datetimeoffset . However, there are casting issues that occur when SQL Server casts datetime datatypes to datetimeoffset datatypes. This happens because SQL Server will end up casting a datetime value of 2021-09-27 18:36:01.933 to 2021-09-27 18:36:01.9333333 , so when you would you have a query that looks like select * from Table where Date = <cfqueryparam cfsqltype="cf_sql_timestamp" value="2021-09-27 18:36:01.933" /> the query ends up getting translated to select * from Table where '2021-09-27 18:36:01.9333333 +00:00' = '2021-09-27 18:36:01.9330000 +00:00' when the Date column would contain a value of 2021-09-27 18:36:01.933 . This leads to where statements not matching.The jTDS JDBC drivers have some issues with new SQL Server datatypes and it does not support encryption, so it’s not an option for us.I’ve been working on code to convert our schema over to using datetime2 datatypes, because this avoids the issue, but it’s turning into a much larger scope of project than I really want to tackle now, especially since there really isn’t any real tangible benefit. Part of the problem is I have to switch everything over at once since everything is handled at the JDBC level. Modifying the data in each table is going to take a while because we’re talking hundreds of millions of rows of data, then there’s the fact that the I’ve got to drop all nested dependencies and rebuild them (which involves a lot of indices and stats) and there’s substantial time involved where I’d have to bring the system offline (since we’re not on SQL Server Enterprise, I can’t rebuild the indices online).I’ve got some strategies where I could isolate the conversion of the data separately, but this project is drastically increasing in scope and is something I’d rather not tackle at the moment if I do not have to.
One way I can work around the casting issues, is if I explicitly cast parameters to datetime . For example:
select *
from Table
where Date = cast(<cfqueryparam cfsqltype="cf_sql_timestamp" value="2021-09-27 18:36:01.933" /> as datetime)
This would force the casting of the datetimeoffset parameter to be a datetime value and the code would work.Going through all the code and doing explicit conversion though doesn’t work in all cases (like when using <cfstoredproc />).Is there anyway to get Lucee to write the casting conversions for me?