ERROR: ODBC: State H: [unixODBC][

Help!
1

Hi, I’m using swi-prolog under macos 12.3.
The .odbc.ini is :

[ODBC Data Sources]
ftta-projects = MySQL ODBC 8.0 Unicode Driver
test          = MySQL ODBC 8.0 Unicode Driver

[ODBC]
Trace         = 0
TraceAutoStop = 0
TraceFile     =
TraceLibrary  =

[ftta-projects]
Driver = /usr/local/mysql-connector-odbc-8.0.28-macos11-x86-64bit/lib/libmyodbc8w.so

[test]
Driver = /usr/local/mysql-connector-odbc-8.0.28-macos11-x86-64bit/lib/libmyodbc8w.so
SERVER      = localhost
PORT        = 3306
DATABASE    = ftta-projects

➜  ~ odbcinst -j
unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/vc/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

➜  ~ cat /usr/local/etc/odbcinst.ini

not content in /usr/local/etc/odbcinst.ini .

However When I call odbc_connect(test, C, [user('root'), password('password')]). , it reports:

ERROR: ODBC: State H: [unixODBC][
ERROR: In:
ERROR:   [10] odbc:odbc_connect(test,_3598,[user(root),...])
ERROR:    [9] toplevel_call(user:user: ...) at /usr/local/lib/swipl/boot/toplevel.pl:1162

What is wrong with the odbc?
Thanks very much.

2

I think you should try to turn tracing on. Check if this works and writes something more into the log file (error code H is missing actual info)

1 Like
3

Here is the configuration file of odbc:

(odbc) ➜  ~  odbcinst -j
unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/vc/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

I open the /usr/local/etc/odbcinst.ini file, I find that it is empty.
I add the following content to it.

[ODBC]
Trace = yes
TraceFile = /Users/vc/odbc.log

Then I rerun odbc_connect(test, C, [user('root'), password('password')]).
The same error still occurs, but the odbc.log file is not found.

4

I run the following code
catch(odbc_connect('test', C, [user('root'), password('password')]), E, true), write(E). to get the exception, it returns

error(odbc(H,1045,[unixODBC][),_130)
E = error(odbc('H', 1045, "[unixODBC]["), _).
5

The Wiki page SWI-Prolog connecting to PostgreSQL via ODBC might be of help. While it is not specific to your setup it does take smaller steps in verifying things along the way. Also it uses /etc/odbc.ini as opposed to .odbc.ini.

HTH

EDIT

In trying to identify where in the source code the error is being thrown found

Now knowing the database is MySQL and the error number seems to be 1045 with a Google search found many pages like this

6

I can login into the database with mysql -uroot -ppassword, which seems MySql database allows to login via localhost. But I still cannot connect via swiprolog

7

Did you check

  1. That you are using the correct database name. postgres uses \l to list the valid databases, I don’t know what MySQL command would need to be.
  2. That your user id has access to the database?
  3. That MySQL is listening on the correct port using netstat?
  4. That you can log in using iusql which can check just the ODBC connect without using the SWI-Prolog part.
8

Yes， I have checked. The database name is correct, the userid is correct, mysql is listening on 3306, and I can query mysql with other database tools.

9

Did you check the ODBC setup via iusql?

10

On Ubuntu, I have my DSNs defined in /etc/odbc.ini

[blog]
Driver      = MySQL
Description = test ODBC 
DATABASE    = blog
USER        = ********
PASSWORD    = *********
PORT        = 3306
SOCKET      = /var/run/mysqld/mysqld.sock
SERVER      = localhost
...

?- odbc_connect(blog,C,[]).
C = '$odbc_connection'(36329168).
1 Like
11

It’s very strange. Today I make a new DSN which use MySQL ODBC 8.0 ANSI Driver instead of the MySQL ODBC 8.0 Unicode Driver. Now the ~/.odbc.ini file contains two DSNs.

➜  unixODBC-2.3.9 cat ~/.odbc.ini 
[ODBC Data Sources]
test  = MySQL ODBC 8.0 Unicode Driver
test2 = MySQL ODBC 8.0 ANSI Driver

[ODBC]
TraceLibrary = 

[test]
Driver   = /Library/ODBC/libmyodbc8w.so
SERVER   = localhost
PORT     = 3306
DATABASE = ftta-projects
USER     = root
PASSWORD = *******

[test2]
Driver   = /Library/ODBC/libmyodbc8a.so
SERVER   = localhost
PORT     = 3306
USER     = root
PASSWORD = *******
DATABASE = ftta-projects

Then I call odbc_connect with the two DSNs.
odbc_connect(test, C, []). reports the following error:

ERROR: ODBC: State H: [unixODBC][
ERROR: In:
ERROR:   [10] odbc:odbc_connect(test,_5560,[])
ERROR:    [9] toplevel_call(user:user: ...) at /usr/local/lib/swipl/boot/toplevel.pl:1162

And odbc_connect(test2, C, []). returned successfully.

?- odbc_connect(test2, C, []).
C = '$odbc_connection'(105553150193936).

So what is the difference between MySQL ODBC 8.0 Unicode Driver and MySQL ODBC 8.0 ANSI Driver? Why MySQL ODBC 8.0 Unicode Driver cannot be used in my projects?

1 Like
12

Who knows? It has been suggested here before: first try using the commandline tools that come with the ODBC implementation. Also note that the SWI-Prolog ODBC driver can be compiled against iODBC and UnixODBC. The pre-build binary you can download from SWI-Prolog.org has been compiled against UnixODBC. The commandline tool of UnixODBC is isql as mentioned here before. iusql is its Unicode cousin. These tools have debug options that may hint at what is wrong.

1 Like
13

Just a wild guess. It might be that the one or both the user name and password are in the wrong encoding when being passed.

On Windows I would use one of the tools from Sysinternals. As I don’t use Mac I don’t know the equivelent.

14

I prepare two DSNs, one with the ANSI driver and the other with unicode driver.

➜  cat ~/.odbc.ini 
[ODBC Data Sources]
test  = MySQL ODBC 8.0 Unicode Driver
test2 = MySQL ODBC 8.0 ANSI Driver

[ODBC]
TraceLibrary = 

[test]
Driver   = /Library/ODBC/libmyodbc8w.so
SERVER   = localhost
PORT     = 3306
DATABASE = ftta-projects
UID      = root
PWD      = toorroot
USER     = root
PASSWORD = ******

[test2]
Driver   = /Library/ODBC/libmyodbc8a.so
SERVER   = localhost
PORT     = 3306
USER     = root
PASSWORD = ******
DATABASE = ftta-projects

The isql command works fine with the DSN of MySQL ODBC 8.0 ANSI Driver:

➜ isql -v test2
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

but iusql fails with test2 of MySQL ODBC 8.0 Unicode Driver:

➜ iusql -v test
[
[ISQL]ERROR: Could not SQLDriverConnect
15

The point some us are trying to make is that if you get an error from either or both isql or iusql then the problem is not with SWI-Prolog it is with something else.

Since the problem is not with SWI-Prolog you are more likely to get better help some where else. :slightly_smiling_face:

16

Thanks, SWI-Prolog now works with ANSI Driver