Postgresql UUID type

[canbican]

Test case:

% create extension if not exists "uuid-ossp"
% create table test_table(id uuid);

go :-
    odbc_connect(pglocal, Connection, [user(can), alias(pglocal), open(once)]),
    odbc_prepare(Connection, 'select id from test_table', [], Statement2),
    odbc_execute(Statement2, [], _);
    !.

Gives the warning:

Mapped unknown fSqlType -11 to atom

I think the only solution is to add a new case in odbc.c (please correct me if there is an easier one). Before I propose a pull request, I wonder which is more convenient:

• Add a case -11: line. Hard-coded but no dependencies.

• Add postgresql odbc as a dependency and add a case PG_TYPE_UUID: line. Looks better but adds a dependency.

Can you help me with that?

[JanWielemaker]

Hmmm. I guess you can fix your local problem with a patch. The problem is how to deal with extended SQL types in general. If we bind -11, we'll probably bind it incorrectly for some other database, no? In a portable way I guess the only options we have is to use the parameters argument of odbc_prepare (might already work?) or specify additional type mappings for the connection. Is that right? My ODBC is a bit rusty; long time ago I used that ...

[canbican]

There is no unique identifier database for odbc, so yes, there might be clashes. odbc_prepare isn't of use as far as I can see, since there aren't any parameters for the query.

I'm also into odbc on a pragmatic basis, so maybe the question is whether the warning can be suppressed. Is there any way one can "hook" Sdprintf messages from inside the prolog code? Or another way for redirecting them?

[JanWielemaker]

Reading the code, it indicates you can use the option silent(true) when creating the connection. We could also maintain a flag that will cause this message to be printed only once. B.t.w., I think this will work too

 odb_execute(Statement2, [atom], _).

[canbican]

silent(true) does the trick, I'll turn it on in production.

I think you meant odbc_prepare, where parameter types input to the query are provided, but it doesn't change the warning. The warning is displayed as soon as it encounters an unknown column. I believe the only way is to teach it the postgresql types by importing the header file with types from postgresql-odbc and checking if the connection is postgresql. But I don't think it's worth the effort until more databases require similar exceptions. So, the silent option looks sufficient for now.

BTW, odbc_execute crashes when it gets one variable where no parameters are provided in odbc_prepare:

...
odbc_prepare(Connection, 'select id from test_table', [], Statement2),
odbc_execute(Statement2, [atom], _),
...

SWI-Prolog [thread 1 (main) at Wed May 16 11:38:44 2018]: received fatal signal 11 (segv)
C-stack trace labeled "crash":
  [0] /usr/local/lib/swipl-7.7.10/lib/x86_64-darwin17.4.0/libswipl.dylib(save_backtrace+0xe5) [0x1046a4d45]
  [1] /usr/local/lib/swipl-7.7.10/lib/x86_64-darwin17.4.0/libswipl.dylib(print_c_backtrace+0x16) [0x1046a54c6]
  [2] /usr/local/lib/swipl-7.7.10/lib/x86_64-darwin17.4.0/libswipl.dylib(sigCrashHandler+0xb0) [0x1046a5340]
  [3] /usr/local/lib/swipl-7.7.10/lib/x86_64-darwin17.4.0/libswipl.dylib(dispatch_signal+0x25a) [0x10463fe9a]
  [4] /usr/lib/system/libsystem_platform.dylib(_sigtramp+0x1a) [0x7fff6f3d8f5a]
  [5] /usr/lib/system/libdyld.dylib(_Z21dyldGlobalLockReleasev+0x0) [0x7fff6f0ca149]
  [6] /usr/local/lib/swipl-7.7.10/lib/x86_64-darwin17.4.0/libswipl.dylib(PL_next_solution+0x8532) [0x1045cabf2]
  [7] /usr/local/lib/swipl-7.7.10/lib/x86_64-darwin17.4.0/libswipl.dylib(query_loop+0x141) [0x104620181]
  [8] /usr/local/lib/swipl-7.7.10/lib/x86_64-darwin17.4.0/libswipl.dylib(prologToplevel+0x51) [0x104620801]
  [9] /usr/local/lib/swipl-7.7.10/lib/x86_64-darwin17.4.0/libswipl.dylib(PL_initialise+0xf10) [0x1046651d0]
  [10] /usr/local/bin/swipl(main+0x9) [0x1045b5f49]
  [11] /usr/lib/system/libdyld.dylib(start+0x1) [0x7fff6f0ca015]
Running on_halt hooks with status 139
Killing 24324 with default signal handlers

But it's the subject of another bug :)