>>>>> "Jacob" == Jacob D Parr <archiver@stripped> writes:
Jacob> This message was sent from Geocrawler.com by "Jacob D. Parr"
> <jacobp@stripped>
Jacob> Be sure to reply to that address.
Jacob> I get the error "S1109: [TCX][MyODBC]SQLSetPos with lock or refresh is not
> supported" when I attempt to call the update method in VB's DAO 3.x library. The sample
> code is below:
Jacob> Set wrkODBC = DBEngine.CreateWorkspace("wrkODBC", "root", "", dbUseODBC)
Jacob> Set richworksDB = wrkODBC.OpenConnection("Richworks", dbDriverComplete,
> False)
Jacob> Set rec.Value = richworksDB.OpenRecordset("SELECT id, buying_plans FROM
> LON_Prequalify", dbOpenDynaset)
Jacob> Do While rec.EOF = False
Jacob> rec.Edit True
Jacob> rec.field("buying_plans").Value = rec.field("id").Value
Jacob> rec.update
Jacob> Debug.Print rec.field("id").Value & " - " &
> rec.field("buying_plans").Value
Jacob> rec.moveNext
Jacob> Loop
Jacob> The table does have a timestamp and a primary key...
Jacob> | TIMSTAMP | timestamp(14) | YES | | ||
Jacob> | id | int(11) | | PRI | 0 ||
Jacob> My ODBC options are as follows:
Jacob> Don't optimize column widht
Jacob> Return matching rows
Jacob> Trace MyODBC
Jacob> Use compressed protocol
Jacob> What am I doing wrong, or does it just not work?
Hi!
The problem here is that as MyODBC doesn't support updateable cursors natively
it doesn't support SQLSetPos with a lock or refresh option.
On the other hand, if you use the microsoft default cursor library,
this should would (I don't however know how to force VB to use this)
It would be nice to know if VB uses the 'flock' or 'refresh' option
to SQLSetPos in this case. Can you mail me the relevant log entries
from a ODBC log that shows the argument to SQLSetPos ?
My guess would be that in the above case the problem is that VB
requires updateable cursors for the above code to work and the Microsoft
cursor library can't handle this properly :(
One way to fix this is to not use record sets but instead use direct
SQL statements for the update.
We are just discussing with Source-Exchange to put out a request to
finding someone that would be interested in extend MyODBC to have full
cursor support. We hope this will enable us to fix the cursor
problem in MySQL relatively soon as we ourselves don't have time to
look at this during the next 2 months.
Regards,
Monty
PS: Sorry for the long delay before replying, but I just come back from
a 2 weeks vacation trip.
