Array in specific columns
-
Hi there,
I 'm looking for a way to save an array in to a table. Whereby the members of the array should be written in to seperat columns of the table.
My goal is the logging of values from a PLC in to an SQL server. Problem is that the array can contain up to 250 variables (REAL).
The PLC side is clear to me, but the SQL side is a different beast.
If you have an simple idea to solave my problem, please let me know.Pete
-
Can you provide an example of what the array looks like and what type of array it is?
Problem is that the array can contain up to 250 variables
Does this mean that you will need up to 250 columns to store the values in the array?
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
- dallmann - Wednesday, May 23, 2018 11:21 AM
In addition to Alan's comments, PLCs typically associate at least a time value, if not a date/time value, with each event they deal with, so the question is whether you've got any date or time elements that exist and perhaps need to be preserved, because that might make it a LOT easier to create the table. In that specific scenario, a table with just two columns might be sufficient. One would be a datetime value and the other the specific event detail. Depending on the nature of the data, you might have additional columns that may only have values with specific events, but would otherwise be NULL. Dealing with this kind of setup would be a great deal easier for pretty much anything that needs doing.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
The array in question comes from a power monitor. I have added a screen shot for the values from the power meter.
I send also a time stamp with the array every time I sent the data to the DB.
Before I have done the logs in to CSV files but for this application, that would be not the way to go. I have 81 power meters that will have to log every 60 seconds. That would kill my memory card in the PLC very fast.
The array would be like this: Array[timestamp; Va-n; Vb-n; Vc-n; Va-b; Vb-c; Va-c; ....]
All values are floting point numbers with 2 decimal places.
-
From your picture, I can count 42 potential values as opposed to 250, so what might I be missing? Your post indicates you get a timestamp plus the values, with semi-colons as delimiters. If there is also a space after the semi-colon, that's important to know. The question is, what, exactly, will SQL Server have available to it to consume? Are you planning to run an application that will receive the PLC data and then INSERT it into a table in SQL Server? Not sure what the problem is in terms of using CSV files, but I'm not familiar with your process for data capture, or what the limitations of the PLC devices are. In order to help much, I need a better understanding of the intended process, from start to finish. If you are getting exactly one row every 60 seconds, then that is one heck of a lot of data over time. One minute intervals means 1,440 times per day. In 2 years you;ll have more than a million rows. The question is what the utility of all that data will be. That said, what data to keep is your decision. I would probably want to have a companion table that identifies the types of measurements, and that table would have all the measurement types you intend to keep. There would be one column for an ID value (integer, identity would work well), and the other as a description of that specific measurement. Then you would link each piece of your measurements to its appropriate description. You;ll want the order of the ID values to be the exact order in which the measurements appear in your incoming data.
Quick review:
1.) What will SQL Server have available to consume? A delimited string? Jeff Moden's has a string splitter function that's ideal for this.
2.) Please identify the HOW you plan to get the data into SQL Server. An application receiving the data from the PLCs ? CSV files?
3.) Please review the frequency. I have to question the need for 1 minute intervals. 30 days down the road from now, will the observations for tomorrow at 1:23 am really be all that important?Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
The power monitor is only one part. we have also flow meters; pressure transmitters and temperature transmitters.
Interval is the longest possible. The goal is to monitor a production unit. The logs are per production cycle, day and week.
The limitation on the PLC memory card is due to the guaranteed life time of the memory card. A standard SD card has a 2500000 guaranteed read/write cycles. That I would exceed fast.
The PLC lets me use SQL commands directly in form of a STRING variable. Via a function block this will direct interface with the SQL server (TCP connection).
That would lets me run queries directly from the PLC. But I'm just an electrical engineer and my knowledge in to SQL is limited. I can sent single variables with no problem but I would like to combine all the required variables in to one array and send them "just" once. - dallmann - Thursday, May 24, 2018 10:55 AM
Okay,. so the PLC will talk directly to SQL Server. So you assemble the data into an INSERT statement with the values in the order in which the columns appear in your table. That way, you don't need anything fancy, ... you just construct the INSERT and submit the query and you're good to go...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - dallmann - Wednesday, May 23, 2018 11:21 AM
>> I 'm looking for a way to save an array in to a table. Whereby the elements of the array should be written in to separate columns of the table. <<
I see your new to SQL. Get a good intro book to the language and to RDBMS, and study the parts about normal forms. We don't like data structures (this is part of first normal form or 1NF) which says all data is represented as scalar values in the columns of rows in tables in the schema.
What you have to do to get an array like A[1:5, 1:5] into a table is declare it something like this:
CREATE TABLE A
(i INTEGER NOT NULL UNIQUE
CHECK (i BETWEEN 1 and 5),
j INTEGER NOT NULL UNIQUE
CHECK (j BETWEEN 1 and 5),
element_value REAL NOT NULL);Basically, what you're doing is writing the underlying definition of an array from a procedural language in SQL. The bad news is that if you want to do array calculations on it, such as a transpose or multiplication, it gets kind of ugly
Basically, what you're doing is writing the underlying definition of an array from a procedural language in SQL. The bad news is that if you want to do array calculations on it, such as a transpose or multiplication, it gets kind of uglyPlease post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Sunday, May 27, 2018 7:13 PM
Joe,
This is clearly a case of you not looking at the entire problem. Please go back and re-read all the posts prior to yours. He does NOT need to implement an array in SQL Server. He's got a PLC device that can talk to SQL Server, and all he really needs is a table to insert the values in and to construct a simple INSERT statement with a VALUES clause to supply all those values as they occur.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply