Re: Postgresql ODBC Truncates Timestamp second fractions

<p>We have started with Oracle, but most probably they won&#39;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&#39;s hard to press them on that.</p>

<p>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.</p>

<p>Unfortunately I don&#39;t think I&#39;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.</p>

<p>Thanks anyway for your help.</p>

<p>On 04/05/2014 05:15 PM, pg_gg(at)mailinator(dot)com wrote:<br />
&gt; I don&#39;t think the problem is only with the OGG. My understanding is that<br />
&gt; OGG polls the destination metadata first and then describes and prepares<br />
&gt; the parameters and the SQL statement based on the metadata. Either OGG<br />
&gt; interprets what it receives from PG differently than other tools, or the<br />
&gt; driver behaves differently for OGG, and that&#39;s what we are trying to<br />
&gt; understand based on the code. Just don&#39;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 does treat 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 />
&quot;timestamptz data type<br />
PostgreSQL<br />
timestamp with timezone<br />
column type is recognized as<br />
SQL_VARCHAR<br />
and therefore<br />
Oracle GoldenGate writes the data in the native<br />
format of the source<br />
database, rather than<br />
normalizing it to its PostgreSQL form. As a<br />
result, some replicated timestamp data might<br />
not be compatible with Oracle GoldenGate column-conversion functions and<br />
FILTER<br />
clauses.&quot;<br />
<br />
&gt;<br />
&gt; Also the DataDirect ODBC only handles timestamps when it&#39;s defined<br />
&gt; without a percision in which case the metadata query returns -1 for the<br />
&gt; field lenght. If precision is defined, even if it&#39;s 6 then it fails with<br />
&gt; the error mentioned in the link below and we would need to set the<br />
&gt; WorkArounds2=2. But that doesn&#39;t solve the problem because then it nags<br />
&gt; about data overflowing. So the only way to make the Data Driect drive to<br />
&gt; work is to not define the precision, but as I mentioned before, that<br />
&gt; driver doesn&#39;t handle Unicode null character, so we can&#39;t really use it.<br />
&gt;<br />
<br />
<br />
<br />
--<br />
Adrian Klaver<br />
adrian(dot)klaver(at)aklaver(dot)com</p>