cursortype and locktype parameters 1

[avivit]

Hi,

I am using ms-access, a database which is updated frequently (actually all the time) by users.
Each use of an asp page by any user updates one record in this database.
In order to keep multiple updates to the same record, when occur, up-to-date (and of course to other records as well), and in order to avoid crash, but not slower the access, what cursortype and locktype shall I use?

adOpenDynamic, AdLockOptimistic
Or
adOpenDynamic, adLockPessimistic
Or
adOpenDynamic, adLockBatchOptimistic

(I am not what should go wrong if chossing one of the above)

Thanks

 

[Tarwn]

I generally prefer to never use the dynamic method, it has the highest overhead and with a web application if someone inserts a record during the 1 second I have the recordset open then I am not to concerned. The optimistic won't help if someone saves changs to a record while another person is editing it because the content has already been delivered to the browser. In cases where that is a concern you generally have to build your own locking mechanism.

Same with the lock, if I am selecting a record to display it for editing, the lock will be gone by the time the user is actually seeing the record and changing values, so it won't help.

I couldbe completely wrong in this philosophy, but here is how I see it as it concerns web applications:
If I am selecting record for display/editing/etc than I just go optimistic and static or forward-only for the performance. I use a connection object and SQL to send in updates and inserts so I don't have to worry about locks and such because I'm not opening the table into a recordset (kinda nasty performance wise to do that).

If I did need some sort of locking when a user was editing a record I would probably build a check out table in my database and do change tracking. Something like:

[b]Rough table design[/b]
change_id - auo incrementing id
user_id - user id of person making changes
table_name - string name of table they are editing from
record_key - numeric primary key of record they are editing
updated - True/False - set to true if they edit and update, left as false by default so ifthey do click edit and then go off and look at their hotmail instead I can tell later that they didn't do anything
change_date - Date/Time of either beginning edit or submission of changes

Then when someone requested a record to edit I would insert a new record into the change table. I could also check that change table and see if anyone else is marked as editing the same record in the past few minutes and let the user know. When the user submits the record I could let them know if anyone started editing the record while they wereediting it. This method is less secure than a full blown lock system, but it also means I don't have to alter my global.asa to clean up any locks people may have.

The other option would be to create a Lock collection as an application variable and each tim a page is loaded have it remove any entries older than 5 minutes plus remove an entry after a user submits updates. But again we're getting complex

I'm not sdure if Access has a manual locking scheme. I know other db's generally will allow you to lock records/tables manually and then unlock them later, but I don't know if this is state based (if so than it is again useles from an ASP perspective because ASP pages don't keep state).

Any thoughts or corrections from the more SQL-ish gurus out there?


_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[avivit]

Thanks for explaining. I know much more now.

I should explain more about my files, so the purpose will be clearer.

I am making a statistic report (which is perfectly working in the local host, i.e. - on my pc only), counting the number of hits each link in certain pages is clicked:
I have an asp page which displays a list of urls(external links!!!) which is taken from titles db (one database).
I would like to count the hits of each link, and keep the statistics data in statistics db (database 2).

I have 3 cases:
a. Inserting a new record when the title does not exist in the statistics db. In this case I am taking the data from database 1 (according to ID number)
b. Updating (incrementing) the hits of a certain site title in the statistics database, when condition 1 (a certain condition) is true.
c. Updating the hits of a certain site title in the statistics database, when condition 1 )same condition as above) is false.

I see you use update in sql when u want to update a record, and in this case there is no problem when multi users try to modify the same record at the same time,and locking should not be dealt by me,if I got u right(???)
What does happen when 2 users try to modify the same record in this case? (In my site, when the user clicks a link, the statistics db should be updated, and the user sees in his browser, the target link (i.e. - the site he cliked and asked for).

I would like to find the easiet solution.
Currently, I do not see how I can NOT open a recordset, thus avoiding multiple updates problems. I must, in order to check if the title exists or not. Must'nt I?
If the title is not there, I am inserting a new record, and am updating if does exist.
If you find a way for me, not to open a recordset, I would be happy to learn about it.
If you think it is not possible, pls let me know as well.

Any help is very much appreciated.
Thanks

 

[Tarwn]

Ah, I see.

So basically what you want to be able to do is see if the target record exists already (one select) and then either insert a new record if it doesn't (one insert) or update the existing one if ti does(one update). And obviously you do not want another user going in at the exact same time and creating the record while your in the middle of the process because you would have duplication and such.

You could use a recordset object andthe Open command to achieve this, remember I only stated that I prefered not to use that whenever possible, there is no rule against it

In the end it is up to you to decide which set you would want to use. You could use a static cursor and pessimistic lock, which would let you view the data more efficiently than the dynamic cursor and block others from changing the data, or an optimistic lock and dynamic cursor so that only the one record you are changing will be locked and only as the Update function is called to send any changes back.

My preference is to never use the recordset.Open command because it is inefficient. One way to get around using it in this case is to create the entry in the statitics db (if your tracking links total, not by date) at the same time you create the link in the first db. This way you will never have to see if the link has a corresponding entry in the statitics db, you can simply update and be on your way.

Tarwn's Solution #1
This assumes that you are tracking total hits to a link, not hits/day. It also assumes that users can add a new link to your list in the first database through some sort of minimal form.
Alter the processing page the submits the new link so that after it adds the link it then adds a new record to the statistics table with a hit count of 0.
When a user clicks a link and you need to update the number ofhits in the statistics db, simply do an update that increments the field: "Update MyTable Set HitsField = HitsField + 1". No muss, no fuss.

Dealing With Hits/Day
If you are doing hits by date then I guess there is no way around it, you will need to check if the record exists for the current date. No matter what type of record locking you do, since two users may try to add new records for the same link at the same time, you will not be able to lock a record tat doesn't exist anymore and one of them will receive an error for creating an identical record.
So here is my efficient, Open-free way of handling this.
1) Select from statistics for the link and date
2) If resulting recordet is EOF
2.1) On Error Resume Next
2.2) Attempt to insert new record
2.3) If an error has occurred, send the update string (record already existed)
2.5)Else (recordset is not EOF)
2.6) Increment hits field with an Update statement

