Unparseable date issue when doing virtual import

[nata...@gmail.com]

Hi,

I am testing R2RML mapping functionality on MS SQL Server 12. One of the columns I am mapping is of type 'datetime' (length 8, precision 23, scale 3). I have DB viewers using MS SQL Server Management Studio and Java Client application SQuirreL. Both render me the data as "2012-10-24 06:50:00.000" (on my windows laptop).

But when running a virtual import (on server) I get this error message on Windows Server 2012

  Unparseable date: "Oct 24 2012  6:50AM"

Any clues as to why it works differently on Stardog.

Thx & regards

Natan

[Zachary Whitley]

On Nov 26, 2015, at 5:59 AM, nata...@gmail.com wrote:

Hi,

I am testing R2RML mapping functionality on MS SQL Server 12. One of the columns I am mapping is of type 'datetime' (length 8, precision 23, scale 3).

This is a little confusing. I wasn't aware that datetime took a length, precision or scale. I believe that datetime2 data type takes a single parameter of scale. Are you sure it's not datetime2 or some other type?

I have DB viewers using MS SQL Server Management Studio and Java Client application SQuirreL. Both render me the data as "2012-10-24 06:50:00.000" (on my windows laptop).

Not sure what you mean by "render". What exact query are you sending that you say works?

But when running a virtual import (on server) I get this error message on Windows Server 2012

  Unparseable date: "Oct 24 2012  6:50AM"

Is there anything else in the logs? Can you include them or the query that's failing? Is there anything in the Stardog logs and if there is can you send it?

Can you send the mappings and db schema you're using?

Any clues as to why it works differently on Stardog.

You might want to try a convert() on the datetime field in your mapping query to convert it to an ANSI SQL datetime format and see how that works. 

Thx & regards

Natan

--
-- --
You received this message because you are subscribed to the C&P "Stardog" group.
To post to this group, send email to sta...@clarkparsia.com
To unsubscribe from this group, send email to
stardog+u...@clarkparsia.com
For more options, visit this group at
http://groups.google.com/a/clarkparsia.com/group/stardog?hl=en
---
You received this message because you are subscribed to the Google Groups "Stardog" group.
To unsubscribe from this group and stop receiving emails from it, send an email to stardog+u...@clarkparsia.com.

[nata...@gmail.com]

On Friday, November 27, 2015 at 1:21:30 AM UTC+1, Zachary Whitley wrote:

On Nov 26, 2015, at 5:59 AM, nata...@gmail.com wrote:

Hi,

I am testing R2RML mapping functionality on MS SQL Server 12. One of the columns I am mapping is of type 'datetime' (length 8, precision 23, scale 3).

This is a little confusing. I wasn't aware that datetime took a length, precision or scale. I believe that datetime2 data type takes a single parameter of scale. Are you sure it's not datetime2 or some other type?

I was incorrect. The field is of type "datetimeoffset" cfr https://msdn.microsoft.com/en-us/library/bb630289.aspx. 

Note: I have zero control over column types in the database.

 

I have DB viewers using MS SQL Server Management Studio and Java Client application SQuirreL. Both render me the data as "2012-10-24 06:50:00.000" (on my windows laptop).

Not sure what you mean by "render". What exact query are you sending that you say works?

They are just viewers to view the database content. So you do not really see what queries are executed but they seem pretty standard (like select all columns and display them in a grid).

But for all intents and purposes the output is quite ok compared parsing of "Oct 24 2012  6:50AM".

 

But when running a virtual import (on server) I get this error message on Windows Server 2012

  Unparseable date: "Oct 24 2012  6:50AM"

Is there anything else in the logs? Can you include them or the query that's failing? Is there anything in the Stardog logs and if there is can you send it?

Can you send the mappings and db schema you're using?

Yes. Will do as soon as I am at the client again (next week).

 

Any clues as to why it works differently on Stardog.

You might want to try a convert() on the datetime field in your mapping query to convert it to an ANSI SQL datetime format and see how that works. 

I know this is an option and I will probably use it. But I guess it cannot hurt to support 'datetimeoffset'.

N

[Zachary Whitley]

On Nov 27, 2015, at 5:05 AM, nata...@gmail.com wrote:

