Problems saving sales orders (pgSQL/jharris)

  • Ashley Gittins

    Ashley Gittins - 2005-08-22

    Hi All,
      Using the 252 compiere tarball from jharris' site, I am getting failures trying to save sales orders. When using the application server, the client sits there waiting indefinitely. Not using the application server allows the client to look like it might have finished the "Completion" process, but it seems it has not.
    An SQL syntax error is logged to the console at the client, so I enabled statement tracing on postgres, which shows the following:

    LOG:  statement: UPDATE C_Invoice SET (PaymentRule,C_PaymentTerm_ID,DateAcct,C_Payment_ID,C_CashLine_ID)=(SELECT PaymentRule,C_PaymentTerm_ID,DateAcct,C_Payment_ID,C_CashLine_ID FROM C_Order o WHERE C_Invoice.C_Order_ID=o.C_Order_ID)WHERE DocStatus NOT IN ('RE','CL') AND C_Order_ID=1000004
    ERROR:  syntax error at or near "(" at character 22

    (sorry if there's a way to quote logs on sf forums, can't see any tips here).

    Running the same query directly in psql gives the same error, plus it nicely points out that the exact location it's complaining about is around the first "(" it encounters, after the SET.

    I am guessing that perhaps it's a call that hasn't been ported, but I'm a bit frazzled at the moment after getting pljava/postgres/java all working on this fedora box so may not be thinking clearly :-)

    Anyone have any ideas?

    • Wolfgang Oellinger

      The update statement is in oracle specific syntax.

      UPDATE t SET (c1, c2,...) =
          (SELECT e1, e2,... FROM ... WHERE ...)

      Postgres does not understand this, it only knows the syntax:

      UPDATE t SET c1 = e1, c2 = e2, ...
      FROM ...
      WHERE ...

      This kind of update is currently not transformed to postgres syntax, so any Compiere module which uses this will not work with Postgres.
      After greping and poking around in the sources I guess a  dozen occurences (don't rely on this, statements are dynamically build, so there may be more. Some classes may not be used, so there may be less).

      In my opinion the inability to change a sales order is a killer for Compiere/Postgres, I strongly disagree with Victor who claims that this ist not a big issue ( ).

      • Ashley Gittins

        Ashley Gittins - 2005-08-30

        Thanks Wolfgang - I had read that before but failed to make the connection!

        After three days I still can't come up with a response that doesn't come across as nasty or unappreciative toward other parties, so I'll leave it at saying that I agree with you.

        As it stands it looks like I'll be reluctantly going for an oracle install until such time as the pgsql port becomes a more transparent project. JHarris has made some good progress on that point as have some others, but I think momentum is an issue. I do plan to help out in that regard when I can make the time.



Log in to post a comment.