ModDBExport to Oracle

Forum Home Forums Development and Integration ModDBExport to Oracle

Tagged: 

This topic contains 16 replies, has 2 voices, and was last updated by Mikhail Mikhail 1 year, 7 months ago.

Viewing 15 posts - 1 through 15 (of 17 total)
  • Author
    Posts
  • #3499
    Avatar
    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)

    #3501
    Mikhail
    Mikhail
    Moderator

    Hello,
    Please find the *.log file of the module in SCADA\ScadaServer\Log\ and check the errors in this file.

    #3504
    Avatar
    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

    #3505
    Avatar
    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

    #3506
    Avatar
    csshenrapid
    Participant

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

    #3512
    Avatar
    csshenrapid
    Participant

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

    #3513
    Avatar
    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)

    #3514
    Avatar
    csshenrapid
    Participant

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

    #3515
    Avatar
    csshenrapid
    Participant

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

    #3519
    Mikhail
    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 ?

    #3522
    Avatar
    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.

    #3525
    Avatar
    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’

    #3528
    Avatar
    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

    #3535
    Mikhail
    Mikhail
    Moderator

    Hello,
    Thank you for the solution. I suppose, that the wrong statement is in the queue and never can be skipped.

    #3536
    Mikhail
    Mikhail
    Moderator

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

    What version of MySQL do you use?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic.