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

I used following classes from CodeGuru in this project:
  • CStringEx
  • CLexicalAnalyser

Downloads

Download application - 37 Kb
Download source - 24 Kb


Comments

  • Is there any upgrade to the tool ?

    Posted by Legacy on 09/02/2002 12:00am

    Originally posted by: Sanjeev Manglani

    Hi,


    I just downloaded the Jetsql application , its a nice one but does not support many sql statements such as TODATE etc, if any upgrade to the application has been made plz inform me or if there is any other application that supports executing sql scripts for mdb files ,

    Regards,
    Sanjeev

    Reply
  • JetSQL - SQL script interpreter for MS Jet

    Posted by Legacy on 08/30/2002 12:00am

    Originally posted by: Faisal Zahidi

    I am trying create view in my access 2000, and the database is unable to find SQLJET Engine. As a result, I was trying to install JetSQL.exe. It's now giving me error that .DLL file, MSVCP50.DLL was not found in my system. Please let me know what should I do
    regards,
    Faisal Zahidi

    Reply
  • How to create a stored procedure?

    Posted by Legacy on 04/22/2002 12:00am

    Originally posted by: bag

    ????

    Reply
  • 我想学

    Posted by Legacy on 03/15/2002 12:00am

    Originally posted by: 寒冰

    我很想学
    但晚我不懂
    我该怎么做??

    Reply
  • Not Support Chiness

    Posted by Legacy on 03/05/2002 12:00am

    Originally posted by: xiaqibing

    It can not support chiness field!

    Reply
  • Can we take more than two tables from a data base in MFC

    Posted by Legacy on 06/22/2001 12:00am

    Originally posted by: vijendra


    i am getting trouble with selecting two or more tables
    of msaccess in the second step of MFC app wizard here
    i am getting an error called "record set is read only"
    why ? kindly solve this ..

    Reply
  • Problem with multiple CONSTRAINT clauses

    Posted by Legacy on 10/14/2000 12:00am

    Originally posted by: David Szutu

    There's a problem when a CREATE TABLE statement has multiple CONSTRAINT clasues, as in:
    
    

    CREATE TABLE POP_ACCOUNT
    (
    popID counter NOT NULL,
    popUserName varchar(255),
    popPassword varchar(255),

    CONSTRAINT popPrimary PRIMARY KEY (popID),
    CONSTRAINT popUnique UNIQUE (popID, popUserName)
    );


    JetSQL sees the second CONSTRAINT as an error. A fix is to change the lines in Interpreter.cpp, lines 763 - 777 from:

    ===============
    if(t.getTokenType()==rightpT) break;
    if(t.getTokenType()!=commaT)
    {
    m_show(m_ln,"Comma or right parenthesis missing in the field list of constraint",error);
    return -1;
    }
    }
    t = m_lexan.GetCurrentToken();
    m_lexan.NextToken();
    if(t.getTokenType()!=rightpT)
    {
    m_show(m_ln,"Right parenthesis missing",error);
    return -1;
    }
    ==================

    to:

    ====================
    t = m_lexan.GetCurrentToken();
    m_lexan.NextToken();
    // if a right param then has reached the end of the CREATE TABLE statement
    if(t.getTokenType()==rightpT) break;

    if(t.getTokenType()!=commaT) // expecting a comma
    {
    m_show(m_ln,"Comma or right parenthesis missing in the field list of constraint",error);
    return -1;
    }

    t = m_lexan.GetCurrentToken();
    m_lexan.NextToken();
    if(t.getTokenType()!=constraintT)
    {
    m_show(m_ln,"CONSTRAINT keyword expected",error);
    return -1;
    }
    }

    if(t.getTokenType()!=rightpT)
    {
    m_show(m_ln,"Right parenthesis missing",error);
    return -1;
    }
    ====================


    There's also a problem with the use of Foreign Key constraint. The line:

    m_lexan.NextToken();

    needs to be inserted after line 690, which is:
    t = m_lexan.GetCurrentToken();

    I've only tested the above changes with a couple of CREATE TABLES.
    Dave

    Reply
  • additional SQL commands DESCRIBE, RENAME, MINUS, INTERSECT ?

    Posted by Legacy on 08/04/1999 12:00am

    Originally posted by: Ralph Zeller

    Dear Pavel, you have done a great job - thank you.

    I like to use your Tool in my SQL lectures at university, because it's exactly what my students need for their SQL training.
    At the moment I use SQL*Plus from Oracle and the only different to your tool ist the absence of the following SQL commands:

    1. How can I see which tables are defined in my database?
    In SQL*Plus I can use:
    SELECT * FROM Tab;
    where Tab is the name of a system table with all tablenames.

    2. How to see which rows are defined in a table?
    In SQL*Plus I can use:
    DESCRIBE tablename;

    3. Renaming of tablenames with RENAME:
    ex: RENAME old_tablename TO new_tablename

    4. Combining two SELECT statments with MINUS or INTERSECT are missing.

    Is it possible to include this commands? Are you planning a GUI version of JetSQL? Are you planning a documentation?

    Bye, Ralph.

    Reply
  • Bug in WITH clause of CREATE INDEX statement

    Posted by Legacy on 08/04/1999 12:00am

    Originally posted by: Ralph Zeller

    A CREATE INDEX statement like:

    CREATE INDEX book_idx ON book (id) WITH PRIMARY;

    gives the following error message:

    *** Line 2: Semicolon missing behind the statement CREATE TABLE

    After changing the code in file Interpreter.cpp, class CSqlInterpreter::CreateIndex() at line 918 from:

    if(t.getTokenType()==rightpT) break;

    to:

    if(t.getTokenType()==rightpT)
    {
    m_lexan.NextToken();
    break;
    }

    everything worked ok.

    A second change might be at line 956 from:

    m_show(m_ln,"Semicolon missing behind the statement CREATE TABLE",error);

    to:

    m_show(m_ln,"Semicolon missing behind the statement CREATE INDEX",error);

    Bye, Ralph.

    Reply
  • What about relationships between tables ?

    Posted by Legacy on 07/16/1999 12:00am

    Originally posted by: bernard alleysson

    Great job !

    I'm not an expert in SQL/Jet programming, and
    I'd like to know if that way (ie using Jet SQL statements),
    is it possible to create/modify relationships between the
    database tables ?

    More precisely is it possible to create/modify the
    inferential integrity options (cascade delete, cascade update)on a table using JetSQL ?


    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: August 19, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT You deployed your app with the Bluemix PaaS and it's gaining some serious traction, so it's time to make some tweaks. Did you design your application in a way that it can scale in the cloud? Were you even thinking about the cloud when you built the app? If not, chances are your app is going to break. Check out this upcoming eSeminar to learn various techniques for designing applications that will scale successfully in Bluemix, for the …

  • Corporate e-Learning technology has a long and diverse pedigree. As far back as the 1980s, companies were adopting computer-based training to supplement traditional classroom activities. More recently, rich web-based applications have added streaming audio and video, real-time collaboration and other new tools to the e-Learning mix. At the same time, the growing availability of informal learning tools--a category that includes everything from web searches to social media posts--are having a major impact on …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds