I'm having a problem with a proprietary JDBC driver to a middle tier server. The datasource that I have defined seems to work OK from a beanshell query but not from the sql or sql-query macros. The following is the result from sql-query, sql, and beanshell run:
sql-query: java.sql.SQLException: The method Connection.setReadOnly is not implemented yet
select Fld1, Fld2 FROM Table WHERE Fld1 like '608174%'
sql: java.sql.SQLException: The method Connection.setReadOnly is not implemented yet
select Fld1, Fld2 FROM Table WHERE Fld1 like '608174%'
Isolation set: 0
Columns in result set: 2
6081740 EA2
The following is the code (beanshell code is modified copy from the scripts plugin page):
{sql-query:dataSource=PhsDev|output=wiki|showsql=true}
select Fld1, Fld2 FROM Table WHERE Fld1 like '608174%'{sql-query}
{sql:dataSource=PhsDev|output=wiki|showsql=true|transactionIsolation=none}
select Fld1, Fld2 FROM Table WHERE Fld1 like '608174%'{sql}
{beanshell}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.naming.NameNotFoundException;
String dataSource = "PhsDev";
String sql = "select Fld1, Fld2 FROM Table WHERE Fld1 like '608174%'";
DataSource ds = null;
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet;
try {
ds = (DataSource) (new InitialContext()).lookup("java:comp/env/jdbc/" + dataSource);
connection = ds.getConnection();
int isolation = connection.getTransactionIsolation();
out.println("<br>Isolation set: " + isolation);
statement = connection.prepareStatement(sql);
if (statement.execute()) { // result set is available
resultSet = statement.getResultSet();
if (resultSet != null) {
ResultSetMetaData rsmd = resultSet.getMetaData();
out.println("<br>Columns in result set: " + rsmd.getColumnCount());
// out.println("<br>Space names: ");
while (resultSet.next()) {
out.println("<br> " + resultSet.getString("Fld1") +
" " + resultSet.getString("Fld2")); //"spacename"));
}
}
} else { // only update count available
out.println("Update count: " + statement.getUpdateCount());
}
}
catch (NameNotFoundException exception) {
out.println(exception.toString());
}
catch (NamingException exception) {
out.println(exception.toString());
}
catch (SQLException exception) {
out.println(exception.toString());
} }
finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (connection != null) {
connection.close();
}
}
catch(Exception discard) {
}
}{beanshell}
The following is the datasource definition:
<Resource name="jdbc/PhsDev" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/PhsDev">
<parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
<parameter><name>driverClassName</name><value>com.phs.client.jdbc.PhsJDBCDriver</value></parameter>
<parameter>
<name>url</name>
<value>jdbc:scwapi://host/NameServer;uid;pswd;db;schema;uid2;pswd2</value>
</parameter>
<parameter><name>maxActive</name><value>20</value></parameter>
<parameter><name>maxIdle</name><value>10</value></parameter>
<parameter><name>maxWait</name><value>-1</value></parameter>
<parameter><name>removeAbandoned</name><value>true</value></parameter>
<parameter><name>removeAbandonedTimeout</name><value>600</value> </parameter>
<parameter><name>logAbandoned</name><value>true</value> </parameter>
</ResourceParams>
Versions used: Confluence Version: 2.6.0 Build:#913 Sep 27, 2007 on Tomcat 5.0.25
Also tried the transactionIsolation switches without success. Any ideas would be appreciated.
Bug
Resolved
Major
setReadOnly method is not implemented error
.
... loop on results ... result = statement.getMoreResults(); if (result) { // result set returned resultSet = statement.getResultSet(); ... } else { // no result set, must get update count instead statement.getUpdateCount() ... }getUpdateCount is suppose to return -1 if there are no more results sets or no update count is available
To avoid this, I have added temporary parameter - set multipleResults = false and see how that does with the following driver: beta-fix2