Read an sql data and write to a FTView SE memory tag.

[thahamp]

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 View SE into a SQL database. However, when attempting to read a single data value from a specific column in the database back into FactoryTalk View SE, I encounter problems. My goal is to read this value and write it to a memory tag in FactoryTalk View SE version 10, but I have been unable to get this working.

The code I have written so far is provided below:

Option Explicit’ connection string definition

Const Connection As String = "Provider=sqloledb;" & "Data Source=RA-FTMETRICS;" & "Initial Catalog=FactoryTalk Activationsdmin;" & "User Id=dbuser;" & "Password=FactoryTalk Activationsdmin"
Dim xlApp As Object
Dim WithEvents ogroup As TagGroup
 

 
Private Sub Button1_Released()
Insert2MSSQL
End SubPrivate Sub Button2_Released()
getInput
End Sub

Private Sub Button3_Released()
DeleteTable
End SubPrivate Sub Button5_Released()
InsertParam
End Sub

Private Sub Insert2MSSQL()
Dim objcon As New ADODB.Connection
Dim objCom As New ADODB.Command
Dim objRS As ADODB.Recordset
Dim objPara As ADODB.Parameter
Dim objpara2 As ADODB.Parameter
Dim objpara3 As ADODB.Parameter

Dim k As Integer
Dim IDRecipe As Integer

’ On Error GoTo ErrorHandler
’ Creating the DB connection string
’ Please change the below connection string as per your server and
’ database being used.
objcon.ConnectionString = Connection

’ Opening the connection
objcon.Open objcon.ConnectionString
MsgBox "Connection opened"

’assigning the command object parameters
With objCom
’Note the green comments are for the stored procedure implementation
.CommandText = "InsertRecipeR01"
’.CommandText = "INSERT INTO dbo.RecipeList(RecipeName,RecipeDescription)VALUES (’RecipeAA’,AA)"
.CommandType = adCmdStoredProc ’Type : stored procedure
’.CommandType = adCmdText
.ActiveConnection = objcon
End With

’ Create 2 output parameters
Set objPara = objCom.CreateParameter("RecipeName", adVarChar, adParamInput, 50, StrinArray(p))
Set objpara2 = objCom.CreateParameter("RecipeDescription", adVarChar, adParamInput, 99, StrinArray(p))
Set objpara3 = objCom.CreateParameter("IDRecipe", adInteger, adParamOutput)

’ Append the output parameters to command object
objCom.Parameters.Append objPara
objCom.Parameters.Append objpara2
objCom.Parameters.Append objpara3

’ Store the result in a recordset
Set objRS = objCom.Execute
’ retrieve the output parameters values

MsgBox "Total records returned: " & objPara.Value
MsgBox objpara2.Value
MsgBox objpara3.Value
ErrorHandler: ’ close connection
objcon.Close
’ cleaning up
Set objCom = Nothing
Set objcon = Nothing
Set objPara = Nothing
Set objpara2 = Nothing
Set objRS = Nothing
MsgBox "Connection closed"
End Sub

Private Sub getInput()
Dim oElement As Element, oElement1
Dim oInput As NumericInput
Dim SInput As StringInput
Dim k As Integer
k = 0
k = p

For Each oElement In Me.Application.ActiveDisplay.Elements
’ Determine the type of graphic object
If oElement.ProgID = "RSVGFX.NumericInput" Then
’ initialize an object of the correct type
Set oInput = oElement
k = k + 1
’ The array NumInpArray is declared global see Module1
NumInpArray(k) = oInput.Value
MsgBox ("NumericInput " & NumInpArray(k))
End If
Next ’ Get the SE input objects

For Each oElement1 In Me.Application.ActiveDisplay.Elements
If oElement1.ProgID = "RSVGFX.StringInput" Then
’ initialize an object of the correct type
Set SInput = oElement1
p = p + 1
’ The array NumInpArray is declared global see Module1
StrinArray(p) = SInput.Value
MsgBox ("StringInput " & StrinArray(p))
End If
’ save the numbers of numeric input obj. into a global variable
Next
End Sub

Private Sub InsertParam()
Dim objcon As New ADODB.Connection
Dim objCom As New ADODB.Command

’ database being used.
objcon.ConnectionString = Connection
’ Opening the connection
objcon.Open objcon.ConnectionString

With objCom
’ Note the green comments are for stored procedure implementation
’.CommandText = "InsertRecipeR01"
.CommandText = "INSERT INTO dbo.countries(surname,name,country) VALUES (’" + StrinArray(2) + "’,’" + StrinArray(3) + "’,’" + StrinArray(4) + "’)"
’.CommandType = adCmdStoredProc ’Type : stored procedure
.CommandType = adCmdText
.ActiveConnection = objcon
.Execute
End With

End
End Sub

Private Sub DeleteTable()
Dim objcon As New ADODB.Connection
Dim objCom As New ADODB.Command

’ database being used.
objcon.ConnectionString = Connection
’ Opening the connection
objcon.Open objcon.ConnectionString

With objCom
’ Note the green comments are for stored procedure implementation
’.CommandText = "InsertRecipeR01"
.CommandText = "DELETE FROM dbo.RecipeList WHERE RecipeName = ’" + StrinArray(p) + "’"
’.CommandType = adCmdStoredProc ’Type : stored procedure
.CommandType = adCmdText
.ActiveConnection = objcon
.Execute
MsgBox "...command executed"
End With
End Sub

I would greatly appreciate any insights you may have into what I need to modify in order to achieve reading an individual column value from the database and writing it to a FactoryTalk View SE memory tag.

 

[thahamp]

Hi Guys, Any update on this

 

[parky]

Some time since I did any SQL but I have noticed you are not closing the connection in some of those subs, it may be a case that the connections are already open, not had a long look at the code but it looks like there is only one close.

 

[thahamp]

I have the updated code. My case is different. I just need to get a sample code how can read data from SQL to FTVIew SE using ADO method.

 

[parky]

I have never done SQL in FTV only in other platforms are you getting any errors ? i.e. catching on error events I noticed that you are not doing anything in the error handler apart from closing the connection. I suggest you show the error message that would hopefully give you & others here some indication of the problem.

 

[JaxGTO]

Here is a sample of code I use to read a formula from a database and places the data in array tags.


Public Sub FormulaLoad(InFolder As String, txtFormulaID As String, fltRevision As Double, txtFormulaDesc As String)
Dim Cnn As ADODB.Connection
Dim MyRecordset As ADODB.Recordset
Dim txtsql, si As String
Dim intNumberOfSteps, i As Integer
Dim aryAct(40) As Integer
Dim aryIng(40) As Integer
Dim arySP(40) As Double
Dim TheTag As Tag
Dim TheTagGroup As TagGroup
Dim ThePLCTagName As String
Dim KettleNo As String

'Connect to database
Set Cnn = New ADODB.Connection
Cnn.Open strCnn

'Open a recordset with only batchable Formulas
Set MyRecordset = New ADODB.Recordset
txtsql = "SELECT intStepID, intActivityID, intIngredientID, fltSetpoint from viwRSViewFormulaLoad where txtFormulaID='" & _
txtFormulaID & "' and fltRevision=" & Str(fltRevision) & " and intStepID <= 40 order by intStepID"

MyRecordset.Open txtsql, Cnn, adOpenKeyset, adLockReadOnly

'Move to the first record in the recordset
MyRecordset.MoveFirst

With MyRecordset

'Number of steps
intNumberOfSteps = .RecordCount

'loop through all the defined steps (may be < 40)
'and extrIng the 3 values to send to the 3 PLC tables by storing them in the array
While Not .EOF

i = !intStepID 'Get step number 1-40
aryAct(i) = !intActivityID
aryIng(i) = !intIngredientID
arySP(i) = !fltSetpoint
.MoveNext
Wend

.Close
End With

it then writes these arrays to the PLC but that gets you what you need.

 

[JaxGTO]

PS the strCnn is a public constant and looks like this:


Public Const strCnn As String = "Provider=SQLOLEDB;Data Source=192.168.1.222;Initial Catalog=plant;UID=sa;PWD=sa;"