Difference in Run SQL Scripts

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.