Query Tool (using ADO) 4.1 Readme

Query Tool (using ADO) 4.1 is a Universal Data Access (UDA) tool. It lets you query OLE DB data sources, author SQL scripts and queries, return query results to a grid or free-form text, retrieve provider properties, execute multiple SQL scripts or stored procedures simultaneously, and more.

Running the application:

Run QTADO.EXE from the folder where you extracted the files.

[A separate ODBC version is also available. Please visit http://gpoulose.home.att.net/ for more information.]

System Requirements

Windows XP or later

Windows 2000 Professional

Windows NT WKS 4.0 w/ sp3 or later¹

Windows Me

Windows 98/95¹

¹Requires Microsoft Office 2000 or later.

Command Line Options and Usage

Usage: QTADO.EXE

           
[-C <Microsoft data link filename>

¹ or <ADO connect string>]

           
[-f <filename to open>]

²

           
[-F <file list to load separated by pipe (|)>]

²

           
[-e <filename to open and execute>]

³

           
[-E <file list to load and execute separated by pipe (|)>]

³

           
[-i <input file> -o <ouput file> -d <delimiter> -q
<auto-publish/export flag>]

³

Examples: ..\QTADO.EXE

           
/C "File Name=<..\test.udl>" -f
"<..\test.sql>"

           
-C "Provider=MSDASQL;DSN=<MY_DSN>" -E
"<..\1.sql|..\2.sql|..\3.sql>"

           
-C "File Name=<..\test.udl>" -i
"<..\test.sql>" -o "<..\1>.adtg"

           
-C "File Name=<..\test.udl>" -i
"<..\test.sql>" -o "<..\2>.csv" -d
","

           
-C "File Name=<..\test.udl>" -i
"<..\test.sql>" -o "<..\2>.txt" -d
"\t" -q "1"

           
-C "File Name=<..\test.udl>" -i
"<..\test.sql>" -o "<..\3>.xml" -q
"1"

           
-C "Provider=SQLOLEDB;Data Source=<Server|Instance
Name>"

           
/C "Provider=MSDAORA;User ID=<UID>;Data Source=<Server
Name>"

           
/C "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=<..\test.mdb>"

           
/C "Provider=MSDASQL;DSN=<MY_DSN>;UID=<UID>;PWD=<PWD>"

           
-C "Provider=MSDASQL;Driver={IBM DB2 ODBC
Driver};DBALIAS=<DBALIAS>"

           
-C "Provider=IBMDADB2;Password=<PWD>;User ID=<UID>;Data
Source=<DBALIAS>;"

           
-C "Provider=OraOLEDB.Oracle;Data Source=<Service_Name>;User
ID=<UID>;"

           
-C "Provider=OraOLEDB.Oracle;Data Source=<Service_Name>;User
ID=/;Password="

           
-C "Provider=OraOLEDB.Oracle;Data
Source=<Service_Name>;OSAuthent=1"

           
-C "Provider=MSDASQL;Driver={SQL Server};Server=<Server Name>;UID=<UID>"

Tip: Connection string may be copied from within the application by right clicking on the Schema window pane root node and choosing Connection String.

¹To create a data link file, follow these steps:

1. Open Windows Explorer.

2. Select the folder in which you want to create the .udl file. Right-click in the right, or results pane, point to New, and then click Text Document.

3. On the Tools menu, click Folder Options. On the View tab, clear the Hide file extensions for known file types check box and then click OK.

4. Right-click on the text file you created in step 2, and then click Rename. Type the new file name using a .udl file extension. Press Enter.

5. A warning may appear, explaining that changing file extensions may cause files to become unusable. Disregard this warning.

6. Double-click the newly created file to set the properties for the Data Link. You will see the following tabs:

Provider | Connection | Advanced | All

7. The Provider tab allows you to specify the provider (the default is Microsoft OLE-DB provider for ODBC drivers).

8. The Connection tab allows you to specify either the data source name designated in the ODBC Administrator or allows you to provide OLE-DB provider specific settings. Here are two ways you can provide information:

1. If you choose Microsoft OLE-DB provider for ODBC drivers, then:

· Use a predefined DSN name for the connection (it has all the information to connect to the Data source).

· Build a connection string or use a known connection string.

2. Use a OLE-DB provider specific settings.

