Question

How to use JDBC driver to populate BD with a timestamp before the year 1000?

A
Badge

I am a FME beginner, trying to load timestamp before the year 1000 in a Vertica database, using JDBC driver.

Using:

@DateTimeParse(@Value(myDate), FME)

an error raise:

Table1_FeatureWriter_c058446b_6d16_48db_85ad_d913e88f07e526: '1' is out of range for 'year'. Range of 'year' is from '1000' to '9999'

Table1_FeatureWriter_c058446b_6d16_48db_85ad_d913e88f07e526: Failed to evaluate expression '@DateTimeParse(00010101000000, FME)'. Result is set to null

How to insert those dates?

Many thanks, best regards.

11 replies

ravenkopelman
Rank
Badge

Hi @ac_ac,

While I'm not sure about the valid uses of @DateTimeParse, it looks like the value in myDate is already in FME format, so you should try replacing @DateTimeParse(@Value(myDate), FME) with @Value(myDate) and see if that works.

A
Badge

Dear @ravenkopelman,

Many thanks for your answer. Using @Value(myDate), the same error is raised.

Best regards.

ravenkopelman
Rank
Badge

Dear @ravenkopelman,

Many thanks for your answer. Using @Value(myDate), the same error is raised.

Best regards.

Have you tried replacing the FeatureWriter with a "regular" JDBC writer? A repro workspace would be helpful to investigate further.

 

 

P

FME currently supports Years 1000-9999 in the Proleptic Gregorian calendar. We'd be interested in your scenario for earlier years. For example, would Years 1 - 9999 be enough? We're open to widening the supported range in future.

A
Badge

FME currently supports Years 1000-9999 in the Proleptic Gregorian calendar. We'd be interested in your scenario for earlier years. For example, would Years 1 - 9999 be enough? We're open to widening the supported range in future.

Dear @paulnalos,

 

Many thanks for your interest, Years 1 - 9999 would be perfect in our case.

 

Best regards.

 

 

P
Dear @paulnalos,

 

Many thanks for your interest, Years 1 - 9999 would be perfect in our case.

 

Best regards.

 

 

Would you be willing to share some details about your use case? That would help us motivate and prioritize this change.

 

A
Badge
Would you be willing to share some details about your use case? That would help us motivate and prioritize this change.

 

Dear @paulnalos,

 

 

In our case FME is used to feed our data warehouse from our operational system. To do so, data are loaded from CSV files into Vertica database. One of our objectives is to preserve data during this operation.

 

In source system, timestamps before the year 1000 are allowed. As the loaded timestamp is optional, this value can be null.

 

Please let me know if any further information is needed.

 

 

Best regards.

 

 

 

david_r
Rank
Userlevel 4

I would love to see the use case and hear the arguments for actually needing dates with a single day resolution going back to before year 1000...

In my experience, dates such as 0001-01-01 are almost always entered by users wanting to indicate "since way back" without having any precise date to enter, combined with unconstrained data entry possibilities in the client GUI.

The last time I was confronted with something similar we ended up pushing all such dates up to something like 1900-01-01 and defining that as "way back" to the users. It's also worth noting that recent versions of ArcGIS do not like dates going very much further back than this.

A
Badge
Have you tried replacing the FeatureWriter with a "regular" JDBC writer? A repro workspace would be helpful to investigate further.

 

 

Dear @ravenkopelman,

 

 

Many thanks for your answer. Please find attached my workspace with a data sample.

 

As suggested, I try a "regular" JDBC writer but connexion string format does not seems to be allowed:

 

jdbc:vertica://$(DBBICore_Host):$(DBBICore_Port)/$(DBBICore_Base)?searchpath=$(DBBICore_Schema)

 

 

The following error occurs:

 

Failed to obtain any schemas from reader 'JDBC' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information

 

Failed to read schema features from dataset 'jdbc:vertica:<solidus><solidus><dollar><openparen>DBBICore_Host<closeparen>:<dollar><openparen>DBBICore_Port<closeparen><solidus><dollar><openparen>DBBICore_Base<closeparen>?searchpath=<dollar><openparen>DBBICore_Schema<closeparen>' using the 'JDBC' reader

 

 

Many thanks for your help!

 

Best regards.

 

reproworkspace.fmwmydatafile.zip

 

 

ravenkopelman
Rank
Badge
Dear @ravenkopelman,

 

 

Many thanks for your answer. Please find attached my workspace with a data sample.

 

As suggested, I try a "regular" JDBC writer but connexion string format does not seems to be allowed:

 

jdbc:vertica://$(DBBICore_Host):$(DBBICore_Port)/$(DBBICore_Base)?searchpath=$(DBBICore_Schema)

 

 

The following error occurs:

 

Failed to obtain any schemas from reader 'JDBC' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information

 

Failed to read schema features from dataset 'jdbc:vertica:<solidus><solidus><dollar><openparen>DBBICore_Host<closeparen>:<dollar><openparen>DBBICore_Port<closeparen><solidus><dollar><openparen>DBBICore_Base<closeparen>?searchpath=<dollar><openparen>DBBICore_Schema<closeparen>' using the 'JDBC' reader

 

 

Many thanks for your help!

 

Best regards.

 

reproworkspace.fmwmydatafile.zip

 

 

Thanks for the repro workspace! Between a conference and long weekend I won't be able to look at it until Tuesday, but your new error message suggests the next step.

 

 

With a FeatureWriter we allow connection string parameter substitution that isn't allowed (at least in the same way) with a "normal" writer. If you were to change your connection string by replacing all the $(...) macros with the actual values we should get further (assuming you don't really need to dynamically set them).
ravenkopelman
Rank
Badge
Dear @ravenkopelman,

 

 

Many thanks for your answer. Please find attached my workspace with a data sample.

 

As suggested, I try a "regular" JDBC writer but connexion string format does not seems to be allowed:

 

jdbc:vertica://$(DBBICore_Host):$(DBBICore_Port)/$(DBBICore_Base)?searchpath=$(DBBICore_Schema)

 

 

The following error occurs:

 

Failed to obtain any schemas from reader 'JDBC' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information

 

Failed to read schema features from dataset 'jdbc:vertica:<solidus><solidus><dollar><openparen>DBBICore_Host<closeparen>:<dollar><openparen>DBBICore_Port<closeparen><solidus><dollar><openparen>DBBICore_Base<closeparen>?searchpath=<dollar><openparen>DBBICore_Schema<closeparen>' using the 'JDBC' reader

 

 

Many thanks for your help!

 

Best regards.

 

reproworkspace.fmwmydatafile.zip

 

 

Hi @ac_ac, sorry for the delayed response, and thank you for the repro.

 

 

Tweaking it to run against postgres instead, I can't see any complaints from the @Value(sowingdate) solution that you indicated didn't change anything. However, some of your fields are of the form @Evaluate(@Value(r7season)) which looks fishy to me; probably those @Evaluates have the potential to cause trouble.

 

 

In addition, I was able to set up a "regular" JDBC writer that respects your parameters. See the attached file.

 

 

We are both using FME 2018.1 so we should be able to get you fully operational.

 

 

20895-reproworkspace.fmw

Reply


Community Stats

28,709
Posts
109,481
Replies
36,554
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.