Trouble with view and proc code getting out of sync

@Aman Subhan

1. No, your source code is applied only when you click submit, it will show you the code to be executed, so that is not kind of background operation. Also, you can always review queries performed by DG in SQL log (Help/Show SQL log...)

For my first question from Devs, I havnt received any answer yet.

In reference to the point 2 in my last comment please see this feature screenshot from Devart DBForge

Can we have this kind of cache refresh capability?

Hello, everybody! In 2018.3 we are testing the new functionality. Please, give it a try.

1. Turn on Notify when the outdated object is opened
   

2. If the source code is changed from another place, you'll see the notification.
   

3. Choose to synchronise the database if you wish.

I thought I had a synchronization issue as described above, but found that I was not using the tool properly.  I was expecting it to act exactly like SQL Management Studio.  Below is what was doing.

1. Double click a procedure which then brings up a the CREATE PROCEDURE.
2. Change the CREATE to ALTER and then make my change.
3. Select all text and click run.
4. Expect my procedure to be updated.
5. It did not alter on the server. Forcing refresh did not help

The problem was that the file opened after double clicking procedure was not capable of running the script. This is what worked for me.

1. Double click a procedure which then brings up a the CREATE PROCEDURE.
2. Select all of the text and create a new scratch file...pasting the text to the scratch file.
3. Change the CREATE to ALTER and then make my change.
4. Select all text and click run.
5. The alter statement has now altered the procedure on the database.

I hope this helps someone.

Fortunately, for me anyway, most of the sync/desync issues have been resolved over the past several updates.

I haven't experienced that specific problem before. An alternative to selecting all text, creating a new scratch file and pasting it in might be to right-click the proc and select "SQL Scripts -> Generate DDL to Console". It essentially does the same thing but without creating a new scratch file with the copy/paste, removing a few steps. Hope that helps!

A Hebden6719

Your first approach is right, but don't change CREATE to ALTER. DataGrip can submit the code in a proper way.

Just make the changes you need and press the Submit button. It's with green arrow.

@Maxim, that has never worked for me if the proc already exists on the server. It fails because it executes a CREATE statement instead of ALTER. I always have to change it to ALTER in the script first. This is for MSSQL databases, not sure if that works for other types.

What version are you using? Please, see my screenshot.

Any updates on this issue?

I have version 2020.1.2 and problem still exists.

No warning about posting outdated version.

Cleaning cache every X minutes is not an option for me, database contains thousand of object, full sync takes almost hour.

Really there is no option to disable cache? 

All I need is just see actual content of object in database.

Same issue here. Is there any solution?

What is your database?

MSSQL 2000, driver JTDS 

Notification does not support that version of MS

I have the same problem with PostgreSQL. I have made changes to datagrip and they are not seen on the server. I can't trust DataGrip to make changes to functions.

Gad,
>I have the same problem with PostgreSQL. I have made changes to datagrip and they are not seen on the server. I can't trust DataGrip to make changes to functions.

Have you submitted your changes?

Hello, 

I have another problem: when I want to change the name of a stored procedure and then jump to editor, the code is generated with the old name, even if it is refreshed. 

Normally I expect that after rename, when I jump to editor the name of the procedure to be the new one.

IntelliJ IDEA 2020.1.2 (Ultimate Edition)
Build #IU-201.7846.76, built on June 1, 2020
Runtime version: 11.0.7+10-b765.53 x86_64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o.
macOS 10.15.5

Data Sources and Drivers: Microsoft SQL Server

Thank you!

Yes, known problem, please upvote https://youtrack.jetbrains.com/issue/DBE-6101

This still doesn't seem to work properly for me. I can click refresh all day long but it never refreshes. I have to update the function outside of the interface  because it changes my `create or replace` with a `drop and create` and I don't want that behavior. This means I can't use the green Submit button (using mariadb). When I run my `create or replace` outside of this window, I can't ever get it to refresh.

@Joe Phillips

Please provide more info. What is the exact change that leads to drop/create (we need everything but routine/view body before and after to reproduce it).
As for stale sources, you may have unsubmitted changes and looking at your modified version. Please attach sreenshot of the source editor. You may also check database changes toolwindow for pending changes.
Without that info we can not tell what is broken.
Also, what is DG version?
PS. It may be better to go to issue tracker as it is more suitable for that, you may attach files/track changes...

I've discovered that I am a ding dong. I was creating my function to the wrong database and therefore the original wasn't being updated as I thought it was.

Hi, I am a new user to Datagrip, and I also have the same lost trust feeling as described above a long ago, it is really frustrating!

How I understand Datagrip is kind of mixing 2 different usages, and I think that is causing the frustration.

One type of usage seems when you are developing a project (maybe in a branch), so you have multiple related database objects. If I compare this to a MS product, this is similar to the Visual Studio. Well, I do not use Datagrip like this, so I cannot tell too much about it.

However we might also debug issues, and run ad-hoc queries. In MS world this is the SSMS. SSMS also knows the objects for intellisense: it stores them in a local cache. If you want you can refresh the cache anytime, but in the end this cache is used only for making suggestions when you write a SQL statement.
BUT when you would like to open eg a view or a stored procedure SSMS does not use the cache, but gets the actual version from the server. This difference is a big pain in Datagrip for some of us. In Datagrip there is no "modify" in the right click menu like in SSMS, so we need to use the context menu > SQL Scripts > Generate DDL …, but that often mislead us.

In the above replies I cannot see that you are actually realising the two use cases, but seems just offering a one fits all solution, which does not work for both. I do not want to click on refresh button manually, nor I need to refresh ALL the objects, not to mention I do not want to wait for long till ALL objects are refreshed which I am not even care about. I am just interested in what I am about to peek into.

If you want to keep the current behaviour due to the consistency provided for devs, that is fine. But in that case please add a “Generate DDL from server …” to the context menu, which should not get the DDL from the cache, but should go right to the server for that particular object and get the actual version.

@Gabor,  I see, it's more UX issue. 

Yes, there is no Modify… menu item for the routines. We assumed a bit different approach. One need to double click or invoke CMD+B (Navigation | Go to DDL) to see sources. If sources are stale the notification would appear (Screenshot 1). 

In case of modification from your side IDE would mark routine with blue color and suggest migration script on the submission (CMD+K)(Screenshot 2).

To see changes use Show Changes (Screenshot 3)
 

Screenshot 1.

Screenshot 2

Screenshot 3