Synametrics Technologies

Revolving around the core of technology

Frequently Asked Questions About WinSQL

Answers

My serial number won't work.

Serial numbers in WinSQL are tightly integrated with the company name. A slight modification in the company name will result in an error when trying to register. Therefore, it is important you copy/paste the serial number from the email that was sent to you from Synametrics Technologies, Inc.

Additionally, serial numbers are version specific when using WinSQL Professional or Standard. This means a serial number sent to you for version 15 will not work with version 16. This rule does not apply for WinSQL Lite.

Please contact our support department via email along with a screenshot of the values you're entering to register if you still run into problems registering WinSQL.

Go to top

I need to register WinSQL if I am using the Lite version.

Yes. You must register the Lite version. There is no charge for it. Once you fill out the registration form, an email will be sent to you within an hour. Click here for more information.

Go to top

How can I register WinSQL Professional.

WinSQL professional is not a freeware product. You can download a 30 days trial version. If you decide to keep it, you must get the serial number that will upgrade the product. Click here for more information.

Go to top

How can I uninstall WinSQL from my machine.
This is a two step process:

Unregister ODBC drivers if you have registered them earlier
Use Add/Remove program in Windows Control Panel to remove WinSQL

Go to top

What should be installed on my machine to use WinSQL?
You need MDAC version 2.5 or above. Every Windows machine comes with MDAC and you should not have to install it separately. For any reason you need to reinstall it can be downloaded from MSDN's site

To what version of MDAC you are currently using check the following node in Windows Registry.

HKEY_LOCAL_MACHINE
    \Software
        \Microsoft
            \DataAccess
                \Version

Go to top

Why should I use WinSQL as opposed to the native tools that comes with the database?

There are several benefits, some of them are listed below:

Heterogenous Environment - WinSQL is not limited to one database. Therefore, it makes perfect sense in a heterogenous environment. For example, you have your Accounting database on IBM DB2 and Inventory on Sybase. WinSQL gives you one environment to work with both of them.

User Interface - WinSQL gives provides you an excellent interface to your back-end database. In fact, in some cases it is much better than the tools provided by the database vendor. Compare it with Sybase's SQL Advantage and Informix's DBAccess. According to one client "WinSQL is better than anything I have seen from Informix".

Cost - WinSQL is very cost effective. In fact, the Lite version of WinSQL is absolutely free.

Rich Features - WinSQL Professional includes features that are usually shipped as separate products, which can potentially cost you thousands of dollars. There is no product in the market that bundles features like Test Data Generation, E/R Diagrams, Data Export, HTML Export, Query Wizards and more all in one product.

Go to top

Does WinSQL support any built-in functions.
No. However, it supports all ODBC Scalar functions as well as any function supported by the back-end database.

Consider the following scenario:

You have a raw text file with the following fields:

FirstName	Char(20)
LastName	Char(20)
HireDate	Date

Important: You must have defined a layout of the text file in ODBC DSN Setup. Click here for instructions.

Problem: You want to run a query that will give you all the people who were hired on a Friday.

Solution:

select FirstName, LastName
from employees.txt
where {fn DAYOFWEEK{HireDate)} = 6

For a complete list of supported functions, please refer to WinSQL User's Guide.

Go to top

Does WinSQL support database transactions.
Yes. WinSQL supports transactions in two different modes.

Auto Commit
Manual Commit

In case of auto commit, all queries will automatically commit unless an explicit transaction is started. Consider the following queries that are run against a Sybase SQL Server:

update customers -- This query will automatically commit
set state = 'NY' -- once it is done.

go

begin tran -- explicitly start a transaction

update customers -- This query will run within a transaction
set city = 'New York' -- and will not have any affect until either
-- commit or rollback is issued.

commit tran -- Now commit transaction

The above example assumed the database server to be Sybase. In case of Oracle, you do not have a choice of explicitly starting a transaction. Therefore, you must turn off auto-commit, in which case, you must explicitly COMMIT or ROLLBACK in order to save the changes.

Go to top

What databases does WinSQL work with?

WinSQL uses ODBC to connect to a number of sources. We have tested WinSQL with following databases:

Informix

Oracle

Sybase

IBM DB2 UDB

MS SQL Server

Borland Interbase

PostgreSQL

MySQL

MS Access

Paradox

FoxPro / DBase

Text Files

Please note that WinSQL is not limited to back-ends specified above. As long as you have a driver, which is usually supplied by the database vendor, you can connect to it.

Go to top

I would like to ship WinSQL along with our product. Do I need your permission

You can free redistribute WinSQL. If you have a product and wish to ship WinSQL along with it, you can do that without getting any permission from us. You do not have to pay any royalties. However, you cannot distribute the serial number(s). Every user of WinSQL MUST obtain a serial number from Synametrics Technologies

Go to top

I get an error: "The WINSQL.EXE file is linked to missing export ODBC32.DLL : SQLAllocHandle"

WinSQL requires version 3.x for ODBC manager. You get this message if you are using version 2.x. You can download the latest version of ODBC manager from http://www.microsoft.com/data/odbc

