pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedODBC Driver
38+ messages / 24 participants
[nested] [flat]
* ODBC Driver
@ 1998-04-16 15:13 Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 38+ messages in thread
From: Byron Nikolaidis @ 1998-04-16 15:13 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; pgsql-interfaces
Marc,
I said "practically re-written", not "completely re-written", there is a
difference.
It seems to me it is very difficult to express things clearly with mail
since you
can only interpret exactly what is written!
If you look at our web site, you will see I have given credit to the
original authors
and said it is based on their work. When I say "practically
re-written", basically
most of the original code is gone. The code is much more modular and I
think
easier to understand. It should make adding new features easier. And
man, there
are some awesome features in this thing -- just look at the highlights
and
enhancements on the web site.
Hope this clears things up,
Byron
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: ODBC driver
@ 1998-04-16 21:50 Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 38+ messages in thread
From: Byron Nikolaidis @ 1998-04-16 21:50 UTC (permalink / raw)
To: Paul Lisewski <paull@techone.com.au>; pgsql-interfaces
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?
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: ODBC driver
@ 1998-04-16 23:49 Stephen Davies <scldad@sdc.com.au>
1 sibling, 1 reply; 38+ messages in thread
From: Stephen Davies @ 1998-04-16 23:49 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; pgsql-interfaces
byronn@insightdist.com said:
> I would like to look into this, but need to know a few things.
>
> First, is this the latest version. As of yesterday (4/15/98) there is a new
> version at our website.
>
> Second, there is a definite possibility I am not handling other datestyles (like
> European, etc.) correctly.
>
> Third, what is the Postgres Data type for these fields that are not appearing: is it 'date' or
> 'datetime' or 'timestamp', etc. and could you maybe show me what
> these might look like in the database?
>
> And one final request, could you please send these things to the Postgres
> interfaces list too. It would be fine if you either just sent it there or send
> it there and CC me as well. The list is: pgsql-interfaces@postgreSQL.org
I must admit that I forgot about psql-interfaces when sending my previous mail
and I have just discovered that I had sent my subscribe requests to the wrong
address. Fixed now I hope.
The fields in question are type date. I do not know what the internal
representation is but they are displayed by psql as either dd-mm-yyyy or
mm-dd-yyyy depending on whether the server has -o -e or not. As in most parts
of the world, Australia uses dd-mm-yyyy as a standard.
The version I have been using is tagged as V6.30.0000 and 10/4/98.
I have just downloaded the new version. I shall test it and let you know how
it goes.
Cheers and thanks,
Stephen
>
========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 61-8-82728863
Computing & Network solutions. Fax: 61-8-82741015
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: ODBC Driver
@ 1998-04-17 02:15 The Hermit Hacker <scrappy@hub.org>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 38+ messages in thread
From: The Hermit Hacker @ 1998-04-17 02:15 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-interfaces
On Thu, 16 Apr 1998, Byron Nikolaidis wrote:
> Marc,
>
> I said "practically re-written", not "completely re-written", there is a
> difference. It seems to me it is very difficult to express things
> clearly with mail since you can only interpret exactly what is written!
Okay...I'll give it to you...this time *stern look* *broad grin*
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] Re: ODBC driver
@ 1998-04-17 02:45 Thomas G. Lockhart <lockhart@alumni.caltech.edu>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 38+ messages in thread
From: Thomas G. Lockhart @ 1998-04-17 02:45 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: Paul Lisewski <paull@techone.com.au>; pgsql-interfaces; Postgres Hackers List <hackers@postgresql.org>
> > {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
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: ODBC driver
@ 1998-04-17 06:55 Stephen Davies <scldad@sdc.com.au>
1 sibling, 0 replies; 38+ messages in thread
From: Stephen Davies @ 1998-04-17 06:55 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; pgsql-interfaces
byronn@insightdist.com said:
> I would like to look into this, but need to know a few things.
>
> First, is this the latest version. As of yesterday (4/15/98) there is a new
> version at our website.
>
G'day again Byron.
I tried your new 15/4 version. I get exactly the same results as before -
sorry.
Cheers,
Stephen.
========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 61-8-82728863
Computing & Network solutions. Fax: 61-8-82741015
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [HACKERS] Re: [INTERFACES] Re: ODBC driver
@ 1998-04-17 14:40 Byron Nikolaidis <byronn@insightdist.com>
parent: Thomas G. Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 0 replies; 38+ messages in thread
From: Byron Nikolaidis @ 1998-04-17 14:40 UTC (permalink / raw)
To: Thomas G. Lockhart <lockhart@alumni.caltech.edu>; +Cc: Paul Lisewski <paull@techone.com.au>; pgsql-interfaces; Postgres Hackers List <hackers@postgreSQL.org>
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
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] Re: ODBC driver
@ 1998-04-17 18:38 Tom Ivar Helbekkmo <tih+mail@Hamartun.Priv.NO>
parent: Stephen Davies <scldad@sdc.com.au>
0 siblings, 1 reply; 38+ messages in thread
From: Tom Ivar Helbekkmo @ 1998-04-17 18:38 UTC (permalink / raw)
To: Stephen Davies <scldad@sdc.com.au>; +Cc: Byron Nikolaidis <byronn@insightdist.com>; pgsql-interfaces
Stephen Davies <scldad@sdc.com.au> writes:
> I do not know what the internal representation is but they are
> displayed by psql as either dd-mm-yyyy or mm-dd-yyyy depending on
> whether the server has -o -e or not. As in most parts of the world,
> Australia uses dd-mm-yyyy as a standard.
Well, no. I have no idea which is more common of the two you mention,
but the majority of the people of this world actually use the ISO 8601
standard representation: yyyy-mm-dd.
-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: ODBC driver
@ 1998-04-17 20:27 Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 38+ messages in thread
From: Byron Nikolaidis @ 1998-04-17 20:27 UTC (permalink / raw)
To: Stephen Davies <scldad@sdc.com.au>; Hannu Krosing <hannu@trust.ee>; +Cc: pgsql-interfaces
Stephen,
I fixed the problems where no dates (or times) were showing up at all in MS Query (and I hope VB too
-- i don't have that to test). I still haven't added the DateStyle stuff yet, but at least US
format dates will show up.
Try it out. The current stuff is at our web site.
Byron
Stephen Davies wrote:
> it does not seem to handle date fields at all well.
>
> In MS Query, I get empty columns for all date fields (using US format and real
> format), in VB5 apps I get "3146 ODBC call failed" on the first
> OpenRecordSet call and in the same app under VB4 I get garbage if the dates
> are in real format (dmy).
>
> If I switch the server to US format dates, the VB4 apps seem to work OK.
>
> Could we please have a version that works with VB5 and knows about
> international issues.
>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] Re: ODBC driver
@ 1998-04-18 01:10 Stephen Davies <scldad@sdc.com.au>
parent: Tom Ivar Helbekkmo <tih+mail@Hamartun.Priv.NO>
0 siblings, 1 reply; 38+ messages in thread
From: Stephen Davies @ 1998-04-18 01:10 UTC (permalink / raw)
To: Tom Ivar Helbekkmo <tih+mail@Hamartun.Priv.NO>; pgsql-interfaces
> Stephen Davies <scldad@sdc.com.au> writes:
>
> > I do not know what the internal representation is but they are
> > displayed by psql as either dd-mm-yyyy or mm-dd-yyyy depending on
> > whether the server has -o -e or not. As in most parts of the world,
> > Australia uses dd-mm-yyyy as a standard.
>
> Well, no. I have no idea which is more common of the two you mention,
> but the majority of the people of this world actually use the ISO 8601
> standard representation: yyyy-mm-dd.
>
OK. Point taken.
However, the point that I was trying to make is that, to my knowledge, nobody
outside the US uses mm/dd.
Cheers,
Stephen.
========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 61-8-82728863
Computing & Network solutions. Fax: 61-8-82741015
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] Re: ODBC driver
@ 1998-04-18 01:46 Christopher Luk <wluk@hkcccc.org>
parent: Stephen Davies <scldad@sdc.com.au>
0 siblings, 0 replies; 38+ messages in thread
From: Christopher Luk @ 1998-04-18 01:46 UTC (permalink / raw)
To: pgsql-interfaces
As a user, I use dd-mm-yyyy in HK. Was I an minority?
Stephen Davies wrote:
> > Stephen Davies <scldad@sdc.com.au> writes:
> >
> > > I do not know what the internal representation is but they are
> > > displayed by psql as either dd-mm-yyyy or mm-dd-yyyy depending on
> > > whether the server has -o -e or not. As in most parts of the world,
> > > Australia uses dd-mm-yyyy as a standard.
> >
> > Well, no. I have no idea which is more common of the two you mention,
> > but the majority of the people of this world actually use the ISO 8601
> > standard representation: yyyy-mm-dd.
> >
> OK. Point taken.
>
> However, the point that I was trying to make is that, to my knowledge, nobody
> outside the US uses mm/dd.
>
> Cheers,
> Stephen.
>
> ========================================================================
> Stephen Davies Consulting scldad@sdc.com.au
> Adelaide, South Australia. Voice: 61-8-82728863
> Computing & Network solutions. Fax: 61-8-82741015
--
Regards, Christopher Luk
System Analyst - The Hong Kong Council of the Church of Christ in China
Phone : (852) 2397-1022 Fax : (852) 2397-7405
mailto:wluk@hkcccc.org URL : http://www.hkcccc.org (Chinese based)
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: ODBC driver
@ 1998-04-18 07:31 Stephen Davies <scldad@sdc.com.au>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 38+ messages in thread
From: Stephen Davies @ 1998-04-18 07:31 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-interfaces
> Stephen,
>
> I fixed the problems where no dates (or times) were showing up at all in MS Query (and I hope VB too
> -- i don't have that to test). I still haven't added the DateStyle stuff yet, but at least US
> format dates will show up.
>
> Try it out. The current stuff is at our web site.
>
Thank you Byron. This version plus the ConnSettings facility now has my apps
running better than ever.
BTW, the VB5 problem was nothing to do with you. It turned out to be one of
those you beaut features of VB5 where code that worked fine under VB4 fails
under VB5. I reworded the code and now VB4 and VB5 run as expected.
On the subject of 16-bit support: I was one of those who needed 16-bit support
before but my last 16-bit PostgreSQL client upgraded to W95 a couple of
months ago.
Despite that, I agree that we really should have a 16-bit option for those
lucky people who can resist the MS behemouth.
Cheers and thanks,
Stephen.
========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 61-8-82728863
Computing & Network solutions. Fax: 61-8-82741015
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: ODBC driver
@ 1998-04-20 01:57 Paul Lisewski <paull@techone.com.au>
0 siblings, 0 replies; 38+ messages in thread
From: Paul Lisewski @ 1998-04-20 01:57 UTC (permalink / raw)
To: 'Byron Nikolaidis' <byronn@insightdist.com>; pgsql-interfaces
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@insightdist.com]
> Sent: 17 April 1998 7:51
> To: Paul Lisewski; pgsql-interfaces@postgreSQL.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@postgreSQL.org" so
> everyone can see them, including me. Are you subscribed to this list?
^ permalink raw reply [nested|flat] 38+ messages in thread
* ODBC Driver
@ 1998-07-22 17:11 Bruce Tong <zztong@laxmi.ev.net>
0 siblings, 3 replies; 38+ messages in thread
From: Bruce Tong @ 1998-07-22 17:11 UTC (permalink / raw)
To: PostgreSQL General <pgsql-general@postgresql.org>; +Cc: pgsql-interfaces
I knew I needed to check my work before bringing the ODBC driver problems
out in the open. Here's what I've found.
I was designing a simple query using MS-Access having already defined
an external data source to a PostgreSQL database just to make sure
things work as I expected. The database is bigger than this, but here's
the relevant parts of the tables...
Table: School
school_id integer unique not null,
school_name varchar( 50 )
Table: Contact
contact_id integer unique not null,
contact_lname varchar( 50 ),
contact_fname varchar( 50 ),
contact_school integer not null
I wanted to see contact_lname, contact_fname, and school_name. Using MS
Access's query view I dragged a link from contact_school to school_id,
then I dragged the three fields I wanted to my query. (Maybe you can see
where this is going.) I attempted to view the results.
A window appeared and said "ODBC-call failed".
Another window appeared and said "Error while executing the query. ERROR:
The field being ordered by must appear in the target list (#1)."
Since I hadn't specified any sorting, the "order by" part of the message
had me confused. I decided to have MS Access show me the SQL it had
generated:
SELECT contact.contact_lname, contact.contact_fname, school.school_name
FROM contact INNER JOIN school ON contact.contact_school =
school.school_id;
If I remember correctly, INNER JOIN is not yet supported in PostgreSQL,
which would explain the failure. Sorry to have accused the failure on the
ODBC driver. While some people's ignorance is bliss, mine thrashes. At
least now I know what a $#@!% INNER JOIN is. ;)
Bruce Tong | Got me an office; I'm there late at night.
Systems Programmer | Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE |
zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] ODBC Driver
@ 1998-07-22 22:42 Byron Nikolaidis <byronn@insightdist.com>
parent: Bruce Tong <zztong@laxmi.ev.net>
2 siblings, 1 reply; 38+ messages in thread
From: Byron Nikolaidis @ 1998-07-22 22:42 UTC (permalink / raw)
To: Bruce Tong <zztong@laxmi.ev.net>; +Cc: PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
Bruce Tong wrote:
>
> I knew I needed to check my work before bringing the ODBC driver problems
> out in the open. Here's what I've found.
>
> I was designing a simple query using MS-Access having already defined
> an external data source to a PostgreSQL database just to make sure
> things work as I expected. The database is bigger than this, but here's
> the relevant parts of the tables...
>
> Table: School
> school_id integer unique not null,
> school_name varchar( 50 )
>
> Table: Contact
> contact_id integer unique not null,
> contact_lname varchar( 50 ),
> contact_fname varchar( 50 ),
> contact_school integer not null
>
> I wanted to see contact_lname, contact_fname, and school_name. Using MS
> Access's query view I dragged a link from contact_school to school_id,
> then I dragged the three fields I wanted to my query. (Maybe you can see
> where this is going.) I attempted to view the results.
>
> A window appeared and said "ODBC-call failed".
>
> Another window appeared and said "Error while executing the query. ERROR:
> The field being ordered by must appear in the target list (#1)."
>
> Since I hadn't specified any sorting, the "order by" part of the message
> had me confused. I decided to have MS Access show me the SQL it had
> generated:
>
Actually, there are two issues here. First, the message about the field
being ordered by must appear in the target list is appearing because
Access is throwing that order by clause in there for some reason which
we have been unable to discover (perhaps for optimization?). BTW, are
you using Access 97 or 95? Second, Dave has a patch for Postgres on our
website which allows the order by and group by clauses to not be in the
target list, which would probably get you past this.
I created your tables in my database and was not able to re-create the
error in Access. You might try upgrading to the latest driver (.0248)
which is what I was using to test with. I used to get the same message
you got here but I haven't been able to reproduce it lately. You might
want to relink your tables in Access and/or re-create the query before
you try your test again.
> SELECT contact.contact_lname, contact.contact_fname, school.school_name
> FROM contact INNER JOIN school ON contact.contact_school =
> school.school_id;
>
> If I remember correctly, INNER JOIN is not yet supported in PostgreSQL,
> which would explain the failure. Sorry to have accused the failure on the
> ODBC driver. While some people's ignorance is bliss, mine thrashes. At
> least now I know what a $#@!% INNER JOIN is. ;)
Sort of true. The INNER JOIN clause is not yet implemented in postgres,
but the Microsoft Jet Database engine will translate this query into a
standard join (i.e., select ... from contact, school WHERE
contact.contact_school = school.school_id). If you look in the commlog
file (psqlodbc.log) on your pc, you will see the translated query.
Hope this helps.
Byron
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] ODBC Driver
@ 1998-07-23 06:39 Sbragion Denis <infotecn@tin.it>
parent: Bruce Tong <zztong@laxmi.ev.net>
2 siblings, 1 reply; 38+ messages in thread
From: Sbragion Denis @ 1998-07-23 06:39 UTC (permalink / raw)
To: Bruce Tong <zztong@laxmi.ev.net>; +Cc: pgsql-interfaces
Hello,
At 13.11 22/07/98 -0400, you wrote:
>Since I hadn't specified any sorting, the "order by" part of the message
>had me confused. I decided to have MS Access show me the SQL it had
>generated:
>
>SELECT contact.contact_lname, contact.contact_fname, school.school_name
>FROM contact INNER JOIN school ON contact.contact_school =
>school.school_id;
>
>If I remember correctly, INNER JOIN is not yet supported in PostgreSQL,
>which would explain the failure. Sorry to have accused the failure on the
>ODBC driver. While some people's ignorance is bliss, mine thrashes. At
>least now I know what a $#@!% INNER JOIN is. ;)
Are you sure this is the cause of the problem? I have been able to perform
INNER JOIN queries (and even outer joins) using Visual Basic 5.0 without
problems even if pgsql doesn't support them. The undelrying Jet database
engine performs the join using simpler queries. Things are pretty slow, but
it works. As far as I know Access should use the same Jet database engine
so things should be the same.
Any hint here?
ByX!
Dr. Sbragion Denis
InfoTecna
Tel, Fax: +39 39 2324054
URL: http://space.tin.it/internet/dsbragio
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [GENERAL] ODBC Driver
@ 1998-07-23 09:23 Maarten Boekhold <maartenb@dutepp0.et.tudelft.nl>
parent: Bruce Tong <zztong@laxmi.ev.net>
2 siblings, 0 replies; 38+ messages in thread
From: Maarten Boekhold @ 1998-07-23 09:23 UTC (permalink / raw)
To: Bruce Tong <zztong@laxmi.ev.net>; +Cc: PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
> SELECT contact.contact_lname, contact.contact_fname, school.school_name
> FROM contact INNER JOIN school ON contact.contact_school =
> school.school_id;
>
> If I remember correctly, INNER JOIN is not yet supported in PostgreSQL,
> which would explain the failure. Sorry to have accused the failure on the
Aren't INNER JOIN's not exactly the same as what we call a 'normal' join?
ie. isn't it an idea to add the syntax of an INNER JOIN to the parser and
just kinda ignore it? You could have the scanner return just a "," (comma)
for the INNER_JOIN token....
Maarten
_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems |
| Department of Electrical Engineering |
| Computer Architecture and Digital Technique section |
| M.Boekhold@et.tudelft.nl |
-----------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] ODBC Driver
@ 1998-07-23 12:48 Bruce Tong <zztong@laxmi.ev.net>
parent: Sbragion Denis <infotecn@tin.it>
0 siblings, 0 replies; 38+ messages in thread
From: Bruce Tong @ 1998-07-23 12:48 UTC (permalink / raw)
To: Sbragion Denis <infotecn@tin.it>; +Cc: pgsql-interfaces
> >If I remember correctly, INNER JOIN is not yet supported in PostgreSQL,
> >which would explain the failure. Sorry to have accused the failure on the
> >ODBC driver. While some people's ignorance is bliss, mine thrashes. At
> >least now I know what a $#@!% INNER JOIN is. ;)
>
> Are you sure this is the cause of the problem? I have been able to perform
> INNER JOIN queries (and even outer joins) using Visual Basic 5.0 without
> problems even if pgsql doesn't support them. The undelrying Jet database
> engine performs the join using simpler queries. Things are pretty slow, but
> it works. As far as I know Access should use the same Jet database engine
> so things should be the same.
No, I'm not sure that is the problem. ;) That is essentially my guess
based on what I remember reading. A number of people have reported more
detailed explanations which are undoubtably more correct.
Bruce Tong | Got me an office; I'm there late at night.
Systems Programmer | Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE |
zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] ODBC Driver
@ 1998-07-23 12:59 Bruce Tong <zztong@laxmi.ev.net>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 38+ messages in thread
From: Bruce Tong @ 1998-07-23 12:59 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
> Actually, there are two issues here. First, the message about the field
> being ordered by must appear in the target list is appearing because
> Access is throwing that order by clause in there for some reason which
> we have been unable to discover (perhaps for optimization?). BTW, are
> you using Access 97 or 95? Second, Dave has a patch for Postgres on our
> website which allows the order by and group by clauses to not be in the
> target list, which would probably get you past this.
Access 97.
> I created your tables in my database and was not able to re-create the
> error in Access. You might try upgrading to the latest driver (.0248)
> which is what I was using to test with. I used to get the same message
> you got here but I haven't been able to reproduce it lately. You might
> want to relink your tables in Access and/or re-create the query before
> you try your test again.
I can make my database available to you over the net if you want. I just
need your IP address so I can authorize you in pg_hba. Contact me directly
if you're interested and I'll create an account for you, etc. The actual
tables are a little bigger than the examples I posted. I can also send you
the exact table definitions as I have all the SQL to create them in a file
I use in accordance with a makefile. Let me know if/how you want to
proceed.
I'll grab the latest driver and try that as well.
Bruce Tong | Got me an office; I'm there late at night.
Systems Programmer | Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE |
zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] ODBC Driver
@ 1998-07-23 15:14 Bruce Tong <zztong@laxmi.ev.net>
parent: Bruce Tong <zztong@laxmi.ev.net>
0 siblings, 1 reply; 38+ messages in thread
From: Bruce Tong @ 1998-07-23 15:14 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
> I'll grab the latest driver and try that as well.
Installed, threw away the old data link, created a new data link, same
problem occurred.
Bruce Tong | Got me an office; I'm there late at night.
Systems Programmer | Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE |
zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] ODBC Driver
@ 1998-07-23 15:57 Byron Nikolaidis <byronn@insightdist.com>
parent: Bruce Tong <zztong@laxmi.ev.net>
0 siblings, 1 reply; 38+ messages in thread
From: Byron Nikolaidis @ 1998-07-23 15:57 UTC (permalink / raw)
To: Bruce Tong <zztong@laxmi.ev.net>; +Cc: PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
Bruce Tong wrote:
>
> Installed, threw away the old data link, created a new data link, same
> problem occurred.
>
OK, could you try this before we resort to more drastic things. When
you are looking at the design view of your query, right click and call
up "properties" or call it up under the "View" menu. For the "RecordSet
type" property, try changing it to "Snapshot". See if this causes
Access to not throw the "order by" clause in the query.
Of course having the recordset type as a snapshot means you can't update
the query resultset. Let me know if it works.
Byron
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: [GENERAL] ODBC Driver
@ 1998-07-23 16:17 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 38+ messages in thread
From: Jackson, DeJuan @ 1998-07-23 16:17 UTC (permalink / raw)
To: M.Boekhold@et.tudelft.nl, Bruce Tong <zztong@laxmi.ev.net>; +Cc: PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
> > SELECT contact.contact_lname, contact.contact_fname,
> school.school_name
> > FROM contact INNER JOIN school ON contact.contact_school =
> > school.school_id;
> >
> > If I remember correctly, INNER JOIN is not yet supported in
> PostgreSQL,
> > which would explain the failure. Sorry to have accused the failure
> on the
>
> Aren't INNER JOIN's not exactly the same as what we call a 'normal'
> join?
> ie. isn't it an idea to add the syntax of an INNER JOIN to the parser
> and
> just kinda ignore it? You could have the scanner return just a ","
> (comma)
> for the INNER_JOIN token....
>
> Maarten
>
If it were only that easy it would have been implemented long ago.
The 'join clause' syntax takes the form of
tuple_struct(1) [AS alias(1)] <JOIN_TYPE> tuple_struct(1) [AS
alias(2)] ON column_contraints
Where:
tuple_struct = a table name, or the result of a 'join clause'
JOIN_TYPE =
INNER JOIN | -- Normal join
LEFT [OUTER] JOIN | -- Includes all rows from
tuple_struct(1) even if they do not satisfy the column_contraints (NULL
padded)
RIGHT [OUTER] JOIN | -- Includes all rows from
tuple_struct(2) even if they do not satisfy the column_contraints (NULL
padded)
FULL OUTER JOIN -- Includes all rows from tuple_struct(1
& 2) even if they do not satisfy the column_contraints (NULL padded)
constraint_type = Same form as a WHERE clause
Which means you could easily have:
SELECT *
FROM ((t1 INNER JOIN t2
ON (t1.col1 = t2.col1 AND t1.name = t2.type)) AS
j1
INNER JOIN (t3 INNER JOIN t4
ON (t3.col1 = t4.col1 AND t3.name = t4.type)) AS
j2
ON j1.col1 = j2.col1) AS r1
WHERE r1.stuff = 17 AND
r1.kind = 'those'
ORDER BY r1.sortCol
Which would have to be rewritten as:
SELECT *
FROM t1, t2, t3, t4
WHERE t1.col1 = t2.col1 AND
t1.name = t2.type AND
t3.col1 = t4.col1 AND
t3.name = t4.type AND
t1.col1 = t3.col1 AND
t1.stuff = 17 AND
t3.kind = 'those'
ORDER BY t4.sortCol
I say all this without a standard sitting in front of me and with no
formal training so I want balk at correction if I missed a point or am
misinformed, but this is what experience has told me. (Haven't ever
actually tried that AS part of it though.)
-DEJ
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] ODBC Driver
@ 1998-07-23 17:11 Bruce Tong <zztong@laxmi.ev.net>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 38+ messages in thread
From: Bruce Tong @ 1998-07-23 17:11 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
> OK, could you try this before we resort to more drastic things. When
> you are looking at the design view of your query, right click and call
> up "properties" or call it up under the "View" menu. For the "RecordSet
> type" property, try changing it to "Snapshot". See if this causes
> Access to not throw the "order by" clause in the query.
>
> Of course having the recordset type as a snapshot means you can't update
> the query resultset. Let me know if it works.
Same error.
Bruce Tong | Got me an office; I'm there late at night.
Systems Programmer | Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE |
zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century
^ permalink raw reply [nested|flat] 38+ messages in thread
* Odbc driver
@ 1999-03-30 15:40 Sergio <ser@perio.unlp.edu.ar>
0 siblings, 2 replies; 38+ messages in thread
From: Sergio @ 1999-03-30 15:40 UTC (permalink / raw)
To: pgsql-interfaces@hub.org
Byron,
Postgres 6.3.2, Odbc driver 6.40.0004
I get this
'Access violation at address xxxxxxx in module 'PSQLODBC.DLL'.
Read of address yyyyyyyy'
calling SQLGetData( hstmt, FieldNo, SQL_BINARY, SQLPOINTER( @i), 0, @iv);
note the 5th parameter -> 0
over a TEXT field with sizes of 4000 or more bytes.
if I try it with small fields (3000 or so bytes) there is no problem.
ok, I tracked it down and it seems to work until iv=4099,
when it must return 4100, boom !
I wonder why a text saved by Notepad is 4183 bytes and this
same text within Postgres is reported as 4099 chars long, but
maybe this is another history.
Sergio
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] Odbc driver
@ 1999-03-30 16:30 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Sergio <ser@perio.unlp.edu.ar>
1 sibling, 0 replies; 38+ messages in thread
From: Ross J. Reedstrom @ 1999-03-30 16:30 UTC (permalink / raw)
To: pgsql-interfaces@hub.org
Sergio wrote:
>
> Byron,
>
> Postgres 6.3.2, Odbc driver 6.40.0004
>
<snipped real problem, since I know nothing about it!>
(although, I do know that the communications protocol changed 6.3->6.4.
Might an upgrade help?)
>
> I wonder why a text saved by Notepad is 4183 bytes and this
> same text within Postgres is reported as 4099 chars long, but
> maybe this is another history.
Hmm, this text wouldn't have 84 lines, would it? That'd be an average of
~49 characters per line, so it's reasonable. DOS/Win uses CR/LF line
ending pairs, while the unix backend only stores LF (or is that CR?
Doesn't matter, 2 bytes vs. one byte)
Just think of it as the earliest example of Microsoft code bloat ;-)
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [INTERFACES] Odbc driver
@ 1999-03-30 22:22 Sergio <ser@perio.unlp.edu.ar>
parent: Sergio <ser@perio.unlp.edu.ar>
1 sibling, 0 replies; 38+ messages in thread
From: Sergio @ 1999-03-30 22:22 UTC (permalink / raw)
To: pgsql-interfaces@hub.org
Sergio <ser@perio.unlp.edu.ar> el día Tue, 30 Mar 1999 12:40:10 -0300,
escribió:
>
>Byron,
>
>Postgres 6.3.2, Odbc driver 6.40.0004
>
>
>I get this
>
>'Access violation at address xxxxxxx in module 'PSQLODBC.DLL'.
>Read of address yyyyyyyy'
>
>calling SQLGetData( hstmt, FieldNo, SQL_BINARY, SQLPOINTER( @i), 0, @iv);
>
>note the 5th parameter -> 0
>
>over a TEXT field with sizes of 4000 or more bytes.
>
>if I try it with small fields (3000 or so bytes) there is no problem.
>
>ok, I tracked it down and it seems to work until iv=4099,
>when it must return 4100, boom !
I _think_ I found the problem:
in convert.c in the function copy_and_convert_field, you declare
char tempBuf[TEXT_FIELD_SIZE+5];
wich is fixed at _compile_ time, no ?, so the TEXT_FIELD_SIZE wich
is an option in the Odbc driver cfg, is ignored.
I'm rigth ? (because changing Max LongVarChar in the cfg doesn't
seems to affect nothing)
Sergio
^ permalink raw reply [nested|flat] 38+ messages in thread
* odbc driver
@ 1999-08-26 18:31 shan <shan@channel21.com>
0 siblings, 0 replies; 38+ messages in thread
From: shan @ 1999-08-26 18:31 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Jason Nadler <dixon@channel21.com>
where can I find the setup program to install a PostgreSQL ODBC driver on my
alpha machine?
Shan Jiang
Channel 21 Productions Inc.
Tel: 516-821-9220
^ permalink raw reply [nested|flat] 38+ messages in thread
* odbc driver
@ 1999-09-02 21:49 Sergio A. Kessler <ser@perio.unlp.edu.ar>
0 siblings, 0 replies; 38+ messages in thread
From: Sergio A. Kessler @ 1999-09-02 21:49 UTC (permalink / raw)
To: pgsql-interfaces
where is the web pages of the odbc driver in postgresql.org ?
and why there is no visible link to it ?
Sergio
^ permalink raw reply [nested|flat] 38+ messages in thread
* ODBC driver
@ 1999-10-29 16:43 Patrick Welche <prlw1@newn.cam.ac.uk>
0 siblings, 0 replies; 38+ messages in thread
From: Patrick Welche @ 1999-10-29 16:43 UTC (permalink / raw)
To: pgsql-interfaces
I promised to mail someone from this list an odbc driver, but stupidly
deleted his email address. Please remind me who you are!
Cheers,
Patrick
^ permalink raw reply [nested|flat] 38+ messages in thread
* ODBC driver
@ 1999-11-01 12:16 Patrick Welche <prlw1@newn.cam.ac.uk>
0 siblings, 1 reply; 38+ messages in thread
From: Patrick Welche @ 1999-11-01 12:16 UTC (permalink / raw)
To: pgsql-interfaces
I promised to mail someone from this list an odbc driver, but stupidly
deleted his email address. Please remind me who you are!
Cheers,
Patrick
^ permalink raw reply [nested|flat] 38+ messages in thread
* [INTERFACES] ODBC driver
@ 1999-11-01 13:00 Adolfo Diaz <adiaz@vega.inf.cu>
parent: Patrick Welche <prlw1@newn.cam.ac.uk>
0 siblings, 0 replies; 38+ messages in thread
From: Adolfo Diaz @ 1999-11-01 13:00 UTC (permalink / raw)
To: Patrick Welche <prlw1@newn.cam.ac.uk>; +Cc: pgsql-interfaces
Hello,
I am waiting for you.
Regards,
Adolfo.
On Mon, 1 Nov 1999, Patrick Welche wrote:
> I promised to mail someone from this list an odbc driver, but stupidly
> deleted his email address. Please remind me who you are!
>
> Cheers,
>
> Patrick
>
> ************
>
^ permalink raw reply [nested|flat] 38+ messages in thread
* ODBC driver
@ 2000-06-01 21:49 Mihai Gheorghiu <tanhq@bigplanet.com>
0 siblings, 0 replies; 38+ messages in thread
From: Mihai Gheorghiu @ 2000-06-01 21:49 UTC (permalink / raw)
To: pgsql-interfaces
A previous message mentioned that MSAccess performs a join locally when
accessing a postgres database via the ODBC drivers (link files).
Does that mean that it sends the entire tables from the postgres server to
the Win computer and the query is actually performed by MSAccess?
Thanks,
Mihai Gheorghiu
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: ODBC driver
@ 2000-06-01 22:14 Gregory Krasnow OpenSourceCRM <gak@opensourcecrm.org>
0 siblings, 0 replies; 38+ messages in thread
From: Gregory Krasnow OpenSourceCRM @ 2000-06-01 22:14 UTC (permalink / raw)
To: pgsql-interfaces
I believe that the JET Database Engine (on which Access is based) always
performs joins, order bys, etc... locally. I believe though that it only
grabs a certain number of rows at a time from the actual table.
- Greg
-----Original Message-----
From: Mihai Gheorghiu [mailto:tanhq@bigplanet.com]
Sent: Thursday, June 01, 2000 2:49 PM
To: pgsql-interfaces@postgresql.org
Subject: [INTERFACES] ODBC driver
A previous message mentioned that MSAccess performs a join locally when
accessing a postgres database via the ODBC drivers (link files).
Does that mean that it sends the entire tables from the postgres server to
the Win computer and the query is actually performed by MSAccess?
Thanks,
Mihai Gheorghiu
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: ODBC driver
@ 2000-06-02 10:12 Nicolas Huillard <nhuillard@ghs.fr>
0 siblings, 0 replies; 38+ messages in thread
From: Nicolas Huillard @ 2000-06-02 10:12 UTC (permalink / raw)
To: 'Mihai Gheorghiu' <tanhq@bigplanet.com>; pgsql-interfaces
The best way to see how it works is to look at the query log in the postgres back-end. My bet is that Access will send select/where-clauses and so on to the back-end, for each table, and then do it's join locally, on the subset of the data. Maybe it can work even smarter...
Nicolas Huillard
-----Message d'origine-----
De: Mihai Gheorghiu [SMTP:tanhq@bigplanet.com]
Date: jeudi 1 juin 2000 23:49
À: pgsql-interfaces@postgresql.org
Objet: ODBC driver
A previous message mentioned that MSAccess performs a join locally when
accessing a postgres database via the ODBC drivers (link files).
Does that mean that it sends the entire tables from the postgres server to
the Win computer and the query is actually performed by MSAccess?
Thanks,
Mihai Gheorghiu
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: RE: ODBC driver
@ 2000-06-02 15:04 Jeff Eckermann <jeckermann@verio.net>
0 siblings, 0 replies; 38+ messages in thread
From: Jeff Eckermann @ 2000-06-02 15:04 UTC (permalink / raw)
To: 'Nicolas Huillard' <nhuillard@ghs.fr>; 'Mihai Gheorghiu' <tanhq@bigplanet.com>; pgsql-interfaces
My experience with MS Access, querying large amounts of data via ODBC, is
that pass-through queries (carried out by the server) are usually the best
way to go. Local queries on linked tables will work fine for simple queries
(small number of rows returned, with a simple WHERE clause), but any kind of
large result set, or especially a complex set of WHERE criteria, slows my PC
to a crawl for 30-60 minutes or more. This is with a PII 350Mhz, 128 Mg
RAM. Same happened (only worse) with my previous PC. I've had to kill
Access plenty of times just to get out of these situations, because I just
couldn't work while it was going on.
I don't have any insight into the processes involved, this is just my
observation on many occasions...
> -----Original Message-----
> From: Nicolas Huillard [SMTP:nhuillard@ghs.fr]
> Sent: Friday, June 02, 2000 5:13 AM
> To: 'Mihai Gheorghiu'; pgsql-interfaces@postgresql.org
> Subject: [INTERFACES] RE: ODBC driver
>
> The best way to see how it works is to look at the query log in the
> postgres back-end. My bet is that Access will send select/where-clauses
> and so on to the back-end, for each table, and then do it's join locally,
> on the subset of the data. Maybe it can work even smarter...
>
> Nicolas Huillard
>
> -----Message d'origine-----
> De: Mihai Gheorghiu [SMTP:tanhq@bigplanet.com]
> Date: jeudi 1 juin 2000 23:49
> À: pgsql-interfaces@postgresql.org
> Objet: ODBC driver
>
> A previous message mentioned that MSAccess performs a join locally when
> accessing a postgres database via the ODBC drivers (link files).
> Does that mean that it sends the entire tables from the postgres server to
> the Win computer and the query is actually performed by MSAccess?
>
> Thanks,
>
> Mihai Gheorghiu
>
^ permalink raw reply [nested|flat] 38+ messages in thread
* ODBC Driver
@ 2000-11-28 09:05 Evelio Martinez <evelio.martinez@testanet.com>
0 siblings, 1 reply; 38+ messages in thread
From: Evelio Martinez @ 2000-11-28 09:05 UTC (permalink / raw)
To: pgsql-interfaces
Hello!
Which ODBC driver would you recommend me to connect postgresql 7.0.3 on
linux with W95/W98/Srv200 clients and
possibly Ms Access or VB ?
Thanks in advance
Evelio
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: ODBC Driver
@ 2000-11-28 09:46 Poul L. Christiansen <poulc@cs.auc.dk>
parent: Evelio Martinez <evelio.martinez@testanet.com>
0 siblings, 0 replies; 38+ messages in thread
From: Poul L. Christiansen @ 2000-11-28 09:46 UTC (permalink / raw)
To: Evelio Martinez <evelio.martinez@testanet.com>; +Cc: pgsql-interfaces
This one:
ftp://ftp.postgresql.org/pub/odbc/index.html
Poul L. Christiansen
Evelio Martinez wrote:
>
> Hello!
>
> Which ODBC driver would you recommend me to connect postgresql 7.0.3 on
> linux with W95/W98/Srv200 clients and
> possibly Ms Access or VB ?
>
> Thanks in advance
>
> Evelio
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: ODBC Driver
@ 2003-11-07 04:03 Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 38+ messages in thread
From: Bruce Momjian @ 2003-11-07 04:03 UTC (permalink / raw)
To: Goertz, Dave PSC <dgoertz@psc.gov.sk.ca>; +Cc: pgsql-interfaces
I got this report from a user. Anyone?
---------------------------------------------------------------------------
Goertz, Dave PSC wrote:
> We have been using the old driver (ver. 07_01_0004) and its seems fine
> except for when doing left outer joins from Access. The log shows that
> characters like '{' are being placed into the code being given to the back
> end.
> We tried the newer one (ver. 07_03_0100) in the summer and found that it
> caused other problems so we stayed with the old one.
> Now we were trying the newest one (ver. 07_03_0200) and it seems to fix the
> left outer join problem but breaks other things.
> In particular we use a language called SAS to talk to PostgreSQL and
> everything works except for certain comparisons.
>
> Ex: If REG_PY NE 0;
>
> The old driver passes some 39,000 records through this statement but the new
> one passes 1900.
>
> Are data types being screwed up. REG_PY should just be a numeric datatype.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 38+ messages in thread
end of thread, other threads:[~2003-11-07 04:03 UTC | newest]
Thread overview: 38+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-04-16 15:13 ODBC Driver Byron Nikolaidis <byronn@insightdist.com>
1998-04-17 02:15 ` The Hermit Hacker <scrappy@hub.org>
1998-04-16 21:50 Re: ODBC driver Byron Nikolaidis <byronn@insightdist.com>
1998-04-17 02:45 ` Re: [INTERFACES] Re: ODBC driver Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1998-04-17 14:40 ` Re: [HACKERS] Re: [INTERFACES] Re: ODBC driver Byron Nikolaidis <byronn@insightdist.com>
1998-04-16 23:49 ` Re: ODBC driver Stephen Davies <scldad@sdc.com.au>
1998-04-17 18:38 ` Re: [INTERFACES] Re: ODBC driver Tom Ivar Helbekkmo <tih+mail@Hamartun.Priv.NO>
1998-04-18 01:10 ` Re: [INTERFACES] Re: ODBC driver Stephen Davies <scldad@sdc.com.au>
1998-04-18 01:46 ` Re: [INTERFACES] Re: ODBC driver Christopher Luk <wluk@hkcccc.org>
1998-04-17 06:55 ` Re: ODBC driver Stephen Davies <scldad@sdc.com.au>
1998-04-17 20:27 Re: ODBC driver Byron Nikolaidis <byronn@insightdist.com>
1998-04-18 07:31 ` Re: ODBC driver Stephen Davies <scldad@sdc.com.au>
1998-04-20 01:57 RE: ODBC driver Paul Lisewski <paull@techone.com.au>
1998-07-22 17:11 ODBC Driver Bruce Tong <zztong@laxmi.ev.net>
1998-07-22 22:42 ` Byron Nikolaidis <byronn@insightdist.com>
1998-07-23 12:59 ` Bruce Tong <zztong@laxmi.ev.net>
1998-07-23 15:14 ` Bruce Tong <zztong@laxmi.ev.net>
1998-07-23 15:57 ` Byron Nikolaidis <byronn@insightdist.com>
1998-07-23 17:11 ` Bruce Tong <zztong@laxmi.ev.net>
1998-07-23 06:39 ` Sbragion Denis <infotecn@tin.it>
1998-07-23 12:48 ` Bruce Tong <zztong@laxmi.ev.net>
1998-07-23 09:23 ` Maarten Boekhold <maartenb@dutepp0.et.tudelft.nl>
1998-07-23 16:17 RE: [GENERAL] ODBC Driver Jackson, DeJuan <djackson@cpsgroup.com>
1999-03-30 15:40 Odbc driver Sergio <ser@perio.unlp.edu.ar>
1999-03-30 16:30 ` Re: [INTERFACES] Odbc driver Ross J. Reedstrom <reedstrm@rice.edu>
1999-03-30 22:22 ` Re: [INTERFACES] Odbc driver Sergio <ser@perio.unlp.edu.ar>
1999-08-26 18:31 odbc driver shan <shan@channel21.com>
1999-09-02 21:49 odbc driver Sergio A. Kessler <ser@perio.unlp.edu.ar>
1999-10-29 16:43 ODBC driver Patrick Welche <prlw1@newn.cam.ac.uk>
1999-11-01 12:16 ODBC driver Patrick Welche <prlw1@newn.cam.ac.uk>
1999-11-01 13:00 ` [INTERFACES] ODBC driver Adolfo Diaz <adiaz@vega.inf.cu>
2000-06-01 21:49 ODBC driver Mihai Gheorghiu <tanhq@bigplanet.com>
2000-06-01 22:14 RE: ODBC driver Gregory Krasnow OpenSourceCRM <gak@opensourcecrm.org>
2000-06-02 10:12 RE: ODBC driver Nicolas Huillard <nhuillard@ghs.fr>
2000-06-02 15:04 RE: RE: ODBC driver Jeff Eckermann <jeckermann@verio.net>
2000-11-28 09:05 ODBC Driver Evelio Martinez <evelio.martinez@testanet.com>
2000-11-28 09:46 ` Poul L. Christiansen <poulc@cs.auc.dk>
2003-11-07 04:03 Re: ODBC Driver Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox