Reading & Writing to a db with unknown date type

Discuss all problems you have with Servoy here. It might help to mention the Servoy version and Operating System version you are using

Reading & Writing to a db with unknown date type

Postby Chuck.Amata » Tue May 29, 2018 10:31 pm

We have an existing Pervasive 11 dB where date fields are saved as "YYYY-MM-DD" length 10. In Servoy 7, the data type comes up as UNKNOWN TYPE#9 with length 10 in the column screen for the table. The data screen shows the data in the field as YYYY-MM-DD. If we connect one of these date data providers to a form field set to type CALENDAR, the data appears correctly on the form as MM/DD/YY. However, if we attempt to edit the field, the application crashes with an "internal error" as soon as Servoy tries to write back the updated value to the table. Since we cannot change the date type or format in the database (it is used by another application), and Servoy seems to know how to properly convert the field for display on the form, is there a way to have Servoy convert the date generated by the CALENDAR field type back into the correct date format for the database (e.g. YYYY-MM-DD") when it attempts to write the information back to the database?

It looks like a UI converter might handle this read write problem. The is a check box [ ] that can be selected in the format element for a field that will allow a UI converter method to be selected. However, when set up two methods in globals.js, they never show up in the format UI Converter combo box. If anyone can tell us how to set up the UI functions so they can be selected, it would be appreciated.
Chuck.Amata
 
Posts: 50
Joined: Sun Oct 04, 2009 8:02 pm
Location: USA

Re: Reading & Writing to a db with unknown date type

Postby Jan Aleman » Tue May 29, 2018 10:49 pm

That doesnt sound very good. Are you using their most recent JDBC driver? Can you get a stacktrace so we can find out what is crashing?
Jan Aleman
Servoy
Jan Aleman
Site Admin
 
Posts: 2074
Joined: Wed Apr 23, 2003 9:49 pm
Location: Planet Earth

Re: Reading & Writing to a db with unknown date type

Postby Chuck.Amata » Wed May 30, 2018 4:23 pm

5/30/18

Jan,

I would be happy to forward the log or a stack trace if you can step me through how to create the stack trace and which log you want. The internal error seems to occur any time Servoy tries to write a datatype that is not matched to the database (e.g. trying to write a string to a number field or visa versa). In our case, I think we can get everything working correctly if you can provide some insight on how the UI conversion function works. When I check the UI Conversion box under the format element for a given object, I always get an empty combo box (it my understanding that the default conversion method should show up at least but it does not). We've also tried adding global methods to the solution scope per some of the examples but these never show up in the list either. What is the trick to get the UI conversion combo box to populate so the conversion can be set up? It's also interesting that the CALENDAR element type has no problems deciphering the UNKNOWN TYPE#9 date fields in the database (the calendar is positioned to the correct date and the field shows mm/dd/yy that corresponds to the database information. The time data appears to load 00:00:000). When you change the value with the calendar and Servoy tries to write the new value back to the database, it throws data cannot be saved exception and then the internal Servoy error is thrown to the smart client or web browser and the application terminates with the internal error message left visible). All of the other data types read/write correctly (e.g. number, string, etc).

Chuck Amata
Chuck.Amata
 
Posts: 50
Joined: Sun Oct 04, 2009 8:02 pm
Location: USA

Re: Reading & Writing to a db with unknown date type

Postby Chuck.Amata » Wed May 30, 2018 4:26 pm

5/30/18

Jan,

We downloaded and installed the most recent JDBC pervasive driver and nothing has changed. We also changed from exception verification to query verification on the database parameters and this also does not change anything.

Chuck Amata
Chuck.Amata
 
Posts: 50
Joined: Sun Oct 04, 2009 8:02 pm
Location: USA

Re: Reading & Writing to a db with unknown date type

Postby Chuck.Amata » Wed May 30, 2018 5:27 pm

Jan,

Here is the stack trace that occurs when servoy tries to write the date to the database:

Exception Object: com.servoy.j2db.ApplicationException: Cannot save form data
MSG: Cannot save form data
is a ServoyException
Errorcode: 330
com.servoy.j2db.dataprocessing.DataException
SQL: update ISTOOLOG set ISTOOL_DATE=?, ISTOOL_WORKDESC=?, ISTOOL_NOTES_1=?, ISTOOL_NOTES_2=?, ISTOOL_NOTES_3=?, ISTOOL_NOTES_4=?, ISTOOL_NOTES_5=?, ISTOOL_NOTES_6=?, ISTOOL_NOTES_7=?, ISTOOL_NOTES_8=?, ISTOOL_NOTES_9=?, ISTOOL_NOTES_10=?, ISTOOL_DATES_1=? where ISTOOL_LOGNUM = ?
SQLState:
VendorErrorCode: 0

After the above error is thrown, the "internal servoy error" occurs when the user attempts to select any record other than the one that the solution attempted to write to.
Chuck.Amata
 
Posts: 50
Joined: Sun Oct 04, 2009 8:02 pm
Location: USA

Re: Reading & Writing to a db with unknown date type

Postby jcompagner » Thu Jun 28, 2018 9:19 am

this is really a problem with that driver or databaes
type "9" doesn't exist at all in the java sql types (you have 9x for some dates and a -9 for NVARCHAR) but 9 is not a valid type

So we have no idea how to handle that column, this is something the driver needs to fix.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8096
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Reading & Writing to a db with unknown date type

Postby Chuck.Amata » Thu Jun 28, 2018 3:17 pm

If this is true, why is Servoy able to properly display the date on the form and populate the calendar tool? It seems Servoy is able to properly interpret the Type#9 date field but is not able to write the data back out to the field. We were hoping to use a custom converter method to write the date back out as YYYY-MM-DD but we cannot figure out how to use the custom convertor functionality in Servoy 7/8. Can you point us to some type of an example of how to use a custom converter in Servoy 7 (all of the references we have found are pre-eclipse servoy 4/5).
Chuck.Amata
 
Posts: 50
Joined: Sun Oct 04, 2009 8:02 pm
Location: USA

Re: Reading & Writing to a db with unknown date type

Postby jcompagner » Thu Jun 28, 2018 3:31 pm

because when reading it it we do in the end just ResultSet.getObject()
that will return an object and the toString() representation is i guess what you kind of expect

But sending it back is a bit more tricky, then you need to really send the correct type to it. or some exactly string notation that that driver accepts.
And that is now the problem, we have no idea what it really wants to have back

Maybe you can try to set strings in different formats to see if it takes that (and only update that specific column)
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8096
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Reading & Writing to a db with unknown date type

Postby Chuck.Amata » Thu Jun 28, 2018 3:40 pm

Thats what we were hoping to use the custom converter method for but we can't figure out how to use it. Also, is there any way to get the column data from the database into servoy raw (e.g. without Servoy interpreting the data or converting it to a string)?
Chuck.Amata
 
Posts: 50
Joined: Sun Oct 04, 2009 8:02 pm
Location: USA

Re: Reading & Writing to a db with unknown date type

Postby jcompagner » Thu Jun 28, 2018 4:51 pm

using a converter is fine if you really need to change something that comes from the ui or from the javascript
But first just focus if you get it working by just doing:

record.datecolumn = X

If you can't get an X that works then no need to look into a converter because that in the end does exactly the same

edit: try for example where X = new Date(); so record.datecolumn = new Date()
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8096
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Reading & Writing to a db with unknown date type

Postby Chuck.Amata » Thu Jul 05, 2018 10:59 pm

We did some research on the Actian (was Pervasive) site. The DATE field format is as follows:

DATE 4 bytes
bits: 0-7 DAY
8-15 MON
16-31 YEAR

Type dateField {
char day
char month
integer year
}

Apparently, this data looks familiar enough that the standard data provider can interpret. We're still struggling on how to write this back out to the database - suggestions?
Chuck.Amata
 
Posts: 50
Joined: Sun Oct 04, 2009 8:02 pm
Location: USA

Re: Reading & Writing to a db with unknown date type

Postby jcompagner » Fri Jul 06, 2018 4:34 pm

we first really need at the java level to see exactly what comes back from the driver.
And what to set back then in this scenario.

It seems to me that they have really some custom stuff. so how to update that back into the database is something we can't tell without having a database where we can connect to to see start testing
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8096
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet


Return to Discuss possible Issues and Bugs

Who is online

Users browsing this forum: No registered users and 2 guests