Postgres Pro
Downloads
Home   >   mailing lists

Thread: ODBC Open cursors on views

ODBC Open cursors on views

From
Shachar Shemesh
Date:
Hi all,

When trying to open a bidirectional cursor on a query that uses a view in the
from (i.e. - select * from "viewname"), the odbc driver returns an error
"Attribute ctid not found". The problem does not happen when I open a forward
only cursor.

It sounds to me like the ODBC driver uses the ctid in order to "find it's way
around". Views don't have that field, and hence the problem. Note: that
explanation was pure guesswork.

My questions are:
1. Is the above explanation correct?
2. Is this problem solveable through the ODBC driver?

       Many thanks,

            Shachar

Re: ODBC Open cursors on views

From
Hiroshi Inoue
Date:
Shachar Shemesh wrote:
>
> Hi all,
>
> When trying to open a bidirectional cursor on a query that uses a view in the
> from (i.e. - select * from "viewname"), the odbc driver returns an error
> "Attribute ctid not found". The problem does not happen when I open a forward
> only cursor.

Is the bidirectional cursor read-only ?

regards,
Hiroshi Inoue
    http://www.geocities.jp/inocchichichi/psqlodbc/

Re: ODBC Open cursors on views

From
Shachar Shemesh
Date:
Quoting Hiroshi Inoue <Inoue@tpf.co.jp>:

> Shachar Shemesh wrote:
> >
> > Hi all,
> >
> > When trying to open a bidirectional cursor on a query that uses a view
> in the
> > from (i.e. - select * from "viewname"), the odbc driver returns an
> error
> > "Attribute ctid not found". The problem does not happen when I open a
> forward
> > only cursor.
>
> Is the bidirectional cursor read-only ?
>
> regards,
> Hiroshi Inoue
>     http://www.geocities.jp/inocchichichi/psqlodbc/
>

I tried it both ways - neither worked.

This is a company I'm helping to add a backend to postgresql (they currently
support Oracle, MSsql and Access). They occasionally also do updates on views
(rare), so I guess that would come at some stage as well.

          Shachar

Re: ODBC Open cursors on views

From
Hiroshi Inoue
Date:
Shachar Shemesh wrote:
>
> Quoting Hiroshi Inoue <Inoue@tpf.co.jp>:
>
> > Shachar Shemesh wrote:
> > >
> > > Hi all,
> > >
> > > When trying to open a bidirectional cursor on a query that uses a view
> > in the
> > > from (i.e. - select * from "viewname"), the odbc driver returns an
> > error
> > > "Attribute ctid not found". The problem does not happen when I open a
> > forward
> > > only cursor.
> >
> > Is the bidirectional cursor read-only ?
> >
> I tried it both ways - neither worked.

The static read-only cursor on views works here.

> This is a company I'm helping to add a backend to postgresql (they currently
> support Oracle, MSsql and Access). They occasionally also do updates on views
> (rare), so I guess that would come at some stage as well.

Updatable cursors in psqlodbc needs CTID and OID columns.
If you create views with CTID and OID columns, you would
probably get updatable bidirectinal cursors on the views.

    CREATE VIEW a_view as select ctid, oid, ...

regards,
Hiroshi Inoue
    http://www.geocities.jp/inocchichichi/psqlodbc/

Re: ODBC Open cursors on views

From
Shachar Shemesh
Date:
Quoting Hiroshi Inoue <Inoue@tpf.co.jp>:

> Shachar Shemesh wrote:
> >
> > Quoting Hiroshi Inoue <Inoue@tpf.co.jp>:
> >
> > > Shachar Shemesh wrote:
> > > >
> > > > Hi all,
> > > >
> > > > When trying to open a bidirectional cursor on a query that uses a
> view
> > > in the
> > > > from (i.e. - select * from "viewname"), the odbc driver returns an
> > > error
> > > > "Attribute ctid not found". The problem does not happen when I
> open a
> > > forward
> > > > only cursor.
> > >
> > > Is the bidirectional cursor read-only ?
> > >
> > I tried it both ways - neither worked.
>
> The static read-only cursor on views works here.
>
> > This is a company I'm helping to add a backend to postgresql (they
> currently
> > support Oracle, MSsql and Access). They occasionally also do updates
> on views
> > (rare), so I guess that would come at some stage as well.
>
> Updatable cursors in psqlodbc needs CTID and OID columns.
> If you create views with CTID and OID columns, you would
> probably get updatable bidirectinal cursors on the views.
>
>     CREATE VIEW a_view as select ctid, oid, ...
>
> regards,
> Hiroshi Inoue
>     http://www.geocities.jp/inocchichichi/psqlodbc/
>

If more than one table participates in the view, who's CTID do I need to get? Is
it enough to take one of the tables at random?

Assuming I don't want to use static cursors (I understand their performance is
not as good), and that the view's expression is rather complicated (lots of
joins from different tables), am I expected to run into trouble if I just pick a
table at random and add it's CTID to the view?

           Shachar

