Re: Need help with Visual Basic 6 and PostgreSQL

On 21 Nov 07 10:17 PM, Richard Broersma Jr wrote: <blockquote cite="mid:523506.48216.qm@web31803.mail.mud.yahoo.com"
type="cite"><prewrap="">--- On Wed, 11/21/07, Finn Lassen <a class="moz-txt-link-rfc2396E"
href="mailto:dcio@AxiomInt.com"><dcio@AxiomInt.com></a>wrote:  </pre><blockquote type="cite"><pre wrap="">It now
crashesat 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
errormessage that it generates.  This will probably a very clear indication of what is wrong in your VB generated
record-setupdate.  </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-2220:21:14 ERROR:  column <b>""</b> of relation "Contact1" does not exist at character 40<br /> 2007-11-22
20:21:14STATEMENT:  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
squarebox will post correctly here.)<br /> During another run the "public" is replaced with the square box (whatever
thatcharacter is).<br /><blockquote cite="mid:523506.48216.qm@web31803.mail.mud.yahoo.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
requirean OBDC data source definition on each work station, which I'd rather avoid....<br /><blockquote
cite="mid:523506.48216.qm@web31803.mail.mud.yahoo.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
ofcharacter sets. </pre></blockquote> I tried that. Same results.<br /><blockquote
cite="mid:523506.48216.qm@web31803.mail.mud.yahoo.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
wantedto do was to quickly populate two tables with data from an existing Borland database so I could run a speed
comparisonagainst Access and MySQL. I tried the "COPY" SQL statement, but could not easily get it to work because one
ofthe source fields is a Memo field containing all kinds of characters (but ANSI only). So I was recommended to use the
recordsetmethod and not having to worry about field delimiters.<br /><blockquote
cite="mid:523506.48216.qm@web31803.mail.mud.yahoo.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
toknow your database schema design well enough to know the names of your tables and columns.  It is also good practice
todirectly 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,
thiswas supposed to be a quick and dirty data import.<br /><br /> Obviously there is a bug on either the MS ADO or in
thepsqlodbc30a.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.<br /><br />
ButI do appreciate you taking the time trying to help! Pointing me to the log file really did help and I would think
thatwhoever maintains the psqlodbc30a.dll would be able to reproduce the error and fix the bug. Meanwhile I'll try
usingMS DAO and a DSN connection.<br /><br /> Finn<br />