|
We will build an update page using Process Form Elements. Our objective is to list the products existing in a relational database called productdb on the page.
The provided functionalities will be:
Users may update the records and click on "Update" button. Afterwards input fields will be verified and the updated records will be stored in the database. Additionally the system administrator will be informed about the changes with an email.
Users also may select records with a checkbox and use delete command to delete them.
To implement the first functionality we will use two processes (RelDBUpdateProcess and SendTextMailProcess) which will be bind to ExecuteCommand "Update".
And to delete the selected records we will use only RelDBUpdateProcess which is connected to ExecuteCommand "Delete".
| • | MS SQL database productdb is available and serving from url |
jdbc:jtdc:sqlserver://localhost:1433/productdb
| • | productdb database has a table called products. |
| • | products table has fields ID, Name, Price, Stock, Discounted |
The Steps
| • | Open the Common Definitions page by pressing "CTRL + F7" |
| • | Place a "mor > ReDB > RelDBConnection" element into the page and define its properties as follows: |
Name
|
PRODUCT_DB
|
JDBC Driver
|
net.sourceforge.jtds.jdbc.Driver
|
Database URL
|
jdbc:jtdc:sqlserver://localhost:1433/productdb
|
User Name
|
??
|
Password
|
??
|
| • | Enter User Name and Password as required |
| • | Place a "mor > ReDB > RelDBQueryDataSource" element into the page and define its properties as follows: |
Name
|
allproducts
|
Connection Name
|
PRODUCT_DB
|
Query (SQL)
|
SELECT * FROM products
|
When the steps to prepare the "Update" page are complete the result should look like the following:
Page Design Area View:
Project Explorer View:
| • | Create a new page and name it "Update" |
| • | Place a "zpro > ProcessForm" Element into the Page and name it as "UpdateForm" |
| • | Place a "zpro > ProcessForm > ProcessBlock" into "UpdateForm" and name it as "UpdateBlock" |
| • | Enter "/allproducts/item" into "Repeat | For Each" Property of "UpdateBlock" |
| • | Place a "zpro > ProcessForm > ProcessBlock > SelectionBox" element into "UpdateBlock" |
| • | Place three "zpro > ProcessForm > ProcessBlock > EditBox" elements into "UpdateBlock" and name them as "Name", "Price" and "Stock" |
| • | Place a "zpro > ProcessForm > ProcessBlock > CheckBox" element into "UpdateBlock" and name it as "Discounted" |
| • | Place a "zpro > ProcessForm > ProcessBlock > HiddenBox" element into "UpdateBlock" and name it as "ID" |
| • | Place a "mor>RelDB>RelDBUpdateProcess" into "UpdateBlock" and name it as "ProcessUpdateRecord" |
| • | Define the Custom properties of "ProcessUpdateRecord" as follows: |
Connection Name
|
allproducts
|
Update Query (SQL)
|
UPDATE products SET
Name = '@vof(Name)',
Price = @vof(Price),
Stock = @vof(Stock),
Discounted = @vof(Discounted)
WHERE ID = @vof(ID)
|
| • | Place a "mor > Mailer > SMTPAccount" element into the Common Definitions page |
| • | Fill in "SMTP Account" properties of the element "SMTPAccount". |
| • | Place a "mor > Mailer > SendTextMailProcess" element into "UpdateBlock" and name it as "ProcessSendMail" |
| • | Define the Custom Properties of "ProcessSendMail" as follows: |
Mail | To
|
admin@mydomain.com
|
Mail | Subject
|
Record @vof(ID) is updated
|
Mail | Message
|
New values of the record are:
Name : @vof(Name)
Price : @vof(Price)
Stock : @vof(Stock)
Discounted : @vof(Discounted)
|
| • | Place a "zpro > ProcessForm > ExecuteCommand" into "UpdateForm" and name it as "CommandUpdateRecord" |
| • | Enter "Update" to its "Value" property |
| • | Locate its "Process List" property, open Process editor and add processes "ProcessUpdateRecord" and "ProcessSendMail" as follows.
|
| • | Define "Next Page" property as "\index" |
| • | Define "Next Page after Error Page" property as "\Update" |
| • | Place a "mor > RelDB > RelDBUpdateProcess" into "UpdateBlock" and name it as "ProcessDeleteRecord" |
| • | Define the Custom properties of "ProcessDeleteRecord" as follows: |
Connection Name
|
allproducts
|
Update Query (SQL)
|
DELETE FROM products WHERE ID = @vof(ID)
|
| • | Place a "zpro > ProcessForm > ExecuteCommand" into "UpdateForm" and name it as "CommandDeleteRecord" |
| • | Enter "Delete" to its "Value" property |
| • | Locate its Process List property and select "ProcessDeleteRecord" Process in the Process editor |
| • | Define "Next Page" property as "\index" |
| • | Define "Next Page after Error Page" property as "\Update" |
We have completed all the steps. If your database environment (MS SQL) is also ready you can build the project and test it.
When "Update" page is displayed it should be look like this:
| 
