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

  • The impact of a data loss event can be significant. Real-time data is essential to remaining competitive. Many companies can no longer afford to rely on a truck arriving each day to take backup tapes offsite. For most companies, a cloud backup and recovery solution will eliminate, or significantly reduce, IT resources related to the mundane task of backup and allow your resources to be redeployed to more strategic projects. The cloud - can now be comfortable for you – with 100% recovery from anywhere all …

  • Download the Information Governance Survey Benchmark Report to gain insights that can help you further establish business value in your Records and Information Management (RIM) program and across your entire organization. Discover how your peers in the industry are dealing with this evolving information lifecycle management environment and uncover key insights such as: 87% of organizations surveyed have a RIM program in place 8% measure compliance 64% cannot get employees to "let go" of information for …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds