Using Microsoft Query to design your queries

Environment:Visual C++ 5 (and above)

Setup procedure

When designing a database application it's required to sometimes make the user design a query. The programmer could write his own graphical query designer, but why not use Microsoft Query? This document describes the design and usage of the MSQuery class. This is a class which will communicate with Microsoft Query. This class is not completed to support all MSQuery DDE commands, but will be enough to create a query in MSQuery and to modify a query in MSQuery. This class and this article uses the Dynamic Data Exchange Management library. This makes the usage of DDE much easier. First the header file of this library must be included in the source. When using MFC this could best be put in stdafx.h. At the moment this #include is put in the headerfile of the MSQuery class.


#include <ddeml.h>
 

Using this library requires the linkage of user32.lib. This shouldn't concern you, because with Visual C MFC, this is done automatically. But you must ensure DDEML.DLL is in your system directory. When this is done, Visual C is ready to use the DDE library of Microsoft. Of course also Microsoft Office should be installed, well at least MSQuery. This article was tested with Microsoft Office 97 and Microsoft Office 2000 Beta2. This article is split up in three parts: the first part describes very briefly the DDEML library. With only the vital information required understanding the rest of the article. The second part describes some basic concepts of MSQuery. And the last part describes how to use the MSQuery classes.

Very Short Introduction DDEML

Initialising DDEML

To use the Dynamic Data Exchange Management Library, you must first initialise it and get an instance-id from it. This can be done by the following piece of code:


DdeInitialize(&idInst, (PFNCALLBACK) DdeCallback, CBF_FAIL_EXECUTES|CBF_SKIP_ALLNOTIFICATIONS,0)
// DWORD idInst, The instance ID, required for every DDEML library
// DdeCallback is a callback function, see below for more details
// The third parameters are a number of flags
// 0 is a reserved parameter, which should be 0.
 

Connecting to the server

When the library is initialised there must be made contact with a DDE server. To do this there must be passed two strings to a special function: the server name and the topic name. It is not possible to pass a normal pointer to a character array (string) to it. The functions of DDEML require the use of string handles. But this isn't very difficult, because DDEML has got several standard functions for this. Below are the two most important string functions.

Creating a string handle:


HSZ DdeCreateStringHandle(DWORD idInst, LPTSTR psz, int iCodePage);
// idInst = the instance id obtained from DdeInitialize
// psz = the string to create
// iCodepage = the codepage used for this string
//		CP_WINANSI = for the normal ansi characterset (default)
//		CP_WINUNICODE = for a unicode string
// Returns: the string handle (or NULL when error)
 

Deleting a string handle:


BOOL DdeFreeStringHandle( DWORD idInst, HSZ hsz); 
// idInst = the instance id obtained from DdeInitialize
// hsz = the handle of the string obtained from DdeCreateStringHandle
 

To connect to a server, there must be passed two strings to the DDEML: the servername and the topicname. The function, which gets a conversationhandle to the DDE conversion, is the following:


HCONV DdeConnect( DWORD idInst, HSZ hszService, HSZ hszTopic, PCONVCONTEXT pCC); 
// idInst = again is the instance id obtained from DdeInitialize
// hszService = a stringhandle of the service (or server) name. 
// hszTopic = a stringhandle of the name of the topic.
// pCC = Conversation context information (can usuable be NULL)
// Return: the conversation handle (or NULL when error)
 

After getting a conversationhandle, it is possible to communicate with the DDE server.

Executing a transaction

To make the server execute a given function, the following command could be used:


HDDEDATA DdeClientTransaction( LPBYTE pData, DWORD cbData, HCONV hConv, HSZ hszItem, UINT wFmt, 
                               UINT wType, DWORD dwTimeout, LPDWORD pdwResult); 
 

This is a pretty complex function, but not all parameters are required. In this article only this form is required for execution:


HDDEDATA DdeClientTransaction( commandstring, strlen(commandstring), hConv, 0, 0, XTYP_EXECUTE, timeout, result); 
// commandstring is the command to execute
// hConv = the handle obtained from DdeConnect
// XTYP_EXECUTE = the type op transaction
// timeout = how long to wait on server (in ms) or TIMEOUT_ASYNC to not wait for the completion of the command.
// result = the result. When using TIMEOUT_ASYNC this will be the transaction handle.
// return value will be != NULL when the function was successfull. 
 

