Error TooLongFrameException on GPS Tracker Protocol traccar 3.5 with opengts 2.6

Anton Tananaev 10 years ago

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

SteveDusty 10 years ago

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 Tananaev 10 years ago

Try query from here:

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

SteveDusty 10 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 Tananaev 10 years ago

Replace ":device_id" with ":deviceId".

SteveDusty 10 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 Tananaev 10 years ago

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

SteveDusty 10 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 Tananaev 10 years ago

Remove "database.updateLatestPosition" from the config.

SteveDusty 10 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>
chapacan 9 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!