Oracle XE/Servoy update/insert problems

[john.allen]

Hi,

I've been traveling for a couple of weeks and decided to more or less replicate my normal work environment for some work I wanted to do. Instead of installing the full Oracle package though I installed Oracle XE. When doing any kind of insert or update though through Servoy I am getting an error to the screen that I am not filling in a required field. The servoy_log says:

2009-12-30 14:46:21,397 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - Error executing sql: update ONC_VISITS set ENTRYID=? where VIS_NUM = ? with params: [[Ljava.lang.Object;@1b6fb73]
com.servoy.j2db.persistence.RepositoryException: couldn't save oracle blob/clob, lob handler not found: java.lang.ClassNotFoundException: com.servoy.extensions.oraclefix.OracleLobHandler

It doesn't matter what the type of the fields are that I am updating/inserting. The one above is a simple NUMBER column. There is no blob or clob column in the table specified in any case so don't know where this is coming from. Anyone have any thoughts?
Oracle XE
Servoy 3.5.10
Windows XP (running on Parallels)

[ROCLASI]

Hi John,

Are you using the proper JDBC driver for Oracle XE ?
I am not sure if it need it's own driver but it might be something to look at.
Usually Oracle is pretty version specific (even at the x.x.'ed release).

[john.allen]

Hi Robert,

I should have mentioned that I have done updates/inserts from Aqua Data Studio successfully on this Oracle XE database. I thought that might be it and so I copied the ADS driver to the Servoy installation but I still get the same problem. I think that means that the driver is not the problem.

John

[ROCLASI]

Hi John,

I noticed the last part of the error message.
Do you have the file 'oraclelobfix.jar' in the drivers directory ?

[john.allen]

Bingo. That's it. The sad thing is that I work with Oracle all the time and that driver is always in my Servoy applications. Somehow in moving back and forth setting this up with Parallels I left it out. Truth be told I never knew exactly what it did and when ADS worked fine doing inserts/updates I just figured that the driver wasn't the problem (the oraclelobfix.jar isn't one of the ADS drivers and that was the only place I checked). I don't understand exactly why Servoy needs it and ADS doesn't but I don't really need to! I just have to make sure I include it!

Many thanks!

[ROCLASI]

Hi John,

I think it has to do with prepared statements. Servoy uses them all the time.
And I don't think you will be using prepared statements in ADS a lot .

[john.allen]

Actually ADS does allow 'parameterized scripts' which might be more or less the same thing. But you are right I very seldom have something that I need to use it for And I imagine ADS must handle it differently somehow because I think you must be right about that being the reason as there are no CLOBs of BLOBs in the tables I was working on.

Thanks again!

[jcompagner]

Oracle driver do have a problem (maybe the fixed it with the latest releases but my hope isnt high in that regard) using the standard jdbc api and setting byte[] columns bigger then 4k.
More then 4kb where just not inserted into the database through the standard jdbc api call PreparedStatemet.setBytes()
What we do is we wrap it and compile directly against oracle drivers that uses oracles calls (so not standard api calls) and then it works.

Welcome to the great world of oracle...

[john.allen]

Welcome to the great world of oracle...

Tell me about it... Installing that sucker is a nightmare... XE on Windows at least isn't too bad. And the drivers for Linux and Macs don't handle it smoothly if there is a 'timeout' with most JDBC applications (the Windows drivers don't have that problem). With Servoy I always put a headless client chron job running sending a dummy request every few minutes to make sure of keeping the connection always open. You guys might have something in Servoy though that keeps that server connection open anyway. I'm not sure I've seen the same issues with Servoy as I have with Aqua Data Studio or other JDBC applications on a Mac where it takes 8 minutes to get a response so that you can reconnect if it times out. But I do it with my Servoy/Oracle applications anyway just to be sure.

[jcompagner]

we do keep them idle see the admin pages setting of: Maximum connections idle
If you have that on 10 or something then up 2 10 connections that are created by servoy are pooled and not released.

[bcusick]

Oracle driver do have a problem (maybe the fixed it with the latest releases but my hope isnt high in that regard) using the standard jdbc api and setting byte[] columns bigger then 4k.
More then 4kb where just not inserted into the database through the standard jdbc api call PreparedStatemet.setBytes()
What we do is we wrap it and compile directly against oracle drivers that uses oracles calls (so not standard api calls) and then it works.

Welcome to the great world of oracle...

I'm getting the error:

Code: Select all

com.servoy.j2db.persistence.RepositoryException: java.lang.NumberFormatException: For input string: "4294967295" For input string: "4294967295"
       at com.servoy.j2db.server.dataprocessing.Zw.Za(Zw.java:1136)
       at com.servoy.j2db.server.dataprocessing.Zw.Za(Zw.java:925)
       at com.servoy.j2db.server.dataprocessing.Zw.performQuery(Zw.java:337)
       at com.servoy.j2db.debug.ProfileDataServer.performQuery(ProfileDataServer.java:284)

I'm trying to use a CLOB in oracle with a size bigger than 4K (e.g. a 30k text field for HTML stuff). Is this just not supported by the driver or is there some workaround?

I DO have the "oraclelobfix.jar" in the drivers folder.

Servoy 7.4.9 with Java 1.8.0_121

FULL STACK:

Code: Select all

com.servoy.j2db.persistence.RepositoryException: java.lang.NumberFormatException: For input string: "4294967295" For input string: "4294967295"
       at com.servoy.j2db.server.dataprocessing.Zw.Za(Zw.java:1136)
       at com.servoy.j2db.server.dataprocessing.Zw.Za(Zw.java:925)
       at com.servoy.j2db.server.dataprocessing.Zw.performQuery(Zw.java:337)
       at com.servoy.j2db.debug.ProfileDataServer.performQuery(ProfileDataServer.java:284)
       at com.servoy.j2db.dataprocessing.RowManager.getRows(RowManager.java:525)
       at com.servoy.j2db.dataprocessing.FoundSet.createRecord(FoundSet.java:3248)
       at com.servoy.j2db.dataprocessing.FoundSet.getRecord(FoundSet.java:2404)
       at com.servoy.j2db.dataprocessing.FoundSet.getRecord(FoundSet.java:2375)
       at com.servoy.j2db.util.model.AlwaysRowSelectedSelectionModel.getRecordAndTestSize(AlwaysRowSelectedSelectionModel.java:364)
       at com.servoy.j2db.util.model.AlwaysRowSelectedSelectionModel.setSelectedRow(AlwaysRowSelectedSelectionModel.java:197)
       at com.servoy.j2db.util.model.AlwaysRowSelectedSelectionModel.setSelectedRow(AlwaysRowSelectedSelectionModel.java:177)
       at com.servoy.j2db.dataprocessing.SwingFoundSet.setSelectedIndex(SwingFoundSet.java:134)
       at com.servoy.j2db.dataprocessing.TableAndListEventDelegate$1.run(TableAndListEventDelegate.java:153)
       at com.servoy.j2db.dataprocessing.TableAndListEventDelegate.fireTableAndListEvent(TableAndListEventDelegate.java:180)
       at com.servoy.j2db.dataprocessing.SwingFoundSet.fireFoundSetEvent(SwingFoundSet.java:149)
       at com.servoy.j2db.dataprocessing.FoundSet.fireDifference(FoundSet.java:4880)
       at com.servoy.j2db.dataprocessing.FoundSet.refreshFromDBInternal(FoundSet.java:547)
       at com.servoy.j2db.dataprocessing.FoundSet.browseAll(FoundSet.java:324)
       at com.servoy.j2db.dataprocessing.FoundSet.loadAllRecords(FoundSet.java:290)
       at com.servoy.j2db.BasicFormController.loadAllRecordsImpl(BasicFormController.java:1340)
       at com.servoy.j2db.BasicFormController$1.run(BasicFormController.java:348)
       at com.servoy.j2db.server.headlessclient.EventsRunnable.run(EventsRunnable.java:58)
       at com.servoy.j2db.server.headlessclient.WebClient.executeEvents(WebClient.java:571)
       at com.servoy.j2db.server.headlessclient.EventsRunnable.run(EventsRunnable.java:70)
       at com.servoy.j2db.server.headlessclient.WebClient.executeEvents(WebClient.java:571)
       at com.servoy.j2db.server.headlessclient.WebClientsApplication$8.respond(WebClientsApplication.java:714)
       at org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1287)
       at org.apache.wicket.RequestCycle.step(RequestCycle.java:1358)
       at org.apache.wicket.RequestCycle.steps(RequestCycle.java:1465)
       at org.apache.wicket.RequestCycle.request(RequestCycle.java:545)
       at org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:486)
       at com.servoy.j2db.server.servlets.Zt.doGet(Zt.java:9)
       at org.apache.wicket.protocol.http.WicketServlet.doGet(WicketServlet.java:138)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
       at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:615)
       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
       at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
       at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
       at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:620)
       at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
       at java.lang.Thread.run(Unknown Source)

[bcusick]

SOLVED: I was unwittingly using that OLD classes12.zip driver.

Once I updated to the ojdbc6.jar (http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html) it's all good.

[patrick]

I don't know why everybody is ranting here against Oracle. Once it's up and running and you've paid your bills, it's a great DB . One thing I noticed when working with Oracle years ago: you need to have the very exact driver for your database version. With very exact I mean not just some latest driver, but the one that really matches the full version (so e.g. "11.1.0.3"). If you have the wrong driver (e.g. "11.1.0.2"), everything starts to become "strange". Especially field sizes are wrong (a varchar(5) is reported to Servoy as varchar(20)), LOB handling becomes strange etc. So make sure you have the exact driver for the DB you connect to.