To make an insert to MySQL from swin PROLOG

Help!
1

I can not insert data into my MySQL database

ventanainsertar :-
   new(Botones,dialog('Cuadro de Botones')),
   new(Nombre,text_item('Nombre')),
   new(Id,text_item('Identificacion')),
   new(BotonInsertar,button('Insertar',message(@prolog,in_sert,Nombre,Id))),
   new(BotonSalir,button('Salir',message(Botones,destroy))),
   send(Botones,append(Nombre)),
   send(Botones,append(Id)),
   send(Botones,append(BotonInsertar)),
   send(Botones,append(BotonSalir)),
   send(Botones,open).

in_sert(Nombre, Id, F):-
   odbc_query('prolog',
      'INSERT INTO persona (identificacion, nombre)
         VALUES (\'Nombre\', \'Id\')',
   affected(F)).
2

First, welcome to this group. With respect to your post, you should provide more details about the error or what you don’t get but rather expect. Anyway, it seems that there is a syntax error in your predicate in_sert/3 (odd name, by the way). The SQL query should be either a correct atom or string: Nombre and Id should be concatenated in the appropriate places to form the correct string or atom (you cannot simply write them inside quotes and expect to be concatenated). Look for atomic_list_concat, for example.

3

atomic_list_concat is a dangerous thing to use with SQL.
(obligatory: https://xkcd.com/327/ and https://www.explainxkcd.com/wiki/index.php/327:_Exploits_of_a_Mom)

It’s better to use ‘?’-substitution: http://www.swi-prolog.org/pldoc/man?section=odbc-param-query

4

The documentation on ODBC Parameterised queries suffers from a lack of clear examples (as does much of SWI Prolog, something I’m trying to help out with by writing tutorials).

The way I’ve done an insert into an SQL database is like so:

sql_escape_single_quotes(StringIn, StringOut) :-
  split_string(StringIn, "'", "", List),
  atomics_to_string(List, "''", StringOut).

db_insert(Title, Art) :-
  sql_escape_single_quotes(Title, ETitle),
  sql_escape_single_quotes(Art, EArt),  
  odbc_connect('blog', Connection, []),
  odbc_query(Connection, "INSERT INTO arts (title, art) VALUES ('~w', '~w')"-[ETitle, EArt]),
  odbc_disconnect(Connection).

I’ve edited the code below from a previous post after taking the time to get it to work. In my original post, I put [Title, Art] as the parameters in odbc_prepare instead of odbc_execute, and simply replaced them with [default, default] in odbc_prepare since I find the alternatives in the provided documentation incomprehensible:

db_insert(Title, Art) :-
   odbc_connect('blog', Connection, []),
   odbc_prepare(Connection, "INSERT INTO arts (title, art) VALUES (?, ?)", [default, default], Statement),
   odbc_execute(Statement, [Title, Art]),
   odbc_disconnect(Connection).

Thing’s I’m still in the dark on is if the findall option used by odbc_query works.

I’ve generally found ODBC to be an awful, Microsoft, technology and spent several hours yesterday figuring out why my SWI Prolog web application would not work on a new server running Postgresql 11. (The answer was in the pg_hba.conf file where the default ident refused to work with ODBC and had to be changed to trust)

5

If you think that ODBC is awful, you should try some of its predecessors, such as embedded SQL. :wink:

Anyway, I think you should add odbc_free_statement(Statement).
See packages/odbc/demo/wordnet.pl.

6

One question is why you would want to use a database? Prolog can do a good deal of that itself. A database can be good, for example if you want to share the data real time with other applications (although you can also provide a web API for other applications to access the Prolog data) or the data is too much to fit into Prolog. Depending on hardware, max startup time, etc., size can become a problem somewhere between 1M and a few hundred million rows.

If you need a database you can also opt for the embedded sqlite (can be used through a pack or ODBC), which saves all the problems of authentication but prohibits sharing. You can also look for the BerkeleyDB or RocksDB interfaces, etc.

7

I think Prolog and SQL make a very powerful combination, each with strengths the other doesn’t have.

As my skills at writing parsers with DCG have improved, I’ve found SWI Prolog a fantastic “data munging” language, able to read files in a given format and then feed the translation into an SQL database. Furthermore, Prolog’s “deductive database” skills let me fill in holes in the provided data.

For example, to teach myself this stuff, I’ve been working on a chess player where Prolog reads “pgn” formatted files, checks each move to see if the rules I translated into SWI Prolog from a hypothetical logical language developed at Stanford University agree its legal (initially, I had tons of bugs, but now I’ve worked through nearly all of Bobby Fisher’s games without my script barfing once) to build up a game tree in the database which will hopefully eventually create a quick and strong AI player.

I believe the same technique could be used for serious, commercial applications, and SWI Prolog could be a strong contender against Python/Ruby/Node… in the “cloud computing space” – but it can’t be hampered with a 1992 era SQL interface to achieve this.

ODBC simply isn’t supported by AWS, Digital Ocean etc for anyone wanting to hook their applications to their dedicated database servers.

I’ve never personally attempted to talk to Postgresql via port 5432 – and my favorite phrase is “everything is easy when you don’t know what you’re talking about” – but I’m nevertheless planning to dabble with SWI Prolog’s socket library to see how difficult it would be bypass ODBC and take a DIY route.

8

Good points. I try to sell Prolog for some time as a data munging language. It doesn’t really work so far, but I recon that is more my shortcoming than a shortcoming of the language :frowning: There are people who got this and run a fine business on this idea :slight_smile:

Not that I even was a big fan of ODBC, but it did had the advantage that you had to write only one interface to access data bases. Is there anything that can replace this except for writing zillions of interfaces? Most databases have a C interface. It isn’t really rocket science to wrap that nicely. The result will probably be about what the ODBC interface is, only a little simpler as when dealing with ODBC you also have to take care of the fact that you sometimes need different approaches depending on the actual database.

Note that using an existing C driver is typically easier and more performant than implementing the wire protocol in Prolog. I know we have JDBC and most modern high level languages have some database abstraction. Interfacing through high level languages is not really a good choice for Prolog though (or any other high level language). If there is something at a lower level we could reuse it gets more interesting.

9

Data munging

Was pretty sure I knew what you meant, but had to check. I am use to it as the phrase Data wrangling.

10

You had me thinking I’d coined a new phrase there, but google showed I must have heard data munging somewhere since it’s a common alternative to data wrangling.

11

I have to confess that as a Posgresql user, I’m only familiar with the specific libraries (in Python, psycopg2) and initially was thinking of using Python as “glue language” between Prolog and Postgresql, but gradually came to prefer doing things directly with Prolog, and my Prolog is generally better than my Python these days.

I was initially happy with ODBC, though it involved compiling the drivers from source – but as I’m a Linux from Scratch/Gentoo guy, no problem on my own box – until it came to conforming to the rules set down by AWS, Digital Ocean etc, who have to be paranoid and therefore don’t allow anyone to edit configuration files to “downgrade” their database access rules.

Using SWI Prolog as a wrapper for existing C libraries is the sensible approach (not something I have any experience in, but need to learn).

12

Long time ago there was a direct driver that, if I recall correctly, covered Oracle and Postgresql floating around. I have no clue how alive it still is, nor how good it is. It probably predates Unicode support. If it makes sense and you revive it, please let us know here.

Do I understand you correctly that the current Postgresql ODBC driver is outdated and talks to Postgresql using deprecated old interfaces?

13

The key hitch I hit was a line in Postgresql’s pg_hba.conf file in a fresh Centos 7 install:

# IPv4 local connections:
host    all             all             127.0.0.1/32            ident

I’m not sure if this is new or Centos 7 specific, because I can’t recall editing anything on my development machine where I installed Postgresql from source, but this breaks ODBC unless ident is changed to trust and SQL server restarted.

I’ve no idea what the difference between ident and trust means, but it’s what the guys offering shared database servers demand, meaning no ODBC support.

I will look into Postgresql C libraries that Python’s psycopg2 etc use and try to get back to you in a few days on my progress (it’s likely to be a steep learning curve for me, so please be patient).

Unfortunately, my creating Postgressql predicates for SWI Prolog will not solve the problem for developers wanting to use the myriad of alternatives (I’ve personally steered clear of noSQL systems, but I hear they’re popular).

I’ll try help by writing some generic documentation on the steps I’ve taken as a guide to others.

14

A very quick search finds https://twiki.org/cgi-bin/view/Plugins/SwiPrologToPostgreSqlAddOn

15

Maybe you should sell Prolog as an ETL language, especially that we’re now in a world of “big data” and the data typically needs a lot of cleansing and transformation.

17

It turns out ODBC is totally innocent, and the culprit is actually the ident server (or lack thereof in my case). I wasn’t aware that I needed to install and run a daemon process to make this work (I went with oidentd, I have no clue if that’s better or worse than authd, xinetd…).

My apologies to ODBC, but I partly blame Digital Ocean’s tech support who told me ODBC wasn’t supported, whereas if you’ve installed the ODBC driver client side, the server doesn’t care, as long as the client machine has an ident server running (a daemon I’d never heard of before).

Even though the solution turned out to be really simple once I understood the problem properly (as they tend to), I’m still keen to learn how to write SWI Prolog wrappers to C/C++ libraries (libpq for Postgresql), but it’s likely to be on the backburner.

18

In principle, it would be great to have a seamless connection between Prolog and a relational database. I don’t really think it is possible in the general case :frowning:

The issue is that a relational database becomes really interesting/useful when you start using the advanced features, and those are all proprietory. This goes beyond the differences in SQL dialects (even though that is also a problem in practice).

So, a C driver per vendor might be the solution that works sufficiently well for the use cases that indeed require a relational database (Postgres, Oracle, etc). I think sqlite is already covered by Nicos’ pack? but again, sqlite solves a different problem altogether.

19

One of Postgresql’s advantages over MySQL is server side programming. It’s default language is a “freeware dialect” of Oracle’s PL/SQL called PL/pgSQL.

Something, as far as I know, only Postgresql supports is a wide choice of server side programming languages, which can be extended. I stumbled on a github project to include SWI Prolog as a Postgresql server side language, but didn’t try it since the documentation indicated it hadn’t been taken far.

I’ve had no problem using “SELECT myfunction()” from SWI Prolog, and my opinion is if your queries in the client application are getting too long and complicated (and the number of rows getting returned are becoming unmanageably large), you need to be doing more stuff in the database server.

Just having a way to communicate short, simple queries to the SQL server is ideal.

But then I am suffering from very slow speeds in adding state1-move-state2 rows to Postgresql in may chess project, and am not sure if I should blame ODBC for that, or that I should not be calling

odbc_connect('blog', Connection, []),
...
odbc_disconnect(Connection)

on every insert.

20

You should definitely not disconnect, unless you have an application that only very infrequently accesses the database and you want to limit open network connections.

You should also keep your prepared statements as long as you can.

I don’t think the overhead of ODBC is prohibitive. Lack of access to proprietary database facilities could be. I’m not familiar with that aspect of ODBC and proprietary database APIs.

21

In my experiencie, the ODBC overhead depends on the database. For example, Oracle is really slow, whereas others as PostgreSQL are really fast (for Windows platforms).