sql resolution scope: I don't understand what is wrong.
I have some code with some queries in it, in which the table is not recognized. I've tried several settings and I just don't understand what I'm supposed to do to make it work.
I my setup I have a lot of *global* database sources, both mysql and postgresql.
Here's an example of code that czn't resolve the table name :
This is the error message :
Clicking on "Choose shema" does nothing. Clicking on "Choose schema" in this menu does also nothing; Note that if offers to "edit mysql fragment" but here I'm connecting to a postgresql database.
If I "Go to SQL Resolution Scopes" I get :
Here I have tried :
- Setting only the public shema on the test database on the local server
- Setting "All databases on the local server"
- Setting "All datasources"
None of those tries seem to trigger any change, "Choose schema" doesn't work.
At some point "Choose schema" did offer me something, but it was only a list of Mysql datasources, confirming that Pycharm seems to belive I'm using mysql here (but this code is for a postgresql database.) and this is my config for SQL Dialects :
Note that changing "Global SQL Dialect" to PostgreSQL doesn't change anything.
If I "Edit Mysql Fragment" Code Pycharm really think it's a Mysql fragment :
Now if I change Mysql to Postgresql it changes :
But If I select a session opened on the right database, now it doesn't work again :
Although the table really exists and is recognized in the console :
So I'm a bit lost here, why does it have to be so complicated ?
Please sign in to leave a comment.
Does it happen only when the query is in the string inside Python code or can be reproduced in a query console?
Have you tried setting Project SQL Dialect in File | Settings | Languages & Frameworks | SQL Dialects?
It happens when the query is inside Python code
I tried setting SQL Dialect, there is a screenshot in the first post, here is the latest setting :
Also : still in python code, when I type in a string "SELECT * FROM" the autocompletion only shows completion from MySQL datasources.
The table is in the database, to show this I had to make a montage
Could you please check if the problem reproduces in a new project but with the same DB?
Thank you for your interest in my problem ! I did the test with a brand new project, the problem reproduces.
In case it matters, here's some details on my version :
PyCharm 2021.2.2 (Professional Edition)
Build #PY-212.5284.44, built on September 14, 2021
Licensed to SAS Marcel Robbez-Masson / David Virebayre
Subscription is active until October 24, 2021.
Runtime version: 11.0.12+7-b1504.28 amd64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o.
Linux 5.4.0-88-generic
GC: G1 Young Generation, G1 Old Generation
Memory: 4096M
Cores: 12
Registry: documentation.show.toolbar=true, search.everywhere.settings=true, ide.balloon.shadow.size=0, ide.images.show.chessboard=true
Non-Bundled Plugins: net.vektah.codeglance (1.5.4), de.netnexus.camelcaseplugin (3.0.8), XPathView (212.4746.57), in.oneton.contrib.plugin.angular.material (0.11.0), com.intellij.ideolog (203.0.27.0), mobi.hsz.idea.gitignore (4.2.0), com.andrewbrookins.wrap_to_column (1.7.0-SNAPSHOT), mobi.hsz.idea.latex (0.2), String Manipulation (8.17.203.000.0), nl.rubensten.texifyidea (0.7.11), com.dubreuia (2.2.0), intellij.prettierJS (212.5080.8), com.koxudaxi.pydantic (0.3.8), com.leinardi.pycharm.mypy (0.11.2), cn.yiiguxing.plugin.translate (3.2.0.1), org.tonybaloney.security.pycharm-security (1.24.2), com.leinardi.pycharm.pylint (0.12.2), net.seesharpsoft.intellij.plugins.csv (2.17.1), dev.ngocta.pycharm-odoo (2021.2.6), com.github.holgerbrandl.pasteimages/ (1.2.7), GrepConsole (11.11.211.6086.0)
Current Desktop: ubuntu:GNOME
hi, to make resolve working SQL Dialect in file should be the same as database type you want to use objects from.
SO, if you have MySQL data source, please set file dialect to MySQL and attach corresponding session.
If you have a file where you want to work with PG, please set it up accordingly.
I want to work with postgresql for this project and it is alread set to postgresql if you have a look at the screenshot.
Edit: screenshots after changing Global SQL Dialect (I thought project SQL dialect would be enough) and project mapping to only look for the postgresql database. Those 2 change didn't change the problem
and when you attach PG session to this file it still does not work?
I'm not sure how to attach the session but if I try to execute the request, it shows only the Mysql sources to connect to
See in this short video :
I have a 6mb mp4 file that shows everything, should I upload it somewhere ?
I have uploaded the fullscreen demo of my problem here : https://youtu.be/unWn0r6UGlY
please try to specify SQL dialect for file in File | Settings | Languages & Frameworks | SQL Dialects. I mean, add this file just like in resolution scopes. and restart IDE
I did just that.
After it was done, the problem is still here. I tried all the things I did in the video, behavior is the same.
I've managed to make it work by :
This is a sad way to solde the issue. For this project I need to switch database / schema sometimes, and or access other servers. I don't dare adding other data sources in case it breaks the highlighting.
I love pycharm but this was a bit disappointing.
After a few more testing, if I add back another source that is mysql, the highlighting gets broken again.
Hello,
I have the same problem. When there is an issue with my embedded SQL, the "Choose Schema" context action does nothing. It happens in Typescript and Python. Most of the database related context actions behave that way (i.e. they don't work). Can this be fixed, please? How can I help to isolate the issue?
Thanks,
Hsns
I encountered the same issue. I was able to fix it by hitting CMD+Enter and selecting "Go to SQL resolution Scopes" (or something similar; it no longer shows up). In the dialog window that opens, I removed the exception for the current file while selecting the appropriate default scope at the top.
Not working for me in Rider 2021.3.2 as well.
FWIW, I was eventually able to get this working for Postgres with RubyMine.
First, I added an entry in the Database tool for the database I was using, and test it using the Query console. This took some figuring out, as I had to not only add the database server and select a database, but select the "public" schema within that database. At some point I was prompted with something like "the schema has not been introspected" and given an option to introspect it, which I did.
Then, after reading the above thread, I was able to find Preferences / Languages & Frameworks / SQL Resolution Scopes, where I had to first uncheck 'All Data Sources', then look through the tree for the 'public' schema of the database I was using;
It would be nice if there was a whole lot more feedback as to what is and isn't working when you tick and untick these boxes — it really seems like the default "All Data Sources" ought to do something useful, but as far as I can tell, it doesn't. It's also counterintuitive that you have to explicitly uncheck "All Data Sources" to check the other boxes, esp. as they don't appear disabled (they just don't work).
All Data Sources resolution scope is straight up broken in all the products.
With inclusion of DataGrip to the lineup, it's a huge ridiculous disappointment.
I had a similar issue and resolved it by "Invalide Caches". I have a pretty common case I think: I have a database (MariaDB) and also use another Database (Oracle) for a few Queries. For that I have class OracleRepsitory, that should just use another Schema.
I tried everything, "SQL Solutions Scope", "SQL Dialects", etc. - nothing worked until I invalided the caches.
Can confirm this is broken many places and has been for several years.
I'm primarily a PhpStorm and DataGrip user (although have also used IntelliJ, CLion, AppCode and WebStorm).
Cxjohnson, could you please share more details and steps to reproduce?