Using TIMEOUT_ASYNC will have as result, that the callback function (see below: Callback function) will be called when the DDE command is executed.

Retrieving information

To retrieve information from the server, the following function should be used. Again it's a variant on DdeClientTransaction:


HDDEDATA DdeClientTransaction((LPBYTE)NULL, 0, conv, str, CF_TEXT, XTYP_REQUEST, timeout, &res);	
// conv = the conversation handle from DdeConnect
// str  = a string handle of the item to get
// CF_TEXT = the format of information to get. (CF_TEXT is string)
// XTYP_REQUEST = the type of transaction
// timeout = the timeout interval in ms, to wait for the result. 
// result = the result. 
// return value will be the data handle to the requested information.
 

When the return value of above function is a nonzero value. The DDE transaction was successful and the data can be retrieved. It would be nice to know the size of the result. The result will be a string, because we used CF_TEXT. This is very easy:


int size = DdeGetData(dta, NULL, 0, 0);
// dta = the data handle returned from DdeClientTransaction
 

When we've got the size, we could reserve memory and get the data: DdeGetData(dta, (unsigned char*)buf, size, 0); // dta = the data handle returned from DdeClientTransaction // buf = a pointer to the buffer, to return the string to. // size = the size of the buffer.

When done delete the handle:


DdeFreeDataHandle(dta);
 

The Callback function

It would also be nice to implement a callback function. This function is called for example, when a TIMEOUT_ASYNC transaction is finished. This is the general callback function:


HDDEDATA CALLBACK DdeCallback(UINT uType,UINT  uFmt, HCONV hconv, HSZ hsz1,  HSZ hsz2, HDDEDATA hdata, DWORD dwData1, DWORD dwData2) 
{ 
	switch (uType) 
	{ 
		case XTYP_REGISTER: 
		case XTYP_UNREGISTER: 
			return (HDDEDATA) NULL; 
		case XTYP_ADVDATA: 
			return (HDDEDATA) DDE_FACK; 
		case XTYP_XACT_COMPLETE: 
			// this one is called after a finish of TIMEOUT_ASYNC
			return (HDDEDATA) NULL; 
		case XTYP_DISCONNECT: 
			return (HDDEDATA) NULL; 
		default: 
			return (HDDEDATA) NULL; 
	} 
} 
 

For the details about this callback function look at the DDEML documentation.

Introduction MSQuery

When using DDE with MSQuery, you have got 2 different types of conversation channels: (also called topics)

system-channel
This is the main channel for conversation. This channel is always available.

query-channels
For every query in MSQuery there's a query channel available. This channel can be requested via the system channel.

Detect and run Microsoft Query

The first step is to make a connection via DDE with Microsoft Query. To make a connection with MSQuery, this piece of code should be executed:


HSZ hszServName = DdeCreateStringHandle(idInst, "MSQuery", CP_WINANSI);   
HSZ hszSysTopic = DdeCreateStringHandle(idInst, SZDDESYS_TOPIC, CP_WINANSI);	
HCONV hConv = DdeConnect(idInst, hszServName, hszSysTopic, (PCONVCONTEXT) NULL);
 

As can be seen from the above sample, the servername of Microsoft Query is 'MsQuery'. The topic we are connecting to is the system topic of MSQuery. The symbolic constant for the system channel is: SZDDESYS_TOPIC.

When trying this and no connection could be made, it could be MSQuery is not running. So when that is the case we should try to run MSQuery. To do this, first the location of MSQuery32.exe must be located and then the executable should be run. The location can be found in the registry at: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\MSQuery\Path. See the sourcecode for details how to do this.

Creating a query

To create a query, the following command should be passed to MSQuery. As can be seen in this example, every command must be between '[ ' and ']'.


"[UserControl(\"&Return to application\",1,TRUE)]" 
// 1 is the state of the MSQuery window: 1=normal, 2=minimized, 3=maximized
// TRUE is used to tell the MSQuery a new query should be created
 