9. Click OK to complete the settings. To learn more about Data Link files, please see the help file, Msdasc.hlp found in the ..\Program Files\Common Files\System\ole db directory.

10. Open a text editor, such as Microsoft Notepad, and open the newly created file. You will see the connection string that the data link represents.

Users can store .udl files anywhere on their system or network.

Note: You can manually build a connection string as long as it conforms to the syntax rules.

For information on making the Microsoft Data Link shortcut available on the Windows 2000 shell, please refer to:

SAMPLE: How to Create a Data Link File with Windows 2000 (Q244659)

²Analogus to dropping of file(s) onto the application.
³Application runs in minimized state once the file(s) is/are loaded and exits after the query execution if the autocommit transaction mode is on, and if there are no errors. When auto-publish/export flag is set to TRUE or 1 the resulting XML or CSV data will be published or exported, respectively.

Batches

Query Tool recognizes the keyword GO as the default batch separator. Users can change this value by choosing Current Connection Options from the Query menu or by choosing Options and New Connections from the Tools menu. The latter option is persistent for new connections.

Users must follow the rules for batches. For example: The scope of a local (user-defined) variable is limited to a batch and cannot be referenced after a query batch separator or in another batch, and so forth. Note that a SQL statement or a comment cannot occupy the same line as a query batch separator. Also note that comments such as '/**/' cannot span across multiple batches.

Oracle scenario:

CREATE
TABLE t1 (c1 NUMBER(10,2))

GO

DECLARE

var1
NUMBER(10,2);

GO

BEGIN

   var1 := 7000; /*yields an error. var1 is
out of scope*/

INSERT INTO t1 VALUES (var1);

END;

GO

SELECT c1
FROM t1

GO

/*demonstrates
invoking Oracle procedures

using
Oracle native syntax and ODBC syntax*/

CREATE
TABLE proc_test (c1 NUMBER(1,0))

GO

BEGIN

INSERT INTO proc_test VALUES (1);

INSERT INTO proc_test VALUES (2);

INSERT INTO proc_test VALUES (3);

END;

GO

CREATE
PROCEDURE del_proc_test (id IN NUMBER)

AS

BEGIN

DELETE FROM proc_test WHERE c1 = id;

END
del_proc_test;

GO

/*use
Oracle native syntax to invoke the procedure*/

BEGIN

del_proc_test (1);

END;

GO

/*use ODBC
escape sequence CALL to invoke the procedure*/

{CALL
del_proc_test (2)}

GO

SELECT *
FROM proc_test

GO

SELECT
'Only one row is left out' FROM dual

GO

DROP TABLE
proc_test

GO

DROP
PROCEDURE del_proc_test

The following example demonstrates how to use an Oracle PL/SQL package to retrieve resultsets from an Oracle stored procedure using OLE DB provider for ODBC drivers (MSDASQL.DLL) and Microsoft ODBC Driver for Oracle version 2.0 or higher or Microsoft OLE DB provider for Oracle.

The PL/SQL code below defines a package containing two procedures that return different resultsets, and then provide ways to return resultsets from the package.

To invoke the procedure, use the following syntax:

{CALL <package-name>.<proc-or-func>;

(..., {RESULTSET <max-records-requested>, <formal-array-param_1>,;

<formal-array-param_2>,...,<formal-array-param_n>}, ...)}

Note: The <max-records-requested> parameter must be greater than or equal to the number of rows present in the resultset. Otherwise, Oracle returns an error that is passed to the user by the driver.

PL/SQL records cannot be used as array parameters. Each array parameter can represent only one column of a database table.

CREATE OR REPLACE PACKAGE test_package AS

TYPE t_id IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;

TYPE t_course IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

TYPE t_dept IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;

PROCEDURE proc1

(

o_id OUT t_id,

ao_course OUT t_course,

ao_dept OUT t_dept

);

TYPE t_pk1type1 IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

TYPE t_pk1type2 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

PROCEDURE proc2

(

i_arg1 IN NUMBER,

ao_arg2 OUT t_pk1type1,

ao_arg3 OUT t_pk1type2

);

END test_package;

CREATE OR REPLACE PACKAGE BODY test_package AS

PROCEDURE proc1 (o_id OUT t_id, ao_course OUT t_course, ao_dept OUT t_dept) AS

BEGIN

o_id(1) := 200;

ao_course(1) := 'M101';

ao_dept(1) := 'EEE';

