How to select the default source in injected SQL code?

Answered

Is there a way to define the default data source and schema for SQL injection?

 

I have several database sources configured in the Database window (they are all MySQL sources, I can see all tables from the Database window, and I can execute scripts against them)

If I create a JDBC string with a select statement, and then inject MySQL language into it(Alt + Enter > Inject Language or Reference > MySQL), the string automatically auto highlights recognising the select/from/where keywords.

However, it does not recognize the table or column names, so auto-complete does not work.   

String sql = "select * from account where name=?"

However, if I enter the schema before the table name, then it works:

String sql = "select * from dev_db.account where name=?"

I would expect to be able to setup somewhere at project level the default source and schema for SQL injection, but I cannot find where.

Thanks

 

5 comments
Comment actions Permalink

See https://www.jetbrains.com/help/idea/2016.1/data-sources-and-drivers-dialog.html#schemasAndTablesTab .

Resolve unqualified references To be able to reference the tables by their "short" names (e.g. in the database console or in your SQL files), select the corresponding check boxes. Otherwise, only fully qualified table names will be treated by IntelliJ IDEA as valid.

Note that the DBMS may reject short names when you execute the corresponding statement or statements.

1
Comment actions Permalink

Thanks Serge, that fixed it! :)

I just had to tick the "Resolve unqualified references" in the Schema tab of the data source configuration, and all my injected SQL started resolving the tables/columns.

As a suggestion, it would be nice if IntelliJ automatically asked you to select the default schema for unqualified references in the injected SQL if not setup or when it cannot find a table, i.e. by pressing Alt + Enter.

 

1
Comment actions Permalink

Yes, it's something that we plan to add soon.

1
Comment actions Permalink

Has this unqualified references option disappeared? I can't see it in 2017.1 (IDEA or PhpStorm)...

0
Comment actions Permalink

It is called SQL resolution scopes now, please check blog post about this update: https://blog.jetbrains.com/datagrip/2017/03/22/datagrip-2017-1-released/#tree It is located under Preferences | Languages & Frameworks | SQL Resolution Scopes.

1

Please sign in to leave a comment.