Insertions and Deletions of Rows through the Relational Level

Insertion and deletion operations can also be performed on an imported table. The two predicates to accomplish these operations are odbc_insert/2 and odbc_delete/2. The syntax of odbc_insert/2 is as follows: the first argument is the declared database predicate for insertions and the second argument is some imported data source relation. The second argument can be declared with some of its arguments bound to constants. For example after Room is imported through odbc_import:

|?- odbc_import('Room'('RoomNo','CostPerDay','Capacity','FId'), room).
yes

Now we can do

| ?- odbc_insert(room_ins(A1,A2,A3),(room(A1,A2,A3,'3'))).

yes
| ?- room_ins('306','NULL'(_),2).

yes

This will insert the row: ('306',NULL, 2,'3') into the table Room. Note that any call to room_ins/7 should have all its arguments bound.

See Section 4.3.7) for information about NULL value handling.

The first argument of odbc_delete/2 predicate is the declared delete predicate and the second argument is the imported data source relation with the condition for requested deletes, if any. The condition is limited to simple comparisons. For example assuming Room/3 has been imported as above:

| ?- odbc_delete(room_del(A), (room('306',A,B,C), A > 2)). 

yes

After this declaration you can use:

| ?- room_del(3).

to generate the SQL statement:

DELETE From Room rel1 
WHERE rel1.RoomNo = '306' AND rel1.CostPerDay = ? AND ? > 2
;

Note that you have to commit your inserts or deletes to tables to make them permanent. (See section 5.2.9).

These predicates also have the form in which an additional first argument indicates a connection, for use with multiple datasources.

Also, some ODBC drivers have been found that do not accept the form of SQL generated for deletes. In these cases, you must use the lower-level interface: odbc_sql.