On Friday, November 27, 2015 at 1:21:30 AM UTC+1, Zachary Whitley wrote:

On Nov 26, 2015, at 5:59 AM, nata...@gmail.com wrote:

Hi,

I am testing R2RML mapping functionality on MS SQL Server 12. One of the columns I am mapping is of type 'datetime' (length 8, precision 23, scale 3).

This is a little confusing. I wasn't aware that datetime took a length, precision or scale. I believe that datetime2 data type takes a single parameter of scale. Are you sure it's not datetime2 or some other type?

I was incorrect. The field is of type "datetimeoffset" cfr https://msdn.microsoft.com/en-us/library/bb630289.aspx. 

Note: I have zero control over column types in the database.

 

I have DB viewers using MS SQL Server Management Studio and Java Client application SQuirreL. Both render me the data as "2012-10-24 06:50:00.000" (on my windows laptop).

Not sure what you mean by "render". What exact query are you sending that you say works?

They are just viewers to view the database content. So you do not really see what queries are executed but they seem pretty standard (like select all columns and display them in a grid).

But for all intents and purposes the output is quite ok compared parsing of "Oct 24 2012  6:50AM".

 

But when running a virtual import (on server) I get this error message on Windows Server 2012

  Unparseable date: "Oct 24 2012  6:50AM"

Is there anything else in the logs? Can you include them or the query that's failing? Is there anything in the Stardog logs and if there is can you send it?

Can you send the mappings and db schema you're using?

Yes. Will do as soon as I am at the client again (next week).

You might also want to check what jdbc driver your using. Microsoft SQLServerDriver or the jTDS driver. Try the MS driver if you're using the jTDS. 

[nata...@gmail.com]

Hi,

I am not using jTDS but SQLServerDriver (sqljdbc41.jar).

Pls note field type datetimeoffset.

Only things I find in logs look like this

