If op_n is 1, Q+E creates a buffer for the string being built and places exec_string_c in it.Julianne Sharer
Julianne Sharer is the Director of Application Development for WexTech Systems, Inc., a consulting and training firm specializing in applications for the MicrosoftÒ WindowsÔ and AppleÒ MacintoshÒ environments.
Microsoft Corporation does not make any representation or warranty, express or implied, with respect to any information herein. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of products manufactured by vendors independent of Microsoft.
Created: March 20, 1992
ABSTRACT
WexTech Systems, Inc. has developed several mission-critical applications for its clients with Pioneer Software’s Q+EÔ database editor serving as a data engine. The Q+E interface is easy to learn and use. It provides all the functionality required to create, index, update, view, and print the contents of files in a variety of data formats, but it contains no scripting language of its own.
The language you use to automate these routines is the macro language in your favorite MicrosoftÒ WindowsÔ or OS/2Ò package, and dynamic data exchange (DDE). All the commands that you can perform from Q+E’s interface can also be performed with DDE execute strings. Pioneer’s DDE command set includes several additional commands not available from the interface that are useful during DDE conversations.
Developers experienced in DDE applications will find it easy to learn Q+E’s ropes; the commands are clearly documented and in almost all cases work as advertised. This article provides pointers for using Q+E’s DDE commands. It is not intended to replace the Q+E User’s Guide but can be used as a “guide to the guide.”
Q+E supports a variety of data formats, two character sets, and two types of SQL compatibility. Although its recommended character set is ANSI and its recommended SQL compatibility is ANSI SQL, the default options are the IBM character set and dBASE SQL compatibility. We found that the recommended options produce more predictable results than the defaults. Because our application was self-contained and did not require compatibility with another program, the ANSI setting was not a problem.
Your application can INITIATE a DDE conversation on topics such as query window names, query file names, SQL SELECT statements, and the System topic. These Q+E topics are defined in detail in the Q+E User’s Guide. Basically, if you need to POKE data to, or REQUEST data from, a single window, the topic of the conversation is usually a window name, a file name, or a SELECT statement. If you want Q+E to perform an action, such as defining a new database or activating a window, you must INITIATE the conversation on the System topic.
Like Microsoft Excel, Q+E supports EXECUTE messages only in conversations initiated on the System topic. However, unlike Microsoft Excel, Q+E lets you use the System topic to POKE or REQUEST data as well. This is the best method for exchanging data with multiple data files over a single channel. Q+E’s active window is the implicit target of such exchanges.
Q+E’s windows display data in a row-and-column format resembling a Microsoft Excel worksheet. You POKE or REQUEST data items in these windows using a notation that resembles worksheet cell notation. You can also request information about the query window, including the number of rows and columns it contains, the SQL statement that defines the query, and the definition of the data fields within it.
Most Q+E commands require string arguments. For example, a SQL.QUERY command used to select all rows where the lname column equals SMITH requires that the comparison string (SMITH) be surrounded by either single (') or double (") quotation marks. Languages such as WordBasic or Microsoft Excel’s macro language use double quotation marks as their own string delimiters; therefore, the ability to use the single quotation mark is welcome, and embedding a literal double quotation mark within a string is usually a challenge.
In our experience, using double quotation marks within Q+E execute strings works all the time, and using single quotation marks works most of the time. Our informal solution has been to use single quotation marks first, because they do make the strings easier to handle and read. When the command fails, however, and no other reason for the failure is apparent, switching to double quotation marks usually does the trick.
The interactive method that Q+E provides for creating complex selection criteria simplifies the criteria creation process significantly and is available from DDE execute strings. Defining queries through DDE by sending the equivalent SQL statements is often more efficient than using the ADD.CONDITION command. This issue is not black and white; in some cases, the ADD.CONDITION command is exactly what the doctor ordered to modify an existing complex query. When specifying a query from scratch, however, using the SQL method is almost always more efficient than sending a long chain of ADD.CONDITION statements.
Some applications, such as Microsoft Excel, have a built-in limit to the length of the string that can be sent in an EXECUTE message. When your SQL queries exceed this limit, use Q+E’s COMMAND command to build a long command string from a series of EXECUTE messages.
The syntax of the COMMAND command is
COMMAND(op_n,exec_string_c)
The op_n argument may be 1, 2, or 3:
If op_n is 1, Q+E creates a buffer for the string being built and places exec_string_c in it.
If op_n is 2, Q+E appends exec_string_c to the buffer’s contents.
If op_n is 3, Q+E appends exec_string_c to the buffer and executes the concatenated result.
The requirements for delimiting each exec_string_c are not, however, clear from the Q+E User’s Guide. The documentation states that each command in an exec_string_c must be enclosed in brackets ([ ]), with the entire string enclosed in single or double quotation marks. What you need to do is not obvious from this short description, and, unfortunately, the manual does not provide an example. The following sample Microsoft Excel functions illustrate how to use this command successfully:
As we discussed, double quotation marks are somewhat more reliable than single quotation marks as delimiters. (It was, in fact, the Execute function that led us to this conclusion.) Therefore, this example uses a double quotation mark whenever a delimiter is required. But why so many? Let’s break the statements apart.
Each exec_string_c must be enclosed in double quotation marks, so each argument begins with a double quotation mark (A in the example). In Microsoft Excel, therefore, we need two double quotation marks to embed one double quotation mark as a literal character in the string. Now, find the closing parenthesis for each string. The two double quotation marks before it (B) are the closing quotation marks for the exec_string_c.
The SQL.QUERY command (whose long string arguments are the reason we are using the COMMAND command) requires double quotation marks around its SQL string argument. Like Microsoft Excel, Q+E requires two double quotation marks for each literal double quotation mark when it concatenates a string argument. Therefore, we use four double quotation marks at the beginning of the SQL string in the first COMMAND function (C) and close all four at the end of the SQL string in the last COMMAND function (D).
The Q+E commands for pasting data from the Clipboard, PASTE and PASTE.APPEND, provide an optional parameter for specifying the data to paste. This is an interesting solution to the limitations of Microsoft Excel’s POKE command, which requires you to place data in a cell before you can poke it to the server application. With PASTE and PASTE.APPEND, you can either paste data from the Clipboard or include it on the command line. You use tabs (ASCII character 9) to separate data columns and carriage return/linefeed pair (ASCII characters 13 and 10) to separate multiple rows.
If, after initiating a conversation on a set of rows, your application simply transfers the data to a document or a worksheet, you can avoid the intermediate step of sending the data to a variable (or macro sheet cell) within your program before transferring the data to the document or worksheet. The Q+E FETCH command lets you tell Q+E exactly where the data should be placed and then handles the transfer for you. What actually happens is that Q+E initiates a conversation with your application as its server and pokes the data to the target item; so your application must be able to function as both client and server.
The FETCH.ADVISE command works in the same way except that it creates a DDE link between the document or worksheet and the query for additional data transfers. Q+E can poke any changes to the data until it receives a FETCH.UNADVISE command or the conversation is terminated.