?
Solved

adOpenDymanic / adLockPessimistic Recordset Fails

Posted on 2004-04-22
5
Medium Priority
?
383 Views
Last Modified: 2012-06-21
I have a multi user application in development and therefore have need to use dynamic recordsets within certain
areas of the system, however despite my best efforts I can't seem to get them to function correctly with MySQL.

I have flagged 'Enable Dynamic Cursor' within the ODBC Driver settings, but still nothing. The same code works
great when used with a MS Access backend !

I am of course aware that MySQL Server does NOT support Server side cursors, but my understanding is that the
ODBC driver does all of the work for you, client side (granted at a substantial sacrifice in speed).

Anyway here is some code:

____________________________________________________________________________________________

'+--------------------+
'|  Connection info. |
'+--------------------+

Global Const s_Conn = "Driver={MySQL ODBC 3.51 Driver};" & _
                      "Server=Chiron;" & _
                      "Port=3306;" & _
                      "Option=32;" & _
                      "Stmt=;" & _
                      "Database=live;" & _
                      "Uid=root;" & _
                      "Pwd="


'+---------------------+
'| Recordset snippet |
'+---------------------+

With o_Recordset

        .Open s_SELECT, o_Conn, adOpenDynamic, adLockOptimistic

End With

____________________________________________________________________________________________


If data is altered @ a different location in the company I am required to perform a 'Requery' in order for it to be
visible within my recordset, could it some how be converted to a static 'CursorType' with out my knowing ?

I'm begining to loose the plot so if anyone has any ideas I would appriciate some feedback. Any additional code or
general/specific information required please let me know!

This is a fairly important question so I hope the points offered with spark some interest !!

Cheers

Chris
0
Comment
Question by:DangerM0use
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 1

Author Comment

by:DangerM0use
ID: 10888162
Sorry I forgot to mention versions:

ODBC: 3.51
MySQL: 4.0.14-nt

Regards
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 10888573
Rather than explain it all here I will point you at an article I wrote specifically on the subject of VB cursors and locks in relation to MySQL:

http://www.vbmysql.com/articles/cursorsandlocks.html

If you have any questions after reading it please let me know, but I believe that it will answer your questions.
0
 
LVL 1

Author Comment

by:DangerM0use
ID: 10889352
Thanks for the swift feedback, I have read your article in the past, along with several other, and have found them to be
extremely informative and helpful (Thanks from the VB brotherhood!).

I am however struggling to grasp 'Dynamic' cursor types, using your analogy of selling tickets, I would assume that when
a client updates a seating record to have been sold, all other connected client machines would update themselves, to
reflect that sale.

The system I am trying to apply this principle to involves lab samples which are awaiting analysis, lab staff all have their
own systems and select samples to analyze as they appear on their work list (which should deminish, as other lab staff
complete their analysis).

I expect that as lab staff update the records of a particular sample all other machines remove it from the list. It is this
function that I am failing to produce.

I have followed (to the best of my knowledge) your article, both the connection & recordset objects have the adUseServer
cursor location specified , I have enabled the 'Enable Dynamic Cursor' & passed option 32 in my connection string. finally
the table I am querying has a primary key, but still I need to requery my recordset before I see any changes.

Is there some fundamental aspect that I am missing ?

Cheers
0
 
LVL 17

Accepted Solution

by:
Squeebee earned 2000 total points
ID: 10901378
This is not an automated process under mySQL. To do what you want use client-side cursors and a static recordset. Issue periodic refreshes of your recordset with atimer control. Dynamic cursor simply means that when you move forwards and backwards in the recordset it requeries, it does not cause changes to be pushed to the client.
0
 
LVL 1

Author Comment

by:DangerM0use
ID: 10923323
Ah... that would explain matters, when I had read that the dynamic recordset constantly requeries I assumed (wrong!) that the WillChange event would be fired, should
the data have changed outside of the application.

Well your question does answer my question, though it is not the answer I had hoped for that is not your fault, and I appriciate you putting me right. I guess I will end up
using some sort of timer to refresh the recordset.

Thanks again for your articles.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question