Need help with Visual Basic 6 and PostgreSQL

I am very new to ADODB, ODBC and SQL databases.



When I run the following which I wrote to populate tables (with data
from a Borland V database) , the Visual Basic Developer Interface
crashes with the following error signature (Windows XP):

AppName: vb6.exe     AppVer: 6.0.97.82     ModName: psqlodbc30a.dll

ModVer: 8.2.4.0     Offset: 00016edc

------------------------------------------------------------ ----------------  


    Dim dbOut As ADODB.Connection

    Dim rsOut As ADODB.Recordset

    Set dbOut = New ADODB.Connection

    Set rsOut = New ADODB.Recordset



    With dbOut

        .ConnectionString = "Driver={PostgreSQL
ANSI};Server=localhost;Port=5432;Database=postgres;Uid=user; Pwd=secret;"

        .Open

        With rsOut

            rsOut.Open """Contact1""", dbOut, adOpenDynamic,
adLockOptimistic, adCmdTable

            Do

                .AddNew

                For Each fld In .Fields

                    Debug.Print fld.name, fld.Type

                    Select Case fld.Type

                        Case 3 'integer (oid)

                        Case 200, 202 'Memo -- Text (ANSI: 200,
Unicode: 202)

                           fld.value = "Memo type text"

                        Case 133 'Date

                           fld.value = Date

                        Case Else

                           fld.value =
"FixedLengthStrings"                 

                    End Select

                Next fld

                .Update

                'Exit Loop code here

            Loop

            .Close

        End With

        .Close

    End With

    Set dbOut = Nothing

    Set rsOut = Nothing

------------------------------------------------------------ --------------------------------------------

Note: I had to add a "oid" column to the table or the rsOut.Open would
fail with "ERROR: column "oid" does not exist;".



It now crashes at the .Update statement.

Previously it managed to add 2 rows to the table.

Another oddity is when using the VB Visual Data Manager add-in to view
the database it will not list the Indexes for the Contact1 table.

It will list the Indexes for the Contact2 table which is not populated
nor been worked with.



Any suggestions on how proceed?

Do I have to configure the PostgreSQL ANSI driver somehow? If so, how?



Finn

-- 

Finn Lassen

Deputy CIO

Axiom

1805 Drew Street

Clearwater, Florida 33765

727-442-7774 voice

727-442-8344 fax

Re: Need help with Visual Basic 6 and PostgreSQL

--- On Wed, 11/21/07, Finn Lassen 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.

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

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

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.

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.

Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Need help with Visual Basic 6 and PostgreSQL

Finn Lassen wrote:
> I am very new to ADODB, ODBC and SQL databases.
>
> When I run the following which I wrote to populate tables (with data
> from a Borland V database) , the Visual Basic Developer Interface
> crashes with the following error signature (Windows XP):
> AppName: vb6.exe AppVer: 6.0.97.82 ModName: psqlodbc30a.dll
> ModVer: 8.2.4.0 Offset: 00016edc

I had perhaps a similar issue with VB6 earlir this year - program
crashing on doing a .Update, though mine was an access violation.

Refer to the thread here:
http://archives.postgresql.org/pgsql-odbc/2007-03/msg00045.p hp

My solution in the end was to drop using recordsets and the
addNew/Update methods to constructing an SQL INSERT/UPDATE/DELETE
statement of my own and running that through .Execute on the database
object instead.

I'd suggest turning on logging on the ODBC driver, running your program
until it crashes and then checking the contents of the mylog and
psqlodbc log in your root c directory. They might give a hint as to what
the problem is - perhaps some funny data being sent through.

--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Need help with Visual Basic 6 and PostgreSQL

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

type="cite">

--- On Wed, 11/21/07, Finn Lassen  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. 

  

type="cite">

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

  

type="cite">

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

  

type="cite">

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.

  

type="cite">

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.

  

Re: Need help with Visual Basic 6 and PostgreSQL

--- On Thu, 11/22/07, Finn Lassen wrote:
> 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" ("",
> "Ext4", "(", "Address2",

Does your table really have all of these field names? In addition to the "" field, I am suspicious of the "(" field. I am curious, if the ado recordset is seeing more fields than the table actually has. Since you weren't able to display the field that didn't exist in the table, you could possible determine if it is a ascii character by getting its charcode()(or something equivalent).

If you indeed find that your recordset does indeed see fields that do not exist in the table, then it is very likely that you have found a bug with the ODBC driver. It would help the developers to correct this problem if you were to submit a small test case that is able to reproduce the effects that you are seeing. It should include a sample database with sample table and it should include the VB codes that creates the recordset and then attempts to send an update to the table. They will probably want you to submit two of the ODBC drivers logs ( Mylog and Commlog ). These can be turned ON from your odbc drivers DSN user interface. (Just remember to turn off all odbc logging when your testing is done since odbc logging is a major performance killer.)

What version number of PostgreSQL are you running. What is the version number of the ODBC driver that you are using? Are they the latest released versions?



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Need help with Visual Basic 6 and PostgreSQL

