Visual Basic and PostgreSQL ODBC

Hi,
Firstly why not issue a DELETE FROM tablename statement
to delete all records, e.g.
db.execute "DELETE FROM xxx"

If that is not what you want to do then I think you need to
issue a Refresh event, e.g. rstRecord2.Refresh
I think this will cause the RecordCount to be re-calculated

Good luck

MC.

"Ryan C. Bonham" <Ryan@srfarms.com>@postgresql.org on 27/07/2001 17:59:36

Sent by: pgsql-general-owner@postgresql.org

To: pgsql-odbc@postgresql.org
cc: pgsql-general@postgresql.org
Subject: [GENERAL] Visual Basic and PostgreSQL ODBC

Hi,

Ok I have a problem, that I need to find a fix or workaround for. I have a
Visual Basic 6 application that calls on a PostgreSQL database. I have code
that calls a table and runs a loop on it, deleting recordsets until the
recordcount equals a certain number.. The code deletes the records fine,
the
problem is the recordcount doesn't change.. Does anyone know what is going
on and how to fix it? Thank you

Ryan

VB CODE

rstRecord2.MoveFirst
Do Until rstRecord2.RecordCount = 0
rstRecord2.Delete
rstReocrd2.MoveNext
Debug.Print rstRecord2.RecordCount
Loop

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

http://www.postgresql.org/users-lounge/docs/faq.html

--

NOTICE: The information contained in this electronic mail transmission is
intended by Convergys Corporation for the use of the named individual or
entity to which it is directed and may contain information that is
privileged or otherwise confidential. If you have received this electronic
mail transmission in error, please delete it from your system without
copying or forwarding it, and notify the sender of the error by reply email
or by telephone (collect), so that the sender's address records can be
corrected.

Check the type of your recordset, and then check the docs and see how that type
of recordset supports the RecordCount property. Or, post the code that sets up
the connection and recordset so we can take a look at it. If you really want to
keep deleting until there are none left, you could probably avoid the
RecordCount property altogether by changing the loop condition to:

Do Until rstRecord2.EOF

You also might consider finding a way to do the same thing with a
straightforward SQL statement. For instance, if you want to delete everyone
whose name is Smith, it looks like you're currently setting a recordset equal to
the results of "select name from customer where name='smith'", then cycling
through the recordset to delete them until there are none left. Quicker is to
execute a simple "delete from customer where name='smith'".

HTH,

Wes

"Ryan C. Bonham" <Ryan%srfarms.com@interlock.lexmark.com> on 07/27/2001 12:59:36
PM

To: pgsql-odbc%postgresql.org@interlock.lexmark.com
cc: pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: [GENERAL] Visual Basic and PostgreSQL ODBC

Hi,

Ok I have a problem, that I need to find a fix or workaround for. I have a
Visual Basic 6 application that calls on a PostgreSQL database. I have code
that calls a table and runs a loop on it, deleting recordsets until the
recordcount equals a certain number.. The code deletes the records fine, the
problem is the recordcount doesn't change.. Does anyone know what is going
on and how to fix it? Thank you

Ryan

VB CODE

rstRecord2.MoveFirst
Do Until rstRecord2.RecordCount = 0
rstRecord2.Delete
rstReocrd2.MoveNext
Debug.Print rstRecord2.RecordCount
Loop

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

http://www.postgresql.org/users-lounge/docs/faq.html

Probably your recordset is a snapshot and not a dynamic recordset.
Strange thing is you get no errors in the delete operation.

Anyway, I think you shouldn't delete rows from tables this way.
Why don't you write a query a run through connection's execute command?

Carlos Felipe Zirbes
DBServer Assessoria em Sistemas de Informa��o
E-mail: carlosz@dbserver.com.br
Fone: (51) 3342-8055
Fax: (51) 3342-4838

-----Original Message-----
From: Ryan C. Bonham [mailto:Ryan@srfarms.com]
Sent: sexta-feira, 27 de julho de 2001 14:00
To: pgsql-odbc@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: [GENERAL] Visual Basic and PostgreSQL ODBC

Hi,

Ok I have a problem, that I need to find a fix or workaround for. I have a
Visual Basic 6 application that calls on a PostgreSQL database. I have code
that calls a table and runs a loop on it, deleting recordsets until the
recordcount equals a certain number.. The code deletes the records fine, the
problem is the recordcount doesn't change.. Does anyone know what is going
on and how to fix it? Thank you

Ryan

VB CODE

rstRecord2.MoveFirst
Do Until rstRecord2.RecordCount = 0
rstRecord2.Delete
rstReocrd2.MoveNext
Debug.Print rstRecord2.RecordCount
Loop

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

http://www.postgresql.org/users-lounge/docs/faq.html

Hi all,

I've passed the entire week to try to run Openldap over PostgreSQL and have
experienced
maby troubles. All the documentation and mail I had found seem not really
working, at least
for me. Seeing my time lost to have this working a documentation seems
necessary for this
more and more openLDAP asked feature. I've in plan to write a working HOWTO for
this or
contribute to one in work and I would like to compare my experience with
others.

Here is what I've done:

- Write the first lines of a HOWTO for installation and configuration.
- Able to give some working samples.
- Write a portable patch to cast the objectClass statement in OpenLDAP. The
previous
patch proposed is not portable and need a new slapd.conf option. I will submit
it to the
OpenLDAP team next week.

I still have some trouble with entry creation that finish to make me crazy.

Problem:

I always received the same id (134551209) when inserting multiple entries, so
integrity failed. For example, to load this LDIFF data:

dn: cn=User Test_Add_Entry,o=sql,c=RU
cn: User Test_Add_Entry
sn: User Test_Add_Entry
objectClass: person

I have 'create_proc' set to "SELECT set_person_name(?,?)" and the function is:

CREATE FUNCTION create_person(int4)
RETURNS int4 AS '
INSERT INTO persons (id, name) VALUES ($1, '' '');
SELECT 1;
'
LANGUAGE 'sql';

Given argument is 134551209 (???)
DEBUG: query: SELECT create_person(134551209)

I use SELECT 1; as final query of the function because whatever I want to
return
(like a current sequence number) is not return or at least I don't know how to
have
it back to the next following query.

Here 'add_proc' is set to "SELECT set_person_name(?,?)" and the function is:

CREATE FUNCTION set_person_name(int4, varchar)
RETURNS int4 AS '
UPDATE persons SET name=text($2) WHERE $1=id;
SELECT 1;
'
LANGUAGE 'sql';

Given arg1 is 134551209 and arg2 'User Test_Add_Entry'
DEBUG: query: SELECT set_person_name(134551209,'User Test_Add_Entry')

And then the ldap_entry is inserted like that:

DEBUG: query: INSERT INTO ldap_entries (id,dn,oc_map_id,parent,keyval)
VALUES (currval('ldap_entries_id_seq'),'cn= User Test_Add_Entry,o=sql,c=RU',
1,1,134551209)

Ok, this works but when I insert more dn I always get the same id 134551209 so
all next queries fail.

What does it means ? I have trace a little what openldap is doing and it seams
(possibly wrong, my knowledge with C++ and ODBC is not enougth) that the
ODBC function SQLBindParamater never update the rgbValue that is used to
set a new identifier. Am I wrong ? Is anybody have ever seen that ?

I'm using PostgreSQL 7.1.2, libiodbc-3.0.5 compiled with libpsqlodbc provided
in 7.1.2 and openldap-2.0.11.

The only workaround I've found is using sequences to first create the entry
and then modify the entry to set the attributes. That works pretty well but
don't
permit direct LDIFF export/import.

Thanks for your help,

Gilles DAROLD

"Ryan C. Bonham" wrote:

Hi,

Ok I have a problem, that I need to find a fix or workaround for. I have a
Visual Basic 6 application that calls on a PostgreSQL database. I have code
that calls a table and runs a loop on it, deleting recordsets until the
recordcount equals a certain number.. The code deletes the records fine, the
problem is the recordcount doesn't change.. Does anyone know what is going
on and how to fix it? Thank you

Ryan

Having done a fair bit of VB programming (in VB 6), it seems to me that
recordcount is simply unreliable, no matter which database or which ODBC driver
you use... plain and simple. I remember not using recordcount at all in my VB
programming, and since i haven't done VB for 6 months, I can scarcely remember
what I used in its place.

Ryan

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

"Ryan C. Bonham" wrote:

Hi,

Ok I have a problem, that I need to find a fix or workaround for. I have a
Visual Basic 6 application that calls on a PostgreSQL database. I have code
that calls a table and runs a loop on it, deleting recordsets until the
recordcount equals a certain number.. The code deletes the records fine, the
problem is the recordcount doesn't change.. Does anyone know what is going
on and how to fix it? Thank you

Two solutions use .EOF or .BOF on your recordset, or .MoveLast, get the
.RecordCount, then a .MoveFirst. If you don't do the .MoveLast the record
count tends to stay at it's last value or one. This should get you on the
right track, note I haven't tried this with postgresql as a backend, but
it's what you have to do with MS backends.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: Made with pgp4pine 1.75

