Error in opening DB using QSqlQuery

[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

[hskoglund]

@mzimmers About libodbcmyS.so that I mentioned above, it seems for MySQL there are actually 2 kinds of .so files: one driver flavored and one setup flavored, and that libodbcmyS.so is the latter.
Also, it seems both flavors are needed to be listed in odbcinst.ini.
Sorry for the confusion. Actually that last link seems pretty helpful, not that old (only 10 years!). Also it mentions running isql to test the connection (I remember the isql program as being very helpful for my debugging.)

[mzimmers]

OK, well I still don't have a libodbcmyS.so. I do have a libmyodbc5S.so; could this be it? And, if so, how am I supposed to list both in the obdcinst.ini file?

Am I using the isql command correctly here?

root@debian:/etc# isql MySQL-test
[ISQL]ERROR: Could not SQLConnect

[hskoglund]

Yeah, the isql command seems right and the error message seems familiar too :-) (And the error is outside the realm of Qt)

About Qt and MySql: you do know there is a probably simpler way to connect to your MySQL db: not going through the ODBC API, but instead loading Qt's plugin for MySQL directly, by using ::addDatabase("QMYSQL")?

[mzimmers]

@hskoglund yeah, I've already gotten the QMYSQL plugin working. I'm working with the ODBC stuff for the sake of my education, in case my next job lands me in a Windows environment.

It's strange how all the web pages on ODBC are so old...it is considered obsolescent technology now?

Anyway, I think I'm done for the night. I'll tackle this again tomorrow. Thanks for all your help.

[mzimmers]

I'm going to do some clean up before I attempt more troubleshooting. I've removed the duplicates of the .ini files, so they only exist in /etc now. Also, something's wrong with my configuration:

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

How do I delete those incorrect entries? I tried the uninstall, but that doesn't seem to work.

Thanks.

[mrjj]

• ..ODBC are so old...it is considered obsolescent technology now?

No, but the web made it less important as the browsers are now the clients and they need not the virtualization/abstraction that ODBC offer since its all html/json etc so maybe its not so high profile anymore.

[mzimmers]

Well, I think I have it solved. Not sure what I did differently, but here are the relevant files:

/etc/odbcinst.ini

[MySQL-test]
Driver=/usr/local/lib/libmyodbc5a.so
Setup=/usr/local/lib/libmyodbc5s.so
Description=Connector/ODBC Driver DSN
FileUsage=1
UsageCount=1

/etc/odbc.ini

[MySQL-test]
Description=MySQL test database
Driver=MySQL-test
SERVER=127.0.0.1
USER=root
PASSWORD=<PASSWORD-GOES-HERE>
PORT=3306
DATABASE=test
Option=3

Notes:

1. I created these files using templates with the exact same data in them, and calling "odbcinst -i -s -f odbcinst.template" and "odbcinst -i -f -f odbc.template".
2. I had to put the password to my root account in my odbc.template file. Not sure I like this, but I don't know what the alternative would be.
3. there is nothing in the directory /etc/ODBCDataSources, and I don't have an .odbc.ini file in my home directory.
4. the two pages that helped the most are:
   unixodbc
   debian
   And, it works:

mzimmers@debian:/etc$ isql MySQL-test
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>  

Thanks to everyone who helped...as I said, I'm not sure what I did between the start and the end of this, but it's working now, both with isql and my Qt example.