Perform CRUD Operation on Excel - Part 1 of 2

This tutorial will show you how easy it is to perform CRUD operation to the database using AlchemyJ

Our last post on executing SQL command in Excel shows how you can work with databases in Excel.

However, the most common operation that we need to perform on database operation is CRUD which also stands for create, read, update and delete. In this tutorial, we are going to perform this process using AlchemyJ.

Goals

1. Create entries directly into the database from Excel

2. Read the database from excel

3. Update database from excel

4. Delete entries in database from excel

Before we start, we will need to create a table in the database. For today's CRUD process, we will use the MySQL database that we created from our last tutorial. Let's create a table that looks like something below.

Use the below code if you have a problem creating a similar table.

CREATE TABLE IF NOT EXISTS `customers_info` (
  `Name` varchar(20) NOT NULL,
  `Age` int(3) DEFAULT NULL,
  `Gender` varchar(1) NOT NULL,
  PRIMARY KEY (`Name`)
) 

Then execute the below code to populate the table.

insert into customers_info(`Name`, `Age`, `Gender`)
values 
    ('John Doe','25','M'),
    ('Mary Jane','22','F'),
    ('Susan Tan','23','F');

Once the database has been created, launch Microsoft Excel and select the AlchemyJ tab then New Model.

For this operation, we will select REST API Sample Model to perform the CRUD operation.

Once the new model has been selected, save this new excel file as DBOperation.xlsm (ensure "Excel Macro-Enabled Worksheet" is selected).

We will need to configure the connection to the database from Excel. Go to the AlchemyJ ribbon and select the Properties, from here select the "Data Source".

Since we are using the MySQL database, we just need to change the "DB Connection String" properties.

"Driver={MySQL ODBC 8.0 Unicode Driver};Server=127.0.0.1;Port=3306;DB=test;UID=[UID];PWD=[PWD];OPTION=3;"

*change the [UID] and [PWD] to your database user ID and password respectively.

Next, we will need to configure the connection to the database for API. Go to the ##RestApi tab, line 33 and expand it. Here, configure the details accordingly as below

Since we are in the ##RestApi tab, let's configure other details as well.

We've done the same on the previous tutorial, so I'll just provide the screen capture as below.

*OpenAPI Specification settings is at line 92

Read Records from Databases

Now we will start to work on our Read Records API. First, we go to the ##RestEndpointGroup tab. Same as our previous tutorial, just configure it the same as the screen capture below.

*the Endpoint group path is the first part of the API address

Then we configure the Function Points table. Sample as below

Function Point = Read Record

*this will be shown when we select the Run Function Point

Path = /readrec

*the Path is the second part of the API address

Operation = GET

*select GET from the drop-down menu

Response Data Address =

*skip this field for now

DB Operation Type = Non-transactional

*select from the drop-down menu

Next, we delete everything on the Input Parameters table (line 27). Then go to the Related Worksheets and remove everything under the Worksheet Effective Range (line 48 and below).

We will look into those only when it's required.

We can leave the rest as default for now.

Let's move on to the BizLogic tab. Here, we will delete everything on this page.

*CTRL+A and then right-click, choose delete.

We will start with the reading of records from the database. I've prepared the table as below, feel free to design it to your liking.

After you've done with the table, select from the cell D5 to F10 then type in the formula.

The formula will be as below

=IF(AlchemyJ_FunctionPoint="Read Record", ajReadRec("customers_info",E4:G4), "")

Once you have typed the code, hold CTRL+SHIFT then press ENTER. This will make the formula becomes an Array Formula so that the data retrieved can be placed into the selected cell range.

*learn more about Array Formula here.

Once you have done the above, we will proceed to the next step to define the IO Schema. To do this, let's insert a new worksheet next to BizLogic and name it IO_Schema. In IO_Schema, select cell A1 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 Read 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_ReadRecord.

*to verify if you have the correct table, look at Refers To. It should indicate =IO_Schema!$A$3:$K$5

Let's fill in the Output Schema Read Record table as below screen capture.

*cell C4 type in '=ajGetAddress(' then use your mouse to select the BizLogic tab and select the Output table we created earlier (from E4:G10) then press Enter.

*if the cell above and below is filled automatically, delete those auto-filled fields

After we have done with IO_Schema, go to the ##RestEndpointGroup change the Response Data Address from BizLogic!A9:K10 to the below.

Response Data Address = =ajGetAddress(Output_Schema_ReadRecord[#All])

*to read more about ajGetAddress, click here.

Now we are ready to test our read record function and see if it works.

Go to the AlchemyJ ribbon and select the Run Function Point. You should have a pop up as below then select Read Record.

Now we go to the BizLogic tab and from here, you should be able to see the records from the table we created in the MySQL database as below.

If you get the above result, then your Excel is reading records from the database.

Now let's generate the API. As usual, go to the AlchemyJ ribbon and select Generate API.

Once you get the Spring Boot command prompt, your API is ready.

Type the below address to your browser.

http://localhost:8080//openapi/index.html

You should land on the OpenAPI (Swagger) Specification page where you can test your API.

Try to test and see if your API can read from the database. Your database response body should be as below.

Now that your API is working as well, let's move on to the next process which allows us to create records in the database.

Create Records on the database

Same as the read records, we will start with the ##RestEndpointGroup.

Let's configure the Function Points the same as the screen capture below.

Function Point = Create Record

*this will be shown when we select the Run Function Point

Path = /createRec

*the Path is the second part of the API address

Operation = POST

*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 move on to the BizLogic tab. I've prepared another table for the create records process as below.

Now, type the formula below in cell E19 for the create records process.

=IF(AlchemyJ_FunctionPoint="Create Record", ajCreateRec("customers_info",A18:C18,A19:C19), "")

*A18:C18 is referring to the header of the table and A19:C19 is the input

Once you have done the above, we will proceed to the next step to define the IO Schema. In IO_Schema, select cell A7 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 Create Record.

Again 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_CreateRecord.

Let's fill in the Output Schema Create Record table as below screen capture.

*cell C10 type in '=ajGetAddress(' then use your mouse to select the BizLogic tab and select the Output table for the create (from E18:E19) then press Enter.

Now we complete the IO_Schema configurations, go to the ##RestEndpointGroup change the Response Data Address cell D14 to the below.

Response Data Address = =ajGetAddress(Output_Schema_CreateRecord[#All])

Next, go to the Input Parameters and fill in as below.

Now we are ready to test our second function, the create record, and see if it works.

Go to the BizLogic tab and fill in the table under the Create 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 created table is 1.

If you get the above result, then your Excel is now creating records on the database.

Now let's generate the API. As usual, 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.

http://localhost:8080//openapi/index.html

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 and see the below.

Your database response body should be as below showing that 1 record is created.

Now that your API is complete, you can continue to test and create records.

We will continue with the remaining of the tutorial (update and delete records) on my next post.