VBA 6 crashes with WIN2K, Postgres 8.1, why ? which dll ??

Hi all,

I'm a beginner to MS Windows.
I began to to test VBA 6.0(SP6) with Postgresql 8.1 -with odbc- on
WIN2K(SP4).
For the test (accessing postgres db thru visual basic) I use the code,
given by the distro (psqlODBC HOWTO- Visual Basic).
The first problem I've encounted that the code in the given test snippet
is not full functioning:
"rs.Refresh" doesn't implemented ?
I use in the "Project Preferences" "Microsoft ActiveXData Objects 2.1
library (msado21.tlb)

I've installed some other MS stuff on the machine, and now the Postgres
database call makes a memory exception and crashes.

Q 1.: Is this general, or some libraries are wrong on my system ?
Q 2.: Which MS tools are to control/update the msado-libraries
Q.3.: Which msado or similars are necessary to connect with visual basic
to postgres ?

thank you in advance
testuser vba

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

Sub Main()
Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset

'Open the connection
cn.Open "DSN=;" & _
"UID=;" & _
"PWD=;" & _
"Database="

'For updateable recordsets we would typically open a Dynamic recordset.
'Forward Only recordsets are much faster but can only scroll forward and
'are read only. Snapshot recordsets are read only, but scroll in both
'directions.
rs.Open "SELECT id, data FROM vbtest", cn, adOpenDynamic

'Loop though the recordset and print the results
'We will also update the accessed column, but this time access it through
'the Fields collection. ISO-8601 formatted dates/times are the safest IMHO.
While Not rs.EOF
Debug.Print rs!id & ": " & rs!data
rs.Fields("accessed") = Format(Now, "yyyy-MM-dd hh:mm:ss")
rs.Update
rs.MoveNext
Wend

'Add a new record to the recordset
rs.AddNew
rs!id = 76
rs!data = 'More random data'
rs!accessed = Format(Now, "yyyy-MM-dd hh:mm:ss")
rs.Update

'Insert a new record into the table
cn.Execute "INSERT INTO vbtest (id, data) VALUES (23, 'Some random data');"

'Refresh the recordset to get that last record...
rs.Refresh

'Get the record count
rs.MoveLast
rs.MoveFirst
MsgBox rs.RecordCount & " Records are in the recordset!"

'Cleanup
If rs.State <> adStateClosed Then rs.Close
Set rs = Nothing
If cn.State <> adStateClosed Then cn.Close
Set cn = Nothing
End Sub




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: VBA 6 crashes with WIN2K, Postgres 8.1, why ? which dll ??

> Q 1.: Is this general, or some libraries are wrong on my system ?

There is problem in psqlodbc >= 08.01 (I'm not sure if in 08.00 too).

> 'For updateable recordsets we would typically open a Dynamic recordset.

Updateable recordsets are broken since begin of rewrite to libpq
library. This is the problem.

> 'Forward Only recordsets are much faster but can only scroll forward and

This and static cursors may work correctly.

> rs.Open "SELECT id, data FROM vbtest", cn, adOpenDynamic

adOpenDynamic isn't supported with the newest version.
If you need this behaviour you can try older 07.03 (or maybe 08.00)
version of psqlodbc.

We are at the start point of rewriting the psqlODBC driver. Please
be patient with us.

To all:
Isn't the time to change the documentation? Is here some volunteer which
provide this job for us?

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: VBA 6 crashes with WIN2K, Postgres 8.1, why ? which dll ??

=20

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org=20
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Ludek Finstrle
> Sent: 06 January 2006 06:33
> To: linuxgps
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] VBA 6 crashes with WIN2K, Postgres 8.1,=20
> why ? which dll ??
>=20
> > Q 1.: Is this general, or some libraries are wrong on my system ?
>=20
> There is problem in psqlodbc >=3D 08.01 (I'm not sure if in 08.00 too).
>=20
> > 'For updateable recordsets we would typically open a=20
> Dynamic recordset.
>=20
> Updateable recordsets are broken since begin of rewrite to libpq
> library. This is the problem.

Updateable cursors have *always* been broken. It's an experimental
feature prior to 8.0 which has never had all the kinks worked out.

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: VBA 6 crashes with WIN2K, Postgres 8.1, why ? which dll ??

> > Updateable recordsets are broken since begin of rewrite to libpq
> > library. This is the problem.
>
> Updateable cursors have *always* been broken. It's an experimental
> feature prior to 8.0 which has never had all the kinks worked out.

I'm sorry I mean completely broken (it has never worked in 08.01).
You can have a luck with older releases. It's only partialy broken there.

Why it's in documentation when the support was *always* broken?
I hope the example in documentation is working in older releases.
I hasn't tested it.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: VBA 6 crashes with WIN2K, Postgres 8.1, why ? which dll ??

=20

> -----Original Message-----
> From: Ludek Finstrle [mailto:luf@pzkagis.cz]=20
> Sent: 06 January 2006 08:39
> To: Dave Page
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] VBA 6 crashes with WIN2K, Postgres 8.1,=20
> why ? which dll ??
>=20
> Why it's in documentation when the support was *always* broken?
> I hope the example in documentation is working in older releases.
> I hasn't tested it.

There's an example in the documentation? Where?

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Antw: Re: VBA 6 crashes with WIN2K, Postgres 8.1, why? which dll ??

=20
--=20

_____________________________________________
Hela Gewürzwerk Hermann Laue GmbH & Co.KG
EDV
Thomas Holschen=20
Beimoorweg 11
22926 Ahrensburg

Tel. : +49 4102/496-381
http://www.hela-food.de=20
_____________________________________________

>>> "Dave Page" schrieb am Freitag, 6.
Januar 2006 um
10:29 in Nachricht
:

>=20
>> -----Original Message-----
>> From: Ludek Finstrle [mailto:luf@pzkagis.cz]=20
>> Sent: 06 January 2006 08:39
>> To: Dave Page
>> Cc: pgsql-odbc@postgresql.org=20
>> Subject: Re: [ODBC] VBA 6 crashes with WIN2K, Postgres 8.1,=20
>> why ? which dll ??
>>=20
>> Why it's in documentation when the support was *always* broken?
>> I hope the example in documentation is working in older releases.
>> I hasn't tested it.
>=20
> There's an example in the documentation? Where?
>=20
> Regards, Dave
>=20
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

The Example is installed with MSI-Installer for psqlODBC 08.01.0101.
It's installed as c:/Programme/psqlODBC/docs/howto-vb.html.

Regards=20

Thomas
________________________________________________________

psqlODBC HOWTO - Visual Basic

Author: Dave Page (dpage@postgresql.org)
Release Date: 13 November 2001
Description: Example based Mini-Howto on Accessing PostgreSQL from
Visual Basic

This document provides some sample code to get you started with Visual
Basic & PostgreSQL.

Requirements to get the subroutines to work:

* Visual Basic 5/6
* A reference in the VB project to Microsoft ActiveX Data Objects
* A PostgreSQL datasource.

The example code shown below may need some modification to make it
actually work in your environment. There is one table used in the
example:

CREATE TABLE vbtest(
id int4,
data text,
accessed timestamp
);=20

Code

Sub Main()
Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset
=20
'Open the connection
cn.Open "DSN=3D;" & _
"UID=3D;" & _
"PWD=3D;" & _
"Database=3D"
=20
'For updateable recordsets we would typically open a Dynamic
recordset.
'Forward Only recordsets are much faster but can only scroll forward
and=20
'are read only. Snapshot recordsets are read only, but scroll in both

'directions.=20
rs.Open "SELECT id, data FROM vbtest", cn, adOpenDynamic
=20
'Loop though the recordset and print the results
'We will also update the accessed column, but this time access it
through=20
'the Fields collection. ISO-8601 formatted dates/times are the safest
IMHO.
While Not rs.EOF
Debug.Print rs!id & ": " & rs!data
rs.Fields("accessed") =3D Format(Now, "yyyy-MM-dd hh:mm:ss")
rs.Update
rs.MoveNext
Wend
=20
'Add a new record to the recordset
rs.AddNew
rs!id =3D 76
rs!data =3D 'More random data'
rs!accessed =3D Format(Now, "yyyy-MM-dd hh:mm:ss")
rs.Update

'Insert a new record into the table
cn.Execute "INSERT INTO vbtest (id, data) VALUES (23, 'Some random
data');"

'Refresh the recordset to get that last record...
rs.Refresh

'Get the record count
rs.MoveLast
rs.MoveFirst
MsgBox rs.RecordCount & " Records are in the recordset!"

'Cleanup
If rs.State <> adStateClosed Then rs.Close
Set rs =3D Nothing
If cn.State <> adStateClosed Then cn.Close
Set cn =3D Nothing
End Sub


Useful Functions

' The escapeString function can be used to fix strings for us in INSERT
and=20
' UPDATE SQL statements. It will take a value, and return it ready for

' use in your statement as NULL, or quoted with backslashes and single
quotes
' escaped.

Function escapeString(vValue As Variant) As String

If IsNull(vValue) Then
escapeString =3D "NULL"
else
escapeString =3D "'" & Replace(Replace(vValue, "", ""), "'", "''"
) &
"'"=20
end if

End Function

Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informa=
tionen. Wenn=20
Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalte=
n haben,=20
informieren Sie bitte den Absender und löschen Sie diese E-Mail. Das uner=
laubte=20
Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet. =
Aus=20
Rechts- und Sicherheitsgründen ist die in dieser E-Mail gegebene Informat=
ion nicht=20
rechtsverbindlich.

This e-mail contains confidential and/or privileged information. If you are=
not the=20
intended addressee or have received this e-mail in error please notify the =
sender and=20
delete this e-mail. Any unauthorized copying, disclosure or distribution of=
the material=20
in this e-mail is strictly forbidden. Due to legal and security reasons the=
information=20
contained in this e-mail is not legally binding.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Antw: Re: VBA 6 crashes with WIN2K, Postgres 8.1, why? which dll ??

=20

> -----Original Message-----
> From: Thomas Holschen [mailto:Thomas.Holschen@hela-food.de]=20
> Sent: 06 January 2006 11:20
> To: Ludek Finstrle; Dave Page
> Cc: pgsql-odbc@postgresql.org
> Subject: Antw: Re: [ODBC] VBA 6 crashes with WIN2K, Postgres=20
> 8.1, why? which dll ??
>=20
> The Example is installed with MSI-Installer for psqlODBC 08.01.0101.
> It's installed as c:/Programme/psqlODBC/docs/howto-vb.html.

Nowhere in there does it mention updateable cursors - in fact, it was
written long before they were even added to the driver.

I just tested the example given, and with a few minor typo/thinko fixes
it works just fine *except* for the RecordCount which has been the
subject of recent discussion anyway.

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Antw: Re: VBA 6 crashes with WIN2K, Postgres 8.1,

In the VB debugger, watching 'CN' the cn.DefaultData immediately after
the connection has
' multi step error and it says to check the ole db status if applicable'

Following this it reads the first record, but ! the "rs.AbsolutPage,
rs.AbsolutePosition" is "adPosUnknown"

Is this a driver bug or I've missed up some setup, grant, odbc registry ???
..
Dave Page wrote:

>
>
>
>
>>-----Original Message-----
>>From: Thomas Holschen [mailto:Thomas.Holschen@hela-food.de]
>>Sent: 06 January 2006 11:20
>>To: Ludek Finstrle; Dave Page
>>Cc: pgsql-odbc@postgresql.org
>>Subject: Antw: Re: [ODBC] VBA 6 crashes with WIN2K, Postgres
>>8.1, why? which dll ??
>>
>>The Example is installed with MSI-Installer for psqlODBC 08.01.0101.
>>It's installed as c:/Programme/psqlODBC/docs/howto-vb.html.
>>
>>
>
>Nowhere in there does it mention updateable cursors - in fact, it was
>written long before they were even added to the driver.
>
>I just tested the example given, and with a few minor typo/thinko fixes
>it works just fine *except* for the RecordCount which has been the
>subject of recent discussion anyway.
>
>Regards, Dave
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Antw: Re: VBA 6 crashes with WIN2K, Postgres 8.1, why? which dll ??

=20

> -----Original Message-----
> From: linuxgps [mailto:linuxgps@invitel.hu]=20
> Sent: 06 January 2006 14:35
> To: Dave Page
> Cc: Thomas Holschen; Ludek Finstrle; pgsql-odbc@postgresql.org
> Subject: Re: Antw: Re: [ODBC] VBA 6 crashes with WIN2K,=20
> Postgres 8.1, why? which dll ??
>=20
>=20
>=20
> In the VB debugger, watching 'CN' the cn.DefaultData=20
> immediately after=20
> the connection has
> ' multi step error and it says to check the ole db status if=20
> applicable'

You get an error merely by opening the connection? Can we see the mylog
output please? FWIW, I'm not surprised the DefaultDatabase isn't
supported - you cannot change it with a PostgreSQL connection so it
doesn't make sense to have a 'default'.

Regard,s Dave

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Antw: Re: VBA 6 crashes with WIN2K, Postgres 8.1, why? which dll ??

=20

> -----Original Message-----
> From: linuxgps [mailto:linuxgps@invitel.hu]=20
> Sent: 06 January 2006 17:53
> To: Dave Page
> Cc: Thomas Holschen; Ludek Finstrle; pgsql-odbc@postgresql.org
> Subject: Re: Antw: Re: [ODBC] VBA 6 crashes with WIN2K,=20
> Postgres 8.1, why? which dll ??
>=20
> I've attached the log.

That doesn't show a crash at startup, nor is it even generated by the
test code we were discussing - there's almost 1MB of output caused by
SQLTables which is not used by the test program. I'm guessing you sent
the wrong log - mine from a single run of the program was 64KB btw.

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Antw: Re: VBA 6 crashes with WIN2K, Postgres 8.1,

This is a multi-part message in MIME format.

--Boundary_(ID_w+ErrqeLfavBEuWUs+KmDA)
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT

Sorry Dave,
you're right that was the log on the other machine.

Here is the log of app crashed with memory exception

Dave Page wrote:

>
>
>
>
>>-----Original Message-----
>>From: linuxgps [mailto:linuxgps@invitel.hu]
>>Sent: 06 January 2006 17:53
>>To: Dave Page
>>Cc: Thomas Holschen; Ludek Finstrle; pgsql-odbc@postgresql.org
>>Subject: Re: Antw: Re: [ODBC] VBA 6 crashes with WIN2K,
>>Postgres 8.1, why? which dll ??
>>
>>I've attached the log.
>>
>>
>
>That doesn't show a crash at startup, nor is it even generated by the
>test code we were discussing - there's almost 1MB of output caused by
>SQLTables which is not used by the test program. I'm guessing you sent
>the wrong log - mine from a single run of the program was 64KB btw.
>
>Regards, Dave.
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>
>


--Boundary_(ID_w+ErrqeLfavBEuWUs+KmDA)
Content-type: text/plain; name=mylog_548_compaq.txt
Content-transfer-encoding: 7BIT
Content-disposition: inline; filename=mylog_548_compaq.txt