The user will see "Return to application" as a replacement in the file-exit menu of MSQuery. When the user select this, the query will be returned to the application. This command should be passed without a time-out interval, so TIMEOUT_ASYNC must be passed. When the user is done, and has created or canceled the query, the callback function is called. In this callback function the result should be retrieved from MSQuery.

Modifying a query

Modifying a query is a little harder, it can be done the following way. Every command except the last one should NOT use TIMEOUT_ASYNC !

Build the connectionstring to connect to:


"[BuildODBC(\">place the connectionstring here>")]"
 

Now connect: (for a list of parameters of LogonNow see the MSQuery help).


"[LogonNow(1)]"
 

Open the query: (this can be a query filename or a real SQL statement)


"[Open(\" <filename or sql-statement> ")]"
 

The last command is used to wait for the user to modify the query:


"[UserControl(\"&Return to application\",1,FALSE)]" 
// Now false to tell MSQuery no new query needs to be created
 

Getting the result

In the callback function it is the responsibilty of the application to get the result. To get the selected query use:


"ODBCSQLStatement"
 

To retrieve the connectionstring use:


"ConnectionString"
 

That's about all you need to know to use MSQuery with your application. Though this articles if very brief in everything, you are free to ask me questions if some things aren't clear. Also try to look at the help of MSQuery, it contains more commands and other information. Read the documentation of DDEML in the Visual C online help.

The CMSQuery class

The CMSQuery class is VERY easy to use. You should use it within a dialog box with a cancel button, because your application should wait until the user presses cancel or returns from MSQuery. There are a few things that needs to be done to create the class. Use the class dynamicly in your cancelbutton dialog. In the OnInitDialog do the following:


BOOL CCancelDlg::OnInitDialog()
{
	m_query = new CDBSMsQuery();
	m_query->Create(m_sqlstring, this);
}
 

m_query is the CMSQuery object as a member of the CCancelDlg class. Create runs MSQuery or tries to connect to an existing instance. Shows MSQuery and makes a DDE connection. You should check the result of Create, if it returns false the connection hasn't been made. The first parameter of Create is the SQL string, when this string is empty a new query is created else the existing is opened. This is the pointer of the cancel dialog.

Then you should wait till the user presses cancel or MSQuery is finished. When MSQuery is finished it will send a WM_COMMAND message with IDCANCEL.

In the OnCancel function of this dialog do something like this:


// Retrieve the result and destroy the object
	m_sqlstringOK = m_query->QueryOK();
	m_sqlstring = m_query->GetQuery();
	m_conenctstr = m_query->GetConnectionString();

// Delete the MSQuery object
	delete m_query;
 

I would advise you to study the included sample, and see how easy it is to use the class.

Rick Blommers,
r.blommers@blommers-it.nl
http://www.blommers-it.nl/r.blommers/programming/

Downloads

Download demo project - 82 Kb

History



Comments

  • Great work!!!

    Posted by Legacy on 07/24/2002 12:00am

    Originally posted by: Alexander N. Treyner

    Really, Great work!!!

    Reply
  • Fix for editing a query:

    Posted by Legacy on 08/23/1999 12:00am

    Originally posted by: Rick Blommers

    It somethimes happend that MSQuery can't open the query for editing.
    After a lot of debugging I discovered the cause:

    Somehow \r\n are inserted in the query. This is caused by the edit control or msquery itself..

    To fix it remove every \r and \n from the query

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Download the Information Governance Survey Benchmark Report to gain insights that can help you further establish business value in your Records and Information Management (RIM) program and across your entire organization. Discover how your peers in the industry are dealing with this evolving information lifecycle management environment and uncover key insights such as: 87% of organizations surveyed have a RIM program in place 8% measure compliance 64% cannot get employees to "let go" of information for …

  • Ever-increasing workloads and the challenge of containing costs leave companies conflicted by the need for increased processing capacity while limiting physical expansion. Migration to HP's new generation of increased-density rack-and-blade servers can address growing demands for compute capacity while reducing costly sprawl. Sponsored by: HP and Intel® Xeon® processors Intel, the Intel logo, and Xeon Inside are trademarks of Intel Corporation in the U.S. and/or other countries. HP is the sponsor …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds