Studio enables you to use the database connectors to connect to your relational databases like Oracle, DynamoDB, MySQL, PostgreSQL and Microsoft SQL, MongoDB, and so on.
Let us now understand how to use the Oracle connector. Oracle is an open-source object-relational database system that allows you to safely store and scale the different data workloads.
Configuring an Oracle Connector
To add a third party DB connectors, under Studio > Connectors, click (+) Connector.
You first need to provide the details of your Connector, thus enter a Category name and Category description. Add an appropriate icon and click Continue.
The most important part of the process is to provide the connection string. It will be dependent upon the different relational databases that you might want to use. 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.
Now let us configure our Oracle database connector. Enter the category name and description and update the icon using the Choose Icon option. Click Continue. You would now need to add the Connection string fields. Depending upon the relational database that you have chosen, you would find the fields required for establishing a database connection. These are non-editable fields, you cannot add or remove these fields.
In this case, note that to connect to the Oracle database you would need a few parameters or fields. The connection parameters that are required are Host, username, password, port, and database name. Add the configuration details that are applicable to your relational database and click Submit.
Once these configurations are done, you need to Test request and connection. If the authentication is successful you would get the response accordingly. You can now click Continue to Finish and Save your configuration.
Adding queries to the Database 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.
You can provide a name to the query using Enter a Service name. Add your query and click Run or 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. You can add the variable using Variables > +Add.
Add the FieldName and select the Field type. Ensure that you select the field type that matches the column type of the table so that the query works correctly. Add a Test Value to check the results before saving. You can also add a Default value or make it Mandatory. Add the Help text that would be shown with the field. You can also add any preset formula. Once you have entered the details, click Add Variable. The variable would be listed under Variables. You can make use of the variable in the query where you created the variable.
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. When using the connector queries the dynamic variables would have to be linked to the respective control for further run-time functioning.
You can now view the queries that you saved for using in your apps later under your specific connector under Custom Database connectors.
Using Oracle connector
Fetch all rows
Now let us take a simple example to fetch all rows from the Sales table and display them in a tablegrid control. In this case, you simply need to add the SQL query as per the Oracle syntax.
Add the query as seen above and run to verify the output. Once done Save the query.
Now, whenever you need to get data from Oracle you can use the connector query anytime within your apps. Now if you want to display the author permissions in a tablegrid control you simply need to add the connector to the app and then bind the connector in the respective control’s properties (tablegrid in this case).
You can also view the SQL query being used using Show query. If you want to hide the query for some reason, click Hide Query. Beyond the configured queries you feel the need a few more queries you can use the Add Query option.
Provide the input values if any and click Finish. You can find the query listed out under the Studio Console > Connectors tab. Whenever you want to use this data you can simply bind the connector to the controls, select the columns, and you are done. You can transform the connector response or apply conditional formatting as required.
Display 10 records
Now here consider an example where we want to find the author permission from the table but want to display only the first 10 records. So we would now need to add pagination by making use of the LIMIT clause to limit the number of rows returned. Now in another scenario, if you want to provide a dynamic input as to the number of rows you can assign the value accepted from the form and send it as a variable to the query.
For example, on the form, you can add a dropdown control which gives the user the option to select the number of records to display. Pass this value to the connector at runtime. In the example below the limitrows variable is a dynamic variable and we are passing the value using the Keywords. Here we have specified numberofrecordstodisplay which is the dropdown control. So the value selected would be set for the Limit clause of the Query.
Now that you want to make use of these Database connectors in your Apps, Under your app, click Connectors > + Add. From the list of connectors select Custom Database Connectors > Your connector, and click Continue.
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, select the Connector from the list of connectors available. You can see in the Custom Formula the BINDAPI formula which you can customize further if you want to format how your data would be displayed in the TableGrid.
Using the DB Connector for Oracle is similar to using it with other databases like MS SQL, MS SQL, 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.