Thread: 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
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.
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
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
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 manyrecords satisfied a given query until you try to fetch them all. Personally I've seen this kind of erros against Oracleand DB2, but I've seen many programmers used to it because of Access databases. []s, Fernando Lozano