Hello Lars
I ran into the same problem some time ago, but not only in Oracle but MySQL
too. However I needed to perform date (and time) comparisons.
Sometimes D2R doesn't detects columns datatype properly, so you have to
declare it explicitely.
What may be messing up things is that you declared the nav:updated property
as dateTime, and maybe you need to declare this column as such, in the
d2rq:database section.
Add a d2rq:timestampColumn "PERSON.MOD_DATO" there and see what happens.
good luck,
--
diogo patrão
On Wed, May 2, 2012 at 8:23 AM, Lars Marius Garshol
<[email protected]>wrote:
>
> I've set up D2RQ against an Oracle database, and for some queries I get
> good results, but the query I actually need takes about 8 minutes and then
> produces nothing. Investigation has lead me to believe that the problem may
> be how D2RQ treats Oracle DATE values (which really are date+time).
>
> Let's go through my reasoning. This query works fine, yielding instant
> results:
> SELECT * WHERE { ?s a nav:Person } LIMIT 10
>
> This query takes 8 minutes to do nothing:
> SELECT * WHERE { ?s a nav:Person; nav:updated ?t } LIMIT 10
>
> Checking the database I see that there are 2.8 million rows in the PERSON
> table, and every single one has a value in the MOD_DATO column.
>
> Looking at the console we find:
>
> 11:49:57 INFO SPARQL :: Query: SELECT * WHERE { ?s a <
> http://psi.
> nav.no/2012/mod/Person>; <http://psi.nav.no/2012/mod/updated> ?t } limit
> 10
> 11:49:57 DEBUG QueryExecutionIterator :: SELECT DISTINCT
> "T1_PERSON"."PERSON_ID"
> , "T2_PERSON"."MOD_DATO" FROM "PERSON" "T1_PERSON", "PERSON" "T2_PERSON"
> WHERE "
> T1_PERSON"."PERSON_ID" = "T2_PERSON"."PERSON_ID"
>
> So this is not mapped to optimal SQL, but it looks correct to me.
>
> I tried simplifying the SPARQL query to just
> SELECT * WHERE { ?s nav:updated ?t } LIMIT 10
>
> This time we get:
>
> 11:12:17 INFO SPARQL :: Query: SELECT * WHERE { ?s <
> http://psi.na
> v.no/2012/mod/updated> ?t } limit 10
> 11:12:17 DEBUG QueryExecutionIterator :: SELECT DISTINCT
> "PERSON"."MOD_DATO", "P
> ERSON"."PERSON_ID" FROM "PERSON"
> 11:20:06 INFO SPARQL :: OK/select: SELECT * WHERE { ?s <
> http://ps
> i.nav.no/2012/mod/updated> ?t } limit 10
>
> Again the SQL looks slow but right, but I get no results. Running the SQL
> query directly gives me results that look perfectly fine.
>
> The relevant part of my mapping file is:
>
> map:Person a d2rq:ClassMap;
> d2rq:dataStorage _:Database1;
> d2rq:class nav:Person;
> d2rq:uriPattern "
> http://psi.nav.no/2012/mod/arena/person/@@PERSON.PERSON_ID@@";
> .
> _:Oppdatert a d2rq:PropertyBridge;
> d2rq:belongsToClassMap map:Person;
> d2rq:property nav:updated;
> d2rq:column "PERSON.MOD_DATO";
> d2rq:datatype xsd:dateTime;
> .
>
> There's also a column ETTERNAVN mapped to foaf:familyName (xsd:string) in
> the same way. If I query for that with
> SELECT * WHERE { ?s a nav:Person>; foaf:familyName ?f } limit 10
>
> I get correct results instantly.
>
>
> This led me to suspect that the declaration of MOD_DATO as xsd:dateTime
> might be the problem. I changed the mapping to xsd:string and tried
> querying for nav:updated/MOD_DATO again, and now I get instant results.
> Sadly, however, they are just dates represented as strings, and I really do
> need the full date and time.
>
> Investigation of the schema shows that MOD_DATO is declared as DATE, but
> SQL queries on it return full date/time values like 2010-05-28 01:59:04.0.
> Further investigation shows that Oracle DATE values actually do include the
> time:
> http://docs.oracle.com/cd/B19306_01/server.102/b14220/datatype.htm#i1847
>
> So... is this a bug in D2RQ, in the sense that you've mapped the Oracle
> DATE type to xsd:date when it should in fact map to xsd:dateTime?
>
> --Lars M.
> http://www.garshol.priv.no/tmphoto/
> http://www.garshol.priv.no/blog/
>
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> d2rq-map-devel mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel
>
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
d2rq-map-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel
