How to use "Read from DTI row, Write to SQL"?

[Priyanka Shahi]

Any help on using the block for reading the data from data table into SQL rows would be appreciated. I could not find enough document on using this ladder element in the help section. I can read successfully execute the query and read the data from the database to the data table.

[Flex727]

Hover your mouse over the letters "A", "B", "C", & "D" for tool tips.

[NoamM]

Hi Priyanka Shahi,
Let's go over the steps to use:

                        


#1. Create a "UniLogic Query" for parameter A
When you are using  "Read from DTI row, Write to SQL" function block  the first parameter is Query that belongs to an SQL data base. for example:

In this example I've created a Query named "My Query" that belongs to data base "Database1". When I will use "SQL Query" function block with "My Query", UniStream will connect to "Database1" according to the specifics I gave it - IP = 1.2.3.4, Port = 1234, etc...
The SQL query syntax will be added in the next steps.


#2. Add a Data Table for parameter B
Let's add the next Data Table: 12 rows of the next struct {"ID" - UINT8, "Age" - UINT8}

 

#3. Add SQL syntax  our "UniLogic Query"
The SQL syntax of the "UniLogic Query" will be the code that will be preformed in the SQL Server. One can use the "Add New Query" (see image below) button, when he creates a "UniLogic Query",  in order to create in a fast & convenient way the SQL syntax for the SQL operation he wants:

For example , let's say we do not have a SQL table that feet to our table created above  in our SQL Server. If we use "Add New Query" -> "Script from Data Table as" -> "CREATE", and link the the table we created, we will get:

/*************************************************************************************
    Use this query in a 'SQL Query' Ladder Element.
    The PLC will create an SQL Table with a strcture that matches the DTI's structure.
****************************************************************************************/

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Table1]') AND type in (N'U'))
BEGIN
CREATE TABLE [Table1](
	[ID] [int] NOT NULL,
	[Age] [int] NOT NULL
)
END

This is the exact code we need to create the Data Table we created in our SQL Server.
The table already exist and you want to add the data? no problem, let's use the  "Add New Query" -> "Script from Data Table as" -> "INSERT":



UniLogic does the job for us.

#4. Add 'From Index' for parameter C and 'Number of rows to write' for parameter D
Straightforward. Just add the values that detriment from what index you start and what are the number of rows you want. If for example we use:
               
           'From Index' = 2
           'Number of rows to write' = 2

If the query syntax is the "INSERT" query before, 2 rows of the Data Table from index 2 will be added to the table in the SQL Server.
 

[Priyanka Shahi]

Thank You for the reply.