Skip site navigation (1) Skip section navigation (2)

Re: Need help with Visual Basic 6 and PostgreSQL

From: Finn Lassen <dcio(at)AxiomInt(dot)com>
To: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Need help with Visual Basic 6 and PostgreSQL
Date: 2007-11-23 02:01:54
Message-ID: 47463492.90002@AxiomInt.com (view raw or flat)
Thread:
Lists: pgsql-odbc
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
On 21 Nov 07 10:17 PM, Richard Broersma Jr wrote:
<blockquote cite="mid:523506(dot)48216(dot)qm(at)web31803(dot)mail(dot)mud(dot)yahoo(dot)com"
 type="cite">
  <pre wrap="">--- On Wed, 11/21/07, Finn Lassen <a class="moz-txt-link-rfc2396E" href="mailto:dcio(at)AxiomInt(dot)com">&lt;dcio(at)AxiomInt(dot)com&gt;</a> wrote: 
  </pre>
  <blockquote type="cite">
    <pre wrap="">It now crashes at the .Update statement.
Any suggestions on how proceed?
Do I have to configure the PostgreSQL ANSI driver somehow?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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. 
  </pre>
</blockquote>
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):<br>
------------------------------<br>
2007-11-22 20:21:14 ERROR:&nbsp; column <b>""</b> of relation "Contact1"
does not exist at character 40<br>
2007-11-22 20:21:14 STATEMENT:&nbsp; BEGIN;insert into "public"."Contact1" (<b>"",</b>
"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'...<br>
------------------------------<br>
skipping over the first 4 field labels before "Department". (I hope the
quoted square box will post correctly here.)<br>
During another run the "public" is replaced with the square box
(whatever that character is).<br>
<blockquote cite="mid:523506(dot)48216(dot)qm(at)web31803(dot)mail(dot)mud(dot)yahoo(dot)com"
 type="cite">
  <pre wrap="">
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
  </pre>
</blockquote>
Thanks. But that would eventually require an OBDC data source
definition on each work station, which I'd rather avoid....<br>
<blockquote cite="mid:523506(dot)48216(dot)qm(at)web31803(dot)mail(dot)mud(dot)yahoo(dot)com"
 type="cite">
  <pre wrap="">
Also, you may consider using the PostgreSQL Unicode ODBC driver over the ANSI driver since it supports a larger array of character sets.
  </pre>
</blockquote>
I tried that. Same results.<br>
<blockquote cite="mid:523506(dot)48216(dot)qm(at)web31803(dot)mail(dot)mud(dot)yahoo(dot)com"
 type="cite">
  <pre wrap="">
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.
  </pre>
</blockquote>
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.<br>
<blockquote cite="mid:523506(dot)48216(dot)qm(at)web31803(dot)mail(dot)mud(dot)yahoo(dot)com"
 type="cite">
  <pre wrap="">
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.
  </pre>
</blockquote>
OK, I'll bite :)<br>
Since I created the tables I'm intimately familiar with the schema.
Again, this was supposed to be a quick and dirty data import.<br>
<br>
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&nbsp;&nbsp; mov&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; edx,dword ptr [edi+0Ch]"), that DLL is the prime
suspect.<br>
<br>
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.<br>
<br>
Finn<br>
</body>
</html>



Attachment: unknown_filename
Description: text/html (4.9 KB)

In response to

Responses

pgsql-odbc by date

Next:From: Richard Broersma JrDate: 2007-11-23 14:39:19
Subject: Re: Need help with Visual Basic 6 and PostgreSQL
Previous:From: Paul LambertDate: 2007-11-22 03:34:01
Subject: Re: Need help with Visual Basic 6 and PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group