Accessing Database

You may need database access if your API needs to persist large amounts of data or query data directly from a database. AlchemyJ lets you execute SQL statements and stored procedures using ajDBRunSQLand ajDBRunStoredProc. This recipe demonstrates the basis of using ajDBRunSQL to retrieve and insert customer data.

Setting up database access in the workbook

Before we start, let us define the database connection we will be using in this workbook. Go to the AlchemyJ ribbon and click Add Definition. On the dropdown list, select External Resources.

The ##ExternalResources worksheet will be added. Fill in the Data Source Configuration accordingly.

Case 1 - Retrieving a single record

This example uses table "tb_customer" in database "aj_sample_mysql57". The following records exist in the table.

Create an AlchemyJ workbook similar to the one below. It has a worksheet named GetCustomer and a function point called Get Customer.

With all that set up, let's add the logic to retrieve customer data from the table tb_customer. ajDBRunSQL executes a SQL statement against a database. Therefore, we should compose the following SQL statement and put it in cell B10. It retrieves customer, MERRY SO, from the table. This cell will be referenced by ajDBRunSQL in the subsequent steps.

SELECT CUSTOMER_NAME, DOB, GENDER, CUSTOMER_LVL, TELEPHONE 
FROM TB_CUSTOMER
WHERE CUSTOMER_NAME ='MERRY SO';                

We would like the retrieved records to be placed in cell B4 to F4. Select cells B4 to F4 and input the following formula (same as the one as shown in B7) and press CTRL+ SHIFT+ ENTER. This will make the formula to become an Array Formula so that the data retrieved can be placed into the selected cell range.

=IF(AlchemyJ_FunctionPoint = "Get Customer", ajDBRunSQL(B10), "")

We use the above formula to execute the SQL statement we just keyed into B10. Please note that the formula can be triggered automatically when refreshed. This is based on your formula calculation setting. This is not desirable as you might not want the SQL statement to be executed whenever you modify the formula or hit the calculate button in Excel. That is why we use an IF function to make sure ajDBRunSQL will only run when AlchemyJ_FunctionPoint = "Get Customer" which is the time when we click Preview Function Point and select the Get Customer function point.

We are now ready to test our work. Run function point Get Customer and see the result.

You should see the retrieved records in B4:F4.

Case 2 - Retrieving multiple records

In this example, we will create a function that retrieves multiple records from the same table. Start by creating a new worksheet named GetCustomers and add a new function point called Get Customers in API Class definition.

Put the following SQL statement in B15 which will be used by ajDBRunSQL.

SELECT CUSTOMER_NAME, DOB, GENDER, CUSTOMER_LVL, TELEPHONE 
FROM TB_CUSTOMER;               

As our table only has 5 records, select B4:F9 and input the following formula as shown in B12. Press CTRL+ SHIFT+ ENTER to make it become an Array Formula. If your table contains more than 5 records, make sure you select a range large enough for the returned rows.

=IF(AlchemyJ_FunctionPoint = "Get Customers", ajDBRunSQL(B15), "")

To test the function, run function point Get Customers. You should see the results in B4:F9.

Case 3 - Creating a record

Based on the earlier cases, you have seen how to retrieve data from a database using ajDBRunSQL. In this example, you will use ajDBRunSQL to insert a record into the customer table. Start by creating a new worksheet named CreateCustomer and add a new function point called Create Customer in API Class definition.

To keep this example simple, we will use a static SQL statement that contains the customer information we want to insert. In reality, the data to be inserted often comes from other cells, such as input parameters of your function. In such cases, you should use the parameter argument in ajDBRunSQL to define the data instead of using a dynamic SQL statement as this can prevent SQL Injection attacks.

To create a record with data as shown in B4:F4, key in the following SQL statement in B10 which will be used by ajDBRunSQL.

INSERT INTO TB_CUSTOMER (CUSTOMER_NAME, DOB, GENDER, CUSTOMER_LVL, TELEPHONE) 
VALUES ('MICKY MAI','1988-05-08','F','3','22206666');           

Select C12 and input the following formula just as shown in B7.

=IF(AlchemyJ_FunctionPoint = "Create Customer", ajDBRunSQL(B10), "")

Now we are all set to go. Run the function point Create Customer to see the result.

The value returned in C12 by ajDBRunSQL is the number of affected records. 1 means one record has been inserted. It indicates the operation is successful.

Finally, you can run the Get Customers function and verify that the record has been created into to database.

Check out the AlchemyJ Extended Function index if you want to learn more about the advanced usage of ajDBRunSQL and ajDBRunStoredProc.