Possible to connect SmartClient to Access database?

JPPoulin · May 5, 2010, 2:35am

Hello,

I’m attempting to connect SmartClient to a Microsoft Access database…

I am succesful in connecting to mysql and sql server databases by downloading & installing the correct jdbc driver, but - as the ‘database configuration’ SmartClient tool does not permit to add a database type of odbc or access, I’m wondering if I should proceed further.

Alternative: if someone could give pointers on how to correctly configure SmartClient to connect to ODBC databases, I could make do with that.

I’ve been butting my head on my monitor the whole day on these JDBC driver issues! Any hint would be greatly appreciated!!

Jean-Pierre

JPPoulin · May 5, 2010, 3:06pm

Solved problem…

Hi everyone,

In case anyone else is trying to do the same thing, I was able to connect to my Access .mdb database using hxtt’s Access JDBC driver (other drivers for Access available)

Here are my settings that worked:
Driver class name: com.hxtt.sql.access.AccessDriver
Driver JDBC string: jdbc:access:/E:/Apps/Office2003/OFFICE11/SAMPLES/Northwind2003.mdb

Good luck!

JP

Isomorphic · May 5, 2010, 5:26pm

Thanks for posting the solution!

JPPoulin · May 8, 2010, 4:24pm

Gave up on Access & ODBC… upsized to SQL Server.

Hi all,

While the above solution did enable the SmartClient test to connect OK to my access database, the server would report several exceptions as I proceeded further to design & use this connection.

(I also tried JDBC-ODBC bridge by Sun, and I kept getting the same type of errors on the server. Unfortunately, the ‘database type’ forces connections like SQL Server or db2 or mysql or oracle, but no ODBC or Access hence the server errors)

I therefore had to give up on trying to get ODBC or Access to work on SmartClient, used the (fantastic) “Microsoft SQL Server Migration Assistant 2008 for Access”, connected with SmartClient to my upsized SQL server, and I can not interact with the db correctly.

If someone ever gets ODBC or Access to work correctly with SmartClient, please send me a note!

Jean-Pierre

Isomorphic · May 8, 2010, 6:40pm

What are the erros you’re running into, are they problems with Access not liking the SQL statements themselves? Those could be easy to correct if you can spell out a few samples of where we’re generating the wrong SQL and what the right SQL would be.

JPPoulin · May 9, 2010, 6:44pm

Server-side errors during ODBC access to Access database.

Hi and thanks for your help.

Currently, I’m focusing on getting SmartClient to work with a generic ODBC source, so while Access is actually my backend, I expect this same type of problem would occur when using SmartClient with any ODBC data source, such as an CSV file, an excel spreadsheet, other db through the ODBC driver.

To replicate the problem, do the following:

