RE: ODBC driver

Byron,
It would be nice to support all function which are supported
within PostgreSQL which are just a simple translation for the driver.

I haven't come across many ODBC drivers that support the full gammit of
ODBC functions within the specification and therefore think that it
would be a good start to support only the functions which can easily be
implemented.

The functions which are not supported could be incorporated into a
future version of PostgreSQL.

I don't think that anybody relies on many functions as a result of the
fact that few ODBC drivers actually support these functions. In my case
the only function we rely on is the Ucase function, which seems to be
supported by most drivers (but not all that we support For the few
drivers which don't support it, we do the translation ourselves by
parsing the SQL string). In any case I think it is nice to support as
many as possible, but not worth implementing rarely used functions which
require a large amount of processing in the driver itself.

In regard to the SQL_VARCHAR datatypes, I agree that name probably
should be SQL_CHAR, but it would be nice for varchar to be reported
prior to bpchar via the SQLGetInfo function. (It seems that it there are
more than one equivalent for a datatype, the most commonly used is
reported first usually.)

In regard to the prefix, suffix with date, datetime, etc. You may well
be right on that one. I just noticed that a few other ODBC drivers
report {d, {ts etc. I don't know of anybody relying on this information
though, so it's probably not a real issue.

Again thank you very much for the great work you're doing and the prompt
responses to the questions.

Regards,

Paul Lisewski.

> -----Original Message-----
> From: Byron Nikolaidis [SMTP:byronn(at)insightdist(dot)com]
> Sent: 17 April 1998 7:51
> To: Paul Lisewski; pgsql-interfaces(at)postgreSQL(dot)org
> Subject: 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(at)postgreSQL(dot)org" so
> everyone can see them, including me. Are you subscribed to this list?