On 23 Nov 07 9:39 AM, Richard Broersma Jr wrote:

type="cite">

Does your table really have all of these field names?

type="cite">

  In addition to the "" field, I am suspicious of the "(" field.

type="cite">

  I am curious, if the ado recordset is seeing more fields than the table actually has.

type="cite">

What version number of PostgreSQL are you running. What is the version number of the ODBC driver that you are using?  Are they the latest released versions?

  

pg_ctl.exe is version 8.2.5.7260

psqlodbc30a.dll is version 8.2.4.0



I downloaded them a couple of days ago, so I have to assume they are the latest.



How do I contact the developers?



Finn



P.S. Unfortunately I have already spent way more time on this than I can afford. This was supposed to be a quick evaluation to determine which database to replace the Borland with. I really should move on to looking at MySQL.

Re: Need help with Visual Basic 6 and PostgreSQL

--- On Fri, 11/23/07, Finn Lassen wrote:
> There is no "(" field. The "(" is the
> SQL syntax for enumerating the
> fields to be populated.

>> 2007-11-22 20:21:14 STATEMENT: BEGIN;insert into "public"."Contact1" ("", "Department", ..., "(", ... ) values (E'93100765018597400Bru'...

Actually if you've noticed from this insert statement that you've previously posted, "(" is listed as a column name for data to be inserted. This must be caused by either the ODBC or ADO driver is telling PostgreSQL to insert data into a column that doesn't really exist. My gut feeling is that this shouldn't be a problem caused by the ODBC driver hence my suggest to specifically call out the column names of your table when you assign values to your fields.

> P.S. Unfortunately I have already spent way more time on
> this than I can afford. This was supposed to be a quick
> evaluation to determine which database to replace the
> Borland with. I really should move on to looking at MySQL.

No arguments from me on this point. MySQL is a very good RDBMS also, and depending upon your needs it should work out well for you. But if you later find that MySQL's features to not adequately satisfy some of you core development needs you could always give PostgreSQL a second try later on.

Regarding getting in touch with the developers, some of the ODBC developers subscribe to this mailing list. It could be that developers haven't had an opportunity to responded since this is a holiday weekend and may be unavailable.

However, the ODBC project has an official web-page for developers and bug reporting.
http://pgfoundry.org/projects/psqlodbc/
http://pgfoundry.org/tracker/?func=add&group_id=1000125&atid =538

Also, 3rd party vendor (Command Prompt) has developed their own ODBC driver that is supposed to have a more aggressive data caching mechanism. I haven't used it since the Official ODBC driver has worked so well in my case. You could see if this driver produces different results.

https://projects.commandprompt.com/public/odbcng/wiki/Downlo ads

They also have a mailing list dedicated to this driver:
http://lists.commandprompt.com/mailman/listinfo/odbcng

Regards,
Richard Broersma Jr.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Need help with Visual Basic 6 and PostgreSQL

A basic question:
In a connection string, if you only specify a driver name ("PostgreSQL
ANSI"), instead of a DSN configured with the ODBC Data Source
Administrator, do any configuration changes made with the ODBC Data
Source Administrator affect the driver itself?
In other words, does registering an ODBC driver automatically make it
subject to any configurations made with ODBC Data Source Administrator?

I still don't understand the:
"ERROR: Column "oid" does not exist;"
I get when executing the " rsOut.Open ..." statement below.
Can the ODBC driver only handle tables with oid columns?

Dim cn As ADODB.Connection
Dim rsOut As ADODB.Recordset
Set cn = New ADODB.Connection
Set rsOut = New ADODB.Recordset
With cn
.ConnectionString = "Driver={PostgreSQL
ANSI};Server=localhost;Port=5432;Database=postgres;Uid=user; Pwd=secret;"
.Open
With rsOut
rsOut.Open """Contact1""", dbOut, adOpenDynamic,
adLockOptimistic, adCmdTable

Finn


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

http://www.postgresql.org/docs/faq

Re: Need help with Visual Basic 6 and PostgreSQL

--- On Fri, 11/23/07, Finn Lassen wrote:
> In a connection string, if you only specify a driver name
> ("PostgreSQL
> ANSI"), instead of a DSN configured with the ODBC Data
> Source
> Administrator, do any configuration changes made with the
> ODBC Data
> Source Administrator affect the driver itself?

I am not sure, but I do know what my connection string gets stored as when I used a system DSN:

DSN=myDSNname;DATABASE=myDatabase;SERVER=myServer;PORT=5432; UID=myUserName;PWD=myPassWord;CA=d;A6=;A7=100;A8=4096;B0=254 ;B1=8190;BI=0;C2=dd_;;CX=1b103bb;A1=7.4-1

Maybe in you augmented your connection string using these values, it might help?

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

http://www.postgresql.org/docs/faq

Re: Need help with Visual Basic 6 and PostgreSQL

> In other words, does registering an ODBC driver automatically make it
> subject to any configurations made with ODBC Data Source Administrator?

as i understand it: partly. i.e. the connection pooling configuration
options you specify in the ODBC Data Source Administrator is a global
setting.

> I still don't understand the:
> "ERROR: Column "oid" does not exist;"
> I get when executing the " rsOut.Open ..." statement below.
> Can the ODBC driver only handle tables with oid columns?

no. i have no problems accessing a DB with and without oids using this
connection string:

Driver={PostgreSQL
UNICODE};Server=yourserver;Port=5432;Database=yourdb;UID=you ruser;pwd=yourpassword;TrueIsMinus1=1;BoolsAsChar=0;TextAsLo ngVarchar=1;UseDeclareFetch=0

> rsOut.Open """Contact1""", dbOut, adOpenDynamic,
> adLockOptimistic, adCmdTable

try this:

rsOut.Open "SELECT * FROM ""Contact1""", dbOut, adOpenDynamic,
adLockOptimistic

the problem might be the adCmdTable recordset type.

regards,
thomas


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Need help with Visual Basic 6 and PostgreSQL

--- On Fri, 11/23/07, Finn Lassen wrote:
> "ERROR: Column "oid" does not exist;"
> I get when executing the " rsOut.Open ..."
> statement below.

Here are some OID setting from ODBC driver manual:
....
OID Options:

Show Column: Includes the OID in SQLColumns. This is good for using as a unique identifier to update records if no good key exists OR if the key has many parts, which blows up the backend.

Fake Index: This option fakes a unique index on OID. This is useful when there is not a real unique index on OID and for apps which can't ask what the unique identifier should be (i.e, Access 2.0).
....

Perhaps the odbc driver defaults to showing ODBC column. In postgresql versions < 8.0 it was the default for every table to be given an OID (basically every row in any and all tables had a unique number that identified it). However, as of postgresql 8.0, OID columns are no longer added to all table by default. This could be causing the problem since the ODBC driver may be trying to select the OID column from your table even though it doesn't actually exist.

I will look for the ODBC documention that describes that settings that you can pass to the ODBC driver in the connection string.

Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Need help with Visual Basic 6 and PostgreSQL

Re: Need help with Visual Basic 6 and PostgreSQL

Thanks for all your help!

I finally broke down and dropped the table and recreated it WITH OID.
Now everything works, even with these simple strings:
.ConnectionString = "Driver={PostgreSQL ANSI};Server=localhost;Port=5432;Database=postgres;Uid=user; Pwd=secret,B1=128000"
rsOut.Open """Contact1""", dbOut, adOpenDynamic, adLockOptimistic, adCmdTable

I don't understand the difference between "LongVarchar" and "Varchar", but I have to use B1 whether I use the ANSI or UNICODE driver.
(Source content of the memo field I put into text data type can be more than 64K, so I choose 128000. The default is 8196, so it has to be increased.)

Conclusion: Version 8.2.4.0 of psqlodbc30a.dll and psqlodbc30w.dll require tables WITH OID.
(The "OID Options, Show column" -- and presumably "ShowOidColumn" -- has no effect.)

Finn



--
Finn Lassen
Deputy CIO
Axiom
1805 Drew Street
Clearwater, Florida 33765
727-442-7774 voice
727-442-8344 fax
dcio@AxiomInt.com
www.AxiomInt.com



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Need help with Visual Basic 6 and PostgreSQL

--- On Fri, 11/23/07, Finn Lassen wrote:
> I don't understand the difference between "LongVarchar" and
> "Varchar",
> but I have to use B1 whether I use the ANSI or UNICODE driver.
> (Content of the memo field I put into text data type can be more than
> 64K, so I choose 128000. The default is 8196, so it has to be increased.)

For what it is worth, Postgresql has a 1 Gb limit on the size of a text field. So if you know that you ascii chars are 8 bits, then you should be able to store a little over 134,000,000 characters.


> Conclusion: Version 8.2.4.0 of psqlodbc30a.dll and psqlodbc30w.dll
> require tables WITH OID.
> (The "OID Options, Show column" -- and presumably
> "ShowOidColumn" -- has
> no effect.)

I will try to spend some time to play with ADO record sets without using a DSN to see what happens and see with I can come up with. But in the mean time at least you can perform your test comparison.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Need help with Visual Basic 6 and PostgreSQL

Richard Broersma Jr writes:
> For what it is worth, Postgresql has a 1 Gb limit on the size of a text field. So if you know that you ascii chars are 8 bits, then you should be able to store a little over 134,000,000 characters.

Uh, the hard limit is 1 gigabyte, not 1 gigabit.

(Whether you can approach the limit with reasonable performance is
another question ... I wouldn't try it in a 32-bit machine, for sure.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: Need help with Visual Basic 6 and PostgreSQL

--- On Fri, 11/23/07, Tom Lane wrote:
> Uh, the hard limit is 1 gigabyte, not 1 gigabit.
Thanks for the clarification Tom.

Regards,
Richard

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly