My Spring Boot Java application consumes a SingleStore Named Stored Procedure using MariaDB JDBC driver( I have also observed the same behavior with the SingleStore jdbc driver as well). The NSP has an json input and produces a resultset with 30 columns that is the result of a complex select from multiple tables. The NSP also has a return value of data type Query that consists of 3 columns. I should also mention that I have no possibility to change the NSP code as it is maintained by another team. I am using Spring Data JDBC to consume the NSP and I am able to get the resultset with no issue. The issue is that I can’t seem to get the return value as I have no way of defining a JDBC Type of Query am I missing something? Also is there an SQL query I can write to retrieve the return values I have experimented with { call nsp(?)}, echo nsp(?) {? = call nsp(?)}. Thank you
You may be able to write an anon code block that gets the QUERY type value (QTV) returned from the SP and then does an ECHO SELECT from the QTV.
Hi Hanson,
Thank you for the suggestion; I was finally able to get both QTV return value and resultset by using low level SQL PreparedStatement leveraging pst.getResult for the resultSet and pst.getMoreReults followed by pst.geResults and using ECHO instead of CALL. It would be nice to have full JPA support for these types of use cases. Could you provide a sample query for what you suggested using an anonymous block?
Hi @jc_mtlstatic,
Not sure I understand your issue correctly, but would be nice to see your procedure example and code. I guess with Spring JPA if NamedStoredProcedureQuery doesn’t work correctly you can always try to use native query support and call procedure directly if I’m not wrong.
As for metadata, you can also fetch it by calling jdbc directly, like:
ResultSet rs = connection.getMetaData().getProcedures("catalogName", null, "procName");
rs.next();
rs.getMetaData().getColumnType(1)