pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedMS Access 97 and Pass-through SQL
6+ messages / 3 participants
[nested] [flat]
* MS Access 97 and Pass-through SQL
@ 1999-01-23 20:08 Gregg Berkholtz <gregg@gbcomputers.com>
0 siblings, 1 reply; 6+ messages in thread
From: Gregg Berkholtz @ 1999-01-23 20:08 UTC (permalink / raw)
To: Access Developer listserv <accessd@mtgroup.com>; pgsql-interfaces
Does anyone know what pass-through SQL statements cannot be used with
PostgreSQL connections via ODBC.
I am running PostgreSQL 6.4.2.
Thanks greatly!
Gregg Berkholtz
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] MS Access 97 and Pass-through SQL
@ 1999-01-23 23:39 David Hartwig <daybee@bellatlantic.net>
0 siblings, 2 replies; 6+ messages in thread
From: David Hartwig @ 1999-01-23 23:39 UTC (permalink / raw)
To: Gregg Berkholtz <gregg@gbcomputers.com>; Access Developer listserv <accessd@mtgroup.com>; pgsql-interfaces
Any legal PostgreSQL statement(s) should work. However, I don't think
multiple resultset are allowed. The big thing is that you are avoiding the
MS Jet Engine. This will allow you access any non-standard functions and
syntax available to PostgreSQL.
If you don't know, Jet has many benifits. Like performing outer joins and
cross-tabulation.
----- Original Message -----
From: Gregg Berkholtz <gregg@gbcomputers.com>
To: Access Developer listserv <accessd@mtgroup.com>; Postgres interface list
<pgsql-interfaces@postgreSQL.org>
Sent: Saturday, January 23, 1999 3:08 PM
Subject: [INTERFACES] MS Access 97 and Pass-through SQL
>Does anyone know what pass-through SQL statements cannot be used with
>PostgreSQL connections via ODBC.
>I am running PostgreSQL 6.4.2.
>
>Thanks greatly!
>
>Gregg Berkholtz
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] MS Access 97 and Pass-through SQL
@ 1999-01-23 23:59 Gregg Berkholtz <gregg@gbcomputers.com>
parent: David Hartwig <daybee@bellatlantic.net>
1 sibling, 0 replies; 6+ messages in thread
From: Gregg Berkholtz @ 1999-01-23 23:59 UTC (permalink / raw)
To: David Hartwig <daybee@bellatlantic.net>; +Cc: Access Developer listserv <accessd@mtgroup.com>; pgsql-interfaces
I was aware of the jet engine, but it is so _SLOW_ that I was hoping to maybe
find a way to speed it up. The only thing that I could think of was
pass-through... Which, of course no longer allowed me to do most joins. :-(
I am just beginning to learn SQL and have been given this project to work on,
not knowing that much about SQL has forced me to build the queries in the
builder and then convert them to an SQL pass-through.
Does anyone know of an equivalent SQL statement/syntax that I could use instead
of INNER JOINS with PostgreSQL.
This is the statement as it comes out of Access (what it is supposed to do is
query the database getting the relevant information about all open Trouble
Tickets from multiple fields):
SELECT tblTroubTick.troubTickID, tblTroubTick.troubTickNum,
[tblClientInfo]![firstName] & " " & [tblClientInfo]![lastName] AS
concatTblClientTbl, tblTroubTick.troubTickOpenDate,
tblTroubTickProblem.troubTickProblem, tblPriority.priority,
tblTroubTick.hoursInvested, [tblSupportPersonelInfo]![firstName] & " " &
[tblSupportPersonelInfo]![lastName] AS concatTblSupportPersonell,
tblTroubTick.troubTickLastUpdate, tblTroubTick.assetID,
tblTroubTickStatus.troubTickStatus
FROM tblSupportPersonelInfo INNER JOIN (tblPriority INNER JOIN
(tblTroubTickProblem INNER JOIN (tblClientLocation INNER JOIN (tblClientInfo
INNER JOIN (tblTroubTickStatus INNER JOIN tblTroubTick ON
tblTroubTickStatus.troubTickStatusID = tblTroubTick.troubTickStatusID) ON
tblClientInfo.clientID = tblTroubTick.clientID) ON tblClientLocation.locationID
= tblClientInfo.locationID) ON tblTroubTickProblem.troubTickProblemID =
tblTroubTick.troubTickProblemID) ON tblPriority.priorityID =
tblTroubTick.priorityID) ON tblSupportPersonelInfo.supportPersID =
tblTroubTick.supportPersID
WHERE (((tblTroubTickStatus.troubTickStatus) Not In ("CLOSED")));
Thanks for the help!
Gregg Berkholtz
David Hartwig wrote:
> Any legal PostgreSQL statement(s) should work. However, I don't think
> multiple resultset are allowed. The big thing is that you are avoiding the
> MS Jet Engine. This will allow you access any non-standard functions and
> syntax available to PostgreSQL.
>
> If you don't know, Jet has many benifits. Like performing outer joins and
> cross-tabulation.
>
> ----- Original Message -----
> From: Gregg Berkholtz <gregg@gbcomputers.com>
> To: Access Developer listserv <accessd@mtgroup.com>; Postgres interface list
> <pgsql-interfaces@postgreSQL.org>
> Sent: Saturday, January 23, 1999 3:08 PM
> Subject: [INTERFACES] MS Access 97 and Pass-through SQL
>
> >Does anyone know what pass-through SQL statements cannot be used with
> >PostgreSQL connections via ODBC.
> >I am running PostgreSQL 6.4.2.
> >
> >Thanks greatly!
> >
> >Gregg Berkholtz
> >
> >
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] MS Access 97 and Pass-through SQL
@ 1999-01-24 02:05 David Hartwig <daybee@bellatlantic.net>
0 siblings, 0 replies; 6+ messages in thread
From: David Hartwig @ 1999-01-24 02:05 UTC (permalink / raw)
To: Gregg Berkholtz <gregg@gbcomputers.com>; +Cc: Access Developer listserv <accessd@mtgroup.com>; pgsql-interfaces
Nice query. :) PostgreSQL supports inner joins logicly, but not
using this INNER JOIN syntax. Perhaps in 6.5. The Jet, as you probably
know, rewrites this query w/o the INNER JOIN stuff.\
Here is something you can try to speed up your query. I don't have M$
Access handy to recall the exact place this is set. But, there is a place
where you can set the property of a query to be a "snapshot" instead of
"dynaset". ( I think a right button click above the query definition.)
If you are not aware of what is happening with a dynaset vs a snapshot, I
strongly recommend reading up on them.
Also, I suggest you turn on the CommLog and keep track of the actual
queries that are being generated. The difference between a dynaset and a
snapshot may interest you. It may also help you in determiming where
bottlenecks exist - by cut/pasting the generated queries directly into the
psql monitor.
----- Original Message -----
From: Gregg Berkholtz <gregg@gbcomputers.com>
To: David Hartwig <daybee@bellatlantic.net>
Cc: Access Developer listserv <accessd@mtgroup.com>; Postgres interface list
<pgsql-interfaces@postgreSQL.org>
Sent: Saturday, January 23, 1999 6:59 PM
Subject: Re: [INTERFACES] MS Access 97 and Pass-through SQL
>I was aware of the jet engine, but it is so _SLOW_ that I was hoping to
maybe
>find a way to speed it up. The only thing that I could think of was
>pass-through... Which, of course no longer allowed me to do most joins. :-(
>
>I am just beginning to learn SQL and have been given this project to work
on,
>not knowing that much about SQL has forced me to build the queries in the
>builder and then convert them to an SQL pass-through.
>
>Does anyone know of an equivalent SQL statement/syntax that I could use
instead
>of INNER JOINS with PostgreSQL.
>
>This is the statement as it comes out of Access (what it is supposed to do
is
>query the database getting the relevant information about all open Trouble
>Tickets from multiple fields):
>
>SELECT tblTroubTick.troubTickID, tblTroubTick.troubTickNum,
>[tblClientInfo]![firstName] & " " & [tblClientInfo]![lastName] AS
>concatTblClientTbl, tblTroubTick.troubTickOpenDate,
>tblTroubTickProblem.troubTickProblem, tblPriority.priority,
>tblTroubTick.hoursInvested, [tblSupportPersonelInfo]![firstName] & " " &
>[tblSupportPersonelInfo]![lastName] AS concatTblSupportPersonell,
>tblTroubTick.troubTickLastUpdate, tblTroubTick.assetID,
>tblTroubTickStatus.troubTickStatus
>FROM tblSupportPersonelInfo INNER JOIN (tblPriority INNER JOIN
>(tblTroubTickProblem INNER JOIN (tblClientLocation INNER JOIN
(tblClientInfo
>INNER JOIN (tblTroubTickStatus INNER JOIN tblTroubTick ON
>tblTroubTickStatus.troubTickStatusID = tblTroubTick.troubTickStatusID) ON
>tblClientInfo.clientID = tblTroubTick.clientID) ON
tblClientLocation.locationID
>= tblClientInfo.locationID) ON tblTroubTickProblem.troubTickProblemID =
>tblTroubTick.troubTickProblemID) ON tblPriority.priorityID =
>tblTroubTick.priorityID) ON tblSupportPersonelInfo.supportPersID =
>tblTroubTick.supportPersID
>WHERE (((tblTroubTickStatus.troubTickStatus) Not In ("CLOSED")));
>
>
>Thanks for the help!
>
>Gregg Berkholtz
>
>David Hartwig wrote:
>
>> Any legal PostgreSQL statement(s) should work. However, I don't think
>> multiple resultset are allowed. The big thing is that you are avoiding
the
>> MS Jet Engine. This will allow you access any non-standard functions and
>> syntax available to PostgreSQL.
>>
>> If you don't know, Jet has many benifits. Like performing outer joins
and
>> cross-tabulation.
>>
>> ----- Original Message -----
>> From: Gregg Berkholtz <gregg@gbcomputers.com>
>> To: Access Developer listserv <accessd@mtgroup.com>; Postgres interface
list
>> <pgsql-interfaces@postgreSQL.org>
>> Sent: Saturday, January 23, 1999 3:08 PM
>> Subject: [INTERFACES] MS Access 97 and Pass-through SQL
>>
>> >Does anyone know what pass-through SQL statements cannot be used with
>> >PostgreSQL connections via ODBC.
>> >I am running PostgreSQL 6.4.2.
>> >
>> >Thanks greatly!
>> >
>> >Gregg Berkholtz
>> >
>> >
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] MS Access 97 and Pass-through SQL
@ 1999-01-24 09:05 Michael Meskes <Michael_Meskes@usa.net>
parent: Gregg Berkholtz <gregg@gbcomputers.com>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Meskes @ 1999-01-24 09:05 UTC (permalink / raw)
To: pgsql-interfaces
On Sat, Jan 23, 1999 at 12:08:16PM -0800, Gregg Berkholtz wrote:
> Does anyone know what pass-through SQL statements cannot be used with
> PostgreSQL connections via ODBC.
> I am running PostgreSQL 6.4.2.
I'm not sure you can run pass-through queries that return something. I think
it's like an execute immediate in embedded SQL, i.e. no return values.
Also be very careful when trying to access this table from C or Basic.
Access itself passes the statement through to the original DB, but when
programmin the jet engine the statement is passed through to Access! So
don't be surprised if your performance goes downhill.
Michael
--
Michael Meskes | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire!
Tel.: (+49) 2431/72651 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net | Use PostgreSQL!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] MS Access 97 and Pass-through SQL
@ 1999-01-24 09:08 Michael Meskes <Michael_Meskes@usa.net>
parent: David Hartwig <daybee@bellatlantic.net>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Meskes @ 1999-01-24 09:08 UTC (permalink / raw)
To: pgsql-interfaces
On Sat, Jan 23, 1999 at 06:39:13PM -0500, David Hartwig wrote:
> If you don't know, Jet has many benifits. Like performing outer joins and
> cross-tabulation.
At the cost of performance. The Jet does this by reading the whole table and
performing the query itself without having a good optimizer.
Michael
--
Michael Meskes | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire!
Tel.: (+49) 2431/72651 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net | Use PostgreSQL!
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1999-01-24 09:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-23 20:08 MS Access 97 and Pass-through SQL Gregg Berkholtz <gregg@gbcomputers.com>
1999-01-24 09:05 ` Michael Meskes <Michael_Meskes@usa.net>
1999-01-23 23:39 Re: [INTERFACES] MS Access 97 and Pass-through SQL David Hartwig <daybee@bellatlantic.net>
1999-01-23 23:59 ` Gregg Berkholtz <gregg@gbcomputers.com>
1999-01-24 09:08 ` Michael Meskes <Michael_Meskes@usa.net>
1999-01-24 02:05 Re: [INTERFACES] MS Access 97 and Pass-through SQL David Hartwig <daybee@bellatlantic.net>
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