Go to top

My icons and bitmaps don't get displayed properly ever since I downloaded version 3.0
WinSQL 3.0 and above uses the latest version of comctl32.dll. This is a shared library by Microsoft and comes with their latest products including Internet Explorer. WinSQL uses controls in this library to display the images on the buttons and catalog window. The latest version of this file can be downloaded from Microsoft.

Go to top

I get "RichEdit line insertion" error when displaying more than 32K of data in the result window
This error only occurs on Windows 95/98. It does not occur on NT. The reason is that when more than 32K of data is displayed in a Rich Edit control, it raises an Exception. This message is displayed by the RichEd32.dll not by WinSQL. Even though WinSQL catches this exception and increases the capacity of the data, it cannot avoid displaying this message. Hopefully, later version of RichEdit control will allow more data.

Go to top

When I try to display the catalog information in Oracle, it takes very long to fetch the data
In Oracle, two or more users can create tables with the same name. Besides, the server creates many tables and stores procedures to maintain the database. Therefore, having more than 500 objects in an Oracle database is not very uncommon. It is strongly suggested that you use a specific user before selecting the objects. This will not only result in fast retrieval of the catalog information but will also save memory resources.

Go to top

I can't create a stored procedure in Oracle using WinSQL
You must be using the ODBC driver that comes with Oracle. Use the drivers that comes with WinSQL

Go to top

Does WinSQL support transaction and how do they work?
WinSQL support two modes for Transactions:

When Auto commit is ON, all the transactions are automatically committed unless they were started manually. For example in Sybase or MS SQL Server you can send a 'begin tran' command to manually start a transaction. Statements within this kind of transaction are only committed or rollbacked when the user explicitly issues the appropriate command.
However, some databases like Oracle and DB2 does not allow this kind of transactions. Therefore, you need to turn the Auto Commit option to OFF in order to rollback or commit a unit of work.

You can turn Auto Commit OFF at the login screen. Click the "Options" button if the connection options are not visible.

Note: When Auto Commit is OFF, a red indicator appear on the status bar. This acts as a reminder for the user to issue a commit statement at the end.

Go to top

While a query is running I press the cancel button and the application hangs
WinSQL starts a new thread when a user runs a query. While it waits for the server to respond the user can click the stop button to abort, which get called from the primary thread. More specifically, WinSQL calls the SQLCancel method in the ODBC API. Some ODBC drivers block the execution when this method is called even from a different thread - they wait for the server to reply, which can take long. When this happens it appears that the application is hung since the primary thread is waiting for the server's reply.

This only occurs with certain ODBC drivers. For instance the driver for Microsoft SQL Server does not block and therefore, the stop button returns right away.

Unfortunately, this problem in the ODBC driver makes the user believe that WinSQL is not responding. Whereas the limitation is either imposed by the driver or the database.

Note that some database do not support aborting a running query at all. In that case the only choice you have is to terminate the connection from your database end. Terminating a connection should cause SQLCancel method to return right away, giving control back to WinSQL.

Go to top

I get a warning message that says I am not suppose to use the ODBC drivers that come with WinSQL.
You get the following message.
Data Direct warning

WinSQL comes with a set of ODBC drivers. These drivers are made by Data Direct Technologies, which is a third party company. We have a license to bundle their drivers with our product.

You have a license to use these drivers only with WinSQL Standard and Professional Edition. If you are using WinSQL Lite, you cannot use these drivers and therefore, you get this warning message.

You have a couple of options to solve this problem.

Upgrade to Standard or Professional Edition
Use a different driver. You do not have to use the drivers that shipped with WinSQL.

Note: Since this warning is displayed by the ODBC drivers not WinSQL, we cannot change its text to display a more descriptive message.

Go to top

Can I change the default database in MS SQL Server and Sybase.
You must specify the name of the database that you wish to connect in the ODBC DSN setup. However, you can use the "USE DBNAME" command in WinSQL to connect to another database without opening another connection.

Go to top

Where can I download a free Sybase ODBC driver.
You can download the latest ODBC driver from Sybase web site

Go to top

Can I use GO to submit multiple batches?
Sybase and MS SQL Server allows multiple queries in a batch. For example:
select * from customer
select * from employees
However, sometimes you need to separate these queries so that they are submitted as a separate batch. You can change the Query separator in WinSQL to "GO".

In case of Sybase or MS SQL Server, it is recommended that you make the following changes to the environment.

Go to top

What do I need to connect to a Sybase database either on UNIX or NT.
Before you can connect to a Sybase database through ODBC, you need to install the Sybase client. After installation, go to into the DSEDIT utility and specify the location of your server along with network protocol. Then, create an ODBC DSN to connect to the server. Refer to the Sybase documentation for further help.

Go to top

How do I use WinSQL with an Informix database
Before you could successfully connect to an Informix database, you must setup the CLI drivers along with the ODBC. Click here to read an article by James Edmiston

Go to top

