Error TooLongFrameException on GPS Tracker Protocol traccar 3.5 with opengts 2.6

OK, we are almost there. Replace ":time" with ":fixTime".

Thanks Anton.. I really appreciate the support, made the change now im getting

2016-04-27 21:56:41  INFO: Starting server...
2016-04-27 21:56:50  INFO: [9EFD804C] connected
2016-04-27 21:56:51 DEBUG: [9EFD804C: 5023 < 186.96.91.205] HEX: 78780d01035548833991140000e8cbc10d0a
2016-04-27 21:56:51 DEBUG: [9EFD804C: 5023 > 186.96.91.205] HEX: 7878050100e8a3130d0a
2016-04-27 21:56:51  INFO: Query not provided: database.updateDeviceStatus
2016-04-27 21:56:56 DEBUG: [9EFD804C: 5023 < 186.96.91.205] HEX: 78781f1210041b153916c50112dbe90906b0fd003c0402c804a41300102400e848090d0a
2016-04-27 21:56:56 DEBUG: [9EFD804C: 5023 > 186.96.91.205] HEX: 7878051200e8c9e20d0a
2016-04-27 21:56:56  INFO: Query not provided: database.updateDeviceStatus
2016-04-27 21:56:56  WARN: Incorrect syntax near the keyword 'TRANSACTION'. - SQLServerException (... < QueryBuilder:431 < DataManager:440 < DefaultDataHandler:27 < ...)
2016-04-27 21:56:56  INFO: [9EFD804C] id: 355488339911400, time: 2016-04-27 15:57:22, lat: 10.00731, lon: -84.12971, speed: 0.0, course: 4.0
2016-04-27 21:57:06 DEBUG: [9EFD804C: 5023 < 186.96.91.205] HEX: 78781f1210041b153920c60112dbe90906b0fd003c0402c804a41300102400e9eaca0d0a
2016-04-27 21:57:06 DEBUG: [9EFD804C: 5023 > 186.96.91.205] HEX: 7878051200e9d86b0d0a
2016-04-27 21:57:06  INFO: Query not provided: database.updateDeviceStatus
2016-04-27 21:57:06  WARN: Incorrect syntax near the keyword 'TRANSACTION'. - SQLServerException (... < QueryBuilder:431 < DataManager:440 < DefaultDataHandler:27 < ...)
2016-04-27 21:57:06  INFO: [9EFD804C] id: 355488339911400, time: 2016-04-27 15:57:32, lat: 10.00731, lon: -84.12971, speed: 0.0, course: 4.0

Anton Tananaev9 years ago

Try query from here:

https://github.com/tananaev/traccar/issues/640

SteveDusty9 years ago

Changed and got.

2016-04-27 22:28:29  INFO: Query not provided: database.updateDeviceStatus
2016-04-27 22:28:29  WARN: The value is not set for the parameter number 4. - SQLServerException (... < QueryBuilder:431 < DataManager:440 < DefaultDataHandler:27 < ...)
2016-04-27 22:28:29  INFO: [624B3E29] id: 355488339911400, time: 2016-04-27 16:28:54, lat: 10.00730, lon: -84.12977, speed: 0.0, course: 162.0

these are the queries in the Current Config

    <entry key='database.selectDevicesAll'>
        SELECT imeiNumber AS id, imeiNumber AS uniqueId FROM msDevice WHERE imeiNumber IS NOT NULL;
    </entry>

    <entry key='database.insertPosition'>

    
DECLARE @accountID varchar(32) 
DECLARE @deviceID varchar(32) 
DECLARE @updatetime bigint
set @updatetime =   (SELECT CONVERT(bigINT,:fixTime));  
UPDATE MSDevice SET lastValidLatitude = :latitude, lastValidLongitude = :longitude, lastGPSTimestamp = @updatetime, lastUpdateTime = GEtDATE() WHERE imeiNumber = :device_id;         
SELECT @accountID = accountID,@deviceID=deviceID  FROM MSDevice WHERE imeiNumber = :device_id;
            INSERT INTO MSEventData (accountID, deviceID, timestamp, statusCode, latitude, longitude, speedKPH, heading, altitude, rawData, creationTime, address)
            VALUES (@accountID, @deviceID, @updatetime, 0, :latitude, :longitude, :speed * 1.852, :course, :altitude, '', GEtDATE(), :address);
    </entry>

    <!-->
        device_id - Long
        id - Long
    <-->
    <entry key='database.updateLatestPosition'>
        UPDATE MSdevices SET latestPosition_id = :id WHERE id = :device_id;
    </entry>

Anton Tananaev9 years ago

Replace ":device_id" with ":deviceId".

SteveDusty9 years ago

Thanks Just one issue more with Date Conversion

2016-04-27 22:55:44  INFO: Query not provided: database.updateDeviceStatus
2016-04-27 22:55:44  WARN: Explicit conversion from data type datetime2 to bigint is not allowed. - SQLServerException (... < QueryBuilder:431 < DataManager:440 < DefaultDataHandler:27 < ...)
2016-04-27 22:55:44  INFO: [93ABFAF3] id: 355488339911400, time: 2016-04-27 16:56:09, lat: 10.00736, lon: -84.12966, speed: 0.0, course: 291.0

Anton Tananaev9 years ago

Not sure how to fix this problem. Try to experiment with CONVERT function.

SteveDusty9 years ago

Thanks Fixed the conert but error still appears

[48984629] id: 355488339911400, time: 2016-04-27 17:59:36, lat: 10.00741, lon: -84.12968, speed: 0.0, course: 210.0
2016-04-27 23:59:20 DEBUG: [48984629: 5023 < 186.96.84.36] HEX: 78781f1210041b173b2ec60112dc9d0906b0d0003cd202c804a41300106b006184830d0a
2016-04-27 23:59:20 DEBUG: [48984629: 5023 > 186.96.84.36] HEX: 787805120061d02b0d0a
2016-04-27 23:59:20  INFO: Query not provided: database.updateDeviceStatus
2016-04-27 23:59:20  WARN: The statement must be executed before any results can be obtained. - SQLServerException (... < QueryBuilder:433 < DataManager:440 < DefaultDataHandler:27 < ...)
2016-04-27 23:59:20  INFO: [48984629] id: 355488339911400, time: 2016-04-27 17:59:46, lat: 10.00741, lon: -84.12968, speed: 0.0, course: 210.0

Current Config

<entry key='database.selectDevicesAll'>
        SELECT imeiNumber AS id, imeiNumber AS uniqueId FROM msDevice WHERE imeiNumber IS NOT NULL;
    </entry>

    <entry key='database.insertPosition'>

    
DECLARE @accountID varchar(32) 
DECLARE @deviceID varchar(32) 
DECLARE @tijd bigint
DECLARE @nu bigint
set @tijd = (select dbo.unix_timestamp(CONVERT(VARCHAR(19),:fixTime,120)));
set @nu = (select dbo.unix_timestamp(CONVERT(VARCHAR(19),getdate(),120)));

UPDATE MSDevice SET lastValidLatitude = :latitude, lastValidLongitude = :longitude, lastGPSTimestamp = @tijd, lastUpdateTime = @nu WHERE imeiNumber = :deviceId;         
SELECT @accountID = accountID,@deviceID=deviceID  FROM MSDevice WHERE imeiNumber = :deviceId;
            INSERT INTO MSEventData (accountID, deviceID, timestamp, statusCode, latitude, longitude, speedKPH, heading, altitude, rawData, creationTime, address)
            VALUES (@accountID, @deviceID, @tijd, 0, :latitude, :longitude, :speed * 1.852, :course, :altitude, '', @nu, :address);
    </entry>

    <!-->
        device_id - Long
        id - Long
    <-->
    <entry key='database.updateLatestPosition'>
        UPDATE MSdevices SET latestPosition_id = :id WHERE id = :deviceId;
    </entry>

Anton Tananaev9 years ago

Remove "database.updateLatestPosition" from the config.

SteveDusty9 years ago

Thanks Anton is working correctly now.

Below is the correct query for Traccar 3.5 with OpenGTS 2.6.1 integration - SQL Server 2014

    <entry key='database.selectDevicesAll'>
        SELECT imeiNumber AS id, imeiNumber AS uniqueId FROM msDevice WHERE imeiNumber IS NOT NULL;
    </entry>

    <entry key='database.insertPosition'>    
DECLARE @accountID varchar(32) 
DECLARE @deviceID varchar(32) 
DECLARE @tijd bigint
DECLARE @nu bigint
set @tijd = (select dbo.unix_timestamp(CONVERT(VARCHAR(19),:fixTime,120)));
set @nu = (select dbo.unix_timestamp(CONVERT(VARCHAR(19),getdate(),120)));

UPDATE MSDevice SET lastValidLatitude = :latitude, lastValidLongitude = :longitude, lastGPSTimestamp = @tijd, lastUpdateTime = @nu WHERE imeiNumber = :deviceId;         
SELECT @accountID = accountID,@deviceID=deviceID  FROM MSDevice WHERE imeiNumber = :deviceId;
            INSERT INTO MSEventData (accountID, deviceID, timestamp, statusCode, latitude, longitude, speedKPH, heading, altitude, rawData, creationTime, address)
            VALUES (@accountID, @deviceID, @tijd, 0, :latitude, :longitude, :speed * 1.852, :course, :altitude, '', @nu, :address);
    </entry>

chapacan8 years ago

Hello, This query could be more accurate to get all devices with numeric IMEI (it works from SQL Server 2012 in advance):

For Traccar 3.5 version used in your example:


        SELECT imeiNumber AS id, imeiNumber AS uniqueId FROM MSDevice WHERE TRY_PARSE(imeiNumber as int) is not null;

For Traccar 3.7 version which I am using now it will be something like:


        SELECT CONCAT('1', imeiNumber) AS id, imeiNumber AS uniqueId FROM MSDevice WHERE TRY_PARSE(imeiNumber as int) is not null;

Thank you for sharing your solution Steve Dusty, and Anton for support given!