How to use a database publisher (insert data in database)

iPaaS Documentation Connection Guides
1

In order to publish data to ​a database, you can use Database Publisher in ​an outgoing configuration.
Below are some samples ​you can use.

​
In order to use the Database Publisher, you need to restructure your data such as the below format.

{ 
  "table": {
    "name": "sample" 
  }, 
  "fields": { 
    "city": "Groningen", 
    "name": "Alumio"
  }
}

The above data will result in the below query to the database server.

Update

In case you need to update a row in the database, you need to restructure your data such as the below format.

{ 
  "table": { 
    "name": "sample", 
    "primaryKey": "id" 
  }, 
  "fields": { 
    "id": 1, 
    "city": "Groningen, NL", 
    "name": "Alumio, B.V" 
  } 
}

The output query of the about JSON data is as below.

This payload can then be sent to a database publisher (outgoing configuration) using a route.

Search terms: sql insert mysql insert

1 Like
2

Hi @Roland , when sending the data i got this error message

Screenshot 2023-05-02 at 04.45.31
Screenshot 2023-05-02 at 04.45.31662×610 24.1 KB

3

Hi @abubakar

I sent you a message regarding your question.

Thank you!

4

Hi @abubakar

Thank you for providing me with the details on DM. We will check the issue you are experiencing and will come back to you with an update.

5

Hi @abubakar

We set the Database publisher deprecated and replaced it with the new Database Batch Publisher. You can use the new publisher such as below.

image
image1039×472 23.3 KB

You just need to set the insert data in JSON format, such as below.

{
  "Name": "ENG 409",
  "Dept_Name": "English",
  "Description": "Creative Writing II",
  "Credit_Hours": "3"
}

The above entity will be sent with the below query in the database.

INSERT INTO Course (Name, Dept_Name, Description, Credit_Hours) VALUES(:Name, :Dept_Name, :Description, :Credit_Hours)

Please give it a try and let us know if you find any problems.

We are going to create a new guide or update this topic to explain how to use the new publisher.

1 Like
6

Thank you @Gugi , this works and fine.

7

I have ran the configuration and task is created but not finished and i cannot identify the error message, please can you help with this

cc: @ahmad

8

Hi @abubakar @ahmad

We checked the tasks and it tried to execute the logged query in a Database transformer and failed. The Database transformer throws an error where a column doesn’t exist in the table.

SQLSTATE[42S22]: Column not found: 0 [Zen][ODBC Client Interface][LNA][Zen][SQL Engine]Invalid column name:'SortField'. (SQLPrepare[0] at /build/php7.4-Z31pLE/php7.4-7.4.3/ext/pdo_odbc/odbc_driver.c:204)

Regardless of the query error, it seems that the Database batch publisher doesn’t show the same error in the logs. Therefore, we will investigate this further and will inform you once we have an update.

9

Hi @abubakar

We found out that the route’s option “Activate retrying of failed tasks” makes the error that is thrown in the outgoing process doesn’t appear in the task. If a task failed and is marked as RETRY, the error log doesn’t appear. When it reaches the maximum number of retry attempts and is marked as FAILED, the error log will then appears.

We have passed this information on to the corresponding team for further investigation. We will let you know once we have an update. As for the workaround, you can try disabling the option “Activate retrying of failed tasks” in the route, so that you can immediately see the error log when a task failed.

1 Like