|
<< Click to Display Table of Contents >> Form Parameter - Use script |
  
|
|
<< Click to Display Table of Contents >> Form Parameter - Use script |
|
Both the form parameter component and other parameter components can submit data to the database. This section will introduce you to the method of submitting the data in the parameter component to the database.
Currently this product supports data submission to databases: SQLSERVER, MYSQL, DB2, ORACLE, DERBY, POSTGRESQL. There are two ways to submit data, one is submitted through the interface, and the other is submitted through a script. Both of these methods require the use of a submit control. When the submit control is clicked, the data is submitted to the database.
❖Interface submission
1. Add a submit control
The components that can be used to submit include: Submit, Image, Text component. Take the submitting component as an example.
Drag the submit
to the dashboard edit area in the dashboard right panel - component.
2. Set the submission properties
The "On Change" window is present in the submitting component's script dialog. The submitted script can be edited in this window.
The submitting script of the form parameter component should be set in the folder corresponding to the form parameter component. There are 4 ways to submit a report parameter component:
Form Parameter1.insert(); can submit the inserted data column;
Form Parameter1.update (); can submit updated data column;
Form Parameter1.remove(); can submit the deleted data column;
Form Parameter1.commit(); can submit insert, update, delete data columns;
As shown in the figure below, you can submit data columns inserted, deleted, and updated in form parameter 1 after setting:
❖Script submission
You need to create a query or data source before inserting the script.
If there is a table "stu" in the SQL database, the data is as follows:
Then the user can create a data set of the SQL database in the connection data module, such as creating a SQL data set with the name: "student" and the path: "test/sql/student". Right-click text, there is a script option in the menu, the open script dialog box inserts the following filling parameter script in the running window when submitting:
var conn = null;
try {
conn = createConnection(SQL, "test/sql/student ");
/*
Create a database connection through an SQL query, where SQL represents the query type, "test/sql/student" represents the path of the query, you can also create a database connection through the data source, and also conn = createConnection(CONNECTION, "test/ Data Source/SQLSERVER_ Data Source ")
*/
var fgrid = getData(" form1"); // " form 1" Indicates the name of the form parameter component
var rowsOld = fgrid.rows(ORIGINAL); // Unchanged data rows
var rowsAdded = fgrid.rows(ADDED); // Increased data rows
var rowsChanged = fgrid.rows(MODIFIED); // Change data line
var rowsDeleted = fgrid.rows(DELETED); // Delete data line
if(rowsAdded != null){
var pstmt = conn.prepareStatement("insert into stu (stuName, stuAge, stuSex) values (?,?,?)");
/*
Insert data into the database, where stu is the name of the table to be inserted into the database, stuName, stuAge, and stuSex are the columns in the table stu, corresponding to the question marks that follow
*/
for(var i = 0; i < rowsAdded.length; i++){
pstmt.setString (1, rowsAdded[i].stuName);
pstmt.setInt (2, rowsAdded[i].stuAge);
pstmt.setString (3, rowsAdded[i].stuSex);
pstmt.executeUpdate();
}
}
/*
Traverse all rows and update the database, stuName, stuAge, and stuSex are the column names in the fill parameters and the question marks in values respectively. string and int respectively indicate the data types of the corresponding columns.
*/
if(rowsDeleted != null) {
var pstmt = conn.prepareStatement("delete from stu where stuId=?");
// Delete a row, stu is the name of the table in the database, stuId is the primary key of the table
for(var i = 0; i < rowsDeleted.length; i++){
pstmt.setInt(1, rowsDeleted[i].stuId);
pstmt.executeUpdate();
}
}
/*
Traverses all rows and updates the database, stuId is the primary key of the table stu, corresponds to the question mark in the where condition, and int indicates the data type of stuId
*/
if(rowsChanged != null){
var pstmt = conn.prepareStatement("update stu set stuName=?, stuAge=?, stuSex=? where stuId=?");
/*
Update the data in the database, where stu is the name of the table to update the database, stuName, stuAge, stuSex is the column in the table stu, stuId is the primary key of the table
*/
for(var i = 0; i < rowsChanged.length; i++) {
pstmt.setString(1, rowsChanged[i].stuName);
pstmt.setInt(2, rowsChanged[i].stuAge);
pstmt.setString(3, rowsChanged[i].stuSex);
pstmt.setInt(4, rowsChanged[i].stuId);
pstmt.executeUpdate();
}
}
}
/*
Traverse all rows and update the database, stuName, stuAge, and stuSex are column names in the form parameters that correspond to question marks in the above statement respectively. string and int respectively indicate the data type of the corresponding column.
*/
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) {
}
}
}
After editing, enter preview mode and click submit button to submit data to the database, as shown in the following figure:
