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:

&nbsp&nbsp -c … new database file is created (existing file is rewrited)

&nbsp&nbsp -v … verbose mode (prints results of successfully completed commands)

&nbsp&nbsp -e … echo on (prints executed commands)

&nbsp&nbsp -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:

&nbsp&nbsp JetSQL -c -mEXTFILE=c:dbdata.mdb test test

&nbsp&nbsp JetSQL -c -mEXTFILE=c:dbdata.mdb test.sql test.mdb – is the same

&nbsp&nbsp 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

I used following classes from CodeGuru in this project:

• CStringEx
  
• CLexicalAnalyser
  

Downloads

Download application – 37 Kb
Download source – 24 Kb