Обсуждение: Re: int types migrated one level lower

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

Re: int types migrated one level lower

От
"Dave Page"
Дата:
Hi,
I do not have the source to hand, so please forgive any errors. Comments below are prefixed with DP

_____________Original message ____________
Subject:    [pgadmin-support] int types migrated one level lower
Sender:    "Reshat Sabiq" <sabiq@purdue.edu>
Date:        Thu, 26 Dec 2002 15:05:37 +0000

My integer types migrated from Access one level lower than is shown in
the Map in the wizard. Thus,
integer became int2 instead int4

Also, long integer became int4 instead int8, although long integer
wasn't in the map so that probably doesn't count formally.

DP: pgAdmin doesn't see the data types you see in Access, it sees the ADO datatypes listed on the type map dialogue.
Thedifferences   you are seeing are almost certainly because ADO is not mapping the types exactly as you expect. 

At the same
time, byte became int2, but apparently that is the only possible way
because I'm not seeing an 8-bit integer type in postgres (please let me
know if there is one).

DP: Isn't there byte in pg? I know there is bytea (array of byte).

P.S. I'd like to suggest enhancements for pgAdmin3:
1) getting integer to map into int4

DP: I don't have any intention of re-writing the migration wizard for pgAdmin 3 at the moment. If I did, the type
mappingwould be definable as it is now, but from ODBC types, not ADO. 

2) adding a source type 'long integer' with a mapping to int8.
In the long run, it would also be nice to have an 8-bit integer type if
there isn't one yet.

DP: We don't define the source types, and the target types come straight from PostgreSQL.

Regards, Dave.

Re: int types migrated one level lower

От
"Reshat Sabiq"
Дата:
DP: Isn't there byte in pg? I know there is bytea (array of byte).

Yes, I've seen bytea, but it's 4+ bytes. I'm not aware of an 8-bit type.

DP: I don't have any intention of re-writing the migration wizard for
pgAdmin 3 at the moment. If I did, the type mapping would be definable
as it is now, but from ODBC types, not ADO.

I'd guess ODBC would be more reliable for Windows-based DBs, since ADO
apparently converts int8 into int4 and int4 into int2 (I don't think MS
Access considers long integer to be int4, and integer to be int2).

Thanks,
r.

Re: int types migrated one level lower

От
"Reshat Sabiq"
Дата:
Actually, I though about it a little, and I am now in doubt about how MS
Access stores integer types internally.

Since there is no "short" type, the consecutive values could potentially
go as follows:
byte - 8 bits
integer - 16 bits
long integer - 32 bits
If that is the case, then integer from Access maps to int2, and long one
maps to int4. That would be a very bad mapping on behalf of MS.

I'd appreciate any feedback on this issue. I'm currently planning to
change my Access ints into long ints, and long ints apparently will have
to stay put.

Sincerely,
r.

-----Original Message-----
From: pgadmin-support-owner@postgresql.org
[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Reshat Sabiq
Sent: Thursday, December 26, 2002 9:12 AM
To: 'Dave Page'; pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] int types migrated one level lower

I'd guess ODBC would be more reliable for Windows-based DBs, since ADO
apparently converts int8 into int4 and int4 into int2 (I don't think MS
Access considers long integer to be int4, and integer to be int2).

Thanks,
r.

Re: int types migrated one level lower

От
"Reshat Sabiq"
Дата:
I was right in last message: I just checked that in Access docs. A
little surprise from MS: 16-bit int, and 32 bit long.

P.S. That means there's nothing wrong with ADO.

Sincerely,
r.

-----Original Message-----
From: pgadmin-support-owner@postgresql.org
[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Reshat Sabiq
Sent: Sunday, December 29, 2002 5:41 AM
To: 'Dave Page'; pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] int types migrated one level lower

Actually, I though about it a little, and I am now in doubt about how MS
Access stores integer types internally.

Since there is no "short" type, the consecutive values could potentially
go as follows:
byte - 8 bits
integer - 16 bits
long integer - 32 bits
If that is the case, then integer from Access maps to int2, and long one
maps to int4. That would be a very bad mapping on behalf of MS.

I'd appreciate any feedback on this issue. I'm currently planning to
change my Access ints into long ints, and long ints apparently will have
to stay put.

Sincerely,
r.

-----Original Message-----
From: pgadmin-support-owner@postgresql.org
[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Reshat Sabiq
Sent: Thursday, December 26, 2002 9:12 AM
To: 'Dave Page'; pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] int types migrated one level lower

I'd guess ODBC would be more reliable for Windows-based DBs, since ADO
apparently converts int8 into int4 and int4 into int2 (I don't think MS
Access considers long integer to be int4, and integer to be int2).

Thanks,
r.




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: int types migrated one level lower

От
"Dave Page"
Дата:

> -----Original Message-----
> From: Reshat Sabiq [mailto:sabiq@purdue.edu]
> Sent: 29 December 2002 13:54
> To: Dave Page; pgadmin-support@postgresql.org
> Subject: RE: [pgadmin-support] int types migrated one level lower
>
>
> I was right in last message: I just checked that in Access
> docs. A little surprise from MS: 16-bit int, and 32 bit long.
>
> P.S. That means there's nothing wrong with ADO.

Not really as Access and ADO are not the same thing. I've checked the
pgAdmin code, and as I said, all it does is to map the ADO-presented
datatype to the PostgreSQL datatype selected in the type map. The code
sample below shows the default vaues (if you read it right - the Case
line is the ADO type without the 'ad' prefix, and the Temp = line is the
default PostgreSQL type):

      Case "BigInt"
        Temp = "int8"
      Case "Binary"
        Temp = "text"
      Case "Boolean"
        Temp = "bool"
      Case "BSTR"
        Temp = "bytea"
      Case "Chapter"
        Temp = "int4"
      Case "Char"
        Temp = "char"
      Case "Currency"
        Temp = "money"
      Case "Date"
        Temp = "date"
      Case "DBDate"
        Temp = "date"
      Case "DBTime"
        Temp = "time"
      Case "DBTimestamp"
        Temp = "timestamp"
      Case "Decimal"
        Temp = "numeric"
      Case "Numeric"       ' AM 20020110  Added adNumeric
        Temp = "numeric"
      Case "Double"
        Temp = "float8"
      Case "Empty"
        Temp = "text"
      Case "Error"
        Temp = "int4"
      Case "FileTime"
        Temp = "timestamp"
      Case "GUID"
        Temp = "text"
      Case "Integer"
        Temp = "int4"
      Case "LongVarBinary"
        Temp = "lo"
      Case "LongVarChar"
        Temp = "text"
      Case "LongVarWChar"
        Temp = "text"
       Case "PropVariant"
        Temp = "text"
       Case "Single"
        Temp = "float4"
       Case "SmallInt"
        Temp = "int2"
       Case "TinyInt"
        Temp = "int2"
       Case "UnsignedBigInt"
        Temp = "int8"
       Case "UnsignedInt"
        Temp = "int4"
       Case "UnsignedSmallInt"
        Temp = "int2"
       Case "UnsignedTinyInt"
        Temp = "int2"
       Case "UserDefined"
        Temp = "text"
       Case "VarBinary"
        Temp = "lo"
       Case "VarChar"
        '1/16/2001 Rod Childers
        'Changed VarChar to default to VarChar
        'Text in Access is = VarChar in PostgreSQL
        'Memo in Access is = text in PostgreSQL
        'Temp = "text"
        Temp = "varchar"
       Case "VarWChar"
          '1/16/2001 Rod Childers
          'Changed VarWChar to default to VarChar
          'Text in Access is = VarChar in PostgreSQL
          'Memo in Access is = text in PostgreSQL
         Temp = "varchar"
       Case "WVar"
        Temp = "text"

What version of Access are you using? Can you send me a schema-only MDB
file to play with?

Regards, Dave.

Re: int types migrated one level lower

От
"Reshat Sabiq"
Дата:
What I meant to say is that there is nothing wrong with either ADO's, or
pgAdmin's mappings. The problem arises from the fact that MS Access has
a "downward-revised" mapping of integer types:
byte - 8 bits (this one is fine)
integer - 16 bits (i.e., int2, but normally should be int4)
long integer - 32 bits (i.e., int4, but normally should be int8)

So to get int4 as output from ADO going into postgres, Access type has
to be long integer, whereas getting int8 appears to be impossible
(unless manually) because Access does not have a 64-bit (8-byte) integer
type.

P.S. I'm using Access 2002, but I believe it's the same for all of them.
I could send the schema, but I don't know how to extract it from Access.
However, it is unnecessary, you could just look up the FieldSize Help
docs: the mappings are shown there.

P.P.S. So I converted all ints to long ints, because otherwise instead
of capacity of 2.something billion, I'd have a capacity of 32.something
thousand. And that would apply to Access itself, not just the migration.

Sincerely,
r.

-----Original Message-----
From: pgadmin-support-owner@postgresql.org
[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Dave Page
Sent: Sunday, December 29, 2002 3:27 PM
To: Reshat Sabiq; pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] int types migrated one level lower



> -----Original Message-----
> From: Reshat Sabiq [mailto:sabiq@purdue.edu]
> Sent: 29 December 2002 13:54
> To: Dave Page; pgadmin-support@postgresql.org
> Subject: RE: [pgadmin-support] int types migrated one level lower
>
>
> I was right in last message: I just checked that in Access
> docs. A little surprise from MS: 16-bit int, and 32 bit long.
>
> P.S. That means there's nothing wrong with ADO.

Not really as Access and ADO are not the same thing. I've checked the
pgAdmin code, and as I said, all it does is to map the ADO-presented
datatype to the PostgreSQL datatype selected in the type map. The code
sample below shows the default vaues (if you read it right - the Case
line is the ADO type without the 'ad' prefix, and the Temp = line is the
default PostgreSQL type):

      Case "BigInt"
        Temp = "int8"
      Case "Binary"
        Temp = "text"
      Case "Boolean"
        Temp = "bool"
      Case "BSTR"
        Temp = "bytea"
      Case "Chapter"
        Temp = "int4"
      Case "Char"
        Temp = "char"
      Case "Currency"
        Temp = "money"
      Case "Date"
        Temp = "date"
      Case "DBDate"
        Temp = "date"
      Case "DBTime"
        Temp = "time"
      Case "DBTimestamp"
        Temp = "timestamp"
      Case "Decimal"
        Temp = "numeric"
      Case "Numeric"       ' AM 20020110  Added adNumeric
        Temp = "numeric"
      Case "Double"
        Temp = "float8"
      Case "Empty"
        Temp = "text"
      Case "Error"
        Temp = "int4"
      Case "FileTime"
        Temp = "timestamp"
      Case "GUID"
        Temp = "text"
      Case "Integer"
        Temp = "int4"
      Case "LongVarBinary"
        Temp = "lo"
      Case "LongVarChar"
        Temp = "text"
      Case "LongVarWChar"
        Temp = "text"
       Case "PropVariant"
        Temp = "text"
       Case "Single"
        Temp = "float4"
       Case "SmallInt"
        Temp = "int2"
       Case "TinyInt"
        Temp = "int2"
       Case "UnsignedBigInt"
        Temp = "int8"
       Case "UnsignedInt"
        Temp = "int4"
       Case "UnsignedSmallInt"
        Temp = "int2"
       Case "UnsignedTinyInt"
        Temp = "int2"
       Case "UserDefined"
        Temp = "text"
       Case "VarBinary"
        Temp = "lo"
       Case "VarChar"
        '1/16/2001 Rod Childers
        'Changed VarChar to default to VarChar
        'Text in Access is = VarChar in PostgreSQL
        'Memo in Access is = text in PostgreSQL
        'Temp = "text"
        Temp = "varchar"
       Case "VarWChar"
          '1/16/2001 Rod Childers
          'Changed VarWChar to default to VarChar
          'Text in Access is = VarChar in PostgreSQL
          'Memo in Access is = text in PostgreSQL
         Temp = "varchar"
       Case "WVar"
        Temp = "text"

What version of Access are you using? Can you send me a schema-only MDB
file to play with?

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 3: 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: int types migrated one level lower

От
"Dave Page"
Дата:

> -----Original Message-----
> From: Reshat Sabiq [mailto:sabiq@purdue.edu]
> Sent: 29 December 2002 21:34
> To: Dave Page; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] int types migrated one level lower
>
>
> What I meant to say is that there is nothing wrong with
> either ADO's, or pgAdmin's mappings. The problem arises from
> the fact that MS Access has a "downward-revised" mapping of
> integer types: byte - 8 bits (this one is fine) integer - 16
> bits (i.e., int2, but normally should be int4) long integer -
> 32 bits (i.e., int4, but normally should be int8)

Ahh, I see. Thanks for clarifying what you meant.

Regards, Dave.