Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Error in opening DB using QSqlQuery



  • 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:

    Qt and DSN

    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...



  • 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..


  • Moderators

    @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. ;)



  • @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.


  • Moderators

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

    So some more questions/ideas:

    1. 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?

    2. Does the odbc stuff require a plugin? Do you have that plugin loaded in your project?

    3. 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.



  • @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.



  • @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?



  • @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# 
    


  • 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.



  • @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?



  • @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"?



  • @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.



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



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

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


  • 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?



  • 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).



  • @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?



  • 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...



  • What file are you referencing above?



  • 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



  • @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.)



  • 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
    


  • 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")?



  • @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.



  • 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.


  • Lifetime Qt Champion

    • ..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.



  • 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.


Log in to reply