[Happy New Year!]

Happy New Year 2015..

[How To Lock Execproc and Different Data Records for Each Users]

 

SET XACT_ABORT ON

 

at the start of every stored proc if it uses transactions.

zilav, thank you very much. This command is very useful and important.  

 

 

I can't say that I understand everything you put there but the general approach is :

 

1. Create a separat table only with countors ( key, id ). Dont "select max(....) from ..." because it's a tricky business in multi user environments.

2. Before insert a new order , select one record from that table based on the "ORD' key for example ,  incremet the ID , Post it and COMMIT !.

3. Save the new number somwhere in the Datamodule or UniMainModule.

4. Generate your key acc. to your formula using that number.

5. Insert a new record in the orders table with the newly generated key.

 

I hope I was clear enough.

 

Salve

Adragan, thank you for your advice.   

 

Your advise is very helpful when the order number do not have a format of years and months which is must back to number "1" every beginning of the new months and years. If I'm using this approach, then in the every beginning of new months and new years i have to reset back that number to "0". I'm very grateful for your assistance on this matter.

 

The "Key Violation" error doesn't appear anymore after I added "SET XACT_ABORT ON" in stored procedure, and a little addition of code on event on error. And then, the code looks like this below:

 

procedure TDataUmum.spEditHOrderError(ASender: TObject;

  const AInitiator: IFDStanObject; var AException: Exception);

begin

if EFDDBEngineException(AException).Errors[0].Kind = ekUKViolated then

  begin

  AException.Free;

  AException := Exception.Create('Mohon Coba Proses Penyimpanannya Sekali Lagi');

  end;

if EFDDBEngineException(AException).Errors[0].Kind = ekRecordLocked then

  begin

  AException.Free;

  AException := Exception.Create('Mohon Coba Beberapa Saat Lagi. Saat Ini Proses Sedang Padat ');

  end;

end;

 

I've tested that code with several simultaneous users trying to accessed the program together, and the "Key Violation" error not appear. Very Thanks!

 

-Jemmy-

[How To Lock Execproc and Different Data Records for Each Users]

"Key violation " si a database message. It has nothing to do with UniGui.

Your tables have primary keys and you are not generating new values for

each new record. If you are using Firebird you need "generators" and a

trigger to generate the new ID for the new record.

If you are in PostgresSQL or MsSQL the same thing is called "sequence"

and you need to define the tables with serial / autoincrement fields ( like ID-s )

Depending on your data access layer you also have to define when you generate the new ID-s.

 

I am using MSSQL Server 2012 and use stored procedure to help me check the last number where I call from my project.

 

The primary key is "nobukti" and type of "nobukti" is varchar.  I am generate new value  from event OnClick because the value of "nobukti" have format.   The format of "nobukti" is   S001-YYYYMMXXX         -->  YYYY= Year,  MM=Month,  XXX=Order Number

 

The codes is :

 

procedure TFTOrder.BSubmitClick(Sender: TObject);

var tamp, info,  :string;

begin

 

  {Check last number}

  DataModule.spLastNobukti.Active:=False;

  DataModule.spLastNobukti.ParamByName('@NOBUKTI').Value:=cust+'-'+FormatDateTime('YYYYMM',Date);

  DataModule.spLastNobukti.Prepared;

  DataModule.spLastNobukti.Active:=True;

  DataModule.spLastNobukti.Last;

 

  if DataModule.spLastNobukti.RecordCount=0 then tamp:=cust+'-'+FormatDateTime('YYYYMM',Date)+'001'

    else tamp:=Cust+'-'+FormatDateTime('YYYYMM',Date)+FormatFloat('000',StrToFloat(Copy(DataModule.spLastNobuktiNOBUKTI.AsString,12,3))+1);

 

  {save data}

  DataModule.spEditHOrder.ParamByName('@ASAL').Value:='SUBMIT';

  DataModule.spEditHOrder.ParamByName('@TRANS').Value:='CUST';

  DataModule.spEditHOrder.ParamByName('@KDCUST').Value:=Cust;

  DataModule.spEditHOrder.ParamByName('@SESSIONID').Value:=UniLogin.SessionID;

  DataModule.spEditHOrder.ParamByName('@USERID').Value:=UniLogin.UserID;

  DataModule.spEditHOrder.ParamByName('@NOBUKTI').Value:=tamp;

  DataModule.spEditHOrder.ParamByName('@TGLBUKTI').Value:=Date;

  DataModule.spEditHOrder.ParamByName('@NOSERI').Value:=trim(ENoSeri.Text);

  DataModule.spEditHOrder.ParamByName('@NOREG').Value:=trim(ENoAsset.Text);

  DataModule.spEditHOrder.ParamByName('@NOBARCODE').Value:=trim(ENoBarcode.Text);

  DataModule.spEditHOrder.ParamByName('@LOKASI').Value:=ELokasi.Text;

  DataModule.spEditHOrder.ParamByName('@UP').Value:=EPerson.Text;

  DataModule.spEditHOrder.ParamByName('@TELP').Value:=ETelp.Text;

  DataModule.spEditHOrder.ParamByName('@MEDIAINFO').Value:=EMedia.Text;

  DataModule.spEditHOrder.ParamByName('@PRODUK').Value:=EJenis.Text;

  DataModule.spEditHOrder.ParamByName('@MERK').Value:=EMerk.Text;

  DataModule.spEditHOrder.ParamByName('@TIPE').Value:=ETipe.Text;

  DataModule.spEditHOrder.ParamByName('@SIZE').Value:=ESize.Text;

  DataModule.spEditHOrder.ParamByName('@KELUHAN').Value:=EKeluhan.Text;

  DataModule.spEditHOrder.Prepare;

  DataModule.spEditHOrder.ExecProc;

  DataModule.spQueryHOrderBlmProses.Active:=False;

  DataModule.spQueryHOrderBlmProses.Active:=True;

 

  ShowMessage('Data Order Sudah Di Submit Dengan Nomor Order '+tamp);

end;

 

 

I have been added exeption in event onError  in FDConnection like this :

 

procedure TDataModule.FDConnectionTransaksiError(ASender: TObject;

  const AInitiator: IFDStanObject; var AException: Exception);

var

  oExc: EFDDBEngineException;

begin

  if AException is EFDDBEngineException then begin

    oExc := EFDDBEngineException(AException);

    if oExc.Kind = ekRecordLocked then

      oExc.Message := 'Mohon Coba Beberapa Saat Lagi. Saat ini Proses Sedang Padat.'

    else if (oExc.Kind = ekUKViolated) and SameText(oExc[0].ObjName, 'UniqueKey_Orders') then

      oExc.Message := 'Mohon Coba Proses Penyimpanannya Sekali Lagi';

  end;

end;

 

 

 

 

After you see the code, you can give me some advice for this codes or in the properties of table in Datamodule?

[How To Lock Execproc and Different Data Records for Each Users]

Hello,

First of all, please pardon my English, I'm Indonesian.

 

I have e-commerce project for request maintenance's order. 

I'm using components stored procedure FireDAC in unidatamodule, unidbgrid, etc.

 

Now I'm in the testing stage to try whether this project is ready to deploy or not, and running some tests on it.

 

The first test is to do some "concurrent data submit".

In same time, multiple testers are inputting and trying to submit the datas. Then, I've got the "Key Violation" error message. Is there any way to lock the session or for buying me some time when I run the 'execproc' to avoid that error? Or, any other way to solve that? Thanks.

 

The second test is to input as many datas as possible in slightly different submit time (not exactly the same, maybe just differ for several seconds). When the datas are successfully saved, each user see differences in unidbgrid. There're different data records for each of them, even when they already run the "Refresh" button (which is already there in unidbgrid); even when I already close and reopen the execproc table.

 

  DataModule.spEditHOrder.Prepare;
  DataModule.spEditHOrder.ExecProc;
  DataModule.spQueryHOrderBlmProses.Active:=False;
  DataModule.spQueryHOrderBlmProses.Active:=True;

 

Example:

Before testing begins, there're 10 records of datas. User1, User2, and User3 are trying to add 3 datas for each of them. The result is that User1 got to see 13 datas, User2 sees 16 datas, and User3 15 datas. In database, the total summary is 19 datas (correct, 10 + 3*3). But, is it normal? For each user just seeing differences in total data records appeareances?

 

After executing the line "DataModule.spEditHOrder.ExecProc;"

Then, what to do next, "DataModule.spEditHOrder.Release;" or "DataModule.spEditHOrder.Refresh;" ?

 

Thank you in advance.

 

 

 

 

 

[Fastreport Demo]

oke Thank

[Fastreport Demo]

Important

 

I've just noticed that there is a big design flaw in this demo. A standard TDataModule and a global reference is used for reporting components. This method is not thread safe and will crash easily when a few users access simultaneously.

 

You must use a uniGUI DataModule which is created separately for each session.

 

You should never use global variables:

 

var

  FrTools: TDataFrTools;

 

Halo Farshad,

 

Do you mean "uniGUI DataModule" is "Form MainModule" in uniGUI?