In this tutorial we are about to see how to access any SQL Database directly in the Studio, from calling the database to displaying it in your application.

‍

Step 1 : Create a new project

‍

The first two tutorials have already learnt you how to create a project, but this one is quite different : We will see here how to make a project that connects to a PostgreSQL database, makes a query and displays data in an application.

To do so, go to File -> New -> Project and search "SQL" in the search bar --> select SQL project, and name it "sql_database_back".

Then, you will have to fill the information to link the database you want : In this case we will take the "Public Postgres Database" from the RNAcentral website.(https://rnacentral.org/help/public-database)
‍
In the first field, select org.postgresql.Driver.
In the JDBC URL field, replace <server_name> by hh-pgsql-public.ebi.ac.uk, <port(optional)> by 5432 and <database name> by pfmegrnargs.
In the Username field, type reader and paste NWDMCE5xdipIjRrp in the password field.
You can find all the connection information for the SQL Database on the link above.

Once you filled everything, click the "Test connection" button to see if it works. You should have a "connection parameters are correct" message.

‍

‍

Note: Don't forget that if you name your project differently, you will not be able to continue properly the tutorial..

Step 2 : Create a transaction

‍

Now that your SQL connector is set up, you will have to add a transaction: Click on your connector, click on the Palette tab where you have to Drag & Drop the SQL Transaction component.
Name your transaction "get_references".
Now click on your transaction, then click on the Query property. Click the [...] button to open the SQL query editor. In this window, you will have to paste the following content : Once you will have clicked the "OK" button, it will automatically create the c8o_title" variable.
SELECT * FROM rnc_references
WHERE strpos(LOWER(title), '{c8o_title}') > 0
LIMIT 10;
Now that you integrated it, execute your transaction by right clicking your transaction and selecting "Execute". It will display the data from the database.
‍

Step 3 : Create a sequence

‍

The next step is to create your sequence, which should be easy now! Click on your project and search in the Palette for the "Generic Sequence" item. Drag & Drop it into your project and name it "get_references_by_id".
Then Drag & Drop your transaction in your sequence by holding CTRL + Drag & Drop on Windows and OPTION + Drag & Drop on Mac.
Once done, you will have to export the Call Transaction variable to the main sequence by right clicking your Call Transaction and select "Export variables to main sequence". This is important because all Call Transaction Step variables must be defined at the sequence level, the right click "export" facilitates the task.
This is not necessarly efficient here but it becomes very interesting when there are a lot of variables, it avoids retyping them manually in the sequence.
Don't forget to regularly save your project!

Step 4 : Create a sequence #2

‍

Now that you have your sequence created, you will have to add an Iterator Step.
Start by adding an Array into your Sequence (click on your sequence and look for the Array component in the palette), drag & drop it into your Sequence and then look for the Iterator in the Palette and drag & drop it into your Array.
Double click on your Call Transaction to open the Source Picker, where you have to find the "sql_output" to integrate it into the iterator but, indeed, we cannot find it..
This is because you need to update your transaction schema.
To do so, right click on your transaction named "get_references" and click the "Update schema from current connector data", then double click again on your Call Transaction : The SQL output is here !
Open this sql_output item and Drag & Drop the "row" field on your iterator.
The sequence will now loop through the Database data.

‍

Step 5 : Create a sequence #3

‍

Once the iterator has been set up, you will have to create an object where you will put fields in it to bind the specific data you want to display.
In the Palette, search for "object" component and Drag & Drop it in the Iterator. In this use case, let's put 4 fields item: One for the id, one for the authors, one for the location and one for the title.
Double click the Iterator item to display the Source Picker tab, and open the row schema and drag & drop each item you need : open the id item and drag & drop the "txt" value in your id field and choose "Value". Repeat it for the 3 others.

‍

Everything has now been implemented, it should work!
To be sure, let's test it by adding a "Test case" by right clicking on your sequence -> new -> Test case. Name it "Test_Case_phosphate", and then open the Testcase "Variables" folder.
Click the "c8o_title" variable and, in the "Properties" window, fill in the "Default value" property with the "phosphate" value.
Right click the Testcase and select "Run". It will execute the "get_references_by_id" sequence that will call the database query transaction which contains the word "phosphate" in the title.

‍

‍

The Back-end steps are now done. Let's move on the Front-end to display the data in the application.

Step 7 : Create Front-end for your application

‍

Let's see how to display this database on your application.
The first thing to do is to create another project: A Front-end project.
Click on File -> New -> Project -> Convertigo Low Code Fullstack Web/Desktop or Mobile app project.
Name this project "sql_database_front".

Now open the Visual app Viewer (the play button above the tree view), then change the header title by right clicking it directly in the app Viewer and change its value in the "Text value" field (SQL Front for example).

‍

‍

Let's dive to some more complex steps : Create your list container to display the data.

Step 8 : Create Front-end for your application #2

‍

The first component you will have to integrate in your project content is a "Form" component.
Expand Application -> NgxApp -> Pages -> Content.
Click it, then write "Form" in the palette. Drag & drop it into your Content.
Then, in this form, you can see that it exists two "FormItem" : Only one is needed here, so you can delete the second. In the "Controls" folder, disable or delete the alert -> you don't need it here.
Take your previously created sequence in your Back-end project "get_references_by_id" and drag & drop it into the "OnSubmit" field. (CTRL + drag & drop on Windows and OPTION + drag & drop on Mac).
Then expand Formitem -> TextInput. In the properties window, change Control name to "c8o_title". This will bind the input field name to the name of the sequence's variable.

‍

Step 9 : Create Front-end for your application #3

‍

Now you need to put a "ListContainer" in your Content (find it in the Palette). In this ListContainer, look for the "ForEach" : Click it, then in the properties window (bottom left), find the "Directive source", click on the little "SC" button, then on the [...] -> it will open a window. In this Window, click on the sequence, then on the "array" to link the ListContainer with the array data.
Then, in the treeview, open the ForEach -> ListItem -> Label -> item.text : Click it, and in the properties choose the Text value, click the Source "SC" and [...] again. This opens a window again, but this time you will have to click the "Show Iterators on current page Sources" top button (blue arrow) to choose the iterator. Then click the "ForEach" and select "title" on the right window.

You can now test on your own: Write "bacteria" in the searchbar and submit it : it will display each data containing the word bacteria in the title.

‍

Step 10 : Create Front-end for your application #4

‍

To improve the display of the data in your application, you will add some components. First, Drag & Drop an "Attr" component into ListContainer -> ForEach -> ListItem -> Label. Set its "Attr name" property to "class" and its "Attr value" property to "ion-text-wrap". The "title" text will now be displayed entirely without ellipsis.

Now, let's add the authors: To do so, Drag & Drop the "Paragraph" component into the "Label" one.
Click "some text" text under it and click the "Text value" property. Double-click the "SC" button, this will display the NgxComponent Source picker. Click the "Show Iterators on current page Sources" (play) top button, then select the "ForEach" entry on the left panel.
Finally, select "authors" on the right panel and click the [OK] button. You can also change the ListHeader text to display something more accurate and dynamic.

Expand ListHeader -> Label -> Heading -> some list header. Double-click the "SC" button, this will display the NgxComponent Source picker. Click the "Show Forms on current page Sources" top button, then select the "Form #formSample" entry on the left panel. On the right panel, expand controls -> ['c8o_title'] -> value, this is the dynamic text when you type your search in the input field. You can add static Javascript text before and after the dynamic value. Copy "'Search for title that contains: ' + (" in the Prefix field and " || '')" in the Suffix field (this will prevent to display 'null' when the field is empty). Click the [OK] button to validate.
Type some text in the input field and the ListHeader text will change accordingly. Click the [SUBMIT] button to display the new data layout.

And this is it ! You know how to integrate SQL data in your application(s).

‍