Thomas A. Lowery wrote:
>
> On Wed, Oct 31, 2001 at 09:26:14AM +0100, Steffen Goeldner wrote:
> > Attached is *not* a patch - it's a first cut and a cry for help!
> > I have problems mapping the ADO fields to SQL/CLI (ODBC, DBI)
> > fields, especially the datatype related fields.
> > I guess that the magic DBD::ADO::_determine_type_support() may
> > provide part of a solution.
> > Any suggestions, hints, insides, patches, ... are welcome!
>
> > BTW: It seems, that the statement handle attributes need the
> > same mapping.
>
> Now that I'm looking at this again I remember why I skipped it ;->
>
> >
> > sub column_info {
>
> I'm beginning to remember why converting ADO to the SQL/ODBC was such a
> pain: Take a simple example of what OpenSchema(adSchemaProviderType)
---- !!!
> returns verse what the table column types return.
>
> The Provider supports:
>
> -> data type Short: 2
> -> data type Long: 3
> -> data type Single: 4
> -> data type Double: 5
> -> data type Currency: 6
> -> data type DateTime: 7
> -> data type Bit: 11
> -> data type Byte: 17
> -> data type GUID: 72
> -> data type BigBinary: 128
> -> data type LongBinary: 128
> -> data type VarBinary: 128
> -> data type LongText: 130
> -> data type VarChar: 130
> -> data type Decimal: 131
>
Looks like Microsoft.Jet.OLEDB.4.0?
> However, my table columns types are
> Number: 5 (adDouble)
> Text: 202 (adVarWChar)
> Number: 131 (adNumeric)
> Number: 131 (adNumeric)
> Date/Time: 7 (adDate)
> Memo: 203 (adLongVarWChar)
>
Can you list some provider properties, please?
My provider returns other results, see attachment.
> Type 5 is supported at mytest.pl line 36
> Type 202 is not supported at mytest.pl line 36
> Type 131 is supported at mytest.pl line 36
> Type 131 is supported at mytest.pl line 36
> Type 7 is supported at mytest.pl line 36
> Type 203 is not supported at mytest.pl line 36
>
> So the Provider does not supply any information on how to support
> data types adVarWChar and adLongVarWChar.
>
IMO, the provider is buggy! From the ADO docs:
adVarChar 200 Indicates a string value
(Parameter object only).
adVarWChar 202 Indicates a null-terminated Unicode character
string (Parameter object only).
adLongVarWChar 203 Indicates a long null-terminated Unicode string
value (Parameter object only).
Note: Parameter object only! Thus, a Field object should never
contain these datatypes.
Further, these types are no valid 'Type Indicators' for the
DATA_TYPE column of the COLUMNS Rowset.
> Converting from the an ADO type to the SQL/ODBC type, without support
> from the provider, may be difficult.
> We'll need to look at more then just the type in an attempt to map it.
>From the COLUMNS Rowset (adSchemaColumns), we'll need:
- DATA_TYPE !
- IS_NULLABLE ? see COLUMN_FLAGS
- COLUMN_FLAGS ! not all flags, but:
- DBCOLUMNFLAGS_ISFIXEDLENGTH !
- DBCOLUMNFLAGS_ISLONG !
- DBCOLUMNFLAGS_ISNULLABLE ? see IS_NULLABLE
- DBCOLUMNFLAGS_MAYBENULL ? see DBCOLUMNFLAGS_ISNULLABLE
- ... ? e.g. DBCOLUMNFLAGS_ISROWID
Thus, a DBD::ADO method (to be written!) like:
$sql_type = ado2sql( $ado_type, $is_fix, $is_long )
should give us an appropriate SQL DATA_TYPE (in most cases).
Finding the TYPE_NAME seems much harder (I think we'll need
adSchemaProviderType).
Unfortunately, every source (COLUMNS Rowset, PROVIDER_TYPES Rowset,
Field object) returns the type info in a slightly different way :-(
> As the provider above shows, the same data type may have different
> column type depending on the other attributes:
>
> TYPE_NAME
> *DATA_TYPE
> *COLUMN_SIZE
> LITERAL_PREFIX
> LITERAL_SUFFIX
> CREATE_PARAMS
> IS_NULLABLE
> CASE_SENSITIVE
> SEARCHABLE
> *UNSIGNED_ATTRIBUTE
> *FIXED_PREC_SCALE
> *AUTO_UNIQUE_VALUE
> *LOCAL_TYPE_NAME
> MINIMUM_SCALE
> MAXIMUM_SCALE
> GUID
> TYPELIB
> VERSION
> *IS_LONG
> *BEST_MATCH
> *IS_FIXEDLENGTH
>
> I'll look at mapping the SQL_* types to ADO attributes.
>
> Tom
>
Steffen
