Pieter Stroobants
Jul 28, 2016, 5:28:18 AM7/28/16
to swi-p...@googlegroups.com
When working with SWI Prolog's ODBC connection, I encountered an error which seems to be caused by Prolog itself. Consider
odbc_connect('M', _,[user(bla), password(bla), alias(m), open(once)]),
findall(ca(Id),odbc_query(m, 'select DA_BEGIN from ABC', row(Id)), Lemmas),
odbc_disconnect(maatregelen).This example causes an ODBC error S1003 on DB2 version 10.5.3 if DA_BEGIN is of SQL datatype date. Even if you add an explicit convertion argument to odbc_query like [types([date])] you
receive the same error.
However if you convert the date to a timestamp like:
odbc_connect('M', _,[user(bla), password(bla), alias(m), open(once)]),
findall(ca(Id),odbc_query(m, 'select DA_BEGIN from ABC', row(Id), [types([timestamp])]), Lemmas),
odbc_disconnect(maatregelen). This code seems to work perfectly. My question now is: is this error related to the prolog interface or is it related to the ODBC driver.
To me it seems that there is an error in the prolog convertion layer between SQL date and prolog date. For your information, I am using
To me it seems that there is an error in the prolog convertion layer between SQL date and prolog date. For your information, I am using
SWI prolog version 7.2.3 (32-bit).
Fernando Sáenz Pérez
Jul 28, 2016, 5:39:26 AM7/28/16
to Pieter Stroobants, SWI-Prolog
Hi,
I get the same behaviour in Windows 10 64bit, DB2 10.5, ODBC 10.05.500.107 (IBM DB2 ODBC DRIVER), and SWI-Prolog 7.2.3 64bit.
This seems to be an issue in the SWI-Prolog ODBC interface because SICStus handles well the DB2 date data type with the same OS ODBC driver.
All the best,
Fernando
--
You received this message because you are subscribed to the Google Groups "SWI-Prolog" group.
To unsubscribe from this group and stop receiving emails from it, send an email to swi-prolog+...@googlegroups.com.
Visit this group at https://groups.google.com/group/swi-prolog.
For more options, visit https://groups.google.com/d/optout.
Jan Wielemaker
Jul 28, 2016, 6:10:37 AM7/28/16
to Fernando Sáenz Pérez, Pieter Stroobants, SWI-Prolog
On 28/07/16 11:39, Fernando Sáenz Pérez wrote:
> Hi,
> I get the same behaviour in Windows 10 64bit, DB2 10.5, ODBC
> 10.05.500.107 (IBM DB2 ODBC DRIVER), and SWI-Prolog 7.2.3 64bit.
> This seems to be an issue in the SWI-Prolog ODBC interface because
> SICStus handles well the DB2 date data type with the same OS ODBC driver.
> All the best,
> Fernando
As you got the test case, can you include the complete code to build
> Hi,
> I get the same behaviour in Windows 10 64bit, DB2 10.5, ODBC
> 10.05.500.107 (IBM DB2 ODBC DRIVER), and SWI-Prolog 7.2.3 64bit.
> This seems to be an issue in the SWI-Prolog ODBC interface because
> SICStus handles well the DB2 date data type with the same OS ODBC driver.
> All the best,
> Fernando
and query the DB? That saves time and avoids confusion.
Thanks --- Jan
> 2016-07-28 11:28 GMT+02:00 Pieter Stroobants
>
> When working with SWI Prolog's ODBC connection, I encountered an
> error which seems to be caused by Prolog itself. Consider
>
> |
> odbc_connect('M',_,[user(bla),password(bla),alias(m),open(once)]),
> When working with SWI Prolog's ODBC connection, I encountered an
> error which seems to be caused by Prolog itself. Consider
>
> |
> findall(ca(Id),odbc_query(m,'select DA_BEGIN from ABC',row(Id)),Lemmas),
> odbc_disconnect(maatregelen).
> |
>
> This example causes an ODBC error S1003 on DB2 version 10.5.3 if
> DA_BEGIN is of SQL datatype date. Even if you add an explicit
> convertion argument to odbc_query like [types([date])] you
> receive the same error.
>
> However if you convert the date to a timestamp like:
>
> |
> odbc_connect('M',_,[user(bla),password(bla),alias(m),open(once)]),
> odbc_disconnect(maatregelen).
> |
>
> This example causes an ODBC error S1003 on DB2 version 10.5.3 if
> DA_BEGIN is of SQL datatype date. Even if you add an explicit
> convertion argument to odbc_query like [types([date])] you
> receive the same error.
>
> However if you convert the date to a timestamp like:
>
> |
> findall(ca(Id),odbc_query(m,'select DA_BEGIN from
> ABC',row(Id),[types([timestamp])]),Lemmas),
>
> odbc_disconnect(maatregelen).
> |
>
> This code seems to work perfectly. My question now is: is this error
> related to the prolog interface or is it related to the ODBC driver.
> To me it seems that there is an error in the prolog convertion layer
> between SQL date and prolog date.
>
> --
> You received this message because you are subscribed to the Google
> Groups "SWI-Prolog" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to swi-prolog+...@googlegroups.com
> <mailto:swi-prolog+...@googlegroups.com>.
> odbc_disconnect(maatregelen).
> |
>
> This code seems to work perfectly. My question now is: is this error
> related to the prolog interface or is it related to the ODBC driver.
> To me it seems that there is an error in the prolog convertion layer
> between SQL date and prolog date.
>
> --
> You received this message because you are subscribed to the Google
> Groups "SWI-Prolog" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to swi-prolog+...@googlegroups.com
> Visit this group at https://groups.google.com/group/swi-prolog.
> For more options, visit https://groups.google.com/d/optout.
>
>
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "SWI-Prolog" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to swi-prolog+...@googlegroups.com
> <mailto:swi-prolog+...@googlegroups.com>.
> You received this message because you are subscribed to the Google
> Groups "SWI-Prolog" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to swi-prolog+...@googlegroups.com
Fernando Sáenz Pérez
Jul 28, 2016, 6:28:07 AM7/28/16
to Jan Wielemaker, Pieter Stroobants, SWI-Prolog
Hi,
I used DES to test this quickly. Now I have prepared a test file for highlighting the issue, which is attached here, where the call test(+Connection,-Result) reveals it. The very same problem occurs in DB2, PostgreSQL and MySQL.
All the best,
Fernando
Jan Wielemaker
Jul 29, 2016, 4:46:37 AM7/29/16
to Fernando Sáenz Pérez, Pieter Stroobants, SWI-Prolog
On 07/28/2016 12:28 PM, Fernando Sáenz Pérez wrote:
> Hi,
> I used DES <http://des.sourceforge.net> to test this quickly. Now I have
> Hi,
> prepared a test file for highlighting the issue, which is attached here,
> where the call test(+Connection,-Result) reveals it. The very same
> problem occurs in DB2, PostgreSQL and MySQL.
Thanks ...
> where the call test(+Connection,-Result) reveals it. The very same
> problem occurs in DB2, PostgreSQL and MySQL.
Hmmm. Just tried on Ubuntu 16.04 (Xenial) using PostgreSQL. Runs fine.
Unfortunately Ubuntu 16.04 ships with MySQL 5.7 for which the ODBC
driver is missing :( Also tried the Windows version of Prolog, still
talking to the same Linux PostgreSQL server. Works fine too.
Is there someone around with a setup that reproduces this and allows for
recompiling the ODBC interface? I've now spent several hours setting up
(partially in vain) different databases and configuring ODBC drivers ...
Cheers --- Jan
> <mailto:J.Wiel...@vu.nl>>:
>
> On 28/07/16 11:39, Fernando Sáenz Pérez wrote:
> > Hi,
> > I get the same behaviour in Windows 10 64bit, DB2 10.5, ODBC
> > 10.05.500.107 (IBM DB2 ODBC DRIVER), and SWI-Prolog 7.2.3 64bit.
> > This seems to be an issue in the SWI-Prolog ODBC interface because
> > SICStus handles well the DB2 date data type with the same OS ODBC driver.
> > All the best,
> > Fernando
>
> As you got the test case, can you include the complete code to build
> and query the DB? That saves time and avoids confusion.
>
> Thanks --- Jan
>
>
> > 2016-07-28 11:28 GMT+02:00 Pieter Stroobants
> > <pieter.s...@gmail.com <mailto:pieter.s...@gmail.com>
> <mailto:pieter.s...@gmail.com
> On 28/07/16 11:39, Fernando Sáenz Pérez wrote:
> > Hi,
> > I get the same behaviour in Windows 10 64bit, DB2 10.5, ODBC
> > 10.05.500.107 (IBM DB2 ODBC DRIVER), and SWI-Prolog 7.2.3 64bit.
> > This seems to be an issue in the SWI-Prolog ODBC interface because
> > SICStus handles well the DB2 date data type with the same OS ODBC driver.
> > All the best,
> > Fernando
>
> As you got the test case, can you include the complete code to build
> and query the DB? That saves time and avoids confusion.
>
> Thanks --- Jan
>
>
> > 2016-07-28 11:28 GMT+02:00 Pieter Stroobants
> > <pieter.s...@gmail.com <mailto:pieter.s...@gmail.com>
> <mailto:pieter.s...@gmail.com>>>:
> >
> > When working with SWI Prolog's ODBC connection, I encountered an
> > error which seems to be caused by Prolog itself. Consider
> >
> > |
> > odbc_connect('M',_,[user(bla),password(bla),alias(m),open(once)]),
> > findall(ca(Id),odbc_query(m,'select DA_BEGIN from ABC',row(Id)),Lemmas),
> > odbc_disconnect(maatregelen).
> > |
> >
> > This example causes an ODBC error S1003 on DB2 version 10.5.3 if
> > DA_BEGIN is of SQL datatype date. Even if you add an explicit
> > convertion argument to odbc_query like [types([date])] you
> > receive the same error.
> >
> > However if you convert the date to a timestamp like:
> >
> > |
> > odbc_connect('M',_,[user(bla),password(bla),alias(m),open(once)]),
> > findall(ca(Id),odbc_query(m,'select DA_BEGIN from
> > ABC',row(Id),[types([timestamp])]),Lemmas),
> >
> > odbc_disconnect(maatregelen).
> > |
> >
> > This code seems to work perfectly. My question now is: is this error
> > related to the prolog interface or is it related to the ODBC driver.
> > To me it seems that there is an error in the prolog convertion layer
> > between SQL date and prolog date.
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "SWI-Prolog" group.
> > To unsubscribe from this group and stop receiving emails from it,
> > send an email to swi-prolog+...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>
> >
> > When working with SWI Prolog's ODBC connection, I encountered an
> > error which seems to be caused by Prolog itself. Consider
> >
> > |
> > odbc_connect('M',_,[user(bla),password(bla),alias(m),open(once)]),
> > findall(ca(Id),odbc_query(m,'select DA_BEGIN from ABC',row(Id)),Lemmas),
> > odbc_disconnect(maatregelen).
> > |
> >
> > This example causes an ODBC error S1003 on DB2 version 10.5.3 if
> > DA_BEGIN is of SQL datatype date. Even if you add an explicit
> > convertion argument to odbc_query like [types([date])] you
> > receive the same error.
> >
> > However if you convert the date to a timestamp like:
> >
> > |
> > odbc_connect('M',_,[user(bla),password(bla),alias(m),open(once)]),
> > findall(ca(Id),odbc_query(m,'select DA_BEGIN from
> > ABC',row(Id),[types([timestamp])]),Lemmas),
> >
> > odbc_disconnect(maatregelen).
> > |
> >
> > This code seems to work perfectly. My question now is: is this error
> > related to the prolog interface or is it related to the ODBC driver.
> > To me it seems that there is an error in the prolog convertion layer
> > between SQL date and prolog date.
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "SWI-Prolog" group.
> > To unsubscribe from this group and stop receiving emails from it,
> > send an email to swi-prolog+...@googlegroups.com
> > <mailto:swi-prolog+...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>>.
> > Visit this group at https://groups.google.com/group/swi-prolog.
> > For more options, visit https://groups.google.com/d/optout.
> >
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "SWI-Prolog" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to swi-prolog+...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>
> > For more options, visit https://groups.google.com/d/optout.
> >
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "SWI-Prolog" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to swi-prolog+...@googlegroups.com
> > <mailto:swi-prolog+...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>>.
Fernando Sáenz Pérez
Jul 29, 2016, 6:00:07 AM7/29/16
to Jan Wielemaker, Pieter Stroobants, SWI-Prolog
Hi Jan,
Did you try to install PostgreSQL in Windows? This should be quite easy. The only thing to note is that, once installed, it is needed to start Application Stack Builder (in the Start menu of Windows, under the PostgreSQL application) to select the ODBC driver to ensure it is installed. After, configuring the ODBC driver is also easy (you have to select the 64bit or 32bit ODBC manager in Windows depending on the SWI-Prolog architecture). In my case, I used the default data for this which is in the screenshot at the end of this message.
I still experience the same issue in Windows, and even with the time data type. But, curiously, this does not happen with timestamp, which is a data type that comprises both date and time. An excerpt for this is also attached (with SICStus there is no problem in retrieving these data) (again, in DES since it is quick for me to test this).
All the best,
Fernando
DES> create table t(a date)
DES> insert into t values (date '2010-01-01')
Info: 1 tuple inserted.
DES> select * from t
Error: ODBC State S1003 Code 0: [Microsoft][Administrador de controladores ODBC] Tipo de programa fuera de intervalo
DES> create table s(a time)
DES> insert into s values (time '10:10:10')
Info: 1 tuple inserted.
DES> select * from s
Error: ODBC State S1003 Code 0: [Microsoft][Administrador de controladores ODBC] Tipo de programa fuera de intervalo
DES> create table r(a timestamp)
DES> insert into r values (timestamp '2010-01-01 10:10:10')
Info: 1 tuple inserted.
DES> select * from r
answer(a:timestamp(16)) ->
{
answer(timestamp(2010,1,1,10,10,10,0))
}
Info: 1 tuple computed.
Pieter Stroobants
Jul 29, 2016, 6:41:29 AM7/29/16
to SWI-Prolog, J.Wiel...@vu.nl, pieter.s...@gmail.com
Hi,
Op vrijdag 29 juli 2016 12:00:07 UTC+2 schreef Fernando Sáenz Pérez:
Indeed. Same issues here. Like Fernando I would like to ask to check the SQL Time datatype as well.
The same type of convertion occurs here as well, unfortunately I did not test this myself unlike with the
date datatype. It is strange that these issues do not popup when working with timestamps.
Pieter
Op vrijdag 29 juli 2016 12:00:07 UTC+2 schreef Fernando Sáenz Pérez:
> <mailto:swi-prolog+unsub...@googlegroups.com
<mailto:swi-prolog%2Bunsu...@googlegroups.com>>.
> Visit this group at https://groups.google.com/group/swi-prolog.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "SWI-Prolog" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to swi-prolog+...@googlegroups.com
<mailto:swi-prolog%2Bunsu...@googlegroups.com>
> <mailto:swi-prolog+unsub...@googlegroups.com
Jan Wielemaker
Jul 29, 2016, 7:02:15 AM7/29/16
to Fernando Sáenz Pérez, Pieter Stroobants, SWI-Prolog
On 07/29/2016 12:00 PM, Fernando Sáenz Pérez wrote:
> Hi Jan,
> Did you try to install PostgreSQL in Windows? This should be quite easy.
If you have a proper windows system with enough disk space, may be ...
> Hi Jan,
> Did you try to install PostgreSQL in Windows? This should be quite easy.
I forwarded this to some people whom I think are better equipped to
solve this. Fingers crossed ...
Cheers --- Jan
>
> On 07/28/2016 12:28 PM, Fernando Sáenz Pérez wrote:
>
> Hi,
> I used DES <http://des.sourceforge.net> to test this quickly.
> Now I have
> prepared a test file for highlighting the issue, which is
> attached here,
> where the call test(+Connection,-Result) reveals it. The very same
> problem occurs in DB2, PostgreSQL and MySQL.
>
>
> Thanks ...
>
> Hmmm. Just tried on Ubuntu 16.04 (Xenial) using PostgreSQL. Runs fine.
> Unfortunately Ubuntu 16.04 ships with MySQL 5.7 for which the ODBC
> driver is missing :( Also tried the Windows version of Prolog, still
> talking to the same Linux PostgreSQL server. Works fine too.
>
> Is there someone around with a setup that reproduces this and allows for
> recompiling the ODBC interface? I've now spent several hours setting up
> (partially in vain) different databases and configuring ODBC drivers ...
>
> Cheers --- Jan
>
>
> All the best,
> Fernando
>
> 2016-07-28 12:10 GMT+02:00 Jan Wielemaker <J.Wiel...@vu.nl
> <mailto:J.Wiel...@vu.nl>
> <mailto:J.Wiel...@vu.nl <mailto:J.Wiel...@vu.nl>>>:
> On 07/28/2016 12:28 PM, Fernando Sáenz Pérez wrote:
>
> Hi,
> I used DES <http://des.sourceforge.net> to test this quickly.
> Now I have
> prepared a test file for highlighting the issue, which is
> attached here,
> where the call test(+Connection,-Result) reveals it. The very same
> problem occurs in DB2, PostgreSQL and MySQL.
>
>
> Thanks ...
>
> Hmmm. Just tried on Ubuntu 16.04 (Xenial) using PostgreSQL. Runs fine.
> Unfortunately Ubuntu 16.04 ships with MySQL 5.7 for which the ODBC
> driver is missing :( Also tried the Windows version of Prolog, still
> talking to the same Linux PostgreSQL server. Works fine too.
>
> Is there someone around with a setup that reproduces this and allows for
> recompiling the ODBC interface? I've now spent several hours setting up
> (partially in vain) different databases and configuring ODBC drivers ...
>
> Cheers --- Jan
>
>
> All the best,
> Fernando
>
> 2016-07-28 12:10 GMT+02:00 Jan Wielemaker <J.Wiel...@vu.nl
> <mailto:J.Wiel...@vu.nl>
> <mailto:swi-prolog%252Buns...@googlegroups.com>>
> > <mailto:swi-prolog+...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>
> <mailto:swi-prolog%2Bunsu...@googlegroups.com
> <mailto:swi-prolog%252Buns...@googlegroups.com>>>.
> > Visit this group at
> https://groups.google.com/group/swi-prolog.
> > For more options, visit
> https://groups.google.com/d/optout.
> >
> >
> > --
> > You received this message because you are subscribed to
> the Google
> > Groups "SWI-Prolog" group.
> > To unsubscribe from this group and stop receiving emails
> from it, send
> > an email to swi-prolog+...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>
> <mailto:swi-prolog%2Bunsu...@googlegroups.com
> https://groups.google.com/group/swi-prolog.
> > For more options, visit
> https://groups.google.com/d/optout.
> >
> >
> > --
> > You received this message because you are subscribed to
> the Google
> > Groups "SWI-Prolog" group.
> > To unsubscribe from this group and stop receiving emails
> from it, send
> > an email to swi-prolog+...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>
> <mailto:swi-prolog%252Buns...@googlegroups.com>>
> > <mailto:swi-prolog+...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>
> <mailto:swi-prolog%2Bunsu...@googlegroups.com
> <mailto:swi-prolog%252Buns...@googlegroups.com>>>.
Keri Harris
Jul 30, 2016, 2:42:14 AM7/30/16
to swi-p...@googlegroups.com
This S1003 error is almost certainly caused by the ODBC driver
defaulting to ODBC 2.0 behaviour, while SWI-Prolog defaults to ODBC 3.0
behaviour. (The ODBC drivers themselves will be able to support both
ODBC 2.0 and ODBC 3.0 connections, but the error indicates that they
choose ODBC 2.0 by default). This mismatch in versions causes the
SWI-Prolog ODBC layer to pass SQL_C_TYPE_DATE (an ODBC 3.0 datatype) as
an arg to SQLBindCol() whereas the ODBC driver expects to receive
SQL_C_DATE (an ODBC 2.0 datatype).
Setting SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3 just after an ODBC
environment has been allocated, but before it has been used should fix
this. For example:
HENV henv;
SQLAllocEnv(&henv);
SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3,
0);
Thanks
Keri
defaulting to ODBC 2.0 behaviour, while SWI-Prolog defaults to ODBC 3.0
behaviour. (The ODBC drivers themselves will be able to support both
ODBC 2.0 and ODBC 3.0 connections, but the error indicates that they
choose ODBC 2.0 by default). This mismatch in versions causes the
SWI-Prolog ODBC layer to pass SQL_C_TYPE_DATE (an ODBC 3.0 datatype) as
an arg to SQLBindCol() whereas the ODBC driver expects to receive
SQL_C_DATE (an ODBC 2.0 datatype).
Setting SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3 just after an ODBC
environment has been allocated, but before it has been used should fix
this. For example:
HENV henv;
SQLAllocEnv(&henv);
SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3,
0);
Thanks
Keri
Jan Wielemaker
Jul 30, 2016, 7:28:14 AM7/30/16
to Keri Harris, swi-p...@googlegroups.com
Thanks Keri! Merged and pushed. The binary for Windows should become
available tomorrow in the daily builds [1].
Cheers --- Jan
[1] http://www.swi-prolog.org/download/daily/bin/
available tomorrow in the daily builds [1].
Cheers --- Jan
[1] http://www.swi-prolog.org/download/daily/bin/
Reply all
Reply to author
Forward
0 new messages