Hi,

I have a strange problem with datasets in server side applications and DBISAM. Say you have a simple demo table:

ID: Integer
When: Timestamp

I define a dataset in the database with INSERT INTO Demo (ID, When) VALUES(:ID, :When), let's call it "dbdemotable". In the server application, I have a dataset named "ds", using dbdemotable, with the two columns (the first one integer, the second one DateTime). It's part of an API, but that sould not be of any importance.

Now, I call

ds.Insert;
ds.Columns['ID'].AsInteger := 123;
ds.Columns['When'].AsDateTime := Now; // "Now" is just an example, I need to store any datetime
ds.Save;

And then all the stuff with role, user, privilege, yada yada.

Now I call it using eg. SoapUI, with the correct authentication token as cookie. What I get is an error message:

"Unhandled exception: DBISAM Engine Error # 11949 SQL parsing error - Expected NULL, Date, or Timestamp expression but instead found ? in INSERT SQL statement at line 1, column 42"

line and column change when I add crs to the SQL statement, they always point to ":When". The server log says that the first parameter used is "123" and the second one is "168122971254". That's "Now" as a number. DBISAM (and more or less any other db system I know) wants a string like "2023-04-01 18:34". And everything works when I get rid of the When field.

So, why does EWB converts the value of a DateTime field to a number and not to a string?

Ralf,

<< Now I call it using eg. SoapUI, with the correct authentication token as cookie. What I get is an error message:

"Unhandled exception: DBISAM Engine Error # 11949 SQL parsing error - Expected NULL, Date, or Timestamp expression but instead found ? in INSERT SQL statement at line 1, column 42"

line and column change when I add crs to the SQL statement, they always point to ":When". The server log says that the first parameter used is "123" and the second one is "168122971254". That's "Now" as a number. DBISAM (and more or less any other db system I know) wants a string like "2023-04-01 18:34". And everything works when I get rid of the When field.

So, why does EWB converts the value of a DateTime field to a number and not to a string? >>

EWB uses "raw" JS date-time values with database/dataset operations:

https://www.elevatesoft.com/manual?action=viewtopic&id=ewb3&topic=Server_Database_Access_API

"Any content included with database API requests, or returned as a response to the request, should/will be formatted as JSON content. The date/time format used in the JSON content is equivalent to a raw JavaScript Date value: an integer value representing the number of milliseconds since 1 January 1970 UTC."

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

thanks for the answer. What can I do to get a datetime via Insert/Update into a DBISAM record? Currenty I am using a workaround I found yesterday, something like

SELECT ID, When, When AS When_String FROM Demo

Then in the EWB dataset, I declare "When" as a datetime field and "When_String" as a string field. When inserting/updating, I put an ANSI formatted date in When_String and use SQL statemens eg. for Update like

UPDATE Demo SET When=:When_String WHERE ID=:Old_ID

It works, but it does not smell right