I have a client attempting to load a postgres database into ODX Server (TX version 20.10.x). The table contains a field with dates that are invalid for SQL but valid in Postgres, for example 20011/01/01. Upon transfer, the task completes with errors that it is unable to parse datetime value.
All datetime fields have been converted to VARCHAR(MAX) using Override Datatypes and this is confirmed by looking in the ODX Storage in SQL Server Management studio.
Since the error still presents itself, it seems likely that the error is coming a step earlier, during the CDATA conversion. Unfortunately, it seems that there is no way to generate RSD/schema files from the CDATA Postgres provider. Normally I would change the datatype there in these situations, since it seems to be going wrong in CDATA somewhere.
How can I load this table?
Kind regards, Andrew - E-mergo
Page 1 / 1
Hi Andrew
You mentioned RSD files. If you have such a thing for the table I would not use a data type override.
Instead I would change the data type in the <api:info> area of the file.
Change it from date or datetime to String and save the file. Then once you synchronize it will change the field to a string instead of a date.
Hi Thomas,
Thanks for your reply. The issue is, the CDATA provider for Postgres does not have an option to generate schema files. Ideally I want it to generate the schema file and change the datatype there, as I would do with most CDATA providers.
Kind regards,
Andrew
Hi Andrew
OK, I see.
If you do not have an option to create such a file, it will not be possible to add it.
Regarding Postgre and showing dates, if you preview the table using a query table, like in this image.
Does it have the same formatting as your windows setup?
Hi Thomas,
Previewing the query shows the date in the appropriate format. Only if the preview happens to come across a record with an invalid date, does it give an answer.
Currently, the client has setup a query table where the SQL Query for the offending field is ‘cast(aField] as nvarchar). This works, however, given the extreme date range that Postgres allows it seems like this issue might happen more often. Having a query table as a long term solution would kind of be a shame…
Ideally we would get CDATA to add the option to generate an RSD file…
Kind regards,
Andrew
Hi Thomas,
I accidentally clicked the best answer button, and I do not see any way to undo it.
Kind regards,
Andrew
Hi Andrew
I can remove it yes.
Could you try to change all dates to datetime2, then it should allow a lot of dates previously not allowed.
Hi @Thomas Lind ,
SQL Server's datetime2 only allows 0001-01-01 through 9999-12-31 while Postgres allows 4713 BC to 5874897 AD. I guess an option needs to be added to clamp values exceeding SQL Server's min/max to those values.
Hi Thomas,
Any change in the data type would have to happen at the CDATA level. Changing the SQL datatype to varchar(max) does not solve the issue, which suggests the error message is coming from the CDATA provider..
I'm not sure how to change the data type for CDATA if there is no option to generate a schema file.
Kind regards,
Andrew
We have our own ADF PostgreSQL data source in the ODX.
So I have a table called rental in my test database.
When added to my ODX it looks like this.
While it is datetime2, which the source may not conform to, it will be a edge case to ever get this as an issue. It would likely be a date set as 0 or null, which it should be able to handle.
I also tried this in CData PostgreSQL.
So it may work better for the ADF one, though no other option seems to exist for Business Units.
Can you confirm that the source contains dates that is outside a normal datetime? If so, send these images to me and I will ask CData for solutions to this.
Hi Thomas,
I can confirm that it is an ‘out of range’ date value as far as SQL is concerned. Users added, for example, an extra zero when inputting the date, so we have years up to the range 20.000. The field is a date field in Postgres, and in Postgres 20.000 is well within the valid date range.
I have asked the client to send the exact value that is causing the error and the exact error coming from CDATA.
I have also confirmed that the datatype of the field in ODX Server is varchar(max) using data type override, so any date range from Postgres should easily fit in the SQL field.
Kind regards,
Andrew
Hi Tomas,
Can you let me know if you are waiting on information from me or have asked CDATA about this?
You should be able to reproduce it with a date in the year 20000.
Kind regards,
Andrew
Hi @andrew.gebhard we received the following response from CData
DateTimes in .NET go from year 0001 to 9999, so we can't successfully convert a date with a year greater than 9999 in ADO: DateTime.MaxValue Field (System) | Microsoft Learn It should be possible to return the date as a string by casting it in your query table if you want to access the data. Otherwise, you can also use the ReplaceInvalidDatesWithNull property to replace the invalid dates with a null value if you just need to prevent the exception from being thrown. ReplaceInvalidDatesWithNull is a hidden property, so you will need to set it via the Other property: Other=ReplaceInvalidDatesWithNull=True;
