Обсуждение: Postgresql ODBC Truncates Timestamp second fractions

Поиск
Список
Период
Сортировка

Postgresql ODBC Truncates Timestamp second fractions

От
pg_gg@mailinator.com
Дата:
<p>Hi<p>We are trying to replicate data from an Oracle database to PG 9.3 (tried 9.2 as well, but that doesn't really
makea difference) on Windows 2008R2 64-bit using Oracle GoldenGate (OGG), but running into an issue with the ODBC
driverpsqlodbc-09.03.0210. The problem is that timestamp values coming from Oracle are truncated and mili and micro
seconddata is lost. For example if data is '2014-04-05 11:12:13.123456000' what is insertedin the PG table is
'2014-04-0511:12:13', even when the field is defined as timestamp or timestamp (6). We logged the queries and it
appearsthat the parameter that is prepared is already truncated to seconds only. We only see this behaviour with OGG,
andnot through other methods of inserting data using the same ODBC connection. However, the ODBC driver that ships with
OGG,which is from DataDirect, doesn't have this problem and passes the timestamp values appropriately. However we
cannotuse that driver, as it doesn't handle unicode null character, i.e. 0x00, which the official PG ODBC driver
somehowhandles. So basically none of the drivers work properly, but each has it's own separate issue. We are trying to
figureout where in the psqlodbc codes the preparation and conversion of parameters happen to find out why the timestamp
valuesare truncated if the data is coming from OGG and not truncated from other mechansims. Any help with this regard
isgreatly appreciated.<p>Thank you<br /> A. 

Re: Postgresql ODBC Truncates Timestamp second fractions

