Stop reformatting of specific strings

Answered

We use %1$s, %2$s and so forth in our SQL queries. We can then replace these with whatever we want and can thus use the same query for different tasks.

When I reformat the code, it always changes these by adding spaces, e. g. "%1$s" turns into "%1 $ S" and thus breaks the code.

Is there a way to stop DataGrip to reformat special strings and ignore them, just keeping them as is?

 

Thank you!

6 comments
Comment actions Permalink

Which DBMS you are working with?

Could you please share some query examples?

0
Comment actions Permalink

We use DataGrip to retrieve data from an Amazon Redshift database. 

We also use R with an in-house developed function to load a SQL query into R and run that SQL query inside of R by connecting with the database. In this step, we replace the mentioned %1$s, %2$s and so forth with different values.

Example:

SQL query:
SELECT activity_date, count(*)
FROM usertable
WHERE country = '%1$s'

R code: 
data.frame <- SQLQuery( query = sprintf(query.sql, 'United States'))

The sprintf() function loads the query and then replaces the %1$s with 'United States' and runs it.

I write the SQL query in DataGrip and want it to be reformatted nicely there, so when you check out the query it looks nice. We can't run the query inside of DataGrip as we only replace the %1$s in R and DataGrip doesn't know what to do with it itself.

Now I want to stop DataGrip from reformatting  "%1$s" into "%1 $ S" because in the reformatted version R doesn't recognise it anymore and can't replace it.

Hope this helps

0
Comment actions Permalink

After testing, this happens only in some of the dialects. The PostgreSQL dialect changes this (it recognises the number as a number and the S as a keyword), while MySQL for example ignores it

0
Comment actions Permalink

Stefan Winters,

Could you share your Code Style settings via e-mail vasily.chernov@jetbrains.com with me? I can't reproduce the issue in MySQL and PostgreSQL in DataGrip 2020.1.4

 



0
Comment actions Permalink

Okay, sorry, I wrote an example in which it works perfectly, as it's in quotation marks. Sorry!

Here is an example that changes the format of the mentioned strings. %1$s can be substituted with additional columns, while %2$s will be changed to 'group by' the new column

SELECT
activity_date,
users.language,
%1$s
count(*) AS impressions,
count(DISTINCT impressions.user_id) AS exposed_users
FROM
impressions
JOIN users
on impressions.user_id = users.user_id
GROUP BY
1, 2 %2$s
1
Comment actions Permalink

Hello Stefan,

The query mentioned above could not be formatted correctly — because it could not be parsed properly (the parse has no info how to threat the placeholders). However, if remove commas from placeholders int the query it could be processed perfectly:

select activity_date,
users.language,
%1$s,
count(*) as impressions,
count(distinct impressions.user_id) as exposed_users
from impressions
join users
on impressions.user_id = users.user_id
group by 1, 2, %2$s

Then configure the user parameter using the pattern

%(\d\d*)\$s

And finally, please watch for the bug report https://youtrack.jetbrains.com/issue/DBE-11070 (the fix should be included into the next build).

 

 

0

Please sign in to leave a comment.