Database SQL usage

[Han]

I have a question about SQL.
“INSERT INTO dbo.PN1 (triggertime,iiot_PN1_A1,iiot_PN1_A2,iiot_PN1_A3,iiot_PN1_A4)
VALUES ( , , , , );” How to associate the value of the variable?
Are there any system default variables? Similar to the system time, it can be used directly.

• This topic was modified 11 months, 3 weeks ago by Han.

[Mikhail]

Hello,

What module of Rapid SCADA do you use to execute this SQL request?
What DBMS type?

[Han]

Microsoft SQL Server,KpDbImport,thank you !

[Mikhail]

Microsoft SQL Server definitely provides a current time function. You should google it on database forums.

If you use KpDbImport for sending commands, it supports @cmdVal and @cmdNum parameters of the SQL statement for command value and command number.

What is the generalized task you need to resolve?

[Mikhail]

There is also ModDbExport module. You should compare it with KpDbImport.

[Han]

Sorry, I am a novice, can you provide the usage of @cmdVal and @cmdNum. I want to write the value read by modbus into SQL, for example, write the value of channel 101 of modbus into SQL?

• This reply was modified 11 months, 2 weeks ago by Han.

[Han]

SQL1SQL2

[Mikhail]

Something like
insert into mytable field1 values (@cmdVal)

A command contains only one value.
You should try. In case of errors, you could send links to screenshots and post logs.

[Han]

Microsoft SQL Server，KpDbImport or ModDBExport;Could you provide the following example ?Thank U

[Mikhail]

Perform the settings according to the manual. In case of errors provide screenshots of the settings and error messages.

[Han]

2021-06-22 22:32:13 Establish a TCP connection with :1433

2021-06-22 22:32:13 Communication session with the Device 2 “SQL2008”, type: KpDbImport
Data request
Error executing query: scalar variable “@CnlNum” must be declared.
Data request
Error executing query: scalar variable “@CnlNum” must be declared.
Data request
Error executing query: scalar variable “@CnlNum” must be declared.
Data request

[Han]

How to declare variables？

[Mikhail]

Variable names are case sensitive. Just copy them from the manual.

[Han]

Hi,Mikhail
Thank you for your help all the time.
Sorry, I failed to describe my needs clearly before. I want to write the data (data1, data2, data3…) obtained by Modbus into the SQL table at a frequency of once a minute. The columns of the SQL table are arranged in accordance with the (writing time, data1, data2, data3… ).Retrieving data from Modbus has been successful. But I don’t know how to write SQL in the above arrangement.

[Han]

I’m sorry, my English is poor, and I don’t know if the grammar I described is correct. I use Google Translate.

[Author]

Posts