От
Adrian Klaver
Дата:
On 04/05/2014 11:50 AM, pg_gg@mailinator.com wrote:
> Hi
>
> We are trying to replicate data from an Oracle database to PG 9.3 (tried
> 9.2 as well, but that doesn't really make a difference) on Windows
> 2008R2 64-bit using Oracle GoldenGate (OGG), but running into an issue
> with the ODBC driver psqlodbc-09.03.0210. The problem is that timestamp
> values coming from Oracle are truncated and mili and micro second data
> is lost. For example if data is '2014-04-05 11:12:13.123456000' what is
> insertedin the PG table is '2014-04-05 11:12:13', even when the field is
> defined as timestamp or timestamp (6). We logged the queries and it
> appears that the parameter that is prepared is already truncated to
> seconds only. We only see this behaviour with OGG, and not through other
> methods of inserting data using the same ODBC connection. However, the
> ODBC driver that ships with OGG, which is from DataDirect, doesn't have
> this problem and passes the timestamp values appropriately. However we
> cannot use that driver, as it doesn't handle unicode null character,
> i.e. 0x00, which the official PG ODBC driver somehow handles. So
> basically none of the drivers work properly, but each has it's own
> separate issue. We are trying to figure out where in the psqlodbc codes
> the preparation and conversion of parameters happen to find out why the
> timestamp values are truncated if the data is coming from OGG and not
> truncated from other mechansims. Any help with this regard is greatly
> appreciated.

Would seem this is coming from the Oracle side of things, given that you
say psqlodbc does the right thing when not receiving data from OGC. Also
that the parameter is already truncated. That indicates to me the Oracle
conversion is doing the pruning before passing it to psqlodbc.

My guess is the DataDirect driver is using a setting to do the right
thing. Confirmed I believe by this:

http://knowledgebase.datadirect.com/articles/Article/8788

>
> Thank you
> A.
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Postgresql ODBC Truncates Timestamp second fractions

От
pg_gg@mailinator.com
Дата:
<p>I don't think the problem is only with the OGG. My understanding is that OGG polls the destination metadata first
andthen describes and prepares the parameters and the SQL statement based on the metadata. Either OGG interprets what
itreceives from PG differently than other tools, or the driver behaves differently for OGG, and that's what we are
tryingto understand based on the code. Just don't know where to look.<p>Also the DataDirect ODBC only handles
timestampswhen it's defined without a percision in which case the metadata query returns -1 for the field lenght. If
precisionis defined, even if it's 6 then it fails with the error mentioned in the link below and we would need to set
theWorkArounds2=2. But that doesn't solve the problem because then it nags about data overflowing. So the only way to
makethe Data Driect drive to work is to not define the precision, but as I mentioned before, that driver doesn't handle
Unicodenull character, so we can't really use it.<p> <p> <p>On 2014-04-05 23:55:08 adrian.klaver@aklaver.com wrote:<br
/>> On 04/05/2014 11:50 AM, pg_gg@mailinator.com wrote:<br /> > > Hi<br /> > ><br /> > > We are
tryingto replicate data from an Oracle database to PG 9.3 (tried<br /> > > 9.2 as well, but that doesn't really
makea difference) on Windows<br /> > > 2008R2 64-bit using Oracle GoldenGate (OGG), but running into an issue<br
/>> > with the ODBC driver psqlodbc-09.03.0210. The problem is that timestamp<br /> > > values coming from
Oracleare truncated and mili and micro second data<br /> > > is lost. For example if data is '2014-04-05
11:12:13.123456000'what is<br /> > > insertedin the PG table is '2014-04-05 11:12:13', even when the field is<br
/>> > defined as timestamp or timestamp (6). We logged the queries and it<br /> > > appears that the
parameterthat is prepared is already truncated to<br /> > > seconds only. We only see this behaviour with OGG,
andnot through other<br /> > > methods of inserting data using the same ODBC connection. However, the<br /> >
>ODBC driver that ships with OGG, which is from DataDirect, doesn't have<br /> > > this problem and passes the
timestampvalues appropriately. However we<br /> > > cannot use that driver, as it doesn't handle unicode null
character,<br/> > > i.e. 0x00, which the official PG ODBC driver somehow handles. So<br /> > > basically
noneof the drivers work properly, but each has it's own<br /> > > separate issue. We are trying to figure out
wherein the psqlodbc codes<br /> > > the preparation and conversion of parameters happen to find out why the<br
/>> > timestamp values are truncated if the data is coming from OGG and not<br /> > > truncated from other
mechansims.Any help with this regard is greatly<br /> > > appreciated.<br /> ><br /> > Would seem this is
comingfrom the Oracle side of things, given that you<br /> > say psqlodbc does the right thing when not receiving
datafrom OGC. Also<br /> > that the parameter is already truncated. That indicates to me the Oracle<br /> >
conversionis doing the pruning before passing it to psqlodbc.<br /> ><br /> > My guess is the DataDirect driver
isusing a setting to do the right<br /> > thing. Confirmed I believe by this:<br /> ><br /> >
http://knowledgebase.datadirect.com/articles/Article/8788<br/> ><br /> > ><br /> > > Thank you<br />
>> A.<br /> > ><br /> ><br /> ><br /> > --<br /> > Adrian Klaver<br /> >
adrian.klaver@aklaver.com

Re: Postgresql ODBC Truncates Timestamp second fractions

От
Adrian Klaver
Дата:
On 04/05/2014 05:15 PM, pg_gg@mailinator.com wrote:
> I don't think the problem is only with the OGG. My understanding is that
> OGG polls the destination metadata first and then describes and prepares
> the parameters and the SQL statement based on the metadata. Either OGG
> interprets what it receives from PG differently than other tools, or the
> driver behaves differently for OGG, and that's what we are trying to
> understand based on the code. Just don't know where to look.

I would say, start with Oracle, it is their product. I am going to
assume support is part of the package. or am I wrong?

There is some evidence it does treat things differently:

http://docs.oracle.com/cd/E35209_01/doc.1121/e29642.pdf

"timestamptz data type
PostgreSQL
timestamp with timezone
column type is recognized as
SQL_VARCHAR
and therefore
Oracle GoldenGate writes the data in the native
format of the source
database, rather than
normalizing it to its PostgreSQL form. As a
result, some replicated timestamp data might
not be compatible with Oracle GoldenGate column-conversion functions and
FILTER
clauses."

>
> Also the DataDirect ODBC only handles timestamps when it's defined
> without a percision in which case the metadata query returns -1 for the
> field lenght. If precision is defined, even if it's 6 then it fails with
> the error mentioned in the link below and we would need to set the
> WorkArounds2=2. But that doesn't solve the problem because then it nags
> about data overflowing. So the only way to make the Data Driect drive to
> work is to not define the precision, but as I mentioned before, that
> driver doesn't handle Unicode null character, so we can't really use it.
>



--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Postgresql ODBC Truncates Timestamp second fractions

От
pg_gg@mailinator.com
Дата:
<p>We have started with Oracle, but most probably they won't do much about it as their ODBC driver handles timestamps
properly,but not the Unicode null character, which they have documented as a limitation, so it's hard to press them on
that.<p>Wewere hoping to make it work by using the official PG ODBC driver, and this driver although handles the
problemof null character, has the problem with timestamps.<p>Unfortunately I don't think I'm getting relevant answer to
myquestion which is where we should look into the code to find out how the PG ODBC driver handles paramter conversion
andspecifically SQLDescribeParam and SQLBindParameter funnction calls.<p>Thanks anyway for your help.<p>On 04/05/2014
05:15PM, pg_gg@mailinator.com wrote:<br /> > I don't think the problem is only with the OGG. My understanding is
that<br/> > OGG polls the destination metadata first and then describes and prepares<br /> > the parameters and
theSQL statement based on the metadata. Either OGG<br /> > interprets what it receives from PG differently than
othertools, or the<br /> > driver behaves differently for OGG, and that's what we are trying to<br /> >
understandbased on the code. Just don't know where to look.<br /><br /> I would say, start with Oracle, it is their
product.I am going to<br /> assume support is part of the package. or am I wrong?<br /><br /> There is some evidence it
doestreat things differently:<br /><br /><a href="http://docs.oracle.com/cd/E35209_01/doc.1121/e29642.pdf"
target="_other">http://docs.oracle.com/cd/E35209_01/doc.1121/e29642.pdf</a><br/><br /> "timestamptz data type<br />
PostgreSQL<br/> timestamp with timezone<br /> column type is recognized as<br /> SQL_VARCHAR<br /> and therefore<br />
OracleGoldenGate writes the data in the native<br /> format of the source<br /> database, rather than<br /> normalizing
itto its PostgreSQL form. As a<br /> result, some replicated timestamp data might<br /> not be compatible with Oracle
GoldenGatecolumn-conversion functions and<br /> FILTER<br /> clauses."<br /><br /> ><br /> > Also the DataDirect
ODBConly handles timestamps when it's defined<br /> > without a percision in which case the metadata query returns
-1for the<br /> > field lenght. If precision is defined, even if it's 6 then it fails with<br /> > the error
mentionedin the link below and we would need to set the<br /> > WorkArounds2=2. But that doesn't solve the problem
becausethen it nags<br /> > about data overflowing. So the only way to make the Data Driect drive to<br /> > work
isto not define the precision, but as I mentioned before, that<br /> > driver doesn't handle Unicode null character,
sowe can't really use it.<br /> ><br /><br /><br /><br /> --<br /> Adrian Klaver<br /> adrian.klaver@aklaver.com 

Re: Postgresql ODBC Truncates Timestamp second fractions

От
Adrian Klaver
Дата:
On 04/05/2014 09:37 PM, pg_gg@mailinator.com wrote:
> We have started with Oracle, but most probably they won't do much about
> it as their ODBC driver handles timestamps properly, but not the Unicode
> null character, which they have documented as a limitation, so it's hard
> to press them on that.
>
> We were hoping to make it work by using the official PG ODBC driver, and
> this driver although handles the problem of null character, has the
> problem with timestamps.
>
> Unfortunately I don't think I'm getting relevant answer to my question
> which is where we should look into the code to find out how the PG ODBC
> driver handles paramter conversion and specifically SQLDescribeParam and
> SQLBindParameter funnction calls.

Well that is easy enough.

Download the source and

grep -E 'SQLDescribeParam|SQLBindParameter' *

shows they are defined in

odbcapi.c

>



--
Adrian Klaver
adrian.klaver@aklaver.com