Error in opening DB using QSqlQuery

mzimmers

Hi, all -

I apologize in advance that this question is going to be long, and probably not entirely sensible.

I'm going through a tutorial right now that uses the QSqlDatabase and QSqlQuery objects:

The author of the tutorial works on Windows, and I'm trying to make this work on Linux (Debian Jessie). As someone new to Qt, SQL and Linux, this is proving a bit of a sporting endeavor.

In order to make this build, I first installed unixODBC's libraries and then built the QODBC driver. Both seemed to work (eventually).

When I run this code:

    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setHostName("localhost");
    db.setDatabaseName("MySQL-test");
    bool ok = db.open();

I get this error message:

false   "[unixODBC][Driver Manager]Data source name not found, and no default driver specified QODBC3: Unable to connect"

I'm fairly sure the problem is in one of my configuration files. I've looked at many examples online (not all of which agree with each other), and this is what I came up with for mine:

mzimmers@debian:/etc$ more odbc.ini
[MySQL-test]
Description     = MySQL test database
Trace       = Off
TraceFile   = stderr
Driver      = MySQL
SERVER      = 127.0.0.1
USER        = root 
PASSWORD    =
PORT        = 3306
DATABASE    = test
mzimmers@debian:/etc$ more odbcinst.ini
;
;  odbc.ini configuration for Connector/ODBC and Connector/ODBC drivers
;

[ODBC Data Sources]
myodbc3     = MyODBC Driver DSN

[MySQL-test]
Driver       = /usr/local/lib/libodbc.so
Description  = Connector/ODBC  Driver DSN
SERVER       = localhost
PORT         =
USER         = root
Password     =
Database     = test
OPTION       = 3
SOCKET       =
mzimmers@debian:/etc$

Here's another data point:

mzimmers@debian:/etc$ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/mzimmers/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
mzimmers@debian:/etc$

So, I'm sure there are multiple errors in my configuration, but I'm in over my head on this one. Can anyone give me an idea of what I might be doing wrong?

Thanks so much...

hskoglund

Hi, I have QSqlDatabase etc. working in Linux when connecting to a MS SQLServer, it should work for MySQL as well. I remember I used an .odbc.ini file in my home directory to get it working.

P.S. If there's any help. have a look at my Qt dblib on GitHub, around lineno. 100..

ambershark

@mzimmers Can we see the content of .odbc.ini and /etc/ODBCDataSources?

The error is saying your datasource MySQL-test is not valid. I want to see how you set up your MySQL-test data source.

Keep in mind linux is usually case sensitive in most areas which is something you won't be used to if you're coming from windows. So make sure your dsn isn't like mysql-test or something that doesn't match case. I'm reaching here as I don't know if the DSN is case sensitive using QODBC or not, but it doesn't hurt to check. ;)

mzimmers

@ambershark my odbc.ini file is listed out in my first post. There is nothing in etc/ODBCDataSources. Perhaps this is part of my error, but I didn't find anything in the unixodbc documentation that spoke of populating this directory. Am I supposed to copy a file there?

hskoglund: the way I read the documentation is that a ~/.odbc.ini file overrides the /etc/odbc.ini, but isn't necessary. Of course, what I read could be incorrect.

It's also very possible that I'm missing something conceptual as well, but I was under the impression that the two files I listed above were all I needed.

ambershark

@mzimmers Oops missed that output in the original post. Looks set up properly to me.

So some more questions/ideas:

I've never used unixODBC, is there a server or something that needs to run or is it just a library you link? If it is linked do you have it linking properly in your build?
Does the odbc stuff require a plugin? Do you have that plugin loaded in your project?
Is mysql running? Can you access the database outside of your application properly?

The error is a bit confusing as it's saying multiple things. DSN not found. Also there is no default driver. And finally unable to connect. So it could mean it can't connect to the database and the rest is fine, or that it can't find the odbc driver, or that your DSN isn't set up properly.

I'm kind of just throwing out ideas as I've never tried the unixODBC libraries. If you don't get a satisfactory answer and I get some spare time I could install it and get a quick tutorial or list of steps for you to make it work. That's a decent amount of work so we'll save that as a last resort. ;)

Oh and you can set QT_DEBUG_PLUGINS=1 before you run your application and see if the QODBC driver is not being loaded for some reason (and what that reason might be). To do this you would QT_DEBUG_PLUGINS=1 ./myapp or set it for the life of that shell with export QT_DEBUG_PLUGINS=1 then run your app as normal ./myapp.

mzimmers

@ambershark I think you're onto something with your first question. I took a closer look, and discovered that unixODBC is merely an API. Using it requires a driver. I chose MySQL Connector/ODBC, and followed the instructions listed here:

