I have a JDBC INSERT INTO statement that uses the syntax given in the FileMaker SQL reference: TIMESTAMP '2019-06-05 14:35:10' . This INSERT statement works fine using this modern format.
But the problem is that once this data is in FileMaker if I try to change any value, I get an error that the values must be in a certain range (the outdated format: "mm/yy/dd hh:mm:ss" format is shown in that error message).
See below:
The FileMaker SQL Reference Manual also lists this format: "{06/05/2019 14:35:10}" as an acceptable Timestamp format, but using this format generates a syntax error (with and without any quote marks) when I try to SQL INSERT it (in the same INSERT statement that works with the above 8601 format) using JDBC into FIleMaker.
I'm probably missing something simple here, but it seems like JDBC requires the modern 8601 format, as does ExecuteSQL, but FileMaker itself can't handle these dates.
What's the solution here for programmatic input of dates using JDBC?
The problem is that, in code, JDBC INSERT seems to only work with this syntax:
TIMESTAMP '2023-07-15 11:11:15'
Then, after the JDBC INSERT, you have a standard ISO 8601 date in your FileMaker database, but FileMaker can't handle it since they have yet to embrace this long-standing standard.
My only workaround thus far is to write a "CF" that will convert the standard date INSERTed back to a non-standard old FileMaker Timestamp.
Perhaps I need to ask Claris why their documented example with brackets (with the standard old-style FIleMaker dates (in my case): {06/05/2019 14:35:10} doesn't work in JDBC code.
Surprisingly, and, for me, disappointingly, is that the Claris office JDBC manual doesn't address this TS point. On page 24 of the SQL Reference manual, Claris lists "accepted TS formats", but from what I read these are for ExecuteSQL, not external JDBC.
I agree, this is annoying since other DB engines can tackle this, I thought that while accepting the TS from JDBC INSERT the TS would be reformatted automatically to the internal one via JDBC interpreter
With UNIX TS one expects to get into a mess but this one should work.
Would it be possible to share a screenshot of the snippet of FMP code that attempts to programmatically update the value that was originally set via JDBC? In particular, I'm thinking that if we take a look at the very steps leading up to the error dialog shown above, that maybe we could offer a way to work around the situation?
EDIT: I missed the word "input" when I read the above post and now realize that maybe the value is not being set by script, and rather through UI input. In which case I'd be curious as to how the layout object displaying this field is set up -- but less confident that I'd be able to suggest something helpful.
To clarify: There is no FMP code; this is an SQL INSERT statement via JDBC coming from outside FileMaker using the Claris JDBC driver.
The problem is when you try to INSERT a Timestamp into FileMaker using JDBC. The only syntax I've found that actually works is: TIMESTAMP '2023-07-15 11:11:15'.
However, this syntax puts a (standard) ISO 8601 date into the FileMaker record's field, which of course FileMaker still cannot handle.
The other variations I've tried for the field to INSERT have all failed with one error or another, such as a syntax error:
There may be some working syntax that will work with old-style, locale-dependent, FileMaker dates, but I haven't found it (the basis of my question).
The CF that I wrote as a temporary workaround converts the ISO date to the old-style, locale-dependent FileMaker date works fine, but really isn't optimal to have to clean up dates after an INSERT statement.
You can test this issue yourself by downloading a data IDE like DataGrip or others and trying to see if you can SQL-INSERT an old-style FileMaker Timestamp into a FileMaker table.
I could be missing something simple, my hope, but this case isn't even mentioned in the Claris JDBC reference manual. The syntax examples I've found thus far are from the Claris SQL Reference and seem to be aimed mostly at ExecuteSQL(), which I'm not using in this case as these JDBC INSERT statements are coming from outside FileMaker.
I think it will work when you substitute the "-" with "+" in the date string. I tried (without the JDBC thing) a timestamp field and typed "2023+08+02 12:00:01" and it worked.
FileMaker uses the ISO format with + instead of -.
I don't want to INSERT ISO Timestamps; that's what's happening now. After the INSERT, I have an ISO date that FileMaker can't handle. FileMaker can't sort an ISO timestamp, FileMaker can't edit an ISO Timestamp, etc. (In other DBs like MySQL, Oracle, etc., ISO Timestamps are the default so this issue wouldn't happen.)
But, in FileMaker, I need to be able to INSERT a Timestamp with JDBC that goes into the FileMaker field as the old style FileMaker date, for example: "08/01/2023 11:11:11 AM"
Your suggestion, while helpful, just puts the ISO date (like my example above) into the FIleMaker field which makes it useless to FileMaker.
The goal is to be able to, using JDBC, SQL INSERT the old style FileMaker Timestamp. Nothing I've tried does that so I am forced to use a CF to "clean up" the standard ISO timestamp INSERTed to convert it back to the old style FileMaker timestamp.
OK - you can't do it directly. Perhaps it’s my bad English. I don't understand why this format should be useless in FileMaker. FileMaker knows what's the year, what's the month and so on. And you're able to sort.
But - replace with + and ReadAsTimestamp should do the job and bring the date portion into the desired format according to the date and time settings of your device. Or am I still missing something?
Sorry if I'm still drawing a blank …
Thanks for explanation, @OliverBarrett . Now I understand that you are looking at it from outside FileMaker, while I was thinking from FileMaker's point of view.
Unfortunately I don't have any helpful idea there.
I see - thank you. I had read into something mentioned above that made me think that some scripting on the FMP end was where the error dialog was showing up, but then I read this next snippet and now see that the error is happening during a manual edit:
If you then try to edit that value, you'll get an error since it's not in the older (expected) format.
I had a thought that it might be easier to work around this at the layout level by specifying a presentation format that FMP will recognize (outside of JDBC), but I just tried a quick test of this idea, and though layout level formatting works well for visually presenting the data, it does not seem to help with the case where a user enters the field to edit the value.
If you as @mipiano suggested change the format to TIMESTAMP '2023+08+02 20:42:00' and put a autoenter-calc GetAsTimestamp ( Self ) on the timestamp-field, the field content gets changed to the file's format. It does not work with TIMESTAMP '2023-08-02 20:42:00'
Yes....The JDBC INSERT with the "+" works as expected with a local FMP table (you get the old style mm/dd/yy... format inserted), but in FMS it's still ISO. I've asked the client to confirm he has a timestamp field on his FMS. More later...
Using the "+" between the year, month, and day worked great. Client gets old style FileMaker locale-dependent date but sorting and everything works as expected, so all good.