[1548]globals.extra_systable_prefixes = 'dd_;'
[1548][[SQLAllocHandle]][1548]**** in PGAPI_AllocEnv **
[1548]** exit PGAPI_AllocEnv: phenv = 36649776 **
[1548][[SQLSetEnvAttr]] att=200,2
[1548][[SQLAllocHandle]][1548]PGAPI_AllocConnect: entering...
[1548]**** PGAPI_AllocConnect: henv = 36649776, conn = 36649832
[1548]EN_add_connection: self = 36649776, conn = 36649832
[1548] added at i =0, conn->henv = 36649776, conns[i]->henv = 36649776
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=77
[1548]PGAPI_GetInfo: p='03.00', len=5, value=0, cbMax=12
[1548][[SQLSetConnectAttr]] 103
[1548]PGAPI_SetConnectAttr 103
[1548]PGAPI_SetConnectOption: entering fOption = 103 vParam = 15
[1548][SQLDriverConnect][1548]PGAPI_DriverConnect: entering...
[1548]**** PGAPI_DriverConnect: fDriverCompletion=0, connStrIn='DSN=hpuj;UID=postgres;PWD=xxxxxxx;Database=postgr es;'
[1548]our_connect_string = 'DSN=hpuj;UID=postgres;PWD=xxxxxxx;Database=postgres;'
[1548]attribute = 'DSN', value = 'hpuj'
[1548]copyAttributes: DSN='hpuj',server='',dbase='',user='',passwd='xxxxx',port='' ,sslmode='',onlyread='',protocol='',conn_settings='',disallo w_premature=-1)
[1548]attribute = 'UID', value = 'postgres'
[1548]copyAttributes: DSN='hpuj',server='',dbase='',user='postgres',passwd='xxxxx' ,port='',sslmode='',onlyread='',protocol='',conn_settings='' ,disallow_premature=-1)
[1548]attribute = 'PWD', value = 'xxxxx'
[1548]copyAttributes: DSN='hpuj',server='',dbase='',user='postgres',passwd='xxxxx' ,port='',sslmode='',onlyread='',protocol='',conn_settings='' ,disallow_premature=-1)
[1548]attribute = 'Database', value = 'postgres'
[1548]copyAttributes: DSN='hpuj',server='',dbase='postgres',user='postgres',passwd ='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_set tings='',disallow_premature=-1)
[1548]globals.extra_systable_prefixes = 'dd_;'
[1548]globals.extra_systable_prefixes = 'dd_;'
[1548]our_connect_string = 'DSN=hpuj;UID=postgres;PWD=xxxxxxx;Database=postgres;'
[1548]attribute = 'DSN', value = 'hpuj'
[1548]CopyCommonAttributes: A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=1;B3=0;B4=1;B5=1;B6=0; B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[1548]attribute = 'UID', value = 'postgres'
[1548]CopyCommonAttributes: A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=1;B3=0;B4=1;B5=1;B6=0; B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[1548]attribute = 'PWD', value = 'xxxxx'
[1548]CopyCommonAttributes: A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=1;B3=0;B4=1;B5=1;B6=0; B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[1548]attribute = 'Database', value = 'postgres'
[1548]CopyCommonAttributes: A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=1;B3=0;B4=1;B5=1;B6=0; B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[1548]CC_connect: entering...
[1548]CC_connect(): DSN = 'hpuj', server = '192.168.1.31', port = '5432', sslmode = 'prefer', database = 'postgres', username = 'postgres', password='xxxxx'
[1548]connecting to the server
[1548]connecting to the database using 192.168.1.31 as the server
[1548]the size is 18
[1548]connection to the database succeeded.
[1548]connection to the database succeeded.
[1548]CC_lookup_pg_version: entering...
[1548]PGAPI_AllocStmt: entering...
[1548]**** PGAPI_AllocStmt: hdbc = 36649832, stmt = 36643048
[1548]CC_add_statement: self=36649832, stmt=36643048
[1548]PGAPI_ExecDirect: entering...
[1548]recycle statement: self= 36643048
[1548]PDATA_free_params: ENTER, self=36643480
[1548]**** PGAPI_ExecDirect: hstmt=36643048, statement='select version()'
[1548]PGAPI_ExecDirect: calling PGAPI_Execute...
[1548]PGAPI_Execute: entering...
[1548]PGAPI_Execute: clear errors...
[1548]recycle statement: self= 36643048
[1548]PDATA_free_params: ENTER, self=36643480
[1548]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=16, stmt='select version()'
[1548] stmt_with_params = 'select version()'
[1548] Sending SELECT statement on stmt=36643048, cursor_name='SQL_CUR022F20E8'
[1548]send_query(): conn=36649832, query='select version()'
[1548]in QR_Constructor
[1548]exit QR_Constructor
[1548]in TL_Constructor
[1548]exit TL_Constructor
[1548]send_query: done sending query
[1548]QR_fetch_tuples: cursor = '', self->cursor=0
[1548]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[1548]MALLOC: tuple_size = 100, size = 800
[1548] done sending the query:
[1548]extend_column_bindings: entering ... self=36643184, bindings_allocated=0, num_columns=1
[1548]exit extend_column_bindings
[1548]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[1548]PGAPI_Fetch: stmt = 36643048, stmt->result= 36643712
[1548]manual_result = 1, use_declarefetch = 0
[1548]**** SC_fetch: manual_result
[1548]extend_getdata_info: entering ... self=36643404, gdata_allocated=0, num_columns=1
[1548]exit extend_gdata_info
[1548]fetch: cols=1, lf=0, opts = 36643184, opts->bindings = 36666032, buffer[] = 0
[1548]PGAPI_GetData: enter, stmt=36643048
[1548] value = 'PostgreSQL 8.1.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)'
[1548]**** PGAPI_GetData: icol = 0, fCType = 1, field_type = 25, value = 'PostgreSQL 8.1.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)'
[1548]copy_and_convert: field_type = 25, fctype = 1, value = 'PostgreSQL 8.1.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)', cbValueMax=128
[1548]DEFAULT: len = 88, ptr = 'PostgreSQL 8.1.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)'
[1548] SQL_C_CHAR, default: len = 88, cbValueMax = 128, rgbValueBindRow = 'PostgreSQL 8.1.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)'
[1548]Got the PostgreSQL version string: 'PostgreSQL 8.1.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)'
[1548]Extracted PostgreSQL version number: '8.1'
[1548]PGAPI_FreeStmt: entering...hstmt=36643048, fOption=1
[1548]QResult: in DESTRUCTOR
[1548]TupleList: in DESTRUCTOR
[1548]TupleList: exit DESTRUCTOR
[1548]QResult: free memory in, fcount=101
[1548]QResult: free memory out
[1548]QResult: exit DESTRUCTOR
[1548]SC_Destructor: self=36643048, self->result=0, self->hdbc=36649832
[1548]APD_free_params: ENTER, self=36643300
[1548]IPD_free_params: ENTER, self=36643364
[1548]PDATA_free_params: ENTER, self=36643480
[1548]ARDFields_free 22f2170 bookmark=22f2330[1548]ARD_unbind_cols freeall=1 allocated=1 bindings=22f7ab0
[1548]reset_a_column_binding: entering ... self=36643184, bindings_allocated=1, icol=1
[1548]APD_free_params: ENTER, self=36643300
[1548]IPD_free_params: ENTER, self=36643364
[1548]GDATA_unbind_cols freeall=1 allocated=1 gdata=22f7ad8
[1548]SC_Destructor: EXIT
[1548]CC_send_settings: entering...
[1548]PGAPI_AllocStmt: entering...
[1548]**** PGAPI_AllocStmt: hdbc = 36649832, stmt = 36643048
[1548]CC_add_statement: self=36649832, stmt=36643048
[1548]PGAPI_ExecDirect: entering...
[1548]recycle statement: self= 36643048
[1548]PDATA_free_params: ENTER, self=36643480
[1548]**** PGAPI_ExecDirect: hstmt=36643048, statement='set DateStyle to 'ISO''
[1548]PGAPI_ExecDirect: calling PGAPI_Execute...
[1548]PGAPI_Execute: entering...
[1548]PGAPI_Execute: clear errors...
[1548]recycle statement: self= 36643048
[1548]PDATA_free_params: ENTER, self=36643480
[1548]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=22, stmt='set DateStyle to 'ISO''
[1548] stmt_with_params = 'set DateStyle to 'ISO''
[1548] it's NOT a select statement: stmt=36643048
[1548]send_query(): conn=36649832, query='set DateStyle to 'ISO''
[1548]in QR_Constructor
[1548]exit QR_Constructor
[1548]The query was executed successfully and the query did not return any result
[1548]send_query: done sending query
[1548]QR_fetch_tuples: cursor = '', self->cursor=0
[1548]QR_fetch_tuples: past CI_read_fields: num_fields = 0
[1548]MALLOC: tuple_size = 100, size = 0
[1548]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[1548]CC_send_settings: result 0, status 1 from set DateStyle
[1548]PGAPI_ExecDirect: entering...
[1548]recycle statement: self= 36643048
[1548]QResult: in DESTRUCTOR
[1548]QResult: free memory in, fcount=101
[1548]QResult: free memory out
[1548]QResult: exit DESTRUCTOR
[1548]PDATA_free_params: ENTER, self=36643480
[1548]**** PGAPI_ExecDirect: hstmt=36643048, statement='set geqo to 'OFF''
[1548]PGAPI_ExecDirect: calling PGAPI_Execute...
[1548]PGAPI_Execute: entering...
[1548]PGAPI_Execute: clear errors...
[1548]recycle statement: self= 36643048
[1548]PDATA_free_params: ENTER, self=36643480
[1548]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=17, stmt='set geqo to 'OFF''
[1548] stmt_with_params = 'set geqo to 'OFF''
[1548] it's NOT a select statement: stmt=36643048
[1548]send_query(): conn=36649832, query='set geqo to 'OFF''
[1548]in QR_Constructor
[1548]exit QR_Constructor
[1548]The query was executed successfully and the query did not return any result
[1548]send_query: done sending query
[1548]QR_fetch_tuples: cursor = '', self->cursor=0
[1548]QR_fetch_tuples: past CI_read_fields: num_fields = 0
[1548]MALLOC: tuple_size = 100, size = 0
[1548]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[1548]CC_send_settings: result 0, status 1 from set geqo
[1548]PGAPI_ExecDirect: entering...
[1548]recycle statement: self= 36643048
[1548]QResult: in DESTRUCTOR
[1548]QResult: free memory in, fcount=101
[1548]QResult: free memory out
[1548]QResult: exit DESTRUCTOR
[1548]PDATA_free_params: ENTER, self=36643480
[1548]**** PGAPI_ExecDirect: hstmt=36643048, statement='set extra_float_digits to 2'
[1548]PGAPI_ExecDirect: calling PGAPI_Execute...
[1548]PGAPI_Execute: entering...
[1548]PGAPI_Execute: clear errors...
[1548]recycle statement: self= 36643048
[1548]PDATA_free_params: ENTER, self=36643480
[1548]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=27, stmt='set extra_float_digits to 2'
[1548] stmt_with_params = 'set extra_float_digits to 2'
[1548] it's NOT a select statement: stmt=36643048
[1548]send_query(): conn=36649832, query='set extra_float_digits to 2'
[1548]in QR_Constructor
[1548]exit QR_Constructor
[1548]The query was executed successfully and the query did not return any result
[1548]send_query: done sending query
[1548]QR_fetch_tuples: cursor = '', self->cursor=0
[1548]QR_fetch_tuples: past CI_read_fields: num_fields = 0
[1548]MALLOC: tuple_size = 100, size = 0
[1548]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[1548]CC_send_settings: result 0, status 1 from set extra_float_digits
[1548]PGAPI_FreeStmt: entering...hstmt=36643048, fOption=1
[1548]QResult: in DESTRUCTOR
[1548]QResult: free memory in, fcount=101
[1548]QResult: free memory out
[1548]QResult: exit DESTRUCTOR
[1548]SC_Destructor: self=36643048, self->result=0, self->hdbc=36649832
[1548]APD_free_params: ENTER, self=36643300
[1548]IPD_free_params: ENTER, self=36643364
[1548]PDATA_free_params: ENTER, self=36643480
[1548]ARDFields_free 22f2170 bookmark=22f2358[1548]ARD_unbind_cols freeall=1 allocated=0 bindings=0
[1548]APD_free_params: ENTER, self=36643300
[1548]IPD_free_params: ENTER, self=36643364
[1548]GDATA_unbind_cols freeall=1 allocated=0 gdata=0
[1548]SC_Destructor: EXIT
[1548]CC_lookup_lo: entering...
[1548]PGAPI_AllocStmt: entering...
[1548]**** PGAPI_AllocStmt: hdbc = 36649832, stmt = 36643048
[1548]CC_add_statement: self=36649832, stmt=36643048
[1548]PGAPI_ExecDirect: entering...
[1548]recycle statement: self= 36643048
[1548]PDATA_free_params: ENTER, self=36643480
[1548]**** PGAPI_ExecDirect: hstmt=36643048, statement='select oid from pg_type where typname='lo''
[1548]PGAPI_ExecDirect: calling PGAPI_Execute...
[1548]PGAPI_Execute: entering...
[1548]PGAPI_Execute: clear errors...
[1548]recycle statement: self= 36643048
[1548]PDATA_free_params: ENTER, self=36643480
[1548]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=42, stmt='select oid from pg_type where typname='lo''
[1548] stmt_with_params = 'select oid from pg_type where typname='lo''
[1548] Sending SELECT statement on stmt=36643048, cursor_name='SQL_CUR022F20E8'
[1548]send_query(): conn=36649832, query='select oid from pg_type where typname='lo''
[1548]in QR_Constructor
[1548]exit QR_Constructor
[1548]in TL_Constructor
[1548]exit TL_Constructor
[1548]send_query: done sending query
[1548]QR_fetch_tuples: cursor = '', self->cursor=0
[1548]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[1548]MALLOC: tuple_size = 100, size = 800
[1548] done sending the query:
[1548]extend_column_bindings: entering ... self=36643184, bindings_allocated=0, num_columns=1
[1548]exit extend_column_bindings
[1548]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[1548]PGAPI_Fetch: stmt = 36643048, stmt->result= 36643712
[1548]manual_result = 1, use_declarefetch = 0
[1548]PGAPI_FreeStmt: entering...hstmt=36643048, fOption=1
[1548]QResult: in DESTRUCTOR
[1548]TupleList: in DESTRUCTOR
[1548]TupleList: exit DESTRUCTOR
[1548]QResult: free memory in, fcount=101
[1548]QResult: free memory out
[1548]QResult: exit DESTRUCTOR
[1548]SC_Destructor: self=36643048, self->result=0, self->hdbc=36649832
[1548]APD_free_params: ENTER, self=36643300
[1548]IPD_free_params: ENTER, self=36643364
[1548]PDATA_free_params: ENTER, self=36643480
[1548]ARDFields_free 22f2170 bookmark=22f2358[1548]ARD_unbind_cols freeall=1 allocated=1 bindings=22f24e8
[1548]reset_a_column_binding: entering ... self=36643184, bindings_allocated=1, icol=1
[1548]APD_free_params: ENTER, self=36643300
[1548]IPD_free_params: ENTER, self=36643364
[1548]GDATA_unbind_cols freeall=1 allocated=0 gdata=0
[1548]SC_Destructor: EXIT
[1548]CC_lookup_characterset: entering...
[1548]send_query(): conn=36649832, query='select pg_client_encoding()'
[1548]in QR_Constructor
[1548]exit QR_Constructor
[1548]in TL_Constructor
[1548]exit TL_Constructor
[1548]send_query: done sending query
[1548]QR_fetch_tuples: cursor = '', self->cursor=0
[1548]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[1548]MALLOC: tuple_size = 100, size = 800
[1548]QResult: in DESTRUCTOR
[1548]TupleList: in DESTRUCTOR
[1548]TupleList: exit DESTRUCTOR
[1548]QResult: free memory in, fcount=101
[1548]QResult: free memory out
[1548]QResult: exit DESTRUCTOR
[1548]send_query(): conn=36649832, query='set client_encoding to 'WIN1250''
[1548]in QR_Constructor
[1548]exit QR_Constructor
[1548]The query was executed successfully and the query did not return any result
[1548]send_query: done sending query
[1548]QR_fetch_tuples: cursor = '', self->cursor=0
[1548]QR_fetch_tuples: past CI_read_fields: num_fields = 0
[1548]MALLOC: tuple_size = 100, size = 0
[1548]QResult: in DESTRUCTOR
[1548]QResult: free memory in, fcount=101
[1548]QResult: free memory out
[1548]QResult: exit DESTRUCTOR
[1548]CC_connect: returning...
[1548]szConnStrOut = 'DSN=hpuj;DATABASE=postgres;SERVER=192.168.1.31;PORT=5432;SS LMODE=prefer;UID=postgres;PWD=xxxxxxx;ReadOnly=0;Protocol=6. 4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemT ables=0;ConnSettings=;Fetch=100;Socket=8192;UnknownSizes=0;M axVarcharSize=254;MaxLongVarcharSize=8190;Debug=1;CommLog=0; Optimizer=1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;Unk nownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt= 0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors =0;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBin ary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0' len=589,1024
[1548]PGAPI_DriverConnect: returning 0
[1548][[SQLGetDiagRec]]
[1548]PGAPI_GetDiagRec entering rec=1[1548]**** PGAPI_ConnectError: hdbc=36649832 <0>
[1548]enter CC_get_error
[1548]enter CC_create_errormsg
[1548]msg = ''
[1548]exit CC_create_errormsg
[1548]exit CC_get_error
[1548]CC_Get_error returned nothing.
[1548]PGAPI_GetDiagRec exiting 100
[1548][SQLGetFunctions][1548][SQLGetInfo(30)][1548]PGAPI_Get Info: entering...fInfoType=23
[1548]PGAPI_GetInfo: p='', len=2, value=2, cbMax=2
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=24
[1548]PGAPI_GetInfo: p='', len=2, value=2, cbMax=2
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=79
[1548]PGAPI_GetInfo: p='', len=4, value=3, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=83
[1548]PGAPI_GetInfo: p='', len=4, value=0, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=78
[1548]PGAPI_GetInfo: p='', len=4, value=1, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=81
[1548]PGAPI_GetInfo: p='', len=4, value=15, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=72
[1548]PGAPI_GetInfo: p='', len=4, value=10, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=82
[1548]PGAPI_GetInfo: p='', len=4, value=90, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=44
[1548]PGAPI_GetInfo: p='', len=4, value=17, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=43
[1548]PGAPI_GetInfo: p='', len=4, value=1, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=144
[1548]PGAPI_GetInfo30: p='', len=4, value=0, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=150
[1548]PGAPI_GetInfo30: p='', len=4, value=4687, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=167
[1548]PGAPI_GetInfo30: p='', len=4, value=4687, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=146
[1548]PGAPI_GetInfo30: p='', len=4, value=1, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=151
[1548]PGAPI_GetInfo30: p='', len=4, value=1, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=168
[1548]PGAPI_GetInfo30: p='', len=4, value=1, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=111
[1548]PGAPI_GetInfo: p='N', len=1, value=0, cbMax=512
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=16
[1548]PGAPI_GetInfo: p='', len=0, value=0, cbMax=0
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=23
[1548]PGAPI_GetInfo: p='', len=2, value=2, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=24
[1548]PGAPI_GetInfo: p='', len=2, value=2, cbMax=4
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=46
[1548]PGAPI_GetInfo: p='', len=2, value=2, cbMax=4
[1548][[SQLSetConnectAttr]] 1
[1548]PGAPI_SetConnectAttr 1
[1548]PGAPI_SetConnectOption: entering fOption = 1 vParam = 0
[1548]SetStmtOption(): SQL_MAX_ROWS, vParam = 0
[1548][[SQLSetConnectAttr]] 0
[1548]PGAPI_SetConnectAttr 0
[1548]PGAPI_SetConnectOption: entering fOption = 0 vParam = 0
[1548]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 0
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=6
[1548]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=52
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=6
[1548]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=52
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=6
[1548]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=52
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=6
[1548]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=48
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=1
[1548]PGAPI_GetInfo: p='', len=2, value=0, cbMax=4
[1548][[SQLGetConnectAttr]] 109
[1548]PGAPI_GetConnectAttr 109
[1548]PGAPI_GetConnectOption: entering...
[1548][SQLGetInfo(30)][1548]PGAPI_GetInfo: entering...fInfoType=26
[1548]PGAPI_GetInfo: p='', len=4, value=2, cbMax=4
[1548][[SQLAllocHandle]][1548]PGAPI_AllocStmt: entering...
[1548]**** PGAPI_AllocStmt: hdbc = 36649832, stmt = 36643048
[1548]CC_add_statement: self=36649832, stmt=36643048
[1548][[SQLGetStmtAttr]] Handle=36643048 10010
[1548]PGAPI_GetStmtAttr Handle=36643048 10010
[1548][[SQLGetStmtAttr]] Handle=36643048 10011
[1548]PGAPI_GetStmtAttr Handle=36643048 10011
[1548][[SQLGetStmtAttr]] Handle=36643048 10012
[1548]PGAPI_GetStmtAttr Handle=36643048 10012
[1548][[SQLGetStmtAttr]] Handle=36643048 10013
[1548]PGAPI_GetStmtAttr Handle=36643048 10013
[1548][[SQLSetStmtAttr]] Handle=36643048 18,10
[1548]PGAPI_SetStmtAttr Handle=36643048 18,10
[1548][[SQLSetStmtAttr]] Handle=36643048 18,0
[1548]PGAPI_SetStmtAttr Handle=36643048 18,0
[1548][[SQLSetStmtAttr]] Handle=36643048 17,1241416
[1548]PGAPI_SetStmtAttr Handle=36643048 17,1241416
[1548][[SQLSetStmtAttr]] Handle=36643048 17,0
[1548]PGAPI_SetStmtAttr Handle=36643048 17,0
[1548][[SQLSetDescField]] h=36643268 rec=1 field=1004 val=12f148
[1548]PGAPI_SetDescField h=36643268 rec=1 field=1004 val=12f148,0
[1548]DESCRIPTOR ERROR: func=PGAPI_SetDescField, desc='', errnum=11, errmsg='bad parameter number'
[1548][[SQLGetDiagRec]]
[1548]PGAPI_GetDiagRec entering rec=1

