How could I access the data from DB in Linux?

[ghost]

Hello @nicolasr75,

First of all, thank you for your contribution to OPCUA server accessing DB. It's a great work!
I'm trying to use your demo in architecture Linux (Ubuntu 16.04LTS). However, I'd some problems.

I'm using UAExpert like client with bounds disabled.
The server was compiled with following changes.

Changes in SQLiteBackend.h :

1. "sscanf_s" became "scanf".
2. "_mkgmtime" became "timegm".
3. "gmtime_s" became "gmtime_r" with parameters inverted.
4. "strcat_s" became "strcat" without QUERY_BUFFER_SIZE, just query and string like parameters.
5. I didn't initialize "char query[] = "";" and I recently used "memset( query, 0, QUERY_BUFFER_SIZE*sizeof(char));"

I also change nothing in DB.
I used "gcc -o opcuatest open62541.c sqlite3.c SQLiteBackend.h OpcUATest.cpp -pthread -ldl -lstdc++" to compile too.

Well, I would like to know how I can access the data from DB following this scenario.
"myDoubleValue" is available to visualize.

Are there some procedure that missed?

Attached UAExpert window.

Best regards
Eduardo Afonso

[nicolasr75]

Hello Eduardo,
first of all sorry, I haven't touched this code for over year now. But I have tried to reproduce your problem. Since this server implements the interface for historical data access, I used the function "History Trend View" in UA Expert. You find it via "Document -> Add". You can then drag myDoubleValue into the configuration of the history. Then set the time range accordingly. The test database has data in January 2019, so set the start time to 2019-01-01. After presseng "Update" I can then see the data records contained in the test database.

I hope this helps.

Nicolas

[ghost]

Hello Nicolas,

Thank you for your answer!
I'd an errors in server when I tried to setup the time of "History Trend View" and clicked in update.
Could you clarify what it could be?

[nicolasr75]

That error comes from the open62541 code. UA Expert displays the values nevertheless for me. The open62541 files I used in this demo are about 2 years old. From looking at their current codes it seems that the issue is fixed now. You should try to compile with a newer version of the open62541 files.

I would like to update it myself but unfortunately I don't have time to do it within the coming 2 weeks :-(

[ghost]

Unfortunately, I've the same problem.
Well, I'm trying to test in Windows too.
I compiled and run the server. However, UaExpert can't find it.
Is there any additional configuration?

[nicolasr75]

Hmm, not that I know of. My discovery runs also on port 4840 but succeeds to find the server. Have you checked the console of server that it successfully listens on port 4840?
Also the "Add Server" function in UaExpert allows you to manually add the server, have you tried that? Is the Windows Firewall turned on? I remember that when you start a server for the first time, the firewall usually brings up a prompt asking you whether the server should be allowed to communicate over the port.

[piperoc]

Maybe off topic but hope this helps.
To make testing more interesting I wrote a quick python script that populates about 3 months from today of 1 minute data.

Feel free to optimize and improve (or change the duration)

import curses
import sqlite3
import random
from datetime import datetime, timedelta

try:
    sqliteConnection = sqlite3.connect('database.sqlite')
    cursor = sqliteConnection.cursor()
    print("Successfully Connected to SQLite")

    sqlite_select_Query = "select sqlite_version();"
    cursor.execute(sqlite_select_Query)
    record = cursor.fetchall()
    print("SQLite Database Version is: ", record)

    table_create_query = "CREATE TABLE `PeriodicValues` (\
	                        `Timestamp`	TEXT NOT NULL, \
	                        `MeasuringPointID`	INTEGER NOT NULL,\
	                        `Value`	REAL NOT NULL, \
	                        PRIMARY KEY(`Timestamp`,`MeasuringPointID`) \
                            )"
    cursor.execute(table_create_query)


    i = 1
    now = datetime.now()

    while i < 131400:
      x = random.uniform(1.1,100.5)
      dateincrement = now - timedelta(minutes=i)
      insert_query = """INSERT INTO "main"."PeriodicValues" ("Timestamp", "MeasuringPointID", "Value") VALUES ('""" + dateincrement.strftime("%Y-%m-%d %H:%M:%S") + """', '1', '""" + str(x) + """');"""
      count = cursor.execute(insert_query)
      sqliteConnection.commit()
      i += 1

    cursor.close()

except sqlite3.Error as error:
    print("Error while connecting to sqlite", error)
finally:
    if sqliteConnection:
        sqliteConnection.close()
        print("The SQLite connection is closed")

[piperoc]

For those reading this now, please be aware that @happybruce successfully ported this code to Linux/CMake some time ago.
Here's the fork https://github.com/happybruce/opcua
Mind that the DB schema is slightly different but the code is the same.

I think this should resolve this issue as well.