List:MySQL ODBC« Previous MessageNext Message »
From:Michael Widenius Date:November 8 1999 1:36pm
Subject:myODBC, VB6.0 and Update Metho
View as plain text  
>>>>> "Jacob" == Jacob D Parr <archiver@stripped> writes:

Jacob> This message was sent from 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?


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.


PS: Sorry for the long delay before replying, but I just come back from
    a 2 weeks vacation trip.
myODBC, VB6.0 and Update MethoJacob D. Parr25 Oct
  • Re: myODBC, VB6.0 and Update MethoKen Menken25 Oct
  • myODBC, VB6.0 and Update MethoMichael Widenius8 Nov
RE: myODBC, VB6.0 and Update MethoGilles Cordier18 Nov