JetSQL - SQL script interpreter for MS Jet

Environment:Visual C++ 5, Windows NT 4 (SP3)

What is it

I'm using MS Jet database engine (mdb files) delivered with VC to store data of my application. I was missing the possibility of executing SQL scripts on this engine. I prefer to define the structure of the database with a SQL script so I created a small application to do this.

The JetSQL tool is a console application that executes SQL scripts on MS Jet databases. It can be used to create and update mdb database and to print its content to standard output. It supports most of MS Jet SQL statements, adds CREATE VIEW and DROP VIEW statements and provides better syntax error analysis and a simple macro capability.

Requirements

The JetSQL tool runs on Windows 95/98/NT and requires mfc42.dll and MS Jet 3.5 installed.

Using

The JetSQL tool is a console application and prints all its outputs to standard output.
Syntax of the command line is :

JetSQL [options] sqlfile[.sql] mdbfile[.mdb]

options:
   -c ... new database file is created (existing file is rewrited)
   -v ... verbose mode (prints results of successfully completed commands)
   -e ... echo on (prints executed commands)
   -mMACRO=VALUE ... defines macro (all occurences of %MACRO% will be replaced by VALUE)
sqlfile ... SQL script file with extension .sql, the extension isn't required on command line
mdbfile ... database file with extension .mdb, the extension isn't required on command line

Examples:
   JetSQL -c -mEXTFILE=c:\db\data.mdb test test
   JetSQL -c -mEXTFILE=c:\db\data.mdb test.sql test.mdb - is the same
   JetSQL -mPARAM="two words" test test

In SQL script you can use following MS Jet SQL statements:

• ALTER TABLE
• CREATE TABLE
• CREATE INDEX
• DELETE
• DROP TABLE
• DROP INDEX
• INSERT INTO
• SELECT
• UPDATE

CREATE VIEW nad DROP VIEW statements are added. They makes it possible to manage stored queries. Their syntax is:

CREATE VIEW name AS sql stament
DROP VIEW name

All statements are terminated by a semicolon. Statements can be divided into multiple lines. Single line comments begins with -- (double minus) at the beginning of the line, multiple line comments are enclosed by /* and */

Example:

/********************************/
/*         TEST.SQL             */
/********************************/

CREATE TABLE PEOPLE
(
	ID		SMALLINT CONSTRAINT kt PRIMARY KEY,
	Name	TEXT(50),
	DOB		DATE
);

-- Import data from external database parametrized by a macro
INSERT INTO PEOPLE (ID,Name,DOB)
	SELECT ID,Name,DOB
	FROM PEOPLE IN %EXTFILE%;

INSERT INTO PEOPLE (ID,Name,DOB) VALUES(10,"George",'1.5.1980');
INSERT INTO PEOPLE (ID,Name,DOB) VALUES(12,"Joe",'3.9.1959');
INSERT INTO PEOPLE (ID,Name,DOB) VALUES(13,"Suzan",'23.4.1905');
INSERT INTO PEOPLE (ID,Name,DOB) VALUES(14,"Amy",'30.6.1988');

-- Integrity constraint violation
INSERT INTO PEOPLE (ID,Name,DOB) VALUES(10,"George",'1.5.1980');

CREATE VIEW YOUNG_PEOPLE AS
  SELECT * FROM PEOPLE WHERE DOB>DateValue('1.1.1980');

SELECT * FROM PEOPLE;
SELECT * FROM YOUNG_PEOPLE;

Source code

The project was created with Visual C++ 5.0 using dynamically linked MFC library. Database is accessed with MFC DAO classes.

Most important is the class CSqlInterpreter. This class does all the work with help of other classes in the project. It reads the script, parses it and executes validated SQL statements. Most of the statements are executed using CDaoDatabase::Execute(), CREATE VIEW and DROP VIEW are executed using CDaoQueryDef class. Results of SELECT statement are handled using CDaoRecordset. I intend to embed this class to other applications (console and GUI) and therefore the class uses callback function to display its output.

The source code demostrates:

• Using of MFC DAO classes in console application without GUI (main.cpp).
• Using of CDaoQueryDef for creating stored queries (CSqlInterpreter::CreateView).
• Using of CDaoRecordset to get data from table or query with unknown structure (CSqlInterpreter::Select).

Acknowledgements

• CStringEx
• CLexicalAnalyser

Downloads