ERROR 2015-11-26 11:30:11,081 [SPEC-Server-1-2] com.complexible.stardog.db.DatabaseConnectionImpl:add(611): There was an error adding data
com.complexible.stardog.plan.eval.operator.OperatorException: java.text.ParseException: Unparseable date: "Oct 24 2012  6:50AM"
 at com.complexible.stardog.virtual.VirtualGraphService$ResultsToSolutionIterator.computeNext(VirtualGraphService.java:155) ~[stardog-virtual-core-4.0.jar:?]
 at com.complexible.stardog.virtual.VirtualGraphService$ResultsToSolutionIterator.computeNext(VirtualGraphService.java:78) ~[stardog-virtual-core-4.0.jar:?]
 at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:143) ~[guava-18.0.jar:?]
 at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:138) ~[guava-18.0.jar:?]
 at com.complexible.stardog.plan.eval.operator.impl.ServiceOperatorImpl.computeNext(ServiceOperatorImpl.java:116) ~[stardog-4.0.jar:?]
 at com.complexible.stardog.plan.eval.operator.impl.ServiceOperatorImpl.computeNext(ServiceOperatorImpl.java:37) ~[stardog-4.0.jar:?]
 at com.complexible.common.collect.AbstractSkippingIterator.tryToComputeNext(AbstractSkippingIterator.java:143) ~[stardog-utils-common-4.0.jar:?]
 at com.complexible.common.collect.AbstractSkippingIterator.hasNext(AbstractSkippingIterator.java:130) ~[stardog-utils-common-4.0.jar:?]
 at com.complexible.stardog.plan.eval.operator.impl.BindOp.computeNext(BindOp.java:108) ~[stardog-4.0.jar:?]
 at com.complexible.stardog.plan.eval.operator.impl.BindOp.computeNext(BindOp.java:34) ~[stardog-4.0.jar:?]
 at com.complexible.common.collect.AbstractSkippingIterator.tryToComputeNext(AbstractSkippingIterator.java:143) ~[stardog-utils-common-4.0.jar:?]
 at com.complexible.common.collect.AbstractSkippingIterator.hasNext(AbstractSkippingIterator.java:130) ~[stardog-utils-common-4.0.jar:?]
 at com.complexible.stardog.plan.eval.operator.impl.SingleProjectionOp.computeNext(SingleProjectionOp.java:107) ~[stardog-4.0.jar:?]
 at com.complexible.stardog.plan.eval.operator.impl.SingleProjectionOp.computeNext(SingleProjectionOp.java:36) ~[stardog-4.0.jar:?]
 at com.complexible.common.collect.AbstractSkippingIterator.tryToComputeNext(AbstractSkippingIterator.java:143) ~[stardog-utils-common-4.0.jar:?]
 at com.complexible.common.collect.AbstractSkippingIterator.hasNext(AbstractSkippingIterator.java:130) ~[stardog-utils-common-

N

[Evren Sirin]

SQL server datetimeoffset does not map to a standard JDBC type which
might be causing the problem. Can you send us the stardog.log file?

Thanks,
Evren

[1] https://msdn.microsoft.com/en-us/library/ms378878(v=sql.110).aspx

[nata...@gmail.com]

For now I am using datetime and it works. I will keep you posted (because I am quite sure we need a solution for this one).

N

[nata...@gmail.com]

A bit more testing here.

First of: MS SQL type of column is DATETIME (!) not TIMESTAMP. So that might explain a lot.

In my query I do no special formatting

  IncidentDate,

In R2RML I do

 rr:predicateObjectMap [
  rr:predicate cim:Document.createdDateTime;
  rr:objectMap [ rr:column "IncidentDate" ]
 ];

and result is

 app:6119d7a1-11e6-40e6-9f5a-7ae6ad2ae1ed | cim:Document.createdDateTime | "May 27 2011  7:32AM"

I checked and datatype is xsd:string. I also checked the specifications and SQL type TIMESTAMP is converted to xsd:dateTime.

There is no mention of DATETIME there (nor in Stardog documentation to support the type for SQL Server).

If I change

  rr:objectMap [ rr:column "IncidentDate"; rr:datatype xsd:dateTime ]

I get

Unparseable date: "Mar 15 2014  1:27AM"

Is it because I use DATETIME and not TIMESTAMP? Is there an easy workaround? 

thx, N

[nata...@gmail.com]

Allright, I tested a lot more cases now and I have a feeling something is wrong in Stardog code.

Why does it always seem to parse a string?

 

Because of this I tested with strings now.

Explicitly setting a string in xsd:dateTime format

"2009-11-19T00:00:00"

"2012-08-12T05:40:21Z"

"2009-11-19T00:00:00+06:00"

 

all fail, but

"2011-02-02 12:55:38"

 

works!

 

So on MS SQL you can do

CONVERT(NVARCHAR(MAX), IncidentDate, 120)

to get it working but it feels like something is off here.

Interestingly

cast('2011-02-02 12:55:38' as datetime)

also works, but

cast(CONVERT(NVARCHAR(MAX), IncidentDate, 120) as datetime)

does not as it seems to get to "Mar 15 2014  1:27AM" format again.

(all tested on Windows Server 2012)

[nata...@gmail.com]

And one more (on SQL Server) most of datetime types seems to work if you do something like

  SUBSTRING(CONVERT(NVARCHAR(MAX), Incident.CreatedOn, 120), 0, 20) as IncidentDate

[charbe...@gmail.com]

Hello,

Can you please share your properties file

I Can't figure out how to connect from Stardog to a MS SQL

jdbc.url=jdbc\:sqlserver\://mySERVER.database.windows.net/mydb

jdbc.username=admin@myServer

jdbc.password=mypassword

jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

I keep getting the following:

The TCP/IP connection to the host mySERVER.database.windows.net/myDB, port 1433 has failed. Error: Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

I can connect to the db without Stardog from java from the same machine.

Thank you.

[Jess Balint]

You should use a semi-colon instead of a slash preceding the database name. Please check the documentation for the SQL Server JDBC driver:

https://msdn.microsoft.com/en-us/library/mt720656(v=sql.110).aspx

What connection string are you using to connect outside of Stardog?

Best,

Jess

stardog+unsubscribe@clarkparsia.com

[Charbel Kaed]

Thank you Jess I got it working by adding the other parameters with semi-colon,

jdbc.url=jdbc\:sqlserver\://sqlSERVER.database.windows.net;database=sqlDBencrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database