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 ajExecuteSqland ajExecuteSp. Currently, it supports Oracle, MySQL, and MSSQL. This recipe demonstrates the basis of using ajExecuteSql 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 toolbar and click Properties. On the popup window, move to the Data Source tab. The two configurations we need to set are Database Type and DB Connection String.

Put the database connection string of your database in the DB Connection String. For Database Type, you can either put MySQL, MSSQL or Oracle depending on the database that you are going to use.

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 logic to retrieve customer data from the table tb_customer. ajExecuteSql 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 ajExecuteSql 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 becomes an Array Formula so that the data retrieved can be placed into the selected cell range.

=IF(AlchemyJ_FunctionPoint = "Get Customer", ajExecuteSql(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 ajExecuteSql will only run when AlchemyJ_FunctionPoint = "Get Customer" which is the time when we click Run 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 ajExecuteSql.

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", ajExecuteSql(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 ajExecuteSql. In this example, you will use ajExecuteSql 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 ajExecuteSql 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 ajExecuteSql.

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", ajExecuteSql(B10), "")

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

The value returned in C12 by ajExecuteSql 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 advance usage of ajExecuteSql and ajExecuteSp.