RazorSQL Support Answers

Common Support Questions

. MySQL: MySQL connection only works from local machine
1. MySQL: MySQL keeps dropping the connection.
2. MySQL: Error when using the DELIMITER statement in MySQL
3. MySQL: cannot convert value '0000-00-00 00:00:00' from Column x to TimeStamp where x is the column number of the DATETIME column
4. MySQL: How to connect to MySQL versions older than 4.x, such as MySQL 3
5. MySQL, PostgreSQL, SQL Server: Unable to make a connection to hosting provider database (MySQL, PostgreSQL, MS SQL Server)
6. PostgreSQL: PostgreSQL connection only works from local machine
7. PostgreSQL: How to connect to PostgreSQL over SSL using self signed certificate.
8. DB2: How to avoid table and row locking in DB2.
9. DB2: Information for connecting to DB2 on the mainframe (z/OS).
10. Oracle: When attempting to connect to Oracle, the following error is thrown:
Class oracle/sql/converter/CharacterConverters violates loader constraints.
11.SQL Server: How to call a SQL Server DTS package.
12. SQL Server: The following error is received when connecting to SQL Server from a Mac:
Single-Sign-On is only supported on Windows. Please specify a user name.
13. SQL Server: SQL Server login failure state codes
14. SQL Server / Sybase / SQL Anywhere: QUOTED_IDENTIFIER settings
15. SQL Server: Unable to connect to SQL Server Express
16. SQL Server / Sybase: How to view multiple results sets for a query such as sp_help.
17. DBASE: Information on connecting to DBASE
18. Netezza: Does RazorSQL support Netezza
19. Pervasive: Does RazorSQL support Pervasive
20. RazorSQL: Are administrator privileges required for installing RazorSQL on Windows machines?
21. RazorSQL: How to increase maximum memory settings for RazorSQL.
22. RazorSQL: How to save a query using RazorSQL

 

MySQL: MySQL connection only works from local machine.

If the MySQL connection is only working locally, make sure the user has been granted privileges to be able to connection remotely to MySQL. The following is an example SQL statement to grant the user remote access: GRANT ALL PRIVILEGES ON [database_name].* to '[user]'@'[hostname]' identified by '[password]'

MySQL: MySQL keeps dropping the connection.

If adjusting the Keep Alive settings via the Edit->Preferences menu does not solve the problem, MySQL does provide several JDBC properties to allow the user to set the connection timeout, socket timeout, and auto reconnect properties of the MySQL JDBC driver. Listed below is some information about how to connect to MySQL with these properties set. 1. Click the "Add Connection Profile" button or select Action - Add Connection Profile in RazorSQL. 2. Select MySQL as the database type and JDBC as the connection type. 3. Enter the following information: Profile Name: <the name you want the connection described as> Driver Location: <browse to the location of the MySQL JDBC driver> Driver Class: com.mysql.jdbc.Driver (assumes the use of the MySQL Connector/J driver) Login: <your login id> Password: <your password> JDBC URL: jdbc:mysql://<host>:<port>/<database_name>? connectTimeout=0&socketTimeout=0&autoReconnect=true autoReconnect should only be set to true if using the other connection properties does not solve the problem. Replace <host> with the ip address or name of the server. Replace <port> with the port. The default is 3306. Replace <database_name> with the name of the database if applicable Here is a link with some information about the properties in the JDBC URL: http://dev.mysql.com/doc/refman/5.0/ en/connector-j-reference-configuration-properties.html

MySQL: Error when using the DELIMITER statement in MySQL.

The DELIMITER statement is not part of the MySQL language. It is a command supported by certain MySQL tools.

MySQL: cannot convert value '0000-00-00 00:00:00' from Column x to TimeStamp where x is the column number of the DATETIME column.

The MySQL Connector/J JDBC driver changed the way it handles DATETIME values in version 3.1. DATETIME values of all zeroes are considered illegal in versions 3.1 and above of MySQL Connector/J. Thus, an exception is thrown when these values are retrieved from a table. Listed at the bottom of this message is an excerpt from the MySQL Connector/J manual that talks about this. There is a way to override this behavior using Connector/J version 3.1.7 or higher. The override can be done by appending a property and value onto the JDBC URL. In the connection profiles tab in RazorSQL, click "Edit" next to the JDBC URL and append the following to the end of the JDBC URL: ?noDatetimeStringSync=true This will allow the all zeroes DATETIME values to be retrieved. The following text is taken from the following link: http://dev.mysql.com/doc/refman/5.0/en/connector-j-installing-upgrading.html Datetimes with all-zero components (0000-00-00 ...) These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet. Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the zeroDateTimeBehavior configuration property. The allowable values are: exception (the default), which throws an SQLException with an SQLState of S1009. convertToNull, which returns NULL instead of the date. round, which rounds the date to the nearest closest value which is 0001-01-01. Starting with Connector/J 3.1.7, ResultSet.getString() can be decoupled from this behavior via noDatetimeStringSync=true (the default value is false) so that you can get retrieve the unaltered all-zero value as a String. It should be noted that this also precludes using any time zone conversions, therefore the driver will not allow you to enable noDatetimeStringSync and useTimezone at the same time

How to connect to MySQL versions older than 4.x, such as MySQL 3

The lastest MySQL Connector/J drivers do not work with MySQL versions prior to version 4. For connecting to MySQL version 3 or earlier, an older version of the MySQL Connector/J driver is needed, for example, MySQL Connector/J 3.1. This driver can be found at the MySQL download site, located here: http://dev.mysql.com/downloads/connector/j/3.1.html The driver class name is also different when connecting to MySQL 3 or earlier. The class name should be the following: org.gjt.mm.mysql.Driver

Unable to make a connection to hosting provider database (MySQL, PostgreSQL, MS SQL Server).

Many hosting providers provider users access to databases such as MySQL, PostgreSQL, or Microsoft SQL Server, but do not allow remote connections to those databases. They force users to use web-based tools to connect to the databases. Also, in the case of SQL Server, some ISPs block the common port for SQL Server, port 1433, because of a virus that used port 1433 in an exploit. In these cases, connecting to the databases using traditional means is not possible. RazorSQL provides PHP bridges for MySQL, PostgreSQL, and SQL Server, and an ASP bridge for SQL Server. So, if your web host provides you with either a PHP or ASP web server, you can then use the bridges to connect to the database. See the help documentation for more information on the PHP and ASP bridges.

PostgreSQL connection only works from local machine.

If the PostgreSQL connection is only working locally, make sure the PostgreSQL instance is configured to allow remote connections. This involves modifying the pg_hba.conf file by adding information about the remote ip addresses / hosts that you want to allow to connect remotely. Also make sure that TCP/IP networking is enabled for your PostgreSQL instance by modifying the postgresql.conf file.

PostgreSQL: How to connect to PostgreSQL over SSL using a self signed certficate.

In the connection wizard, when configuring the PostgreSQL database, select the "Configure" button in the Connection Properties field. This will launch a new window. For the ssl value, select "true". For the sslfactory value, select org.postgresql.ssl.NonValidatingFactory.

How to avoid table and row locking in DB2.

To limit or avoid row locking in DB2, do the following: 1. Set the auto-commit option to Yes on the connection profiles window when connecting to the database. 2. Set the Transaction Isolation level to the lowest level, Read Uncommitted, in the Transaction Isolation dropdown on the connection profiles window when connecting to the database. 3. If table or row locking still occurs, there is a preference entitled "Close Resultsets on Execute" in the Edit - Preferences menu. Check the box next to this preference, and adjust the fetch size upwards to make sure enough rows are returned for each query for your needs.

DB2: Information for connecting to DB2 on the mainframe (z/OS).

RazorSQL ships with the IBM DB2 UDB JDBC Universal driver. This driver does support access to DB2 running on z/OS. However, to connect to DB2 z/OS, a special license file is needed. The license file is contained in the DB2 Connect software installation. Here is the name of the license file: db2jcc_license_cisuz.jar The information below outlines how to get connected to DB2 UDB for z/OS version 8 using the Universal Driver. Once the license file is obtained, to connect to DB2 on z/OS, you can go to the Connections -> Add Connection Profile, select DB2 as the database type, JDBC as the connection type, and enter the paths to the db2jcc_license_cisuz.jar and the db2jcc.jar file that ships with RazorSQL in the driver location field. Those two paths should be separated by a semi-colon. For a JDBC type 4 connection, the JDBC URL format would be the following: jdbc:db2://<server hostname or IP address>:<DB2 UDB Instance port number>/ For example, jdbc:db2//127.0.0.1:50000/SAMPLE For a JDBC type 2 connection, (you would actually be connecting via DB2 Connect, so DB2 Connect needs to be installed and configured on your machine), the JDBC URL format would be the following: jdbc:db2:<dbname> For example, jdbc:db2:SAMPLE The driver class for all of the above examples is the following: com.ibm.db2.jcc.DB2Driver For more information on connecting to DB2 version 8 via JDBC, please see the following: http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0401chong/

When attempting to connect to Oracle, the following error is thrown: Class oracle/sql/converter/CharacterConverters violates loader constraints.

The database being connected to may require charset support not provided in the base Oracle JDBC drivers. RazorSQL versions 3.6 and later ship with additional charset support drivers for Oracle. Earlier versions of RazorSQL do not ship with these drivers, and as such, they need to be downloaded from Oracle's site. Additional charset support is available in the orai18n.jar file for the Oracle 10g JDBC drivers (Previously charset support was available in nlscharset12.jar, but the orai18n.jar file should be used with 10g drivers). RazorSQL versions 3.5 and earlier ship with the base 10g driver, ojdbc14.jar, but do not ship with the jar files for additional charset support. As a side note, the Oracle 10g drivers are compatible with Oracle 9i. The orai18n.jar file can be downloaded from the oracle site. Here are some links: http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html Once on this page, click the "Oracle Database 10g Release 2 . . . drivers" link. After accepting the license agreement, you will be brought to a page with a list of drivers for download. Download the orai18n.jar file. NOTE: You will need to sign in to Oracle to download the file. Registration is free if you do not already have an id. At this point, you can set up a JDBC connection to Oracle via RazorSQL that references both the ojdbc14.jar file and the orai18n.jar file. This can be done by going to the Connections - Add Connection Profile, selecting "Oracle" as the database type, selecting "JDBC" as the connection type, entering the profile name, the location of the jar files separated by semi-colons, for example: /Users/user/ojdbc14.jar;/Users/user/orai18n.jar the driver class: oracle.jdbc.driver.OracleDriver your user name and password and your JDBC URL

How to call a SQL Server DTS package.

RazorSQL cannot execute DTS packages directly. However, RazorSQL can execute stored procedures. If a stored procedure is created to call the DTS package, then RazorSQL can call the stored procedure, which will then execute the DTS package. For example, SQL Server has an available stored procedure called sp_start_job. If the DTS package is setup as a job, then RazorSQL can be used to execute the DTS package by calling the sp_start_job procedure.

SQL Server: The following error is received when connecting to SQL Server from a Mac: Single-Sign-On is only supported on Windows. Please specify a user name.

It is not possible to connect to SQL Server using Windows Single Sign-on from a Mac. Connections must be made using either SQL Server Authentication or Windows Authentication. Make sure to enter a password if connecting via either of these methods. Not entering a password will cause the driver to try to connect using Windows Single Sign-on.

SQL Server login failure state codes.

2 and 5 Invalid userid 6 Attempt to use a Windows login name with SQL Authentication 7 Login disabled and password mismatch 8 Password mismatch 9 Invalid password 11 and 12 Valid login but server access failure (valid login, but login has no access to the server) 13 SQL Server service paused 18 Change password required

SQL Server / Sybase / SQL Anywhere QUOTED_IDENTIFIER settings.

By default, the drivers RazorSQL uses to connect to SQL Server, Sybase and SQL Anywhere databases set the QUOTED_IDENTIFIER setting to ON. To change the status of the QUOTED_IDENTIFIER, the following statement would need to be executed: SET QUOTED_IDENTIFIER OFF The RazorSQL tools that generate SQL use the select @@OPTIONS query to determine the status of the QUOTED_IDENTIFIER before generating SQL. If QUOTED_IDENTIFIER is set to ON, RazorSQL wraps objects with spaces or special characters in double-quotes. If QUOTED_IDENTIFIER is OFF, RazorSQL uses braces to wrap objects. If care is taken to make sure that any names used are not the names of existing database objects or contain spaces or special characters, there is little potential for conflicts.

Unable to connect to SQL Server Express.

The default SQL Server express installation does not enable TCP network protocols, so here are some steps to take to be able to communicate with SQL Server Express. 1. Launch the SQL Server Configuration Manager. It should be in the Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools start menu option. 2. Select the SQL Server 2005 Network Configuration tab. There should be a Protocols for SQLExpress option, and one of the protocols should be TCP IP. 3. Enable the TCP IP protocol if it is not enabled. 4. The default port for SQL Express may not be 1433. To find the port it is listening on, right-click on the TCP IP protocol and scroll all the way down to the IP All heading. There should be a section called TCP Dynamic Ports. This should list the port SQL Express is listening on. You can then put this value into the port field when adding the connection profile via RazorSQL. Make sure to restart SQL Express before trying to connect.

SQL Server / Sybase: How to view multiple result sets for a query such as sp_help.

To view all result sets that a query returns, use the "Execute SQL Fetch All Rows" icon or menu option.

Information on connecting to DBASE.

To connect to DBASE, you would need to setup an ODBC data source on your windows machine. This can be done by going to the Windows control panel, and selecting Administrative Tools and then Datasources ODBC. You can then create a new datasource by clicking the add button and selecting the Microsoft dBase driver. Make sure to select the directory where your dbase file is located when setting up the datasource. To connect with RazorSQL, you would need to do an Connections -> Add Connection Profile, and then select OTHER for the database type and ODBC for the connection type. Select the ODBC datasource you just created for the datasource.

Does RazorSQL support Netezza.

RazorSQL is not tested with Netezza, but should support connections to Netezza. You would need to use the netezza jdbc driver, which may only be available by visiting Netezza's support site with a valid Netezza support id. The driver should be in a jar file called nzjdbc.jar. You can set up a connection by going to the Add Connection Profile screen and selecting "Other" as the database type and "JDBC" as the driver type. The JDBC URL should be the following: jdbc:netezza://host:port/database The driver class is org.netezza.Driver

Does RazorSQL support Pervasive.

RazorSQL does support Pervasive, but does not ship with the Pervasive JDBC drivers. The Pervasive jdbc drivers are included with the Pervasive installation. On Windows machines, the Pervasive ODBC driver can also be used.

Are administrator privileges required for installing RazorSQL on Windows machines?

Administrator privileges are not required to install RazorSQL. However, the default setting for the Windows installer is to install RazorSQL in the Program Files directory, which is probably not writeable for your logon id if you do not have administrator privileges. There are two ways to get around this: 1. The easiest is to download the latest razorsql<version>_windows.zip file from http://www.razorsql.com/download.html to your desktop. Then extract the zip file and navigate to the razorsql<version> directory and launch razorsql.exe or razorsql.bat. 2. The second way is to launch the razorsql<version>_setup.exe Windows installer and when given the choice as to where to install RazorSQL, be sure to change the directory from the Program Files directory to a directory you have write access to such as your desktop.

How to increase maximum memory settings for RazorSQL.

For Windows machines, the max memory setting is contained in a file called razorsql.bat located in the RazorSQL installation directory. In this file, there is a line containing the text -Xmx384M or -Xmx256M depending on the version of RazorSQL you are using. Increase the number before the M, which stands for MegaBytes, to increase the memory. To take advantage of the change, RazorSQL must be launched by double-clicking razorsql.bat. For Mac OS X machines, right-click on the RazorSQL application file and select "View Package Contents". Edit the Info.plist file. In this file, there is a line that contains either -Xms16m -Xmx256m or -Xms16m -Xmx384m depending on the version of RazorSQL you are using. Increase the 256 or 384 number to increase the memory. For Linux / Unix machines, edit the razorsql.sh shell script. Increase the number after the -Xmx to increase the available memory for RazorSQL.

How to save a query using RazorSQL.

There are a couple of ways to save the queries depending on how they were executed. For individual queries, the easiest way to save the query is to press the "Save Query" button located on the query results toolbar or the "Save Query" option located in the right-click menu of the query results tab. You will then be prompted for where to save the query to on your file system. If you typed the query into the sql editor, you can do a File -> Save or File -> Save As to save the editor contents to a file on your computer. If you did not execute the query from the editor, but used the navigator or query builder to execute the query, you can click on the "view query" icon in the query results toolbar or right click on "Query 1" and select "View Query" to see the query that was executed. You can then do a control-A / command-A and control-C / command-C to copy the query to your clipboard. Also, all queries you execute are stored and can be viewed by selecting Connections -> View SQL History or Actions -> View Query Log. The SQL History by default stores 20,000 lines of history. The query log stores the history for the current connection.