How to get auto incremented value in text field

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

How to get auto incremented value in text field

Postby hardina09 » Tue Jun 05, 2012 4:03 pm

I am using MS Sql Server 2005 as backend. I am having a form with text fields,labels and two buttons Save and Cancel for to adding (insert in db table) new employee information. For empid I want to display next available empid in text field and rest of field should filled by the user and hits save button which adds a new record at the end in table and Cancel button to roll back the data entry.

Here's what I did, 1. At Servoy level I had configured employee table columns empid as Row identity as 'PK' and Sequence type as 'db identity' & 2. Programmatically got the max empid from the database and incremented by 1 which is the next available empid for the new record.

Initially, at DB side I had created empid as auto increment that worked to create a new empid, and for roll back the data entry for new employee, it doesn't worked as no matter we add or roll back insert/add, DB is going to auto increment value. In case of roll back I want DB to roll back auto increment empid by 1. That doesn't worked so switch to get next available empid programmatically ( if need more clarification let me know)

Can anyone help me how to display next available id in text field. Any suggestion is appreciated.
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm

Re: How to get auto incremented value in text field

Postby jdbruijn » Tue Jun 05, 2012 4:39 pm

Won't you get into trouble as soon as you have 2 users starting to add a record at the same time? Let's say you have 20 records in your table, the first user starts to add a new record so he gets the number 21. Now before he saves that new record in the database, a second user starts to add a new record. He would also get 21 as the next available number??

My suggestion would be to let the user create the new record, and when the user saves the record, set a lock on the table, use the database event onRecordInsert to get the correct data value and after saving release the lock. So the user would see the number as soon as he has saved the new record.
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 492
Joined: Sun Apr 11, 2010 6:34 pm

Re: How to get auto incremented value in text field

Postby hardina09 » Tue Jun 05, 2012 5:17 pm

Thanks for the quick response,

Thanks for the suggestion. Sorry, I forgot to mention this functionality is going to be used by only one user i.e Admin. I think I need not to worry about locking table. If no, then is there other way where in I can get next available emp id value and display in text field.
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm

Re: How to get auto incremented value in text field

Postby jdbruijn » Wed Jun 06, 2012 8:48 am

Well as long as you can be sure that only 1 user is going to be adding records in this table. I would suggest to store that value in a separete table and use that to show the correct next value. That will be faster as looking for the max value every time you need it.
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 492
Joined: Sun Apr 11, 2010 6:34 pm

Re: How to get auto incremented value in text field

Postby hardina09 » Wed Jun 06, 2012 2:20 pm

Can you please explain in detail regarding creating separate table, how it works. How to add new values to seprate table and what about existing value once admin is going hit cancel button.
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm

Re: How to get auto incremented value in text field

Postby jdbruijn » Thu Jun 07, 2012 8:53 am

If you are sure that it is only going to be used by 1 user than you could also retrieve the max value from the table when you start with a new record.
it would look something like this:
Code: Select all
  var _ds = databaseManager.getDataSetByQuery('example_data','SELECT MAX(emp_nr) FROM EMPLOYEE',null,1);
  var _maxNr = _ds[0][0];
 
  foundset.emp_nr = _maxNr +1;

This way you have full control over this value so if you dont save the record (rollback) than the value is not stored and will be used for the next new record.
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 492
Joined: Sun Apr 11, 2010 6:34 pm

Re: How to get auto incremented value in text field

Postby hardina09 » Thu Jun 07, 2012 1:48 pm

Thank you very much jdbruijn.

You explained me very well. It is working.
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm

Re: How to get auto incremented value in text field

Postby Harjo » Thu Jun 07, 2012 2:13 pm

jdbruijn wrote:If you are sure that it is only going to be used by 1 user than you could also retrieve the max value from the table when you start with a new record.
it would look something like this:
Code: Select all
  var _ds = databaseManager.getDataSetByQuery('example_data','SELECT MAX(emp_nr) FROM EMPLOYEE',null,1);
  var _maxNr = _ds[0][0];
 
  foundset.emp_nr = _maxNr +1;

This way you have full control over this value so if you dont save the record (rollback) than the value is not stored and will be used for the next new record.


Hi Jos, this is not save, if you use this with multiple users, you will end up with same id's!
You have to use some record locking to be 100% sure you won't end up with the same id's!
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: How to get auto incremented value in text field

Postby jdbruijn » Thu Jun 07, 2012 2:28 pm

Hi Harjo, I know this is not save for multiple users. I said the samething in my first post, but as said is only has to work for a single user. So in that case it will work, but like you said in a multiple user environment this will give you problems later on. I use a general settings table that has the most recent value and when I save a new record I lock my settings record. Retrieve and update the value in the settings record. And finally use that value as the ticket-id in my work table.
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 492
Joined: Sun Apr 11, 2010 6:34 pm

Re: How to get auto incremented value in text field

Postby Harjo » Thu Jun 07, 2012 2:38 pm

Indeed, you also need some looping, because if the record is locked, what do you do than?

this is the code we use:

Code: Select all
var theCounter = 1
   while (!databaseManager.acquireLock(main_to_seq_invoice, -1)) {
      application.sleep(500)
      theCounter++
      if (theCounter > 60) {
         // stop if 60 times you attempted to lock
         //showdialogcode here that says that it's not going to happen this time
         plugins.dialogs.showWarningDialog('i18n:1.error', 'i18n:1.message.failedlock', 'i18n:1.ok');
         return null
      }
   }
   databaseManager.refreshRecordFromDatabase(main_to_seq_invoice, 1)
   var invoicecode = main_to_seq_invoice.sequencenumber
   main_to_seq_invoice.sequencenumber += 1
   databaseManager.saveData()
   databaseManager.releaseAllLocks();
   return invoicecode
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: How to get auto incremented value in text field

Postby jdbruijn » Thu Jun 07, 2012 2:53 pm

Ah yes, I forgot about the loop.
I also use a try..finally with the releaselock inside the finally block to make sure that the lock gets released even when somehting goes wrong.
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 492
Joined: Sun Apr 11, 2010 6:34 pm

Re: How to get auto incremented value in text field

Postby hardina09 » Mon Jun 11, 2012 9:22 pm

Thanks both of you
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm


Return to How To

Who is online

Users browsing this forum: No registered users and 4 guests

cron