5.7 Committing User-Modified Data to Database (Database Write-Back)

The previous section, Accessing User-Modified Data in a Table, described how you can acquire the records in a table which have been modified (inserted, deleted, or changed) by the user. If you want the user's changes to persist across sessions, you must commit these changes to the database. This is known as database write-back.

In most cases, you should commit records to the database by placing a script within the onClick handler of a Submit Button. The script should perform the following operations:

1. Create a connection to the database:

var conn = createConnection('datasource','username','password');

If the specified data source does not require credentials, replace the username and password with empty strings ('').

2. Prepare the desired SQL statement, using placeholders for values to be inserted.

var pstmt = conn.prepareStatement("insert into table1 values (?,?,?)");

The quoted string should be a valid SQL statement which will per­form the desired database operation when values are inserted each placeholder (“?”) in the next step.

See Accessing User-Modified Data in a Table for more infor­mation about the get­FormRows() function.

3. Insert values for SQL placeholders. In most cases, these values will be taken from the arrays returned by the table functions getFormRows(ADDED), getFormRows(CHANGED), and getFormRows(DELETED). For example:

pstmt.setInt(1,rowsAdded[i][0]);     // first substitution

pstmt.setString(2,rowsAdded[i][1]);  // second substitution

pstmt.setString(3,rowsAdded[i][2]);  // third substitution

The integer parameter in each of the above functions is a reference to the corresponding placeholder (“?”) in the SQL statement; for example, the parameter value “1” indicates a substitution for the first “?”, “2” indicates a substitution for the second “?”, and “3” indicates a substitution for the third “?”. The row of the array is indexed by variable “i” above because the array is generally accessed iteratively within a loop structure.

4. Update the database tables by executing the SQL statement:

pstmt.executeUpdate();

Note that this does not yet commit any changes to the database. Changes are not committed until the 'commit' function is called (next step).

5. Commit the changes:

conn.commit();

6. If necessary (in case of an error), roll back the changes:

conn.rollback();

7. Close the connection to the data source:

conn.close();

The Submit Button onClick script below provides a complete example, which updates a database table with rows which have been added, deleted, and changed.

Example: Committing User Edits to Database  >>

The following sample Viewsheet, based on the sales_employees table in the 'Orders' database, contains an editable table with five columns. The Viewsheet table, also named sales_employees, contains all of the columns from sales_employees table in the Orders data source.

User editing is enabled only for the first_name, last_name, and quota columns. (See Table Properties: Advanced Tab and Validating User Input in Dashboard Design for details on configuring table and column editing features.)

 

Note: The 'Orders' data source used in this example is a Microsoft Access database, different than the 'Orders' data source installed by default. The default Derby database is not writable, so it cannot be used for this example.

The following script acquires the rows that were added, deleted, or changed, and then commits the modifications back to the database. The script is placed in the onClick handler of the Submit button.

try{

  // Create the connection to data source (no credentials)

  var conn=createConnection('Orders','','');

  // Find new rows added by user

  var rowsAdded = sales_employees.getFormRows(ADDED);

 

  // Find existing rows changed by user

  var rowsChanged = sales_employees.getFormRows(CHANGED);

 

  // Find existing rows deleted by user

  var rowsDeleted = sales_employees.getFormRows(DELETED);

 

  //---------- ADDING ROWS ----------//

  if(rowsAdded!=null){

    // Iterate through array of added rows

    for(var i = 0; i<rowsAdded.length; i++){

 

      // Prepare SQL statement with placeholders

      var pstmt=conn.prepareStatement("insert into sales_employees values (?,?,?,?,?)");

 

      // Insert placeholder values from ith row of array

      pstmt.setInt(1,rowsAdded[i][0]); // row i, column 1

      pstmt.setString(2,rowsAdded[i][1]); // row i, column 2

      pstmt.setString(3,rowsAdded[i][2]); // row i, column 3

      pstmt.setInt(4,rowsAdded[i][3]); // row i, column 4

      pstmt.setInt(5,rowsAdded[i][4]); // row i, column 5

 

      // Execute the SQL statement

      pstmt.executeUpdate();

 

      // Commit the changes

      conn.commit();

    }

  }

 

  //---------- DELETING ROWS ----------//

  if(rowsDeleted!=null){

    // Iterate through array of deleted rows

    for(var i = 0; i<rowsDeleted.length; i++){

 

      // Prepare SQL statement with placeholders

      var pstmt = conn.prepareStatement("delete from sales_employees where employee_id=?");

 

      // Insert placeholder value from ith row of array

      pstmt.setInt(1,rowsDeleted[i][0]); // row i, column 1

 

      // Execute the SQL statement

      pstmt.executeUpdate();

 

      // Commit the changes

      conn.commit();

    }

  }

 

  //---------- CHANGING ROWS ----------//

  if(rowsChanged!=null){

    // Iterate through array of changed rows

    for(var i = 0; i<rowsChanged.length; i++){

 

      // Prepare SQL statement with placeholders

      var pstmt = conn.prepareStatement("update sales_employees set first_name=?, last_name=?, quota=? where employee_id=?");

 

      // Insert placeholder values from ith row of array

      pstmt.setString(1,rowsChanged[i][1]); // row i, column 2

      pstmt.setString(2,rowsChanged[i][2]); // row i, column 3

      pstmt.setInt(3,rowsChanged[i][3]); // row i, column 4

      pstmt.setInt(4,rowsChanged[i][0]); // row i, column 1

 

      // Execute the SQL statement

      pstmt.executeUpdate();

 

      // Commit the changes

      conn.commit();

    }

  }

}

 

// In case of error, roll-back changes.

catch(e) {

  conn.rollback();

}

 

// Close the data source connection

finally{

  conn.close();

}

 

<< 5.6 Accessing the Most Recent User Modification © 1996-2013 InetSoft Technology Corporation (v11.5) 6 Accessing Worksheet Data >>