RazorSQL Support Answers
Common Support Questions
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.
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:
https://www.ibm.com/developerworks/data/library/techarticle/dm-0401chong/index.html
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.
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.
