Thread: Difference between ODBCdirect Connection and Pass-through-query

Difference between ODBCdirect Connection and Pass-through-query

From

DI Hasenöhrl

Date:

28 March 2002, 13:31:16

Hi,

I use MsAccess97 as frontend and Postgresql as backend and till now I thought, that there is no difference between a pass-through-query and a query,that I send via ODBCdirect connection to postgresql. But now I have the followig miracle.

If I make a pass-through-query with LEFT JOINs, I get the correct rows, but by developing the syntax via VBA and transferring this code by an ODBCdirect connection, I get only the rows of a normal INNER JOIN.

This is the code for my ODBCdirect connection:

    'ODBC Direct herstellen
    Set odbcWksp = CreateWorkspace("odbcWkspc", "Ina", "Mai123", dbUseODBC)
    Set odbcConn = odbcWksp.OpenConnection("odbcConn", dbDriverNoPrompt, ,

"ODBC;DATABASE=WAWI;UID=ina;PWD=mai123;DSN=WAWI;")

and this is the code for my query,I want to send to the server:

    Dim qry As QueryDef
    Dim strSQL As String

    Set qry = odbcConn.CreateQueryDef("")
    strSQL = "SELECT artikel.a_nr,artikel.a_bez1,artikel.a_bez2,artikel.kl_ean,artikel.ws_nr,artikel.wg_nr, " & _
              "vartikellieferantbez.lief_nr,vartikellieferantbez.kl_kurzbez " & _
              "FROM Artikel " & _
              "LEFT JOIN vartikellieferantbez ON (artikel.a_nr = vartikellieferantbez.a_nr AND vartikellieferantbez.f_nr=1 ) " & _
              "WHERE (((artikel.a_nr)=-1) AND ((artikel.kl_ean)='HARD'));"

    qry.SQL = strSQL

    Me.RecordSource = qry.SQL

Recordsource is the datasource for a report.

I can't use pass-through-queries, because the select statement depends on variables.

Please, can someone show me the way to transferr this query directly to the server.

Many thanks in advance

Irina

Re: Difference between ODBCdirect Connection and Pass-through-query

From

Cedar Cox

Date:

28 March 2002, 13:56:39

> This is the code for my ODBCdirect connection:
>     'ODBC Direct herstellen
>     Set odbcWksp = CreateWorkspace("odbcWkspc", "Ina", "Mai123", dbUseODBC)
>     Set odbcConn = odbcWksp.OpenConnection("odbcConn", dbDriverNoPrompt, ,
>                                          "ODBC;DATABASE=WAWI;UID=ina;PWD=mai123;DSN=WAWI;")

Hey.. that looks like something I wrote! :)

>     strSQL = "SELECT artikel.a_nr,artikel.a_bez1,artikel.a_bez2,artikel.kl_ean,artikel.ws_nr,artikel.wg_nr, " & _
>               "vartikellieferantbez.lief_nr,vartikellieferantbez.kl_kurzbez " & _
>               "FROM Artikel " & _
>               "LEFT JOIN vartikellieferantbez ON (artikel.a_nr = vartikellieferantbez.a_nr AND
vartikellieferantbez.f_nr=1) " & _ 
>               "WHERE (((artikel.a_nr)=-1) AND ((artikel.kl_ean)='HARD'));"

What about:

  Dim rst As Recordset
  Set rst = odbcConn.OpenRecordset(strSQL, dbOpenSnapshot, dbExecDirect)

>     Me.RecordSource = qry.SQL

This part I'm not sure about (it's been a long time away from Access).
Can you just set Me.RecordSource = rst ?  If so, forget making a query.
Also, keep in mind that this would give you a snapshot recordset.  Change
the args to OpenRecordset as necessary.

-Cedar

Re: Difference between ODBCdirect Connection and Pass-through-query

From

DI Hasenöhrl

Date:

28 March 2002, 14:20:45

Hi Cedar,

Thanks for your response.

I didn't find out, how to make it work with recordsets, I can do it only with a query ;-)

Me.Recordsource=rst doesn't work

maybe someone else knows what I have to do, in order to work with a recordset

Kind regards,

Irina

----- Original Message -----
From: Cedar Cox
To: DI Hasenöhrl
Cc: pgsql-odbc@postgresql.org
Sent: Thursday, March 28, 2002 5:54 PM
Subject: Re: [ODBC] Difference between ODBCdirect Connection and Pass-through-query

> This is the code for my ODBCdirect connection:
>     'ODBC Direct herstellen
>     Set odbcWksp = CreateWorkspace("odbcWkspc", "Ina", "Mai123", dbUseODBC)
>     Set odbcConn = odbcWksp.OpenConnection("odbcConn", dbDriverNoPrompt, ,
>                                          "ODBC;DATABASE=WAWI;UID=ina;PWD=mai123;DSN=WAWI;")

Hey.. that looks like something I wrote! :)

>     strSQL = "SELECT artikel.a_nr,artikel.a_bez1,artikel.a_bez2,artikel.kl_ean,artikel.ws_nr,artikel.wg_nr, " & _
>               "vartikellieferantbez.lief_nr,vartikellieferantbez.kl_kurzbez " & _
>               "FROM Artikel " & _
>               "LEFT JOIN vartikellieferantbez ON (artikel.a_nr = vartikellieferantbez.a_nr AND vartikellieferantbez.f_nr=1 ) " & _
>               "WHERE (((artikel.a_nr)=-1) AND ((artikel.kl_ean)='HARD'));"

What about:

Dim rst As Recordset
Set rst = odbcConn.OpenRecordset(strSQL, dbOpenSnapshot, dbExecDirect)

>     Me.RecordSource = qry.SQL

This part I'm not sure about (it's been a long time away from Access).
Can you just set Me.RecordSource = rst ? If so, forget making a query.
Also, keep in mind that this would give you a snapshot recordset. Change
the args to OpenRecordset as necessary.

-Cedar

'infinity'::timestamp not translated correctly using ODBC v7.01.00.10 ?

From

Kristis Makris

Date:

28 March 2002, 21:47:46

Hello,

I'm issuing the following SQL statement through psql:

mydb=# select 'infinity'::timestamp as reply;
  reply
----------
 infinity
(1 row)


When I try to issue the same statement through VC++ in the following
piece of code I get back "2002-03-28 00:00:00". Is infinity somehow
effectively translated into now()::date || "00:00:00" by the driver?

if (db.CanTransact())
    db.BeginTrans();

CRecordset rsNewRecords(&db);

strStmt = "SELECT 'infinity'::timestamp as reply";
rsNewRecords.Open(CRecordset::forwardOnly, strStmt);

while (!rsNewRecords.IsEOF())
    {
        rsNewRecords.GetFieldValue("reply", retval);
        rsNewRecords.MoveNext();
    }


I'm actually calling a backend function that attempts to make a date
estimate, and happens to return "infinity" in some cases, but so far
I've determined that 'infinity' is never reported as "infinity".

Thanks,
-Kristis

Re: 'infinity'::timestamp not translated correctly using ODBCv7.01.00.10 ?

From

"Hiroshi Inoue"

Date:

29 March 2002, 14:29:37

> -----Original Message-----
> From: Kristis Makris
>
> Hello,
>
> I'm issuing the following SQL statement through psql:
>
> mydb=# select 'infinity'::timestamp as reply;
>   reply
> ----------
>  infinity
> (1 row)
>
>
> When I try to issue the same statement through VC++ in the following
> piece of code I get back "2002-03-28 00:00:00". Is infinity somehow
> effectively translated into now()::date || "00:00:00" by the driver?

There's no concept of inifinity in the timestamp type of ODBC(and in
many DBMSs).

regards,
Hiroshi Inoue

Re: 'infinity'::timestamp not translated correctly using

From

Kristis Makris

Date:

29 March 2002, 14:38:47

On Fri, 2002-03-29 at 09:29, Hiroshi Inoue wrote:
> > -----Original Message-----
> > From: Kristis Makris
> >
> > Hello,
> >
> > I'm issuing the following SQL statement through psql:
> >
> > mydb=# select 'infinity'::timestamp as reply;
> >   reply
> > ----------
> >  infinity
> > (1 row)
> >
> >
> > When I try to issue the same statement through VC++ in the following
> > piece of code I get back "2002-03-28 00:00:00". Is infinity somehow
> > effectively translated into now()::date || "00:00:00" by the driver?
>
> There's no concept of inifinity in the timestamp type of ODBC(and in
> many DBMSs).

Since postgres does allow it as a valid value of the date datatype,
shouldn't all postgres interfaces (jdbc/odbc, etc.) support it? If this
concept shouldn't be supported, then why is it by the backend?

> regards,
> Hiroshi Inoue
>
--
---------------------------------------------------------------------
Kristis Makris                DataSoft Corporation
kristis.makris@datasoft.com        http://www.datasoft.com
(480) 763-5777 x412
---------------------------------------------------------------------

Re: Difference between ODBCdirect Connection and Pass-through-query

From

"Hiroshi Inoue"

Date:

