• Blog posts

    Bio

    by David Crowther

    QGIS_SQL_Connection_DC_01.png

    Question:

    I have an existing Database Connection in QGIS to my SQL Database, but when I edit the details e.g. point to a new Database,  the OK button is greyed out – how can I resolve this?

    Answer:

    In a recent release of QGIS (3.14) there is an issue where for SQL Database Connections you are now required to enter a Provider/DSN value or you cannot press the OK button to Save/Edit the Database Connection.

    See below where I have an existing SQL Database Connection to my TrainingDB – called Training_Start and I want to edit this Connection to point to another Database e.g. MapThatV4.

    QGIS_SQL_Connection_DC_02.jpg

    Let’s make the change to this SQL Database Connection:

    • Renaming the Connection Name to be – MapThatV4
    • Listing the Databases again and choosing the MapThatV4 Database

     QGIS_SQL_Connection_DC_03.jpg

    After making the required changes I now cannot press the OK button.

    The reason for this is that the SQL Connection cannot be edited and Saved without including a Provider/DSN value.

    However, SQL Database Connections don’t need the Provider/DSN value and adding one will break the Connection. If you add a Provider/DSN value e.g. TEMP and now try to press  list your databases it will throw an error message and you still won’t be able to press OK and Save/Edit the Connection details.

     

    QGIS_SQL_Connection_DC_04.jpg

    The resolution is to access your SQL Database connection, leave the Provider/DSN blank and then choose the required Database e.g. MapThatV4.

    QGIS_SQL_Connection_DC_05.jpg

    But to then save the changes, you must then add a temporary Provider/DSN value e.g. TEMP…. but don’t press List Database again, simply press OK.

    QGIS_SQL_Connection_DC_06.jpg

    This now means that you have edited/created a SQL Database connection, as you can now press OK. However, if you try to CONNECT and  add data from this connection it will fail as the Provider/DSN value breaks the connection.

    QGIS_SQL_Connection_DC_07.jpg

    To resolve this issue, we need to remove the Provider/DSN value, but we can’t do this via the SQL Connection Details as we can’t press OK without the value!!

    The solution is to remove the Provider/DSN value using the Advanced Options in your QGIS Settings.

    In QGIS choose  Settings > Options and in the Options window choose the ADVANCED pane.

    QGIS_SQL_Connection_DC_08.jpg

    As QGIS states…. Be very careful when changing the Advanced Options…… so choose the I will be careful button.

    In the Advanced Settings Editor, find the SQL Database Connection – in this case MapThatV4. Open the settings and find the SERVICE line item. Here we can see that the Service String (the Provider/DSN) has a value of TEMP.

    QGIS_SQL_Connection_DC_09.jpg

    Simply remove that value by deleting it and then press OK to the Advanced Settings.

    QGIS_SQL_Connection_DC_10.jpg

    Now when you try to CONNECT to your SQL Database it will work and it will list the Spatial Tables in your SQL Database:

    QGIS_SQL_Connection_DC_11.jpg

    You have now successfully edited (or created) a SQL Database Connection, bypassing the issue with the Provider/DSN value.