--Boundary_(ID_w+ErrqeLfavBEuWUs+KmDA)
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--Boundary_(ID_w+ErrqeLfavBEuWUs+KmDA)--

Re: VBA 6 crashes with WIN2K, Postgres 8.1, why ? whichdll ??

This is a multi-part message in MIME format.
--------------080607080200040004060700
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

"Interesting" example.
1. Using PostgreSQL is basically a client-server relationship,..so adOpenDynamic (updated cursors) is a
bad choice. I recommend adOpenForwardOnly for fast one-way reading, and adOpenStatic to pull a client side
recordset so that you can move forward and back. In either case declare the recordset Cursor to be client
side (rs.CursorLocation = adUseClient).

This also means no rs.AddNew and no rs.Refresh.
Outside of MS Jet, I think of them as abominations.

I have come to think of the connection between my app and the database as a potentially slow narrow pipe
across a vast distance. Updateable cursors hold open the connection while I cursor up and down, always
tying up more database side resources and network resources. I always send commands, and pull back
recordsets. The only exception that makes sense is the browsing of mammoth datasets (millions of rows).
How practical is your application if you're visually browsing millions of rows.

2. ADODB library version 2.1 is mildly ancient. I'm use to seeing 2.5 on older installations, and lately
2.6,2.7 and maybe a 2.8. At any rate, the latest is always a free download from the MSDN MDAC area
(Microsoft Data Access Components).

3. Again this is a personal preference, but I always use connection.Execute for INSERTS,UPDATE, and
DELETES. Your example used on AddNew and one cnn.Execute.

4. I find a object referencing style and stick with it. rs.Fields("fieldname") and rs!fieldname are both a
little dated. rs("fieldname"), or rs(variable_holding_field_name) or rs(integer_field_ndx) seem to be
cultural standards now.

5. I loathe to admit it, ADO.NET is also a much stronger tool for web/client apps for things like
databinding. With it data grids are bound to disconnected recordsets, which execute discrete INSERTS,
UPDATES, AND DELETE underneath,...again avoiding the updateable cursor.

Oh, and PostgreSQL via ODBC,...
it has never been strong on the updateable cursor/dynamic cursor thing.



linuxgps wrote:
> Hi all,
>
> I'm a beginner to MS Windows.
> I began to to test VBA 6.0(SP6) with Postgresql 8.1 -with odbc- on
> WIN2K(SP4).
> For the test (accessing postgres db thru visual basic) I use the code,
> given by the distro (psqlODBC HOWTO- Visual Basic).
> The first problem I've encounted that the code in the given test snippet
> is not full functioning:
> "rs.Refresh" doesn't implemented ?
> I use in the "Project Preferences" "Microsoft ActiveXData Objects 2.1
> library (msado21.tlb)
>
> I've installed some other MS stuff on the machine, and now the Postgres
> database call makes a memory exception and crashes.
>
> Q 1.: Is this general, or some libraries are wrong on my system ?
> Q 2.: Which MS tools are to control/update the msado-libraries
> Q.3.: Which msado or similars are necessary to connect with visual basic
> to postgres ?
>
> thank you in advance
> testuser vba
>
> -----------------
>
> Sub Main()
> Dim cn as New ADODB.Connection
> Dim rs as New ADODB.Recordset
>
> 'Open the connection
> cn.Open "DSN=;" & _
> "UID=;" & _
> "PWD=;" & _
> "Database="
>
> 'For updateable recordsets we would typically open a Dynamic recordset.
> 'Forward Only recordsets are much faster but can only scroll forward
> and 'are read only. Snapshot recordsets are read only, but scroll in
> both 'directions. rs.Open "SELECT id, data FROM vbtest", cn,
> adOpenDynamic
>
> 'Loop though the recordset and print the results
> 'We will also update the accessed column, but this time access it
> through 'the Fields collection. ISO-8601 formatted dates/times are the
> safest IMHO.
> While Not rs.EOF
> Debug.Print rs!id & ": " & rs!data
> rs.Fields("accessed") = Format(Now, "yyyy-MM-dd hh:mm:ss")
> rs.Update
> rs.MoveNext
> Wend
>
> 'Add a new record to the recordset
> rs.AddNew
> rs!id = 76
> rs!data = 'More random data'
> rs!accessed = Format(Now, "yyyy-MM-dd hh:mm:ss")
> rs.Update
>
> 'Insert a new record into the table
> cn.Execute "INSERT INTO vbtest (id, data) VALUES (23, 'Some random
> data');"
>
> 'Refresh the recordset to get that last record...
> rs.Refresh
>
> 'Get the record count
> rs.MoveLast
> rs.MoveFirst
> MsgBox rs.RecordCount & " Records are in the recordset!"
>
> 'Cleanup
> If rs.State <> adStateClosed Then rs.Close
> Set rs = Nothing
> If cn.State <> adStateClosed Then cn.Close
> Set cn = Nothing
> End Sub
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--------------080607080200040004060700
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------080607080200040004060700
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--------------080607080200040004060700--

