Thread: Re: int types migrated one level lower
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.
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.
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.
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
> -----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.
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
> -----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.