DIGITAL - Iconics Suite - Database functionalities

DIGITAL - Iconics Suite - Database functionalities

Summary: This article describes how to integrate ICONICS software with various relational database systems, using SQL Server as an example. It covers the basics of connecting to databases and performing SQL queries such as SELECT, INSERT, UPDATE, and DELETE. It also explains how to use stored procedures for data management and presents the possibility of using any Asset as a value from the database

Connecting to SQL Server Databases

Connectivity to databases is a standard protocol in the ICONICS solution stack, and a variety of platforms are supported. This integration is frequently used for 3rd party relational database systems integration.

In this window you can enter the name and description for the Database Connection like also configure the connection.


Select a server name (the default setting is the local server). If the server does not appear on the drop down list, you can refresh the list by clicking on the green refresh button.

Next, enter information to log on to the server and choose the databases on the server to connect to. Once the configuration is done, click OK.

You can check the connection parameters for possible error by using the Test Connection link.


Click Apply to save your changes and Close to exit from the connection properties.

SELECT statement queries

Explaining how to create a SELECT statement for your data source as well as configure a user-defined parameter.



Clicking the Configure Command button will open the Configure SELECT Command window.

 

There are three type to choose from: Custom query, Table query of a table or view or Stored Procedure listed. 

In this example will be chosen the Table query and the Customer table from the query.



The command that is being generated automatically selected all the column from the table. You can further customize this by adding a user defined parameter. In order to, add the string to the end of the command or go next. “WHERE City = @City” has been added as an example.


This new added parameter will be detected, where you could further configure the data type an length.


The Test Command button can also be used to indicate the result of select command.


After you will define a specific city, the query test result will show all the rows with “London” under the City column. 




The Data Selection Schema section will be autofilled based on the setting made in the Data Selection Command. You can also click on the “Autodetect Schema” button, which will refresh the existing found data selection. You can also configure your own schema by clicking on the “add new data” button.


The Data Refresh tab allows users to set the data refresh type or period for the configured Data Source. You can choose from No Refresh, Period or By Triggers as the type, and then further define a time period or a trigger of your wish.



Data Modification Options

Except simple SELECT statements you can alco integrate INSERT, UPDATE and DELETE queries into your application, both configuration and runtime use.

To enable this options, select designated checkbox


 




Once you have applied the changes of this options, you can verify the feature using GraphWorX. (Using GridWorX Viewer)


Configure it to show the Categories table. In runtime,  click on the table and you should be able to select the option of inserting a row.







After successfully adding these options, we will be able to add new data, delete, or edit existing ones without any problems.



Data manipulator

Data Manipulators provide powerful direct access queries that can be applied to the SQL database via runtime commanding and automated transactions.

Choose one of the commands (INSERT/UPDATE/DELETE); Apply and save the changes.


Now go into GraphWorX and create a GridWorx Viewer. Double click on the table to bring up the configure window (Proceed as before).




Create a new subscription:

(My Computer – Data Connectivity – Databases – SQL Server – Northwind – Data Sources – Customers)



Insert “Data Entry” above the Table. As DataSource set the @CustomerID parameter.





Place a normal button next to it and set the command to be Write Value. Set also the data source to be Delete Customers - @@Execute; and the ON Up Value is going to be 1.



Insert the name, press enter to accept the changes and click the button to delete on of the Customer.





Asset as a selected field from the Database

There is a possibility to create and utilize any Asset so that it is defined as a field in the database. In the discussed example, the selected Asset will return a specified value from the database.



In the "Read Expression" tab, include the code that will be responsible for the "SELECT" function, meaning it will retrieve a specific value from the database.



After accepting the settings, you can verify the functionality in GraphWorX. To do this, add a process point and assign the created Asset to it. Once the simulation is started, the assigned field from the database will be displayed on the screen.






    • Related Articles

    • Iconics Suite - GraphWorX Client on external machine [Iconics Architecture]

      This article will explain how to install GraphWorX64 Client on external PC (other than Iconics server with application) Start Iconics installation from Iconics Suite installation .iso by clicking RunMe Select ICONICS Suite Wait till all necessary ...
    • Iconics Suite - Filtering data with Database DataSource parameter

      This article shows you how to use Data Entry control and Database DataSource parameter to filter data in Table Control In attached files you can find GraphWorX64 display with sample solution. To make it work you have to use Iconics project package - ...
    • Iconics Suite - Question mark tags (Current etc.)

      This article show the list and usage of ?Current tags and rest of special syntax tags with question mark on beginning of the tag ?Current tags The Security Server exposes several points that expose information about the user, who is currently logged ...
    • Iconics Suite - Installation guide

      This article will guide you how to prepare your operating system and database to install Iconics Suite. Please note that you will have to have Windows full admin user account to install Iconics properly! To complete Iconics Suite installation without ...
    • Iconics Suite - Client type check (GraphWorX or web browser)

      This article give you possibility to recognize on which Iconics Suite client type your display is open and use this information for future action like show or hide certain elements Notice quality code 192 is good, 0 is bad Expression for label ...