by David Crowther
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.
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
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.
The resolution is to access your SQL Database connection, leave the Provider/DSN blank and then choose the required Database e.g. MapThatV4.
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.
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.
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.
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.
Simply remove that value by deleting it and then press OK to the Advanced Settings.
Now when you try to CONNECT to your SQL Database it will work and it will list the Spatial Tables in your SQL Database:
You have now successfully edited (or created) a SQL Database Connection, bypassing the issue with the Provider/DSN value.