psqlODBC Unicode 8.01.02.00 Driver "Overflows" On MS-Access Dates

Hi Folks,

I'm trying to export a table from my MS-Access database to my postgresql
database. I downloaded and installed the 8.01.02.00 driver on my Win2k
system, created the datasource. Then when I try to export my table to the
datasource, I get a single-world message "overflow" and the export aborts.

I traced the cause of the overflow to a column that is in MS-Access's
date/time format.

Is this a known bug? Is there a workaround?
--
% Randy Yates % "I met someone who looks alot like you,
%% Fuquay-Varina, NC % she does the things you do,
%%% 919-577-9882 % but she is an IBM."
%%%% % 'Yours Truly, 2095', *Time*, ELO
http://home.earthlink.net/~yatescr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: psqlODBC Unicode 8.01.02.00 Driver "Overflows" On MS-Access

I had one of these, too.

The date wasn't correctly formated in the Access table.
It was something like 11/1/106 which should have been entered as
1/11/2006.

It was a wee bit unpleasant to actually locate the line within the table.
I limited the export to chunks by a numeric primary key. First 1000 then
gradually narrowing down to the bad record.
I'd be cute if the error message contained some form of identifier like
the primary key. On the other hand how should odbc know what column of
some SELECT is an identifier?


Randy Yates schrieb:
> Hi Folks,
>
> I'm trying to export a table from my MS-Access database to my postgresql
> database. I downloaded and installed the 8.01.02.00 driver on my Win2k
> system, created the datasource. Then when I try to export my table to the
> datasource, I get a single-world message "overflow" and the export aborts.
>
> I traced the cause of the overflow to a column that is in MS-Access's
> date/time format.
>
> Is this a known bug? Is there a workaround?
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: psqlODBC Unicode 8.01.02.00 Driver "Overflows" On MS-Access

maps.on@gmx.net (Andreas) writes:

> I had one of these, too.
>
> The date wasn't correctly formated in the Access table.
> It was something like 11/1/106 which should have been entered as
> 1/11/2006.

Yep! Mine was similar! Thank you, Andreas!

> It was a wee bit unpleasant to actually locate the line within the table.
> I limited the export to chunks by a numeric primary key. First 1000
> then gradually narrowing down to the bad record.
> I'd be cute if the error message contained some form of identifier
> like the primary key. On the other hand how should odbc know what
> column of some SELECT is an identifier?

It could certainly emit each row of the table and identify the offending
field.
--
% Randy Yates % "Rollin' and riding and slippin' and
%% Fuquay-Varina, NC % sliding, it's magic."
%%% 919-577-9882 %
%%%% % 'Living' Thing', *A New World Record*, ELO
http://home.earthlink.net/~yatescr

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: psqlODBC Unicode 8.01.02.00 Driver "Overflows" On MS-Access

Whenever I get these I write a tiny "stuff one record in and tell me if
it errors" iteration. Just leave that running and get on with something
else productive.

Tim Clarke

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org=20
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Andreas
> Sent: 15 March 2006 19:39
> To: Randy Yates
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] psqlODBC Unicode 8.01.02.00 Driver=20
> "Overflows" On MS-Access
>=20
>=20
> I had one of these, too.
>=20
> The date wasn't correctly formated in the Access table.
> It was something like 11/1/106 which should have been entered as=20
> 1/11/2006.
>=20
> It was a wee bit unpleasant to actually locate the line=20
> within the table.
> I limited the export to chunks by a numeric primary key.=20
> First 1000 then=20
> gradually narrowing down to the bad record.
> I'd be cute if the error message contained some form of=20
> identifier like=20
> the primary key. On the other hand how should odbc know what=20
> column of=20
> some SELECT is an identifier?

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: psqlODBC Unicode 8.01.02.00 Driver "Overflows" On MS-Access

Tim.Clarke@manifest.co.uk ("Tim Clarke") writes:

> Whenever I get these I write a tiny "stuff one record in and tell me if
> it errors" iteration. Just leave that running and get on with something
> else productive.

I think the point is, if the driver error messages were a little more
intelligent (including some context) to begin with, then you (and a
bazillion others) wouldn't have to take any extra time to begin with.
It would be an instant "Oh, I see the problem."

It might also be helpful if the driver converted all records it was
able to convert and not simply abort on the first record with an
error.
--
% Randy Yates % "Maybe one day I'll feel her cold embrace,
%% Fuquay-Varina, NC % and kiss her interface,
%%% 919-577-9882 % til then, I'll leave her alone."
%%%% % 'Yours Truly, 2095', *Time*, ELO
http://home.earthlink.net/~yatescr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: psqlODBC Unicode 8.01.02.00 Driver "Overflows" On MS-Access

Randy Yates wrote:
> Tim.Clarke@manifest.co.uk ("Tim Clarke") writes:
>
>
>>Whenever I get these I write a tiny "stuff one record in and tell me if
>>it errors" iteration. Just leave that running and get on with something
>>else productive.
>
>
> I think the point is, if the driver error messages were a little more
> intelligent (including some context) to begin with, then you (and a
> bazillion others) wouldn't have to take any extra time to begin with.
> It would be an instant "Oh, I see the problem."

As far as I see, it's MS-Access not the ODBC driver which rsports the
*Overflows*.

> It might also be helpful if the driver converted all records it was
> able to convert and not simply abort on the first record with an
> error.

Unfortunately the ODBC driver itself doesn't detect any error before
MS-Access calls SQLEndTran.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: psqlODBC Unicode 8.01.02.00 Driver "Overflows" On MS-Access

inoue@tpf.co.jp (Hiroshi Inoue) writes:

> Randy Yates wrote:
>> Tim.Clarke@manifest.co.uk ("Tim Clarke") writes:
>>
>>>Whenever I get these I write a tiny "stuff one record in and tell me if
>>>it errors" iteration. Just leave that running and get on with something
>>>else productive.
>> I think the point is, if the driver error messages were a little more
>> intelligent (including some context) to begin with, then you (and a
>> bazillion others) wouldn't have to take any extra time to begin
>> with. It would be an instant "Oh, I see the problem."
>
> As far as I see, it's MS-Access not the ODBC driver which rsports the
> *Overflows*.

How can you tell?

>> It might also be helpful if the driver converted all records it was
>> able to convert and not simply abort on the first record with an
>> error.
>
> Unfortunately the ODBC driver itself doesn't detect any error before
> MS-Access calls SQLEndTran.

I don't understand your statements. Is it not the driver that is performing
the conversions?

If it is MS, then of course that explains everything ...
--
% Randy Yates % "Bird, on the wing,
%% Fuquay-Varina, NC % goes floating by
%%% 919-577-9882 % but there's a teardrop in his eye..."
%%%% % 'One Summer Dream', *Face The Music*, ELO
http://home.earthlink.net/~yatescr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: psqlODBC Unicode 8.01.02.00 Driver "Overflows" On MS-Access

Randy Yates wrote:
> inoue@tpf.co.jp (Hiroshi Inoue) writes:
>
>
>>Randy Yates wrote:
>>
>>>Tim.Clarke@manifest.co.uk ("Tim Clarke") writes:
>>>
>>>
>>>>Whenever I get these I write a tiny "stuff one record in and tell me if
>>>>it errors" iteration. Just leave that running and get on with something
>>>>else productive.
>>>
>>>I think the point is, if the driver error messages were a little more
>>>intelligent (including some context) to begin with, then you (and a
>>>bazillion others) wouldn't have to take any extra time to begin
>>>with. It would be an instant "Oh, I see the problem."
>>
>>As far as I see, it's MS-Access not the ODBC driver which rsports the
>>*Overflows*.
>
>
> How can you tell?

I can reproduce a similar (not sure if exactly the same) case here.
When I run the 1 row test case with mylog output and ODBC trace on,
I find a SQLTransact(.., SQL_ROLLBACK) call suddenly after some
SQLBindParameter calls in the trace log without any surrounding errors.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly