5.6. mSQL Utilities
We have already covered one of the utilities that ships with mSQL, msqladmin. mSQL provides seven basic utilities for basic interaction with mSQL. In addition to those basic utilities, mSQL supports a lightweight scripting utility called Lite and a web interface called W3-mSQL. Third party developers support numerous tools beyond those that come with mSQL. We will now take a detailed look at the basic mSQL utilities beyond msqladmin and then skim over the list of third party tools. We will address Lite and W3-mSQL later in the book.
5.6.1. Command Line Tools
Each mSQL command line tool comes with detailed help to explain its syntax. In general, however, the syntax mirrors that of the msqladmin command. Specifically, a -h option enables you to specify a host and a -f options enables you to name a specific configuration file.
- msql
This tool is a command line interface into mSQL that enables you to interactively execute SQL against a specific database. In addition to the common options described above, you specify which database you wish to work against. Of all the commands that come with mSQL, this is likely the one you will use the most.
- msqldump
This command outputs the complete contents of a table or a whole database, including the table structure itself, as a series of SQL statements. The output of this utility can be used to backup a database and recreate it on another machine.
- msqlimport
This command reads a formatted file of data and inserts the data into the specified database table. The format of the file can come in any number of forms, including comma-delimited and quoted.
- relshow
This tool displays the structure of the database and tables within them. It is useful, for example, if you want to know what tables exist in a database or what columns exist within a specific table.
- msqlexport
This tool sends the contents of a table to the standard output in the form of a delimited text file. Many other database and applications like Microsoft Excel can read this file and import its data.
5.6.2. Third Party Support
As with any popular Internet product, the third party products that support mSQL are numerous. These products range from conversion utilities to programming interfaces. No matter how you use mSQL, you are certain to make use of at least one of the third party products outlined here. This list cannot, of course, be comprehensive as the availability of third party tools changes daily. You should check with the Hughes and mSQL PC web sites listed earlier in this chapter for the most up-to-date lists of third party tools.
5.6.2.1. Database conversion utilities
- dbf2msql
Translates DBF files into mSQL tables. DBF files are the format of the dBASE database that once was the leading desktop database. Even though dBASE is no longer as popular, the DBF format has become ingrained as the most common cross-application database file format. Every major desktop database can read and write DBF files. This application is thus useful when exporting data to or importing data from commercial desktop databases.
- mSQLpp
Converts Ingres Embedded SQL (ESQL) to SQL readable by mSQL. ESQL is embedded directly into C source files to provide easy access to databases in C programs. This program will translate a C source file with ESQL to a C source file using the standard mSQL API. It is intended to work as a preprocessor, so it is usually possible to use ESQL files -- filtered through the program -- with mSQL without any modification.
5.6.2.2. CGI interfaces
- PHP
Creates HTML pages using special tags recognized by the PHP parser. PHP includes interfaces to most major databases, including MySQL and mSQL. PHP is covered in more detail in Chapter 12, "PHP and Other Support for Database-driven HTML".
- dbadmin
Provides a CGI interface to mSQL. This program is a CGI program that enables you to access any mSQL database table as if it were in an HTML form. You can modify table data and even perform operations on the database itself.
- Jate
A complete CGI interface to mSQL. Jate comes with many features, all of which are accessible via HTML forms. You can view and modify tables and customize the output. Jate imports flat file data through HTML text fields. It also optimizes some data and searches before sending them to the database server.
- mSQLCGI
Serves as another CGI interface to mSQL tables. This CGI program enables you to view and modify mSQL database tables over the web using HTML forms. This particular interface requires you to run a supplied program on each table you wish to use before you can access it over the web.
5.6.2.3. Client applications
- dbview
Shows the structure of a mSQL database. This utility is similar to relshow, but it has a few added features. Most notably, dbview displays the number of records within each table.
- XfSQL
Provides full access to mSQL table data as an Xforms client for the X Window System. Using this tool, you can insert, delete, and view table data under the X Windows System.
- XmSQL
Provides full access to mSQL table data as a library-independent client for the X Window System. It will thus compile on any X Window system.
- mSQLsql
Displays formatted tables. You can use this tool to view a mSQL table in a variety of ways, including customizable borders and delimiters for ASCII formatted tables. You can also produce HTML tables based on user-definable options.
- mSQLwin-relshow
Provides a Windows-based GUI representation of the relshow tool.
5.6.2.4. Programming interfaces
- ConNExS
Provides an interface between mSQL and the NExS spreadsheet application. NExS is a popular spreadsheet that can interactively link with an external data source. ConNExS enables mSQL database tables to serve as data sources for an NExS spreadsheet. Any changes to the spreadsheet will appear in the underlying mSQL table.
- mSQLBase
Exposes the SQLBase API as a C wrapper to mSQL programs. The SQLBase API is a third party database connectivity API that supports several major SQL servers. This program translates the mSQL API into the SQLBase API so that SQLBase clients can work with mSQL.
- mSQLCLI
Encapsulates the mSQL C API so that ODBC clients on OS/2 can run against mSQL. ODBC (Open Database Connectivity) is a popular database-independent API supported by most databases for access from OS/2 and Windows. This wrapper implements the common ODBC features that mSQL supports, but only for the OS/2 operating system. It includes the minimum allowed ODBC functionality.
- msqldll
Packages the mSQL C API in the form of a Windows Data Linked Library (DLL). Using this DLL, you can write Windows applications using VisualBasic or any other Windows programming tool that works against DLLs.
- MsqlJava
Wraps the mSQL TCP/IP network protocol into a Java API that resembles the mSQL C API. It provides a quick start to writing Java applications for developers who already know the mSQL C API but may not be familiar with the Java standard JDBC API. MsqlJava only works under JDK 1.0.
- mSQL-JDBC
Implements the Java standard Java Database Connectivity (JDBC) API on top of the mSQL TCP/IP network protocol. This package supports the maximum level of JDBC functionality possible for mSQL in both JDK 1.1 and JDK 1.2 (Java 2) environments, including the JDBC 2.0 standard. Chapter 14, "Java and JDBC", discusses Java programming against this API in detail, while Chapter 22, "JDBC Reference", provides a full reference for JDBC 2.0.
- MsqlODBC
Implements the ODBC API for mSQL 1 on Windows. David Hughes is currently working on a mSQL 2 implementation.
- mSQLPerl
Enables Perl scripts to access mSQL databases. Chapter 10, "Perl", discusses Perl programming for MySQL and mSQL in detail, and Chapter 21, "Perl Reference", provides a full reference for mSQLPerl.
- mSQLPython
Enables Python scripts to access mSQL databases. Chapter 11, "Python", discusses Python programming for MySQL and mSQL in detail, and Chapter 20, "Python Reference", provides a full reference for mSQLPython.
- mSQLRexx
Supports mSQL access from REXX, a scripting language most commonly found on the OS/2 operating system.
- mSQLTCL
Enables any program in Tcl to access mSQL databases. This support encompasses many Tcl extensions, including Tcl/Tk and Expect.
- mSQLVdb
Provides mSQL database access via the Vdb database library. Vdb is a database-independent C API that enables common client code to access several different backend database servers. Vdb clients can use this API to access mSQL databases.
- zmsql
Provides object-oriented mSQL database access to C++ programs.
5.6.2.5. Miscellaneous
- mod_auth_msql
Authenticates users of the Apache web server. Apache normally controls access through plain text files with user names and encrypted passwords. This Apache module enables you to manage access control inside a mSQL database.
- mSQLEmacs
Expands the basic SQL-mode support in Emacs to handle special indenting and color highlighting for the mSQL variant of SQL.
- msqlexpire
Deletes old data from mSQL tables. To use this program, a table must have a column that contains the age of the data. This program will examine the table for data that is older than desired and remove that data. You can configure msqlexpire to optionally send email notifications after each deletion.
- mSQLSSL
Patches mSQL to support secure SSL-based network communications. In order to take advantage of this product, you need to compile mSQL with it. Once in place, your network communications are totally secure from prying eyes.
- Sqs
Generates unique ID numbers for database tables. Using this program as a permanent daemon on a server, any number of clients can connect to the daemon and create, read, or delete sequences.
 |  |  |
| 5.5. Database Administration |  | 6. SQL According to MySQL and mSQL |
Copyright © 2001 O'Reilly & Associates. All rights reserved.