Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » scout » Update a CLOB field in a table
Update a CLOB field in a table [message #1042457] Tue, 16 April 2013 13:11 Go to next message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Hi all,
sometimes I have problems in updating a CLOB field on table in my database, becasue the SQL command is too long. This depends on the fact that the value that I have to save can be very long.

    
private String createSQLUpdateOrder(String idComm, String commission, String xmlOrder) {
    return "UPDATE MyTable "
        + "SET CLOBField='" + xmlOrder + "' "
        + "WHERE OrderId='" + idComm + "' And Order='" + commission + "')";
  }

public boolean saveOrderInformation(String xmlOrder, String commission, String idComm) {
    int retval = 0;

    try {
      retval = SERVICES.getService(EDICCSqlService.class).update(createSQLUpdateOrder(idComm, commission, xmlOrder));
    }
    catch (ProcessingException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      System.out.println(e.getMessage());
      retval = -1;
    }
  }



Probably I manage this kind of information in incorrectly way. Anyone has any suggestions?

Thanks in advance for your help
Re: Update a CLOB field in a table [message #1042484 is a reply to message #1042457] Tue, 16 April 2013 13:40 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 698
Registered: October 2011
Senior Member
You should consider using binds in the SQL:

private String createSQLUpdateOrder() {
        return "UPDATE MyTable "
            + "SET CLOBField=:xmlOrderBind "
            + "WHERE OrderId=:idCommBind And Order=:commissionBind)";
      }

    public boolean saveOrderInformation(String xmlOrder, String commission, String idComm) {
        int retval = 0;

        try {
          retval = SERVICES.getService(EDICCSqlService.class).update(
                           createSQLUpdateOrder(),
                           new NVPair("xmlOrderBind", xmlOrder),
                           new NVPair("idCommBind", idComm),
                           new NVPair("commissionBind", commission)
                           );
        }
        catch (ProcessingException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
          System.out.println(e.getMessage());
          retval = -1;
        }
      }


It is possible that this good practice solve your problem. Let me know.

Some documentation is started there:
http://wiki.eclipse.org/Scout/Concepts/Sql_Service
Re: Update a CLOB field in a table [message #1042506 is a reply to message #1042484] Tue, 16 April 2013 14:08 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Hi Jeremie,
I try your code but I have the following error:

ProcessingException[ProcessingStatus[ERROR code=0 SQL with binds:
UPDATE    MyTable
SET       CLOBField = :xmlOrderBind
WHERE     OrderId = :idCommBind
*** UNPARSED ***
) / Cannot find input for 'ValueInputToken[parsed 'CLOBField = :xmlOrderBind', replaced 'CLOBField = :xmlOrderBind']' in bind bases.]]
	at org.eclipse.scout.rt.server.services.common.jdbc.internal.exec.StatementProcessor.createInput(StatementProcessor.java:862)
	at org.eclipse.scout.rt.server.services.common.jdbc.internal.exec.StatementProcessor.<init>(StatementProcessor.java:131)
	at org.eclipse.scout.rt.server.services.common.jdbc.AbstractSqlService.createStatementProcessor(AbstractSqlService.java:815)
	at org.eclipse.scout.rt.server.services.common.jdbc.AbstractSqlService.update(AbstractSqlService.java:762)
	at org.zeiss.mo.scout.CZVEDIController.server.services.process.OrderDetailsProcessService.saveOrderInformation(OrderDetailsProcessService.java:201)
	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 org.eclipse.scout.service.ServiceUtility.invoke(ServiceUtility.java:172)
	at org.eclipse.scout.rt.server.DefaultTransactionDelegate.invokeImpl(DefaultTransactionDelegate.java:218)
	at org.eclipse.scout.rt.server.DefaultTransactionDelegate.invoke(DefaultTransactionDelegate.java:92)
	at org.eclipse.scout.rt.server.ServiceTunnelServlet.runServerJobTransactionWithDelegate(ServiceTunnelServlet.java:392)
	at org.eclipse.scout.rt.server.ServiceTunnelServlet.runServerJobTransaction(ServiceTunnelServlet.java:388)
	at org.eclipse.scout.rt.server.ServiceTunnelServlet$RemoteServiceJob.runTransaction(ServiceTunnelServlet.java:416)
	at org.eclipse.scout.rt.server.ServerJob.runTransactionWrapper(ServerJob.java:202)
	at org.eclipse.scout.rt.server.ServerJob.access$0(ServerJob.java:190)
	at org.eclipse.scout.rt.server.ServerJob$1.run(ServerJob.java:161)
	at org.eclipse.scout.rt.server.ServerJob$1.run(ServerJob.java:1)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Unknown Source)
	at org.eclipse.scout.rt.server.ServerJob.run(ServerJob.java:156)
	at org.eclipse.scout.commons.job.JobEx.runNow(JobEx.java:50)
	at org.eclipse.scout.rt.server.ServerJob.runNow(ServerJob.java:148)
	at org.eclipse.scout.rt.server.ServiceTunnelServlet.doPost(ServiceTunnelServlet.java:297)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:755)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
	at org.eclipse.scout.http.servletfilter.HttpServletEx.access$0(HttpServletEx.java:1)
	at org.eclipse.scout.http.servletfilter.HttpServletEx$1.service(HttpServletEx.java:38)
	at org.eclipse.scout.http.servletfilter.internal.FilterChainImpl.doFilter(FilterChainImpl.java:44)
	at org.eclipse.scout.http.servletfilter.helper.HttpAuthJaasFilter.doFilter(HttpAuthJaasFilter.java:62)
	at org.eclipse.scout.http.servletfilter.internal.FilterChainImpl.doFilter(FilterChainImpl.java:41)
	at org.eclipse.scout.http.servletfilter.helper.DevelopmentAuthFilter.doFilter(DevelopmentAuthFilter.java:61)
	at org.eclipse.scout.http.servletfilter.internal.FilterChainImpl.doFilter(FilterChainImpl.java:41)
	at org.eclipse.scout.http.servletfilter.security.AbstractChainableSecurityFilter.doFilter(AbstractChainableSecurityFilter.java:103)
	at org.eclipse.scout.http.servletfilter.internal.FilterChainImpl.doFilter(FilterChainImpl.java:41)
	at org.eclipse.scout.http.servletfilter.security.AbstractChainableSecurityFilter.doFilterInternal(AbstractChainableSecurityFilter.java:220)
	at org.eclipse.scout.http.servletfilter.security.AbstractChainableSecurityFilter.access$0(AbstractChainableSecurityFilter.java:219)
	at org.eclipse.scout.http.servletfilter.security.AbstractChainableSecurityFilter$1.run(AbstractChainableSecurityFilter.java:157)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Unknown Source)
	at org.eclipse.scout.http.servletfilter.security.AbstractChainableSecurityFilter.doFilter(AbstractChainableSecurityFilter.java:149)
	at org.eclipse.scout.http.servletfilter.internal.FilterChainImpl.doFilter(FilterChainImpl.java:41)
	at org.eclipse.scout.http.servletfilter.ServletFilterDelegate.delegateServiceMethod(ServletFilterDelegate.java:57)
	at org.eclipse.scout.http.servletfilter.HttpServletEx.service(HttpServletEx.java:35)
	at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180)
	at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61)
	at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:128)
	at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
	at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:386)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:598)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:486)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1065)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:413)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:192)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:999)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:111)
	at org.eclipse.jetty.server.Server.handle(Server.java:350)
	at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:454)
	at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:900)
	at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:954)
	at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:851)
	at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:235)
	at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:77)
	at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:606)
	at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:46)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:603)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:538)
	at java.lang.Thread.run(Unknown Source)
