ODBCTEST is a small test application that enables you to connect to an ODBC source and execute SQL statements against it. It is there for those of you that do not already have some other (and better) application able to talk to ODBC drivers. Its main strong point is its ability to return the raw ODBC error messages directly to you without any filtering. This can be a very handy feature. You may also find it a nice utility if you often issue long streams of SQL against different ODBC compatible databases.
ODBCTEST is written by Jorgen Grosbol, ISS Data, Denmark, in 1996 and 1997.
ODBCTEST is freeware.
Disclaimer: If you use this software, you and you alone carry the responsibility for errors and/or problems resulting from its use.
There exists a 16-bit and a 32-bit version of the ODBCTEST program, named
ODBCT16.EXE (16 bit version)
ODBCT32.EXE (32 bit version)
The two versions are identical except that the 16-bit version uses 16-bit libraries and the 32-bit version uses 32-bit libraries. If you execute the program under Windows 95 or Windows NT, you can use either program. If you use the program under Windows 3.x, you have to use the 16-bit version of the program.
Note that the 16-bit version talks to 16 bit ODBC drivers while the 32-bit version talks to 32-bit ODBC drivers.
You activate ODBCTEST by simply double clicking its icon. The interface is MDI (multi document interface), so you can access and test several ODBC sources at the same time, one in each window.
Before you can execute SQL statements against an ODBC source, you must connect to it. Use the SQL/Open ODBC connection menu item to perform the connect.
Once the connection is established you use the SQL/Execute SQL commands menu item to enter and submit SQL commands against the data source. You can load and save the SQL statements in files via the buttons placed directly under the SQL source filename field.
You can submit more than one SQL statement if you delimit them by semicolons. If semicolons are used they should be either the last nonblank character on the line or occupy an otherwise blank line by themselves. In addition, you may place SQL comment characters (double dash, --) in front of lines to have ODBCTEST ignore them. Tab characters are translated to blanks before the SQL statement is submitted to the host.
If you execute several SQL statements and an error occurs you are shown the error message and you are asked if you want to cancel the command stream now or if you want to continue processing commands.
Note that due to Windows peculiarities you have to use Ctrl+Enter to place newline characters in the SQL source window.
This function allows you to browse through the list of available tables in your server and to create several types of SQL statements towards them. The SQL dialect may not match the requirements of your driver completely, but even so, this feature can substantially reduce the work needed to create new SQL statements from scratch - and it is very easy to use.
This of ODBCTEST Gives you the ability to change the default data type names in the create statements and also allows you to save and restore the defaults from a file. If you repeatedly use the program to generate create statements for a database system with a deviating syntax this can save you some tedious editing work.
If you activate the ODBCTEST program from a command prompt or from a BAT file you can make it perform SQL commands to a host without opening any windows. You do it like this:
C:> ODBCTxx.EXE =outputfile options sql-statement
where:
'xx' is either 16 or 32 (specifying whether you want to run the 16-bit or the 32-bit version of the program)
'=outfile' is the name of the output file where the result is placed. This must be the first parameter on the command line. If the name contain blanks you must enclose it in single or double quotes. You control the contents of this file by options.
'options' are some set of options where each option has must start with a
slash and a lowercase letter. You cannot group options. Each option must be
prefixed with a slash. If the option contain spaces you must quote the option
value (like this:
/s"My Source")
The possible options are:
/v inserts a line containing program name, version and compile date into the output file.
/i inserts a line with the command line options into the output file.
/m inserts a line containing either the text "OK" or the ODBC error message if something went wrong.
/o lists the available ODBC driver names in the output file. Each name starts on a new line and is prefixed with a colon.
/c lists the name and some column information for each column in the result set (if any) to the output file. Each column is starts on a new line and is prefixed with a '>' character.
/sNNNNN defines the ODBC source name to be used.
/uXXXXX defines the userid to be used when connecting to ODBC.
/pPPPPPP defines the password to be used when connecting to ODBC.
/dX uses the character X as delimiter between data fields returned in the output file. The default is a comma.
/rY replaces all occurrences of character Y with blanks in the returned data fields in the output file. Typically used to remove commas from the text fields to avoid ambiguity about where the field ends.
/n inserts the string NULL in the output file each time a NULL value data field is encountered.
/bN1,N2,N3,... (where ' Nx ' is some positive integer number)
skips to a new line in the output file each time the corresponding number of
columns has been output. If you specify
/b2,3
and have 10 columns in the result set, then the first two columns will be placed
in the first line, the next 3 columns will be placed in the second line and the
last 5 columns will be placed in the third line. In this case each output row
will produce three lines in the output file.
/fN1,N2,N3,... (where 'Nx' is left out or some positive integer) defines the output width of each column. In this way you can force the width of columns to always have the same length effectively creating fixed format output records. The data in each field is left justified. Fields that are longer than the specified number of positions are truncated from the right. If you do not specify the width of a column it defaults to the current character width which may be varying form row to row.
/xABCD (where 'A', 'B', 'C' and 'D' are single characters) defines what
characters in your SQL line should be translated into '(', ')', '<' and
'>' respectively. If you leave this option out the default is
/x[]{}
. This option allows you to use parentheses and comparations in your SQL
statements. So, if you want to write
SELECT COUNT(*) FROM A.B
you actually have to write
SELECT COUNT[*] FROM A.B
. The reason behind this option is that certain characters (like parentheses and
the '>' and '<' operators) are not allowed on command lines in certain
systems.
'sql-statement' is the SQL statement to be executed.
You must start the options on the command line with an equal sign for the program to run in batch mode with no windows displayed.
This may seem somewhat daunting, but really, there is not that much to it. An example:
ODBCT32.EXE =c:/myfile.txt /m /n /sSORUCE1 select * from MY.TABLE
This will make the 32-bit version of ODBCTEST connect to the ODBC source named SOURCE1 and issue the SELECT statement. The result is written to the file c:\myfile.txt. After executing the above line from a command prompt the file should look something like this:
OK
1,55.3,Jensen,
2,NULL,Petersen,
...
The first line is the return code of the select statement (because the /m option was used) and the following lines contain the data rows selected from the table. The /n option ensures that the text NULL is inserted in place of empty (NULL) data values.
The ODBC interface exists in several versions and each version implements several levels (core level, level 1 and level 2). This alone ensures that you cannot be sure that your ODBC driver will work with the ODBCTEST program, but to complicate matters further not all drivers are written exactly to specifications, and some drivers contain bugs.
The ODBCTEST program is written to the ODBC version 2 function level 2, but it does not by far use all functions in the interface, so drivers implementing a lower level may still work. Sometimes this may result in irrelevant error messages. Higher levels of ODBC (like version 3) should of course work with the ODBCTEST.
To summarize: you should always test to see if a driver actually works with the software mix you are going to use. Do not take the driver supplier's word for it.
We have tested ODBCTEST against several ODBC drivers, and our general impression is that the drivers from the major suppliers work just fine. When it comes to drivers for less popular systems, you just have to try them out.
You can reach me at my E-mail address: jorgengros@cybernet.dk, put please remember that this is a freeware program given to you as-is. While I would love to hear from you, I may find it difficult to take time out to answer all mail, so don't be disappointed if you don't get an answer.