Re: Table doesn't have attribute

Hugues Demers writes:
 > Yes, it worked. I did exactly what you suggested, and it returned the actual
 > values that are in the DB. I did the odbc_sql right after the odbc_import 
 > that
 > does not work:
 > 
 >  : ?- odbc_import('DB.TABLENAME'('attribute_1',... ), tablename).
 > 
 > Does not work
 > 
 >  : ?- odbc_sql([], 'SELECT attribute_1 from DB.TABLENAME', [ANS]).
 > 
 > Does work.
 > 
 > 
 > Is it possible to map table to predicate with the lower-level sql interface? 
 > I
 > guess not, since it's low-level. However, that is precisely what interest me:
 > mapping table to predicate so I can write rules easily, without requiring 
 > SQL.
 > My initial problem is to write inference rules that uses data stored in a
 > database. The closer I am to the database, the better, I think. That's why 
 > XSB
 > with its table mapping to predicate is interesting. There are other solution,
 > however, that I've looked into. For example, we have a mapping of the DB 
 > schema
 > to an OWL-DL ontology. Inferring what we need is not easy with OWL. 
 > 
 > On another subject, why am I unable to do [odbc_call]. in a program file 
 > (.P)?
 > It says: 
 >  [odbc_call loaded]
 >  ++Error[XSB/runtime/P]: [Existence (No procedure usermod : odbc_open / 3 
 > exists)] []
 >  Forward Continuation...
 > 
 > I am able to call functions if I import them one at a time with import 
 > odbc_[...] from odbc_call.
 > 
 > I am fairly new to XSB and Prolog, I apologize for my newby questions.

odbc_call is a "module" so when using predicates from it in compiled
code, you must import them.  (The consult works for modules only at
the interactive user interface.)  So you must import them
one-at-a-time.  

[Of course, you can import many in a single command, but they all must
be named.  As in:
:- import odbc_sql/3, odbc_open/3, odbc_close/0 from odbc_call.
]

So it seems we have two options:

1. Define your own predicates (i.e. do by hand what the higher-level
interface does.)

or

2. Have me try to figure out how to extend the higher-level interface
to handle the database qualifier (assuming it is possible with the SQL 
Server odbc driver.)

The way I would approach 1. is as follows:

Say we have a database table we want to access
'DB.TABLENAME'('attribute_1','attribute_2','attribute_3').  and we
want to refer to it with the relation tablename/3.

I would first write a clause:
tablename(A1,A2,A3) :-
        odbc_sql([],'SELECT attribute_1,attribute_2,attribute_3 
                     FROM DB.TABLENAME',
                 [A1,A2,A3]).

This will allow you to access the data from that table by calling
tablename(?,?,?) in rules.

Now the problem with this is that it can be very inefficient, since it 
will scan each table entirely whenever you access it.

So we can improve this by improving the lookup when there are database 
indexes on the table.  For example, say that attribute_2 is the
primary key for this table (and primary keys have indices on them.)
Then I would modify the above definition to become:

tablename(A1,A2,A3) :-
        (nonvar(A2)
         ->     odbc_sql([A2],'SELECT attribute_1,attribute_3 
                               FROM DB.TABLENAME 
                               WHERE attribute_2 = ?',[A1,A3])
         ;      odbc_sql([],'SELECT attribute_1,attribute_2,attribute_3 
                               FROM DB.TABLENAME' [A1,A2,A3])
        ).

Now this clause checks to see if there is a value for the key, and if
so, then does a direct lookup using that value.  Here the database
will not scan the entire table, but use the index for faster access.

Now it may even be that this isn't fast enough for your application
and so you add a database index on attribute_1.  Then I can further
modify this clause to become:

tablename(A1,A2,A3) :-
        (nonvar(A2)
         ->     odbc_sql([A2],'SELECT attribute_1,attribute_3 
                               FROM DB.TABLENAME 
                               WHERE attribute_2 = ?',[A1,A3])
         ; nonvar(A1)
         ->     odbc_sql([A1],'SELECT attribute_2,attribute_3 
                               FROM DB.TABLENAME 
                               WHERE attribute_1 = ?',[A2,A3])

         ;      writeln(userout,'Non-indexed access to TABLENAME'),
                odbc_sql([],'SELECT attribute_1,attribute_2,attribute_3 
                               FROM DB.TABLENAME' [A1,A2,A3])
        ).

This is, in effect, what the odbc_import function generates for you.

I'll try to find some time to look into why the DB modifier doesn't
work in the higher-level interface.

-David

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/