Обсуждение: BUG #2555: runtime error 80040e21

The following bug has been logged online:

Bug reference:      2555
Logged by:          Jonas Bruhn
Email address:      jonas.bruhn@gmail.com
PostgreSQL version: 8.1.4
Operating system:   Windows 2000 Pro
Description:        runtime error 80040e21
Details:

I'm using VB6, and trying to update a table. It is all happening on my home
pc with IIS running too. I can download the table to a datagrid, bit I can't
type any text i a textbox and update it whith out the error occuring. Here
is the code and some remarks afterwards:

Private Sub command2_click()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection

    cn.Open "Provider=PostgreSQL.1;Password=***;User ID=postgres;Data
Source=localhost;Location=***" ';Extended Properties="""

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "select tilbudspris, materialer from efterkalk", cn,
adOpenKeyset, adLockOptimistic

    rs.AddNew
debug here ---  rs("tilbudspris").Value = Text1.Text
    rs("materialer").Value = Text2.Text
    rs.Update

    Set rs.ActiveConnection = Nothing
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

Remarks:
The columns in the table is set to text.
I've tried to write rs!tilbudspris and rs!materialer.
I've tried to trim(text1.text)
I've tried adopenstatic
I've tried rs.movefirst

I can't recognise where the problem should be, if it isn't a bug.

Best regards

Jonas Bruhn

I don't know VB very well, but I'm guessing that you can't update a
recordset that's the product of a SELECT. In any case, it's certainly
not a PostgreSQL bug. If you need more help, try pgsql-general.

On Jul 28, 2006, at 11:54 PM, Jonas Bruhn wrote:
> The following bug has been logged online:
>
> Bug reference:      2555
> Logged by:          Jonas Bruhn
> Email address:      jonas.bruhn@gmail.com
> PostgreSQL version: 8.1.4
> Operating system:   Windows 2000 Pro
> Description:        runtime error 80040e21
> Details:
>
> I'm using VB6, and trying to update a table. It is all happening on
> my home
> pc with IIS running too. I can download the table to a datagrid,
> bit I can't
> type any text i a textbox and update it whith out the error
> occuring. Here
> is the code and some remarks afterwards:
>
> Private Sub command2_click()
>
>     Dim cn As ADODB.Connection
>     Dim rs As ADODB.Recordset
>
>     Set cn = New ADODB.Connection
>
>     cn.Open "Provider=PostgreSQL.1;Password=***;User ID=postgres;Data
> Source=localhost;Location=***" ';Extended Properties="""
>
>     Set rs = New ADODB.Recordset
>     rs.CursorLocation = adUseClient
>     rs.Open "select tilbudspris, materialer from efterkalk", cn,
> adOpenKeyset, adLockOptimistic
>
>     rs.AddNew
> debug here ---  rs("tilbudspris").Value = Text1.Text
>     rs("materialer").Value = Text2.Text
>     rs.Update
>
>     Set rs.ActiveConnection = Nothing
>     Set rs = Nothing
>     cn.Close
>     Set cn = Nothing
>
> End Sub
>
> Remarks:
> The columns in the table is set to text.
> I've tried to write rs!tilbudspris and rs!materialer.
> I've tried to trim(text1.text)
> I've tried adopenstatic
> I've tried rs.movefirst
>
> I can't recognise where the problem should be, if it isn't a bug.
>
> Best regards
>
> Jonas Bruhn
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim Nasby <jnasby@pervasive.com> writes:

>> I'm using VB6, and trying to update a table. It is all happening on
>> my home pc with IIS running too. I can download the table to a
>> datagrid, bit I can't type any text i a textbox and update it whith
>> out the error occuring. Here [skip]
> I don't know VB very well, but I'm guessing that you can't update a
> recordset that's the product of a SELECT. In any case, it's certainly
> not a PostgreSQL bug. If you need more help, try pgsql-general.

This is a limitation of the PostgreSQL OLE DB provider. At present,
the provider doesn't support updating. Most of the required
functionality (namely, support for parameterized queries that ADO
issues) is already committed, but ADO doesn't want to issue update
queries until the following patch is applied to the CVS version of
pgoledb:

RCS file: /cvsroot/oledb/oledb/TypeInfo.h,v
retrieving revision 1.20
diff -u -r1.20 TypeInfo.h
--- TypeInfo.h  20 Apr 2006 11:54:18 -0000      1.20
+++ TypeInfo.h  21 Apr 2006 10:50:52 -0000
@@ -148,7 +148,7 @@
         colinfo->ulColumnSize=PQfsize( res, field_num );
         if( colinfo->ulColumnSize<0 )
             colinfo->ulColumnSize=~0;
-        colinfo->dwFlags=DBCOLUMNFLAGS_MAYBENULL|
+        colinfo->dwFlags=DBCOLUMNFLAGS_MAYBENULL|DBCOLUMNFLAGS_WRITEUNKNOWN|
             (colinfo->ulColumnSize==~0?0:DBCOLUMNFLAGS_ISFIXEDLENGTH);
         colinfo->wType=DBTYPE_UDT; // User Defined Type
         colinfo->bPrecision=~0;


Nobody on the oledb-devel@pgfoundry (dot) org list knows if this is
the right solution (actually, only I and Shachar Shemesh - the
committer of the project - participated in discussion about this)

Although, this patch makes updating work.

Feel free to discuss arising problems at oledb-devel@ list.

Known problems so far:
  1) lack of bytea support
  2) date/time types don't support updating
  3) bool types are still handled incorrectly.

I have one big patch to solve them all. Most of the fixed have been
proposed at oledb-devel@, but not accepted due to the following:
  1) to implement bytea support, we need to avoid the bug in ATL
  2) I have implemented date/time uploading as text for now, and all
    the other data types are passed in binary.
  3) The fix is OK, but wasn't committed yet.

The patch is accessible here:

http://www.indorsoft.ru/oledb-current.diff

--
WBR, Victor V. Snezhko
E-mail: snezhko@indorsoft.ru