Wednesday, August 26, 2020

Difference in Run SQL Scripts

acs run sql scripts content assist

I have had the latest version of Access Client Solutions, ACS, 1.1.8.5 for just over a week. I use "Run SQL Scripts" so much I leave it open all day. It is my favorite part of ACS, as I often start with a simple SQL statement, and then copy that to make it ever more complex until I have the final form of the statement I want to use. Something that is hard to do using the STRSQL interface.

There are several ways to start "Run SQL Scripts". The most common way is from the 5250 emulation display. I will find the option to start it in the "Actions" menu.



Or there is an icon for it on the line below the menu.



I was first introduced to the "Content Assist" as part of the "Run SQL Scripts" in the last release of ACS, 1.1.8.4, and have been sharing it with people learning SQL as a way to get help started. All I had to do was press Ctrl+Space and a list of possible statements would be displayed.


Click on image to open larger version

From a list I could just select the statement I wanted to build…


Click on image to open larger version

And when I pressed Enter the example SQL statement would be inserted into my "Run SQL Scripts" window.



I was discouraged to find that with the new release this functionality was removed. If I had 1.1.8.4, and then installed the new release what I have described above may still work, as it is residual functionality from the older release. But if you are a brand new install of 1.1.8.5 you will have to have some SQL experience to know the type of statement you want to create.

One good improvement is that I can also use F4 to enter the "Content Assist", as well as using Ctrl+Space.

Let me go to an example. I want to create a Select the list only the first field in the DDS file TESTFILE. I would type in the most basic Select statement, and if I could not remember the name of the file I could just use the "Content Assist" to generate a list of files that start with the letter "T".


Click on image to open larger version

I just move the cursor to the file I want and press Enter, and the file name is inserted into my SQL statement.



To get a list of the fields I can select I move the cursor next to the asterisk ( * ) and press F4. The available fields can be displayed in one of two ways either by their "SQL names", long name, or their "System names". You can switch between the two by pressing F11.


Click on image to open larger version
 

Click on image to open larger version

In a DDS file the only time I would see a long name is if a field had been defined with an alias.

The available option is what I put a green box around, "Switch to Syntax Proposals". When I press F4 or Ctrl+Space the syntax proposals are displayed.


Click on image to open larger version

To exit any of the "Content Assist" windows just press the Esc key.

In my example I only wanted the first field from the file, so I select it and when I press Enter my SQL statement is updated.



Above I have two SQL statements the first using the "SQL Name" for the first field, and the second uses the "System Name" of the field. Which is returned depends upon which window I selected the field from.

I went to end of the first statement pressed F4, then the Ctrl+Space to go to the "Syntax Proposals" so I can select the where clause.


Click on image to open larger version

Press Enter and it is added to the end of the statement, on the next line.



I move the cursor after the WHERE and press F4 for the "Content Assist", and I select the second field.


Click on image to open larger version

And press Enter to insert the name of the field.



I completed the where clause manually. I am now ready to add the order by clause. I press F4 and select "ORDER BY". When I press Enter "ORDER BY" has been added to the statement.


Click on image to open larger version

I need to field that the results will be sorted by. I press F4 and a list of the file's fields is displayed. I select the third field and press Enter.


Click on image to open larger version

I have created a fully functional SQL select statement without having to understand the syntax.



I can use the "Content Assist" to create an insert statement. As this is just an example statement I would need to replace V1 and V2 with the values for the three fields in the file.



Did you know you can run CL commands in this interface too?

All I need to do is enter "CL:" and then the command. I can press F4 and a window appears for the command's parameters.


Click on image to open larger version

Another under-utilized part of "Run SQL Scripts" is the "Insert from Examples". You will find it in the "Edit" menu, or Ctrl+I which I can never remember.


Click on image to open larger version

There are all kinds of examples for all types of statements. You can even add your own.


Click on image to open larger version

These examples are what the release 1.1.8.4 woould returned when you selected the type if SQL statement you wanted.

 

This article was written for Access Client Solutions release 1.1.8.5 only.

4 comments:

  1. You mentioned you leave the SQL Scripts session open for most of the day. There is a caveat in doing that and has to do with record locks. We have found that when we run a select or update against a single record in production SQL puts a lock on the applicable record and when a program attempts to access that record it bombs due to a record lock situation. It took us a while to definitively pin point the issue and figure out it was ACS run sql scripts that was causing it. We are currently running Version: 1.1.6.0 Build 6320 and prior to this version we never had the issue. Perhaps it's been fixed in the newer version 1.1.8.4 that you're using but anyone with 1.1.6.0 may have an issue with record locks. It only effects the record selected pertaining to the last SQL statement ran. If another sql statement is ran the lock is released, but the lock does remain up until either another sql statement is ran or the sql scripts session is ended/terminated. It is a very specific bug and only happens, as mentioned, when the sql statement pertains to a single record selection/update. This is a significant bug and did cause some havoc for us until we figured it out and hopefully IBM has fixed it in the newer release.

    ReplyDelete
    Replies
    1. That sounds like you're running with your isolation level and commitment control. Have a look at JDBC Configuration on the System tab. I have "Isolation level" set to No Commit. If you have something else, you may need to have AutoCommit true and don't Hold Cursors across transactions

      Delete
  2. Hi Simon.

    Another very good post :-)

    Just want to add, that if you add "for update" to the select statement, then it is possible to update the resultset by double clicking on af value.

    Paul Tuohy has written about it :
    https://www.itjungle.com/2019/10/07/guru-edit-result-sets-in-run-sql-scripts/

    ReplyDelete
  3. One of my favorite features is saving your current season as a custom example. To use those statements again just use the insert examples and select custom.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.