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.
Comment From: 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?
Comment From: 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.
Comment From: 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.
Comment From: jesudornenkrone
Thank you very much, I tested with spring-jdbc-6.1.4-20240129.153252-63.jar and it works.