Re: Postgresql ODBC Truncates Timestamp second fractions - Mailing list pgsql-odbc

<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