o_id(2) := 201;

ao_course(2) := 'PHY320';

ao_dept(2) := 'ECE';

END proc1;

PROCEDURE proc2 (i_arg1 IN NUMBER, ao_arg2 OUT t_pk1type1, ao_arg3 OUT t_pk1type2) AS

i NUMBER;

BEGIN

FOR i in 1 .. i_arg1 LOOP

ao_arg2(i) := 'row number ' || TO_CHAR(i);

END LOOP;

FOR i in 1 .. i_arg1 LOOP

ao_arg3(i) := i;

END LOOP;

END proc2;

END test_package;

{CALL test_package.proc1({RESULTSET 3, o_id, ao_course, ao_dept})} /*return all the columns in a single resultset*/

{CALL test_package.proc2(5, {RESULTSET 5, ao_arg2, ao_arg3})} /*return all the columns in a single resultset*/

Note that the Microsoft ODBC driver for Oracle 2.0 or higher
does have a special feature of returning multiple resultsets from
packages/procedures.

The driver itself does not support returning multiple resultsets on a single statement. When the Microsoft OLE DB Provider for ODBC drivers (MSDASQL.DLL) attempts to determine if the ODBC driver supports multiple resultsets, it returns FALSE


and, hence the provider cannot be used to return multiple resultsets.

Microsoft OLE DB Provider for Oracle does not support returning multiple resultsets.

DB2 scenario:

-- DB2
supports multiple resultsets

CREATE TABLE t1 (c1 INT);

INSERT INTO t1 VALUES (7);

SELECT c1
FROM t1;

GO

CREATE
VIEW myView

AS

SELECT c1
FROM t1;

GO

SELECT *
FROM myView;

GO

CREATE
PROCEDURE my_procedure

(IN value_param INT)

LANGUAGE SQL

BEGIN

INSERT INTO t1 VALUES (value_param);

END

GO

{CALL
my_procedure (7)} -- execute the procedure

GO

CREATE
PROCEDURE t1_result_set()

LANGUAGE SQL

RESULT SETS 1

BEGIN

DECLARE c1 CURSOR WITH RETURN FOR

SELECT * FROM t1;

OPEN c1;

END

GO

{CALL
t1_result_set ()} -- execute the procedure

GO

SQL Server scenario:

-- SQL
Server supports multiple resultsets

USE tempdb
--

also see item #1 in the Known Problems and Workarounds section

GO

IF
EXISTS(SELECT name FROM sysobjects WHERE id = OBJECT_ID('t1'))

DROP TABLE t1

GO

CREATE
TABLE t1 (c1 int)

INSERT
INTO t1 VALUES (7)

SELECT c1
FROM t1

GO

IF
EXISTS(SELECT name FROM sysobjects WHERE id = OBJECT_ID('sp_test_proc'))

DROP PROC sp_test_proc

GO

CREATE
PROC sp_test_proc

(@n int)

AS

SET NOCOUNT
ON

DECLARE
@return_value int

SET
@return_value = -1

INSERT
INTO t1 VALUES (@n)

-- test
the error value

IF @@ERROR
<> 0

BEGIN

   -- return 99 to the calling program to
indicate failure

PRINT 'Error: INSERT operation failed.'

SET @return_value = 99

END

ELSE

BEGIN

   -- return 0 to the calling program to
indicate success

PRINT 'INSERT operation succeeded.'

SET @return_value = 0

END

RETURN
@return_value

GO

SET
NOCOUNT ON

DECLARE @n
int

SET @n =
-1

EXECUTE @n
= sp_test_proc 7

SELECT @n
return_value

GO

SELECT
@@VERSION

sp_help t1
/*yields an error. EXECUTE keyword is required if the

           statement is not the first
statement in the batch*/

GO

IF
EXISTS(SELECT name FROM sysobjects WHERE id = OBJECT_ID('sp_test_proc'))

DROP PROC sp_test_proc

GO

CREATE
PROC sp_test_proc

(@n int
OUTPUT)

AS

SET @n =
7000

GO

DECLARE @n
int

SET @n =
-1

GO

EXEC
sp_test_proc @n OUTPUT /*yields an error. @n is out of scope*/

SELECT @n
output_value

GO

Scenario: Data source/provider does not support multiple resultsets.

CREATE
TABLE t1 (c1 int)

GO

INSERT
INTO t1 VALUES (7)

