VPME9.0 and MySQL

The place for peer-to-peer support for Visual ProMatrix versions older than VPME 9.1.

VPME9.0 and MySQL

Postby Stephen Ibbs » Sat Aug 23, 2014 3:33 am

Wasn't sure where to post this ...

Our internal Customer system is a view-based application in VPME9.0, and one of our programmers, Paul, is modifying it to work with MySQL, to eventually tie in with our on-line ordering system for customers.

I have never used MySQL and being only 18 months from retirement it is like the nether regions of hell - I don't want to go there!

We have a situation where at least two members of staff have to be able to view the same customer record, and this causes a hang and eventual crash, we presume because of a record-locking issue in ODBC/MySQL.

Has anybody any experience in this area, sample code, links to on-line documentation, anything that can help us investigate this further?

Many thanks - Stephen
Stephen Ibbs
Data Developments
Stephen Ibbs
 
Posts: 77
Joined: Mon Jun 14, 2010 1:46 pm

Re: VPME9.0 and MySQL

Postby stecenko » Sun Aug 24, 2014 1:42 pm

hi steve,

could you explain more?

what's the sequence of events? who hangs: the 1st user on the record when saving a change? or does the 2nd user hang when selecting the customer record that the 1st user has just selected?

also, have you tried doing simple SQL execs rather than VPME views to select a common record? If you hang with sql exec then the problem is with sql. If you don't hang, then the problem is with VFP/vpme.


I believe that sql should return a read only cursor for the second user if the first has a lock.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: VPME9.0 and MySQL

Postby Stephen Ibbs » Tue Aug 26, 2014 2:14 pm

Hi Paul

It is when the second person attempts to access a record currently being viewed by the first user.

No we have not used EXEC, it is VPM views based.

Many thanks
Stephen Ibbs
Data Developments
Stephen Ibbs
 
Posts: 77
Joined: Mon Jun 14, 2010 1:46 pm

Re: VPME9.0 and MySQL

Postby stecenko » Wed Aug 27, 2014 4:35 pm

Here's what I did.

I created a brand new VPME 9.0 project. I created a DBC with a connection to an MS SQL database. I created a remote view to a table in the database.

Then I created a simple toolbar form with view as the ISA. Built the exe and ran two instances on my computer.

Worked just like it is supposed to. I could view the same record, Add a new record on one, look at it on the other. Change it, see the change all that stuff.

Now you said you are using My SQL. I don't think that makes any difference at all. For simple selects SQL is SQL is SQL.

You said you're using ODBC, I'm using a connection string.

I don't know if you are using that wacky local to remote switch. I'm not.

So my test is not exactly the same.

I think you should create a new project with just one remote view, because the problem could be in your DBC or maybe there's something in one of those pre- methods that we used to use in 9.0 that is triggering a lock of some sort.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: VPME9.0 and MySQL

Postby stecenko » Wed Aug 27, 2014 5:19 pm

Is it possible that the view that is selecting the record is long-running; i.e. has a function that requires reading many records.

Something like...
Select customer.name, min(activity.date) as firstContact, max(activity.Date) as lastContact, sum(inoice.total) as invTotal from customer join activity on customer.Pk=activity.fk join invoices on customer.Pk=invoices.fkgroup by customer.name

That could interfere with the second user.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: VPME9.0 and MySQL

Postby Stephen Ibbs » Tue Sep 02, 2014 5:54 am

Thanks Richard - we will investigate further.

Life a bit stressful at the moment! We are moving tomorrow to bigger offices on the Science Park, having a new VOIP phone system and 100MB broadband, and appointed 3 additional staff, starting tomorrow, all with C# expertise, so our future destiny is pretty much decided !!
Stephen Ibbs
Data Developments
Stephen Ibbs
 
Posts: 77
Joined: Mon Jun 14, 2010 1:46 pm

Re: VPME9.0 and MySQL

Postby paulvaswani » Tue Sep 02, 2014 7:26 am

Hi Richard

Thanks for your help on this. To answer your first question the sequence of events is that if two users are viewing the same record and user1 saves a change to the data, user2 then gets an ODBC error along the lines of "Timed out waiting for a lock" at the point that they attempt to move off the record.

I have been trying to work within the VPME framework as far as possible, so I have been using remote views and the LocalToRemote() method. I do also use SQLexec() quite a bit within the application but all the views in all the forms are remote views.

I followed your advice and created a very basic new VPME application using the same methodology and tried it against the same data and it doesn't seem to suffer the same problems. That makes me wonder whether my problems may be to do with the kind of views I have used, as you suggest in your final point. The live application does include some complex views whereas my simple test application doesn't. This is an issue I have not come across before so I wonder if you would be kind enough to explain a bit further... If I have a view which accesses child records and then groups by the header, as in your example, it would still only be the header fields which would be writable so I can see that the view would take longer to run but once it has run why would it cause this kind of problem in a multi-user environment? Do you know of any ways around this, other than abandoning remote views and LocalToRemote(), which would mean a fairly serious rewrite.


Many thanks,

Paul Vaswani
Paul Vaswani
Data Developments
paulvaswani
 
Posts: 10
Joined: Tue Sep 21, 2010 5:18 am

Re: VPME9.0 and MySQL

Postby stecenko » Tue Sep 02, 2014 12:12 pm

Ok, here's one thing to check...
For the complex view, go to the Update Criteria tab and be sure that...
1) the Table combo box shows only the parent table, not All Tables; if it shows All Tables, change it just the parent table.
2) be sure no child fields are marked as updatable.

If you are doing a compound view, like this..
select customer.pk, customer.name, type.code, max(child.date) as maxDate
from customer
join type on customer.TypeCode=type.pk
join child on customer.pk=child.customerPk

and you All Tables in the combo box, I think you might also have a lock on type.

You don't have to abandon localtoRemote(); but you might have to create a separate DBC with just your complex remote views in it. I'm not saying that there is a problem with LocalToRemote; I never used it because I was in VPME 9.1 when I started using SQL.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: VPME9.0 and MySQL

Postby stecenko » Tue Sep 02, 2014 12:32 pm

You said, "the sequence of events is that if two users are viewing the same record and user1 saves a change to the data, user2 then gets an ODBC error along the lines of "Timed out waiting for a lock" at the point that they attempt to move off the record.'

Has User2 made a change to the record? If not, then you will have to check your code to determine why VPME is trying to obtain a lock. If User2 hasn't made a change, then there's no need for a lock.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: VPME9.0 and MySQL

Postby paulvaswani » Fri Sep 05, 2014 8:26 am

Hi Richard

Many thanks. The update criteria drop down was set to the main table only (although I have to admit I thought that only determined which fields were displayed in the list, I didn't realise it had any bearing on which fields were actually updated).

In answer to your second question, user 2 wasn't making any changes but there seems to be something in the framework triggering a save anyway.

I think I may have resolved the problem, although I need to test further to see whether what I have done has created any different problems. I have used a setting in the MySQL ODBC driver which tells it not to wrap every operation in an automatic transaction. This seems to have fixed things but, as I say, more testing is needed...

I really appreciate all your help and suggestions. If I learn anything more I will post here.

Paul
Paul Vaswani
Data Developments
paulvaswani
 
Posts: 10
Joined: Tue Sep 21, 2010 5:18 am

Next

Return to VPM - older versions

Who is online

Users browsing this forum: No registered users and 1 guest

cron