Re: 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)

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(at)postgresql(dot)org
[mailto:pgadmin-support-owner(at)postgresql(dot)org] On Behalf Of Dave Page
Sent: Sunday, December 29, 2002 3:27 PM
To: Reshat Sabiq; pgadmin-support(at)postgresql(dot)org
Subject: Re: [pgadmin-support] int types migrated one level lower

> -----Original Message-----
> From: Reshat Sabiq [mailto:sabiq(at)purdue(dot)edu]
> Sent: 29 December 2002 13:54
> To: Dave Page; pgadmin-support(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly