Self-growth ID of the database

1. Problem description

Database self-growth ID data entry means that when data entry is performed in the FineReport template, there is no need to set and bind ID data columns. When inserting data into the database, the database will automatically generate an incremental ID for each piece of data.

2. Implementation ideas

The realization of self-increasing ID mainly depends on the settings of the database, and the corresponding self-increasing ID field needs to be created in the data table first. The specific creation method may be different for each database. You can find relevant information on Googel.

This article will briefly introduce the self-increment id setting methods of several common databases.

Note: The user can also install the "Serial Number Function Plug-in" to generate the serial number. Mainly used to report the function of generating primary keys.The plug-in does not need to rely on the type of database, it is convenient for the report to switch the database, and it can be used in any database.

II. SQL Server database

In the SQL Server database, an identity is provided, and the self-growth of the SQL Server primary key can be achieved by setting the identifier, identifier seed, and identifier increment.

1. Self-increasing ID setting

1) Assuming that there is the following data table in the SQL Server database, we need to realize the self-growth of its ID column, we can set the ID in the column properties, modify the identity increment and identity seed, as shown in the following figure:

2) You can also use SQL statements to create, identity(1,1) specifies that ID starts from 1, and increases by 1.

create table KHK (
id int identity(1,1) primary key,
kh varchar(20)
)

2. Template production

1) New data connection

Create a new data connection and connect to the database. For the method of creating a new data connection, please refer to Data Connection.

2) Template settings

Create a new template and add the data set SELECT * FROM dbo.[KHK].

In the first row of the template design main page, add two text widgets and an insert row button widget, and drag the data column to the corresponding cell in the third row, as shown in the following figure:

3) Data entry attributes settings

Click Template> Data Entry Attributesto add a built-in SQL, as shown in the figure below:

Note: If you only need to add data, you do not need to bind the auto-increment id field, otherwise the submission may report an error "When IDENTITY_INSERT is set to OFF, you cannot insert an explicit value for the identity column in the table'KHK'".

3. Effect preview

Click Data Entry Preview, and fill in the data on the Web side, as shown in the figure below:

Insert 2 pieces of data in the widget under the KH title, click theSubmit button to submit the data to the database, refresh the page, you can see that there are 2 more pieces of data in the blue area below, that is, the data just filled in, and the ID column is also Carried out self-growth.

1. Self-growth ID setting

In the Oracle database, the sequence function is provided, and the self-growth of the primary key can be realized through Oracle's sequence and trigger.

1) Create sequence

CREATE SEQUENCE seq_name -- Try to use uniform prefix naming to facilitate management   
     INCREMENT BY 1   -- Self-increment step size is set to 1 here  
     START WITH 1     -- Counting starting point is set to 1 here
     NOMAXVALUE       -- Do not set the maximum value Option MAXVALUE|MINVALUE
     NOCYCLE          -- Accumulate all the time, do not loop  
     CACHE 10;   -- If you are pursuing efficiency, you can set the cache. If Oracle is down or out of power and other
                    abnormal service interruptions, it may cause the sequence to not continue. If you do not use the 
                    cache, write NOCACHE here.

2) Create a trigger

CREATE OR REPLACE TRIGGER TRG_name  --Trigger name
BEFORE INSERT ON "tbl_name" -- Triggered before inserting data into the tbl_name table
FOR EACH ROW-- Indicates that the trigger is a row-level trigger
BEGIN -- Trigger start -- The following code represents the behavior of the trigger 
  SELECT seq_name.nextval INTO :new.ID FROM dual ; -- seq_name.nextval returns the next value of the sequence, which 
                                                      is set as the new value and inserted into the primary key column 
                                                      tbl_id in the tbl_name table.  
END ; -- Trigger ends

2. Template implementation

The template implementation is consistent with the SQL Server database, so we won’t repeat it here.

1. Problem phenomenon

When using the database self-growth ID to fill in the data, the error shown in the figure below may occur: when IDENTITY_INSERT is set to OFF, you cannot insert an explicit value for the identity column in the table'KHK'.

2. Cause of the problem

The reason for this error is that the self-incrementing ID mechanism is to automatically give this data a number when inserting data, without the need to manually fill in. At this time, if the self-increment field is bound to the value in the reporting attribute, it will cause the conflicting error mentioned above. For example, as shown in the figure below, ID is an auto-increment field, and an error will occur if a value is bound to ID.

3. Solution

1) Scheme 1 

(applicable to scenarios where only new data is needed, no update and deletion requirements are required)

Remove the self-increasing id field in the Data Entry Attributes.

2) Scheme 2 

(applicable to scenarios that need to implement new, updated and deleted requirements at the same time)

Write the following content into the notepad file, and save the file as insertIgnoreColumn.properties, and put it under the %FR_HOME%\WEB-INF\resources folder. The function of this file is to ignore the auto-increment id field when adding data. The content of the file is:

KHK.ignoreColumns=id

illustrate:

KHK: The name of the data table for filling in the data, which needs to be modified according to the actual scenario

id: The column name of the self-increasing id field, which needs to be modified according to the actual scenario

Note: If there are multiple data tables that need to use self-increasing ID, then directly write in this configuration file in a new line. New or modified configuration files need to restart the web server to take effect.