R: timestamp parse error --- *** Migration PLUG-IN *** - Mailing list pgadmin-support

Dave,

I still have problems with a database generated from sql server 2000. I
cannot inser data with pgadmin II ( and with the migration plugin ) to
timestamps fields. This error was generated :
----------------------------------------
28/03/2002 10.26.41 - Error in pgAdmin
II:frmSQLOutput.cmdSave_Click: -2147467259 - Error while executing the
query; ERROR:  Bad timestamp external representation '2002-10-10 00.00.00'
--------------------------------------

In this case i've tried to modify a date already inserted with Coldfusion
( inserted as odbctimedate ). I can see the date inside the pgadmin modify
form
as 10/10/2002 but I cannot modify this . For me it's a bit strange, can you
help me ? I think that if we solve this poroblem I can also migrate
correctly windows 2000 datas with the plugin.

Bye and thanks in advance.

    Samuele.

-----Messaggio originale-----
Da: Dave Page [mailto:dpage@vale-housing.co.uk]
Inviato: giovedì 21 marzo 2002 9.35
A: 'samuele.brignoli@darpha.com'; pgadmin-support@postgresql.org;
Henshall, Stuart - WCP
Oggetto: RE: [pgadmin-support] timestamp parse error --- *** Migration
PLUG-IN ***




> -----Original Message-----
> From: Samuele Brignoli [mailto:samuele.brignoli@darpha.com]
> Sent: 21 March 2002 08:21
> To: Dave Page; pgadmin-support@postgresql.org
> Subject: R: [pgadmin-support] timestamp parse error --- ***
> Migration PLUG-IN ***
>
>
> Yes, but the problem is with database migration plugin.

Yes, that is what I'm referring to when I say pgAdmin. I look at the whole
thing.

> It
> seems that this component extract data from sql server to the
> format 00.00.00 which is not correct.

The Migration Wizard uses the settings of your ODBC datasource/Access MDB
file/SQL Server OLEDB datasource, which in turn probably uses your system's
locale settings.

> This tool ( I don' t
> know why and how ) try, at the end, to insert the timestamp
> as 00.00.00 which generates an error.

It uses a Microsoft function to convert the date from adDBTimestamp (that's
a Microsoft ADO data type) to ISO 8601 format (yyyy-MM-dd hh:mm:ss) which is
recognised by PostgreSQL *no matter what the locale*. The only way I can see
that it doesn't do this conversion, is if Microsoft's ADOX does not
recognise the source column as being adDBTimestamp, in which case (with a
few exceptions) the Migration Wizard will not change it in any way, and will
insert the data as it was supplied by the souce database.

> I' ve talked to
> postgresql specialist that told me that this dot notation (
> 00.00.00 ) is not supported by postgresql.

Yes, I told you that some time ago.

> Where does it come
> from ? I don' t think It's a particular configuration of my
> sql server.
>
> Surely a simple check in the Migration plug-in, which manage
> this conversion, can solve the problem.

There already is one. From the CVS:
(http://cvs.pgadmin.org/cgi-bin/viewcvs.cgi/pgadmin2/plugins/migration/frmWi
zard.frm?rev=1.8&content-type=text/vnd.viewcvs-markup&logsort=rev)

'We used to have a bit of a hack here, but if the recordset thinks it has a
valid date
'then it must do. Format to ISO (8601? - I can never remember!) will
automatically add
'1899-12-30 if only a time is actually in the string
Case adDate, adDBDate, adDBTimeStamp
  Values = Values & "'" & Format(rsTemp.Fields(Z).Value, "yyyy-MM-dd
hh:mm:ss") & "', "

Note that this is from the version of the file that pgAdmin 1.2.0 was built
with, not a current fixed version.

Regards, Dave.