WinCC -> SQL real datatype

del1

Member
Join Date
Feb 2023
Location
CE
Posts
5
Hello all!

I have a problem with data logging from winCC to SQL server. I am trying to write a single variable type "Real" to SQL server. The problem is that SQL table doesn't show the decimal places. Data type of the variable in SQL database is also Real. "data1" is a HMI tag connected to a DB value of type Real in PLC.

If I send value 5.0, only 5 is written into the database.

VB script code:

Sub VBFunction_2()

Dim objConnection
Dim strConnectionString
Dim strSQL
Dim objCommand

Dim Data1

strConnectionString = "Provider=MSDASQL;DSN=TestODBC;UID=;PWD=;"

Data1=SmartTags("data1")

strSQL = "INSERT INTO TableReal (Number) VALUES ( " & Data1 & " );"

Set objConnection = CreateObject ("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objCommand = CreateObject("ADODB.Command")
With objCommand
.ActiveConnection = objConnection
.CommandText = strSQL
End With
objCommand.Execute
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing


End Sub

Has anyone experienced similar issues?

Thank you for helping :)
 
My quick answer is that I think when the column is created in the database there are some parameters like 'precision' that have to be set. I don't know if you can modify them on an existing column. The requirements for the parameters vary with the type of the data.
 
My quick answer is that I think when the column is created in the database there are some parameters like 'precision' that have to be set. I don't know if you can modify them on an existing column. The requirements for the parameters vary with the type of the data.


+1. Can you post the SQL database schema (e.g. see here)?
 
Thank you for replying,

I found no such parameter as "precision", either in an existing table or when creating a new one. Please see the table scheme in the attachment.

Table scheme.jpg
 
SQL data type "Decimal" has parameters "precision" and "scale". If I set "scale" to 2, all existing values in the table are now represented with two decimal places, eg. 5 -> 5.00.

But I still cannot write variables of datatype Real from WinCC to SQL. If I now send value 5.0, I get 5.00 in the table, which is OK. But if I send 5.5, it is not written into the table at all.
 
Try changing the SQL table to float, I seem to remember having a similar problem but not being at home I cannot confirm, I know there is a problem with some transactions from say certain scada systems & SQL. I know we had to format some real numbers from recipes that had been populated to a DB by an old style BPCS system.
 
No luck, with float column 5.0 is again displayed as 5 and 5.5 isn't written at all..
 
I use the real data type in SQL with WinCC with no problem. The data type of the "Data1" tag also must be real, otherwise it will be treated as an INT (or DINT).

Your main problem is that you don't have an identity column. SQL MUST have unique rows. Duplicate rows create ambiguity. Create a second column called ID (or whatever you want) and make it the primary key, set the data type to bigint, and set the "Is Identity" to "yes" in the properties. Then you can insert more than one row. As you have your program, you can only INSERT a specific value once and never again.

If you want to keep your table as you have it with a single row, your first INSERT command must change to an UPDATE command in order to update that same row.

Alternatively you could do "DELETE from TableReal" then execute your INSERT.

strSQL = "UPDATE TableReal SET Number = " & Data1 & ";"
 
Last edited:
Solved: the problem was in the settings of the PC on which SQL server is running. The decimal separator sign (Controlpanel -> Regional settings) must be set to ".". Mine was set to ",", that's why SQL table didn't accept Real values.

Thank you all for helping,
BR
 
Solved: the problem was in the settings of the PC on which SQL server is running. The decimal separator sign (Controlpanel -> Regional settings) must be set to ".". Mine was set to ",", that's why SQL table didn't accept Real values.

Thank you all for helping,
BR


Thanks for the feedback!
 

Similar Topics

I'm little stucked and ask for small help from one with little more experience of vb script. So, I have alarm control window Control1[Alarm...
Replies
3
Views
1,470
Hi all, I'm trying to connect WinCC Pro to SQL server through OBDC. Everything is okie, I'm able to transfer number, DateTime and even a direct...
Replies
2
Views
1,435
I am new to WinCC and have limited experience with SQL servers and databases, but I have been presented with a problem with the overwriting of...
Replies
1
Views
1,286
Kindly, while trying to install Siemens Pcs7 Wincc v6, we got a message that "SQL Server 2000 Service Pack 3a" need to be installed from a CD...
Replies
0
Views
1,325
Hello, Question about database on a Panel PC.. I had a weird question from a customer, but it's a customer so I have to check if it is possible...
Replies
6
Views
3,263
Back
Top Bottom