Cannot use SimpleJdbcInsert with catalog name

[jesudornenkrone]

I use spring 6.1.3 with MSSQL.

My code:

orderInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("tbOrder").withCatalogName("CamWeso").withSchemaName("dbo").usingGeneratedKeyColumns("OrderID");

orderId = orderInsert.executeAndReturnKey(newOrderParams);

If I use this code, it generate the following SQL:

INSERT INTO dbo.tbOrder (CustomerID, OrderNumber, OrderTypeID, StatusID, DivisionID, AssigneeID, ReceivedAt, CreatedBy, CreatedAt, OwnerID, IsPortationOrder, WorkflowStateIndex) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

But I expected it to be:

INSERT INTO CamWeso.dbo.tbOrder ...

I took a look at TableMetaDataContext.createInsertString which uses schemaName and tableName but doesn't use catalogName.

If I use SimpleJdbcCall with withCatalogName, it works.

[snicoll]

Thanks for the report. We usually just use the schema and catalog names for metadata lookup, but I can see how that looks inconsistent. However, it's far from being obvious as some database may not support specifying a catalog this way.

Why is the catalog necessary for MSSQL server here?

[jesudornenkrone]

We have one SQL Server instance but with two separate databases (designed as different catalogs).
One is the applications database and one is a legacy database, we just want to keep in sync, until we get rid of it.

I could create two JDBC connections and use them, but that would involve distributed transactions, wouldn't it? So using the same JDBC connection to write into both catalogs, seemed like a "smart/simple" idea.

[snicoll]

That makes sense. If you get a chance to test 6.1.4-SNAPSHOT available from repo.spring.io/snapshots shortly, that would be very much appreciated.

[jesudornenkrone]

Thank you very much, I tested with spring-jdbc-6.1.4-20240129.153252-63.jar and it works.