pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
12+ messages / 5 participants
[nested] [flat]
* Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-24 14:36 Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 12+ messages in thread
From: Byron Nikolaidis @ 1998-07-24 14:36 UTC (permalink / raw)
To: Bruce Tong <zztong@laxmi.ev.net>; +Cc: PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
Bruce Tong wrote:
>
>
> 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:
>
I finally figured out what the heck is going on with this order by
problem in MS Access. I tested your query on a computer that has older
versions of the Microsoft Jet db engine on it and guess what? It does
the same thing you described! The reason that I used to have the
problem and it miraculously went away was because I installed Visual
Basic 5.0 and it upgraded the MS Jet dll's.
Your right about the snapshot. It still tries to throw the order by
clause in there.
But, here's a simple workaround that you can easily try. When looking
at your query in Access, right click on the fields area and select
"Totals". This will add a group by clause but it won't have any real
effect, other than it will let your query run!
So, there are two things you must do to fully fix the order by problem:
1. Upgrade the MS Jet DLL (this is the real problem here)
2. Get the patch for the order by/group by from our web site (it will be
in postgres 6.4 also)
Interestingly, even if you get the postgres patch like we have here, the
older MS Jet engine will still not let you sort the columns the way you
want, because it always throws that same old order by in there.
The working version of \windows\system\MSJET35.DLL is 3.51.0623.4
The broken version I tested was 3.50.3602.4
I'm not sure at what release they fixed this problem.
I will dig around on MS web site.
Does anyone know if its OK to redistribute the MS JET DB Engine DLL,
like with our ODBC driver for example??? (That thing is big too,
1MB!!!)
For that matter, is there a simple way to get it from Microsoft?
Byron
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-24 16:00 Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 12+ messages in thread
From: Byron Nikolaidis @ 1998-07-24 16:00 UTC (permalink / raw)
To: Bruce Tong <zztong@laxmi.ev.net>; +Cc: pgsql-interfaces; PostgreSQL General <pgsql-general@postgreSQL.org>
Bruce Tong wrote:
>
> > The working version of \windows\system\MSJET35.DLL is 3.51.0623.4
> > The broken version I tested was 3.50.3602.4
> > I'm not sure at what release they fixed this problem.
> >
> > I will dig around on MS web site.
> >
> > Does anyone know if its OK to redistribute the MS JET DB Engine DLL,
> > like with our ODBC driver for example??? (That thing is big too,
> > 1MB!!!)
> >
> > For that matter, is there a simple way to get it from Microsoft?
>
Yes, I found the updated jet database engine on Microsoft site.
http://support.microsoft.com/download/support/mslfiles/Jet35upd.exe
> I don't know the answers to these questions, but if you're just doing this
> to save me time there's no reason to put yourself out as that's something
> I should do for myself. If you're planning on distributing the DLLs along
> with the driver, that sounds like a great idea, assuming they'll let you.
>
Well, this Access order by problem has bothered us and many others that
use the odbc front end with postgres for quite some time. Its a great
relief to finally have a clear understanding of the problem!
I think we will probably just include a link to the above site off of
our site and mention this problem.
> I take it I don't have to gather the log information you requested?
>
No, thanks anyway.
Byron
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-24 19:14 Richard Lynch <lynch@lscorp.com>
0 siblings, 2 replies; 12+ messages in thread
From: lynch@lscorp.com @ 1998-07-24 19:14 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; Bruce Tong <zztong@laxmi.ev.net>; +Cc: PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
At 10:36 AM 7/24/98, Byron Nikolaidis wrote:
>Bruce Tong wrote:
>> 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:
>I finally figured out what the heck is going on with this order by
>problem in MS Access. I tested your query on a computer that has older
>versions of the Microsoft Jet db engine on it and guess what? It does
>the same thing you described! The reason that I used to have the
>problem and it miraculously went away was because I installed Visual
>Basic 5.0 and it upgraded the MS Jet dll's.
FWIW:
I seldom do much more than add the tables in MS Access's <OPINION> stupid
</OPINION> query design thingy. I immediately use the menu to view SQL and
just type the SQL I want. Obviously, different folks think different ways,
and maybe you actually understand and like that MS Access point and click
interface that I hate, but I want to be sure you're at least aware that if
it sticks an ORDER BY in there that you never asked for, you can just rip
the damn thing out in the SQL view and then run the query... At least,
that's how I make it give me the SQL I really want, instead of the SQL it
thinks I want, which it never gets right anyway.
Hope that helps.
--
--
-- "TANSTAAFL" Rich lynch@lscorp.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-24 19:43 Byron Nikolaidis <byronn@insightdist.com>
parent: Richard Lynch <lynch@lscorp.com>
1 sibling, 0 replies; 12+ messages in thread
From: Byron Nikolaidis @ 1998-07-24 19:43 UTC (permalink / raw)
To: Richard Lynch <lynch@lscorp.com>; +Cc: Bruce Tong <zztong@laxmi.ev.net>; PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
Richard Lynch wrote:
>
>
> FWIW:
>
> I seldom do much more than add the tables in MS Access's <OPINION> stupid
> </OPINION> query design thingy. I immediately use the menu to view SQL and
> just type the SQL I want. Obviously, different folks think different ways,
> and maybe you actually understand and like that MS Access point and click
> interface that I hate, but I want to be sure you're at least aware that if
> it sticks an ORDER BY in there that you never asked for, you can just rip
> the damn thing out in the SQL view and then run the query... At least,
> that's how I make it give me the SQL I really want, instead of the SQL it
> thinks I want, which it never gets right anyway.
>
> Hope that helps.
>
Thanks, I would probably agree with you, but it makes no difference if I
like it or not, its our customers who want the graphical interface and
FINALLY, we have a fix for it! They are most likely not the types who
are gonna drop down to the pound sign and type some SQL into the
monitor!
And BTW, if you look at the SQL, you wouldn't even see the ORDER BY
thing because this clause is placed on the "keyset" query, which is the
collection of keys it uses to dynamically access rows in the query
result (i.e. the Dynaset).
Actually, I understand your hate of the Microsoft thing, but I only
recently realized the benefit and power of this Jet database engine
thing that Access uses. For example, it can update the results of a
query that has several tables joined, right while your looking at the
row on the screen, automatically translating the SQL2 syntax (i.e.,
INNER JOIN ... ON ...) into standard SQL that Postgres can use.
Byron
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-24 20:26 Bruce Tong <zztong@laxmi.ev.net>
parent: Richard Lynch <lynch@lscorp.com>
1 sibling, 1 reply; 12+ messages in thread
From: Bruce Tong @ 1998-07-24 20:26 UTC (permalink / raw)
To: Richard Lynch <lynch@lscorp.com>; +Cc: Byron Nikolaidis <byronn@insightdist.com>; PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
> FWIW:
>
> I seldom do much more than add the tables in MS Access's <OPINION> stupid
> </OPINION> query design thingy. I immediately use the menu to view SQL and
> just type the SQL I want. Obviously, different folks think different ways,
> and maybe you actually understand and like that MS Access point and click
> interface that I hate, but I want to be sure you're at least aware that if
> it sticks an ORDER BY in there that you never asked for, you can just rip
> the damn thing out in the SQL view and then run the query... At least,
> that's how I make it give me the SQL I really want, instead of the SQL it
> thinks I want, which it never gets right anyway.
Since I'm learning SQL in my spare time, I tend to use these feature in MS
Access and PgAccess to point me in the right direction or sometimes
confirm, or deny my assertions.
I like psql, but its not the kind of tool which suggests other
alternatives. It just says "this part is bogus." That's fine, but when I
fail to get it right after a dozen attempts, its nice to let something
else take a stab at it.
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] 12+ messages in thread
* Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-24 20:47 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Bruce Tong <zztong@laxmi.ev.net>
0 siblings, 1 reply; 12+ messages in thread
From: Bruce Momjian @ 1998-07-24 20:47 UTC (permalink / raw)
To: zztong@laxmi.ev.net; +Cc: lynch@lscorp.com; byronn@insightdist.com; pgsql-general@postgreSQL.org; pgsql-interfaces
>
> Since I'm learning SQL in my spare time, I tend to use these feature in MS
> Access and PgAccess to point me in the right direction or sometimes
> confirm, or deny my assertions.
>
> I like psql, but its not the kind of tool which suggests other
> alternatives. It just says "this part is bogus." That's fine, but when I
> fail to get it right after a dozen attempts, its nice to let something
> else take a stab at it.
Please tell use what else psql can tell the user. We have \h and \d
commands.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-24 21:08 Bruce Tong <zztong@laxmi.ev.net>
parent: Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Bruce Tong @ 1998-07-24 21:08 UTC (permalink / raw)
To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: lynch@lscorp.com; byronn@insightdist.com; pgsql-general@postgreSQL.org; pgsql-interfaces
> > Since I'm learning SQL in my spare time, I tend to use these feature in MS
> > Access and PgAccess to point me in the right direction or sometimes
> > confirm, or deny my assertions.
> >
> > I like psql, but its not the kind of tool which suggests other
> > alternatives. It just says "this part is bogus." That's fine, but when I
> > fail to get it right after a dozen attempts, its nice to let something
> > else take a stab at it.
>
> Please tell use what else psql can tell the user. We have \h and \d
> commands.
Example:
zztong=> SELECT * FROM school SORT DESC BY school_lname;
ERROR: parser: parse error at or near "desc"
Follow this with 10 more seemingly reasonable attempts at guessing the
correct syntax which all fail. Then I'm off to the books, or maybe I just
run PgAccess and quickly design a view soley to see the SQL.
The problem is not with psql, its with me.
I mean if you want to have psql attempt to suggest the correct syntax
based on my mistakes, I won't complain, but I really don't think it is
needed and I'm certain I would become annoyed with the feature as I became
more experienced.
I have little need for generating SQL "on the fly" as I'm just putting it
into a textfile to be run through psql from a makefile, or I'm burrying it
in a program somewhere. Thus I spend little time with SQL, so I'm learning
it slowly. I don't try to memorize things as I prefer to pick them up via
usage and I try to keep lots of books around.
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] 12+ messages in thread
* Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-24 22:52 Richard Lynch <lynch@lscorp.com>
0 siblings, 1 reply; 12+ messages in thread
From: lynch@lscorp.com @ 1998-07-24 22:52 UTC (permalink / raw)
To: Bruce Tong <zztong@laxmi.ev.net>; +Cc: Byron Nikolaidis <byronn@insightdist.com>; PostgreSQL General <pgsql-general@postgreSQL.org>; pgsql-interfaces
At 4:26 PM 7/24/98, Bruce Tong wrote:
>Since I'm learning SQL in my spare time, I tend to use these feature in MS
>Access and PgAccess to point me in the right direction or sometimes
>confirm, or deny my assertions.
Absolutely. For new (to me) SQL, I point and click at what I think MS
would want me to point and click at, and then look at the SQL and rip out
the crap I know they got wrong, and then run it to see what they got wrong
in the new stuff, and then mess around with bits and pieces of what they
came up with until it's right.
>I like psql, but its not the kind of tool which suggests other
>alternatives. It just says "this part is bogus." That's fine, but when I
>fail to get it right after a dozen attempts, its nice to let something
>else take a stab at it.
I especially hate that it doesn't even say "this part is bogus" sometimes.
It says "error near 'from'". *WHICH* from. I could easily have 3 or 4 in
even the simplest query. And god forbid it says "error near ','". How
useless is that?!
Okay, enough bitching, I'll be positive:
psql would be infinitely nicer if it dumped out the query and indicated the
EXACT location of the problem.
--
--
-- "TANSTAAFL" Rich lynch@lscorp.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-24 23:12 Cary B. O'Brien <cobrien@access.digex.net>
parent: Bruce Tong <zztong@laxmi.ev.net>
0 siblings, 1 reply; 12+ messages in thread
From: Cary B. O'Brien @ 1998-07-24 23:12 UTC (permalink / raw)
To: zztong@laxmi.ev.net; +Cc: maillist@candle.pha.pa.us; lynch@lscorp.com; byronn@insightdist.com; pgsql-general@postgreSQL.org; pgsql-interfaces
> > > Since I'm learning SQL in my spare time, I tend to use these feature in MS
> > > Access and PgAccess to point me in the right direction or sometimes
> > > confirm, or deny my assertions.
> > >
> > > I like psql, but its not the kind of tool which suggests other
> > > alternatives. It just says "this part is bogus." That's fine, but when I
> > > fail to get it right after a dozen attempts, its nice to let something
> > > else take a stab at it.
> >
> > Please tell use what else psql can tell the user. We have \h and \d
> > commands.
>
> Example:
>
> zztong=> SELECT * FROM school SORT DESC BY school_lname;
> ERROR: parser: parse error at or near "desc"
>
> Follow this with 10 more seemingly reasonable attempts at guessing the
> correct syntax which all fail. Then I'm off to the books, or maybe I just
> run PgAccess and quickly design a view soley to see the SQL.
>
Hmm Did you try...
cary=> \h select
Command: select
Description: retrieve tuples
Syntax:
select [distinct on <attr>] <expr1> [as <attr1>], ... <exprN> [as <attrN>]
[into [table] <class_name>]
[from <from_list>]
[where <qual>]
[group by <group_list>]
[order by <attr1> [ASC | DESC] [using <op1>], ... <attrN> ]
[union [all] select ...];
That's pretty good help if you ask me, and shows that there is
no BY keyword in the order by clause.
Psql is pretty good, especially if you have the readline support or
run it from emacs. With the new query cancel it will be even better.
[snip]
-- cary
> The problem is not with psql, its with me.
>
> I mean if you want to have psql attempt to suggest the correct syntax
> based on my mistakes, I won't complain, but I really don't think it is
> needed and I'm certain I would become annoyed with the feature as I became
> more experienced.
>
> I have little need for generating SQL "on the fly" as I'm just putting it
> into a textfile to be run through psql from a makefile, or I'm burrying it
> in a program somewhere. Thus I spend little time with SQL, so I'm learning
> it slowly. I don't try to memorize things as I prefer to pick them up via
> usage and I try to keep lots of books around.
>
>
> 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] 12+ messages in thread
* Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-24 23:58 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Cary B. O'Brien <cobrien@access.digex.net>
0 siblings, 0 replies; 12+ messages in thread
From: Bruce Momjian @ 1998-07-24 23:58 UTC (permalink / raw)
To: cobrien@access.digex.net; +Cc: zztong@laxmi.ev.net; lynch@lscorp.com; byronn@insightdist.com; pgsql-general@postgreSQL.org; pgsql-interfaces
> Hmm Did you try...
>
> cary=> \h select
> Command: select
> Description: retrieve tuples
> Syntax:
> select [distinct on <attr>] <expr1> [as <attr1>], ... <exprN> [as <attrN>]
> [into [table] <class_name>]
> [from <from_list>]
> [where <qual>]
> [group by <group_list>]
> [order by <attr1> [ASC | DESC] [using <op1>], ... <attrN> ]
> [union [all] select ...];
I am working on uppercasing the keywords, so it will be much clearer.
The use of <> is really bogus. I will look into reporting the place of
the error, as well as the word.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-25 00:18 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Richard Lynch <lynch@lscorp.com>
0 siblings, 1 reply; 12+ messages in thread
From: Bruce Momjian @ 1998-07-25 00:18 UTC (permalink / raw)
To: lynch@lscorp.com; +Cc: hackers@postgreSQL.org
> I especially hate that it doesn't even say "this part is bogus" sometimes.
> It says "error near 'from'". *WHICH* from. I could easily have 3 or 4 in
> even the simplest query. And god forbid it says "error near ','". How
> useless is that?!
>
> Okay, enough bitching, I'll be positive:
> psql would be infinitely nicer if it dumped out the query and indicated the
> EXACT location of the problem.
>
New 6.4 psql help will show:
test=> \h select
Command: select
Description: retrieve tuples
Syntax:
SELECT [DISTINCT [ON attrN]] expr1 [AS attr1], ...exprN
[INTO [TABLE] class_name]
[FROM from_list]
[WHERE qual]
[GROUP BY group_list]
[HAVING having_clause]
[ORDER BY attr1 [ASC|DESC] [USING op1], ...attrN ]
[UNION [ALL] SELECT ...];
Removed <> around user-supplied values, and uppercase the reserved words
to make things clear. I don't think there is a need to do this on the
manual pages because we have bolding. Comments?
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [HACKERS] Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
@ 1998-07-25 01:00 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Bruce Momjian @ 1998-07-25 01:00 UTC (permalink / raw)
To: maillist@candle.pha.pa.us; +Cc: lynch@lscorp.com; hackers@postgreSQL.org
Now it is:
> New 6.4 psql help will show:
>
> test=> \h select
> Command: select
> Description: retrieve tuples
> Syntax:
> SELECT [DISTINCT [ON attrN]] expr1 [AS attr1], ...exprN
> [INTO [TABLE] class_name]
> [FROM from_list]
> [WHERE qual]
> [GROUP BY group_list]
> [HAVING having_clause]
> [ORDER BY attr1 [ASC|DESC] [USING op1], ...attrN ]
> [UNION [ALL] SELECT ...];
>
>
> Removed <> around user-supplied values, and uppercase the reserved words
> to make things clear. I don't think there is a need to do this on the
> manual pages because we have bolding. Comments?
>
>
> --
> Bruce Momjian | 830 Blythe Avenue
> maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
> + If your life is a hard drive, | (610) 353-9879(w)
> + Christ can be your backup. | (610) 853-3000(h)
>
>
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~1998-07-25 01:00 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-07-24 14:36 Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!! Byron Nikolaidis <byronn@insightdist.com>
1998-07-24 16:00 Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!! Byron Nikolaidis <byronn@insightdist.com>
1998-07-24 19:14 Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!! Richard Lynch <lynch@lscorp.com>
1998-07-24 19:43 ` Byron Nikolaidis <byronn@insightdist.com>
1998-07-24 20:26 ` Bruce Tong <zztong@laxmi.ev.net>
1998-07-24 20:47 ` Bruce Momjian <maillist@candle.pha.pa.us>
1998-07-24 21:08 ` Bruce Tong <zztong@laxmi.ev.net>
1998-07-24 23:12 ` Cary B. O'Brien <cobrien@access.digex.net>
1998-07-24 23:58 ` Bruce Momjian <maillist@candle.pha.pa.us>
1998-07-24 22:52 Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!! Richard Lynch <lynch@lscorp.com>
1998-07-25 00:18 ` Bruce Momjian <maillist@candle.pha.pa.us>
1998-07-25 01:00 ` Bruce Momjian <maillist@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