Source language: Translate to:

Multi User record problem

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Multi User record problem

Postby DaveJ » Sun Jan 05, 2014 2:16 pm

Hi Guys,
This one has me stumped.
I have created a Multiuser database application where details of jobs are stored in a table. These jobs could be edited by any one of a number of users but I don't want the last user to save over a previous users content. Ideally it would be nice to lock the record while it is open by another user.

It seems that something strange is going on as if two users are in the same record the first user edits something and saves and then the second saves results in dbpError=a change the Row cannot be located for updating. Some values may have been changed since it was last read. I assume that this is showing an inconsistency between the cached record and what Neobook wants to update.

Also if User 'A' changes the content of a field, user 'B' doesn't see any changes when browsing through records unless a query is performed. Is it possible when Neobook see's a change that it refreshes the stored record?

My experience with databases is very limited and so I might be completely off track.
I would be grateful for any suggestions.

DaveJ
DaveJ
 
Posts: 90
Joined: Tue Dec 30, 2008 11:06 am

Re: Multi User record problem

Postby Neosoft Support » Mon Jan 06, 2014 11:37 am

You may be able to prevent this by using a server-side cursor. From the help file:

By default NeoBookDBPro uses something called a client-side cursor. Simply put, this is a method of accessing data that relies on the client application to handle most of the data storage and processing. Client-side processing is very flexible and generally provides the fastest performance for small to medium sized databases. For large databases, however, a client-side cursor can sometimes consume too many system resources resulting in poor performance. To compensate for this problem, you may want to use a server-side cursor when working with very large databases. In NeoBookDBPro you can switch to a server-side cursor by adding "CursorLocation=Server" to your connection string. For example:

dbpOpenDatabase "MyDB" "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[PubDir]AddressBook.mdb;CursorLocation=Server"

With a client-side cursor (the default) all data is copied to the local machine and processed there. This provides access to features not normally supported by servers such as sorting and indexing. When using a SQL query, only the data returned is copied to the local machine. A server-side cursor doesn't provide as much flexibility, but is often more appropriate for large databases, and may be required when the size of a database exceeds the available memory and disk space available on a local machine.

Is it possible when Neobook see's a change that it refreshes the stored record?


Unfortunately, the database engine does not inform DBPro when changes are made.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Multi User record problem

Postby DaveJ » Mon Jan 13, 2014 12:42 pm

Thanks for the reply but now this raises more questions.
I am using a mysql database and most functions now don't seem to work.
If I try to show a table in a grid, I get 'Dataset does not support bookmarks, which are required for multi-record data controls'
I open the database with the following settings;
dbpOpenDatabase "db" "Provider=[Provider];Driver={[Driver]};Server=[Address];Database=[Data_source];User=[User_ID];Password=[Password];Option=3;CursorLocation=Server"

The are some of the variables;
Provider=MSDASQL.1
Driver=MySQL ODBC 5.2 ANSI Driver
Address=192.168.1.200
Data_Source=db
Password=dave
db.$Status=Connected
db.inventory.$State=Browse
db.inventory.$RecNum=0
db.inventory.$RecCount=-1
db.customer.$State=Browse
....
...
..

This table contains 3 records but according to $RecCount it equals -1
All the settings worked before I set 'CursorLocation=Server'

Am I doing something wrong? Has anyone else successfully created a multi-user application using mysql and the cursor location the server?
Thanks in advance

Dave J
DaveJ
 
Posts: 90
Joined: Tue Dec 30, 2008 11:06 am

Re: Multi User record problem

Postby Neosoft Support » Tue Jan 14, 2014 10:52 am

DBPro provides an interface to various database engines (Access, mySQL, Oracle, etc.). The ADO connector, ODBC driver and the database engines themselves handle all of the interaction and data processing. Getting the connection string, drivers, programming, etc. all correct for multi-user access can be quite difficult. Unfortunately, in multi-user mode some features are not available because they conflict with the server-side cursor.

Having said that, try adding "MultiUser=Yes" to your connection string. For example:

dbpOpenDatabase "MyDB" "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[PubDir]AddressBook.mdb;CursorLocation=Server;MultiUser=Yes"

This will set the LockType to "Pessimistic" which may help.

An explaination of the complexity of getting multi-user to work can be found here: http://www.mysqlab.net/knowledge/kb/detail/topic/visual%20basic/id/5911
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Multi User record problem

Postby Neosoft Support » Thu Jan 16, 2014 11:49 am

Instead of the cursor and multi-user connection string, you might try adding an AutoInc to your table and make it the Primary Key. The "the Row cannot be located for updating" error is often associated with the lack of a valid primary key in the table. The database engine can't find the record the user had edited because it has changed to much. Adding a non-editable AutoInc field as the Primary Key usually solves this problem.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Multi User record problem

Postby DaveJ » Thu Jan 16, 2014 5:03 pm

Thank you so much for the advice.
After a lot of research I have noticed that it seems only to be the 'find' function that looks at the cached database content. The query command seems to retrieve updated data on a local cursor setting.
I have also decided to go with using a lock table and look up if another user is editing a record.
I will do a bit more research on what functions work best. Much more work to do to see what will work.

I do seem to have opened a can of worms!

Thanks for all your help.
DaveJ
 
Posts: 90
Joined: Tue Dec 30, 2008 11:06 am


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests