HistorySQLite: "Error binding parameter 0 - probably unsupported type."

[Alfy102]

Good day, I tried to historize a node (ns=2;i=2127) data change by following the examples.
Got this error from logging info and its not able to record the data change into the SQL file.

ERROR:asyncua.server.history_sql:Historizing SQL Insert Error for NodeId(Identifier=2127, NamespaceIndex=2, NodeIdType=<NodeIdType.Numeric: 2>): Error binding parameter 0 - probably unsupported type.

Snippets from my main:

import asyncio
import logging
from asyncua import ua, Server
import os.path
import sys
sys.path.insert(0, "..")
from asyncua.server.history_sql import HistorySQLite

class opc_server_worker(QObject):
    async def opc_server(self):
        _logger = logging.getLogger('server_log')
        server = Server()
        server.iserver.history_manager.set_storage(HistorySQLite("server_history.sql"))
        await server.init()
        endpoint = server_ip
        server.set_endpoint(f"opc.tcp://{endpoint}/freeopcua/server/" )
        _logger.info(f"Establisihing Server at {endpoint}/freeopcua/server/")
        server.set_security_policy([ua.SecurityPolicyType.Basic256Sha256_SignAndEncrypt],permission_ruleset=SimpleRoleRuleset())
        
        """


        Some bits over here on loading xml file and "for loop" the nodes to put into list.


        """
    
    
    
        _logger.info('Starting server!')
        async with server:
            myvar=device_group[0] #<-- the list of variables node ids.
            print(myvar)
            await server.historize_node_data_change(myvar, period=None, count=100)
            _logger.info('Server Started!')
            try:
                while True:
                    await asyncio.sleep(2)
                    tasks = [await asyncio.create_task(self.scan_loop_plc(start_device[i],zipped_data[i],server,ipaddress[i])) for i in range(len(device_group))]
            except:
                 _logger.info('Server Failed to Start!')
                
            
            await server.stop()
            await asyncio.sleep(1)

Seems like this does not raised exception as the program can still perform the while loop.

My log output:

INFO:server_log:Starting server!
INFO:asyncua.server.internal_server:starting internal server
INFO:asyncua.server.binary_server_asyncio:Listening on 127.0.0.1:4840
ns=2;i=2127
INFO:asyncua.server.subscription_service:create subscription
INFO:asyncua.common.subscription:Subscription created 79
INFO:asyncua.common.subscription:Publish callback called with result: PublishResult(SubscriptionId=79, AvailableSequenceNumbers=[], MoreNotifications=False, NotificationMessage_=NotificationMessage(SequenceNumber=1, PublishTime=datetime.datetime(2021, 8, 14, 6, 27, 45, 767086), NotificationData=[]), Results=[], DiagnosticInfos=[])
INFO:asyncua.server.history_sql:Historizing SQL Table Creation Error for NodeId(Identifier=2127, NamespaceIndex=2, NodeIdType=<NodeIdType.Numeric: 2>): table "2_2127" already exists
INFO:asyncua.server.subscription_service:create monitored items
INFO:asyncua.server.monitored_item_service.79:request to subscribe to datachange for node NodeId(Identifier=2127, NamespaceIndex=2, NodeIdType=<NodeIdType.Numeric: 2>) and attribute AttributeIds.Value
INFO:server_log:Server Started!
INFO:asyncua.common.subscription:Publish callback called with result: PublishResult(SubscriptionId=79, AvailableSequenceNumbers=[], MoreNotifications=False, NotificationMessage_=NotificationMessage(SequenceNumber=1, PublishTime=datetime.datetime(2021, 8, 14, 6, 27, 45, 770096), NotificationData=[DataChangeNotification(MonitoredItems=[MonitoredItemNotification(ClientHandle=201, Value=DataValue(Value=Variant(Value=1, VariantType=<VariantType.Int64: 8>, Dimensions=None, is_array=False), StatusCode_=StatusCode(value=0), SourceTimestamp=DateTime(2021, 8, 14, 6, 27, 45, 759092), ServerTimestamp=DateTime(2021, 8, 14, 6, 27, 45, 760087), SourcePicoseconds=None, ServerPicoseconds=None))], DiagnosticInfos=[])]), Results=[], DiagnosticInfos=[])
ERROR:asyncua.server.history_sql:Historizing SQL Insert Error for NodeId(Identifier=2127, NamespaceIndex=2, NodeIdType=<NodeIdType.Numeric: 2>): Error binding parameter 0 - probably unsupported type.
INFO:asyncua.common.subscription:Publish callback called with result: PublishResult(SubscriptionId=79, AvailableSequenceNumbers=[], MoreNotifications=False, NotificationMessage_=NotificationMessage(SequenceNumber=1, PublishTime=datetime.datetime(2021, 8, 14, 6, 27, 47, 783038), NotificationData=[DataChangeNotification(MonitoredItems=[MonitoredItemNotification(ClientHandle=201, Value=DataValue(Value=Variant(Value=0, VariantType=<VariantType.Int64: 8>, Dimensions=None, is_array=False), StatusCode_=StatusCode(value=0), SourceTimestamp=DateTime(2021, 8, 14, 6, 27, 47, 773036), ServerTimestamp=DateTime(2021, 8, 14, 6, 27, 47, 781047), SourcePicoseconds=None, ServerPicoseconds=None))], DiagnosticInfos=[])]), Results=[], DiagnosticInfos=[])

[AndreasHeine]

what nodetype is it?
what is myvar? list of stings?

[AndreasHeine]

ua.DataValue(count,SourceTimestamp=DateTime.utcnow()

you need to pass in a variant class instead of count with the propper type

[Alfy102]

I tried to pass in a variant class below however the same error still occurs:

from asyncua.ua.uatypes import DateTime


data_value = ua.DataValue(ua.Variant(count, ua.VariantType.Int64),SourceTimestamp=DateTime.utcnow(), ServerTimestamp=DateTime.utcnow())
await server.write_attribute_value(zipped_data[0].nodeid, data_value)

However by using date time from import datetime instead of from asyncua.ua.uatypes import DateTime, the error does not occur and the database can be historized.

import datetime

data_value = ua.DataValue(ua.Variant(count, ua.VariantType.Int64),SourceTimestamp=datetime.datetime.now(), ServerTimestamp=datetime.datetime.now())
await server.write_attribute_value(zipped_data[0].nodeid, data_value)

[swamper123]

The class DateTime is the same as datetime.datetime. The Methods are the same.
In your first example you are using utcnow()and in the second one now(). This is a difference! now()returns the local time, while utcnow()returns, as you suggest, utc time. So it's not an import issue.

[swamper123]

What I am wondering is, why your "Date seperator" is different to Andreas ones. You are having / while Andreas has ....not sure if this may cause trouble as well?!

[Alfy102]

    myvar1 = await myobj.add_variable(idx, "MyVariable", ua.Variant(0, ua.VariantType.Int64))
    myvar2 = await myobj.add_variable(idx, "MyVariable", ua.Variant(0, ua.VariantType.Int64)) 
    await myvar1.set_writable()  # Set MyVariable to be writable by clients
    await myvar2.set_writable()  # Set MyVariable to be writable by clients
    print(f"{myvar1} using DateTime.utcnow()")
    print(f"{myvar2} using datetime.datetime.utcnow()")

    # starting!
    await server.start()

    # enable data change history for this particular node, must be called after start since it uses subscription
    await server.historize_node_data_change(myvar1, period=None, count=100)
    await server.historize_node_data_change(myvar2, period=None, count=100)

    #async with server:
    try:
        count = 0
        while True:
            await asyncio.sleep(2)
            count += 1
            #source_time = datetime.datetime.now()
            data_value1 = ua.DataValue(ua.Variant(count, ua.VariantType.Int64),SourceTimestamp=DateTime.utcnow(), ServerTimestamp=DateTime.utcnow())
            data_value2 = ua.DataValue(ua.Variant(count, ua.VariantType.Int64),SourceTimestamp=datetime.datetime.utcnow(), ServerTimestamp=datetime.datetime.utcnow()) #<------Declaring data_value this way causes the error. For this example, I used DateTime.utcnow() for both.
            print(f"{DateTime.utcnow()} on {myvar1}")
            await server.write_attribute_value(myvar1.nodeid, data_value1)
            print(f"{datetime.datetime.utcnow()} on {myvar2}")
            await server.write_attribute_value(myvar2.nodeid, data_value2)
    finally:
        # close connection, remove subscriptions, etc
        await server.stop()

Changed to utcnow() and also added the second variable to compare.
I also check about the / and . difference by printing by out the time.

Here is my output:

and here is the database (left is ns=2;i=3, right is left is ns=2;i=2)

[Alfy102]

Found a workaround for this issue by not using from asyncua.ua.uatypes import DateTime for SourceTimestamp = DateTime.now() and ServerTimestamp = DateTime.now().

But instead use import datetime and then set SourceTimestamp = datetime.datetime.now() or ServerTimestamp = datetime.datetime.now()

I am unsure what causes the issue when using from asyncua.ua.uatypes import DateTime
If I were to remove the SourceTimestamp and ServerTimestamp from
data_value = ua.DataValue(count,SourceTimestamp=DateTime.utcnow(), ServerTimestamp=DateTime.utcnow())
by writing it as data_value = ua.DataValue(count) the Error binding parameter 0 - probably unsupported type. does not occur.
However it will only write with it the source time in the database table.

Now by using import datetime it properly writes to the database the data changes as well as both sourcetime and servertime.

[AndreasHeine]

However it will only write with it the source time in the database table.

https://reference.opcfoundation.org/v104/Core/docs/Part4/7.7.4/ -> The serverTimestamp is used to reflect the time that the Server received a Variable value or knew it to be accurate.

https://reference.opcfoundation.org/v104/Core/docs/Part4/7.7.3/ -> The sourceTimestamp is used to reflect the timestamp that was applied to a Variable value by the data source.