Hi Larry,
The typeless nature of SQLite makes it perhaps impossible to treat the dates in
a perfect way. There is some background info in this thread
http://thread.gmane.org/gmane.comp.gis.gdal.devel/40172
Here follows a full test set-up. Start from an empty Spatialite DB and create
test data with SQL.
CREATE TABLE date_test (geometry GEOMETRY,date_field DATE);
insert into date_test values (GeomFromText('POINT (1 1)',4326),'2015-05-06');
Execute with DB Query Plugin:
select * from date_test;
Result:
-------
Error when column is created as (date_iso DATE)
java.sql.SQLException: Error parsing time stamp
at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:538)
at org.freevoice.jumpdbqueryextension.spatialite.JumpSpatialiteDbQuery.g
etAttributeValueFromResults(JumpSpatialiteDbQuery.java:444)
at org.freevoice.jumpdbqueryextension.AbstractJumpDbQuery.addFeatureToCo
llection(AbstractJumpDbQuery.java:132)
at org.freevoice.jumpdbqueryextension.spatialite.JumpSpatialiteDbQuery.g
etCollection(JumpSpatialiteDbQuery.java:408)
at org.freevoice.jumpdbqueryextension.JumpDbQueryPlugin.run(JumpDbQueryP
lugin.java:172)
at java.lang.Thread.run(Thread.java:724)
Caused by: java.text.ParseException: Unparseable date: "2015-05-06"
at java.text.DateFormat.parse(DateFormat.java:357)
at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:535)
... 5 more
Update date field to NULL with:
update date_test set date_field=NULL;
Repeat select * from date_test;
Result now:
-----------
java.lang.NullPointerException
at org.freevoice.jumpdbqueryextension.spatialite.JumpSpatialiteDbQuery.g
etAttributeValueFromResults(JumpSpatialiteDbQuery.java:444)
at org.freevoice.jumpdbqueryextension.AbstractJumpDbQuery.addFeatureToCo
llection(AbstractJumpDbQuery.java:132)
at org.freevoice.jumpdbqueryextension.spatialite.JumpSpatialiteDbQuery.g
etCollection(JumpSpatialiteDbQuery.java:408)
at org.freevoice.jumpdbqueryextension.JumpDbQueryPlugin.run(JumpDbQueryP
lugin.java:172)
at java.lang.Thread.run(Thread.java:724)
Activating Select Features Tool
Make a little trick in the database:
PRAGMA writable_schema = 1;
update sqlite_master set sql='CREATE TABLE date_test (geometry GEOMETRY,
date_field TEXT)'
where name='date_test';
vacuum;
Now the date_field column appears as TEXT type in the SQLite schema and we can
run
new tests:
select * from date_test;
update date_test set date_field='2015-05-06'
select * from date_test;
Result:
-------
Queries work. The date_field in OpenJUMP is of type String.
I tested that if a column is defined as DATE or DATETIME in the SQLite schema
then it is supposed to contain time values as the Unix time. What Even
implemented into GDAL is that if data in the column are numbers then they are
interpreted to be Julian days and if they are valid ISO time strings they are
read as such. Otherwise NULL is returned. This may be too complicated for
OpenJUMP and DB Query. I would be ready to accept a simple schema override: if
schema is announcing field as DATE or DATETIME it is changed automatically and
always into TEXT.
Regards,
-Jukka-
Larry Reeder wrote:
Jukka, can you send me the exception stack you are seeing, or even better, a
small example SpatialLite DB file that contains the data causing the error,
along with an example of the query you are using that causes the error? The
SQLite and/or SpatiaLite JDBC drivers are responsible for providing a common
interface to the database. In this case they should be converting the date
string to a Java Timestamp object, but may be failing to do so.
I'm travelling this week, but could take a look next week.
Regards........... lreeder
On Wed, Apr 22, 2015 at 3:46 PM, Rahkonen Jukka (MML)
<jukka.rahko...@maanmittauslaitos.fi<mailto:jukka.rahko...@maanmittauslaitos.fi>>
wrote:
Hi,
The SQLite/Spatialite/GeoPackage driver of the DB Query Plugin is reading data
from a column that is recognized as a DATE with this code that comes from
http://sourceforge.net/p/jumpdbqplugin/code/ci/default/tree/src/main/java/org/freevoice/jumpdbqueryextension/spatialite/JumpSpatialiteDbQuery.java
(line 252)
else if (AttributeType.DATE.equals(attributeType))
{
returnObject = new Date(results.getTimestamp(dbColumnIndex).getTime());
}
I found that the code gives an error if dates in the database are encoded into
ISO text format, like "2015-04-23". After some experiments I started to think
that results.getTimestamp(dbColumnIndex).getTime() awaits a number, perhaps an
integer number, maybe UNIX seconds.
It would be better to consider that all times in those databases (DATE, TIME,
TIMESTAMP, DATETIME) are ISO strings. If date is not a valid ISO data string
they it could be skipped and converted into NULL for OpenJUMP. That would be
more user friendly than to stop parsing and quit.
More details about times in SQLite based databases in gdal-dev thread
http://thread.gmane.org/gmane.comp.gis.gdal.devel/40172
-Jukka Rahkonen-
------------------------------------------------------------------------------
BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
Develop your own process in accordance with the BPMN 2 standard
Learn Process modeling best practices with Bonita BPM through live exercises
http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual- event?utm_
source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
_______________________________________________
Jump-pilot-devel mailing list
Jump-pilot-devel@lists.sourceforge.net<mailto:Jump-pilot-devel@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
_______________________________________________
Jump-pilot-devel mailing list
Jump-pilot-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
