About DS SQL Tools

DS SQL Tools is an invaluable tool for the serious database programmer. This first release of the library  provides 2 sets of functions. One that allows validation of SQL queries against the data source and another that enables the programmer to predict the size of a result set without executing the query against the database. 

DS SQL Tools can be useful in a variety of scenarios whenever one of the following is needed:

• Syntax checking and validation against the actual database structure of user or runtime generated queries.
• Estimation of result-set size (number of rows and data size) without query execution - useful for predicting execution time and required storage space for export functions etc...

DS SQL Tools works with MS SQL Server 6.5, 7 and 2000
(SQL Sever 6.5 does not support result-set size prediction)

 DS SQL Tools is a set of COM components and can therefore be used with Scripting languages (VBScript, JavaScript), Visual Basic (Versions 4 and up), VBA (Word, Excel, Access ...), VC++, BC++, Delphi, Power Builder and any other COM compliant software package.

DS SQL Tools Installation

Installing of the component is very simple using the provided installation utility. The file DSSQLTools.dll and is copied to the system directory (like \winnt\system32 for NT or \windows\system for Win95). However if you with to move the file to another directory remember to register the component using the resgsrv32 program.

Change to the directory where you copied the DLL and type:

regsvr32 DSSQLTools.dll

If you get an error message at the end of the installation procedure "cannot register...", some of your system files might need to be updated to the latest version.

If you need to update your system download the latest version of MFC libraries for Windows 9x/NT.
When the download is complete, open the cab file, extract it's contents and run the setup file.

SQL Tools usage samples:

1. Validating a Query against the database server:

1. Create an instance of the SQLParse Object
2. Provide the Connection String
3. Call the VerifyQuery Method

The following code demonstrates how to use DS SQL Tools from VBScript (or ASP). In this example we'll validate a query against a database.

Set QryVal = CreateObject("DSSQLTools.SQLParse")
QryVal.ConnectionString = "dsn=YOUR DSN;uid=Your USER ID;pwd=YOUR PASSWORD;database=YOUR DATABASE"
If QryVal.VerifyQuery("YOUR SQL QUERY") = FALSE then
        ErrDesc = QryVal.LastError
Else
        ErrDesc = "The Query Is Valid"
End If

By reading the value of the LastError property we can determine why the Query was invalid.

2. Estimating the size of a result-set returned by a select statement.

1. Create an instance of the SQLPerf Object

2. Provide the Connect String

3. Call the GetQueryStats Method

4. Read the EstimatedRows and EstimatedBytes property values.

Set QryStats = CreateObject("DSSQLTools.SQLPerf")
QryStats.ConnectString = "dsn=YOUR DSN;uid=Your USER ID;pwd=YOUR PASSWORD;database=YOUR DATABASE"
If QryStats.GetQueryStats("YOUR SQL QUERY") = FALSE Then
       ErrDesc = QryStats.LastError
Else
       NumberOfRows = QryStats.EstimatedRows
       NumberOfBytes = QryStats.EstimatedBytes
End If

If GetQueryStats fails, LastError will contain the error description.
If GetQueryStats succeeds EstimatedRows and EstimatedBytes will respectively hold the number of rows and the number of bytes that the result-set will contain.
Please keep in mind that this function is only supported by SQL Server 7.0 and that the Estimates are only as good as the statistics on the Server. You can find more information on this subject in SQL Server's documentation.

About Upgrades

1. Users can upgrade for free for minor version changes. For example, upgrades from version 1.00 to 1.99 are free. The upgrade from 1.99 to 2.0 may carry an additional license fee.
2. The latest version of the components are always available at http://www.donia.com/products.htm. If a fee is associated with obtaining the upgrade it will be noted on that page.

Upgrade Instructions

To upgrade the component from a previous version please follow these steps:

1. Stop all programs using the component. If you are using it in ASP stop IIS related services such as Gopher, FTP and W3SVC..
2. Install the update using the provided setup utility.
3. Don't forget to run regsvr32 as described above if you choose to move the DLL to another directory.
4. Restart the necessary programs / services.

Technical Support

If you require technical support please send complete details about the problem you are having to support@donia.com. Include the version of DSSQLTools you are using, any error messages, sample code snippets that demonstrate the problem (most problems are scripting errors), information about the hosting environment etc. For example, if you are using ASP to host the component please let me know what version of IIS and ASP you are running (and if you have installed any of the Hot Fixes). The more information you can provide in your request for help, the faster your problems can be resolved.

SQLParse Properties

Property	Description
ConnectionString	Contains the information used to establish a connection to a data source. Sets or returns a String value.
LastError	If the VerifyQuery method returns FALSE this property will hold a String explaining why FALSE was returned. Note: This property is read only

SQLParse Methods

Method	Parameters	Return Value	Description
VerifyQuery	String value of the SQL Statement to be verified against the data source.	True/False based upon success or failure.	Validates SQL Statements against a data source without executing them. Returns TRUE if the Statement is valid. Example: Result = VerifyQuery("Select AuthorName from Authors")
About	None		Use this method to check if you are running an Evaluation version or a Full version.

SQLPerf Properties

Property	Description
ConnectionString	Contains the information used to establish a connection to a data source. Sets or returns a String value.
EstimatedRows	Contains the ESTIMATED number of rows that would be returned by a statement if it was executed. This value should be read after a successful call to GetQueryStats. Note: This property is read only
EstimatedBytes	Contains the ESTIMATED number of bytes that would be returned by a statement if it was executed. This value should be read after a successful call to GetQueryStats. Note: This property is read only
LastError	If the GetQueryStats method returns FALSE this property will hold a String explaining why FALSE was returned. Note: This property is read only

SQLPerf Methods

Method	Parameters	Return Value	Description
GetQueryStats	String value of the SQL Statement to be evaluated against the data source.	True/False based upon success or failure.	Evaluates SQL Statements against a data source without executing them. Returns TRUE if the Statement was evaluated successfully. The next step would be to read the EstimatedRows and EstimatedBytes properties. Example: Result = GetQueryStats("Select AuthorName from Authors")
About	None		Use this method to check if you are running an Evaluation version or a Full version.

www.donia.com

Copyright © 2000 - 2003 Donia Software. All Rights Reserved.