SQL Server Service Broker support

[gdupontf]

Expected Behavior

From a high-level standpoint, it seems like an adapters and gateways using SQL Server Service Broker statements would seem worthwhile, albeit with the caveat of being implementation-specific.

Current Behavior

No direct support for SSSB.

Context

This is mainly to cover the specific use case of SQL Server Service Brokers paired with queues. Of course this issue can expand to more RDBMS and their applicable implementations. This could even cover the generic case of queue tables (with performance concerns left to the user).

[artembilan]

I cannot find too much information about this in the Internet: https://stackoverflow.com/questions/44911123/how-to-use-sql-server-service-broker-with-java. However if you see how this could be implemented with plain JDBC, based on those specific command, you are more than welcome to contribute such a feature .

We have already a general purpose support for DB-based queues: https://docs.spring.io/spring-integration/reference/jdbc/message-store.html#jdbc-message-store-channels.
So, no need to look into that direction.

And we also have a PostgreSQL channel implementation for its notify and listen statements: https://docs.spring.io/spring-integration/reference/jdbc/message-store.html#postgresql-push. And from there you can borrow ideas.

[gdupontf]

It's straightforward enough and well-documented from what the docs describe.

[artembilan]

Yes, saw that doc, but it still does not mandate how to use that stuff from Java or at least JDBC. It looks more like Transact SQL which is for stored procedures in database by itself.
So, what are your ideas how this could be implemented in Java?

[gdupontf]

Sounds like that's where I'll have to put some effort into a POC.

[gdupontf]

Here's some quick afternoon's worth of a PoC. Don't mind some of the hardcoded stuff for now.

Also, seeing some of the minimal work needed to setup this makes me think there may be some value to expose most of the initialization scripts as code-based initializers.
Is that worth it?
Think akin to Flyway's Java-based migrations.
Heck, flyway support, maybe in its own module, might be nice in itself.

[artembilan]

@gdupontf ,

thank you for sharing that! Really good and readable code.
However those connection.prepareStatement() don't provide any gain over existing feature with JDBC-backed QueueChannel.
Sure! On the SQL Server side that might be a queue instead of table, but from our perspective we just still use plain polling JDBC, no any constantly listening connection like in case of PostgreSQL.
More over, as I said before: looks like those statements (and the whole service broker configuration) should be hidden behind stored procedures on the server side.
And for the purpose we already have support in Spring Integration: https://docs.spring.io/spring-integration/reference/jdbc/stored-procedures.html