iEYEARECAAYFAjtiTnoACgkQwtU6L/A4vVBifQCgkMTbhLbhgPEbYQyrUGKg5Vfh
hP4An35rDduD2oNELy3jJZV4PSSFWOmK
=h0Vj
-----END PGP SIGNATURE-----

----- Original Message -----
From: "Mr. Shannon Aldinger" <god@yinyang.hjsoft.com>
To: <pgsql-general@postgresql.org>
Sent: Saturday, July 28, 2001 7:32 AM
Subject: Re: [GENERAL] Visual Basic and PostgreSQL ODBC

-----BEGIN PGP SIGNED MESSAGE-----

(...)

Two solutions use .EOF or .BOF on your recordset, or .MoveLast, get the
.RecordCount, then a .MoveFirst. If you don't do the .MoveLast the record
count tends to stay at it's last value or one. This should get you on the
right track, note I haven't tried this with postgresql as a backend, but
it's what you have to do with MS backends.

(...)

Hi,
I have done some work with visual basic for Applications (Excel and Acces).
And ... it's horrible.
The .RecordCount in Excel is set right, when opening the Recordset an it's
updated, when inserting or deleting records.
With Access .RecordCount defaults to 0 after the recordset is created. I had
do .MoveLast to get the correct number. Furthermore .RecordCount is not
updated on .delete or .insert.
This happens with PostgreSQL and Oracle-datasouces.
So I use only .EOF to test, if there are records or not.

BTW: I hate this MS-Stuff for different behaviour in different application,
where it should behave the same way. But management says "It's our standard
... do it with MS" .... I'm very happy, i was allowed to use the
very-non-standard PostgreSQL :-)

Andre

Two solutions use .EOF or .BOF on your recordset, or
.MoveLast, get the
.RecordCount, then a .MoveFirst. If you don't do the
.MoveLast the record
count tends to stay at it's last value or one. This should
get you on the
right track, note I haven't tried this with postgresql as a
backend, but
it's what you have to do with MS backends.

If you're going to do the .MoveLast, .RecordCount, .MoveFirst, make sure
your resultset is scrollable (ie, not forward-only). This method can be
very inefficient if you have many rows, and you don't plan on using all
of them in your application.

Another approach is to execute a SELECT COUNT(*) FROM TABLE WHERE <where
clause>, to get the record count.

eric

Hi there,

Having done a fair bit of VB programming (in VB 6), it seems to me that
recordcount is simply unreliable, no matter which database or which ODBC driver
you use...

In fact the ODBC specs states that it may not be accurate for client/server databases because they may not know how many records satisfied a given query until you try to fetch them all. Personally I've seen this kind of erros against Oracle and DB2, but I've seen many programmers used to it because of Access databases.

[]s, Fernando Lozano

-----Original Message-----
From: Ryan C. Bonham [mailto:Ryan@srfarms.com]
Sent: 27 July 2001 18:00
To: pgsql-odbc@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: [ODBC] Visual Basic and PostgreSQL ODBC

Hi,

Ok I have a problem, that I need to find a fix or workaround
for. I have a Visual Basic 6 application that calls on a
PostgreSQL database. I have code that calls a table and runs
a loop on it, deleting recordsets until the recordcount
equals a certain number.. The code deletes the records fine,
the problem is the recordcount doesn't change.. Does anyone
know what is going on and how to fix it? Thank you

Ryan

VB CODE

rstRecord2.MoveFirst
Do Until rstRecord2.RecordCount = 0
rstRecord2.Delete
rstReocrd2.MoveNext
Debug.Print rstRecord2.RecordCount
Loop

Try the following:

rstRecord2.MoveLast
rstRecord2.MoveFirst
Do Until rstRecord2.RecordCount = 0
rstRecord2.Delete
rstRecord2.MoveNext
Debug.Print rstRecord2.RecordCount
Loop

The RecordCount property normally remains at zero until you've moved to the
end of the recordset, hence you need to MoveLast first. This can be a
resource issue though if you have large recordsets as this will cause the
entire recordset to be read from PostgreSQL. If you can though, you will
probably be better off executing a DELETE FROM sql query, and then
refreshing the recordset, or if you are always deleting all records in the
recordset, try:

rstRecord2.MoveFirst
While Not rstRecord2.EOF
rstRecord2.Delete
rstRecord2.MoveNext
Wend

regards, Dave.