Using ODBC interface

[Lena Alkhalili]

I am working to integrate prolog with the sql server 2016, Prolog can insert rows to the database

and read from it, I faced a problem, when I tried to insert a row which contains date colum the error "ༀERROR: ODBC: State S1003: [Microsoft][ODBC Driver Manager] Program type out of range"

Kindly note that it works fine if the row does not have date column

anybody can help me?

[Fernando Sáenz Pérez]

Dear Lena,

It seems that this interface does not handle the date datatype (at least in several DBMS's I tried). Otherwise, you can use a convert function in SQL Server as in:

select cast(a as varchar) from t;

where "a" is a date column in table "t". After the keyword "as" you can use the datatype you want to convert the date column to.

Hope this helps.

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]

On 29/05/16 22:14, Fernando Sáenz Pérez wrote:
> Dear Lena,
> It seems that this interface does not handle the date datatype (at least
> in several DBMS's I tried). Otherwise, you can use a convert function in
> SQL Server as in:
>
> select cast(a as varchar) from t;
>
> where "a" is a date column in table "t". After the keyword "as" you can
> use the datatype you want to convert the date column to.
> Hope this helps.

If it is a conversion missing in our ODBC connection, please provide a
(complete) example. Shouldn't be too hard to fix.

Cheers --- Jan

> Fernando
>
> 2016-05-29 11:06 GMT+02:00 Lena Alkhalili <l.alkh...@gmail.com

> <mailto:l.alkh...@gmail.com>>:

>
> I am working to integrate prolog with the sql server 2016, Prolog
> can insert rows to the database
> and read from it, I faced a problem, when I tried to insert a row
> which contains date colum the error "ༀERROR: ODBC: State S1003:
> [Microsoft][ODBC Driver Manager] Program type out of range"
> Kindly note that it works fine if the row does not have date column
> anybody can help me?
>
> --
> 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>.

> 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+...@googlegroups.com>.

[Fernando Sáenz Pérez]

Hello,

For example, giving the ODBC connection name 'sqlserver', first we create a table "t" with a column "a" of type "date", then insert a tuple and try to retrieve it:

test(X) :-

    setup_call_cleanup(odbc_connect(sqlserver,Handle,[]),

                       (odbc_query(Handle,

                                   'create table t(a date)',

                                   _),

                        odbc_query(Handle,

                                   'insert into t values("2016-05-31")',

                                   _),

                        odbc_query(Handle,

                                  'select a from t', X,

                                  [ types([date]) ])

                       ),

                       odbc_disconnect(Handle)).

The following call delivers an exception when tried in Windows 7 and SWI-Prolog 7.2.3:

17 ?- test(X).

ERROR: ODBC: State S1003: [Microsoft][ODBC Driver Manager] Program type out of range

^  Exception: (9) setup_call_catcher_cleanup(user:odbc_connect(sqlserver, '$odbc_connection'(16962836), []), user: (odbc_query('$odbc_connection'(16962836), 'create table t(a date)', _G38906), odbc_query('$odbc_connection'(16962836), 'insert into t values(\'2016-05-31\')', _G38913), odbc_query('$odbc_connection'(16962836), 'select a from t', _G38827, [types([date])])), _G39000, user:odbc_disconnect('$odbc_connection'(16962836))) ? 

All the best,

Fernando

[Jan Wielemaker]

I'm surely not an expert here. Matt (much better) gives a complicated
answer. Quoting:

Cheers --- Jan

I think we need a lot more information. Specifically:
* What database is he using? (SQL Server, I'm guess).
* What version of the database (I think it's very very old, but
that's just a guess)
* What driver is he using? The MS SQL Native client? FreeTDS? The
Windows SQL Server driver? Something else?
* What version of that driver?

I tried it here, and it worked fine once I changed

odbc_query(Handle,
'insert into t values("2016-05-31")',
_),

to

odbc_query(Handle,
'insert into t values(\'2016-05-31\')',
_),

since the first one gave me an error that there was no such column
'2016-05-31', (I think this is right - the quote character for SQL is '
and not ", so I think that means that "2016-05-31" is interpreted as a
column name).

For reference, I'm using the Microsoft SQL Native Client driver, version
11.0 (which is listed as 2011.110.3000.00 in ODBC Driver Manager, but
10.50.2500 to odbc_connection_get/2 which is interesting), to connect to
SQL Server 2014 (version 12.00.2269). With that one modification, the
SQL worked perfectly. A quick google for the error message seems to turn
up a lot of references to really old versions of SQL Server and some
workarounds to deal with shortcomings in them. Perhaps this is a problem
that has nothing to do with Prolog or odbc4pl and everything to do with
Fernando's setup?

>
>
> All the best,
> Fernando
>
> 2016-05-30 9:58 GMT+02:00 Jan Wielemaker <J.Wiel...@vu.nl

> <mailto:J.Wiel...@vu.nl>>:

>
> On 29/05/16 22:14, Fernando Sáenz Pérez wrote:
> > Dear Lena,
> > It seems that this interface does not handle the date datatype (at least
> > in several DBMS's I tried). Otherwise, you can use a convert function in
> > SQL Server as in:
> >
> > select cast(a as varchar) from t;
> >
> > where "a" is a date column in table "t". After the keyword "as" you can
> > use the datatype you want to convert the date column to.
> > Hope this helps.
>
> If it is a conversion missing in our ODBC connection, please provide a
> (complete) example. Shouldn't be too hard to fix.
>
> Cheers --- Jan
>
> > Fernando
> >
> > 2016-05-29 11:06 GMT+02:00 Lena Alkhalili <l.alkh...@gmail.com <mailto:l.alkh...@gmail.com>

> > <mailto:l.alkh...@gmail.com <mailto:l.alkh...@gmail.com>>>:

> >
> > I am working to integrate prolog with the sql server 2016, Prolog
> > can insert rows to the database
> > and read from it, I faced a problem, when I tried to insert a row
> > which contains date colum the error "ༀERROR: ODBC: State S1003:
> > [Microsoft][ODBC Driver Manager] Program type out of range"
> > Kindly note that it works fine if the row does not have date column
> > anybody can help me?
> >
> > --
> > 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 toswi-prolog...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>
> > <mailto:swi-prolog+...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>>.
> > Visit this group athttps://groups.google.com/group/swi-prolog.
> > For more options, visithttps://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 toswi-prolog...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>
> > <mailto:swi-prolog+...@googlegroups.com
> <mailto:swi-prolog%2Bunsu...@googlegroups.com>>.
> > Visit this group athttps://groups.google.com/group/swi-prolog.
> > For more options, visithttps://groups.google.com/d/optout.
>
>

[Fernando Sáenz Pérez]

Hello,

   * What database is he using? (SQL Server, I'm guess).
      * What version of the database (I think it's very very old, but that's just a guess)

You are right with your guess: In my case, SQL Server 2008 32 bit, but Lena reported originally the same issue for SQL Server 2016.

 

   * What driver is he using? The MS SQL Native client? FreeTDS? The Windows SQL Server driver? Something else?
      * What version of that driver?

In my case, Microsoft SQL Server Native Client 11.0, version 2011.110.3000.00, also 32bit. Don't know in the case of Lena.

 

I tried it here, and it worked fine once I changed
                       odbc_query(Handle,
                                  'insert into t values("2016-05-31")',
                                  _),
to
                       odbc_query(Handle,
                                  'insert into t values(\'2016-05-31\')',
                                  _),

since the first one gave me an error that there was no such column '2016-05-31', (I think this is right - the quote character for SQL is ' and not ", so I think that means that "2016-05-31" is interpreted as a column name).

This is amazing, copy-paste to gmail changed two single quotes ('') to a single double quote (") (maybe, when you receive this, the same might been happened again!) Double quotes are indeed identifier delimiters as proposed in the SQL standard. Repeating the single quote seems to behave the same as for escaping the quote, and I use to escape this way.

 

A quick google for the error message seems to turn up a lot of references to really old versions of SQL Server and some workarounds to deal with shortcomings in them. Perhaps this is a problem that has nothing to do with Prolog or odbc4pl and everything to do with Fernando's setup?

Might it be because of the 32bit architecture? (Lena suffered the problem with SQL Server 2016, 32bit or 64bit?

All the best,

Fernando

 

[Fernando Sáenz Pérez]

P.S. You can also find the double quotes in the SWI-Prolog web dedicated to ODBC (in the insert example):

http://www.swi-prolog.org/pldoc/man?section=odbc

and I guess this is the result of some automation (the browser?) that changed '' (single quotes) to " (double quote) as well.

All the best

[Julio Di Egidio]

On Monday, May 30, 2016 at 7:40:59 PM UTC+2, Fernando Sáenz Pérez wrote:

> Repeating the single quote seems to behave the same as for escaping the quote, and I use to escape this way.

> Might it be because of the 32bit architecture?

The escape sequence does not depend on the architecture, it is per the language reference (T-SQL) for single-quoted string literals.

On the other hand, both for identifiers and string literals, there is a QUOTED_IDENTIFIERS option to take into account (the behaviour you are seeing goes with the default value):

  https://technet.microsoft.com/en-us/library/ms176027.aspx

HTH,

Julio

[Fernando Sáenz Pérez]

The guess referred to the inline text, not to the escape sequence:

Repeating the single quote seems to behave the same as for escaping the quote, and I use to escape this way.

> A quick google for the error message seems to turn up a lot of references to really old versions of SQL Server and some workarounds to deal with shortcomings in them. 

>Perhaps this is a problem that has nothing to do with Prolog or odbc4pl and everything to do with Fernando's setup?

Might it be because of the 32bit architecture? (Lena suffered the problem with SQL Server 2016, 32bit or 64bit?

Anyway, thanks for the pointer.

All the best,

Fernando

2016-05-30 20:07 GMT+02:00 Julio Di Egidio <ju...@diegidio.name>:

On Monday, May 30, 2016 at 7:40:59 PM UTC+2, Fernando Sáenz Pérez wrote:

> Repeating the single quote seems to behave the same as for escaping the quote, and I use to escape this way.

> Might it be because of the 32bit architecture?

The escape sequence does not depend on the architecture, it is per the language reference (T-SQL) for single-quoted string literals.

On the other hand, as for the rules for identifiers, there is a QUOTED_IDENTIFIERS option to take into account (the behaviour you are seeing goes with the default value):

  https://technet.microsoft.com/en-us/library/ms176027.aspx

HTH,

Julio

--
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.

[Julio Di Egidio]

If you are referring to Jan's example, he is rather [changing the double- to single-quotes around the date literal, then] escaping the single quotes of the date literal as per Prolog's rules for a quoted atom.  In general, if you have SQL inside Prolog, you have to apply SQL escaping, then apply Prolog escaping to it.

Julio

[Jan Wielemaker]

On 05/30/2016 07:59 PM, Fernando Sáenz Pérez wrote:
> P.S. You can also find the double quotes in the SWI-Prolog web dedicated
> to ODBC (in the insert example):
> http://www.swi-prolog.org/pldoc/man?section=odbc
> and I guess this is the result of some automation (the browser?) that
> changed '' (single quotes) to " (double quote) as well.

You mean in http://www.swi-prolog.org/pldoc/doc_for?object=odbc_query/4?
This is not a conversion issue. Should the " be \'?

Cheers --- Jan

[Fernando Sáenz Pérez]

You mean in http://www.swi-prolog.org/pldoc/doc_for?object=odbc_query/4?

Yes.

The example is:

insert_child(Child, Mother, Father, Affected) :-
        odbc_query(parents,
                   'INSERT INTO parents (name,mother,father) \
                      VALUES ("mary", "christine", "bob")',
                   affected(Affected)).

And the double quotes " should be either '' or \'.

All the best,

Fernando

[Jan Wielemaker]

On 05/30/2016 08:45 PM, Fernando Sáenz Pérez wrote:
> You mean in http://www.swi-prolog.org/pldoc/doc_for?object=odbc_query/4?
>
>
> Yes.
> The example is:
>
> insert_child(Child, Mother, Father, Affected) :-
> odbc_query(parents,
> 'INSERT INTO parents (name,mother,father) \
> VALUES ("mary", "christine", "bob")',
> affected(Affected)).
>
>
> And the double quotes " should be either '' or \'.

Thanks. Changed example and highlight this issues in the description of the
example.

Cheers --- Jan

[Fernando Sáenz Pérez]

Hello,

With Windows 10 64 bit, SQL Server 2014 64bit, and 64bit ODBC driver manager with SQL Server Native Client 11.0 (2011.110.3000.00) (the platform is listed as 32bit/64bit, which means that the same connection can be used from both, as I checked), the same issue remains:

4 ?- test(X).

ERROR: ODBC: State S1003: [Microsoft][Administrador de controladores ODBC] Tipo de programa fuera de intervalo

^  Exception: (9) setup_call_catcher_cleanup(user:odbc_connect(sqlserver, '$odbc_connection'(26751756), []), user: (odbc_query('$odbc_connection'(26751756), 'create table t(a date)', _G26335), odbc_query('$odbc_connection'(26751756), 'insert into t values(\'2016-05-31\')', _G26342), odbc_query('$odbc_connection'(26751756), 'select a from t', _G26259, [types([date])])), _G26425, user:odbc_disconnect('$odbc_connection'(26751756))) ? 

>> Perhaps this is a problem that has nothing to do with Prolog or odbc4pl and everything to do with Fernando's setup?

I'm not sure. I did another test, this time with PostgreSQL 9.5.1 (PostgreSQL ODBC Driver ANSI 9.05.01.00) and first, SICStus Prolog 4.3.1 64bit and, second, with SWI-Prolog 7.2.3 64 bit. The first went fine but the second raised the same error. If the issue would come from the OS/ODBC/DBMS setup, I think that the same issue would be revealed in both Prolog systems, but it doesn't.

All the best,

Fernando

[Matt Lilley]

On 1/06/2016, at 12:25 am, Fernando Sáenz Pérez <fsaen...@gmail.com> wrote:

Hello,

With Windows 10 64 bit, SQL Server 2014 64bit, and 64bit ODBC driver manager with SQL Server Native Client 11.0 (2011.110.3000.00) (the platform is listed as 32bit/64bit, which means that the same connection can be used from both, as I checked), the same issue remains:

4 ?- test(X).

ERROR: ODBC: State S1003: [Microsoft][Administrador de controladores ODBC] Tipo de programa fuera de intervalo

^  Exception: (9) setup_call_catcher_cleanup(user:odbc_connect(sqlserver, '$odbc_connection'(26751756), []), user: (odbc_query('$odbc_connection'(26751756), 'create table t(a date)', _G26335), odbc_query('$odbc_connection'(26751756), 'insert into t values(\'2016-05-31\')', _G26342), odbc_query('$odbc_connection'(26751756), 'select a from t', _G26259, [types([date])])), _G26425, user:odbc_disconnect('$odbc_connection'(26751756))) ? 

>> Perhaps this is a problem that has nothing to do with Prolog or odbc4pl and everything to do with Fernando's setup?

I'm not sure. I did another test, this time with PostgreSQL 9.5.1 (PostgreSQL ODBC Driver ANSI 9.05.01.00) and first, SICStus Prolog 4.3.1 64bit and, second, with SWI-Prolog 7.2.3 64 bit. The first went fine but the second raised the same error. If the issue would come from the OS/ODBC/DBMS setup, I think that the same issue would be revealed in both Prolog systems, but it doesn't.

That does sound a bit weird, but I'm starting to get out of my depth; the error being returned is coming from outside of Prolog, which presumably means we're passing through something different which the ODBC driver (or manager) doesn't understand. Unfortunately, it works perfectly for me, both on Windows (in the situation described in my earlier email) and on my personal machine (OS X 10.10.5, PostgreSQL 9.3.5 using psqlodbc 09.02.100).

Maybe we'll find out more if we can see exactly what they driver is getting.  Can you turn on ODBC driver tracing (it's in the ODBC administrator dialog on a tab somewhere) then get a trace for both SWI and SICStus - maybe this will turn up something interesting.

Sorry I can't be of more help. If only I could reproduce it :(

Cheers,

Matt

To unsubscribe from this group and stop receiving emails from it, send an email to swi-prolog+...@googlegroups.com.

[Fernando Sáenz Pérez]

Hi,

I've struggled a bit with ODBC tracing and finally I got the attached log, which corresponds to executing test(X) in p.pl (also attached) with SWI-Prolog 7.2.3 64bit, Windows 10, SQL Server 2014, and SQL Server Native Client 11.0 (2011.110.3000.00). The connection sqlserver is as listed in the ODBC Manager, where you can see that the platform is 32/64 bit in the screenshot below.

Sorry because messages are in Spanish, but I think that the important information is not language dependent. I hope that this may help in understanding the issue with this error state S1003.

All the best,

Fernando

[jacques....@gmail.com]

All the best,

Fernando

>     <mailto:swi-prolog+unsub...@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+unsub...@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.
Visit this group at https://groups.google.com/group/swi-prolog.
For more options, visit https://groups.google.com/d/optout.

Hi there

My current experiences with ODBC and MySQL :

19 ?- odbc_query(csv_db,'INSERT INTO Test_table (PersonID,Name,Phone) VALUES (113,''XXXX'',1234567)',Res).
 Res = affected(1).

20 ?- odbc_query(csv_db,'INSERT INTO Test_table (PersonID,Name,Phone) VALUES (114,"XXXX",20160604)',Res).
 Res = affected(1).

21 ?- odbc_query(csv_db,'INSERT INTO Test_table (PersonID,Name,Phone) VALUES (~w,~w,~w)'-[118,'Abc',20160604],Res).
ERROR: ODBC: State S0022: [MySQL][ODBC 5.3(a) Driver][mysqld-5.7.11-log]Unknown column 'Abc' in 'field list'

22 ?- odbc_query(csv_db,'INSERT INTO Test_table (PersonID,Name,Phone) VALUES (~w,~w,~w)'-[118,"Abc",20160604],Res).
ERROR: ODBC: State S0022: [MySQL][ODBC 5.3(a) Driver][mysqld-5.7.11-log]Unknown column 'Abc' in 'field list'

23 ?- odbc_query(csv_db,'INSERT INTO Test_table (PersonID,Name,Phone) VALUES (~w,~w,~w)'-[118,'''Abc''',20160604],Res).
Res = affected(1).

 Hope this helps.

I will soon need the functionnality : ODBC_query(Dsn,Query_insert-[Values],Res).

Best regards

JAB

[jacques....@gmail.com]

Dear Jan,

I spent a bit more time on what seems to be a problem in odbc_query when used with :

2.2.1 One-time invocation

odbc_query(+Connection, +SQL, -RowOrAffected, +Options)

                Fire an SQL query on the database represented by Connection. S

                SQL is any valid SQL statement.

                SQL statements can be specified as a plain atom, string or

                a term of the format Format-Arguments, which is converted using format/2.

The expected behaviour is to be able to insert an atom as a value for a VARCHAR
and not to have to quote it to : '"wathever_the_value_is"'   (see #10 in the log file).

I join a file with the various predicates used for the test and

a second file with the log produced running SWI 7.3.14  (32bits) on Windows 10

Hope my investigations will help to find a solution for a next release.

Thank you for your great job on SWI

Best regards

JAB

[Jan Wielemaker]

On 06/10/2016 12:27 PM, jacques....@gmail.com wrote:
> Dear Jan,

Thanks for the examples.

My first hope is that Matt will look after this. He knows way more
about databases and ODBC than I do.

Cheers --- Jan

>
> I spent a bit more time on what seems to be a problem in odbc_query when
> used with :
> 2.2.1 One-time invocation

> *odbc_query*(+Connection, +SQL, -RowOrAffected, +Options)

>
> Fire an SQL query on the database represented by
> Connection. S
> SQL is any valid SQL statement.
> SQL statements can be specified as a plain atom,
> string or
> a term of the format Format-Arguments, which is

> converted using _format/2_
> <http://www.swi-prolog.org/pldoc/man?predicate=format/2>.

[Fernando Sáenz Pérez]

Hello,

I do not see a problem here. What you are sending to the ODBC connection should be a valid SQL query for, in this case, MySQL. As indicated in the docs, the predicate odbc_query/2 applies format/2 to the term Format-Arguments. In your call to input insert_4, you get the result of the following as a statement to be sent to MySQL:

6 ?- format('INSERT INTO Test_table (PersonID,Name,Phone) VALUES (~w, ~w, ~w)', [4,jab,5]).

INSERT INTO Test_table (PersonID,Name,Phone) VALUES (4, jab, 5)

true.

But this is not what MySQL is expecting because jab, without quotes, is understood as an identifier by any DBMS.

On the other hand, in your predicate insert_2, you get:

7 ?- format('INSERT INTO Test_table (PersonID,Name,Phone) VALUES (~w, "jab2", ~w)', [2,6457]).

INSERT INTO Test_table (PersonID,Name,Phone) VALUES (2, "jab2", 6457)

true.

which is a valid MySQL SQL statement (out of standard SQL, where double quotes should delimite identifiers, not strings) because strings are allowed to be enclosed between such double quotes.

That said, why not put single quotes (the standard way also accepted by MySQL) around SQL strings in your term?:

8 ?- format('INSERT INTO Test_table (PersonID,Name,Phone) VALUES (~w, \'~w\', ~w)', [4,jab,5]).

INSERT INTO Test_table (PersonID,Name,Phone) VALUES (4, 'jab', 5)

true.

(Note that single quotes must be escaped because they occur inside a term. Alternatively, you can escape a single quote by typing it twice.)

What you get now is a standard, valid query accepted by MySQL.

Hope this helps,

Fernando

--

[jacques....@gmail.com]

What about the example insert_3, and the quotation problem with format.

JAB

[jacques....@gmail.com]

What about the example insert_3, and the quotation problem with format.

and the problems with the others examples in the log_file ...

Regards

JAB