1

I'm having trouble to pass a list of string I'm getting back from my bean to my sql-component query to make a call to the database.

<bean ref="fo" method="transformTo(${body})" />

So in this upper line of code I'm taking data from the body that is an xml and transform it to json.

<bean ref="fot" method="getOTs(${body})" />

Then I'm extracting the part I want from the json and return a list of string (method signature) :

public List<String> getOTs(String jsonOTs)

Now the part that isn't working (I'm getting that one parameter is expected but there are a couple each time)

<to uri="sql:insert into dbo.table_example (OT) VALUES :#body;"/>

My goal is quite simple, retrieving a list of string from my bean (working) and making and an insert into query. I have only one parameter but multiple values. Example:

INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);

Example taken from here

Pouissante
  • 56
  • 1
  • 5
  • 25

1 Answers1

1

Bulk insert

For a bulk insert, you need to set the query parameter batch to true, this way, Camel will understand that you want to insert several rows in one batch.

Here is the corresponding to endpoint in your case:

<to uri="sql:insert into dbo.table_example (OT) VALUES (#)?batch=true"/>

Miscellaneous remarks

Actually, for all the use cases that you listed above, you have no need to explicitly refer to the body.

Indeed, in the case of a bean, you could only specify the method to invoke, Camel is able to inject the body as a parameter of your method and automatically converts it into the expected type which is String in your case.

Refers to https://camel.apache.org/manual/bean-binding.html#_parameter_binding for more details.

Regarding the SQL producer, assuming that you did not change the default configuration, the proper way is to rather use the placeholder that is # by default, Camel will automatically use the content of the body as parameters of the underlying PreparedStatement.

So you should retry with:

<to uri="sql:insert into dbo.table_example (OT) VALUES (#)"/>

If you really want to explicitly refer to the body in your query, you can rather use :#${body} as next:

<to uri="sql:insert into dbo.table_example (OT) VALUES (:#${body})"/>

Misuse of named parameter

If you only use #body as you did, Camel interprets it as a named parameter so it will try to get the value from the body if it is a map by getting the value of the key body otherwise it will try to get the value of the header body but in your case, there are no such values, therefore, you end up with an error of type

Cannot find key [body] in message body or headers to use when setting named
parameter in query [insert into developers (name) values :?body;] on the exchange
Nicolas Filotto
  • 43,537
  • 11
  • 94
  • 122
  • Hello, thank your for your answers. In both cases I'm getting an error back : - Number of parameters mismatch. Expected: 1, was: 2 (I have 2 items in my list of string in this case) - Conversion from UNKNOWN to UNKNOWN is unsupported Thank you for your answers – Pouissante Oct 19 '22 at 07:46
  • My body contains the result of the bean with ref "fot" in my example. This means it gets a List of strings. It is not a fixed size I am returning, I'm querying a database and in this example, I have been given two Strings for my List. – Pouissante Oct 19 '22 at 11:55