[HELP] Gateway acknowledges new data but doesn't create new devices or update existing ones?

[S1L3NTANG3L]

Describe the issue The gateway is polling my postgres database and pulling the device data but it isn't appearing on thingsboard. ""2023-01-10 17:28:06" - |INFO| - [odbc_connector.py] - odbc_connector - __poll - 231 - [ODBC Connector] Polling iteration finished. Processed rows: current 11, total 93" How can I get the gateway to create new devices using the data? Am I misunderstanding the use of the gateway or is there something wrong with my setup?

odbc.txt tb_gateway.txt _thingsboard-gateway_logs.txt

Versions (please complete the following information):

• OS: Unraid 6.11.5
• Thingsboard IoT Gateway version Docker 3.2
• Python version 3.10.2

[samson0v]

Hi @S1L3NTANG3L, ensure that you have not reached the device limit in ThingsBoard or aren't rewriting already created devices.

[S1L3NTANG3L]

I've got two devices that I manually added and then the gateway device

[samson0v]

@S1L3NTANG3L, you have to configure deviceType as in the example below:

[S1L3NTANG3L]

@S1L3NTANG3L, you have to configure deviceType as in the example below: Chnage it from str(device_id) to entity_id or str(entity_id) throws a no reference error ""2023-01-11 07:07:02" - |WARNING| - [odbc_connector.py] - odbc_connector - process_row - 264 - [ODBC Connector] Failed to process database row: name 'entity_id' is not defined" Just device_id throws this error: ""2023-01-11 08:54:39" - |WARNING| - [odbc_connector.py] - odbc_connector - __process_row - 264 - [ODBC Connector] Failed to process database row: can only concatenate str (not "int") to str" Currently have this error but not sure where its from: ""2023-01-11 10:01:41" - |WARNING| - [odbc_connector.py] - odbc_connector - process_row - 264 - [ODBC Connector] Failed to process database row: 'type'"

[S1L3NTANG3L]

Used the postgres creation scipt and attributes_odbc.json to create the database and connection

[samson0v]

@S1L3NTANG3L why are you changing deviceName?

I wrote about deviceType, you have to simply add "type": "default" to device config section. I sent an example, it is not mean that you have to use exactly what I sent, just replace values with yours.

[S1L3NTANG3L]

Okay it appears I missed the ball somewhere, will you be able to help me with the odbc.json file if I send you a photo of my database tables?

[samson0v]

@S1L3NTANG3L, yes But before I recommend you read ODBC Connector's official documentation if you didn't read it.

[S1L3NTANG3L]

Okay I went through the odbc documentationa and the only section that is confusing me is the timeseries subsection in the device section, here is my new odbc.json file and the current database layout. There are two tables devices and telemetry, every device has a name(Gets used for identification and to name the device in thingsboard), a category and a id(Used to identify device in telemetry table), the telemetry table is pretty much the same as the attributes table from the example data,entity_id => identifies device, ts => is unix time, key => device attribute(temp,humidty, etc) and bool_v, str_v, long_v and dbl_v are all just to store the value of the key. Tables: New ODBC: odbc.txt

[samson0v]

@S1L3NTANG3L, so what is a question?

[S1L3NTANG3L]

Whether the new odbc config is setup correctly

[S1L3NTANG3L]

Started it up and getting one error ""2023-01-12 09:03:43" - |ERROR| - [tb_gateway_service.py] - tb_gateway_service - _connect_with_connectors - 609 - 'str' object has no attribute 'get'"

[samson0v]

@S1L3NTANG3L, send, please, your new config files

[S1L3NTANG3L]

@samson0v Here you go odbc.txt

[samson0v]

@S1L3NTANG3L, send, please, your tb_gateway.yaml config file.

[S1L3NTANG3L]

@samson0v Hi I'll send it in a few minutes

[S1L3NTANG3L]

@samson0v here you go tb_gateway.txt

[samson0v]

@S1L3NTANG3L, it seems that Gateway uses the wrong config files, please, make sure that you are using the right one because there is no other explanation for this error.

[S1L3NTANG3L]

Hi @samson0v I just checked and there is only one odbc.json file in the config folder so I'm not sure what it can be...

[S1L3NTANG3L]

""2023-01-17 08:21:53" - |ERROR| - [odbc_connector.py] - odbcconnector - run - 195 - [ODBC Connector] Error while polling database: ('42703', '[42703] E\x00R\x00R\x00O\x00R\x00:\x00 \x00c\x00o\x00l\x00u\x00m\x00n\x00 \x00d\x00e\x00v\x00i\x00c\x00e\x00s\x00.\x00d\x00e\x00v\x00\x00c\x00a\x00t\x00e\x00g\x00o\x00r\x00y\x00 \x00d\x00o\x00e\x00s\x00 \x00n\x00o\x00t\x00 \x00e\x00x\x00i\x00s\x00t\x00;\x00\n\x00E\x00r\x00r\x00o\x00r\x00 \x00w\x00h\x00i\x00l\x00e\x00 \x00p\x00r\x00e\x00p\x00a\x00r\x00i\x00n\x00g\x00 \x00p\x00a\x00r\x00a\x00m\x00e\x00t\x00e\x00r\x00s\x00 (1) (SQLExecDirectW)')" @samson0v Different error so progress I think, found a missing comma in the odbc.json file

[samson0v]

@S1L3NTANG3L, ok, after adding a comma, does the connector work well?

[S1L3NTANG3L]

No getting the above error, it goes into a infinite loop

[samson0v]

@S1L3NTANG3L, send, please, the complete log

[S1L3NTANG3L]

@samson0v You can ignore the top issue it was just a problem with the AND clause in my select statement, I think the issue in the config has to do with the device{name:} section I'm not using the python eval() I am feeding it the device name directly it just has to take it and forward it and the telemetry to thingsboard

[S1L3NTANG3L]

I have gone ahead and wrote my own api to sit between the database and thingsboard so I will not be continuing with the gateway. Thanks for all the help @samson0v