Cannot find input for 'ValueInputToken[parsed 'CLOBField = :xmlOrderBind', replaced 'CLOBField = :xmlOrderBind']' in bind bases.


Used code is:

 private String createSQLUpdateOrder() {
    return "UPDATE MyTable "
        + "SET CLOBField=:xmlOrderBind "
        + "WHERE OrderId=:idCommBind)";
  }

  @Override
  public boolean saveOrderInformation(String xmlOrder1, String commission, String idComm) {
    int retval = 0;

    try {
      retval = SERVICES.getService(EDICCSqlService.class).update(
          createSQLUpdateOrder(),
          new NVPair("xmlOrderBind", xmlOrder1),
          new NVPair("idCommBind", idComm)
          );
    }
    catch (ProcessingException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      System.out.println(e.getMessage());
      retval = -1;
    }
    catch (InvalidSyntaxException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      System.out.println(e.getMessage());
      retval = -1;
    }
}

[Updated on: Tue, 16 April 2013 14:11]

Report message to a moderator

Re: Update a CLOB field in a table [message #1042547 is a reply to message #1042506] Tue, 16 April 2013 15:01 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 698
Registered: October 2011
Senior Member
What are you import for NVPair?

It should be:
org.eclipse.scout.commons.holders.NVPair
Re: Update a CLOB field in a table [message #1042551 is a reply to message #1042547] Tue, 16 April 2013 15:06 Go to previous message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Hi,
I imported "oracle.net.jdbc.nl.NVPair", here is the error.

Now it works. Thanks

[Updated on: Tue, 16 April 2013 15:28]

Report message to a moderator

Previous Topic:Scout 3.9 nightly build
Next Topic:Modular Application
Goto Forum:
  


Current Time: Thu Sep 18 01:41:14 GMT 2014

Powered by FUDForum. Page generated in 0.03090 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software