Re: ODBC Open cursors on views

From
Hiroshi Inoue
Date:

Shachar Shemesh wrote:

[snip]

> If more than one table participates in the view, who's CTID do I need to get? Is
> it enough to take one of the tables at random?

Unfortunately no. The driver couldn't handle updatable
cursors which contain more than 1 table.

regards,
Hiroshi Inoue
    http://www.geocities.jp/inocchichichi/psqlodbc/

Re: ODBC Open cursors on views

From
Shachar Shemesh
Date:
Quoting Hiroshi Inoue <Inoue@tpf.co.jp>:

>
>
> Shachar Shemesh wrote:
>
> [snip]
>
> > If more than one table participates in the view, who's CTID do I need
> to get? Is
> > it enough to take one of the tables at random?
>
Hi,

You said that static readonly cursors worked for you. I'm trying the
following
code, without success:
    UpdateData(TRUE);
    CString sql="select * from \"ACCOUNTLEDGER\"";

    try {
        if( m_crs==NULL ) {
            m_db.OpenEx(NULL);
            m_crs=new CRecordset( &m_db );
        }

        m_crs->Open(CRecordset::snapshot, sql, CRecordset::readOnly );
        RefreshData();
    } catch( CDBException *cdbexcept )
    {
        MessageBox( cdbexcept->m_strError, "SQL error", MB_ICONEXCLAMATION );
    }

I get the same error (attribute "ctid" not found).

Am I doing something wrong?

            Shachar

Re: ODBC Open cursors on views

From
Shachar Shemesh
Date:
Quoting Shachar Shemesh <psql@shemesh.biz>:

> Quoting Hiroshi Inoue <Inoue@tpf.co.jp>:
>
> >
> >
> > Shachar Shemesh wrote:
> >
> > [snip]
> >
> > > If more than one table participates in the view, who's CTID do I
> need
> > to get? Is
> > > it enough to take one of the tables at random?
> >
> Hi,
>
> You said that static readonly cursors worked for you. I'm trying the
> following
> code, without success:
>     UpdateData(TRUE);
>     CString sql="select * from \"ACCOUNTLEDGER\"";
>
>     try {
>         if( m_crs==NULL ) {
>             m_db.OpenEx(NULL);
>             m_crs=new CRecordset( &m_db );
>         }
>
>         m_crs->Open(CRecordset::snapshot, sql, CRecordset::readOnly );
>         RefreshData();
>     } catch( CDBException *cdbexcept )
>     {
>         MessageBox( cdbexcept->m_strError, "SQL error", MB_ICONEXCLAMATION );
>     }
>
> I get the same error (attribute "ctid" not found).
>
> Am I doing something wrong?
>
>             Shachar
I found the source of the problem.

I'm using the CVS version. options.c, line 73, has the following:
            else if (ci->updatable_cursors)
                setval = SQL_CONCUR_ROWVER;
In other words, if updateable cursors are at all available, they will override
whatever I choose to use for concurrency settings.

Commenting out these two lines solve the problem for me. I can also disable
"Updateable cursors" in the ODBC settings, but that would mean that no cursor on
the database can be updateable at all, and that's not acceptable.

             Shachar

Re: ODBC Open cursors on views

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Shachar Shemesh [mailto:psql@shemesh.biz]
>
> Quoting Shachar Shemesh <psql@shemesh.biz>:
>
> I found the source of the problem.
>
> I'm using the CVS version. options.c, line 73, has the following:
>             else if (ci->updatable_cursors)
>                 setval = SQL_CONCUR_ROWVER;
> In other words, if updateable cursors are at all available,
> they will override
> whatever I choose to use for concurrency settings.

Oops my mistake. You are right.
I seem to have forgotten to put an "else" before the previous
"if (ci->drivers.lie)" .
I would commit the fix to cvs tomorrow.

Thanks.
Hiroshi Inoue

Re: ODBC Open cursors on views

From
sun@consumer.org.il
Date:
Quoting Hiroshi Inoue <Inoue@tpf.co.jp>:

>
>
> Shachar Shemesh wrote:
>
> [snip]
>
> > If more than one table participates in the view, who's CTID do I need
> to get? Is
> > it enough to take one of the tables at random?
>
Hi,

You said that static readonly cursors worked for you. I'm trying the following
code, without success:
    UpdateData(TRUE);
    CString sql="select * from \"ACCOUNTLEDGER\"";

    try {
        if( m_crs==NULL ) {
            m_db.OpenEx(NULL);
            m_crs=new CRecordset( &m_db );
        }

        m_crs->Open(CRecordset::snapshot, sql, CRecordset::readOnly );
        RefreshData();
    } catch( CDBException *cdbexcept )
    {
        MessageBox( cdbexcept->m_strError, "SQL error", MB_ICONEXCLAMATION );
    }

I get the same error (attribute "ctid" not found).

Am I doing something wrong?

           Shachar