GO

INSERT
INTO t1 VALUES (8)

GO

SELECT c1
FROM t1

GO

Note that not all data sources and/or providers support '/**/' and/or '--' comment syntaxes.

Tip: While in the Messages window pane, simply double click on the error message to jump to the batch containing the error.

Transactions

By default Query Tool operates in autocommit transaction mode on. Users can override this behavior by initiating a transaction programmatically either explicitly or implicitly or by choosing Options and New Connections from the Tools menu and unchecking the Autocommit check box found under Transactions thereby initiating a transaction implicitly.

SQL Server scenario:

-- Assuming Sybase system 10 or above or Microsoft SQL Server 6.5

-- or above. For Sybase, replace SET IMPLICIT_TRANSACTIONS {ON | OFF}

-- with SET CHAINED {ON | OFF} in the code below.

USE tempdb -- also see item #1 in the Known Problems and Workarounds section

CREATE TABLE t1 (a int)

INSERT INTO t1 VALUES (1)

PRINT 'USE EXPLICIT TRANSACTION'

BEGIN TRAN

INSERT INTO t1 VALUES (2)

SELECT 'tran count in transaction' = @@TRANCOUNT

COMMIT TRAN

SELECT 'tran count outside transaction' = @@TRANCOUNT

PRINT 'SETTING IMPLICIT_TRANSACTIONS ON'

SET IMPLICIT_TRANSACTIONS ON

PRINT 'USE IMPLICIT TRANSACTIONS'

-- no begin tran needed here

INSERT INTO t1 VALUES (4)

SELECT 'tran count in transaction' = @@TRANCOUNT

COMMIT TRAN

SELECT 'tran count outside transaction' = @@TRANCOUNT

PRINT 'USE EXPLICIT TRANSACTIONS WITH IMPLICIT_TRANSACTIONS ON'

BEGIN TRAN

INSERT INTO t1 values (5)

SELECT 'tran count in transaction' = @@TRANCOUNT

COMMIT TRAN

SELECT 'tran count outside transaction' = @@TRANCOUNT

SELECT * FROM t1

DROP TABLE t1

COMMIT TRAN

DB2 Scenario:

In DB2, a transaction is started implicitly with the first executable SQL statement and ends with a COMMIT or ROLLBACK or ends when the program terminates and, only when the autocommit transaction mode is off.

Choose Options and New Connections from the Tools menu and uncheck the Autocommit check box found under Transactions and connect to an DB2 data source to initiate an implicit transaction.

CREATE TABLE test_tran (c1 INT, c2 VARCHAR(12));

INSERT INTO test_tran VALUES (1, 'John'), (2, 'Reji'),

(3, 'Roy'), (4, 'Sherin');

SELECT * FROM test_tran;

COMMIT;

UPDATE test_tran SET c2 = 'Ashley';

SELECT * FROM test_tran;

ROLLBACK;

UPDATE test_tran SET c2 = 'Ashley' WHERE c1 = 1;

SELECT * FROM test_tran;

DROP TABLE test_tran;

COMMIT;

Oracle Scenario:

In Oracle, as in DB2, a transaction is started implicitly when an insert, update, or delete operation is performed, and, only when the autocommit transaction mode is off.

Choose Options and New Connections from the Tools menu and uncheck the Autocommit check box found under Transactions and connect to an Oracle data source to initiate an implicit transaction.

CREATE TABLE test_tran (c1 NUMBER(1,0), c2 VARCHAR2(12))

BEGIN

INSERT INTO test_tran VALUES (1, 'John');

INSERT INTO test_tran VALUES (2, 'Reji');

INSERT INTO test_tran VALUES (3, 'Roy');

INSERT INTO test_tran VALUES (4, 'Sherin');

END;

SELECT * FROM test_tran

COMMIT

UPDATE test_tran SET c2 = 'Ashley'

SELECT * FROM test_tran

ROLLBACK

UPDATE test_tran SET c2 = 'Ashley' WHERE c1 = 1

SELECT * FROM test_tran

DROP TABLE test_tran

COMMIT

Query Tool uses a default transaction isolation level value of READ COMMITED when in autocommit mode off. READ COMMITED permits the following:

Non-repeatable Read: Transaction T1 retrieves a row; then transaction T2 updates that row and T1 retrieves the "same" row again. T1 has now effectively retrieved the "same" row twice and has seen two different values for it.

Phantoms: T1 reads a set of rows that satisfy certain search conditions. T2 then insert one or more rows that satisfy the same search condition. If T1 repeats the read, it will see rows that did not exist previously - "phantoms".

READ COMMITED does not permit the following:

Dirty Read: T1 modifies a row. T2 then reads the row. Now T1 performs a rollback - so, T2 has seen a row that never really existed.

Note that the application may hang, if there is/are any open transaction(s) on a connection or connections and, when attempting to use the Schema browser on the same connection or connections on the same data source. To workaround the problem, first commit or rollback the transaction on the connection or connections on the same data source and then use the Schema browser on the same connection or connections on the same data source.

ODBC Scalar Functions

Scalar Functions are useful if you are writing portable SQL statements across different data sources. ODBC specifies the following types of scalar functions:

· String Functions

· Numeric Functions

· Time, Date, and Interval Functions

· System Functions

· Explicit Data Type Conversion Function

· SQL-92 CAST Function

The escape sequence for calling a scalar function is:

{fn <scalar_function_name>(<argument_list>)}

Scalar functions operate on the column(s) of a resultset and/or the column(s) that restrict row(s) of a resultset. To determine which scalar functions are supported by a driver, choose ODBC Driver Info from the Help menu and go through the SQL Functions list found under Functions tab.

The following two examples illustrate the use of a scalar function CONVERT. These examples assume the existence of a table called employees, with an empno column of type SQL_SMALLINT and an empname column of type SQL_CHAR.

If you specify the following SQL statement:

SELECT empno FROM employees WHERE {fn CONVERT(empno, SQL_CHAR)} LIKE '7%'

· A driver for ORACLE translates the SQL statement to:

SELECT empno FROM employees WHERE TO_CHAR(empno) LIKE '7%'

· A driver for SQL Server translates the SQL statement to:

SELECT empno FROM employees WHERE CONVERT(char, empno) LIKE '7%'

If you specify the following SQL statement:

SELECT {fn ABS(empno)}, {fn CONVERT(empname, SQL_SMALLINT)} FROM employees WHERE empno <> 0

· A driver for ORACLE translates the SQL statement to:

SELECT ABS(empno), TO_NUMBER(empname) FROM employees WHERE empno <> 0

· A driver for SQL Server translates the SQL statement to:

SELECT ABS(empno), CONVERT(smallint, empname) FROM employees WHERE empno <> 0

· A driver for Ingres translates the SQL statement to:

SELECT ABS(empno), INT2(empname) FROM employees WHERE empno <> 0

Users can mix calls to scalar functions that use native syntax and calls to scalar functions that use ODBC syntax. For example, assume that we have a table called customers with a column name and names are stored as a last name, a comma, and a first name. The following SQL statement creates a resultset of last names of customers in the customers table. The statement uses the ODBC scalar function SUBSTRING and the SQL Server scalar function CHARINDEX and will execute correctly only on SQL Server.

SELECT {fn SUBSTRING(name, 1, CHARINDEX(',', name) - 1)} FROM customers

For maximum interoperability, users should use the CONVERT scalar function to make sure the output of a scalar function is the required type.

Tip: To guard against the unintentional use of non-portable SQL extensions, turn on the FIPS² FLAGGER and go through the messages in the Messages window pane.

SQL Server scenario:

-- Assuming Microsoft SQL Server 6.5 or above

SET FIPS_FLAGGER 'entry' -- <'entry' | 'full' | 'intermediate' | off>

Oracle scenario:

/*Assuming Oracle 7.1 or above*/

ALTER SESSION SET FLAGGER = entry /*<entry | intermediate | full | off>*/

SELECT TO_CHAR(2*3) FROM dual

¹Native OLE DB providers may also support ODBC scalar functions for maintaining portability and/or compatibility issues.

²Federal Information Processing Standard (FIPS). Standards that apply to computer systems purchased by the United States government. Each FIPS standard is defined by the National Institute of Standards and Technology (NIST). The current standard for SQL products is FIPS 127-2, which is based on the ANSI SQL-92 standard. ANSI SQL-92 is aligned with ISO/IEC SQL-92.

ODBC aligns with the following specifications and standards that deal with the Call-Level Interface (CLI). (The ODBC features are a superset of each of these standards.)

