To make an insert to MySQL from swin PROLOG

Help!
22

Hi Jan,

one example just to demonstrate what I mean. Oracle has had for a few years now a feature generally known as “Flashback Archive” that lets you, for example, query the data as it was at some point in the past, or create an audit trail for a row in a table, for a given time-frame.

This is not a “lightweight” feature, it must be taken in consideration during the design of the application and it has costs associated with it (amount of data stored, mostly, but in some cases it can create an overhead when inserting/updating).

These are the kind of proprietory features I was thinking about. Basically, you must commit to a vendor eventually and at that point you might as well start taking advantage of what you have…

24

Something like this was done in DES, where you can open an ODBC connection and use database tables and views as if they were predicates. The database interface was indeed tweaked for each database server.

Such connection permits for example the following:

DES> /open_db postgresql
DES> create table t(a int);
DES> insert into t values (1), (2), (3);
DES> t(X), t(Y), X>Y.
Info: Processing:
  answer(X,Y) :-
    t(X),
    t(Y),
    X>Y.
{
  answer(2,1),
  answer(3,1),
  answer(3,2)
}
Info: 3 tuples computed.

This can be tried at desweb.fdi.ucm.es with a shared PostgreSQL instance (pending work includes to provide a private instance for each account).

1 Like
25

Hello @fsaenzperez

I did try to evaluate DES. You helped me get on with it at the beginning (thank you once again).

I have to admit I gave it up at some point simply because I wasn’t sure what I’ll get out of it in the long run. The easy queries are easy in SQL (even if annoying to write). The proprietory features are proprietory. For example, in Oracle DB (using the “flashback archive” again as an example):

SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';
26

PS: even setting up ODBC to work with Oracle DB just on my own machine was more trouble than I thought it would be… :frowning:

27

You are welcome :slight_smile: And yes, I completely agree. Even for “standard” queries one has to tweak the interface as mentioned.

28

I guess you used some Linux distro. To me, configuring ODBC on non-Windows machines was also difficult. For Windows, it seemed to me pretty straightforward.

29

I think it was not meant to be. Request for Comments were intended for discussing fairly concrete proposals for new functionality or changes.

32

@Elmer

I changed the category to help because if I don’t do it now I won’t remember to do it in the future.

1 Like
33

Hi Elmer

I hope you’ve sorted this out. Basically, your problem is Nombre and Id need to be subsituted which can be done this simple, dangerous way:

in_sert(Nombre, Id, F):-
  odbc_query(prolog, "INSERT INTO persona (identificacion, nombre) VALUES ('~w', '~w')"-[Nombre, Id], affected(F)).

Reasons your code wouldn’t have worked included that you either need to put the SQL query string in double quotes (as I’ve done), or escape the single quotes as in (\`~w\`).

Another hitch is that if you’re not connecting to the database within the predicate making the insert, you would need to have created a connection elsewhere with odbc_connect elsewhere, where you could have been given it the alias prolog which becomes global so you don’t need to pass it on as an argument.

A safer way is to prepare the SQL string with odbc_prepare, put in the values with odbc_execute, and then subsequently call odbc_free_statement. I’m busy rewriting my own code to do it that way, preparing the SQL initially when I call odbc_connect and then freeing these when I finally close the database connection. (My method of constantly connecting and disconnecting has created an incredibly slow program which rewriting it this way will hopefully fix).

Apologies for turning this into a digression on ODBC and Postgresql, which mainly had to do with my struggle to understand Unix authentication when you graduate from a single workstation to internet-facing shared database servers.

ODBC, and SWI Prolog’s implementation, really work well once everything is set up correctly and one gets to grips with the basics of the predicates.

34

It has been stress-tested a lot by SecuritEase, who also contributed a lot with bug fixes and new functionality. Most of their application, AFAIK, talks to MIcrosoft SQL server. Wrapping a C API isn’t very hard, but getting it well designed and robust is, surely for complicated APIs, not that easy and typically requires several iterations with people actually using it.

That is what worries me for replacing the one mature ODBC interface with a whole collection of dedicated database interfaces. But, yes, ODBC requires one more configuration step (both the driver config and the client config), involves runtime overhead and cannot access all features of the underlying database.

1 Like
35

My experience with Postgresql is that whatever string gets passed to it as the SQL statement by ODBC, gets executed. So I don’t quite understand why “non-standard” features would get lost, assuming they are sent to the database in whatever flavour of SQL.