Wednesday, April 21, 2004

 

Conditional Expressions in JET SQL

AKA “Can I use If … Then or Select … Case in a Microsoft Access query?”

Part Three

Previous posts in this series raised the questions “just what functions can be used in JET queries outside of the Microsoft Access environment?” and “what exactly is the relationship between the JET database engine and the VBA expression service?”

I’m still looking for documentation to answer the first question. (The MDAC SDK lead turned out to be a dead end.) In the meantime, I’ll continue with the trial-and-error process I started in Part Two.

The answer to the second question turns out to be, in general terms at least, fairly simple and not terribly exciting. The VBA expression service is a DLL (Dynamic Link Library). Dynamic link libraries are designed to be shared by multiple applications. So just because the VBA expression service happens to have the letters “VBA” in its name doesn’t mean that it can only be used by VBA, or by an application that hosts VBA, such as Microsoft Access. In general (and anthropomorphic) terms, the JET database engine “knows” how to call functions in the DLL.

In Part Two of this series, we started working our way through the alphabetical list of functions in the VBA help file by testing functions with names that begin with the letter “A”. There aren’t any functions in the list with names beginning with the letter “B”, so the next group of functions to look at are those with names that begin with the letter “C”. It’s a big group, because it includes all the data-type conversion functions which, not surprisingly, begin with “C”, presumably for “Convert”. This group also includes the “Choose” function, one of the conditional functions that this series of posts was supposed to be about in the first place, which is just as well, because I’d almost forgotten about that. (Hey, this is a blog, it’s supposed to be spontaneous, OK?)

As with the Array function that we skipped over in Part Two, some of these functions just wouldn’t make much sense in a query, at least not without being combined in complex expressions with other functions, so we’ll leave them aside for now. Here’s a SQL statement for a query that includes all of the “C” functions that seem relevant and testable in a query …

SELECT
    CBool([TestLong]) AS TestCBool, 
    CByte([TestDouble]) AS TestCByte, 
    CCur([TestDouble]) AS TestCCur, 
    CDate([TestDouble]) AS TestCDate, 
    CDbl([TestDate]) AS TestCDbl, 
    Choose([TestDouble], "One", "Two", "Three", "Four", "Five") AS TestChoose, 
    Chr([TestLong]+34) AS TestChr, 
    Chr$([TestLong]+34) AS [TestChr$], 
    ChrW([TestLong]+34) AS TestChrW, 
    CInt([TestDouble]) AS TestCInt, 
    CLng([TestDouble]) AS TestCLng, 
    Cos([TestDouble]) AS TestCos, 
    CSng([TestDouble]) AS TestCSng, 
    CStr([TestDouble]) AS TestCStr, 
    CVar([TestDouble]) AS TestCVar, 
    CVDate([TestDouble]) AS TestCVDate
FROM tblTest
WITH OWNERACCESS OPTION;

As the names imply, "TestLong" is a long integer field, "TestDouble" is a double field, and "TestDate" is a date/time field.

Again, please remember that the purpose of this rather strange query is not to return meaningful results, but simply to determine whether these functions can be used in JET queries when executed outside the Microsoft Access environment.

The functions that I didn’t attempt to test were “CallByName”, “Command”, “Command$”, and “CreateObject”.

The following functions failed with “undefined function” error messages when I attempted to display the result of the query in datasheet view within Microsoft Access: “ChrB”, “ChrB$”, “CurDir”, “CurDir$”, and “CVErr”. I was expecting the “CurDir” and “CurDir$” functions to be blocked by the new JET “sandbox” mode introduced with Access 2003, but I was expecting a different error message. However, that’s an investigation for another day.

The behaviour of the “CDec” function was also unexpected. When I attempted to use it in the query, in the form “CDec([TestDouble])” I received the following error message: “The expression you entered has a function containing the wrong number of arguments.”

The “CDec” function, when used in VBA code, takes one argument. But a little experimentation soon showed that when used in the query, the function expected two arguments. Given two numeric arguments, the function executed without error in the query, but despite experimenting with various argument values, it always returned the value 0. This, too, will have to be an investigation for another day.

I saved this query using the name “qryTestFunctionsC”.

The next step was to modify the C# test code a little, to accept the name of the query to run as a command-line argument, instead of having to edit the code to change the hard-coded query name for every test …

using System;

namespace JetTest
{
    /// 
    /// Summary description for Class1.
    /// 
    Class Class1
    {
        /// 
        /// The main entry point for the application.
        /// 
        [STAThread]
        static void Main(string[] args)
        {
            if (args.Length == 0)
            {
                Console.WriteLine (@"No query specified.");
                Console.WriteLine();
                Console.WriteLine(@"TestJet requires the name of the query you");
                Console.WriteLine(@"wish to run as a command-line argument.");
                Console.WriteLine();
                Console.WriteLine(@"Example: TestJet myquery where 'myquery' is");
                Console.WriteLine(@"the name of the query you wish to run).");
                Console.WriteLine();
                Console.WriteLine(@"Please press Enter to exit, then try again.");
                Console.Read();
                return;
            }
            System.Data.OleDb.OleDbConnection TheConnection =
                new System.Data.OleDb.OleDbConnection();
            try
            {
                System.String strProvider = @"Microsoft.Jet.OLEDB.4.0";

                // Change this to the actual location of your test MDB.
                System.String strDataSource =
                    @"C:\Documents and Settings\Brendan Reynolds\My Documents\Blog\Test2.mdb";

                System.String strConnectionString =
                    @"Provider = " + strProvider + @"; " + @"Data Source = " + strDataSource;
                TheConnection.ConnectionString = strConnectionString;
                TheConnection.Open();
                System.Data.OleDb.OleDbCommand TheCommand = new System.Data.OleDb.OleDbCommand();
                TheCommand.CommandText = args[0];
                TheCommand.CommandType = System.Data.CommandType.StoredProcedure;
                TheCommand.Connection = TheConnection;
                System.Data.OleDb.OleDbDataReader TheReader =
                    TheCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                While (TheReader.Read())
                {
                    for (System.Int32 i = 0; i < TheReader.FieldCount; i++)
                    {
                        Console.Write(TheReader.GetValue(i).ToString());
                        if (i < (TheReader.FieldCount - 1))
                        {
                            Console.Write(@", ");
                        }
                        Else
                        {
                            Console.WriteLine();
                        }
                    }
                }
                TheReader.Close();
                Console.Read();
            }
            catch (System.Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                System.Console.Read();
            }
            finally
            {
                TheConnection.Dispose();
            }
        }
    }
}

There’s a lot more we could do to make this code more generic and reusable, for example by accepting the path to the MDB as an argument, and accepting an arbitrary SQL statement rather than requiring the name of a saved query. But aside from the fact that all those parameters would make the application too cumbersome as a command-line driven console application, forcing me to change it to a Windows Forms application, there are security implications to consider here. I don’t want to be held responsible if you accidentally run a DELETE query against your production data. As the application stands at the moment, as long as the hard-coded path points to an MDB that you use solely for testing purposes, the application will not have any access (no pun intended) to production databases. And as long as you don’t save any action queries in your test database, the application will not be able to modify any data even in the test database.

I may very well add additional functionality to the test code in the future, but I’m not going to rush into it without adding safeguards, such as checking for keywords like “DELETE”, “INSERT”, “UPDATE”, etcetera in SQL statements.

We can now run the test application (I called it JetTest) from the command prompt and pass the name of the query as a parameter, e.g. “JetTest qryTestFunctionsC”. The code executes the new query without error. The output is pretty ugly, but that’s not the point. Other than a few formatting differences, it’s the same output that was produced by running the query in the Microsoft Access user interface. So we can now add the following functions to our list of functions that can be used in JET queries executed outside of the Microsoft Access environment:

CBool, CByte, CCur, CDate, CDbl, Choose, Chr, Chr$, ChrW, CInt, CLng, Cos, CSng, CStr, CVar, and CVDate.

Future plans for this series include, in no particular order, and in no specific time-frame …

Test more JET functions. Explain the syntax and use of the Choose function. Enhance the JetTest test utility and perhaps produce VB.NET and/or VBA versions. Explain how to build the test app for those who may not be familiar with VS.NET.


Send feedback on this post to (change the obvious):

Brendan Reynolds
bwnnfb at hotmail dot com

(For the insatiably curious, "bwnnfb" is an acronym for "Blog With No Name Feed-Back")

Copyright © 2004 Brendan Reynolds. All rights reserved.


Comments: Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?