Issue with Calling Stored Procedure on Babelfish Aurora PostgreSQL with SQL Server Compatibility

Question

Issue with Calling Stored Procedure on Babelfish Aurora PostgreSQL with SQL Server Compatibility

Problem Description

I'm encountering an issue when trying to execute a stored procedure using a Babelfish Aurora PostgreSQL database with SQL Server compatibility. The stored procedure call works fine on a regular SQL Server connection, but it throws an exception on Babelfish. Specifically, the command is:

exec sp_sproc_columns @procedure_qualifier=DivDB, @procedure_owner=dbo, @procedure_name=spProcName, @ODBCVer=3, @fUsePattern=0

I'm using mssql-jdbc-12.2.0.jre8 and giving the below code from this library.

The error message I'm getting on Babelfish is:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: column "divdb" does not exist ...

More additional information:

2023-09-08 13:24:23.369 DEBUG 39492 --- [   scheduling-1] c.m.s.jdbc.internals.SQLServerStatement  : SQLServerStatement:41 Executing (not server cursor) exec sp_sproc_columns @procedure_qualifier=DivDB, @procedure_owner=dbo, @procedure_name=spProcessPricesInBulk , @ODBCVer=3, @fUsePattern=0
2023-09-08 13:24:23.570 DEBUG 39492 --- [   scheduling-1] c.m.s.jdbc.internals.SQLServerException  : *** SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: column "divdb" does not exist Msg 33557097, Level 16, State 1, column "divdb" does not exist
2023-09-08 13:24:23.571 DEBUG 39492 --- [   scheduling-1] c.m.s.jdbc.internals.SQLServerException  : *** SQLException:SQLServerCallableStatement:40 com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: column "divdb" does not exist com.microsoft.sqlserver.jdbc.SQLServerException: column "divdb" does not exist

And these

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: column "divdb" does not exist
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:237)
    at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.findColumn(SQLServerCallableStatement.java:1360)
    at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setStructured(SQLServerCallableStatement.java:2183)

SQL Server Query output:

BabelFish Connection Execution:

This is the method in our code which is trying to set the TableType as proc Input.

private CallableStatementCreator getCollableStatementCreator(String procName, Map<String, ?> params,
                                                                 List<TableParam> tableParams) {

        CallableStatementCreator callStCreator = new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {

// procName = 'DivDb.dbo.procName'                

                CallableStatement stmnt = con.prepareCall("exec " + procName);
                SQLServerCallableStatement callableStatement = stmnt.unwrap(SQLServerCallableStatement.class);


                if (params != null) {
                    for (String key : params.keySet()) {
                        callableStatement.setObject(key, params.get(key), DbUtils.determineParameterType(params.get(key)));
                    }
                }
                try {
                    for (TableParam tableParam : tableParams) {

                        callableStatement.setStructured(tableParam.getTvpName(), tableParam.getTvpType(),
                                DbUtils.getSqlServerDataTable(tableParam));
                    }
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }

                return stmnt;
            }
        };
        return callStCreator;
    }

I deeper down and debugged the library code, especially findColumn method, that is throwing the exception. Here is the code

@Override
    public final void setStructured(String parameterName, String tvpName,
            SQLServerDataTable tvpDataTable) throws SQLServerException {
        tvpName = getTVPNameIfNull(findColumn(parameterName), tvpName);
........
        
    }

This is the whole code block that actually trying to find the column into internal database.

SQLServerCallableStatement::findColumn Method ( library mssql-jdbc-12.2.0.jre8)

try (SQLServerStatement s = (SQLServerStatement) connection.createStatement()) {
                // Note we are concatenating the information from the passed in sql, not any arguments provided by the
                // user
                // if the user can execute the sql, any fragments of it is potentially executed via the meta data call
                // through injection
                // is not a security issue.

                ThreePartName threePartName = ThreePartName.parse(procedureName);
                StringBuilder metaQuery = new StringBuilder("exec sp_sproc_columns ");
                if (null != threePartName.getDatabasePart()) {
                    metaQuery.append("@procedure_qualifier=");
                    metaQuery.append(threePartName.getDatabasePart());
                    metaQuery.append(", ");
                }
                if (null != threePartName.getOwnerPart()) {
                    metaQuery.append("@procedure_owner=");
                    metaQuery.append(threePartName.getOwnerPart());
                    metaQuery.append(", ");
                }
                if (null != threePartName.getProcedurePart()) {
                    // we should always have a procedure name part
                    metaQuery.append("@procedure_name=");
                    metaQuery.append(threePartName.getProcedurePart());
                    metaQuery.append(" , @ODBCVer=3, @fUsePattern=0");
                } else {
                    // This should rarely happen, this will only happen if we can not find the stored procedure name
                    // invalidly formatted call syntax.
                    MessageFormat form = new MessageFormat(
                            SQLServerException.getErrString("R_parameterNotDefinedForProcedure"));
                    Object[] msgArgs = {columnName, ""};
                    SQLServerException.makeFromDriverError(connection, this, form.format(msgArgs), SQLSTATE_07009,
                            false);
                }

                try (ResultSet rs = s.executeQueryInternal(metaQuery.toString())) {
                    parameterNames = new HashMap<>();
                    insensitiveParameterNames = new TreeMap<>(String.CASE_INSENSITIVE_ORDER);
                    int columnIndex = 0;
                    while (rs.next()) {
                        String p = rs.getString(4).trim();
                        parameterNames.put(p, columnIndex);
                        insensitiveParameterNames.put(p, columnIndex++);
                    }
                }
            } catch (SQLException e) {
                SQLServerException.makeFromDriverError(connection, this, e.toString(), null, false);
            }

This line, tries to execute the SQL query

try (ResultSet rs = s.executeQueryInternal(metaQuery.toString()))

In my opinion, the code line metaQuery.append(threePartName.getDatabasePart()); Should have been placed it wisely.

or the method ThreePartName threePartName = ThreePartName.parse(procedureName); should have been parsed it more accurately.

By the way one more question,

In case I'm using Babelfish, Do I need to use PostgressDriver? or SqlServer Driver is fine?

I'm using Spring boot, Jdbc, Sql Server Java 8

spring.datasource.jdbc-url=jdbc:sqlserver://DB-URL;databaseName=DivDB;appName=XXXXX;allowMultiQueries=true;encrypt=true;trustServerCertificate=true;
spring.datasource.username=*****
spring.datasource.password=DivaDev*321
spring.datasource.sqlserver.schema=dbo

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.queryTimeout=300
spring.datasource.test-on-borrow=true
spring.datasource.validation-query=SELECT 1

Answer 1

Unquoted string parameters are a feature of T-SQL but not currently supported by Babelfish (try sp_helpdb master). Currently such unquoted strings are parsed as identifiers by Babelfish, which is why you get the 'column does not exist' error. The same is true for unquoted parameter defaults in CREATE PROCEDURE/CREATE FUNCTION. Support for such unquoted string parameters is currently being worked on. It will not be available in Babelfish v.3.3.0, but hopeful