Thread: Re: ODBC driver

Paul Lisewski wrote:

> Byron,
>         Thanks very much for your prompt response. After renaming a few
> columns (password and position), I've got my exisitng application
> working correctly, with the following minor problems:
>
>         {fn UCase(...) ODBC function is not supported. Are you planning
> to support ODBC functions??
>

Man, there are a ton of scalar functions that would need to be supported:

String Functions: ascii, char, concat, difference, insert, lcase, left,
length, locate, ltrim, repeat, replace, right, rtrim, soundex, space,
substring, ucase

Numeric Functions: abs, acos, asin, atan, atan2, ceiling, cos, cot, degrees,
exp, floor, log, log10, mod, pi, power, radians, rand, round, sign, sin,
sqrt, tan, truncate

Time/Date: curdate, curtime, dayname, dayofmonth, dayofweek, dayofyear,
hour, minute, month, monthname, now, quarter, second, timestampadd,
timestampdiff, week, year

System Functions: database, ifnull, user

And of course, the granddaddy of all functions, CONVERT().

Now, some of the string functions like '{fn ucase()}' could fairly easily be
mapped to the Postgres "Upper" function.  But what about all the others?
Should they be implemented in the driver or in the backend?  Or do we just
do the easy ones?

> When getting a list of DataTypes via SQLGetInfo, there are 4 SQL_VARCHAR
> types.
> They are:
> bpchar, varchar, text and name. I have a utility to interogate a database
> and create tables using the syntax from SQLGetInfo. I pick the first match
> from the list (in this case bpchar). Could the Types be modified to
> SQL_LONGVARCHAR for the non varchar datatypes or at least resequenced so
> that varchar comes before bpchar

Actually, looking at the latest driver source code, 'text' is mapped to an
SQL_LONGVARCHAR. Now that SQLPutData and SQLParamData are implemented,
LongVarChar can be properly handled and I think it makes sense to map it
this way.  At least on MSACCESS, it uses SQLPutData to handle
SQL_LONGVARCHAR's.

You should be seeing only 3 (bpchar, varchar, and name).  I would argue that
'name' should probably be mapped to SQL_CHAR, since I think it is fixed at
32 anyway.  So that leaves 2 types, bpchar and varchar.  Postgres handles
both as variable, and the driver looks up the length dynamically for the
SQLColumns call.

Now I dont believe there is an ODBC way of discriminating types based on
"blank padded" strings.

Any ideas anyone?


> I also noticed that the infomation returned by SQLGetInfo for DataTypes
> didn't have the correct prefix and suffix for date, datetime. They
> report ' for both pre ans suffix instead of {d } and {ts and }.
>

I dont know about that one.  I thought the prefix was supposed to be for the
native SQL, which would be a quote character (').

Regards,

Byron

P.S.,  please send these notes to the "pgsql-interfaces@postgreSQL.org" so
everyone can see them, including me.  Are you subscribed to this list?

> > {fn UCase(...) ODBC function is not supported. Are you planning
> > to support ODBC functions??
> There are a ton of scalar functions that would need to be supported:
> Strings: ascii, char, concat, difference, insert, lcase, left,
> length, locate, ltrim, repeat, replace, right, rtrim, soundex, space,
> substring, ucase
>
> Numerics: abs, acos, asin, atan, atan2, ceiling, cos, cot, degrees,
> exp, floor, log, log10, mod, pi, power, radians, rand, round, sign,
> sin, sqrt, tan, truncate
>
> Dates: curdate, curtime, dayname, dayofmonth, dayofweek, dayofyear,
> hour, minute, month, monthname, now, quarter, second, timestampadd,
> timestampdiff, week, year
>
> System Functions: database, ifnull, user
>
> And of course, the granddaddy of all functions, CONVERT().
>
> Now, some of the string functions like '{fn ucase()}' could fairly
> easily be mapped to the Postgres "Upper" function.  But what about all
> the others?
> Should they be implemented in the driver or in the backend?  Or do we
> just do the easy ones?

Let's do both. Some already map to existing functions, which mean you
get to do this in your driver I suppose. Others, like many of the math
routines, should/could be in the backend.

How do you want to organize attacking these? I can help now with
suggesting mappings for existing functions (e.g. date_part('dow',
datetime) gives you "dayofweek") and can help with new functions in a
month or two. Or, perhaps others can help with that more quickly...

                            - Tom

Suggested mappings would be a big help.

Thanks,

Byron


Thomas G. Lockhart wrote:

> > > {fn UCase(...) ODBC function is not supported. Are you planning
> > > to support ODBC functions??
> > There are a ton of scalar functions that would need to be supported:
> > Strings: ascii, char, concat, difference, insert, lcase, left,
> > length, locate, ltrim, repeat, replace, right, rtrim, soundex, space,
> > substring, ucase
> >
> > Numerics: abs, acos, asin, atan, atan2, ceiling, cos, cot, degrees,
> > exp, floor, log, log10, mod, pi, power, radians, rand, round, sign,
> > sin, sqrt, tan, truncate
> >
> > Dates: curdate, curtime, dayname, dayofmonth, dayofweek, dayofyear,
> > hour, minute, month, monthname, now, quarter, second, timestampadd,
> > timestampdiff, week, year
> >
> > System Functions: database, ifnull, user
> >
> > And of course, the granddaddy of all functions, CONVERT().
> >
> > Now, some of the string functions like '{fn ucase()}' could fairly
> > easily be mapped to the Postgres "Upper" function.  But what about all
> > the others?
> > Should they be implemented in the driver or in the backend?  Or do we
> > just do the easy ones?
>
> Let's do both. Some already map to existing functions, which mean you
> get to do this in your driver I suppose. Others, like many of the math
> routines, should/could be in the backend.
>
> How do you want to organize attacking these? I can help now with
> suggesting mappings for existing functions (e.g. date_part('dow',
> datetime) gives you "dayofweek") and can help with new functions in a
> month or two. Or, perhaps others can help with that more quickly...
>
>                             - Tom