Editor’s Note: ShowCase VISTA and STRATEGY are some of the most popular Client Access software products available for the AS/400. Yet how many of us are maximizing the full potential of these powerful tools? In this article, Tom Healy shows us how to use the ShowCase DDE to build seamless queries into our users’ Windows-based applications.

Many of you have heard of Dynamic Data Exchange (DDE), and you may know that DDE is used when you copy a cell or range of cells from a spreadsheet and paste link them into a word processor document. Then, when the spreadsheet changes, the word processor document automatically changes to reflect the new data from the spreadsheet. As you will see, DDE has a lot more to offer than just this one function. In this article, I will give you a closer look at DDE’s component parts, as well as practical tips about using DDE to help control and automate your ShowCase queries.

To set the stage, I need to explain what the query feature of ShowCase STRATEGY is all about. Query is one feature of ShowCase Corporation’s line of STRATEGY products. It is used to create SQL statements using a GUI front-end. Using ODBC, the query feature passes these statements to a server—typically an AS/400. If you are an experienced query user, you already know that not every request can be performed with one SQL statement, and you also know that running queries interactively can be detrimental to the performance of your system. How can you avoid these problems? DDE will help address these issues (and many others).

[Note: ShowCase Corporation recently migrated its various individual products to a new product called STRATEGY. The query feature of STRATEGY described in this article originated in the earlier VISTA product. To remain forward- and backward-compatible, Windows DDE still refers to the query feature as VISTA.

What is DDE?

DDE is a Microsoft Windows protocol that lets your application extract information from other applications, update other applications with new information, and send commands or keystrokes to control your applications remotely. One might be inclined to think that because DDE is a standard Windows protocol, every application supports DDE in the same way. Unfortunately, this is not the case. While the concepts remain the same, the syntax of communications will differ greatly from one DDE implementation to another.

DDE lets various applications interact in a sort of client/server relationship. The application issuing the commands is the “client,” and the application responding to the commands is the “server.” The client performs a set of actions. The first action every client needs to perform is to establish a communications link with the server, typically referred to as initiating the conversation. Once this link is established, the client can send commands to the server, typically referred to as executing a command. If the client desires and the server supports it, the client can also request information back from the server. After all desired communications have taken place, the client ends the communications channel, referred to as terminating the conversation (see Figure 1).

The Server Application

The basic commands supported by the client are fairly standard. In contrast, servers support commands differently based on their respective capabilities, so the supported commands will vary from one server to the next. For instance, while the query feature of ShowCase STRATEGY supports running queries through DDE, some other products—such as Microsoft Word—don’t.

The ShowCase STRATEGY query feature supports five commands: File Open, Set Variable, Run Query, File Close, and File Exit. To see a detailed list of the parameters associated with these commands, search on DDE in the ShowCase STRATEGY online Help database. The basic syntax of each command is shown in Figure 2.

The Query Feature Script File as the Client

An undocumented capability of the ShowCase STRATEGY query function is an ability to pass commands to the STRATEGY server using a script file and DDE. The query feature script file is a text file using the STRATEGY server DDE commands. Each script file is identified with a VISTA Script File (VSF) extension. You can simply assign this script file to an icon and it will perform any actions that you have entered into the file. You can use a script file to automate a number of STRATEGY functions. You can run multiple STRATEGY queries from a single icon; link the output of one STRATEGY query to the input of another STRATEGY query; and run, print, and close STRATEGY queries automatically. You can also submit STRATEGY queries to batch by clicking an icon; retrieve the data from a prior batch run of a STRATEGY query; close the STRATEGY query feature; and create and populate a PC database with AS/400 data.

Working with STRATEGY Script Files

The following are two examples of VSF scripts, each one using the DDE feature of ShowCase STRATEGY.

The first VSF, called VSCRIPT1.VSF, opens two queries. It first extracts data by passing a variable to a query and then runs a second query against the extracted information.

File: VSCRIPT1.VSF
[FILE.OPEN(“C:QUERY1.DBQ”)]
[SET.VARIABLE(“C:QUERY1.DBQ”,”

STATE”,”MN”)]
[RUN.QUERY(“C:QUERY1.DBQ”,5,14,

”C:QUERY1.DBF”,1)]
[FILE.CLOSE(“C:QUERY1.DBQ”,1)]
[FILE.OPEN(“C:QUERY2.DBQ”)]
[RUN.QUERY(“C:QUERY2.DBQ”,3)]
[FILE.CLOSE(“C:QUERY2.DBQ”,1)]
[FILE.EXIT()]

In the first line of the script, VSCRIPT1.VSF uses the FILE.OPEN command to open a query that has already been written and stored in a file called QUERY1.DBQ. Next, the script passes the value MN to the query variable called STATE, using the SET.VARIABLE command. Then it interactively runs the query and redirects the output into a dBASE IV file called QUERY1.DBF, using the RUN.QUERY command. After this new dBASE IV file has been created, the script closes the original QUERY1.DBQ file using the FILE.CLOSE command and then opens a second previously stored query called QUERY2.DBQ. Again, the script uses the FILE.OPEN command to accomplish this. Subsequently, it runs this second query using the RUN.QUERY command and then closes the file using a second FILE.CLOSE command. Finally, using the FILE.EXIT command, the VSCRIPT1.VSF file ends its DDE session.

A second powerful technique is to send a query to run in batch mode. VSCRIPT2.VSF is an example of this technique.

File: VSCRIPT2.VSF
[FILE.OPEN(“C:QUERY3.DBQ”)]
[RUN.QUERY(“C:QUERY3.DBQ”,,,,2)]
[FILE.CLOSE(“C:QUERY3.DBQ”)]
[FILE.EXIT()]

In this example, a previously written query is stored in a file called QUERY3.DBQ. The file is opened using the FILE.OPEN command and submitted to batch using the RUN.QUERY command. After the query has been submitted, both QUERY3.DBQ and the query feature are closed. Notice that the process of submitting the query to batch is controlled from within the RUN.QUERY command using option 2. Finally, the QUERY3.DBQ file is closed using the FILE.CLOSE command, and the DDE session is ended using the FILE.EXIT command.

Inside Client Applications

We have now looked at the server side of this DDE client/server relationship. As you will see, when the client comes into the picture, the syntax becomes a bit more involved. The question then becomes, “If we can issue all the DDE Server commands directly from the query feature script file, then why would you ever want to execute the query feature server commands from within other applications?”

The answer is simple: power! Instead of making the user manually interact with the STRATEGY tool, you can automate the process and turn it into a custom application that accesses AS/400 data. For example, by embedding the server commands into a Microsoft Word for Windows document, you could create a button that automatically executes a query, returns the data to the Word document, and triggers a mail merge to take place based on the returned AS/400 data—all without the user leaving MS Word.

There are a few steps you must follow. When creating macros in your client applications, you must first initiate a DDE channel with the query feature. During the initiation of this DDE channel, you will need to specify the application you want to communicate with and the topic of the conversation. The topic will either be the name of the open window, called a window topic in the server application, or a special topic called System. A window topic is used when you want to perform an action aimed at the open file. In order to establish a window topic, the file you are connecting to must be open in the server application. By contrast, a system topic is used when you want the server application to perform an action like opening a file. A system topic can be established whether a file is open or not.

Examples using DDE Inside Client Applications

Let me show you what starting DDE conversations, executing commands, requesting information, and ending DDE conversations with STRATEGY query from within Microsoft Excel, Microsoft Word, and Lotus 1-2-3 looks like.

Example 1: Submit QUERY1.DBQ to Batch

Sub SubmitToBatch()

DDEChn = DDEInitiate(“VISTA”,
“SYSTEM”)
ErrorCode = DDEExecute(DDEChn,
“[FILE.OPEN(“”C:QUERY1.DBQ””)]”)
ErrorCode = DDEExecute(DDEChn,
“[RUN.QUERY(“”C:QUERY1.DBQ””,,,,
2)]”)

ErrorCode = DDETerminate(DDEChn)
End Sub

This example submits the query statements stored in the file QUERY1.DBQ to batch using Microsoft Visual Basic for Applications (VBA). In this example, the first statement opens a communications channel with the STRATEGY query feature using the System topic. It returns the channel identifier number, which is assigned to the DDEChn variable. This variable is then used for all subsequent DDE commands.

The variable ErrorCode is used to hold any error values that may be returned from the corresponding DDE command. While this sample is not checking the value of the variable ErrorCode for errors, in a production setting it would be a good idea to verify the correct execution of each statement.

In the next example, we’ll perform the same process, but this time we’ll fetch back the batch results from the QUERY1.DBQ.

Example 2: Fetching the Results of the Batch Query

Sub FetchBatchResults()

DDEChn = DDEInitiate(“VISTA”,
“SYSTEM”)
ErrorCode = DDEExecute(DDEChn,
“[FILE.OPEN(“”C:QUERY1.DBQ””)]”)
ErrorCode = DDEExecute(DDEChn,
“[RUN.QUERY(“”C:QUERY1.DBQ””,,,,
3)]”)

ErrorCode = DDETerminate(DDEChn)
End Sub

Once again, we’re retrieving the channel identifier number and assigning it to the DDEChn variable. This time, however, when we execute RUN.QUERY, we’re pulling back the results from the QUERY1.DBQ using the 3 option.

In example 3, we’ll request the results back from the QUERY1.DBQ by using a Window topic instead of the System topic. The results of this query will then be stored in an array variable called QueryValues.

Example 3: Using the Window Topic and Assigning the Data into an Array

Sub RequestFromQuery()

DDEChn = DDEInitiate(“VISTA”,
“C:QUERY1.DBQ”)
QueryValues =
DDERequest(DDEChn2, “DATA”)
ErrorCode =
DDETerminate(DDEChn)

End Sub

Of course, DDE is useful with products other than Microsoft’s Excel and Word. Here’s an example using DDE to submit the QUERY1.DBQ to batch, this time using the Lotus 1-2-3 version 5.0 macro language.

Example 4: Using Lotus 1-2-3 Macro Language DDE

{DDE-OPEN “VISTA”; “SYSTEM”}
{DDE-EXECUTE

“[FILE.OPEN(“”C:QUERY1.DBQ””)]”}
{DDE-EXECUTE

“[RUN.QUERY(“”C:QUERY1.DBQ””,,,,2)]”
{DDE-CLOSE}

A Powerful Tool

DDE is a powerful Windows-environment tool that is often overlooked. DDE gives you the ability to link your application directly with AS/400 data and increase your ability to control how queries are run. Many applications within Windows support some form of DDE, either as a client, a server, or both. I hope this article has given you some ideas both about how to use DDE in general and, more specifically, how to use DDE in conjunction with the query feature of ShowCase STRATEGY.

Figure 1: DDE Client/Server Relationship

Command Syntax Purpose

File.Open [FILE.OPEN(“query_name”, Opens a query options,replace)]

Set.Variable [SET.VARIABLE(“query_name”, Sets either a global or

“variable_name”, value)] local variable inside of the query feature of STRATEGY Run.Query [RUN.QUERY(“query_name”, Runs a query and output,format,”output_file”,batch)] optionally creates an output file
File.Close [FILE.CLOSE(“query_name”,save)] Closes a query File.Exit [FILE.EXIT()] Exits out of the query

feature of STRATEGY

Figure 2: STRATEGY Query DDE Commands

Also Read

The Power of Writing Code in a Low-Code Solution

TechTip: Let’s Have a Date, Part 2

Going off the Rails