Microsoft SQL
Microsoft SQL in Illa is a database integration that allows users to connect and interact with a Microsoft SQL (MS SQL) database. MS SQL is a popular open-source relational database management system that is known for its speed, reliability, and ease of use.
With the MS SQL integration in Illa, you can query, insert, update, and delete data from a MS SQL database using SQL commands. You can also create custom SQL queries and run them within Illa. This makes it easy to build data-driven applications and dashboards that display real-time data from a MS SQL database.
Create MS SQL
There are two ways to create a resource in Illa after signing into your Illa account.
- Create in Resources
Sign into your Illa account, select **Resources**
on the top of the page, and click **Create New**
button.
Select Microsoft SQL
from the database list.
Connect to the database with the required parameters described in Connection Settings
below.
Click **Test Connection**
to see if we can successfully connect to the database. If yes, click Save Resources
, else, double check the hostname, port, username, and password is correct.
After creating a resource, the ready MS SQL will display as shown.
- Create in Builder
Sign into your Illa account, create a project in Illa Builder in the **App**
page, and navigate to the Action List
at the bottom of the page. Click **new**
, then select Microsoft SQL
from the database list. Then, connect to the database with required parameters described in Connection Settings
below.
Click **Test Connection**
to see if we can successfully connect to the database. If yes, click Save Resources
, else, double check the hostname, port, username, and password is correct.
Connection Settings
Here we need to provide information for connecting to MS SQL database.
Properties | Required | Description |
---|---|---|
Name | required | The name for resource when creating actions in the ILLA. |
Hostname | required | The URL or IP address for your database |
Port | required | The server host's port number that you should use to connect. If you don't specify a port, default port is '1433'. |
Database | required | The name of the database |
Username | required | the username you wish to use when logging in to the MS SQL server. |
Password | required | Use this password for authentication. |
Connection option | optional | key-value pair to specify the connection option |
SSL options | optional | decides how high a secure SSL TCP/IP connection is prioritized while negotiating with the server. |
Create Actions
We have created a MS SQL resource; we can add the action by selecting MS SQL from action list and choosing the Create action
button.
Now we have added the MS SQL server as an action to our building page.
Configure MS SQL
Properties | Description |
---|---|
Config Type | the type of configuration that you use to connect to your MS SQL Server database: SQL and Bulk insert. For SQL mode, you can use SQL query. For Bulk insert mode, you can insert data into a selected table with the format {{ [{x:a, y:b}, … ] }} |
SQL query | sql commands to fetch (select), insert, update, delete data from database. |
Transformer | transforming data into the style you like using JavaScript |
Example usage:
- Select
SELECT * FROM users ORDER BY lastname;
- Insert
INSERT INTO users
(firstname, lastname, email)
VALUES
(
{{ NewUser.data.firstNameInput }},
{{ NewUser.data.lastNameInput }},
{{ NewUser.data.emailInput }}
);
- Update
UPDATE users
SET firstname = '{{form1.updatedProfile.first}}'
WHERE id = {{ form1.updatedProfile.uid}};
- Delete
DELETE FROM users WHERE id = {{ form1.updatedProfile.uid }};