SQL Component parameter order?

[dunnlow]

I have an application that stores a POJO to a database.  I read the attribute values from a variety of sources (JMS and XML File).  I have been using a bean processor to construct the insert statement which I pass to my jdbc endpoint.  This has been working, but now I need to insert more complex (HTML) values into an oracle database.  The SQL component seems like is would be a good option, however, from my reading it looks like I need an ordered list that is directly mapped to the SQL parameters.  This seems terribly fragile -- that if database field order changes everything brakes.  Is there a smart way to handle this problem without adding that dependency on database field order?

Thanks for any thoughts,
-J

[dunnlow]

I wanted to add, I'm using Spring DSL routes.  

Also after some more thought, I'm thinking of two things to try:

1) come up with a way to create the SQL component parameter definition list on the fly based upon the order of the headers.

2) Create a converter for my POJO bean from Bean to List and put that pojo in the message body; hoping that Camel will use that converter to create the list.  The converter will be responsible for doing a DESCRIBE on the database and then creating the resulting List using the correct order.

Any thoughts?

[Claus Ibsen-2]

Hi

No it doesnt support a map so you can have a keyed based lookup.
It could possible be a good additon, to allow you to specify the key
name enclosed by # tokens, such as

So you can do something a like:
insert into foo (title, amount) values (#title#, #amount#)

Fell free to create a JIRA ticket in the issue tracker
http://camel.apache.org/support.html


An alternative is to look at MyBatis, its really good for these kind
of SQL mapping
http://camel.apache.org/mybatis
http://mybatis.org/


On Mon, Mar 5, 2012 at 5:28 PM, dunnlow <[hidden email]> wrote:

> I have an application that stores a POJO to a database.  I read the attribute
> values from a variety of sources (JMS and XML File).  I have been using a
> bean processor to construct the insert statement which I pass to my jdbc
> endpoint.  This has been working, but now I need to insert more complex
> (HTML) values into an oracle database.  The SQL component seems like is
> would be a good option, however, from my reading it looks like I need an
> ordered list that is directly mapped to the SQL parameters.  This seems
> terribly fragile -- that if database field order changes everything brakes.
> Is there a smart way to handle this problem without adding that dependency
> on database field order?
>
> Thanks for any thoughts,
> -J
>
> --
> View this message in context: http://camel.465427.n5.nabble.com/SQL-Component-parameter-order-tp5538064p5538064.html
> Sent from the Camel - Users mailing list archive at Nabble.com.

--
Claus Ibsen
-----------------
FuseSource
Email: [hidden email]
Web: http://fusesource.com
Twitter: davsclaus, fusenews
Blog: http://davsclaus.blogspot.com/
Author of Camel in Action: http://www.manning.com/ibsen/

[Łukasz Dywicki]

I think that support for named parameters is something we could provide. Another thing is to let users provide own implementation of PreparedStatementCallback to map more complex parameters. I use camel-sql and as you, have same problem with conversion to list..

Best regards,
Lukasz

Wiadomość napisana przez dunnlow w dniu 5 mar 2012, o godz. 18:15:

> I wanted to add, I'm using Spring DSL routes.  
>
> Also after some more thought, I'm thinking of two things to try:
>
> 1) come up with a way to create the SQL component parameter definition list
> on the fly based upon the order of the headers.
>
> 2) Create a converter for my POJO bean from Bean to List and put that pojo
> in the message body; hoping that Camel will use that converter to create the
> list.  The converter will be responsible for doing a DESCRIBE on the
> database and then creating the resulting List using the correct order.
>
> Any thoughts?
>
> --
> View this message in context: http://camel.465427.n5.nabble.com/SQL-Component-parameter-order-tp5538064p5538182.html
> Sent from the Camel - Users mailing list archive at Nabble.com.

[Claus Ibsen-2]

2012/3/9 Łukasz Dywicki <[hidden email]>:
> I think that support for named parameters is something we could provide. Another thing is to let users provide own implementation of PreparedStatementCallback to map more complex parameters. I use camel-sql and as you, have same problem with conversion to list..
>

Good ideas. Feel free to create a JIRA. And as always we love contributions.

> Best regards,
> Lukasz
>
> Wiadomość napisana przez dunnlow w dniu 5 mar 2012, o godz. 18:15:
>
>> I wanted to add, I'm using Spring DSL routes.
>>
>> Also after some more thought, I'm thinking of two things to try:
>>
>> 1) come up with a way to create the SQL component parameter definition list
>> on the fly based upon the order of the headers.
>>
>> 2) Create a converter for my POJO bean from Bean to List and put that pojo
>> in the message body; hoping that Camel will use that converter to create the
>> list.  The converter will be responsible for doing a DESCRIBE on the
>> database and then creating the resulting List using the correct order.
>>
>> Any thoughts?
>>
>> --
>> View this message in context: http://camel.465427.n5.nabble.com/SQL-Component-parameter-order-tp5538064p5538182.html
>> Sent from the Camel - Users mailing list archive at Nabble.com.
>

--
Claus Ibsen
-----------------
FuseSource
Email: [hidden email]
Web: http://fusesource.com
Twitter: davsclaus, fusenews
Blog: http://davsclaus.blogspot.com/
Author of Camel in Action: http://www.manning.com/ibsen/

[Łukasz Dywicki]

FYI, corresponding JIRA: CAMEL-5075.

Best regards,
Lukasz Dywicki
--
Code-House
http://code-house.org

Wiadomość napisana przez Claus Ibsen w dniu 9 mar 2012, o godz. 12:19:

> 2012/3/9 Łukasz Dywicki <[hidden email]>:
>> I think that support for named parameters is something we could provide. Another thing is to let users provide own implementation of PreparedStatementCallback to map more complex parameters. I use camel-sql and as you, have same problem with conversion to list..
>>
>
> Good ideas. Feel free to create a JIRA. And as always we love contributions.
>
>
>> Best regards,
>> Lukasz
>>
>> Wiadomość napisana przez dunnlow w dniu 5 mar 2012, o godz. 18:15:
>>
>>> I wanted to add, I'm using Spring DSL routes.
>>>
>>> Also after some more thought, I'm thinking of two things to try:
>>>
>>> 1) come up with a way to create the SQL component parameter definition list
>>> on the fly based upon the order of the headers.
>>>
>>> 2) Create a converter for my POJO bean from Bean to List and put that pojo
>>> in the message body; hoping that Camel will use that converter to create the
>>> list.  The converter will be responsible for doing a DESCRIBE on the
>>> database and then creating the resulting List using the correct order.
>>>
>>> Any thoughts?
>>>
>>> --
>>> View this message in context: http://camel.465427.n5.nabble.com/SQL-Component-parameter-order-tp5538064p5538182.html
>>> Sent from the Camel - Users mailing list archive at Nabble.com.
>>
>
>
>
> --
> Claus Ibsen
> -----------------
> FuseSource
> Email: [hidden email]
> Web: http://fusesource.com
> Twitter: davsclaus, fusenews
> Blog: http://davsclaus.blogspot.com/
> Author of Camel in Action: http://www.manning.com/ibsen/