Jump to content
Priyanka Shahi

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

Recommended Posts

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.

snip.PNG

Share this post


Link to post
Share on other sites

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

                       image.png 


#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:

Capture.PNG

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}

image.png

 

#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:

image.png

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":

image.png

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.
 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...