When I create a stored procedure I get all sorts of errors
Informix treats a semi-colon as a special character. By default WinSQL uses it as a query terminator. Therefore, when you write a create procedure statement with semi-colons in it, WinSQL breaks the statement into many smaller pieces and sends it to the server which are rejected by the database.
To solve this problem, you should change the query terminator string in WinSQL. Go to Edit->Options in the menu and change the following settings:

Query terminator string - <<Anything but a semi-colon>>, we recommend the word "GO"
Terminator must be on a new line - Check
Terminator string is case sensitive - Uncheck

Go to top

Why do I get errors when I have comments in my code?
DB2 have very limited support for comments in static SQL, which means that you could write them within a stored procedure through control center. It does not support comments in dynamic SQL, like a SELECT statement through ODBC. Therefore, you must select "Local comment parsing" option in the Configuration window (Edit->Option on the menu).

Go to top

I don't see anything in the catalog and get invalid table pointers.

DB2's ODBC driver require some custom packages installed on the server. This is typically done by clicking the "Bind CLI/ODBC support utilities" in the ODBC driver setup. Refer to the image on the right. You will need administrative privileges in order to bind the driver. This step is required for for each database that you need to connect to. In a network environment, if you are using multiple clients that run on different operating systems or are at different versions or service levels of DB2, you must bind the utilities once for each operating system and DB2-version combination.

Binding a utility creates a package, which is an object that includes all of the information that is needed to process specific SQL statements from a single source file.

The bind files are grouped together in different .lst files in the bnd directory, under the installation directory. Each file is specific to a server.

What if I am using the drivers that ships with WinSQL?

Similar steps are required by this driver. However, you don't have to click any button. Necessary packages are automatically installed on the server as long as the user has enough privileges.

What happens in WinSQL when these packages are missing?

WinSQL uses the ODBC API to fetch information about different objects in the database. When these packages are missing you won't be able to see any tables, views or stored procedures in WinSQL's catalog tab.

To find out which packages are missing, run WinSQL in debug mode. This is done by passing -g as a command line argument to WinSQL.exe. In debug mode WinSQL will generate a debug.log file which will contain the names of all missing packages.

Go to top

What do I need to connect to PostgreSQL database?
Before you can connect to a PostgreSQL database, you need to download and install the ODBC driver from Postgres web site.
After installation of the ODBC driver, run the odbc.sql file usually found in the /usr/local/pgsql/share/ directory.

Go to top

How do I create functions using WinSQL?
By default, PL/PGSQL interpreter is not installed on each database. You must install it before creating a function in PL/PGSQL. Use the following command to install one.

createlang --host=hostname --port=5432 --username=postgres --dbname=yourdb --pglib=$PGLIB plpgsql yourdb

Here $PGLIB is the location where plpgsql.so library is installed.

Go to top

Why is my database read-only?
By default, the ODBC DSN is created in a read-only mode. You must modify the DSN and uncheck "Read Only" option in the "Data Source" options.

Go to top

Why do I see all the tables twice in the catalog window?
This problem is caused due to a problem in older versions of the ODBC driver for MySQL. Please upgrade to MyODBC version 3.51.06 to fix the problem.

Go to top

Why do I get errors when I have comments in my code?
MS Access does not support comments natively. Therefore, you must use local comment parsing in WinSQL. Open the configuration screen from Edit->Options on the main menu. Check "Parse comments locally"

Go to top

I get a message "Capability not supported" when trying to display foreign keys. Why?
MS Access supports referential integrity. However, this information is not available through the ODBC driver. WinSQL heavily depends on the capabilities of the driver. In this case the driver does not return required information and therefore, WinSQL cannot display it.

Go to top

How can I get the latest drivers for MS Access?
MDAC 2.8 does not include drivers for Jet. You need to download them separately. Refer to KB Article 829558 for details.

Go to top How can I get the latest drivers for MS Access?

Can I use WinSQL to query raw text files.
Yes you can. All you need is a text ODBC driver. You can download the drivers from Microsoft from free.

Tip: In case of text data, you should specify the format of the text file in the ODBC DSN setup.

Click the "options" button to display additional options. Then click "Define Format" button to specify the data format.

Specify the data type for every field that you have in the text file.

Once you have specified the data format, you can this text file in WinSQL as if it is a table in a database. However, following restrictions apply:

You cannot create an index on any field.
Cannot create Referential Integrity Constraints
You can only use ODBC escape functions or if there are any functions supported by the driver. Check the driver help file for details.

Go to top

I get an error regarding the license when I try to use WinSQL ODBC Drivers.
ODBC License Warning

Synametrics Technologies have licensed these drivers from DataDirect Technologies and you can use these drivers only if you are using the Standard or the Professional version of WinSQL. Trying to use the drivers with WinSQL Lite or any other third party product produces this error message.

Go to top

I already have drivers to connect to my back-end database. Do I have to use WinSQL's drivers?

No. You don't have to use the drivers that are shipped with WinSQL. However, there are a few benefits if you do. Click here for details.

Go to top

Quick Links

Support