WinSQL     General Questions My serial number won't work. I found a company that is selling WinSQL at a very discounted rate. Should I buy it? Do I need to register WinSQL if I am using the Free version. How can I register WinSQL Professional. How can I uninstall WinSQL from my machine. Why should I use WinSQL as opposed to the native tools that comes with the database? Does WinSQL support any built-in functions. Does WinSQL support any database transaction. What databases does WinSQL work with? What should be installed on my machine to use WinSQL? Distribution I would like to ship WinSQL along with our product. Do I need your permission Error Messages I get an error: "The WINSQL.EXE file is linked to missing export ODBC32.DLL : SQLAllocHandle" My icons and bitmaps don't get displayed properly ever since I downloaded version 3.0 I get "RichEdit line insertion" error when displaying more than 32K of data in the result window Common Problems While a query is running I press the cancel button and the application hangs I get a warning message that says I am not suppose to use the ODBC drivers that come with WinSQL. Database specific questions Oracle When I try to display the catalog information in Oracle, it takes very long to fetch the data I can't create a stored procedure in Oracle using WinSQL Does WinSQL support transaction and how do they work? Sybase/MS SQL Server Can I change the default database in MS SQL Server and Sybase. Where can I download a free Sybase ODBC driver. Can I use GO to submit multiple batches? What do I need to connect to a Sybase database either on UNIX or NT Informix How do I use WinSQL with an Informix database. When I create a stored procedure I get all sorts of errors. IBM DB2 UDB Why do I get errors when I have comments in my code? I don't see anything in the catalog and get invalid table pointers PostgreSQL What do I need to connect to PostgreSQL database? How do I create functions using WinSQL? Why is my database read-only? MySQL Why do I see all the tables twice in the catalog window? MS Access Why do I get errors when I have comments in my code? I get a message "Capability not supported" when trying to display foreign keys? How can I get the latest drivers for MS Access? Text files Can I use WinSQL to query raw text files. ODBC Drivers I get an error regarding the license when I try to use WinSQL ODBC Drivers. I already have drivers to connect to my back-end database. Do I have to use WinSQL's drivers?     Answers My serial number won't work. Besides being case and space sensitive, the version of WinSQL matters as well. You cannot use a serial number that is generated for version 4.6 with version 4.5 or earlier. If you received your serial number after April 15, 2005 you must use WinSQL version 4.6. How can I tell what version am I using? There are a few ways to check that: Open the "About" box found under Help→About on the main menu of WinSQL From Windows Explorer highlight WinSQL.exe file, click the right mouse button to bring up the pop-up menu and check properties. The registration window for WinSQL version 4.6 refers to "http://www.synametrics.com" rather than "http://www.indus-soft.com". Refer to the images below. Version 4.1 Version 4.6 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 lastest 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: Auto Commit Manual Commit 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. 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 Developer 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 Developer 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. Click on Edit->Options on the menu Change the query terminator to "GO" Check "Terminator must be on a new line" Uncheck "Terminator string is case sensitive"   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 lastest 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 lastest 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. Synametrics Technologies have licensed these drivers from DataDirect Technologies and you can use these drivers only if you are using the Developer 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