Re: ODBC in VB6

DanPerlman wrote:
> Hi,
>
> I have never used ODBC in a VB6 program before.
> I am trying to write some simple code to append data from an access 2k
> table to a postgres table.
> My DSN works with access.
> I am getting a "type mismatch" error when trying to open the postgres db.

First off, for a good answer I think we need some more information,
specifically what version of PostgreSQL you are using, as well as what
version of the ODBC driver you are using. Also, it would be helpful to
know what your settings are on the driver.

With that said, let me outline what has worked for me.

I am currently using version 7.3.2 of PostgreSQL running under Solaris.
I am using version 7.03.01 of the ODBC driver on my XP workstation.

What I have found to work best with my VB application is to use ADO,
instead of DAO, for working with the PostgreSQL database. There are many
reasons behind this, which if you want to dig in the ODBC and GENERAL
mailing list archives, you can see what I went through to get to this point.

Along with DAO, I use a DSN-less connection. This is mainly because my
application is currently 2-tier, and I don't want to have to set up, or
have set up, 75-100 ODBC DSNs on the client machines. If your
application is a web application or something similar (that is, the
database isn't likely to have multiple clients connecting, or the pool
of machines connecting is small, or non-migrating), then a "hard" DSN
connection would likely be preferable.

My code for connecting with and updating the database then is as follows
- first you need to set up your connection:

---

Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "dsn_name" ' DSN Connection
conn.Open "driver={PostgreSQL};server=xxxx;port=yyyy; _
    database=zzzz;uid=;pwd=;ksqo=;" ' DSN-less Connection

---

A few words here:

Choose either the DSN or DSN-less connection (comment out one of the
lines, or only type one in). For the DSN connection, set the name of the
DSN you have defined in the connection string you pass. For the DSN-less
connection, you will need to sent the server name (xxxx), the port it is
on (yyyy), the database name (zzzz), and perhaps the user name (uid),
and password (pwd). Leave ksqo set to nothing. This variable is the
"keyset query optimization" flag - I found that setting it caused
problems (whether in the driver for a DSN connection, or in the
connection string for a DSN-less connection).

Also, note that I haven't defined a Workspace. There is a lot of code
floating around on the internet about ADO, and nearly every example
shows using a workspace. I am not certain why this is, but I found that
the code I was using worked fine without one, and there didn't seem to
be any need to include the extra code (I tried it out, it didn't seem to
help nor hinder any). If you feel you need it, by all means include it.

Now, you need to get the recordset:

---

Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open strSqlStatement, conn, adOpenDynamic, _
    adLockOptimistic, adCmdText

---

Here we set up a new ADO recordset object, then using the connection
object (conn) and a passed SQL statement, we select our recordset. You
will notice I am doing optimistic locking. There are other options you
can pass (for example, if all you are doing is reading, you might pass
adLockReadOnly instead).

A few more notes: wildcard characters in SQL are different for Access
vs. PostgreSQL. Keep this in mind if you are accustomed to the wildcard
characters for Access.

Another thing to note is that if you are doing all of your updates via
SQL, this is all the code you are likely to need (ie, if you do SQL
INSERTs and DELETEs). In fact, if you were developing a new application,
I would reccommend doing this, instead of programmatically from the
application. It should allow for more portable code, plus you get the
database backend to do all the "heavy lifting", among other pluses. If I
had the chance to redo my application this way, I would.

If not, read on - to programmatically read from the recordset:

---

With rs
   '
   If Not (.BOF And .EOF) Then ' Make sure there are records selected
     '
     .MoveLast
     .MoveFirst
     '
     strData = ![field]
     '
   End If
   '
End With

---

To programmatically add to the recordset:

---

With rs
   '
   .AddNew
   '
   ![field] = strData
   '
   .update
   '
End With

---

To programmatically update the recordset:

---

With rs
   '
   If Not (.BOF And .EOF) Then ' Make sure there are records selected
     '
     .MoveLast
     .MoveFirst
     '
     ![field] = strData
     '
     .update
     '
   End If
   '
End With

---

Note that no ".Edit" is needed - in ADO this is unnecessary.

To programmatically delete from the recordset:

---

With rs
   '
   If Not (.BOF And .EOF) Then ' Make sure there are records selected
     '
     .MoveLast
     .MoveFirst
     '
     .Delete
     '
   End If
   '
End With

---

There are a few other things I want to note about using ADO (and
PostgreSQL). One thing is that in using ADO, for some reason there isn't
a way to get a recordcount (at least, as I have set things up). That is,
if you try something like "x = rs.RecordCount", you will *always* get -1
as the value. IIRC, it has to do with setting the cursor location to
that of the server. However, setting it to the client introduces other
issues. I have found that the best way to get the record count is to
simply do a SQL "SELECT COUNT(*)" with the same criteria as your select.
Obviously, this method isn't dynamic (ie, if you add or delete records,
the count doesn't change), but it does work.

Another thing I have found is that strange things can occur if you
attempt to update records within a recordset that is already open and is
being updated. That is, if you open the recordset, and in the middle of
the update you call a function (whether explicitly, via a timer, or some
other method) that selects the any of the same records and updates them,
you may get errors by the second update, or when it returns and you try
to close the first. With DAO, this isn't a problem. I think it is really
bad habits "taught" by DAO - that you shouldn't be able to update the
same record everywhere in the application and have the changes reflect
everywhere. Not sure...

Finally, close the recordset and the database connection when finished:

---

rs.Close: Set rs = Nothing
Set conn = Nothing

---

Also, if you notice, I don't show using "bound controls" in my examples
(as you seem to have been showing in your example). I am of the position
of having the application do the work, and I have also found that bound
controls, while making initial application development quick, tends to
make maintenance more difficult in the future. I have also run across
reports on the web about why using bound controls is a bad thing, and
the arguments were persuasive. Whether this is a new application or not,
I would like to persuade you to read up on this issue, and decide for
yourself.

I hope this response helps you in some manner. Much of what I have
outlined here came from various experimentation on my own, as well as a
ton of insight and help from both the ODBC and GENERAL PostgreSQL lists.
Even so, I am sure I am missing things, but so far the above methods
have worked within my application.

Good luck!

Andrew L. Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.