SQL Stored Procedure Question
I am creating a stored procedure and need to increment a system file field (counter) and not sure if I am doing it correctly. This is what I have within my procedure:
....
UPDATE [dbo].[Sys1] Set
@Trno2 = [TRNO2], [TRNO2] = [TRNO2] + 1;
...
I am assigning the variable @Trno2 to Sys1.Trno2 then increment that counter. I will use this variable to save to a newly create record. In a multi-user environment I just want to be sure no one else has incremented the field while this is taking place.
Re: SQL Stored Procedure Question
I'm a proponent of stored procedures but I'd think that this would be overkill and would not buy you anything. The point of using Reread in DF is to insure that you have the latest data and it's locked. So why not just do this in DF with a simple reread, move, save & unlock?
Re: SQL Stored Procedure Question
That is it way I was doing it originally, but we are updating around 16 to 20,000 records and was just trying to speed things up. So, I should probably put that part back on the DF side of things.
Re: SQL Stored Procedure Question
If you really want to speed things up. you should not use system fields to hold increment values at all. get rid of them , and use the auto-incremented columns from the dbms engine..
Re: SQL Stored Procedure Question
DDs add a lot of overhead when updating thousands of records! Anyway, that is not an option right now.
Re: SQL Stored Procedure Question
How is "Reread" and "Unlock" handled when "Updating" through an SQL statement. Does the Statement do this? Should one not do any updates through an SQL statement because of multi-user activity?
Re: SQL Stored Procedure Question
I guess I was not clear!
I was not talking about DD auto-increment.. but the database (SQL) "auto-increment" column.
Just configure your column on back-end db to provide auto-increment values.. each DBMS has an option for this... I forgot the name in MSSQL, but on DB2 its called "GENERATED AS IDENTITY" columns.
The database will handle it... and you don't need to create system tables for holding the last used value.
Edit: found some refs for MSSQL ([URL]https://www.w3schools.com/sql/sql_autoincrement.asp[/URL])
[B]Most benefit of using this[/B]: You avoid a single point of lock contention in your system/app. Specially in the scenario you are describing... several inserts ! During this job execution, your system table (one row) will be locked very often, and if you have other transactions running on from other users, trying to get new row Ids for other tables as well, you got stuck..
Regards
Re: SQL Stored Procedure Question
What Samuel is referring to is an MSSQL Identity column, also referred to as auto_increment. Assuming you're using this as the primary ID for a table, this removes the need for a second table that holds the next ID, it lets the database do the honors. A recnum column is exactly that. If you don't use recnums and have a unique ID column in a table you can set it as an identity column in SSMS or the Studio, look for DF_FIELD_AUTO_INCREMENT in the properties & set to true for that column. This means that the "next" ID is automatically added to the table at the point that the record is created. This eliminates the worry about locking and someone else grabbing that ID.
If in a stored procedure there are a couple of ways to pass that back to the DF application if you need it, the driver will have a property or some means of grabbing it, I don't remember the syntax.
Re: SQL Stored Procedure Question
Yes, I was aware of the auto_increment. However, this cannot be done right away as we are just getting ready to convert the live data to SQL (testing phase now) and there are too many components that use the current method. I was just experimenting with ways to speed up some of the batch updates.
Re: SQL Stored Procedure Question