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_MACHINESOFTWAREMicrosoftShared ToolsMSQueryPath. 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

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read