Can No Longer Properly Connect to Oracle 10g

[gsl1]

Currently evaluating: SmartClient_SC_SNAPSHOT-2010-11-17
Browser: Firefox 3.6.12
Database: Oracle 10g R2

I connected once with no problems to our internal dbase use the SmartClient Admin Console. Now,
I can no longer properly connect to our internal Oracle dbase server when I use the Admin console. I get an insufficient priveleges [sic] complaint. However, if I ignore this complaint and select the line item in the database configuration tab, I can browse the database and can see all of the tables and associated columns. I also can use Oracle client sqlplusw to connect from the identical workstation with no difficulty using the identical credentials and ran the pingtest sql statement with no difficulty using sqlplusw.

I am currently using Oracle 11g drivers, since the Oracle FAQ indicates that said drivers are compatible with 10g, but provide access to jdk 1.6. I have also tried the 10g drivers with no success.

Aside from the unable to connect but ability to browse functionality, I cannot access the database from Visual Builder either (not really a surprise, but since I could browse the dbase I was initially hopeful).

I have completely thrown away the SmartClient install and reinstalled and changed the server.properties file within the WEB-INF/classes directory and I have placed the Oracle drivers in the WEB-INF/lib directory. I have also rebooted, cleared browser caches and reloaded all pages to no avail.

Any ideas on how to debug this?

I have captured the output sent to the console when I press the Admin Console Test button (obviously passwords and the like have been changed):


=== 2010-11-17 15:45:37,778 [sor3] INFO RequestContext - URL: '/tools/adminConsoleOperations.jsp', User-Agent: 'Mozilla/5.0 (Windows; U; Windows NT 5
.1; en-US; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12 ( .NET CLR 3.5.30729)': Moz (Gecko) with Accept-Encoding header
=== 2010-11-17 15:45:37,778 [sor3] DEBUG XML - Parsed XML from (in memory stream): 0ms
=== 2010-11-17 15:45:37,794 [sor3] DEBUG RPCManager - Processing 1 requests.
=== 2010-11-17 15:45:37,794 [sor3] DEBUG RPCManager - Request #1 (RPCRequest) data: {
appID:"isc_builtin",
className:"com.isomorphic.tools.AdminConsole",
methodName:"testDB",
arguments:[
"Oracle",
{
"driver.networkProtocol":"tcp",
"database.type":"oracle",
"database.supportsSQLLimit":"false",
"interface.type":"dataSource",
"driver.serverName":"myHost",
"driver.portNumber":"1521",
"driver.password":"mypasswd",
"driver.user":"myuserid",
dbName:"Oracle",
isDefault:true,
dbStatus:"Unable to connect",
"driver.driverType":"thin",
driver:"oracle.jdbc.pool.OracleDataSource",
"driver.databaseName":"mydbase",
autoJoinTransactions:"true",
_selection_3:true,
"driver.context":null,
"interface.credentialsInURL":true
}
],
is_ISC_RPC_DMI:true
}
=== 2010-11-17 15:45:37,794 [sor3] DEBUG RPCDMI - appConfig: isc.Application.create({
rpcBindings:[
{
ID:"builtin",
className:"com.isomorphic.rpc.BuiltinRPC",
visibleMethods:[
{
name:"downloadWSDL"
},
{
name:"downloadClientContent"
},
{
name:"downloadClientExport"
},
{
name:"xmlToJS"
},
{
name:"uploadProgressCheck"
},
{
name:"saveFile"
},
{
name:"appendToFile"
},
{
name:"loadFile"
},
{
name:"deleteFile"
},
{
name:"loadSharedXML"
},
{
name:"saveSharedXML"
},
{
name:"getAvailableScriptEngines"
},
{
name:"devConsoleEvalServerScript"
},
{
name:"evalJava"
},
{
name:"getLogNames"
},
{
name:"getLogEntries"
},
{
name:"getLogThresholds"
},
{
name:"setLogThreshold"
}
]
},
{
ID:"builtin_tools",
className:"com.isomorphic.tools.BuiltinRPC",
visibleMethods:[
{
name:"getDataSourceFromTable"
},
{
name:"getDataSourceJSONFromTable"
},
{
name:"getDataSourceFromHibernateMapping"
},
{
name:"getDataSourceJSONFromHibernateMapping"
},
{
name:"getTables"
},
{
name:"getFieldsFromTable"
},
{
name:"getBeanFields"
},
{
name:"getHibernateBeans"
},
{
name:"getDatabaseProductNameAndVersion"
},
{
name:"getDatabaseTableTypes"
},
{
name:"setAttributes"
},
{
name:"clearAttributes"
},
{
name:"getAttributes"
},
{
name:"getAttribute"
},
{
name:"getDataSourceConfigFromJavaClass"
},
{
args:"cName",
language:"groovy",
name:"getJavaSource",
script:"\n if (!com.isomorphic.auth.DevModeAuthFilter.devModeAuthorized(request)) throw new Exception(\"Not Aut
horized\"); \n //import org.apache.bcel.Repository;\n\n try {\n return
org.apache.bcel.Repository.lookupClass(cName).toString();\n } catch (Throwable e) {\n return \"Unable to re
verse engineer class \"+cName+\": \"+e.getMessage();\n }\n "
},
{
name:"loadDataSource"
},
{
name:"dsFromXML"
},
{
name:"dsConfigFromXML"
},
{
name:"getDefinedDataSources"
}
]
},
{
ID:"builtin_adminconsole",
className:"com.isomorphic.tools.AdminConsole",
visibleMethods:[
{
name:"getDefinedDatabases"
},
{
name:"testDB"
},
{
name:"saveDBConfig"
},
{
name:"setDefaultDB"
},
{
name:"importDataSources"
},
{
name:"discoverJNDIDatabases"
}
]
}
]
})

=== 2010-11-17 15:45:37,825 [sor3] INFO BuiltinRPC - testing db 'Oracle' with config: {
"driver.networkProtocol":"tcp",
"database.type":"oracle",
"database.supportsSQLLimit":"false",
"interface.type":"dataSource",
"driver.serverName":"myHost",
"driver.portNumber":"1521",
"driver.password":"mypasswd",
"driver.user":"myuserid",
dbName:"Oracle",
isDefault:true,
dbStatus:"Unable to connect",
"driver.driverType":"thin",
driver:"oracle.jdbc.pool.OracleDataSource",
"driver.databaseName":"mydbase",
autoJoinTransactions:"true",
_selection_3:true,
"driver.context":null,
"interface.credentialsInURL":true
}
=== 2010-11-17 15:45:37,841 [sor3] DEBUG BuiltinRPC - Testing DB connection for database: Oracle with config: {
"driver.networkProtocol":"tcp",
"database.type":"oracle",
"database.supportsSQLLimit":"false",
"interface.type":"dataSource",
"driver.serverName":"myHost",
"driver.portNumber":"1521",
"driver.password":"mypasswd",
"driver.user":"myuserid",
dbName:"Oracle",
isDefault:true,
dbStatus:"Unable to connect",
"driver.driverType":"thin",
driver:"oracle.jdbc.pool.OracleDataSource",
"driver.databaseName":"mydbase",
autoJoinTransactions:"true",
_selection_3:true,
"driver.context":null,
"interface.credentialsInURL":true
}
=== 2010-11-17 15:45:37,841 [sor3] DEBUG PoolableSQLConnectionFactory - Initializing SQL config for 'Oracle' from system config - using DataSource: o
racle.jdbc.pool.OracleDataSource
=== 2010-11-17 15:45:37,841 [sor3] INFO DataTools - setProperties: couldn't set:
{
context:"No such property"
}
=== 2010-11-17 15:45:37,997 [sor3] DEBUG PoolableSQLConnectionFactory - Returning unpooled Connection
=== 2010-11-17 15:45:38,044 [sor3] INFO BuiltinRPC - testDB (Oracle) error: com.isomorphic.tools.SQLTestException: Insufficient Priveleges
=== 2010-11-17 15:45:38,044 [sor3] DEBUG RPCDMI - rpc returned RPCResponse
=== 2010-11-17 15:45:38,044 [sor3] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2010-11-17 15:45:38,044 [sor3] INFO Compression - /tools/adminConsoleOperations.jsp: 198 -> 167 bytes

[Isomorphic]

The SQL used to test the validity of the connection is "select 1 from dual". Maybe your user is able to access a variety of tables (hence can browse the DB) but cannot execute this validity test?

You can change the validity test SQL in server.properties:

sql.oracle.pingTest: select 1 from dual

[gsl1]

As I mentioned in the post, I can connect as the identical user using Oracle client for 10g from the identical workstation and ran the ping test you described with no problems.

[Isomorphic]

Sorry, wasn't sure if by "pingtest" you meant specifically the pingtest the framework performs or some other test of your own.

Looking more closely at where that error comes from, what is being tested is actually the ability to create and drop tables, since without this ability, several parts of the Admin Console will not work.

If you don't need to do this, your settings saved to server.properties should be sufficient for normal DB access.

We'll clarify the error handling here so it doesn't appear to be a bad connection.

[gsl1]

I have what I believe is a related problem which I did not bring up initially, since I wanted to see if the first problem could be resolved and would lead to a solution to the following problem.

Even if you "clarify the error handling" in a future build (I assume), at this time I also cannot use Visual Builder anymore to create datasources from existing tables. This worked once and just once and you guessed it, it doesn't work anymore. In fact the Oracle database is not even a choice in the database dropdown when running under the same embedded Tomcat at the same time that the admin console is that can browse the dbase. Only the internal HSQLDB is. How can I get around this problem and use the datasource creation functionality which would be really useful for me? Surely this functionality does not require the ability to create or drop tables, right?

Note that you are correct. The account that I have been given by the DBA is forbidden to create or drop tables.

[Isomorphic]

Although we may try to make the tools work with restricted rights in the future, for now, we would recommend using an Oracle user without such restricted rights when using the Development Tools. They are development tools and developers typically work against environments where they have rights to do whatever they need to for testing purposes.