Oracle XE/Servoy update/insert problems

Questions and answers regarding general SQL and backend databases

Oracle XE/Servoy update/insert problems

Postby john.allen » Wed Dec 30, 2009 4:59 pm

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)
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Re: Oracle XE/Servoy update/insert problems

Postby ROCLASI » Wed Dec 30, 2009 5:10 pm

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).
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5230
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Oracle XE/Servoy update/insert problems

Postby john.allen » Wed Dec 30, 2009 5:28 pm

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
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Re: Oracle XE/Servoy update/insert problems

Postby ROCLASI » Wed Dec 30, 2009 5:44 pm

Hi John,

I noticed the last part of the error message.
Do you have the file 'oraclelobfix.jar' in the drivers directory ?
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5230
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Oracle XE/Servoy update/insert problems

Postby john.allen » Wed Dec 30, 2009 6:19 pm

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!
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Re: Oracle XE/Servoy update/insert problems

Postby ROCLASI » Wed Dec 30, 2009 6:41 pm

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 :) .
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5230
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Oracle XE/Servoy update/insert problems

Postby john.allen » Thu Dec 31, 2009 1:54 am

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!
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Re: Oracle XE/Servoy update/insert problems

Postby jcompagner » Mon Jan 04, 2010 2:06 pm

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...
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8008
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Oracle XE/Servoy update/insert problems

Postby john.allen » Tue Jan 05, 2010 12:08 am

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.
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Re: Oracle XE/Servoy update/insert problems

Postby jcompagner » Tue Jan 05, 2010 10:18 am

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.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8008
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Oracle XE/Servoy update/insert problems

Postby bcusick » Fri Mar 10, 2017 9:51 pm

jcompagner wrote: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)
Bob Cusick
bcusick
 
Posts: 1190
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Oracle XE/Servoy update/insert problems

Postby bcusick » Fri Mar 10, 2017 10:34 pm

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.
Bob Cusick
bcusick
 
Posts: 1190
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Oracle XE/Servoy update/insert problems

Postby patrick » Sat Mar 11, 2017 1:39 pm

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.
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3622
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 3 guests

cron