Getting Data from SQL Database to the PLC

TheNewPLCGUY

Member
Join Date
Jan 2018
Location
Cambridge
Posts
5
Hello Eveyone,

I need your advice on one problem.I am using CompactLogix PLC.I am looking for some ways.So, that a PLC program can read data from the SQL server and use it to send the data to Laser Marker.

Basically, the PLC has to send a unique code to the laser marker for laser marking purpose.The Machine will confirm with the database of existing markup codes,That the code it is about to mark is unique.If the code is not unique then PLC will raise an alarm .

I am a newbie here and I am doing it for the first time.If you have any other questions please let me know.Any suggetions from the experts would be appreciated.

Thank you
 
Welcome to the PLCTalk forum community !

In general, a CompactLogix cannot directly exchange data with an SQL Server. There has to be some kind of middleware that performs the SQL queries and exchanges data with the controller.

Is the SQL server running on a local computer, or a server somewhere in the enterprise ?

Do you have any local HMI devices ?

There is always a trade-off between the degree of programming skill and experience required, and the amount of off-the-shelf software necessary.

If this were my system I would have built the HMI in Indusoft Web Studio, and therefore would have its comms drivers and SQL exchanges and VBScript available as my tools.

Others might approach it with AdvancedHMI and its driver set and VB container and SQL connectivity.
 
https://sourceforge.net/projects/advancedhmi/
I would use AdvancedHMI which is a free software written in VB.Net. All of the drivers required to communicate to the CompactLogix PLC is included. This runs in visual studio, so the connection to a SQL database is possible.
AdvancedHMI will read the information in the PLC, compare the database information and write back to the PLC.

Regards,
 
Welcome to the PLCTalk forum community !

In general, a CompactLogix cannot directly exchange data with an SQL Server. There has to be some kind of middleware that performs the SQL queries and exchanges data with the controller.

Is the SQL server running on a local computer, or a server somewhere in the enterprise ?

Do you have any local HMI devices ?

There is always a trade-off between the degree of programming skill and experience required, and the amount of off-the-shelf software necessary.

If this were my system I would have built the HMI in Indusoft Web Studio, and therefore would have its comms drivers and SQL exchanges and VBScript available as my tools.

Others might approach it with AdvancedHMI and its driver set and VB container and SQL connectivity.
HI Ken & gclshortt,
Thank you for the quick response.The SQL server is running on a local machine.I was thinking about the AdvancedHMI but I am not sure that it helps me to read data from the SQL server and write it to the PLC program.Also, we do not have a dedicated HMI but the present system has a VB.Net program that has some screens .We are using it for any control and troubleshoot purpose.
Thanks
 
Last edited:
Also would need the Allen Bradley driver module right?

Still could be considered cheap depending on the value of your time vs. skill with VB.net

Yeah, I guess cheap is subjective. You will need driver.
AdvancedHMI is definitely an option, but quite a bit harder. The SQL module is quite easy to use. Best thing, try it for free.
 
Long shot option; Omron NJ PLC's have SQL built in. Their tutorial is very good, I have no SQL experience at all and had a data query up and running in 15 minutes. They have native ethernet IP support so it is a good candidate to be in between your server and CLX PLC. All done from ladder and dialogue boxes.
 
Is node-red bidirectional? I've never heard of it, thanks for posting.

Oh yeah. There are AB Ethernet/IP drivers and PCCC drivers. Read and write to tags. It’s for IoT applications and is programmed graphically much like FBD. It is not necessarily marketed for PLCs but it has the capability.

I have a barcode verification app built with it. Operators must scan a master sku and then scan all components as they pack them. It queries our sql server for the BOM and also writes completed packouts with time stamp to the sql server.
Using a RaspberryPi and a Cognex 8050 with WIFI hooked up via USB to the Pi. And no pesky PLC!

https://flows.nodered.org/node/node-red-contrib-cip-ethernet-ip
 
HI Ken & gclshortt,
Thank you for the quick response.The SQL server is running on a local machine.I was thinking about the AdvancedHMI but I am not sure that it helps me to read data from the SQL server and write it to the PLC program.Also, we do not have a dedicated HMI but the present system has a VB.Net program that has some screens .We are using it for any control and troubleshoot purpose.
Thanks

If you have the source for that app you could add a subsystem to get the data from SQL and send it to the PLC. I write custom middle-ware for a living but I use C# instead of VB. Basically you need something to trigger a SQL query. Usually this is from an operator pressing a button or a register in the PLC changing states. This requires a PLC driver. You can buy one and use it in your existing VB app. Usually they are a dll you reference in your project. You also need the database connection code. This requires a connection string and a query. You can put the query in the VB code or call a stored procedure. Here is an example of the database connection to a PostgreSQL database. Microsoft SQL looks similar. hopefully the formatting isn't too bad. There are many different types of queries. A single value or multiple values, or a record, or a record set, dataset etc. As you can see there is a lot to it, but once you learn it it is really easy and fun. This is just one approach. I mention it because you have an existing program in place.

public void GetData()
{
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=User;" + Password=Pass;Database=test_db;");
conn.Open();

// Define a query
NpgsqlCommand cmd = new NpgsqlCommand("select * from products", conn);

// Execute a query
NpgsqlDataReader dr = cmd.ExecuteReader();

// Read all rows and output the first column in each row
while (dr.Read())
{
//Console.Write ("{0}\n", dr [0]);
Console.Write (dr [0].ToString() + "<>" + dr [1].ToString() + "\n");
}

// Close connection
conn.Close();
}
 
I would keep the existing computer and HMI in place. Add another computer to run AdvancedHMI.
"The Machine will confirm with the database of existing markup codes,That the code it is about to mark is unique." "I was thinking about the AdvancedHMI but I am not sure that it helps me to read data from the SQL server and write it to the PLC program."
The markup code will be read by AdvancedHMI. It will then compare to the database and write back if the code is unique to the PLC.
What I would do is look at the software and run a few experiments with reading and writing to the PLC. Then run some other experiments with connecting and executing the SQL commands to the database.
+1 SD_Scott

Regards,
 
no need for SQL, you can search for SCADA recipe, it allows operator to download parameters to the PLC program based on the production process requirements.


these parameters are stored in a separate recipe files on the scada PC harddrive.



BR
 

Similar Topics

So i have a tag in wincc and when i run the projeckt the tag has different values. I created an arhive for the tag but in sql i dont find the...
Replies
3
Views
3,315
How can i get out the data from sqserver 2000. I have some tags and i want there values. In what intervals is the program stores the value of the...
Replies
2
Views
6,825
All, I have a CompactLogix L33 and would like to get data from a height gauge. The gauge is a Fowler Sylac Mark VI with RS232 output. I can...
Replies
8
Views
2,574
Hello I am hoping someone here can maybe at least tell me what I shlould read up on or if this is even possible. Rockwell Studio 5000 is my...
Replies
12
Views
4,222
Hi, Guys I'm trying to upgrade a device witch comms is thru a RS232. The PLC is a Compactlogix L16ER to a remote PIO 1734-AENT then on this PIO a...
Replies
22
Views
8,157
Back
Top Bottom