Tuesday, December 29, 2009

UpdateRow results error " Result set is not updatable " Or "The cursor is READ ONLY" JDBC Java sql server 2000 , sql server 2005 database connectio

The cursor is READ ONLY OR Result set is not updatable in JDBC


When running a JAVA application which uses Microsoft sql server 2005 database connectivity and trying update a table Microsoft Sql Server 2000 database. When using updaterow method to update the resultset you will receive the following error message.

If you are using Sql Server 2005 database driver version 1.1 the error message will be

com.microsoft.sqlserver.jdbc.SQLServerException: Result set is not updatable.

If you are using Sql Server 2005 database driver version 1.1 the error message will be

com.microsoft.sqlserver.jdbc.SQLServerException: The cursor is READ ONLY.

The reason for this error is the implicit cursor conversion happening in sql server 2000. The implicit conversion is happening if the query returns a result set or the the table have no primary key or unique key including the column which you re using for order by.



To avoid this error change the query or set primary key for the table.

Another way is after selecting fetch each record in resultset and manually update



REGARDS

Monday, November 30, 2009

Invalid operation for read only resultset

Invalid operation for read only resultset

Sometimes when running select queries you will get the following error message in JDBC

Invalid operation for read only resultset


The possible reasons and solutions are

Result Set Limitations

The following limitations are placed on queries for enhanced result sets. Failure to follow these guidelines will result in the JDBC driver choosing an alternative result set type or concurrency type.

To produce an updatable result set:

* A query can select from only a single table and cannot contain any join operations.

In addition, for inserts to be feasible, the query must select all non-nullable columns and all columns that do not have a default value.
* A query cannot use "SELECT * ". (But see the workaround below.)
* A query must select table columns only. It cannot select derived columns or aggregates such as the SUM or MAX of a set of columns.

To produce a scroll-sensitive result set:

* A query cannot use "SELECT * ". (But see the workaround below.)
* A query can select from only a single table.

Communication Link Failure , Mysql,jdbc

Communication Link Failure

So, I was indexing a 10+ million records in MySQL into Solr and kept coming across the following odd MySQL exception:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications
link failure
Last packet sent to the server was 4467745 ms ago
...

com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2985) at
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2871) at
In my code, I loop over a JDBC ResultSet and add the records to Solr per the Solr field schema, mapping columns to fields, etc. This would happen after getting through something like 9M+ records. After some tracking down, hypothesizing, talking with others, we came to the conclusion that the issue was a combination of having the autocommit value in Solr set and MySQL timing out the ResultSet, such that when Lucene had to do a large merge (even in the background), Solr had to wait for said merge to finish, thus keeping the ResultSet open too long w/o activity. Now, these large merges can take some time. They can happen in the background, but Solr can’t refresh it’s IndexReader until the merge finishes, AIUI. Thus, we’re stuck in the middle of a ResultSet loop, holding the cursor open past MySQL’s default setting (600 seconds, more on that later), causing MySQL to kill the connection, and rightfully so. On the MySQL side of things, we are streaming the results, since it’s JDBC driver does not support setFetchSize() (ugh!). As it turns out MySQL has a Streaming timeout value named netTimeoutForStreamingResults (see http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html) which defaults to 600 seconds.

Long story short, I have at least two options:

Turn off autocommit, meaning user’s won’t be able to see documents as soon as they may like
Increase the netTimoutForStreamingResults value. This is great for MySQL and I have verified it works, but is not a generic value for other DBs, which our code supports
I am still deciding on what to do, and also thinking of some other options that can decouple DB retrieval from the indexing process. At any rate, I wanted to post the cause of my seeing this exception, because I did not see anyone else with this exception whose cause was due to a timeout during ResultSet processing and hopefully it will save them some time.

Sunday, November 29, 2009

Exception: [MERANT][SequeLink JDBC Driver][SequeLink Server]License verification failed. JDBC database connection DriverManager.getConnection()

Exception: [MERANT][SequeLink JDBC Driver][SequeLink Server]License verification failed.

Sometimes query with database using DriverManager.getConnection() will cause the following error message

Exception: [MERANT][SequeLink JDBC Driver][SequeLink Server]License verification failed.

There is no problem if the application uses WebSphere's DataSource.

Solution
The SequeLink Java Client is branded and shipped with WebSphere Application Server 3.5.3+, 4.0.x and 5.0.x (sljc.jar and sljcx.jar which are in $WAS_HOME\lib directory where $WAS_HOME is the directory where WebSphere Application Server is installed). The branded SequeLink Java Client provides an encrypted OEM password which unlocks the SequeLink OEM Server.

The branded SequeLink Java Client can be unlocked by JDBC applications that call a special method, which is defined in the com.merant.SlExtensionInterface interface. If you fail to unlock the branded SequeLink Java Client, or if another unauthorized application tries to use the branded SequeLink Java Client, the above exception is generated.

If the application uses the DriverManager.getConnection() method, then it must call that special method to unlock the SequeLink OEM Server by itself. As a SequeLink OEM, IBM WebSphere can't give out the OEM password to customers.. If you really want to use the DriverManager.getConnection() method to obtain the database connection, then you must buy the license copy of the SequeLink Server from MERANT.

When you use WebSphere's DataSource, the Connection Manager calls that special method to unlock the SequeLink OEM Server.

Note: In 2001, DataDirect business unit spins off from MERANT to form privately-held, independent software vendor, called DataDirect Technologies.

DGL5390A for minimum string length violation

DGL5390A for minimum string length violation

An attribute that is defined in the item type or the component type has a minimum string length that is lower than or equal to your value. The VARCHAR attribute returns the error message DGL5390A:
Attribute named 'myAttribute' contains a string of length 'actual
string length', which violates the minimum required length of 'some
value' specified in the attribute definition.
Possible cause
The system is enforcing minimum and maximum VARCHAR string lengths that are based on the original attributes that were defined before the item type that uses them.

Action
Modify minimum and maximum VARCHAR string length on the original attribute definitions. Short and integer attributes are not affected.

Tip: Adjustments appear in retrieved item type and component type definitions, but not in item type view or component type view definitions.

data type out of range. JDBC error

data type out of range

You may receive the following error when doing database programming in JAVA


[IBM ][CLI Driver ] CLI0123E SQL data type out of range.
SQLSTATE=HY004

Reason
The JDBC version is back-level (Version 1).

Action to be done
Run usejdbc2.bat to ensure the JDBC upgraded from Version 1 to 2:

Stop the DB2 JDBC Applet Server service.
Run usejdbc2.bat located in SQLLIB\java12 directory (for example, c:\Program Files \sqllib \java12 ).
Restart the DB2 JDBC Applet Server service.