swi-prolog odbc error while inserting into postgresql

Question

I have the following problem and I'm begging for help: I'm using swi-prolog and odbc interface to connect to postgresql database. Problem occurs when I try to insert in database. SELECT works fine but INSERT doesn't work. Does anybody know what am I doing wrong.

Here's my simple test code:

:-use_module(library(odbc)).

connect(C):-
  odbc_connect(baza, C, [user(Mat), 
              password(lozinka), alias(baza), open(once)]).

sel(R) :-
    odbc_query(baza,
               'SELECT * FROM pacijent',
               R). 

ins:-
 odbc_query(baza, 'INSERT INTO pacijent (name, surname, passw, number) VALUES ("James", "Bond", 007, 007)').

This is the error when i try to insert:

?- ins.
ERROR: ODBC: State S1000: [unixODBC]ERROR:  column "James" does not exist at character 30;
Error while executing the query

Also i tried to insert through psql console and everything works fine, but as said problem is when inserting from prolog.

Please help, im stuck here.

Thanks

Answer 1

A suggestion: proper escaping, don't know if it'll be enough though. I'd turn

"James", "Bond"

into

\'James\', \'Bond\'

Answer 2

The problem is you've passed the column data for columns name and surname in double quotes instead of single quotes. Most databases use " (double quotes) for identifiers like table and column names and ' (single quotes) for data. How you ensure that in prolog I don't know.

I suspect you'll want to quote the other 2 columns as well.

In ODBC you'd use SQLGetInfo and get SQL_IDENTIFIER_QUOTE_CHAR which will usually return double quotes meaning to quote identifiers use these quotes.