FDBatchMove SQL Writer to PostgreSQL is inserting an empty string in TEXT type columns instead null values
Hi, I'm new in this community,
In a new Job, I'm started learning how to use Embarcadero Delphi XE7 with FireDac and my first duty is migrate a large database from SQLSERVER2014 to PostgreSQL 10. My RAD version is 21.0.17017-3725
I have an issue in my destination DB (Postgres) using the component FDBatchMoveSQLWriter, FDBatchMove is not inserting NULL values in TEXT type columns, empty strings are inserted instead (these values are null in the origin table). NULL is not equal to Empty.
There is an option called StrsEmpty2Null located in Connection>Format Options, but some tables had a column constraint that restricts NULL values from being inserted in TEXT type fields and this results into a big error message. Also using it will insert null values in records that originally are meant to be empty strings. So this is not an option in my case as far as I know.
On the other hand, using FDBatchMoveDataSetWriter (Way slower) the NULL values in TEXT type columns are inserted correctly (as nulls), but for a reason that I ignore the actual text is converted to Asian characters. The origin DB General Collation is LATIN1_CI_AS and the Destination is UTF8.
I'm not using Latin1 Collation at the destination DB becasue UTF8 accented vocals were not inserted the right way (Some UTF8 Characters are not allowed in Latin1).
My DB reader in both scenarios is FDBatchMoveSQLReader from a MSSQL-2014 database.
Many thanks in advance.
I have had the same problem and then some when copy data from a NexusDB to Microsoft SQL Server.
There are at least 5 bugs here in FireDAC. I have reported them as bugs in https://quality.embarcadero.com/ as support issues RSP-19731, RSP-19732, RSP-19733 and RSP-19735 and RSP-19746. See the sample application for each support issue.
PS. The use of the TFDBatchMove.OnWriteValue event is not available to you if you are using Delphi XE7.
PPS. Please vote on the support issues above that you feel affect you as well. This so that we might get them corrected.