Forum Home › Forums › Development and Integration › ModDBExport to Oracle
Tagged: ModDBExport
- This topic has 16 replies, 2 voices, and was last updated 6 years ago by Mikhail.
-
AuthorPosts
-
April 14, 2018 at 2:33 am #3499csshenrapidParticipant
Dear Mikhail,
When I use the ModDBExport for archive data to oracle, it doesn’t work. Could you help me. Debug Information were following:
1、Oracle database connect was ok, because current data can be send to oracle.
2、ModDBExport.txt showed:
Oracle – 192.168.88.139:1521; state: normal; in queue cur/arc/ev: 100/0/0; exported cur/arc/ev: 0/0/0; skipped cur/arc/ev: 812/0/0
3、SQL for archive data in ModDBExport.dll of ScadaServer:— Insert or update existing archive data
MERGE INTO cnldata
USING dual ON (datetime = :dateTime AND cnlnum = :cnlnum)
WHEN MATCHED THEN
UPDATE SET val = :val, stat = :stat
WHEN NOT MATCHED THEN
INSERT (datetime, cnlnum, val, stat)
VALUES (:dateTime, :cnlNum, :val, :stat)April 14, 2018 at 1:49 pm #3501MikhailModeratorHello,
Please find the *.log file of the module in SCADA\ScadaServer\Log\ and check the errors in this file.April 15, 2018 at 1:34 am #3504csshenrapidParticipantScadaServerSvc.log:—————————–
——————————————————————————–
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> ScadaServerService 5.1.0.3 is started
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> Module is loaded from the file C:\SCADA\ScadaServer\Mod\ModDBExport.dll
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> Check the existence of the data directories is completed successfully
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> Check the existence of the configuration database files is completed successfully
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> Input channels are read from the configuration database. Active channel count: 47
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> Ouput channels are read from the configuration database
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> Users are read from the configuration database
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> Formulas are read from the configuration database
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> The formulas source code has been compiled
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> Connection listener is started
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> Start server
2018-04-15 09:32:41 <WIN-DRJLMJGU35K><SYSTEM><ACT> Current data are loaded
2018-04-15 09:32:50 <WIN-DRJLMJGU35K><SYSTEM><ACT> Connect to client 127.0.0.1
2018-04-15 09:32:50 <WIN-DRJLMJGU35K><SYSTEM><ACT> The user ScadaComm is successfully authenticatedModDBExport.log:
——————————————————————————–
2018-04-15 09:32:41 Start ModDBExport moduleApril 15, 2018 at 1:35 am #3505csshenrapidParticipantModDBExport.log:
——————————————————————————–
2018-04-15 09:32:41 Start ModDBExport module
2018-04-15 09:34:17 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:18 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:19 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:20 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:21 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:22 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:22 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:22 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:23 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:24 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:24 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:24 Unable to enqueue current data. The maximum size of the queue 100 is exceeded
2018-04-15 09:34:25 Unable to enqueue current data. The maximum size of the queue 100 is exceededApril 15, 2018 at 5:24 am #3506csshenrapidParticipantEverything looks well, but there is no archive data in Oracle Database.
April 16, 2018 at 11:28 am #3512csshenrapidParticipantThe connection string is:
Server=192.168.88.139:1521/orcl;User ID=system;Password=54325April 17, 2018 at 1:05 am #3513csshenrapidParticipantwhile insert current data to Oracle database using the following sql string:
INSERT INTO CnlData (DateTime, CnlNum, Val, Stat)
VALUES (:dateTime, :cnlNum, :val, :stat)The error was returned:
2018-04-17 09:04:37 Error export current data to DB Oracle – 192.168.88.139:1521: ORA-00001:Violation of unique constraints (SYSTEM.SYS_C007354)April 17, 2018 at 1:06 am #3514csshenrapidParticipantDear Mikhail,
Could you give me some idea about it?April 17, 2018 at 4:02 am #3515csshenrapidParticipantIn Oracle database, the millisecond of timestamp are all zero:
17-4-18 11.55.08.000000000 amApril 17, 2018 at 5:28 am #3519MikhailModeratorIt seems like server executes requests too long. Can you stop the Communicator service and send current data using Server’s Generator?
How many record already contained in the table cnldata ?
April 17, 2018 at 10:02 pm #3522csshenrapidParticipant“stop the Communicator service and send current data using Server’s Generator”.
I did it, and it worked. Current and archive data were send to oracle database correctly, but the status value was not setting value.“How many record already contained in the table cnldata?”
I executed “delete from cnldata”, and there was no data in the table cnldata.April 18, 2018 at 5:37 am #3525csshenrapidParticipantIt is the same to MYSQL database, and the error information is following:
2018-04-18 13:35:31 Error export current data to DB MySQL – 192.168.88.139:3306: Duplicate entry ‘2018-04-18 13:35:00-21’ for key ‘PRIMARY’April 18, 2018 at 8:25 am #3528csshenrapidParticipantDear Mikhail
Maybe I found the source of the problem.
ScadaServer generated current data with the millisecond of timestamp are all zero. And then, error export to DB MySQL was happened while insert data to MYSQL database because of the same key ‘PRIMARY’. After that ScadaServer could not insert any other data to database.
It worked while insert current data to MYSQL
database using the following sql string:
INSERT INTO cnldata (datetime, cnlnum, val, stat)
VALUES (@dateTime, @cnlNum, @val, @stat)
ON DUPLICATE KEY UPDATE val = @val, stat = @statApril 18, 2018 at 4:55 pm #3535MikhailModeratorHello,
Thank you for the solution. I suppose, that the wrong statement is in the queue and never can be skipped.April 18, 2018 at 4:59 pm #3536MikhailModeratorScadaServer generated current data with the millisecond of timestamp are all zero.
What version of MySQL do you use?
-
AuthorPosts
- You must be logged in to reply to this topic.