Studio enables you to use the database connectors to connect to your relational databases. Like MySQL, PostgreSQL and Microsoft SQL, MongoDB, and so on.
Let us now understand how to use the Microsoft SQL connector. MS SQL Server is a relational database management system (RDBMS) developed by Microsoft that allows you to perform different actions like storing and retrieving data to and from the databases.
Configuring Microsoft SQL Connector
To add third party DB connectors, under Studio > Connectors, click (+) Connector.
You need to first configure the MS SQL Connector with the necessary domain details and the database name. To configure the connector, enter a Category name and Category description. Add an appropriate icon and click Continue.
You can specify the environment to be used for your SQL operations. If you simply want to Test your app before making it ready for production you can set the environment to Dev or Beta depending upon your test scenario and connect your Dev database. Whenever your app is ready for production, and you want to publish and distribute the package, you can connect your Prod database and simply update the environment to Prod for the connectors added in your App. Here, the basic assumption is your Dev, Beta, and Prod environment databases have the same schema, only the data is different so it will work seamlessly.
The most important part of the process is to provide the Connection string. You would now need to add the Connection string fields required for establishing a database connection. These are non-editable fields, you cannot add or remove these fields.
You can add them as a Connection String or may provide the respective connection parameters required, namely Host, username, password, port, and database name. You can also enable further action to Connect using SSL, Enable SSH Tunneling, and Whitelisting IP.
Once these configurations are done, you need to Test the connection. If the authentication is successful you would get the response accordingly. You can now click Save to Finish
Microsoft SQL – TDS Version
On DronaHQ Studio while configuring the Microsoft SQL connector, we also get the option to set the TDS version for it. The Tabular Data Stream (TDS) Protocol is an application-level protocol used for the transfer of requests and responses between clients and database server systems. The versions of TDS are used to make the server compatible with the project.
The studio provides various versions of TDS which users can select from the dropdown according to their use.
It is usually the best practice to use the highest protocol version of TDS supported by the server, in order to have no compatibility issues, but some organization works on an older version of TDS because upgrading requires lots of adjustments which can adversely affect the security and data.
That is why DronaHQ Studio provides you with older versions of TDS too, to select from.
SQL Server proxy host
On DronaHQ Studio while configuring the Microsoft SQL connector, we can set the SQL server proxy host also. A SQL proxy server host provides a secure gateway between the client and the target SQLserver.
This is basically accessing external resources of SQL by using different credentials which would be of the proxy server.
This is useful because Proxy accounts in SQL Server provide a work-around for logins in SQL Server to execute Windows shell commands and SQL Server Agent jobs without giving excessive permissions.
Adding queries to the connector
Now that you have configured the connector you will find it under the Custom Database connectors list. Now to fetch data or undertake any other action, click Add query option available once your connection is ready.
Here you can add your queries - simple as well as complex ones. To the right-hand side of the screen, you can see the list of tables from the database. You can expand the tables to view the fields from the table.
Enter a Query name, add your query, and press Ctrl + Enter** to run the query. The rows returned from the table are shown in the Response section.
You can also add dynamic values using the Variables. To use a variable inside a query, you simply need to put it into double Curly brackets. The Test value that you entered would be considered for fetching data.
You can now view the queries that you saved for use in your apps later under your specific connector under Custom Database connectors.
Using Microsoft SQL connector
Fetch all rows and apply pagination
Now here consider an example where we want to find the author names from the table. Let us first fetch data and apply pagination using the MS SQL Connector. So click Add Query to go to the Query Editor. Here you would add the query with the OFFSET clause. In MS SQL Server it is important to have an order by to enable pagination solution.
Run the query to view the results. You can add a dynamic variable if you want to define the OFFSET clause value to specify the number of rows for pagination. Save the query. Your connector is now configured.
Now whenever you want to make use of these Database connectors in your Apps, Under Studio Console, click Connectors > + Add. Select your ready connector and click Continue. From the connector select the query for pagination and click Continue. You can add a query using Add Query in case you need to build another query for a different scenario. Here you can see that every Query that you have configured can be viewed using Show Query and hidden using Hide Query.
Connect your authenticated account. Based on the query selected, you would have the connector fields that would be used in the BINDAPI formula. Add the connector name and other details and click Finish.
In this example form, to bind the fetched data to the Tablegrid control need to use BINDAPI formula either by adding it to the Custom Formula or through Data > Connectors > Dropdown selection (API name and keys) as shown below.
Using the DB Connector for MS SQL is similar to any other SQLs like PostgreSQL, MySQL, and so on. You can make use of the Queries to fetch data as well bind data such that you can undertake the insert, update, delete actions as well. You need to ensure the syntax which would vary a bit for different SQL systems.