Home > Archive > Prolog > March 2005 > swi prolog and odbc connectivity
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
swi prolog and odbc connectivity
|
|
|
|
Hi All:
I've been experimenting with the ODBC package that's included in SWI
Prolog. I wanted to see if anyone has any experience with it and
might be able to point me in the right direction.
I have a short program where I am just running an SQL statement just
to see a recordset be displayed. The connectivity parts seems to be
fine but the display of the result recordset seems to be the problem.
My short program is below (very bottom) and is invoked by simply 'go'.
It loads fine. When I run it, I simply get a 'Yes' back. I was
thinking and I want to see the result set of the SQL statement.
If I turn on odbc debugging with odbc_debug(10) and then turn on
tracing with trace, I get a better hint of what is going on.
Down at the bottom of the trace, I can tell the ODBC connectivity is
there and that data is being read where the text reads 'Alfreds
Futterkiste', 'Maria Anders' and displays the rest of the fields.
Unfortunately, when I don't run in trace mode and just run normally,
the record isn't printed. Also, it looks as though from the trace,
that only one record is being fetches. Since my SQL query is select *
from customers, it should give me every record.
Anyone have any ideas on this?
Thanks much!
Drew
2 ?- odbc_debug(25).
Yes
3 ?- trace.
Yes
[trace] 3 ?- go.
Call: (7) go ? creep
Call: (8) open_database ? creep
Call: (9) odbc:odbc_connect('NorthWind', _L216, [alias(nwind),
open(once)]) ? creep
Exit: (9) odbc:odbc_connect('NorthWind', nwind, [alias(nwind),
open(once)]) ? creep
Exit: (8) open_database ? creep
Call: (8) run_query ? creep
Call: (9) odbc:odbc_query(nwind, 'select * from customers', _L217,
[types([string, default, default, default, default|...]),
null(nullski)]) ? creep
prepare_result(): column 1, sqlTypeID = 12, cTypeID = 1, columnSize =
5
prepare_result(): column 2, sqlTypeID = 12, cTypeID = 1, columnSize =
40
prepare_result(): column 3, sqlTypeID = 12, cTypeID = 1, columnSize =
30
prepare_result(): column 4, sqlTypeID = 12, cTypeID = 1, columnSize =
30
prepare_result(): column 5, sqlTypeID = 12, cTypeID = 1, columnSize =
60
prepare_result(): column 6, sqlTypeID = 12, cTypeID = 1, columnSize =
15
prepare_result(): column 7, sqlTypeID = 12, cTypeID = 1, columnSize =
15
prepare_result(): column 8, sqlTypeID = 12, cTypeID = 1, columnSize =
10
prepare_result(): column 9, sqlTypeID = 12, cTypeID = 1, columnSize =
15
prepare_result(): column 10, sqlTypeID = 12, cTypeID = 1, columnSize =
24
prepare_result(): column 11, sqlTypeID = 12, cTypeID = 1, columnSize =
24
Exit: (9) odbc:odbc_query(nwind, 'select * from customers',
row("ALFKI", 'Alfreds Futterkiste', 'Maria Anders', 'Sales
Representative', 'Obere Str. 57', 'Berlin', nullski, '12209',
'Germany', '030-0074321', '030-0076545'), [types([string, default,
default, default, default|...]), null(nullski)]) ? creep
Exit: (8) run_query ? creep
Exit: (7) go ? creep
Yes
[debug] 4 ?-
--------------------------------------------------------------------------------------------------------------
% Source code below
go :- open_database, run_query.
open_database :-
odbc_connect('NorthWind', _,
[
alias(nwind),
open(once)
]).
run_query :- odbc_query(nwind,
'select * from customers',
X,
[types([string,default,default,
default,default,default,
default,default,default,
default,default]),
null(nullski)
]
).
| |
| Brian Hulley 2005-03-04, 4:00 pm |
|
Drew wrote:
> It loads fine. When I run it, I simply get a 'Yes' back.
In Prolog, you yourself must code some method of printing out results
eg using some predicate that recurses through the result calling
write/1, nl/0, etc to actually get output to appear on the console.
> run_query :- odbc_query(nwind,
> 'select * from customers',
> X,
> [types([string,default,default,
> default,default,default,
> default,default,default,
> default,default]),
> null(nullski)
> ]
> ).
ie change the above to:
run_query :- odbc_query(....., X, ....), print_result(X).
print_result(X) :- write(X),nl.
Then you can improve the implementation of print_result/1 once you see
how X is structured.
Hope this helps,
Brian.
| |
| Brian Hulley 2005-03-04, 4:00 pm |
|
Brian Hulley wrote:
> Drew wrote:
>
> In Prolog, you yourself must code some method of printing out results
Of course you could alternatively just use run_query(X) :- ... to get
the Prolog top level to print out the binding for X, but this would not
give you any control of the formating etc.
| |
|
|
Hey Brian:
Thanks for your help! This worked fine. I should have thought of
that.
The only thing I'm still puzzled about is that SWI Prolog is still
just printing the first record in the table whereas the SQL statement
should be giving it all of the records.
Thanks!
Drew
On 4 Mar 2005 08:22:07 -0800, "Brian Hulley" <brianh@metamilk.com>
wrote:
>
>Drew wrote:
>
>In Prolog, you yourself must code some method of printing out results
>eg using some predicate that recurses through the result calling
>write/1, nl/0, etc to actually get output to appear on the console.
>
>
>ie change the above to:
>
>run_query :- odbc_query(....., X, ....), print_result(X).
>
>print_result(X) :- write(X),nl.
>
>Then you can improve the implementation of print_result/1 once you see
>how X is structured.
>
>Hope this helps,
>
>Brian.
| |
| Brian Hulley 2005-03-04, 8:57 pm |
|
Drew wrote:
> The only thing I'm still puzzled about is that SWI Prolog is still
> just printing the first record in the table whereas the SQL statement
> should be giving it all of the records.
I must confess I don't know the answer to this second question either.
However you might find some more info on the SWI-Prolog website
www.swi-prolog.org - there is a searchable mailing list archive, user
twiki etc
Brian.
| |
| Fabien Todescato 2005-03-07, 4:04 pm |
| Drew wrote:
> The only thing I'm still puzzled about is that SWI Prolog is still
> just printing the first record in the table whereas the SQL statement
> should be giving it all of the records.
Drew,
In SWI-Prolog, odbc_query/4 is a non-deterministic predicate that will
return multiple solutions upon backtracking.
If you write your run_query/0 as follows :
run_query :- odbc_query(....., X, ....), print_result(X),fail;true.
That is, by forcing backtracking with fail/0, you should have all the
rows from your 'SELECT * ...;' statement printed out.
Cheers.
| |
| Djamé Seddah 2005-03-07, 4:04 pm |
| Fabien Todescato a écrit :
> Drew wrote:
>
>
>
> Drew,
>
> In SWI-Prolog, odbc_query/4 is a non-deterministic predicate that will
> return multiple solutions upon backtracking.
>
> If you write your run_query/0 as follows :
>
> run_query :- odbc_query(....., X, ....), print_result(X),fail;true.
>
> That is, by forcing backtracking with fail/0, you should have all the
> rows from your 'SELECT * ...;' statement printed out.
>
> Cheers.
or maybe simpler
run(Result):-findall(X ,odbc_query(...,X,....),Result).
and then
:-run(Result),pretty_print(Result).
use some list predicates (like setof to remove duplicates, etc..)
pretty_print is defined in pretty_print.pl somewher in $PLACE_OF_SWI/lib
(I guess it's in the source package)
you can include some print query directly into your findall
findall(X,(odbc_query(...,X,...),print(X)),Result)
have fun
don't forget that you can put anything which unify with the body of the
findall statement instead of the X (lists, predicates,
assert(something_usefull(X)) ....)
| |
|
|
Thanks Fabien.
I think I understand now. It sounds like my first version was finding
the first record and showing it successfully, but then not
backtracking to pick up all possible solutions. The fail predicate
you mentioned forces this backtracking. What does the ';true' after
the fail do?
It sounds like odbc_query doesn't know whether it succeeds or fails.
Is that what you mean by it being non-deterministic? And then, since
it doesn't know, you have to force the backtracking to get all
records.
This is different from normal predicates in that backtracking
automatically happens unless you use the cut operator.
Am I understanding it correctly?
Thanks so much for your help. All the records are shown now.
Drew
On Mon, 07 Mar 2005 07:54:01 +0100, Fabien Todescato
<fabien.todescato@wanadoo.fr> wrote:
>Drew wrote:
>
>Drew,
>
>In SWI-Prolog, odbc_query/4 is a non-deterministic predicate that will
>return multiple solutions upon backtracking.
>
>If you write your run_query/0 as follows :
>
>run_query :- odbc_query(....., X, ....), print_result(X),fail;true.
>
>That is, by forcing backtracking with fail/0, you should have all the
>rows from your 'SELECT * ...;' statement printed out.
>
>Cheers.
| |
|
|
Thanks Djame!
I couldn't quite get this solution to work but I found some
documentation on findall and I understand what you are saying about
cleaning up the print with a pretty_print routine.
Here's what I attempted with yours. I think I've probably just got a
simple error. Thanks again for your help!
Sounds like there are two solutions for this: use of findall
predicate or the forcing of backtracking. I'm guessing internally
that 'findall' is basically a way of forcing backtracking.
Thanks again.
Drew
go2(Z) :- open_database, run_query2(Z).
open_database :-
odbc_connect('NorthWind', _,
[
alias(nwind),
open(once)
]).
run_query2(Result) :- findall(X, odbc_query(nwind,
'select * from customers',
X,
[types([string,default,default,
default,default,default,
default,default,default,
default,default]),
null(nullski)
]
), Result), print_result(X),fail;true.
print_result(X) :- nl, write(X), nl.
On Mon, 07 Mar 2005 12:27:38 +0100, Djamé Seddah
<djame@jamais-de-la-vie.com> wrote:
>Fabien Todescato a écrit :
>or maybe simpler
>
>run(Result):-findall(X ,odbc_query(...,X,....),Result).
>and then
>:-run(Result),pretty_print(Result).
>use some list predicates (like setof to remove duplicates, etc..)
>pretty_print is defined in pretty_print.pl somewher in $PLACE_OF_SWI/lib
>(I guess it's in the source package)
>you can include some print query directly into your findall
>findall(X,(odbc_query(...,X,...),print(X)),Result)
>
>
>have fun
>
>don't forget that you can put anything which unify with the body of the
>findall statement instead of the X (lists, predicates,
>assert(something_usefull(X)) ....)
>
| |
| Djamé Seddah 2005-03-07, 4:04 pm |
| Hello,
yes, you're right : using findall and forcing the backtracking is the
same use trace just to follow the step
but you don't need to use fail and true in your predicate (findall will
find every answer to your request using precisly this kind of statement)
just a question : don't you need to get the handle of the database,
juste like this (and don't you need to close it (in perl odbc, you have
to I think)) and to close the database ?
look at this one :
open_database(MyDB, :-
MyDB = 'nwind',
odbc_connect('NorthWind', _,
[
alias(MyDB),
open(once)
]).
run_query2(Result, MyDB) :- findall(X,
(% Start of the 'main' statement
odbc_query(MyDB,
'select * from customers',
X,
[types([string,default,default,
default,default,default,
default,default,default,
default,default]),
null(nullski)
]
),print_result(X) % print each X
), %end of 'main' statement
Result %list of X
) %end of findall
odbc_close(MyDB) %if I was you...
. %end of run_query2
print_result(X) :- nl, write(X), nl.
Drew a écrit :
> Thanks Djame!
>
> I couldn't quite get this solution to work but I found some
> documentation on findall and I understand what you are saying about
> cleaning up the print with a pretty_print routine.
>
> Here's what I attempted with yours. I think I've probably just got a
> simple error. Thanks again for your help!
>
> Sounds like there are two solutions for this: use of findall
> predicate or the forcing of backtracking. I'm guessing internally
> that 'findall' is basically a way of forcing backtracking.
>
> Thanks again.
> Drew
>
>
> go2(Z) :- open_database, run_query2(Z).
>
> open_database :-
> odbc_connect('NorthWind', _,
> [
> alias(nwind),
> open(once)
>
> ]).
>
>
>
> run_query2(Result) :- findall(X, odbc_query(nwind,
> 'select * from customers',
> X,
> [types([string,default,default,
> default,default,default,
> default,default,default,
> default,default]),
> null(nullski)
> ]
> ), Result), print_result(X),fail;true.
>
> print_result(X) :- nl, write(X), nl.
>
>
> On Mon, 07 Mar 2005 12:27:38 +0100, Djamé Seddah
> <djame@jamais-de-la-vie.com> wrote:
>
>
>
>
| |
| Fabien Todescato 2005-03-07, 8:59 pm |
| Drew wrote:
> Thanks Fabien.
>
> I think I understand now. It sounds like my first version was finding
> the first record and showing it successfully, but then not
> backtracking to pick up all possible solutions. The fail predicate
> you mentioned forces this backtracking. What does the ';true' after
> the fail do?
Well, the some_predicate :- some_goal,fail ; true. is a common trick to
force the 'results' of some_goal to be enumerated through backtracking,
and then, have some_predicate succeed nevertheless.
Say, you want a goal that *succeeds* and prints out all the elements of
some list, you could write :
write_list(L) :-
member(X,L) % Non-deterministically pick some X in the list L
, write_ln(X) % print it
, fail % force backtracking to enumerate another X
; true % succeed after all the solutions for X have been exhausted
..
This technique is commonly known as a 'failure-driven loop'...
>
> It sounds like odbc_query doesn't know whether it succeeds or fails.
> Is that what you mean by it being non-deterministic? And then, since
Well, a a non-deterministic goal is one that may yields zero or multiple
solutions upon backtracking. If I understand your viewpoint, I'd say a
non-deterministic goal is one that may *succeed* several times with
different variable bindings.
> it doesn't know, you have to force the backtracking to get all
> records.
>
> This is different from normal predicates in that backtracking
> automatically happens unless you use the cut operator.
I prefer not to over-elaborate on cut and backtracking, I'd say you
would be much better off with a good tutorial, such as :
http://kti.ms.mff.cuni.cz/~bartak/prolog/
http://www.coli.uni-saarland.de/~kris/learn-prolog-now/
Cheers.
>
> Am I understanding it correctly?
>
> Thanks so much for your help. All the records are shown now.
>
> Drew
>
>
>
> On Mon, 07 Mar 2005 07:54:01 +0100, Fabien Todescato
> <fabien.todescato@wanadoo.fr> wrote:
>
>
>
>
|
|
|
|
|