1.Form in the form parameter component script
You need to add a data source or create a dataset before inserting a script. Here, we take the SQL database as an example.
1)If the table "Student" exists in the SQL database, then the user can create a SQL dataset in the create dataset module, for example, create a SQL dataset with the name of "Student table" and the save path of "Form / Student table". The data of the database table are as follows:
2)Add the form parameter component to the dashboard from the right form component, and bind the data in the dataset "Form / Student table", as follows.
3)Add the commit to the dashboard from the right form component and set the on commit script for the component script as follows.
var conn = null;
//Create a database connection through SQL query, where SQL represents the query type, "Data Form/reporting/table/student" represents the path of the query, can also create a database connection through the data source, also conn = createConnection (CONNECTION, "test /data source/SQLSERVER_ data source")
try {
conn = createConnection(CONNECTION, ”Data Form”);
// The data of the form parameter components
var fgrid = getData ("FormParameter1", DATA); / / "FormParameter1" indicates the name of the form parameter component
// Gets the changing data rows
var rowsOld = fgrid.rows(ORIGINAL); // No-changing data rows
var rowsAdded = fgrid.rows(ADDED); // Increased data lines
var rowsChanged = fgrid.rows(MODIFIED); // Change the data line
var rowsDeleted = fgrid.rows(DELETED); // Delete the data line
//Insert data into the database, where stu is the table name, name, age, gender is the columns in the table "Student", corresponding to the question mark respectively
if (rowsAdded != null) {
var pstmt = conn.prepareStatement("insert into reporting.Student(Name,Age,Gender) values( ?, ?, ?)");
for (var i = 0; i < rowsAdded.length; i++) {
pstmt.setString(1, rowsAdded.Name);
pstmt.setInt(2, rowsAdded.Age);
pstmt.setString(3, rowsAdded.Gender);
pstmt.executeUpdate();
}
}
// Delete a row, the student is the name of the table in the database, and the student ID is the main key of the table
if (rowsDeleted != null) {
var pstmt = conn.prepareStatement ("delete from reporting.Student where Student ID = ?");
for (var i = 0; i < rowsDeleted.length; i++) {
pstmt.setString(1, rowsDeleted.Student ID );
pstmt.executeUpdate();
}
}
//To update the database data, students want to update the database table name, name, age, gender is the column in the table student, the student ID is the main key of the table, corresponding to the question mark in the where condition, string represents the data type of the student ID
if (rowsChanged != null) {
var pstmt = conn.prepareStatement ("update reporting.Student set Name =?, Age =?, Gender =? where Student ID =?");
for (var i = 0; i < rowsChanged.length; i++) {
pstmt.setString(1, rowsChanged.Name);
pstmt.setInt(2, rowsChanged.Age);
pstmt.setString(3, rowsChanged.Gender);
pstmt.setString(4, rowsChanged.Student ID);
pstmt.executeUpdate();
}
}
}
//Capture exception
catch (e) {
try {
if (conn != null) {
conn.rollback();
}
} catch (e1) {
}
debug(”Update DataBase Error: ”+e);
} finally {
if (conn != null) {
try {
conn.commit();
fgrid.updateFlag();
conn.close();
} catch (e2) {
}
}
}
4)Save the script, enter the preview mode or view the dashboard to view the dashboard, add, modify or delete the data in the form parameter component, and then click the commit button to commit the data to the database.
2.Parameter component form script
For example, commit students' basic information to the database through the interface, to design the interface as shown in the figure below:
1)First, you need to add components to the dashboard:
Input box for the name: "TextParameter1".
Input box for age: "TextParameter2".
Gender selection box: "Dropdownlistparameter1", bind the existing data column "gender" or manually enter the option data in the list parameter component, and change the list parameter component setting to single selection.
Submit button: "Commit1"
2)Select the commit button "Commit1" and set the script for "on load" in the button script:
var conn = null;
try {
/ * Create a database connection through the data source, if it can be written as conn = createConnection (CONNECTION, "dataset"), where CONNECTION is the query type and dataset is the created data source, need to take a path.
You can also create data to create a database connection through a SQL query, createConnection (SQL, "test/sql database/student table"); SQL represents the query type, "test/sql database/student table" represents the table path of the query.
*/
conn = createConnection(CONNECTION, ”Data Form”);
// Data for the parameter components
var stuName = Textparameter1.value; // get the value entered in textparameter1
var stuAge = Textparamete2.value; // get the value entered in textparameter2
var stuSex = Dropdownlistparameter1.getSelectedObjects()[0]; // get the selected value in dropdownlistparameter1,
//Insert the data into the database, where reporting.Student is the name of the table to be inserted into the database. Name, Age and Gender are the columns in tables reporting.Student correspond to the question mark
var pstmt = conn.prepareStatement (" insert into reporting.Student (Name, Age, Gender) values (?,?,?)”);
// Insert the obtained stuName, stuAge, and stuSex values into the database and update the database
pstmt.setString (1,stuName);
pstmt.setInt (2, stuAge);
pstmt.setString (3, stuSex);
pstmt.executeUpdate();
conn.commit();
}
// Capture exception
catch(e){
try {
if(conn != null) {
conn.rollback();
}
}
catch(e1) {
}
debug(”Update DataBase Error: ” + e);
}
finally {
if(conn != null) {
try {
conn.close();
}
catch(e2) {
}
}
}
3)Save the script, enter the preview mode or view the dashboard to view the dashboard, enter "Sam", 20, "Men" in the interface, click the committed data database, equivalent to execute sql directly in the database: insert into reporting.Student (Name, Age, Gender) values ("Sam", 20, "Men");
3.Note
•Using the script form, the user will not prompt when the commit is successful.
•The freestyle form component is temporarily not supported by the script form.
