SQl

Sam1980

Supporting Member
Join Date
Aug 2018
Location
WA
Posts
7
Hi all, why some employers ask for SQL knowledge for a PLC position, how can use SQL with PLC?
 
I guess because SQL is a very useful interface with a PLC.

For example- lets say you want to collect production statistics and trend over a month for example; a PLC can collect this information- but can be difficult to store and present all relevant data.

Once the data is collected, it can be read, compared or trended as many times as you want.
 
Recipes are also often stored in a SQL server for safe keeping.
The PLC will need to query the SQL server for the recipe parameters for a particular batch or process.

Some PLC brands do not support connecting to a SQL server and require some sort of middleware to transfer data from the SQL server to the PLC.
 
In the future, more and more, production , manufacturing at its automation will be integrated into the company's ERP.
It is where Industry 4.0 directs and in an ERP everything is stored in databases
 
SQL server is one of the best friends a PLC can have. Generally speaking PLCs don't support dynamic memory allocation. I can't remember exactly but the Siemens S7 might be able to create data blocks at run time. That's not the point though. Is it a good idea to do that at the PLC level? With SQL, many things are possible. From recipe management to data collection to tracking and routing of products through a process. I designed a routing controller for a high speed crossbelt sorter. The routing assignments change all the time and items can move on and off the sorter multiple times per hour/day and the routing of items is very dynamic. It produces approximately 250000 log entries in SQL Server every day.

I have a warehouse gantry/palletizer system I designed from scratch that handles 100s of SKUs and sorts, stacks, palletizes the product and inducts them into the warehouse. There's barcode reading, barcode printing, sending pick and place locations to the robots etc. All of the logic is done in SQL stored procs.

It would be tough to do all this without a database.

With the right middleware, MS SQL Server can be very fast. I stress tested the crossbelt sorter at 20 plus items per second. That includes getting the routing request from the Controllogix, doing the lookup in SQL and responding to the PLC. We are using unsolicited messaging and the packets contain lots of telegrams. Pretty fast.



Cheers.
 
Thank you for all the information you gave me, so If I wanna use SQL with PLC S7-1200
do I need another device between PLC and PC or just the software(SQL) in PC? And what is the good way to start learning SQL to use with PLC
 
Thank you for all the information you gave me, so If I wanna use SQL with PLC S7-1200
do I need another device between PLC and PC or just the software(SQL) in PC? And what is the good way to start learning SQL to use with PLC

You don't necessarily need another physical device between the two, but there will be another layer. A middle layer to move the data between SQL and the PLC. You can use various scada systems some of which are very inexpensive or maybe free. SQL server is free if you are just using it to learn on. So is Microsoft Visual Studio. There are many ways to do it. You can find free libraries to communicate to the PLC using Visual Studio or some other environment and writing a middleware layer yourself.
 
Our plant uses Inductive Automation's Ignition software for data logging. When a bit is triggered in the PLC, Ignition captures the data for the part being built and saves that data into an SQL database. If an issue arises, we can use the serial number of the part to see if there were any anomalies in the build cycle, such as bypassed equipment or supervisor overrides.

We also use SQL databases as a means to display alarms. The PLC stores an alarm number in an integer file, and Ignition uses that number to look up in the database which alarm is triggered so that it can be displayed in multiple areas of the plant.
 
We use SQL extensively here with our plc's

lot numbers, ingredient information, and other information is contained in our recipe data and transferred to the plc. actual package data is then read by SQL from the plc and stored into our data base for future use if required.

we have to many data recipes to store in a plc. they can also vary based on customer requirements.
we get the order, put the po information into our system, plan the order, make the required product, print labels, container quantities, shipping labels, data sheets. all of this information is in our SQL system.

when there is a customer complaint about the produce we sent them, we call up our records and send them the information. we also keep track of the vendors materials used in case there is a quality issue based on that complaint.
we also keep warehouse information in the data base.

hope this helps with your question,
james
 
Thanks for all answers, they are very helpful to get an idea about the SQL with PLC.
What is the good way to learn the SQL and transfer the data from PLC to SQL
any books, websites or online training you suggest to learn this technology or other ideas?

Sattar
 
Script para obter Dados do Sql para o FactoryTalk.

Boa tarde, estou precisando de uma ajuda.

Alguém sabe como faco para buscar dados do Sql Server e mostrar este no meu supervisório FactoryTalk?

Sabem de algum script para fazer esta leitura?

Agradeço a colaboração.
 
You can download SQL Server Express from MS this allows you to create databases etc. It is limited to 4gb I think If your Company uses SQL Server then you need to think about licencing (how many they have) and if they will create a database for your needs.
You will need some form of middleware, however some PLC manufacturers have what is essentially a module that sits on the PLC bus usually called MES, these can be configured to interface with SQL directly. Not sure what Siemens have as it's been some years since I have used them. There are plenty of websites that can give you a guide on SQL. Note: SQL procedures are quite strict on format and not particularly clear on error reporting so any written procedures must follow strict rules.
 
You can download SQL Server Express from MS this allows you to create databases etc. It is limited to 4gb I think If your Company uses SQL Server then you need to think about licencing (how many they have) and if they will create a database for your needs.
You will need some form of middleware, however some PLC manufacturers have what is essentially a module that sits on the PLC bus usually called MES, these can be configured to interface with SQL directly. Not sure what Siemens have as it's been some years since I have used them. There are plenty of websites that can give you a guide on SQL. Note: SQL procedures are quite strict on format and not particularly clear on error reporting so any written procedures must follow strict rules.

Bom dia Amigao, na verdade uso o SQL Server Express, sendo gratuito pois varios clientes nao querem pagar mais uma licenca alem do software de supervisorio.
Meu objetivo e buscar dados do SQL para dentro do meu supervisorio Factorytalk. Andei pesquisando varios sites varios forum e nao conseguie achar nada como exemplo de script em vba para mim fazer esta busca de dados conforme estou querendo. Se tiver alguma ideia de como fazer isto agradeco muito.
 
Gabriel, this is one I did some time ago
Option Explicit
Dim GroupStr1,SQLStr, BatchIDStr, CookerStr,RecipeStr, Phases, Times As String
Dim Rs1 As New ADODB.Recordset
Dim Conn1 As New ADODB.Connection

Sub Main()
On Error GoTo Err
' This creates a string used for a unique batch ID made up of the Date & time i.e. 190523164533
Times = "'" + Format(Now,"yyyy-mm-dd hh:mm:ss") + "'"
BatchIDStr = "'" + Mid(Times,4,2) + Mid(Times,7,2) + Mid(Times,10,2) + Mid(Times,13,2)+ Mid(Times,16,2)+ Mid(Times,19,2) + "'"
'This sets the field for the cooker number in this case set to 3
CookerStr = "'3'"
'This sets The recipe field in this case Recipe 1
RecipeStr = "'Recipe 1'"
' This gets the phase data
Phases = "'" + CStr(GetVariableValue(CH3_Revorack3_Current_Total_Phases)) + "'"
'Set up the Connection String to Database (note: using A datasource name configured on PC
Conn1.ConnectionString = "dsn=DerbySC;uid='sa';pwd='';"
'This is the SQL string to insert the data into the Database fields
SQLStr = "INSERT INTO CookerBatch(BatchStarted,BatchID,Cooker,Recipe,No_Phases) Values (" & Times & "," & BatchIDStr & "," & CookerStr & "," & RecipeStr & "," & Phases & ")"
'Open the connection
Conn1.Open
'Execute the command
Conn1.Execute SQLStr
' Close the connection
Conn1.Close
'Release the connection & clean up
Set Conn1 =Nothing
Debug.Print "Batch Started on Cooker 3 "
Exit Sub
Err:
Debug.Print Error + ": Failed to Write Cooker 3 Batch Details to Database"
Exit Sub

End Sub

The above creates a new record, below updates the other fields as the batch progresses

SQLStr = "INSERT INTO CookerBatch(BatchStarted,BatchID,Cooker,Recipe,No_Phases) Values (" & Times & "," & BatchIDStr & "," & CookerStr & "," & RecipeStr & "," & Phases & ")"

The pic shows the tables as populated
Also see this link
https://use-the-index-luke.com/
Sorry wrong insert into string had two projects open at same time but gives you an idea

SQL.png
 
Last edited:

Similar Topics

Hi all, I'm having difficulties trying to connect FactoryTalk View SE Local Station (V13.00) to MS SQL Server Express. I state that they are...
Replies
2
Views
109
Hi all, I have FTV v13 installed on my VM. I made an ME application and exported it in v11, as that's the version being used on the Panelviews at...
Replies
3
Views
366
Dear colleagues, I am reaching out for assistance with an issue I am having. I have a code that can successfully insert data from FactoryTalk...
Replies
6
Views
1,008
Hi, All: I have a project to replace 3 old window 7 PCs with new window 10 PCs. the 3 old PCs: one is FTV SE server, other 2 are SE clients...
Replies
2
Views
563
Hello, Does anyone have experience managing the FactoryTalk alarm & events SQL database from outside of FactoryTalk? Essentially I'd have tag...
Replies
3
Views
1,246
Back
Top Bottom