13 Replies Latest reply on Jan 31, 2019 6:56 AM by Christian Erlinger

    Create new SQL Worksheet on new Connection

    Christian Erlinger

      Greetings!

       

      I have a question regarding opening new SQL Worksheets which bothers me for some time now (but until now I haven't found the time to ask that question):

       

      When I create a new connection from the connections tree I also get a new SQL Worksheet for each new connection. So far so correct. This works for as long as I don't create another SQL Worksheet for an already open connection (Alt + F10 or Tools => SQL Worksheet or the Tool icon). The moment I do this when I open a new connection I do not get another new SQL Worksheet for that connetion - I have to do this manually.

       

      A quick glance at the settings for Databse => Worksheet I have those settings

       

      - Open a Worksheet on connect (checked)

      - New Worksheet to use unshared connection (unchecked)

      - Close all worksheets on disconnect (checked)

      - Prompt for Save file on close (unchecked)

       

      (the other settings don't seem to be related).

       

      Is there any setting I am missing? Normally I would want a new SQL Worksheet for every new connection no matter how many SQL Worksheets I opened for a previous connection. Or is this expected behaviour?

       

      Currently I am on SQL Developer 18.1.0.095 Build 095.1630 Running on Windows 10; I am using the one bundled with the JDK.

       

      If it is of any consequence my initial settings are quite old - the first Installation was SQL Developer 3 or something (maybe even from the raptor days - can't remember) and I migrated my settings for each and every step along the way. I can't recall the Worksheets behaving differently as I described previously though.

       

      TIA

      Christian

        • 1. Re: Create new SQL Worksheet on new Connection
          Glen Conway

          I believe this is normal behavior.  Here is an explainer:

           

          Opening a connection with the preference set to "Open a Worksheet on connect"

          1) Opens a worksheet as you describe.

          2) Then opening an unshared worksheet from that worksheet's tool bar icon, of course, also works.

          3) Next, if you close those worksheets but leave the connection open, double clicking on the connection node just

              collapses or expands that connection node in the Connection tree without opening a new worksheet.

           

          If, however, you disconnect the connection (an option in the context menu for that node) or all connections (context menu in the owning connections folder or the top level connections node in the Connection tree), then opening the connection (which is literally opening a new JDBC connection to the database) will open a new worksheet.

           

          Cheers

          • 2. Re: Create new SQL Worksheet on new Connection
            Christian Erlinger

            Hi,

             

            Thanks for your input. The behaviour you are describing seems logical - however I am not talking about opening the very same connection (a fact I accidentally did not share in my initial description); I open a different connection to a different database.

             

            Everything works well and continuous if you have only one connection (meaning different databases). If I got more connections to different databases the behaviour isn't continuous at least in my opinion. I tried a sequence of opening / closing worksheets and connections:

             

            Say I have defined connections to 4 different databases; a, b, c and d

             

            1.) I open up a connection to database a, a worksheet opens for that connection (expected)

            2.) I open up a connection to database b, a worksheet opens for that connection (expected)

            3.) I open a new worksheet for database a, a worksheet opens for that connection (expected)

            4.) I open a new connection to database c, no worksheet opens (why?)

            5.) I close the connection to database a: 2 worksheets for connection a are closed (expected)

            6.) I open a new connection to database d: a worksheet opens for that connection (expected)

            7.) I open a new connection to database a, a worksheet opens for that connection (expected)

            8.) I close all connections to all databases, all worksheets are closed(expected)

            9.) I open a connection to database a, a worksheet is opened (expected)

            10.) I open another worksheet for database a, a worksheet is opened (expected)

            11.) I open a connection to database b, no worksheet (why?)

            12.) I open a connection to database c, a worksheet is opened (and why does that happen now?)

            13.) I open a connection to database d, a worksheet is opened (same here).

             

            To me it seems, that the first new connection opened after I create a new worksheet doesn't get it's own worksheet by default. The next new connections do get new worksheets. It doesn't matter how many worksheets you additionally open for your connection, if you open one extra the next connection doesn't get one.

             

            If that is indeed expected behaviour or is caused by expected behaviour it is at least very strange expected behaviour

             

            thanks & regards

            Christian

            • 3. Re: Create new SQL Worksheet on new Connection
              Glen Conway

              In that case, I do not see the same puzzling behavior.  Here is an image of 4 open connections and 5 worksheets, with the unshared worksheet opened from the second connection:

              Capture.JPG

              This image was taken yesterday, and I should have included it in my prior post.  Your idea about something having gone wrong with your migrated user settings (migrated through multiple SQL Developer versions) could well be correct.

               

              Anyway, for full disclosure, in the image above all connections are to schemas in an 11g XE database, JDBC thin, JDK 1.8u201 on WIndows 10.

              1 person found this helpful
              • 4. Re: Create new SQL Worksheet on new Connection
                Christian Erlinger

                Hi,

                 

                I retried with a clean installation SQLDev 18.4 on Server 2016 - the behaviour is the same.

                 

                However - I found something: when I check

                 

                "New Worksheet to use unshared connection" (as you seem to have) in the Worksheet settings and the Behaviour is as expected - every new connection creates a new Worksheet.

                 

                As for now I am not sure if I want a new Connection for each and every Worksheet though...

                 

                cheers

                • 5. Re: Create new SQL Worksheet on new Connection
                  Glen Conway

                  Having all connections as unshared might cause your DBA concerns over resource usage, yes, plus you have to remember to commit more often if you want DML done in one connection to be visible in the other(s) for the same schema.

                   

                  My test was also on 18.4, with no migration of settings, but connections imported from an 18.3 export.  I remember due to my comment in another discussion on nested folders (Re: SQLDev 18.3 - Folder lost when exporting connections (Bug) ).

                   

                  And, no, I do not have "New Worksheet to use unshared connection" checked, so the behavior you see is even more mysterious:

                  Capture.JPG

                  • 6. Re: Create new SQL Worksheet on new Connection
                    Christian Erlinger

                    Glen Conway wrote:

                     

                    Having all connections as unshared might cause your DBA concerns over resource usage

                    No worries here - I am the DBA (we are talking about dev / test databases; I do not intend to add connections to any production system to my local SQL Developer installation).

                     

                    However;

                     

                    plus you have to remember to commit more often if you want DML done in one connection to be visible in the other(s) for the same schema.

                     

                    Yep, that is something that bothered / bothers me. Normally I use SQL Developer for writing Queries, but in some rare cases I spread DML over multiple worksheets and I do want to keep the atomicity of that transaction. That won't be possible with seprate connections for each worksheet.

                     

                    I applied all the worksheet settings you have according to your settings screenshot - still the behaviour is the same.

                     

                    I did run SQL Dev on Windows 7 previously - now on Windows 10 (also on Server 2016).

                     

                    I asked a Colleague of mine to test the behaviour as well - the described behaviour is the same. Windows 10 running SQL Dev 18.2.  Are you using the Version where the JDK is bundled with SQL Developer, or a standalone JDK?

                     

                    cheers

                    • 7. Re: Create new SQL Worksheet on new Connection
                      Glen Conway

                      I almost always use a standalone JDK.  For those rare times when it is necessary to go back to a very old release of SQL Developer, then I will download the zip with a bundled JDK. As noted above, I currently use JDK 1.8u201 (standalone), and do not see your issue on either 18.4 or 18.2 (just tested that).

                      1 person found this helpful
                      • 8. Re: Create new SQL Worksheet on new Connection
                        Christian Erlinger

                        Hi,

                         

                        thanks for your input. I'll retry with a standalone JDK and see if that makes any difference.

                         

                        cheers

                        Christian

                        • 9. Re: Create new SQL Worksheet on new Connection
                          Christian Erlinger

                          Hello,

                           

                          I retried using the blank current SQL Developer (18.4) with a separate JDK (1.8u201). Still the same behaviour. I created a screen video showing the behaviour (.zip file attached - should be a .webm video).

                           

                          cheers

                          • 10. Re: Create new SQL Worksheet on new Connection
                            Glen Conway

                            Thank you for the video.  I tend to avoid the Alt-F10 approach (or either of the 2 possibilities from the main toolbar SQL icon)

                            MainToolBarSharedSQLWorksheet.JPG

                            to get a shared worksheet, but instead rely on unshared worksheets (Ctrl-Shft-N or the Unshared Worksheet icon on the Worksheet's toolbar)

                            WorksheetToolBarUnSharedSQLWorksheet.JPG

                            So my automatic custom blinded me from understanding what you were saying.  You are absolutely correct, the behavior SQL Developer exhibits seems to fall a bit short here. 

                             

                            On the other hand, It seems a minor problem to me.  If you do not want an unshared worksheet, an alternative to opening a new worksheet on an already open shared connection is to split the existing worksheet.  Have you tried that?  Just right-click on the worksheet's tab and select Split Horizontally or Split Vertically.

                             

                            Cheers

                            1 person found this helpful
                            • 11. Re: Create new SQL Worksheet on new Connection
                              Christian Erlinger

                              Hi,

                               

                              in most cases when I use another (shared) worksheet for the same connection this is when the current worksheet is cluttered with SQL Statements I somehow will need again but need to do some other things. Mostly for debugging etc.

                               

                              Mostly in that cases I do some dml, then call a stored procedure, then dml, then do some queries, then...after a while I start a new worksheet if need be - and as I do not want to commit my modifications but want to see my modifications in the other worksheet I cannot use an unshared worksheet as I wouldn't see my uncommited data. Then I might want to check with a different test database and create a new connection - and that's where the new Worksheet is missing.

                               

                              You are absolutely correct - this is a minor nuisance but something that bothered me a little for some time but I never found it that annoying to post about it.

                               

                              Splitting the Worksheet doesn't exactly help that workflow as the 2 shared worksheets contain different SQL Statements which - at least in my installation - is not the case when I split the Worksheet. It is however something practical I'll keep in mind - thanks for the Tip.

                               

                              In any case - thanks for your continuing effort in trying to help me .

                               

                              cheers

                              • 12. Re: Create new SQL Worksheet on new Connection
                                Glen Conway

                                Obviously you know best how to deal with your specific workflow.  The split worksheet approach does indeed duplicate all statements in both worksheets, so that could be confusing.  It would be nice if the statements "belonging" to each instance of a split worksheet could have a different highlighting color or style (like Bold or Italic).

                                 

                                With regard to your comment "I somehow will need again", do not forget about another useful feature:  View -> SQL History

                                1 person found this helpful
                                • 13. Re: Create new SQL Worksheet on new Connection
                                  Christian Erlinger

                                  Hello,

                                   

                                  Thanks for the Tip. With "I somehow will need again" I mean in the current session (like select col1, col2, col3 from tab where <complex where>) for debugging purposes but nothing more. Statements which I need repeatedly mostly end up in the Snippets

                                   

                                  I'll leave the Thread open for a while - maybe someone else stumbles upon that behaviour as well and finds it annoying .

                                   

                                  cheers