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:dbdata.mdb test test
   JetSQL -c -mEXTFILE=c:dbdata.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
I used following classes from CodeGuru in this project:
- CStringEx
- CLexicalAnalyser