Re: Antw: Re: VBA 6 crashes with WIN2K, Postgres 8.1,

This is a multi-part message in MIME format.
--------------080400060700030406030603
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

What type of cursor are you using.
The sample in the MSDN (VB6 era help) says to use Client side cursor to enable AbsolutePosition,
AbsolutePage.

I guess it makes sense. A server side cursor is a pointer to records on the server. A ForwardOnly cursor
pulls back the recordset but only gives you a pointer to it, but doesn't know how many records are in the
set or how to page it before it has gone to the end -- and it can't go backward.

rule of thumb -- use:
rsCursorLocation = adUseClient
rs.open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText ---- or something close to this

rs.Open sql, cnn -- also works



linuxgps wrote:

>
>
> In the VB debugger, watching 'CN' the cn.DefaultData immediately after
> the connection has
> ' multi step error and it says to check the ole db status if applicable'
>
> Following this it reads the first record, but ! the "rs.AbsolutPage,
> rs.AbsolutePosition" is "adPosUnknown"
>
> Is this a driver bug or I've missed up some setup, grant, odbc registry ???
> .
> Dave Page wrote:
>
>>
>>
>>
>>
>>> -----Original Message-----
>>> From: Thomas Holschen [mailto:Thomas.Holschen@hela-food.de] Sent: 06
>>> January 2006 11:20
>>> To: Ludek Finstrle; Dave Page
>>> Cc: pgsql-odbc@postgresql.org
>>> Subject: Antw: Re: [ODBC] VBA 6 crashes with WIN2K, Postgres 8.1,
>>> why? which dll ??
>>>
>>> The Example is installed with MSI-Installer for psqlODBC 08.01.0101.
>>> It's installed as c:/Programme/psqlODBC/docs/howto-vb.html.
>>>
>>
>>
>> Nowhere in there does it mention updateable cursors - in fact, it was
>> written long before they were even added to the driver.
>>
>> I just tested the example given, and with a few minor typo/thinko fixes
>> it works just fine *except* for the RecordCount which has been the
>> subject of recent discussion anyway.
>>
>> Regards, Dave
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--------------080400060700030406030603
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------080400060700030406030603
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--------------080400060700030406030603--

Re: VBA 6 crashes with WIN2K, Postgres 8.1, why ? whichdll ??

Well Greg, you're right.
Just the used code snippet is about 4 years old and was shipped with the
distro.
For beginners I think even a slow code is much better as having nothing.
Have you any tutorial code using Postgres thru VBA with ADO.NET ?

Campbell, Greg wrote:

> "Interesting" example.
> 1. Using PostgreSQL is basically a client-server relationship,..so
> adOpenDynamic (updated cursors) is a bad choice. I recommend
> adOpenForwardOnly for fast one-way reading, and adOpenStatic to pull a
> client side recordset so that you can move forward and back. In either
> case declare the recordset Cursor to be client side (rs.CursorLocation
> = adUseClient).
>
> This also means no rs.AddNew and no rs.Refresh.
> Outside of MS Jet, I think of them as abominations.
>
> I have come to think of the connection between my app and the database
> as a potentially slow narrow pipe across a vast distance. Updateable
> cursors hold open the connection while I cursor up and down, always
> tying up more database side resources and network resources. I always
> send commands, and pull back recordsets. The only exception that makes
> sense is the browsing of mammoth datasets (millions of rows). How
> practical is your application if you're visually browsing millions of
> rows.
>
> 2. ADODB library version 2.1 is mildly ancient. I'm use to seeing 2.5
> on older installations, and lately 2.6,2.7 and maybe a 2.8. At any
> rate, the latest is always a free download from the MSDN MDAC area
> (Microsoft Data Access Components).
>
> 3. Again this is a personal preference, but I always use
> connection.Execute for INSERTS,UPDATE, and DELETES. Your example used
> on AddNew and one cnn.Execute.
>
> 4. I find a object referencing style and stick with it.
> rs.Fields("fieldname") and rs!fieldname are both a little dated.
> rs("fieldname"), or rs(variable_holding_field_name) or
> rs(integer_field_ndx) seem to be cultural standards now.
>
> 5. I loathe to admit it, ADO.NET is also a much stronger tool for
> web/client apps for things like databinding. With it data grids are
> bound to disconnected recordsets, which execute discrete INSERTS,
> UPDATES, AND DELETE underneath,...again avoiding the updateable cursor.
>
> Oh, and PostgreSQL via ODBC,...
> it has never been strong on the updateable cursor/dynamic cursor thing.
>
>
>
> linuxgps wrote:
>
>> Hi all,
>>
>> I'm a beginner to MS Windows.
>> I began to to test VBA 6.0(SP6) with Postgresql 8.1 -with odbc- on
>> WIN2K(SP4).
>> For the test (accessing postgres db thru visual basic) I use the
>> code, given by the distro (psqlODBC HOWTO- Visual Basic).
>> The first problem I've encounted that the code in the given test
>> snippet is not full functioning:
>> "rs.Refresh" doesn't implemented ?
>> I use in the "Project Preferences" "Microsoft ActiveXData Objects 2.1
>> library (msado21.tlb)
>>
>> I've installed some other MS stuff on the machine, and now the
>> Postgres database call makes a memory exception and crashes.
>>
>> Q 1.: Is this general, or some libraries are wrong on my system ?
>> Q 2.: Which MS tools are to control/update the msado-libraries
>> Q.3.: Which msado or similars are necessary to connect with visual
>> basic to postgres ?
>>
>> thank you in advance
>> testuser vba
>>
>> -----------------
>>
>> Sub Main()
>> Dim cn as New ADODB.Connection
>> Dim rs as New ADODB.Recordset
>>
>> 'Open the connection
>> cn.Open "DSN=;" & _
>> "UID=;" & _
>> "PWD=;" & _
>> "Database="
>>
>> 'For updateable recordsets we would typically open a Dynamic recordset.
>> 'Forward Only recordsets are much faster but can only scroll forward
>> and 'are read only. Snapshot recordsets are read only, but scroll in
>> both 'directions. rs.Open "SELECT id, data FROM vbtest", cn,
>> adOpenDynamic
>>
>> 'Loop though the recordset and print the results
>> 'We will also update the accessed column, but this time access it
>> through 'the Fields collection. ISO-8601 formatted dates/times are
>> the safest IMHO.
>> While Not rs.EOF
>> Debug.Print rs!id & ": " & rs!data
>> rs.Fields("accessed") = Format(Now, "yyyy-MM-dd hh:mm:ss")
>> rs.Update
>> rs.MoveNext
>> Wend
>>
>> 'Add a new record to the recordset
>> rs.AddNew
>> rs!id = 76
>> rs!data = 'More random data'
>> rs!accessed = Format(Now, "yyyy-MM-dd hh:mm:ss")
>> rs.Update
>>
>> 'Insert a new record into the table
>> cn.Execute "INSERT INTO vbtest (id, data) VALUES (23, 'Some random
>> data');"
>>
>> 'Refresh the recordset to get that last record...
>> rs.Refresh
>>
>> 'Get the record count
>> rs.MoveLast
>> rs.MoveFirst
>> MsgBox rs.RecordCount & " Records are in the recordset!"
>>
>> 'Cleanup
>> If rs.State <> adStateClosed Then rs.Close
>> Set rs = Nothing
>> If cn.State <> adStateClosed Then cn.Close
>> Set cn = Nothing
>> End Sub
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: VBA 6 crashes with WIN2K, Postgres 8.1, why ? whichdll??

This is a multi-part message in MIME format.
--------------030208040208040208020705
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

For ADO.NET I am using Npgsql, which you can get from the affiliated project site -- the Npgsql project.
It is just like using the .NET SQL server, and Oledb libraries from the MSDN. The project site has
documentation with examples on how to use Npgsql. I thinks there stuff in the GBorg and the pgfoundry site.

I works for me.
(except for Parameterized stored procedures using an actual Parameters collection,...maybe it's just me.)



linuxgps wrote:
>
> Well Greg, you're right.
> Just the used code snippet is about 4 years old and was shipped with the
> distro.
> For beginners I think even a slow code is much better as having nothing.
> Have you any tutorial code using Postgres thru VBA with ADO.NET ?
>
> Campbell, Greg wrote:
>
>> "Interesting" example.
>> 1. Using PostgreSQL is basically a client-server relationship,..so
>> adOpenDynamic (updated cursors) is a bad choice. I recommend
>> adOpenForwardOnly for fast one-way reading, and adOpenStatic to pull a
>> client side recordset so that you can move forward and back. In either
>> case declare the recordset Cursor to be client side (rs.CursorLocation
>> = adUseClient).
>>
>> This also means no rs.AddNew and no rs.Refresh.
>> Outside of MS Jet, I think of them as abominations.
>>
>> I have come to think of the connection between my app and the database
>> as a potentially slow narrow pipe across a vast distance. Updateable
>> cursors hold open the connection while I cursor up and down, always
>> tying up more database side resources and network resources. I always
>> send commands, and pull back recordsets. The only exception that makes
>> sense is the browsing of mammoth datasets (millions of rows). How
>> practical is your application if you're visually browsing millions of
>> rows.
>>
>> 2. ADODB library version 2.1 is mildly ancient. I'm use to seeing 2.5
>> on older installations, and lately 2.6,2.7 and maybe a 2.8. At any
>> rate, the latest is always a free download from the MSDN MDAC area
>> (Microsoft Data Access Components).
>>
>> 3. Again this is a personal preference, but I always use
>> connection.Execute for INSERTS,UPDATE, and DELETES. Your example used
>> on AddNew and one cnn.Execute.
>>
>> 4. I find a object referencing style and stick with it.
>> rs.Fields("fieldname") and rs!fieldname are both a little dated.
>> rs("fieldname"), or rs(variable_holding_field_name) or
>> rs(integer_field_ndx) seem to be cultural standards now.
>>
>> 5. I loathe to admit it, ADO.NET is also a much stronger tool for
>> web/client apps for things like databinding. With it data grids are
>> bound to disconnected recordsets, which execute discrete INSERTS,
>> UPDATES, AND DELETE underneath,...again avoiding the updateable cursor.
>>
>> Oh, and PostgreSQL via ODBC,...
>> it has never been strong on the updateable cursor/dynamic cursor thing.
>>
>>
>>
>> linuxgps wrote:
>>
>>> Hi all,
>>>
>>> I'm a beginner to MS Windows.
>>> I began to to test VBA 6.0(SP6) with Postgresql 8.1 -with odbc- on
>>> WIN2K(SP4).
>>> For the test (accessing postgres db thru visual basic) I use the
>>> code, given by the distro (psqlODBC HOWTO- Visual Basic).
>>> The first problem I've encounted that the code in the given test
>>> snippet is not full functioning:
>>> "rs.Refresh" doesn't implemented ?
>>> I use in the "Project Preferences" "Microsoft ActiveXData Objects 2.1
>>> library (msado21.tlb)
>>>
>>> I've installed some other MS stuff on the machine, and now the
>>> Postgres database call makes a memory exception and crashes.
>>>
>>> Q 1.: Is this general, or some libraries are wrong on my system ?
>>> Q 2.: Which MS tools are to control/update the msado-libraries
>>> Q.3.: Which msado or similars are necessary to connect with visual
>>> basic to postgres ?
>>>
>>> thank you in advance
>>> testuser vba
>>>
>>> -----------------
>>>
>>> Sub Main()
>>> Dim cn as New ADODB.Connection
>>> Dim rs as New ADODB.Recordset
>>>
>>> 'Open the connection
>>> cn.Open "DSN=;" & _
>>> "UID=;" & _
>>> "PWD=;" & _
>>> "Database="
>>>
>>> 'For updateable recordsets we would typically open a Dynamic recordset.
>>> 'Forward Only recordsets are much faster but can only scroll forward
>>> and 'are read only. Snapshot recordsets are read only, but scroll in
>>> both 'directions. rs.Open "SELECT id, data FROM vbtest", cn,
>>> adOpenDynamic
>>>
>>> 'Loop though the recordset and print the results
>>> 'We will also update the accessed column, but this time access it
>>> through 'the Fields collection. ISO-8601 formatted dates/times are
>>> the safest IMHO.
>>> While Not rs.EOF
>>> Debug.Print rs!id & ": " & rs!data
>>> rs.Fields("accessed") = Format(Now, "yyyy-MM-dd hh:mm:ss")
>>> rs.Update
>>> rs.MoveNext
>>> Wend
>>>
>>> 'Add a new record to the recordset
>>> rs.AddNew
>>> rs!id = 76
>>> rs!data = 'More random data'
>>> rs!accessed = Format(Now, "yyyy-MM-dd hh:mm:ss")
>>> rs.Update
>>>
>>> 'Insert a new record into the table
>>> cn.Execute "INSERT INTO vbtest (id, data) VALUES (23, 'Some random
>>> data');"
>>>
>>> 'Refresh the recordset to get that last record...
>>> rs.Refresh
>>>
>>> 'Get the record count
>>> rs.MoveLast
>>> rs.MoveFirst
>>> MsgBox rs.RecordCount & " Records are in the recordset!"
>>>
>>> 'Cleanup
>>> If rs.State <> adStateClosed Then rs.Close
>>> Set rs = Nothing
>>> If cn.State <> adStateClosed Then cn.Close
>>> Set cn = Nothing
>>> End Sub
>>>
>>>
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>> choose an index scan if your joining column's datatypes do not
>>> match
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--------------030208040208040208020705
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------030208040208040208020705
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--------------030208040208040208020705--

Re: VBA 6 crashes with WIN2K, Postgres 8.1, why ?

-----Original Message-----
From: Campbell, Greg [mailto:greg.campbell@us.michelin.com]
Sent: Sun 1/8/2006 4:32 PM
To: Dave Page
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] VBA 6 crashes with WIN2K, Postgres 8.1, why ?
=20
> And on the post midnight thing, I lost track of the time=20
> difference and at the time remember thinking,=20
> "Why is this guy doing postgres odbc postings in the middle=20
> of the season premier of Galactica?".

Hehe - the best I had to watch were reruns of season 1 of The Shield. Galac=
tica starts tomorrow iirc.

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Antw: Re: VBA 6 crashes with WIN2K, Postgres 8.1, why? which dll ??

=20

> -----Original Message-----
> From: linuxgps [mailto:linuxgps@invitel.hu]=20
> Sent: 06 January 2006 22:18
> To: Dave Page
> Cc: Thomas Holschen; Ludek Finstrle; pgsql-odbc@postgresql.org
> Subject: Re: Antw: Re: [ODBC] VBA 6 crashes with WIN2K,=20
> Postgres 8.1, why? which dll ??
>=20
> Sorry Dave,
> you're right that was the log on the other machine.
>=20
> Here is the log of app crashed with memory exception

What version of the driver is this? I can't see anything obviously wrong
in there :-(

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Antw: Re: VBA 6 crashes with WIN2K, Postgres 8.1, why? which dll ??

=20

> -----Original Message-----
> From: linuxgps [mailto:linuxgps@invitel.hu]=20
> Sent: 09 January 2006 16:37
> To: Dave Page
> Subject: Re: Antw: Re: [ODBC] VBA 6 crashes with WIN2K,=20
> Postgres 8.1, why? which dll ??
>=20
> I've reinstalled the newest postgres v8.1.2 win32 msi=20
> (http://wwwmaster.postgresql.org/download/mirrors-ftp?file=3 Dbin
> ary%2Fv8.1.2%2Fwin32%2Fpostgresql-8.1.2-1.zip)
> from the pgfoundry
> http://wwwmaster.postgresql.org/download/mirrors-ftp?file=3D odbc
> %2Fversions%2Fdll%2Fpsqlodbc-08_01_0102.zip
> In the VB 6(SP6) the cn debugstring has the following value after the=20
> connection:
> debug1 Provider=3DMSDASQL.1;Extended=20
> Properties=3D"DSN=3Dcompaq1;DATABASE=3Dpostgres;SERVER=3Dloc alhost;POR
> T=3D5432;SSLMODE=3Dprefer;UID=3Dpostgres;PWD=3Dpgtest;ReadOn ly=3D0;Proto
> col=3D6.4;FakeOidIndex=3D0;ShowOidColumn=3D0;RowVersioning=3 D0;ShowSys
> temTables=3D0;ConnSettings=3D;Fetch=3D100;Socket=3D8192;Unkn ownSizes=3D0
> ;MaxVarcharSize=3D254;MaxLongVarcharSize=3D8190;Debug=3D1;Co mmLog=3D1;
> Optimizer=3D1;Ksqo=3D1;UseDeclareFetch=3D0;TextAsLongVarchar =3D1;Unkno
> wnsAsLongVarchar=3D0;BoolsAsChar=3D1;Parse=3D1;CancelAsFreeS tmt=3D0;Ex
> traSysTablePrefixes=3Ddd_;;LFConversion=3D1;UpdatableCursors =3D0;Dis
> allowPremature=3D0;TrueIsMinus1=3D0;BI=3D0;ByteaAsLongVarBin ary=3D0;Us
> eServerSidePrepare=3D0;LowerCaseIdentifier=3D0"
> stepping to next program line:

The only differences I can see are that you have commlog turned on which
I do not, and you have Parse Statements turned on, which I don't. I know
which I'd lay money on being the culprit if it is one of those, but
perhaps you could turn the options off one at a time, testing after each
one.

Other that that, I'm stumped. Perhaps someone else can run the test and
see what happens. Or maybe someone else has an idea.

It might be worth posting the full VB project you are using, just in
case there's a copy n paste error or something in it.

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Antw: Re: VBA 6 crashes with WIN2K, Postgres 8.1, why? which dll ??

Hello,

I'm sorry I'm out of problem. I don't monitor this thread as Dave
responds.

> > I've reinstalled the newest postgres v8.1.2 win32 msi
> > (http://wwwmaster.postgresql.org/download/mirrors-ftp?file=b in
> > ary%2Fv8.1.2%2Fwin32%2Fpostgresql-8.1.2-1.zip)
> > from the pgfoundry
> > http://wwwmaster.postgresql.org/download/mirrors-ftp?file=od bc
> > %2Fversions%2Fdll%2Fpsqlodbc-08_01_0102.zip

Could you try the newer "development snapshot"? There is the newest one
here:
http://pgfoundry.org/tracker/index.php?func=detail&aid=10005 10&group_id=1000125&atid=538
(at the bottom of the page - psqlodbc-setpos.zip)
Note: there is only two DLLs which replace files from realease version.

> Other that that, I'm stumped. Perhaps someone else can run the test and
> see what happens. Or maybe someone else has an idea.

Could you write me few lines about it? Maybe link to related archive.
I try to read this in archive but it's blind for me. There is several
threads and I don't know which one I may follow.

> It might be worth posting the full VB project you are using, just in
> case there's a copy n paste error or something in it.

It is the best for me if you create minimalistic example which show the
problem and send it to me compiled (I have no VB compiler or I know
nothing about it).

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq