Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

adOpenDymanic / adLockPessimistic Recordset Fails

Posted on 2004-04-22
5
Medium Priority
?
392 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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 Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

609 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