Getting extended_info into OpenGTS 2.6.5 from traccar 4.2

Hi,

I am using the traccar watch protocol decoder to pass location information into openGTS and everything seems to be working OK. I followed the instructions here to set things up.

OpenGTS integration link

openGTS version 2.6.5
traccar version 4.2

The problem occurs when I try to pass extended information (such as battery level) across to openGTS. Whatever I put in the traccar config. I get an error in the log files when the device updates

For example in the following, I modify the database.insertPosition SQL command to pass the extended_info into the notes field of openGTS Device table

    <entry key='database.insertPosition'>
        START TRANSACTION;
            UPDATE Device SET lastValidLatitude = :latitude, lastValidLongitude = :longitude, lastGPSTimestamp = UNIX_TIMESTAMP(:fixTime), lastUpdateTime = UNIX_TIMESTAMP(NOW()), notes = :extended_info WHERE imeiNumber = SUBSTRING(CAST(:deviceId AS CHAR(32)), 2);
            SELECT @accountID := accountID, @deviceID := deviceID FROM Device WHERE imeiNumber = SUBSTRING(CAST(:deviceId AS CHAR(32)), 2);
            INSERT INTO EventData (accountID, deviceID, timestamp, statusCode, latitude, longitude, speedKPH, heading, altitude, rawData, creationTime, address)
            VALUES (@accountID, @deviceID, UNIX_TIMESTAMP(:fixTime), 0, :latitude, :longitude, :speed * 1.852, :course, :altitude, '', UNIX_TIMESTAMP(NOW()), :address);
        COMMIT;
    </entry>

I then stop and start the traccar server, and force the device to send an update. The traccar server logs now show the following

2019-02-02 12:10:02  INFO: Starting server...
2019-02-02 12:10:44  INFO: [c8f78c0a] connected
2019-02-02 12:10:53  INFO: [c8f78c0a: 5093 < 192.168.0.65] HEX: 5b53472a393035313136393235382a303035412a55442c3032303231392c3132313034342c412c35322e3739383031302c4e2c322e3131343737382c572c302e323430372c3030302c312c30362c3130302c39302c302c35302c30303030303030302c302c312c3233342c31302c2c30305d
2019-02-02 12:10:53  INFO: Query not provided: database.updateDeviceExtended
2019-02-02 12:10:54  WARN: Failed to store position - No value specified for parameter 4 - SQLException (... < QueryBuilder:480 < DataManager:458 < DefaultDataHandler:30 < ...)
2019-02-02 12:10:54  INFO: Query not provided: database.updateLatestPosition
2019-02-02 12:10:54  INFO: [c8f78c0a] id: 9051169258, time: 2019-02-02 12:10:44, lat: 52.79801, lon: -2.11478, speed: 0.1, course: 0.0

In this example, I'm not even trying to decode the extended_info - Just pass it in its entirety, and yet the command fails. The hex value decodes to

[SG*9051169258*005A*UD,020219,121044,A,52.798010,N,2.114778,W,0.2407,000,1,06,100,90,0,50,00000000,0,1,234,10,,00]

so I'm obviously getting some good info (battery is at 90%), but I cant work out the value to pass here.

I've tried the suggestions provided under this link, but nothing seems to work.

GitHub issue 1128

Can anyone assist? I'm happy to provide further information if required.

Update

I tried changing :extended_info to :other but this didn't work either.......

Thought I had it there. Sigh.....

I guess the question has to be "In traccar 4.2, what is the named parameter that stores extended information such as battery level"

You should use :attributes.

That has worked! Thank you!

I've been knocking my head against this stumbling block for some time. Big kudos for the speedy answer

For those that come after, here is the relevent section of the my working config

Please note!

The first entry (database.xml) is important. It allows us to use the EXTRACTVALUE function later to get the battery level out of an XML string.

Without this traccar will default to JSON format, and my version of openGTS mysql/mariadb database doesn't have the handy JSON extract functions.

Now the web front-end wont work with XML - It requires JSON, but seeing as I'm not using that (I'm just using traccar for the protocol decoders) I went with the XML format instead of using some clunky string slicing in SQL.

    <entry key='database.xml'>true</entry>
    
    <entry key='database.selectDevices'>
        SELECT CONCAT('1', imeiNumber) AS id, imeiNumber AS uniqueId FROM Device WHERE imeiNumber REGEXP '^[0-9]+$';
    </entry>

    <entry key='database.selectAllDevices'>
        SELECT CONCAT('1', imeiNumber) AS id, imeiNumber AS uniqueId FROM Device WHERE imeiNumber REGEXP '^[0-9]+$';
    </entry>

    <entry key='database.insertPosition'>
        START TRANSACTION;
            UPDATE Device SET lastValidLatitude = :latitude, lastValidLongitude = :longitude, lastGPSTimestamp = UNIX_TIMESTAMP(:fixTime), lastUpdateTime = UNIX_TIMESTAMP(NOW()), lastBatteryLevel = CAST(EXTRACTVALUE( :attributes , '/info/batteryLevel' ) AS DOUBLE) WHERE imeiNumber = SUBSTRING(CAST(:deviceId AS CHAR(32)), 2);
            SELECT @accountID := accountID, @deviceID := deviceID FROM Device WHERE imeiNumber = SUBSTRING(CAST(:deviceId AS CHAR(32)), 2);
            INSERT INTO EventData (accountID, deviceID, timestamp, statusCode, latitude, longitude, speedKPH, heading, altitude, rawData, creationTime, address)
            VALUES (@accountID, @deviceID, UNIX_TIMESTAMP(:fixTime), 0, :latitude, :longitude, :speed * 1.852, :course, :altitude, :attributes, UNIX_TIMESTAMP(NOW()), :address);
        COMMIT;
    </entry>

I hope this helps

I think you should learn markdown ;) use 3 back-ticks to mark a code block instead of putting back-ticks on every line.

(Hangs head in shame)

Thanks Anton, will do.