We will work on updating and deleting records in the database today with AlchemyJ.
If you notice the pattern on both reading records and creating records, the process is almost the same. The splits into 6 steps as below
Configure the ##RestAPI
Configure ##RestEndpointGroup
Configure the BizLogic
Define the IO_Schema
Complete the #RestEndpointGroup configuration
Generate API and test
The above process is a simple way of how you can create an API using AlchemyJ. Let's proceed with the tutorial.
Updating Records in databases
So the same as the create records, we will start with the ##RestEndpointGroup.
Let's configure the Function Points the same as the screen capture below.
Function Point = Update Record
*this will be shown when we select the Run Function Point
Path = /updateRec
*the Path is the second part of the API address
Operation = PUT
*select PUT from the drop-down menu
Response Data Address =
*skip this field for now
DB Operation Type = Transactional
*select from the drop-down menu
We can leave the rest as default for now.
Let's proceed to the BizLogic tab. I've prepared another table for the update records process as below.
Now, type the formula below in cell E28 for the update records process.
=IF(AlchemyJ_FunctionPoint="Update Record", ajUpdateRec("customers_info", B27:C27, B28:C28, 0, A31),"")*B27:C27 is referring to the header of the table and B28:C28 is the input
*for more information on ajUpdateRec, refer here.
Next, we will type the formula below in cell A31 so that the output can be used by the update record process.
=A27& " = '" &A28 &"'"Once you have done with BizLogic, we will proceed to the next step to define the IO Schema. In IO_Schema, select cell A13 then go to AlchemyJ ribbon and select Insert Snippet then select the Data Relationship Schema. A table will automatically be created for you as below.
For this function, we will name the table as Output Schema Update Record.
For best practice and easy maintenance, go to the Formula ribbon and select the Name Manager. Scroll down and look for "AlchemyJ_Snippet_Data_Relationship_Schema_tbl".
Change the table name to Output_Schema_UpdateRecord.
Let's fill in the Output Schema Update Record table as below screen capture.
*cell C16 type in '=ajGetAddress(' then use your mouse to select the BizLogic tab and select the Output table for the update (from E27:E28) then press Enter.
Now we complete the IO_Schema configurations, go to the ##RestEndpointGroup change the Response Data Address cell D15 to the below.
Response Data Address = =ajGetAddress(Output_Schema_UpdateRecord[#All])
Next, go to the Input Parameters and fill in as below.
Now we are ready to test our third function, the update record, and see if it works.
Go to the BizLogic tab and fill in the table under the Update Record table. I've filled in as below.
Go to the AlchemyJ ribbon and select the Run Function Point. You should have a pop up then select Create Record.
You should be able to see under the Number of records update table is 1.
If you get the above result, then your Excel is now updating records on the database.
Now let's generate the API. Go to the AlchemyJ ribbon and select Generate API.
*if there's an error, make sure the AlchemyJ and DB_OP API (Spring Boot command prompt) is closed
Once you get the Spring Boot command prompt, your API is ready.
Type the below address to your browser.
You should land on the OpenAPI (Swagger) Specification page where you can test your API.
Try to test and see if your API can create a new record into the database. On this page, you should be able to select the Try it out as below.
Your database response body should be as below showing that 1 record is created.
Now that your API is working as well, let's move on to the last process which allows us to delete records in the database.
Deleting Records in databases
Let's start with the ##RestEndpointGroup.
Let's configure the Function Points the same as the screen capture below.
Function Point = Delete Record
*this will be shown when we select the Run Function Point
Path = /deleteRec
*the Path is the second part of the API address
Operation = DELETE
*select PUT from the drop-down menu
Response Data Address =
*skip this field for now
DB Operation Type = Transactional
*select from the drop-down menu
We can leave the rest as default for now.
Now, proceed to the BizLogic tab. I've prepared a table for the delete records process as below.
We will type the formula below in cell A43 so that the output can be used by the delete record process.
="Name = " & "'" & A40 &"'"Then type the formula below in cell E40 for the delete records process.
=IF(AlchemyJ_FunctionPoint="Delete Record", ajDeleteRec("customers_info",,,0,A43),"")*A43 is referring to the output of the Selection Criteria
*for more information on ajDeleteRec, refer here.
Once you have completed the above process, we will proceed to the next step to define the IO Schema. In IO_Schema, select cell A19and select Insert Snippet then select the Data Relationship Schema. A table will automatically be created for you as below.
For this function, we will name the table as Output Schema Delete Record.
Let's change the table name in the Name Manager. Change the table name to Output_Schema_DeleteRecord.
Let's fill in the Output Schema Update Record table as below screen capture.
*cell C22 type in '=ajGetAddress(' then use your mouse to select the BizLogic tab and select the Output table for the update (from E39:E40) then press Enter.
Now we complete the IO_Schema configurations, go to the ##RestEndpointGroup change the Response Data Address cell D16 to the below.
Response Data Address = =ajGetAddress(Output_Schema_DeleteRecord[#All])
Next, go to the Input Parameters and fill in as below.
Now we are ready to test our last function, the delete record, and see if it works.
Go to the BizLogic tab and fill in the name under the Name Input field.
Go to the AlchemyJ ribbon and select the Run Function Point. You should have a pop up then select Create Record.
You should be able to see under the Number of records deleted table is 1.
If you get the above result, then your Excel is now deleting records from the database.
Now let's generate the API. Go to the AlchemyJ ribbon and select Generate API.
*if there's an error, make sure the AlchemyJ and DB_OP API (Spring Boot command prompt) is closed
Let wait for your API to be ready then type the below address to your browser.
You should land on the OpenAPI (Swagger) Specification page where you can test your API.
Try to test and see if your API can create a new record into the database. On this page, you should be able to select the Try it out as below.
Your database response body should be as below showing that 1 record is created.
With this, we complete all our CRUD process in Excel.
With this, we complete our CRUD process in Excel.
Comments