30 March 2002, 07:00:51

-----Original Message-----
From: DI Hasenöhrl

> Hi,

> I use MsAccess97 as frontend and Postgresql as backend and till now I
thought, that there
> is no difference between a pass-through-query and a query,that I send via
ODBCdirect
> connection to postgresql. But now I have the followig miracle.

> If I make a pass-through-query with LEFT JOINs, I get the correct rows,
but by developing
> the syntax via VBA and transferring this code by an ODBCdirect connection,
I get only the
> rows of a normal INNER JOIN.

Please try the newest dll at http://w2422.nsk.ne.jp/~inoue/ .

regards,
Hiroshi Inoue

Re: 'infinity'::timestamp not translated correctly

From

Hiroshi Inoue

Date:

01 April 2002, 05:43:40

Kristis Makris wrote:
>
> On Fri, 2002-03-29 at 09:29, Hiroshi Inoue wrote:
> > >
> > > When I try to issue the same statement through VC++ in the following
> > > piece of code I get back "2002-03-28 00:00:00". Is infinity somehow
> > > effectively translated into now()::date || "00:00:00" by the driver?
> >
> > There's no concept of inifinity in the timestamp type of ODBC(and in
> > many DBMSs).
>
> Since postgres does allow it as a valid value of the date datatype,
> shouldn't all postgres interfaces (jdbc/odbc, etc.) support it? If this
> concept shouldn't be supported, then why is it by the backend?

I don't think it's a good idea to rely on PostgreSQL's
*infinity* timestamp. As I already mentioned there's no
concept in ODBC's SQL_C_(TYPE_)TIMESTAMP type. However
psqlodbc driver seems to be able to return *infinity* as
SQL_C_CHAR and I've just changed the driver. Please try
the latest driver at http://w2422.nsk.ne.jp/~inoue/.

regards,
Hiroshi Inoue

Re: 'infinity'::timestamp not translated correctly

From

Kristis Makris

Date:

02 April 2002, 14:30:33

> I don't think it's a good idea to rely on PostgreSQL's
> *infinity* timestamp. As I already mentioned there's no
> concept in ODBC's SQL_C_(TYPE_)TIMESTAMP type. However
> psqlodbc driver seems to be able to return *infinity* as
> SQL_C_CHAR and I've just changed the driver. Please try
> the latest driver at http://w2422.nsk.ne.jp/~inoue/.

I like the change. At least now there's something consistent we can rely
on to interpret as "infinity".

BTW: an hour of 24 is invalid. 23:59:59 may be a better choice.

Thanks,
-Kristis

Attachment

convert.c.patch

Re: 'infinity'::timestamp not translated correctly

From

Bruce Momjian

Date:

18 April 2002, 01:56:35

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Kristis Makris wrote:
> > I don't think it's a good idea to rely on PostgreSQL's
> > *infinity* timestamp. As I already mentioned there's no
> > concept in ODBC's SQL_C_(TYPE_)TIMESTAMP type. However
> > psqlodbc driver seems to be able to return *infinity* as
> > SQL_C_CHAR and I've just changed the driver. Please try
> > the latest driver at http://w2422.nsk.ne.jp/~inoue/.
>
> I like the change. At least now there's something consistent we can rely
> on to interpret as "infinity".
>
> BTW: an hour of 24 is invalid. 23:59:59 may be a better choice.
>
> Thanks,
> -Kristis

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: 'infinity'::timestamp not translated correctly

From

Hiroshi Inoue

Date:

18 April 2002, 02:16:54

Bruce Momjian wrote:
>
>
> Your patch has been added to the PostgreSQL unapplied patches list at:

Oh I've missed the patch, sorry.
I would apply the patch together with other fixes.

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

Re: 'infinity'::timestamp not translated correctly

From

Bruce Momjian

Date:

18 April 2002, 02:31:10

Hiroshi will apply this.  Thanks.

---------------------------------------------------------------------------

Kristis Makris wrote:
> > I don't think it's a good idea to rely on PostgreSQL's
> > *infinity* timestamp. As I already mentioned there's no
> > concept in ODBC's SQL_C_(TYPE_)TIMESTAMP type. However
> > psqlodbc driver seems to be able to return *infinity* as
> > SQL_C_CHAR and I've just changed the driver. Please try
> > the latest driver at http://w2422.nsk.ne.jp/~inoue/.
>
> I like the change. At least now there's something consistent we can rely
> on to interpret as "infinity".
>
> BTW: an hour of 24 is invalid. 23:59:59 may be a better choice.
>
> Thanks,
> -Kristis

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026