Connect an ODBC database using Window’s ODBC control panel app. (I’m using Access’ sample called Northwind)
In SmartClient Admin Console, create a new database configuration with the settings:
a. “Obtain Connections Using”: DriverManager
b. “Driver Implementer”: sun.jdbc.odbc.JdbcOdbcDriver
c. Check “Specify explicit JDBC URL
d. Driver URL: jdbc:odbc:<NameYouGaveYourDatabaseInODBC> (example jdbc:odbc:Northwind)
e. UNKNOWN: There is no ODBC choice in the combo box for “Database Type”. I have tried each one and each fails during usage, indicating that your code probably cannot talk to ODBC databases.
Test and SmartClient should report OK.
In Visual Builder, create a simple form to test the ODBC database. (Steps below assume HSQLDB were selected in step 2.e above… other databases give other problems)
a. In the lower left “Data Source”, click New to create a new data source
b. In the DataSource Wizard, select “Existing SQL Table” and press Next
c. In the Database Browser, select the database configuration you created in step 2
d. Try to expand one of the tables of your database, and nothing should happen.
e. Go back to your Server log, you should get something like clip A below.
f. Even though no error is given to user as table was expanded, lets proceed further to view other errors during usage. Click next to go to the final step of the wizard.
g. Note that the final step of the wizard does not have table name or database name filled in.
h. (I got further with other database types, where I could actually insert an editable list in visual builder, it would show the column name for the fields, but not the values, and the server log gave a different trapped exception with no visible clue given to the user.

Hope this helps and that you’ll be able to replicate the problem. (I’ve only tried with Access database, but I strongly suspect csv files and excel spreadsheets would crash SmartClient server in similar ways)

As I badly need this working and upsizing to SQL server just for this is a huge overkill for my current project, please give me an idea of how complex the work would be to get SmartClient working with ODBC. (If it’ll take more than a couple weeks I’m forced to move to SQL server)

Many thanks,

Jean-Pierre

— Partial clip of server log error during table expansion above —
Note the caught exception during table expansion. I’ve seen several types of other errors… just keep the log open and perform different actions with ODBC databases to see more.

=== 2010-05-09 14:31:21,796 [sor8] DEBUG Reflection - adaptArgsAndInvoke:

public static java.util.List com.isomorphic.tools.BuiltinRPC.getFieldsFromTable(java.lang.String,java.lang.String,java.lang.String) throws java.lang.Exception

requiredArgs: [java.lang.String, java.lang.String, java.lang.String] optionalArgs: [com.isomorphic.servlet.RequestContext, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse, jav
, javax.servlet.http.HttpSession, com.isomorphic.rpc.RPCManager, com.isomorphic.rpc.RPCRequest]
=== 2010-05-09 14:31:21,796 [sor8] DEBUG Reflection - checking whether type: java.lang.String fulfills type: java.lang.String
=== 2010-05-09 14:31:21,796 [sor8] DEBUG Reflection - Successfully adapted required arg type: java.lang.String to type: java.lang.String
=== 2010-05-09 14:31:21,800 [sor8] DEBUG Reflection - checking whether type: java.lang.String fulfills type: java.lang.String
=== 2010-05-09 14:31:21,800 [sor8] DEBUG Reflection - Successfully adapted required arg type: java.lang.String to type: java.lang.String
=== 2010-05-09 14:31:21,800 [sor8] DEBUG Reflection - checking whether type: java.lang.String fulfills type: java.lang.String
=== 2010-05-09 14:31:21,801 [sor8] DEBUG Reflection - Successfully adapted required arg type: java.lang.String to type: java.lang.String
=== 2010-05-09 14:31:21,801 [sor8] DEBUG Reflection - method takes: 3 args. I’ve assembled: 3 args
=== 2010-05-09 14:31:21,801 [sor8] DEBUG Reflection - invoking method:
java.util.List com.isomorphic.tools.BuiltinRPC.getFieldsFromTable(java.lang.String, java.lang.String, java.lang.String) throws java.lang.Exception

with arg types: java.lang.String, java.lang.String, java.lang.String
=== 2010-05-09 14:31:21,801 [sor8] DEBUG PoolableSQLConnectionFactory - DriverManager fetching connection for ODBC-Test via jdbc url jdbc:odbc:CompanyDB
=== 2010-05-09 14:31:21,844 [sor8] DEBUG PoolableSQLConnectionFactory - Returning pooled Connection
=== 2010-05-09 14:31:21,913 [sor8] DEBUG SQLTransform - isBeforeFirst()/isAfterLast() throwing exceptions - attempting workaround
=== 2010-05-09 14:31:21,927 [sor8] DEBUG RPCDMI - Invocation threw exception
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Driver does not support this function
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLPrimaryKeys(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDatabaseMetaData.getPrimaryKeys(Unknown Source)
at com.isomorphic.sql.SQLMetaData.getPrimaryKeys(SQLMetaData.java:291)
at com.isomorphic.sql.SQLDSGenerator.getFieldsFromTable(SQLDSGenerator.java:210)
at com.isomorphic.sql.SQLDSGenerator.getFieldsFromTable(SQLDSGenerator.java:96)
at com.isomorphic.sql.SQLDataSource.getFieldsFromTable(SQLDataSource.java:1603)
at com.isomorphic.tools.BuiltinRPC.getFieldsFromTable(BuiltinRPC.java:251)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.isomorphic.base.Reflection.adaptArgsAndInvoke(Reflection.java:867)
at com.isomorphic.base.Reflection.adaptArgsAndInvoke(Reflection.java:722)
at com.isomorphic.rpc.RPCDMI.execute(RPCDMI.java:303)
at com.isomorphic.rpc.RPCDMI.execute(RPCDMI.java:66)
at org.apache.jsp.tools.visualBuilder.vbOperations_jsp._jspService(vbOperations_jsp.java:91)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at com.isomorphic.js.JSSyntaxScannerFilter.doFilter(JSSyntaxScannerFilter.java:251)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:248)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
at java.lang.Thread.run(Unknown Source)

Isomorphic · May 9, 2010, 10:47pm

Hello,

In exception trace you can see that ODBC is not willing to provide table metadata such as primary keys. This means the wizard won’t work, but a hand written DataSource descriptor (.ds.xml file) may well work. See any of the SQL samples and the SmartClient Reference for how to write one.

As far as having official support added to the product, if work is required, it’s likely less than 2 weeks. The Feature Sponsorship program is the way to get this done.

JPPoulin · May 10, 2010, 9:47pm

Questions on ODBC databases…

Hi, thanks for your reply.

Yes, I could bypass the datasource configuration step, but I’ve seen several instances during app-usage where the server crashes.

Q1: Which of the preset database choices do you think is closest to ODBC? (e.g. SQL Server, MySQL, DB2, HSQLDB, etc)

Q2: Has anyone ever connected a SmartClient server to an ODBC database?

Q3: If not, has anyone succesfully connected a SmartClient server to an Access database? (Through a tier 4 JDBC-Access bridge)

Q4: It looks like we’ll be adopting SmartGWT instead. Should I expect the same problems with ODBC under SmartGWT?

Many thanks,

Jean-Pierre

Isomorphic · May 10, 2010, 9:49pm

So, once again, we would need specific instances of SQL not working and what is wrong with the SQL, including server errors.

Probably SQL Server

2 & 3. We’ve seen anecdotal reports only. Unclear whether it was successful for all operations or only some

Yes, it’s the same underling engine

JPPoulin · May 11, 2010, 5:56pm

Very well, I’ll try once more with direct Access class 4 JDBC driver or ODBC-JDBC bridge in SmartGWT when I get to connecting the database in SmartGWT.

If I get errors during datasource creation I’ll try to get further and attempt to get a server log during app usage.

Q: Which method between the class 4 JDBC driver and the ODBC-JDBC bridge do you think would work best with SmartClient / SmartGWT?

Jean-Pierre

Isomorphic · May 11, 2010, 5:59pm

Sorry, as to the relative quality of those drivers, your guess is as good as ours.