· The X/Open CAE Specification "Data Management: SQL Call-Level Interface (CLI)"

· ANSI/ISO/IEC 9075-3:1995 (E) Call-Level Interface (SQL/CLI)

Known Problems and Workarounds

1. In the case of Microsoft OLE DB provider for ODBC drivers (MSDASQL.DLL) for SQL Server, the T-SQL keyword USE may not switch the database context to the specified database. To workaround this problem, either use the combo box provided on the toolbar to change the database context or place the USE <database> statement in a separate batch.

2. In the case of ORACLE, the following errors may appear when the application is launched from a location that exceed eight characters and/or when it contain special characters:

When using Microsoft OLE DB provider for Oracle:

ORA-12162: TNS:service name is incorrectly specified

State: (null), Native: 0, Source: Microsoft OLE DB provider for Oracle

When using MSDASQL provider for Microsoft ODBC driver for Oracle:

[Microsoft][ODBC driver for Oracle][Oracle]ORA-12162: TNS:service name is incorrectly specified

State:NA000, Native:12162, Source: Microsoft OLE DB provider for ODBC drivers

When using MSDASQL provider for Oracle ODBC driver:

[Oracle][ODBC][Ora]General error

State:S1000,Native:12162,Source: Microsoft OLE DB provider for ODBC drivers

To fix the above problems, choose a short location (less than or equal to eight characters) that do not contain any special characters besides making sure that OracleTNSListener service is running.

3. The following may be observed when executing query batches using Microsoft OLE DB Provider for ODBC Drivers (MSDASQL.DLL):

MDAC 2.7/2.6

Character columns (with size 2147483647) may return NULL values even if the columns contain non-NULL values.

MDAC 2.5

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

State: (null), Native: 0, Source: Microsoft OLE DB Provider for ODBC Drivers

Most often, this problem can be fixed by choosing Current Connection Options from the Query menu or choosing Options and New Connections from the Tools menu and setting the Cache size value to 1. The latter option is persistent for new connections.

4. When you connect to Sybase 11.x with the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL.DLL) and the Sybase ODBC driver, and, if the catalog name is specified in the connection string or in the Data Link Properties under the connection tab, you may encounter the following error:

HRESULT: 0x8000FFFF; Error: Catastrophic failure

To workaround the problem, do not specify the catalog name in the connection string or in the Data Link Properties under the connection tab. Once the connection is established, use the combo box provided on the toolbar to specify the catalog name.

5. When saving a Table/View/Column by choosing Save As or a query batch by choosing Execute and Save As may produce incorrect results if the resultset(s)'s column(s) contains NULL values. This problem occurs only when you select XML Files (*.xml) from the Save as type combo box. You can workaround this problem as follows:

-- Assuming SQL Server scenario

USE tempdb

IF EXISTS(SELECT name FROM sysobjects WHERE id = OBJECT_ID('t1'))

DROP TABLE t1

CREATE TABLE t1 (c1 char(10), c2 char(10))

INSERT INTO T1 (c1) VALUES ('Hello')

INSERT INTO t1 (c2) VALUES ('Hi there')

SELECT c1, c2 FROM t1 -- may produce incorrect output

SELECT ISNULL(c1, '') c1, c2 FROM t1 -- ok

-- or

SELECT {fn IFNULL(c1, '')} c1, c2 FROM t1 /*ok. This is portable if you are using Microsoft

OLE DB Provider for ODBC drivers (MSDASQL.DLL). For more info, please see ODBC Scalar Functions*/

Unless otherwise noted, the persisted XML recordset is read-only and cannot be used for Update, Delete, or Insert operations.

The Save As and Execute and Save As operations can be invoked by right clicking on a Table/View/Column name in the Schema window pane and choosing Execute and Save As from the Query menu, respectively.

6. Date fields may not be formatted correctly when exporting grid results to Excel by choosing Save As from the File menu or by choosing Save Selection by right clicking on the grid and when you opt to have the data exported to Excel. To workaround this problem, manually format the date fields from within Excel.

7. When you copy grid data and paste it into Excel, Excel may show a weird character between each column. To workaround this problem, use a new instance of Excel.

8. Using very large resultset(s) can cause problems when scrolling the grid. When you drag the thumbtrack to the bottom of the vertical scrollbar, the thumbtrack might jump back to the top. This happens whenever the top row of the grid would have been greater than 65,535. To workaround this problem, limit the resultset to fewer than 65,536 records. While the grid can display more rows, you cannot use the thumbtrack to scroll past this number.

9. Windows NT/Me/98/95 specific:

When you right-click in the Query/Grid/Results/Messages window pane while a context menu is displayed you may not be able to track the context menu at the position where you clicked. To workaround this problem, first dismiss the context menu and then do a right-click.

10. The application may go to an indeterminate state or may end up in a fatal crash if you attempt to retrieve more than 2 million (2,000,000) or so rows. There is no workaround for this problem currently.

11. The “DSN-less” logins found under File menu and Connect sub-menu is invoked with minimal ODBC driver-specific <keyword=value> pairs. And, you may not be able to connect to some databases using the specified “DSN-less” login. To workaround the problem, first define a Data Source Name (DSN) using the ODBC Data Source Administrator found under Tools menu and then use the defined DSN by choosing Data Link Properties from the File menu and Connect sub-menu, and choosing Microsoft OLE DB Provider for ODBC Drivers to establish the connection.

12. The Edit Find in Files operation may return incorrect results if the file searched contain lines longer than the character maximum the application can handle. To workaround the problem, perform the Find in Files operation with Output file names only on. Once the results are displayed choose Next Error Batch/Tag from the Edit menu or press F4 or double click on the filename in the Results window pane, follow the onscreen instructions (if, any), and press F3 to find the subsequent occurrence(s) within the same file. The Find in Files operation can be invoked by pressing Ctrl+Shift+F.

13. The application may hang, if there is/are any open transaction(s) on a connection or connections and, when attempting to use the Schema browser on the same connection or connections on the same data source. To workaround the problem, first commit or rollback the transaction on the connection or connections on the same data source and then use the Schema browser on the same connection or connections on the same data source. Also see Transactions.

Known Limitations and Workarounds

1. The Results in Text option found under the Query menu is good only for retrieving Procedure/View text. If this option is not selected, results will be shown in grid by default.

2. The maximum number of results grid generated is limited to 1,500.

3. The total number of connections is limited to 512.

4. The maximum number of Schema window pane items that can be printed is limited to 1,820 (approx.).

5. The maximum number of Grid rows that can be printed is limited to 2,046 (approx.)¹.

6. The maximum number of SQL URL links that can be added to the Help menu is limited to 512.

7. The edit control used in this application is limited to 16 MB (16,777,215 bytes) of text.

8. The maximum number of “DSN-less” Logins is limited to 512.

Unless otherwise noted, the above limitations depend on the available system resources and the operating system.

¹Tip: To print a large resultset, choose Save As by right clicking on a Table/View/Column name in the Schema window pane or choose Execute and Save As from the Query menu and opt to have the data exported to Excel or XML compatible browsers (also see item #5 in the Known Problems and Workarounds section) automatically.

Unsupported Features

1. Connection Pooling is not supported. This behavior is by design. If a connection fails during a query processing, the Multiple Document Interface (MDI) child window that holds the connection is unusable. You can workaround this problem by choosing New from the File menu without doing an explicit Connect from the File menu.

2. Print previewing of Query window pane, Results window pane (in text mode), and Messages window pane is not supported currently.

File List and Uninstall Information

Default "Unzip To Folder":

C:\Tools\QTADO40

File name, Version, File date, and Size:

QTADO.EXE, 4.1.1.7, 07/07/2003, 1.07 MB

PI.DLL, 2.0.1.3, 07/07/2003, 236 KB

ODI.DLL, 2.0.1.1, 07/07/2003, 308 KB

UINSTQTA.EXE, 2.0.1.2, 07/07/2003, 48 KB

MSVCRT.DLL, 6.1.8924.0, 05/04/2001, 285 KB

MSHFLXGD.OCX, 6.0.88.4, 5/22/2000, 429 KB

QTADO.xsl, 1 KB

QryTool.reg, 1 KB

Readme.htm, 219 KB

OrderForm.txt, 4 KB

Uninstall Information:

1. Run ..\QTADO40\UINSTQTA.EXE.

2. Delete ..\QTADO40.

Site Licenses, Pricing, and Ordering Information

Site Licenses:

A site license for Query Tool (using ADO) 4.1 entitles an organization to receive one copy of the distribution package and duplicate the software as necessary for use inside or outside an organization on the specified number of computers. See the table below for site license pricing.

[Note: Customers who bought version 3.5 on or before February 18, 2002 and wishing to upgrade to version 4.1 may contact the author at gpoulose@att.net to request a new product ID. Please indicate version 3.5 product ID in your e-mail. All other version 3.5 customers please see the table below for upgrade pricing.]

Pricing:

Single copy: US $25.00 (upgrade price US $18.00) each

2 to 9 computers: $20.00 (upgrade price $15.00) each

10 to 49 computers: $16.00 (upgrade price $12.00) each

50 to 499 computers: $12.00 (upgrade price $9.00) each

500 to 999 computers: $10.00 (upgrade price $8.00) each

Over 1000 computers: $7.00 (upgrade price $6.00) each

Unlimited licenses (for use inside an organization/company)¹: $700.00

Source code² (for use inside an organization/company)¹: $1600.00

¹Restrictions apply.

²Compiles under Microsoft Visual C++ .Net 7.0 or higher.

PayPal customers outside the United States, please add 3.9% + US $0.30 fee.

PayPal customers within the United States, please add 2.9% + US $0.30 fee.

New York residents, please add 8.25% sales tax.

Discounts upto 25% is available for Federal, State, and Local Governments, and Educational Institutions (US customers only).

Diskette order(s) will be shipped within five business days after they are received via the US mail.

Upgrades are free upto version 5.0.0.0.

Ordering Information:

To order, send the completed Order Form and a check or a bank/personal/postal money order. Postal money order must be in US dollars only.

Customers outside the United States, please use the Currency Converter to calculate the foreign equivalent (please add US $10.00 fee) and enclose a check for the calculated amount.

PayPal customers, please use the e-mail ID gpoulose@att.net to send the payment. Please make sure that you also send the completed Order Form as an attachment to gpoulose@att.net.

Payment address:

George Poulose

147 Lake Shore Road

Unit #24-A

Lake Ronkonkoma, NY 11779-3167

USA

Credit card orders are not accepted. When payment is received you will be sent a Product ID.

Customer information is considered confidential and will not be shared or distributed to any third party.

Comments and Bug Report

If you have any comments, please send it to: gpoulose@att.net

If you encounter a problem, please visit http://gpoulose.home.att.net/ to obtain the latest version. If you still have problems, please record all the information relevant to the problem, determine if the problem is reproducible, and if so, how, and send this information to: gpoulose@att.net

License Agreement

Unless you have a different license agreement signed by the author your use of this software indicates your acceptance of this license agreement.

Registered Version:

One registered copy of Query Tool (using ADO) 4.1 may either be used by a single person who uses the software personally on one or more computers, or installed on a single workstation used non-simultaneously by multiple people, but not both.

You may access the registered version of Query Tool (using ADO) 4.1 through a network, provided that you have obtained individual licenses for the software covering all workstations that will access the software through the network.

Evaluation Version:

Subject to the terms below, you are hereby licensed to use this software for evaluation purposes without charge for a period of 70 days. If you use this software after the 70-day evaluation period a registration fee of US $25.00 is required.

Terms and Conditions:

Provided that you verify that you are distributing the evaluation version you are hereby licensed to make as many copies of the evaluation version of this software and documentation as you wish; Give exact copies of the original evaluation version to anyone; And distribute the evaluation version of the software and documentation in its unmodified form via electronic means. There is no charge for any of the above. You are specifically prohibited from charging, for any such copies, however made; And from distributing the software and/or documentation with other products (commercial or otherwise) in any form without prior written permission from the author.

Warranty

This software and the accompanying files are sold “as is” without express or implied warranty. Use it at your own risk. Any liability of the author will be limited exclusively to product replacement or refund of purchase price.

Acknowledgements

Thanks to the following people:

Ronald Pihlgren, ODBCINFO: Retrieves ODBC Driver Capabilities, MSDN 2002.

Dino Esposito, Cutting Edge: XML Database Applications, Microsoft Internet Developer, August 1999.

Ivan Zhakov, MDI Windows Manager Dialog, Codeproject.com 2000.

Iuri Apollonio, Sort List (numeric/text) Using Callback, Codeguru.com 1999.

Oleg G. Galkin and Kirk Stowell, Show/Hide Static Panes, Codeguru.com 1999.

Koay Kah Hoe, A Print Enabled Tree View, Codeproject.com 2000.