Table of Contents

Frequently Asked Questions about i-net JDBC drivers for MS SQL Server

For General JDBC FAQ see the Sun Site.

General FAQ for all i-net JDBC drivers for MS SQL Server

FAQ for the JDBC 2.0 drivers i-net SPRINTA, i-net OPTA and for the JDBC 3.0 driver i-net MERLIA only

FAQ for the JDBC 3.0 driver i-net MERLIA only

Which JDBC subprotocol should I use (inetdae6, inetdae7 or inetdea7a) ?

inetdae6

This subprotocol implements the communication protocol for the SQL Server 6.5. If you want to be compatible with SQL Server 6.5 then you need to use this protocol. SQL Server 2000 is very slow with this protocol (approx 10-100 times slower than inetdae7). This is an ASCII protocol (1 byte per character) and it supports only features of SQL Server 6.5 like

  • column and table names with only 30 characters
  • no empty space strings

inetdae7

This is a unicode communication protocol. Every character has 2 bytes. It is not compatible with SQL Server 6.5. You need SQL Server 7.0 or higher. This subprotocol has a very good support for the nXXX data types. Because JDBC API does not make a difference between ASCII and UNICODE data types and the SQL Server cannot convert NTEXT to TEXT you cannot use the TEXT data type with this subprotocol.

If you have an ASCII column (varchar or char) then some SQL Server versions (without service pack) will have performance problems because they cannot use indexes.

If you save data to an ASCII column then SQL Server will convert the unicode from the driver with the codepage of the SQL Server to ASCII.

If you read ASCII data then the driver convert the ASCII data to UNICODE. The driver uses the codepage that you set with the option charset for converting. If you do not set the charset then the driver will detect the codepage from the SQL Server.

If you want to save include values as UNICODE then the include values will need to be marked with the N prefix.

inetdae7a

The “a” stand for ASCII. This protocol equals inetdae7 except string parameter of PreparedStatements. These parameters are sent as ASCII. But all SQL expressions are sent as UNICODE. This means if you have parameters included in your SQL expression then these will also be sent as UNICODE. If you want to save it as UNICODE then the include values will need to be marked with the N prefix.

If you use ASCII data types this is the recommended subprotocol. This protocol might be a little slower than inetdea7 if the SQL Server has no index problems. This is the case because the character converting of Java is slower than the converting of the SQL Server (Java byte code versus native code).

See also:

How to use international language characters?

  • If you use the SQL Server 6.5 compatible mode (subprotocol inetdae without option sql7 or the subprotocol inetdae6).

    You need to use the option charset in the JDBC URL. The option is case sensitive. It needs to be an existing character converter of your JVM. You can test your JVM with: “TestString”.getBytes( “MS932” ); for example “jdbc:inetdae6:LocalHost?charset=KSC5601” If you do not set a charset then only the low part of the characters is save.
  • If you use the SQL Server 7 or higher mode (option sql7=true or subprotcol inetdae7).

    If you use the unicode data types nVarchar, nChar or nText you do not need to do anything. If you use the single byte data types varchar, char or text there are problems. The driver will send all data as unicode to the SQL Server and the SQL Server converts them with its codepage. The data type text is not supported in this mode because the SQL Server can't convert ntext to text. For the data type varchar and char you need to use the option charset. This charset option needs to be compatible with the codepage of the SQL Server.
  • You have saved the data with ODBC or another tool and in your ODBC driver or your in the SQL Server Client configuration you have to enable ANSI to OEM conversion (all SQL Server and all modes with ASCII data types)

    You need to use the option charset=Cp437 or charset=Cp850.
  • If you do not use a PreparedStatement with parameters then you need to mark your text as unicode with the N prefix. This should look like … N'YourUnicode' …

    Attention: A list of existing character converters can you find in the jar files i18.jar and rt.jar (Sun JVM). The class names of the converters are like ByteToCharXXXX.class and CharToByteXXXX.class. You can test your JVM with: “TestString”.getBytes( “MS936” );

How to create international language error messages?

You need to use the option language in JDBC URL. The available languages can be requested with:

SELECT * FROM syslanguages;

Can i get a java.sql.Timestamp from a timestamp column?

No, you cannot get a java timestamp from a timestamp column of the SQL server. The timestamp column in the SQL server is not a column that contains a time or date. It contains a binary number (8 byte) that is unique in the database. You can get bytes from a timestamp field that can be read with the methods getBytes, getString or getBinaryStream.

To get a java timestamp from a SQL Server column you need to use a date/time column in the SQL Server database.

If you call getObject on a column that is a timestamp column in the SQL Server then you get a binary object. If you call toString on that binary object then you get something like “[B”.

Empty Strings "" return as a single space " "

The SQL Server versions 6.5 and 7.0 do not behave equivalently with spaces and null values in strings.

You can make the following test:

UPDATE TABLE SET textfield= ? 
SELECT textfield FROM TABLE

and here is the result of this test:

Update Value Return Value from
SQL Server 6.5
Return Value from
SQL Server 7.0
Return Value from
SQL Server 7.0** with sql7=true
null null null null
0 space 1 space 1 space 0 space
1 space 1 space 1 space 1 space
2 space 1 space 2 space 2 space
3 space 1 space 3 space 3 space

This conversion comes from the SQL Server and not from the driver.

** The database needs to employ a CompatibilityLevel of 70 or higher. You can set and request the CompatibilityLevel with the stored procedure sp_dbcmptlevel.

  • The SQL Server 2000 is compatible to the SQL Server 7.0 in this case.
  • sql7=true is equal to the JDBC subprotocol inetdae7.

Support of Firewall and Proxy

i-net software's MS SQL JDBC drivers can be used through a firewall. The firewall must let pass the TCP/IP port of the SQL Server. The default TCP/IP port is 1433. Proxies are not supported.

Connection con = null;
try{
con = DriverManager.getConnection("jdbc:inetdae:webserver:1433", user, password );
}catch(Throwable e){e.printStackTrace();}
 
if (con == null)
try{
con = DriverManager.getConnection("jdbc:inetdae:webserver:119", user, password );
}catch(Throwable e){e.printStackTrace();}
if (con == null)
try{
con = DriverManager.getConnection("jdbc:inetdae:webserver:443", user, password );
}catch(Throwable e){e.printStackTrace();}
if (con ==null)
// message to the user

Support of Unicode and character conversion

The driver supports Unicode in sql7 mode.

To save 2 bytes per character (Unicode) you need to set the option “sql7=true” in the JDBC url and you need to use nXXX columns (e.g. nvarchar, nchar ).

If you use the SQL Server 6.5 compatible mode then you can use the option charset (e.g. charset=Cp1250) in the JDBC url to convert the characters. In this mode the SQL Server saves only 1 byte per character.

java.sql.SQLException: [TDS Driver]This is an unlicensed test version of the 'i-net xxxx' and has expired.

I bought the i-net driver - do I employ the correct version?

Please empty the cache of your application to make sure that there is no limited trial-version sitting somewhere anymore. Check the class path for old versions.

If you use the command DriverManager.setLogStream(System.out) you will see the license text of the employed driver for setting up the connection. You can find the same text in the file TdsConnection.class (Or if not available in the largest class file). You can check the texts and make sure you employ the correct driver version.

In what operating system environments does your JDBC driver work?

i-net UNA (a JDBC 1.22 driver) and i-net SPRINTA (a JDBC 2.0 driver) are type 4 drivers. A JDBC type 4 driver is a native-protocol fully Java technology-enable driver that converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Type 4 drivers work on all operating systems with a JVM. The JDBC 2.0 driver i-net SPRINTA requires a JVM 1.2 or higher.

The JDBC 1.22 driver i-net UNA requires a JVM 1.1 or higher.

Do the driver support the data types NVARCHAR, NCHAR, and NTEXT?

Yes, both drivers, i-net UNA and i-net SPRINTA, do support these new datatypes. You need to use the flag sql7=true in the JDBC URL.

Strings larger than 255 characters are truncated.

You use the SQL Server 6.5 compatible mode. The SQL Server 6.5 supports only strings with a size up to 255 characters. You use this mode because you use the JDBC subprotocol inetdae or inetdea6. You need to use the JDBC subprotocol inetdae7 or inetdae7a. For older versions you will have to set the JDBC URL option sql7=true. More information can be found in the file Manual.

Samples:

  • jdbc:inetdae7:localhost
  • jdbc:inetdae:localhost?sql7=true

How to create a read lock on a record for update

There are no absolute read locks for the SQL Server. The reading connection needs to support the read lock (transaction isolation level).

1. Case

The writing connection started a transaction with:

setAutoCommit ( false );

After the first update of data there is a read lock if the reading connection has an isolation level of TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ or TRANSACTION_SERIALIZABLE. The read lock ends with the end of the transaction ( setAutoCommit (true), commit(), rollback() ).

2. Case

The writing connection has not started a transaction ( setAutoCommit(true) ) and create a JDBC 2.0 statement with:

createStatement(ResultSet.TYPE_SCROLL_SENSITIVE ,ResultSet.CONCUR_UPDATABLE+1);

After the first update of data there is a read lock if the reading connection has an isolation level of TRANSACTION_SERIALIZABLE. The read lock ends if the ResultSet scrolls to the next fetch block ( setFetchSize() ).

How do I retrieve the value from the identitiy column of a newly inserted record

The best solution for the JDBC 1.22 driver i-net UNA is to call two statements in one. For example:

st.execute( "INSERT INTO YourTable(..) Values(..) SELECT @@IDENTITY" );
if (st.getUpdateCount() == 1) {
	st.getMoreResults();
	rs = st.getResultSet();
	rs.next();
	Object identity = rs.getObject();
}

With the JDBC 2.0 drivers i-net SPRINTA and i-net OPTA you can use an updateable ResultSet.

After calling the insertRow method you can do a moveLast(xxx) and then getObject(xxx) to get the values of the inserted row if you use a different cursor type than the Scrolling Cursor Type: TYPE_SCROLL_SENSITIVE+1 (Dynamic cursor).

If you use the Scrolling Cursor Type: TYPE_SCROLL_SENSITIVE+1 (Dynamic cursor) then the inserted row does not need to be at the end of the ResultSet.

How can i enable the logging of the driver for debugging?

If you have problems with the driver then you can enable the logging of the driver with:

DriverManager.setLogStream( System.out );
 
// or
 
DriverManager.setLogStream( System.err );

The driver prints the messages in the default print streams (e.g. java console of the JDK or Browser). You can also use any other PrintStream (e.g. an log file) to make this output. This can be used if you can not see the default PrintStreams. If you use the driver in an servlet, .jsp script or in an Application Server then you need to set an PrintStream on an log file:

PrintStream ps = new PrintStream( new FileOutputStream( "c:\\driver.log" ) );
DriverManager.setLogStream( ps );

Now the driver prints the messages in the file that you have specified.

Application Servers

In some Application Server you can enable the JDBC logging with the visual managment console or enviroment.

  • JBoss: Set the DataSource Attribute “LoggingEnabled” to “true”.
  • WebLogic: In the Dialog “Servers ⇒ ServerName ⇒ Logging ⇒ JDBC” enable the logging and enter a logging filename.

Please note: Please enable the logging only when you need it to find a problem because the driver have a better performance without the logging.

How to add the driver to the classpath?

If you use the driver in an java application then add the file “DriverName.jar” to your classpath or extract the jar file in the directory of your application.

If you use the driver in an applet then add the file “DriverName.jar” to the Archive attribute of the applet tag, e.g.:

<APPLET CODE="..." Codebase="..." ARCHIVE="DriverName.jar" WIDTH="100%" HEIGHT="100%">
...
</APPLET>

or extract the jar file in the directory of the applet.

For more informations see the readMe.txt of the driver and the JavaSoft documentation at: http://java.sun.com/docs/books/tutorial/java/package/managingfiles.html.

How can I get XML data from the SQL Server

The SQL Server sends the XML data in one or more rows in the first column of the ResultSet. The size of each row is variant. With the following loop you can concatenate the pieces of the XML data:

...
StringBuffer sb = new StringBuffer();
while (rs.next()) {
	sb.append(rs.getString(1));
}
System.out.println("XML data: " + sb.toString());
...

Performance improvement with i-net SQL Server driver

Why is it that the i-net SQL Server driver seems to be slower than other drivers in my tests?

  • You need to test the performance on the console. Our driver is completely written in the Java language so that a debugger can slow it down much more than a native driver like the JDBC-ODBC -Bridge or an type 2 driver.
  • You have tested it with a profiler. A profiler does not calculate the network waiting times correctly. This is the major running time. And profilers do slow down Java components much more than native components. You should montior the running time with System.currentTimeMillis().
  • You disabled the JIT (Just In Time Compiler). Java components slow down much more as native components with a disable JIT.
  • You use the JDBC subprotocol inetdae or inetdae6 with the SQL Server 7 or higher. These are the subprotocols for SQL Server 6.5 compatibility. With the SQL Server 7.0 it can be 2 times slower and with the SQL Server 2000 it can be up to a 100 times slower in some resons.
  • You selected a large count of data (row count or field size) and you use a forward only, read only ResultSet without a cursor. In this case the driver reads all data into memory. You should test without scrolling through the ResultSet. The scrolling is very fast in this case.
  • You use the JDBC subprotocol inetdae7 with PreparedStatements or CallableStatements with String parameters. Your tables include the ASCII data types char, varchar or text, only. The driver sends all data as unicode and the server needs to convert it for every row. You should use the JDBC subprotocol inetdae7a or the mode MODE_SQLSERVER_70_ASCII.
  • You use the fixed length data types CHAR or NCHAR in the WHERE clause of a PreparedStatement. The driver uses VARCHAR or NVARCHAR (depends the mode) for parameters. The SQL Server can't use an index because it converts the values of the table column to VARCHAR or NVARCHAR. Solutions:
    1. do not use the fixed length data types rather use the variable length data types
    2. use a cast like “… cast(? as char(xx)) …”
    3. use a simple Statement with inlined parameters instead of PreparedStatements
  • SQL Server 2000 SP4:BigDecimal parameter send by driver will processed up to 20% slower. This happens if parameter definition send by Driver don't exactly match the column type. For example Driver sends parameter as “decimal(38,36)”, the column type is “numeric (7,0)”. It seems that the index for the numeric column won't used with SP4. With SQL Server 2005 or earlier SQL Server version such a behavior was not seen. A workaround for that problem is to cast to column type.
    E.g.
    SELECT * FROM testnumeric WHERE a = CAST(? AS NUMERIC(7,0));

Support for SSL

The driver OPTA and MERLIA support SSL (protected by U.S. Patent) since version 6.00. View the Manual and the current Release Changes. for the SSL system requirements.

Another solution for encryption between client and server is to use the driver in conjunction with i-net KONNEKTER, a Java middleware on the Server.

The encryption of i-net KONNEKTER is a dynamic encryption (not SSL). Using i-net KONNEKTER has security advantages over SSL. For example you do not need to code the real SQL Expression in your client Java classes.

Why does ResultSetMetaData.getTableName() return null ?

The method ResultSetMetaData.getTableName() requires a server cursor or the “for browse” statement. This is a limitation of the MS SQL Server.

Prepared statements and temporary tables

It is a limitation or a feature of the MS SQL Server that temporary tables that are created in a stored procedure (prepared statement) are only valid in this stored procedure (prepared statement). That means that a temporary table that was created with a PreparedStatement is only valid within the PreparedStatement with that it was created.

The possible solution are:

  1. use prepare=false in the JDBC url. This is slower because the driver does not prepare the statement on the SQL Server.
  2. use a simple Statement for the table creation like: con.createStatement().execute(“CREATE TABLE #temp”);

Data type values for NVARCHAR, NCHAR, NTEXT, UNIQUEIDENTIFIER and SQL_VARIANT

The ODBC-JDBC-Bridge returns the type value Types.OTHER for this data types. This is not helpful. That's why our driver returns the original SQL99 that the SQL Server sends.

  1. 8 - NCHAR
  2. 9 - NVARCHAR
  3. 10 - NTEXT
  4. 11 - UNIQUEIDENTIFIER
  5. 150 - SQL_VARIANT

Why I have deadlocks with the inet driver and not with other drivers?

In general the risk of a deadlock increases with the performance of the driver. Since our drivers are known to be among the very fastest they might be more vulnerable to the deadlock problem.

You can find many general guidelines in the SQL Server online manual to solve deadlocks. If you use a query timeout then the driver will not hang rather it will throw a timeout exception in such cases; with a rollback you can solve the deadlock.

To solve a deadlock you need to verify which SQL expression produces the deadlock in the first place. You can find out about this:

  • If you set a query timeout. Then a SQLException will be thrown and with the stacktrace you have a line number of your Java code.
  • You can take a look in the Enterprise Manager for the last TSQL command.
  • You can look which lock was created.

When you have found the critical transaction you need to verify the order of your SQL expression whether it can produce a deadlock. Please note that a row lock is like a table lock if the SQL Server can't use an index. If the SQL Server can't use an index then it needs to read every row to verify the WHERE clause. If a row is locked then the SQL expression will wait on this row.

Deadlocks can occur if the SQL Server can't use indexes. The SQL Server can only use an index if the parameter data type and the index data type are compatible. If the data types are different then the data type precedence is used.

With PreparedStatements the driver uses NVARCHAR for string parameters in Unicode mode and VARCHAR in ASCII mode.

If the index data type is lower then the SQL Server will convert the data from the table and not the parameter data. For example, if you use the CHAR data type then the SQL Server can't use the index or if you use the VARCHAR data type and you work in unicode mode.

Solutions:

  • Change the data type of the table and the mode that the table values have a equals or higher precedence. For example VARCHAR and ASCII mode (inetdae7a; mode=71).
  • Cast the parameter like “… cast( ? as CHAR(xx) ) …

Deadlocks can occur if you do not commit a transaction. This can happen when a transaction has been started without your knowledge. This may occur when you (or your environment) change the isolation level. You can disable this JDBC ODBC default behavior with impltran=false in the JDBC URL.

java.sql.SQLException: No suitable driver

This error message means that for the requested url no driver was registered. There are 3 causes:

  1. A bug in some JDK 1.1.x JVM. Please replace. Class.forName(“com.inet.tds.TdsDriver”); with Class.forName(“com.inet.tds.TdsDriver”).newInstance();
  2. The JDBC url is wrong. The url has to start with “jdbc:inetdae”. This string is case sensitive.
  3. The driver is not in the classpath. Please see “How to add the driver to the classpath?”.

java.sql.SQLException: Connection refused

If you receive this SQLException on the call of DriverManager.getConnection(..) then your SQL Server does not listen on the selected TCP/IP port. Please read the chapter “Check Host Name and Port Number of Your Server” in the readme.txt.

If there are already connections established to the SQL Server then the problem could occur, that too many threads request a connection at the same time; the socket connection request queue of the SQL Server is full. A solution to this problem is the use of the connection pool with an initial size.

java.sql.SQLException:[TDS Driver]Too many connections :3

You downloaded a test-version from our site. The test-version is limited to two connections. Currently you try to get a third connection. Causes:

  • If you close a connection (“killing an application”) without closing all result sets with “close()” the connection might still be active for another 15 minutes or so.
  • You use the driver also from another client/application. The driver is limited to 2 connections for all clients. You can look in the “current activity” of the SQL Server.
  • If you have purchased the driver then you have not replace the driver correctly. If you enable the logging with: DriverManager.setLogStream( System.out ); the driver print the license text of the running instance. If you look in the file TdsConnection.class (or the largest class file) you can see the license text of the purchased driver. Please remove all old drivers from your classpath until you receive a ClassNotFoundException. After this exception add the new driver.

java.sql.SQLException: [TDS Driver]Read timed out

Generally, this error message means that the driver is waiting for an answer from the SQL Server of its own request. If you like to execute a command (SELECT, UPDATE, INSERT…) that need more time to finish then you can increment the query timeout of your statement to resolve the problem.

The default query timeout is that value of the driver login timeout that was valid at this time at the connection was created. You can read or change the login timeout of the Driver Manager with set/getLoginTimeout.

java.sql.SQLException: [TDS Driver]ResultSet is READ ONLY

  • This exception occurs if you use a forward only or read only cursor.
    Please refer to the sections “8. Scrolling Cursor Types” and “9. Locking and concurrency control” in the readme.txt of the driver for more information.
  • You have use the methods execute() and getResultSet(). An updateable ResultSet was only create with executeQuery().
  • This error message can also occur if the ResultSet was closed.

See also:

java.sql.SQLException: [TDS Driver]ResultSet is closed.

In contrast to older versions of the driver the version 4.xx is checking the status of the ResultSet before using it. The JDBC 1.22 and JDBC 2.0 standard allows only one open ResultSet per statement.

A Resultset can be closed with the following methods:

  • ResultSet.close()
  • Statement.execute()
  • Statement.executeUpdate()
  • Statement.executeQuery()
  • Statement.getMoreResults()
  • Statement.close()

SQLException "[Host]Invalid column name 'xyz'."

You tried to execute one of the following SQL statements:

SELECT * FROM table1 WHERE textColumn = "xyz";
UPDATE table1 SET textColumn="xyz";

In standard configuration (ANSI) strings in double quotes are interpreted as identifiers. Use the following SQL statements instead with single quotation:

SELECT * FROM table1 WHERE textColumn = 'xyz';
UPDATE table1 SET textColumn='xyz';

or execute the SQL statement “set quoted_identifier off” once per connection.

Exception: java.lang.ClassNotFoundException

The classes are not in your classpath. See the question "How to add the driver to the classpath?".

java.lang.ClassCastException: java/lang/Object after getTimestamp() or getDate() on a timestamp column

java.lang.OutOfMemoryError / Server cursor versus Client cursor

By default our MS SQL Server JDBC drivers uses a Forward Only ResultSet (client cursor) and reads all rows into memory. This is very fast for small ResultSets. This can produce an OutOfMemoryError, though.

You can use the following methods to use a server cursor with a fetch size of 128 for executeQuery(). This doesn't work for execute() or executeUpdate().

All drivers:

  • setCursorName()
  • set the JDBC URL property useCursorsAlways=true
  • set the JDBC URL parameter mars=true. This work only with SQL Server 2005 since version 6.8.

i-net UNA:

  • set the value of the fetch size in the JDBC URL property fetchsize. The method executeQuery always uses cursors with this fetch size.

i-net SPRINTA, i-net OPTA and i-net MERLIA only:

  • use another scrolling or concurrency type like read only, forward only
  • or use setFetchSize (i-net SPRINTA version 3.03 or higher and i-net OPTA version 2.02 or higher).

How large is the fetch size and the default fetch size?

The default fetch size is 0. This means a unlimited fetch size. This value will change when:

All drivers:

  • The method setCursorName() ⇒ fetch size = 1
  • The JDBC URL property useCursorsAlways ⇒ default fetch size = 128

i-net UNA

  • The JDBC URL property fetchsize ⇒ default fetch size = this value

i-net SPRINTA, i-net OPTA and i-net MERLIA only:

  • use another scrolling or concurrency type like read only, forward only ⇒ default fetch size = 128 or
  • use setFetchSize ⇒ fetch size = this value

The messages "access denied" or "cannot access" in a Browser

This problem can occur in one of the following causes:

  • An unsigned applet can only connect to the webserver not to other servers. The SQL server and the webserver must be the same. A solution for this problem could be the usage of the JDBC to JDBC driver i-net KONNEKTER.
  • If your web-server is your local computer you must not connect with “localhost” or “127.0.0.1” but with the name or the IP-address of your computer.
  • If you have a name resolution problem the IP-address for the SQL server can not be identically resolved with the name of the web-server and v.v. Therefore always use the same syntax for the name of both servers (web-server and SQL server). A combination of ip address and host name is not valid (see below). This can be accomplished best with getCodeBase().getHost().
http://MyWebServer/myApplet.html jdbc:inetdae:100.100.100.100 invalid
http://100.100.100.100/myApplet.html jdbc:inetdae:MyWebServer invalid
http://MyWebServer/myApplet.html jdbc:inetdae:MyWebServer valid
http://100.100.100.100/myApplet.html jdbc:inetdae:100.100.100.100 valid
http://MyWebServer/myApplet.html “jdbc:inetdae:” + getCodeBase().getHost() valid
http://100.100.100.100/myApplet.html “jdbc:inetdae:” + getCodeBase().getHost() valid

SecurityException in a Browser

Connection reset by peer

This message means that the socket to the SQL server was closed. This can occur when

  • you restart or reboot the SQL server
  • the SQL server kills the process of your connection. You can take a look into the SQL server log to receive more info about your problem. If you have a different problem like a “Read timed out” the connection synchronisation can be corrupt.
  • you have a network problem (e.g., firewall, router). Some network components kill a TCP/IP connection after a time of inactivity to reduce the connection costs.
  • the SQL server has been busy for a long time an cannot respond to the socket.

In general if you use a database connection over a long time you should check it before you use it. Example for connection check:

try{
	if (con.isClosed()) con = DriverManager.getConnection( ..... );
}catch(Exception e){
	e.printStackTrace();
	con = DriverManager.getConnection( ..... );
}

Syntax error converting datetime from character string.

I am trying to execute the following SQL statement against our SQL Server database:

INSERT INTO Notice (Id, DateApproved, DateTimeReceived)
VALUES (1,{d'2002-1-1'},'edyke',{ts'2001-1-12 10:20:54.27'})

and receive the following error: Syntax error converting datetime from character string.

What could be causing this? You need to use two digits for the months and day. The correct syntax:

  • for a date column: {d 'yyyy-mm-dd'}
  • for a datetime column: {ts 'yyyy-mm-dd hh:mi:ss'} or {ts 'yyyy-mm-dd hh:mi:ss.mmm'}.

Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

You have called a Connection.rollback() in a Distributed Transaction. You need to call Transaction.rollback().

java.lang.AbstractMethodError

This exception will occur if you call a method from a higher JDBC API level than the driver implements. For every JDBC API level and every driver a minimum JDK version is needed. I-net software offers drivers for 3 different JDBC API level.

  • JDBC 1.22 ⇒ i-net UNA minimum JDK 1.1
  • JDBC 2.0 ⇒ i-net SPRINTA and i-net OPTA minimum JDK 1.2
  • JDBC 3.0 ⇒ i-net MERLIA minimum JDK 1.4

For example:

  • You use a JDBC 3.0 method and compile it with the JDK 1.4. You use i-net OPTA at runtime and the “AbstractMethodError” occurs.
  • You use a JDBC 2.0 method and compile it with the JDK 1.3. You use i-net UNA at runtime and the “AbstractMethodError” occurs.
  • You use a JDBC 3.0 method and use i-net MERLIA with the JDK 1.4. This works fine. You use a JDBC 3.0 method and use i-net MERLIA with the JDK 1.3. The Exception “JDK 1.4 is required” occurs.

Exception: [...]nxxx data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

The SQL server 7.0 supports new datatypes, i.e. nchar, ntext, nvarchar, varchar larger than 255 character. If you like to use these new datatypes you need to use the subprotocol inetdae7 or set the property sql7=true in the url of the JDBC connection. If you use the subprotocol inetdae7 or set the property sql7=true you will not be able to connect to the SQL Server 6.5.

You can find more information about this topic in the file View the Manual and the current Release Changes..

Where can i find a list of Java objects and their corresponding SQL data types as mapped by i-net OPTA?

The default data type mapping of the driver is equivalent to the JDBC specification:

  • String ⇒ VARCHAR, TEXT or nVARCHAR, nTEXT
  • java.math.BigDecimal ⇒ NUMERIC
  • java.math.BigInteger ⇒ NUMERIC
  • Boolean ⇒ BIT
  • Byte ⇒ TINYINT
  • Short ⇒ SMALLINT
  • Integer ⇒ INTEGER
  • Long ⇒ BIGINT
  • Float ⇒ REAL
  • Double ⇒ FLOAT
  • byte[] ⇒ VARBINARY, LONGVARBINARY
  • InputStream ⇒ VARBINARY, LONGVARBINARY
  • java.sql.Date ⇒ DATETIME
  • java.sql.Time ⇒ DATETIME
  • java.sql.Timestamp ⇒ DATETIME
  • null ⇒ NULL

java.sql.SQLException: [ServerHost]The cursor is READ ONLY

You generated a statement with the property ResultSet. CONCUR_UPDATABLE and receive this error message when updating data. The following might cause the error:

  • You generated the statement with TYPE_SCROLL_INSENSITIVE.
  • Your table lacks a primary key.

You can obtain further information on this topic in the Books Online of MS SQL Server looking for “Implicit Cursor Conversions”.

See also:

java.sql.SQLException: [TDS Driver]xxxx driver requires a JVM 1.2 or higher.

The driver checks the required JDBC 2.0 interface. The JVM that you use does not have not this interface. If you use the driver in a browser you need to use a JVM plugin because no browser supports a JVM 1.2.x currently. You can check the version of your JVM with:

System.out.println( System.getProperty( "java.version" ) );
System.out.println( System.getProperty( "java.vendor" ) );

java.lang.NoClassDefFoundError: javax/sql/DataSource orjava.lang.NoClassDefFoundError: javax/naming/Referenceable

If you use connection pooling, DataSource or the driver “com.inet.pool.PoolDriver” you will have to download:

  • the JDBC 2.0 Optional Package interface (javax.sql.*) at Sun's website.
  • the JNDI 1.2.1 class libraries (javax.naming.*) at Sun's website if you do not use the JDK 1.3.x. (J2SE version 1.3).

ClassNotFoundException: java.sql.SavepointMethod setSavepoint(java.lang.String) not found in interfacejava.lang.NoClassDefFoundError: java/sql/Savepoint

The JDBC 3.0 driver i-net MERLIA is a driver for the JDK 1.4.x. If you need a driver for an older JDK then you can use one of our other drivers for MS SQL Server.

SQLException: Address in use: no further informationor SQLException: Address already in use: connect

The message text can vary with different Java VM's

com.inet.tds.SQLException: Address in use: no further information
java.net.BindException: Address in use: no further information
java.net.PlainSocketImpl.socketConnect(Native Method)
java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:305)
java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:125)
java.net.PlainSocketImpl.connect(PlainSocketImpl.java:112)
java.net.Socket.<init>(Socket.java:269)
java.net.Socket.<init>(Socket.java:98)
com.inet.tds.TdsDriver.connect(Unknown Source)

This is a problem of the used sockets with Windows NT. You can request the active sockets with the command netstat. The problem is a function of:

MaxUserPort (default 5000)
KeepAliveTime (default 120)
  • The MaxUserPort describes the highest port number that new Socket() uses.
  • The KeepAliveTime describes the time after a Socket.close() the Socket port has been marked as being in use (TIME_WAIT).

The exception “com.inet.tds.SQLException: java.net.BindException: Address in use” occurs if you want to create a new socket and all sockets between 1024 and MaxUserPort are in use.

With the formula: (MaxUserPort - 1024 - UsedSocket) / KeepAliveTime you can calculate the count of sockets that can be created per second. With the default values you can create a maximum of 33 sockets per second.

Workaround:

  1. Use connection pooling. Our driver i-net OPTA (JDK 1.2 or higher) includes i-net PLEXA for internal pooling.
  2. increment the MaxUserPort.
 

© Copyright 1996 - 2017, i-net software; All Rights Reserved.