installation

and here:

configuration

But I'm still getting the same error. It's not clear to me how the program in Qt picks up these libraries. There's no mention of them in the .pro file, but I assume it's finding them, or I'd be getting some kind of linker error.

I'm also not sure that the Qt program is getting my environment variables ODBCSYSINI and ODBCINI.

Thanks for any other suggestions.

hskoglund

@mzimmers Actually it's not Qt that picks up the MySQL .so dll, that's what unixODBC suppose to do for you (i.e. a 3-step process: Qt's odbc plugin -> unixODBC -> /usr/local/lib/libmyodbc3.so)

Perhaps unixODBC does not load the libmyodbc.3.so file correctly, does your odbc.ini specify /usr/local... or something else?

mzimmers

@hskoglund: thank you for the clarification. But where did you get the filename libmyodbc.3.so you mention above? I have no such file on my system.

I only have two ODBC configuration files on my entire system with anything in them:

root@debian:/etc# pwd
/etc
root@debian:/etc# ls odbc*
odbc.ini  odbcinst.ini
root@debian:/etc# more odbc*
::::::::::::::
odbc.ini
::::::::::::::
[MySQL-test]
Description     = MySQL test database
Trace       = Off
TraceFile   = stderr
Driver      = MySQL
SERVER      = 127.0.0.1
USER        = root 
PASSWORD    =
PORT        = 3306
DATABASE    = test
::::::::::::::
odbcinst.ini
::::::::::::::
;
;  odbc.ini configuration for Connector/ODBC and Connector/ODBC drivers
;

[ODBC Data Sources]
myodbc3     = MyODBC Driver DSN

[MySQL-test]
Driver       = /usr/local/lib/libmyodbc5a.so
Description  = Connector/ODBC  Driver DSN
SERVER       = localhost
PORT         =
USER         = root
Password     =
Database     = test
OPTION       = 3
SOCKET       =
root@debian:/etc#

hskoglund

I got the name from your link above configuration in it is an odbc.ini example file, and I know from my struggles with MSSQLServer that that Driver= <path to dll/.so file> line is needed, so that unixODBC can load the dll properly.

mzimmers

@hskoglund said in Error in opening DB using QSqlQuery:

I got the name from your link above configuration in it is an odbc.ini example file, and I know from my struggles with MSSQLServer that that Driver= <path to dll/.so file> line is needed, so that unixODBC can load the dll properly.

Interesting. I thought the documentation was just out of date, and that the "3" represented a version that is now 5. (The "a" refers to ANSI vs. Unicode.) Not so?

hskoglund

@mzimmers Yes you're right, it's libmyodbc5a.so now, so unixODBC should load it.
Next pain point is the [DSN] stuff, do you set QSQLDatabase's .setDatabaseName to "MySQL-test"?

mzimmers

@hskoglund : yes I do:

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setHostName("localhost");
    db.setDatabaseName("MySQL-test");
    bool ok = db.open();

And this open() produces the error that I mention above.

hskoglund

Hmm, just guessing, but have you tried moving your odbc.ini to your home directory?

hskoglund

Another guess: try changing the first part of your odbc.ini to:

[ODBC Data Sources]
MySQL-test  = MyODBC Driver DSN

mzimmers

Just did...same results. I also renamed it to ".odbc.ini" with the same results.

The tutorial author doesn't mention it, but the configuration instructions mention some variables:

export ODBCINI=/usr/local/etc/odbc.ini
export ODBCSYSINI=/usr/local/etc

I've done this, except they point to /etc. But I'm wondering whether this is correct.

Regarding your latest suggestion, should that change be in odbc.ini or odbcinst.ini?

hskoglund

Hi, about [ODBC Data Sources] try putting those 2 lines in both files.

I've googled a bit, and https://lists.mysql.com/myodbc/6311 says that the default location for odbc.ini and odbcinst.ini is /usr/local/etc, so try putting the files there (maybe unixODBC does not see your exports).

mzimmers

@hskoglund : did the insertion of those two lines, and copied the files to /usr/local/etc. Same results.

Configuring this isn't exactly easy, is it?

hskoglund

Believe me, it's even trickier on the Mac, I had some grand plans to make my db library support Windows, Linux and Mac (that's what Qt is all about after all). But to get ODBC work on Linux took maybe 2 or 3 nights, but on the Mac; easily one week :-(

Anyway, perhaps it's time to try another .so file, that post above mentions libodbcmyS.so...

mzimmers

What file are you referencing above?

hskoglund

Hi more google tips
it mentions running odbcinst, e.g. "odbcinst -i -d -f /home/username/odbc/mysql"
I don't remember having to do that for SQLServer but perhaps MySQL requires it