ModDBExport to Oracle

Forum Home › Forums › Development and Integration › ModDBExport to Oracle

Tagged: 

Viewing 15 posts - 1 through 15 (of 17 total)
1 2
  • Author
    Posts
  • April 14, 2018 at 2:33 am #3499
    csshenrapid
    Participant

    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 #3501
    Mikhail
    Moderator

    Hello,
    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 #3504
    csshenrapid
    Participant

    ScadaServerSvc.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 authenticated

    ModDBExport.log:
    ——————————————————————————–
    2018-04-15 09:32:41 Start ModDBExport module

    April 15, 2018 at 1:35 am #3505
    csshenrapid
    Participant

    ModDBExport.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 exceeded

    April 15, 2018 at 5:24 am #3506
    csshenrapid
    Participant

    Everything looks well, but there is no archive data in Oracle Database.

    April 16, 2018 at 11:28 am #3512
    csshenrapid
    Participant

    The connection string is:
    Server=192.168.88.139:1521/orcl;User ID=system;Password=54325

    April 17, 2018 at 1:05 am #3513
    csshenrapid
    Participant

    while 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 #3514
    csshenrapid
    Participant

    Dear Mikhail,
    Could you give me some idea about it?

    April 17, 2018 at 4:02 am #3515
    csshenrapid
    Participant

    In Oracle database, the millisecond of timestamp are all zero:
    17-4-18 11.55.08.000000000 am

    April 17, 2018 at 5:28 am #3519
    Mikhail
    Moderator

    It 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 #3522
    csshenrapid
    Participant

    “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 #3525
    csshenrapid
    Participant

    It 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 #3528
    csshenrapid
    Participant

    Dear 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 = @stat

    April 18, 2018 at 4:55 pm #3535
    Mikhail
    Moderator

    Hello,
    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 #3536
    Mikhail
    Moderator

    ScadaServer generated current data with the millisecond of timestamp are all zero.

    What version of MySQL do you use?

  • Author
    Posts
Viewing 15 posts - 1 through 15 (of 17 total)
1 2
  • You must be logged in to reply to this topic.