Re: Need help with Visual Basic 6 and PostgreSQL

[Finn Lassen <dcio(at)AxiomInt(dot)com>]

On 21 Nov 07 10:17 PM, Richard Broersma Jr wrote:

--- On Wed, 11/21/07, Finn Lassen <dcio(at)AxiomInt(dot)com> wrote: 
  

It now crashes at the .Update statement.
Any suggestions on how proceed?
Do I have to configure the PostgreSQL ANSI driver somehow?
    

I would look at your PostgreSQL logs to see the exact crashing update statement that VB is sending to PostgreSQL to process (you may need to first turn on query logging in your postgresql.conf file and then restart your PostgreSQL service).  Next I would try to manually run the listed update query in postgres' command line editor psql to see the error message that it generates.  This will probably a very clear indication of what is wrong in your VB generated record-set update. 
  

After rebooting the computer, it will make it through the first iteration of the loop (successful .Update). Next time through I get this (excerpt from log):
------------------------------
2007-11-22 20:21:14 ERROR:  column "" of relation "Contact1" does not exist at character 40
2007-11-22 20:21:14 STATEMENT:  BEGIN;insert into "public"."Contact1" ("", "Department", "TITLE", "SECR", "Phone1", "Phone2", "Phone3", "Fax", "Ext1", "Ext2", "Ext3", "Ext4", "(", "Address2", "Address3", "City", "State", "Zip", "Country", "Dear", "Source", "Key1", "Key2", "Key3", "Key4", "Key5", "Status", "MERGECODES", "CreateBy", "Owner", "LastUser", "LastDate", "LastTime", "VisibleTo", "RecID") values (E'93100765018597400Bru'...
------------------------------
skipping over the first 4 field labels before "Department". (I hope the quoted square box will post correctly here.)
During another run the "public" is replaced with the square box (whatever that character is).

Also, give the how-to example for using ADO + ODBC + PG a try that comes with your ODBC installation help documentation?
it is found in this path on my computer: 
C:\Program Files\PostgreSQL\8.2\doc\psqlODBC\howto-vb.html
  

Thanks. But that would eventually require an OBDC data source definition on each work station, which I'd rather avoid....

Also, you may consider using the PostgreSQL Unicode ODBC driver over the ANSI driver since it supports a larger array of character sets.
  

I tried that. Same results.

I wish I could be of more help, but I've never really grown accustom to using Records sets to update or delete records.  I've always manually crafted my own SQL statements that I've passed to the back-end server.
  

All I wanted to do was to quickly populate two tables with data from an existing Borland database so I could run a speed comparison against Access and MySQL. I tried the "COPY" SQL statement, but could not easily get it to work because one of the source fields is a Memo field containing all kinds of characters (but ANSI only). So I was recommended to use the recordset method and not having to worry about field delimiters.

Also at the risk of getting my head bitten off by someone more knowledgeable that disagrees :-), it is a good practice to know your database schema design well enough to know the names of your tables and columns.  It is also good practice to directly call out those column names in your code rather than simply guessing based on the field types.
  

OK, I'll bite :)
Since I created the tables I'm intimately familiar with the schema. Again, this was supposed to be a quick and dirty data import.

Obviously there is a bug on either the MS ADO or in the psqlodbc30a.dll. Since the psqlodbc30a.dll is the one that produces the exception ("Access violation, Context: PSQLODBC30A! 06f49797(), 06F49797   mov         edx,dword ptr [edi+0Ch]"), that DLL is the prime suspect.

But I do appreciate you taking the time trying to help! Pointing me to the log file really did help and I would think that whoever maintains the psqlodbc30a.dll would be able to reproduce the error and fix the bug. Meanwhile I'll try using MS DAO and a DSN connection.

Finn