Problem with Postgres SQL
Hello,
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.
But...
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.
Micropole.