8 Replies Latest reply on Jun 25, 2010 8:40 PM by 778169

    Problem with Postgres SQL


      I need to extract data from Postrgres SQL 7.3 and to load it into an Oracle table.

      I have seen that ODI have a driver for Postgres SQL so I have begin to define my Topology.
      Ihave created my Data Server and then a Physical and Logical schema to my BD.
      The connexion test is succesfull.

      I open the designer and then create a model where I reverse my tables.
      Until here all work fine.

      When I try to view my data I have an error message:
      "ERROR: syntax error at or near "%" "
      and when I look to the SQL generated in the viewer I see :
      "select * from %DATABASE.public.stations "

      So I have change into the Physical Schema, the Local Object Mask and the Remote Object Mask, replacing %DATABASE with the name of my DB.

      this is not really great but it works, i can view my data.
      The problem is after.

      I create an Interface which have in Source one table Postgres SQL and an Oracle table in target, with a simple mapping.

      I'm using an LKM SQL to Oracle (or LKM SQL to SQL) and an IKM SQL Control Append.
      My bug appear in the 3rd step of the LKM in the "Load Data".
      Before the Drop and the Create Work Table are executed well.

      But the Load raise an error which is :
      0 : 42601 : org.postgresql.util.PSQLException: ERROR: syntax error at or near "SAVEPOINT"
      org.postgresql.util.PSQLException: ERROR: syntax error at or near "SAVEPOINT"
           at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
           at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
           at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
           at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
           at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
           at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329)
           at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execCollOrders(SnpSessTaskSql.java)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
           at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
           at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
           at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
           at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
           at com.sunopsis.dwg.cmd.e.i(e.java)
           at com.sunopsis.dwg.cmd.g.y(g.java)
           at com.sunopsis.dwg.cmd.e.run(e.java)
           at java.lang.Thread.run(Thread.java:595)

      We think that is about the driver, so we have tested a few one that we have downloaded from the Postgres Site but it's always the same bug...

      Does someone have ever seen this message ?
      Do yo know what to do ??

      I'm supposed to perform this loading in express so all help is welcome.

      Thanks in advance.

        • 1. Re: Problem with Postgres SQL
          I'm answering myself cause the problem is solved.

          Postgres 7.3 doesn't accept savepoints which was added on the 7.4.
          So we must use a driver for the 7.3.

          Moreover we have customized the LKM which was used cause in 7.3 Manual Commit are not supported so we have set Autocommit in the source select.
          • 2. Re: Problem with Postgres SQL
            Hello Micropole,

            1) I don't thing that ODI provide a driver for Postgres SQL. But this is not important.

            2) do you correctly set the Physical schema (CATALOG+SCHEMA)?
            Could you please send us the query in the source tab?

            • 3. Re: Problem with Postgres SQL
              Yes I have seen in the Knowledge Base for the %Catalog that I have change now.

              And I was thinking for the driver cause it was present in the list box for the data server in Topology.
              But how I have imported another one in ../Drivers and how I have inserted it in ODIParamsI now have a doubt...

              For my source query it was a really easy one :
                   STATIONS.stat_id     AS C1_ID_TMEASUREMENTSTATION
              from     gsm.public.stations AS STATIONS
              where     (1=1)"

              And in the LKM :
              "select     <%=snpRef.getPop("DISTINCT_ROWS")%>
                   <%=snpRef.getColList("", "[EXPRESSION]\t[ALIAS_SEP] [CX_COL_NAME]", ",\n\t", "", "")%>
              from     <%=snpRef.getFrom()%>
              where     (1=1)

              I only add the Autocommit in the Source tab.

              But now all seems to be well...

              I have a question for you as hold you,
              I cannot create new SR on Oracle SR site.
              On the SR Create page it says me "NO VALID CSI"
              Can you explain me how to resolve this if you know...
              Note that I have ever create SR.

              Thanks a lot.

              • 4. Re: Problem with Postgres SQL
                in order to open a SR you must be an Oracle Customer or partner, you should have then a CSI : Customer Support Identifier...
                what is the name of your company?
                • 5. Re: Problem with Postgres SQL
                  Thanks for your answer.

                  We are Oracle partner. My company is nammed Micropole Univers and I'm logging on the SR site with information of the partner account.
                  I was able to create SR few months ago but not now...

                  What can I do ???
                  • 6. Re: Problem with Postgres SQL
                    Partners are not allowed to create SRs. Only final customers are allowed to. Partners can open SRs in the name of their final customers but they need to use the final customer's CSI (Customer Support Identifier). Do you have an email?
                    • 7. Re: Problem with Postgres SQL
                      Yes I have one (:


                      So the client is City of Lausanne (Switzerland), but I don't know if they have already create a SR ??
                      • 8. Re: Problem with Postgres SQL

                        Facing the same error

                        0 : 42601 : org.postgresql.util.PSQLException: ERROR: syntax error at or near "SAVEPOINT"
                        org.postgresql.util.PSQLException: ERROR: syntax error at or near "SAVEPOINT"

                        The postgres versrion is 7.4.19 and JDBC driver postgresql-8.4-701.jdbc3.jar .

                        I can view the data in ODI but interface fails.

                        How did you fix it ? What did you mean by "I only add the Autocommit in the Source tab" ?