Last Solution
The last solution is to, of course, use the Recordst.Open command to open the table. This is the least efficient because the database has to send you a copy of all the data in the table, if you used a dynamic lock the recordset will update portions of itself as you scroll through, wasting more time, etc. If you opened the whole table you will have to search through for a record that matches the link and date, which is much less efficient for ASP to do ten the originating db. Then at the end you will either be doing an update on one field (optimistic lock: the record will lock when you call update and possibly have an error if someone ele updated it; pessimistic: every record you looked at would lock when you looked at it, more communication, more lost processing time) or doing an insert (there is no way to lock a field that hasn't been aded to tghe db yet). In the end, even using these locking methods, you risk two users trying to insert a new record for the same criteria at the same time. I believe this will give you an error just like my earlier method.

---

Sorry tat I have provided no solid answers, but my approach is by preference rather than law. I am pretty sure that the information I have provided in the previous sections is all correct, but I cannot guarantee it 100% simply because some of it is based on benchmarks I have done in the past and some is based on information I have gleaned actually using the processes (but have never seen an adequate explanation from MS about). I hope tey have helped,

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[avivit]

Ok.
I appreciate you help very much.
I have learned a lot.

1. I use total hits.

2. the first database holds all the links that are
displayed in the asp page.
The asp page is taking the data from the 1st database,
so there s no way that a link is clicked and is not in the 1st database.

3. Modifications to the the 1st database (adding links),
is not added by a form, nor by asp, but a local copy of the database is being modified, and once a day it is replacing the database that is on the live server (simple copy/paste).
There are many reasons to it.
One of them, is that I do not want that databse to be a read only database. But it is more than just that.

4. Reading your great ideas, I have a preceeding idea.
Do tell me what you think of it:

If the statistics db and the 1st db will hold the same links, then I won't need to use insert at all.
Then, I will have to use 2 kind of updates, as I mentioed above (for 2 conditions), for I do not know which update should take place, as I do not know if the condition is true or false, if I do not open a recordset.
Btw- What happens when I use something like the following:
"update ...where condtion1=true",
but the condition is false for all records?
Will it create an error?

Also, if I use 2 updates one after another in the same asp page. Will it slower access, etc.?

Also, the question is how to synchronize the 2 databases all the time? and also doing it without interferring the users to work fluently. Afterall they only want the url.

And maybe even better idea:
If it is possible to use an update query even when no records meets the criteria, and no error will occur, or if possible to use a resume.next in that case (if it is "legal", and is not harmful(???),
than I can use 2 updates in the asp page one after the other + one insert, without the need to use a recordset, and without checking anything first (sounds bad ha...):

a. Updating hits in the statistics database, when condition 1 is true (=title exists) and condition 2 is true.
b. Updating the hits in the statistics database, when condition 1 is true (=title exists))and condition 2 (same condition as above) is false.
c. If possible to check if 2 above updates were wrong, for the title does not exist at all , than use insert to a different table in the statistics db, and once day (if possible) to join the tables to the first table, including joining the same records from table 2 if created (maybe even possible to do it automatically in a certain time)

Sounds sick and totally irresponsible?

 

[Tarwn]

No problem with an update that dosn't match. You could send updates all day long with field criteria that won't be matchd and it will just sit there and take it (unless you mispelled a field or some other type of syntactic error).
Don't take my word for it, go ahead and abuse your db Try doing a "UPDATE YourTable SET YourFieldName = 'something' WHERE 0=1".
Obviously 0 will never be equal to 1 so nothing happens, no biggie

You could actually accomplish both updates with a single statement just by putting either True or False in it, so you really only have a single update statement.

So really all you will need to do is:
"UPDATE YourTable SET hitField=hitField+1 WHERE linkField = '" & whatever & "' AND conditionField = " & myBooleanVariable

There shouldn't be any errors unless you have a field it can't find or something like that. As I mentioned before, it doesn't bother the database if no records match the criteria, only if there are synctactic problems. This is just like trying to SELECT from an authentication table to see if a record exists for a specific username/password combination. It either returns a matching record or returns nothing at all, it won't give you an error because nothing matches.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[avivit]

Thanks for approving to abuse the db.

Actually I can not use one update, as I should update more fields that just the hits in one of the cases, nlike in the other case,
but thanks for showing the syntax, I may use it someday.

I guess I can not check if the update query updated anything or not, and therefore to insert new record if not.
In order to check it I have to open a recordset, therefore going back to where we start from, back to the thing we wanted to avoid.

How about a totally different approach, but safer?
Maybe for each day, I can insert records for each link that was clicked, all to a new table,
and update the main table from this table, once a day (maybe at night) if possible.
What do you think?

Also, I would like to get a report once a month, in excel file, from the database.
I found a site that explains how to create an excel file from a database on server, but how about the timing?
How can I set a date each month (the end of each month), to create such file?What permissions should be given?
And also, where is such scheduling done? In IIS?

Thanks

 

[Tarwn]

Affected Records: Actually if you take a look at the Connection.Execute method, you should be able to give it a variable as a second argument and after you execute your statement that variable will tell you how many records were affected:

http://www.w3schools.com/ado/met_conn_execute.asp

Oh well, I really liked the one update method, but I guess there is no problem with two, still cheaper processing-wise than opening the table into a recordset.

Keep Db's Sync'd: I would honestly consider making a tiny admin page that lets you add records to the databases simultaneously. You could even make it back up your original database if your worried about that and hardcode in a password. It wouldn't be that difficult:
Login form - body tag, form tag, 1 or 2 inputs, submit
Verify - if values are equal to hardcoded values, continue, else show a nasty message like "Bad User, I recorded your IP Address and it is " & Request.ServerVariables("REMOTE_HOST")
Backup - Use the FileSystemObject's CopyFile function to make a backup copy of the database.
Add Link - Simple form with however many entries you want
Submit Link - Open connections to both db's. Insert data to both db's, done.

That simple tool would be a lot easier to code than including a temporary table and having days where hits are inserted into temp records and so on. I added the backup afterwards because I remember you saying something about not wanting to write o the live db, this ay your still writing to the live db but if you make a whole slew of mistakes you can go back to the backup.

Excel
If you creat a page to give you a report for the whole month, all you would have to do is visit itthe first day of the next month to get a new report. Generally having things run automatically is something that needs to be scheduled using Scheduled Tasks. If you don't have access to the Scheduled Tasks on the server your pags are hosted on than this option wouldn't be open to you (though I could think of a couple workarounds). You might want to take a look at the search on this one, while I have done some automatd rports before, it's not something I have done recently
A god keyword search might be "run automatic" and of course the other would be "excel".

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[avivit]

Thanks,

1. I will check the execution part.

2. I still do not want to write directly to the first database, as there are more than 1 people handling it.
And that database is in great use by many users.

3. As for excel, I can visit once a month of course,
it looks the easier solution.
I guess I can ask the admin to use scheduling, if possible, as well.

Thanks very much for your great help so far,
I have learned a lot.

I am sure I will have q soon...
Don't worry...

 

[onpnt]

Great reading!!!

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811

 

[avivit]

Terrific so far.

Tarwn , Following your instructions, I used the following, in order to avoid opening a recordset to a perferct results (so far):

sql update query (lets call it "sqlUpdate1")
another sql update query (lets call it "sqlUpdate2")
insert query (lets call it "sqlInsert")
************The diagrem*************

objDB.execute sqlUpdate1,affectRec,adCmdText + adExecuteNoRecords
IF affectRec=0 THEN 'If no records were updated
objDB.execute sqlUpdate2,affectRec,adCmdText + adExecuteNoRecords
IF affectRec=0 THEN 'If no records were updated
objDB.execute sqlInsert 'Insert a new record
END IF
END IF
*****************************************

The "adExecuteNoRecords" parameter in the above execute expression is preventing from ADO to build an empty recordset, which is what is done in case the parameter is ommited.
See explanation in:

http://tutorials.findtutorials.com/read/id/148

Tarwn, Thanks very very much
You have been so helpful

I'll be (probably) back

 

[Tarwn]

Good stuff, yeah I like that adExecuteNoRecords, I can't be 100% sure but I would think that would speed things up
-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/