WinCC -> SQL real datatype

[del1]

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

[Corsair]

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.

[drbitboy]

Originally Posted by Corsair

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)?

[del1]

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.

[del1]

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.

[parky]

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.

[del1]

No luck, with float column 5.0 is again displayed as 5 and 5.5 isn't written at all..

[sigmadelta]

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 & ";"

[del1]

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

[drbitboy]

Originally Posted by del1

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!