In this tutorial, we will execute the SQL Statement in Excel using AlchemyJ.
Before we begin, ensure you have your database installed. For this tutorial, we will use the MySQL database.
After you've installed the database, try creating a database at the same time. I'm naming this database as 'test'.
CREATE DATABASE test;*use this script if you need help.
Once you have the database created, let's start by selecting the REST API Sample Model for today's model.
Once the new model has been selected, save this new excel file as ExecuteSQL.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 from API to the database. Go to the ##RestApi tab, line 33, and expand it. Here configure the details accordingly as below
*change the [UID] and [PWD] to your database user ID and password respectively.
Since we are in the ##RestApi tab, let's configure other details as well.
First, we will configure the Basic Settings (the first table on the page) and change the API name and Package Name.
For this API, I'll change the API name to Exe_SQL and the Package Name to exe_sql. You can name yours anything you want, but make sure there's no space in between and the Package Name can only have lower cases.
Let's enable the OpenAPI (Swagger) specification as well. Go to line 92 and expand it. Configure it as below.
*for more detail on OpenAPI (Swagger) specification, go here.
We will leave the other settings as it is, let's move to ##RestEndpointGroup tab to configure other settings.
On basic settings, I've configured mine as below.
Then move on to Function Points, here I've configured as below.
*change Function Point from Function Point 1 to Execute SQL
*change Path from /resource1 to /exeSQL
*change DB Operation Type from empty to Non-transactional
Next, we configure the Input Parameters as below
*change Function Point from Function Point 1 to Execute SQL
*change Name from input_parameter_1 to input
Now we've completed the settings, let's move to the BizLogic tab and work on the model. I've made some adjustments to the default model as below.
Next, we will type in our formula in cell B2. We are going to use the code as below
=IF(AlchemyJ_FunctionPoint="Execute SQL",ajExecuteSql(B1),"")
*The image above explained in detail on how we use the functions of AlchemyJ
Let's test our function in Excel. Type in "select version()" in cell B1, then select Run Function Point. I got my result as below as I'm running MySQL version 8.0.12.
This shows that you are connected to the database from Excel.
Let's try various MySQL commands and see if it works for you. Let me know what commands you tried in the comments below.
Now let's generate the API. Go to AlchemyJ ribbon then select the Generate API. Your API should be generated automatically within a minute. Once you see the below screen, you are ready to test the API.
Open any browser and type in the below
http://localhost:8080/openapi/index.htmlYou should land on the OpenAPI (Swagger) specification page.
Select GET and then Try it out. Type in select version () on the input field then select Execute.
Under Responses, you should have the Request URL that you can use to call this API from browsers directly.
Under the Response body, you will be able to see the result of the API call as below.
With this test, it shows that your API is connected to the database as well.
Go ahead and test your API with some other MySQL commands and share with me what's your results below.
That's all for this tutorial.