CDATA Postgres .rsd Schema Files

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([Field] 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.

​I found this regarding what CData convert source data fields to.
https://cdn.cdata.com/help/FPH/ado/pg_datatypemapping.htm

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