The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Concurrency issues with external application using slx provider
Posted: 11 Aug 10 1:02 PM
|
I have created a web service that inserts a record into a custom table in the SLX db using the provider. The two key columns are ref_prefix varchar(7) and seq_num varchar(3). The web service creates the ref_prefix based on the year and month (REF1008). Now for the complicated part!  The web service queries the table to get the next seq_num [Select isnull(max(seq_num),0)+1 from c_RefTable where ref_prefix = 'REF1008']. The seq_num starts at 001 and increments until the month changes which changes the ref_prefix ie. REF1009. This works great. Except that we will be calling this web service from 5 separate events across 4 applications and it is highly likely two requests could hit at the same time. I have been trying to find a way that the provider would allow to atleast reduce the concurrency issue.
Bascially I am querying the next number from a separate function returning the result to the
OleDbCommand.Parameters.Add("seq_num", OleDbType.VarChar, 3, "seq_num");
OleDbCommand.Parameters["seq_num"].Value = getNextSeqNum();
The provider is very restrictive and I have not been able to find a solution. Anyone have any suggestions?
|
|
|
| |
|
Re: Concurrency issues with external application using slx provider
Posted: 11 Aug 10 2:37 PM
|
Well the column repeats based on the month.
REF1008 001
REF1008 002
REF1009 001
REF1010 001
I could create a new column ref_id and concat the two. Right now I cancat in the code REF1008-001 for the ReferenceID but I could store that in a separte field. But then how would I make it unique? I guess I would have to delete the unique SLX ID field and use this field? |
|
|
| |
| |
| |
|
Re: Concurrency issues with external application using slx provider
Posted: 12 Aug 10 8:07 AM
|
OK, just altered the table with SSMS and created it. I will adjust my code and test today. Since the webservices works at the home office where the database resides and the remotes will be accessing the webservice I don't see the constraint not syncing as being an issue. I am syncing the table out to the remotes so they can include it on thier groups but not accepting updates back from the remotes. Maybe this will solve my issue. 
I tried the ado.net transaction approach but could not figure out a way to do all the steps inside a transaction that the provider would allow. My approach uses a query to get the next number and a query to insert the record based on the next number so I have to 'lock' the process from being able to get a next number until the previous insert completes. Tried about a half dozen combinations. I would appreciate any suggestion in this area.
Thanks, |
|
|
|
Re: Concurrency issues with external application using slx provider
Posted: 12 Aug 10 8:15 AM
|
We have the same issues.....we bypass the SLX OLE DB Provider and use a SQL Native connection....the goal is to quickly find the answer and write the record with the 'constraint' number to the database as quickly as possible....then go back and update the record(s) with the other values. If that means only storing the 3 out of 50 fields that can't be NULL on the first 'pass' there's an answer. Then go back and Update the 47 out of 50 fields on that constrained record and the 7 child records in 3 subordinate tables....
We have no remotes, no synch, and do our Select statements almost entirely with SQL Native connections (doesn't use SECCODE stuff nor Field Level Security)....Bulk writes or 'quick' writes we also use SQL Server native connection. |
|
|
|
Re: Concurrency issues with external application using slx provider
Posted: 12 Aug 10 8:26 AM
|
I have 84 remotes so most times syncing is a real issue. But in this instance the only need to the remote sync process will be the raw data to include in there groups which will go since I am inserting with the provider so I think I am safe with this solution of the constraint.
Thanks, |
|
|
|