Thanks to a great idea and a little guidance from a good friend, I have finished the first version of this utility. This, like most of my other utilities, is a small one. This utility is called BatchDBx (or Batch DB Executer) and its purpose is to run SQL commands against an ODBC data source in an automated manner. If you are new to how ODBC works, you can get more details about it on the free Wikipedia http://en.wikipedia.org/wiki/ODBC website. If you want to skip the talk and get the utility right away download now or get the source code.

The whole idea of ODBC is pretty-much to be something of a universal access point to all sorts of database servers and technologies. This is great as a technology – but, as many would tell you, ODBC access tends to be slower than a direct-to-DBMS connection. I certainly won’t get into the struggle between those that would defend the speed issue, but one of the more under-exploited functions of ODBC has been its potential power as an automation tool. The potential to use ODBC to, say, update records in a database or to add or delete data in an automated fashion can give an administrator tremendous options to do things like data copying, data backup (online), data purging, or something as simple as creating a database using an SQL Statement. Creating a database automatically can be especially useful if you add that to the process of installing a program.

WARNING: DO NOT USE THIS UTILITY ON A PRODUCTION DATABASE UNLESS YOU KNOW EXACTLY HOW IT WILL IMPACT YOUR DATA. I can not be responsible for misuse of this tool (there’s no way I could get data you’ve destroyed anyway). Use caution when using this, and for Pete’s sake test before you you deploy.

Here’s how BatchDBx works:

There is simply one file to run: BatchDBx.exe. This program will run and not show errors or messages on the screen it simply runs, does its work, and exits. The design of the program is, such that it will attempt to suppress all possible errors. When BatchDBx.exe runs it will also create a log file (overwriting the log every time). This log file will contain details about the last operation, details about the SQL commands issued and any errors that might have been returned from the ODBC interface. To ensure the operation runs at a specific time, you might choose to automate the running of this file using the Windows Task Scheduler or using some other automation utility (like Automate for example).

Since there are no windows or options dialogues to set options, you control two main parts of the process by way of two text files. These files need to be completed BEFORE you run BatchDBx.

The first file “CnString.txt” contains a connection string. This connection string is one line of text that describes how to gain access to your database. You only place one connection string into this file and it should be contained entirely on one line. Do not add any other information. You might use the built-in Windows ODBC administrator to graphically build (and test) a connection string or you can find examples of connection strings on this website. One example of a connection string would be (when connecting to a mySQL Database, driver required of course):

Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;

The second file “Cmds.txt” contains the SQL commands that BatchDBx will run against your Database connection. These commands can include most SQL statements with the obvious exception of statements that return data to view. The file itself is simply a text file that is modelled after your typical .SQL file type (if you are familiar with using those files, the format is the same). Commands start with a statement of some sort, for example, CREATE TABLE, and then continue with a specific syntax until the end of command, which is marked by a semicolon “;”. If you do not place a semicolon at the end of the command it will at the very least ensure the command is ignored or at worst run two commands against your ODBC interface (and most likely fail).

Some common SQL statements might be:

to create a new table (Access Database):

CREATE TABLE NameList (IDKey TEXT(10) PRIMARY KEY, NameLast TEXT(24) NOT NULL, NameFirst TEXT(20) NOT NULL, NameMiddleInitial TEXT(1), Notes MEMO, Picture LongBinary, BirthDate DATE);

..and a more readable version of the above (Access Database):

CREATE TABLE NameList
(
IDKey TEXT(10) PRIMARY KEY,
NameLast TEXT(24) NOT NULL,
NameFirst TEXT(20) NOT NULL,
NameMiddleInitial TEXT(1),
Notes MEMO,
Picture LongBinary,
BirthDate DATE
)
;

to update a series of records (Access Database):

UPDATE [6011] SET [Item]=”2″ WHERE [CallNumType] = “O%”;

or a more complex statement (Advantage Database Server):

UPDATE GTAB SET STATE= ‘IN’ WHERE GTAB.NUM=A.NUM AND GTAB.STATE=’ACTIVE’ AND RIGHT(RTRIM(ALL.DESC),3) = ‘.33′ AND LEFT(ALL.DESC,3)=’35’;

BatchDBx will consider each section of text between semicolons a full syntactically correct SQL statement and run it against the ODBC interface as such. In the program’s log file you will find each command categorized and the specific errors returned by the interface for each command (if any).

Since implementations of SQL syntax vary quite a bit from vendor to vendor – you will need to contact the database vendor or look for documentation regarding that server. The SQL language is actually similar across the board regardless of these syntax differences. In addition to differences in actual syntax, there may be naming differences inside various commands. For example, The mySQL implementation of CREATE TABLE may describe a ten-character text field as “char(10)” where the Microsoft Access implementation describes a ten-character text field as “text(10)”.

Here’s an example of how you might use this utility:

Say you have a database and you want to create and prepare a table for use with your application. In our case, we will create/or use a database on the mySQL server and name it Test. You will then build your connection string. If doing this from the localhost, you might do this:

Provider=MySqlProv;Data Source=Test;Integrated Security=””;Password=””;User ID=root;Location=localhost;Extended Properties=””

You’ll need to have the MyOLEDB installed to make this work of course Using MyOLEDB, you will also be able to test this connection.

Next, build the Cmds.txt file. In our example, we may create a table and add data that looks like this:

CREATE TABLE Sample ( ID int4, FirstName char(16), LastName char(26), Street char(31), City char(31), State char(3), Zip char(11), Country char(21), Phone char(14) );

INSERT INTO Sample VALUES (101042707, ‘Sabrina’, ‘Jason’, ‘1620 Stokes Park Road’, ‘Northport’, ‘NY’, ‘11768-1166’, ‘USA’, ‘(512)555-1583’ );

INSERT INTO Sample VALUES (101042708, ‘Sabrina’, ‘Joe’, ‘1620 Stokes Park Road’, ‘Northport’, ‘NY’, ‘11768-1166’, ‘USA’, ‘(512)555-1583’ );

INSERT INTO Sample VALUES (101042709, ‘Sabrina’, ‘Jim’, ‘1620 Stokes Park Road’, ‘Northport’, ‘NY’, ‘11768-1166’, ‘USA’, ‘(512)555-1583’ );

INSERT INTO Sample VALUES (101042710, ‘Sabrina’, ‘Jan’, ‘1620 Stokes Park Road’, ‘Northport’, ‘NY’, ‘11768-1166’, ‘USA’, ‘(512)555-1583’ );

INSERT INTO Sample VALUES (101042711, ‘Sabrina’, ‘Dave’, ‘1620 Stokes Park Road’, ‘Northport’, ‘NY’, ‘11768-1166’, ‘USA’, ‘(512)555-1583’ );

Once you run BatchDBx.exe, the connection string and commands will all be processed and a log will be created in the BachDBx directory. A typical log would look like this:

BatchDBx Loaded… [7/6/2005] [12:01:02 AM]

Reading Connection String:

===========================
—————
Provider=MySqlProv;Data Source=Test;Integrated Security=””;Password=””;User ID=root;Location=localhost;Extended Properties=””
————
Reading 6 SQL command(s):
==============================
Command #1:
————
CREATE TABLE Sample ( ID int4, FirstName char(16), LastName char(26), Street char(31), City char(31), State char(3), Zip char(11), Country char(21), Phone char(14) );
————
Command #2:
————
INSERT INTO Sample VALUES (101042707, ‘Sabrina’, ‘Jason’, ‘1620 Stokes Park Road’, ‘Northport’, ‘NY’, ‘11768-1166’, ‘USA’, ‘(512)555-1583’ );
————
Command #3:
————
INSERT INTO Sample VALUES (101042708, ‘Sabrina’, ‘Joe’, ‘1620 Stokes Park Road’, ‘Northport’, ‘NY’, ‘11768-1166’, ‘USA’, ‘(512)555-1583’ );
————
Command #4:
————
INSERT INTO Sample VALUES (101042709, ‘Sabrina’, ‘Jim’, ‘1620 Stokes Park Road’, ‘Northport’, ‘NY’, ‘11768-1166’, ‘USA’, ‘(512)555-1583’ );
————
Command #5:
————
INSERT INTO Sample VALUES (101042710, ‘Sabrina’, ‘Jan’, ‘1620 Stokes Park Road’, ‘Northport’, ‘NY’, ‘11768-1166’, ‘USA’, ‘(512)555-1583’ );
————
Command #6:
————
INSERT INTO Sample VALUES (101042711, ‘Sabrina’, ‘Dave’, ‘1620 Stokes Park Road’, ‘Northport’, ‘NY’, ‘11768-1166’, ‘USA’, ‘(512)555-1583’ );
————
Running SQL command(s):
==============================
— Start #1 (12:01:02 AM)
— Finish #1 (12:01:02 AM)
——————
— Start #2 (12:01:02 AM)
— Finish #2 (12:01:02 AM)
——————
— Start #3 (12:01:02 AM)
— Finish #3 (12:01:02 AM)
——————
— Start #4 (12:01:02 AM)
— Finish #4 (12:01:02 AM)
——————
— Start #5 (12:01:02 AM)
— Finish #5 (12:01:02 AM)
——————
— Start #6 (12:01:02 AM)
— Finish #6 (12:01:02 AM)
——————

BatchDBx Finished… [7/6/2005] [12:01:02 AM]

If there are no comments or errors in between Start #x and Finish #x, then you can assume the command was completed successfully. otherwise, depending on your implementation, your ODBC driver will return an error and it will be placed in this file.

Some links to different interface implementations:

Jet SQL for Access 2000

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

Creating UDL Files:

http://www.efg2.com/Lab/Library/Delphi/ADO/UDL/index.html

mySQL MyOLEDB:

Documentation: http://www.mysql.com/Downloads/Win32/MyOLEDB.chm

Be sure to check out the readme.txt file included with this program because it contains a slew of links to more information about ODBC drivers elsewhere on the net and anything new I thought about in the time since I wrote this and/or the program itself.

There you have it. If you use this utility to facilitate the creation of databases or to update tables in databases or even automate the purging of databases. You’ll find that, in the right cases, this utility can really streamline the process of automated data operations. If you are interested in seeing the source code, please contact me and I’ll be happy to send that to you.