Procedure name is appended with delimitter ;1

[vishal98]

Driver version or jar name

mssql-jdbc-6.2.1.jre8

SQL Server version

Azure Sql 13

Client operating system

windows

Java/JVM version

Example: java version "1.8.0_65

Table schema

Problem description

Procedure schema name contain delimitter like ;1 with procedure name This is giving issue while fetching param using procedure name. Following error is coming with jar org.springframework.dao.InvalidDataAccessApiUsageException: Unable to determine the correct call signature - no procedure/function/signature for 'uspGetAllVideosByUsername;1'

Expected behavior and actual behavior

We should be able to fetch procedurename without any delimited

Repro code

connection = datasource.getConnection();
        long currTs = System.currentTimeMillis();
        DatabaseMetaData metaData = connection.getMetaData();
        log.debug("Metadata call in " + (System.currentTimeMillis() - currTs) + " ms");
        currTs = System.currentTimeMillis();
        ResultSet tables = metaData.getProcedures(connection.getCatalog(), getSchemaString(), null);
        log.debug("GetProcedures call in " + (System.currentTimeMillis() - currTs) + " ms");
        String procedureName=tables.getString(3));

[ulvii]

Hi @vishal98,

;number is an optional integer that is used to group procedures of the same name. You can have different stored procedures with the same name, differentiated by numbers, wich can be dropped together at once. Please read more about numbered procedures here to have an idea about the delimiter.

You are probably wondering why your application returns ;number in the procedure name when you are not using numbered procedures. When your application calls getProcedures(catalog, schemaPattern, procedureNamePattern), the JDBC driver executes sp_stored_procedures catalog, schemaPattern, procedureNamePattern on SQL Server and returns the result set as it is. Even if you are not using numbered procedures, sp_stored_procedures appends ;1 to the procedure name, because uspGetAllVideosByUsername is treated the same as uspGetAllVideosByUsername;1. Try executing sp_stored_procedures using SSMS.

As a workaround, you can safely add the following lines to your application:

if (procName.endsWith(";1")) {
    procName = procName.substring(0, name.length() - 2);
}

Please also note that, numbered procedures are deprecated starting from SQL Server 2017 (See this page).

I will keep the issue open for a few more days in case you have questions.

[ajlam]

Closing due to inactivity. @vishal98, please feel free to reopen if you have more questions.

[sualeh]

@ulvii @ajlam @cheenamalhotra - please reopen this issue. This issue is not about how "sp_procedures" works, but how the JDBC "DatabaseMetaData.getProcedures" works. My suggestion is that you provide the procedure name without numbering in the PROCEDURE_NAME column, and the numbered procedure name in the SPECIFIC_NAME column.

See https://github.com/schemacrawler/SchemaCrawler/issues/470

[mletterle]

Bump, this should really be reopened and addressed.

[sualeh]

Bump, this should really be reopened and addressed.

Please reconsider, reopen, and address.

[Jeffery-Wasty]

We'll take another look at this.

[sualeh]

@Jeffery-Wasty The JDBC DatabaseMetaData.getProcedures method returns PROCEDURE_NAME and SPECIFIC_NAME. Let us say you have a stored procedure called New_Publisher, you can return "PROCEDURE_NAME=New_Publisher" and "SPECIFIC_NAME=New_Publisher;1", and handle overloaded stored procedures with different parameters. Currently, the driver does not return SPECIFIC_NAME.

[David-Engel]

I've been thinking about this one.

If we change the driver to return function names without the numbered procedure identifier, we end support for numbered procedures in the driver. This would be a breaking driver change and would only happen on a major version bump. Even moving the numbered identifier to the SPECIFIC_NAME field would be a breaking change.

NOTE: Deprecated by SQL Server does NOT mean unsupported.

As long as you are using JDBC DatabaseMetadata APIs to gather information about database objects, numbered procedures doesn't cause any issues. But as soon as you start running your own database queries to gather that information, you must ensure you support database-specific features like numbered procedures in your queries or you can run into issues like this one.

This appears to be the case in https://github.com/schemacrawler/SchemaCrawler/issues/470. A custom query to retrieve additional database metadata based on the info from DatabaseMetaData.getProcedures doesn't support numbered procedures. So the numbered identifier name doesn't match.

This is not a driver error.

That said, the driver could expose an option, like supportNumberedProcedures, that would change how the driver queries database metadata. This wouldn't be an insignificant amount of work. Most of the metadata procedures call simple SQL system stored procedures built specifically for driver metadata queries. This is basically asking to write new queries to something different that works across all supported SQL Server versions. It would be a fairly low priority in the scope of things. This change wouldn't be limited to getProcedures(). At the very least it's getProcedures() and getProcedureColumns(). Investigation would need to be done to see if any other metadata APIs would be affected. Also, metadata queries need to work across SQL Server (multiple versions), Azure SQL DB, Azure Synapse Analytics, and other SQL endpoints, so writing new queries isn't a simple task.

Since this issue will resolve itself once SQL Server actually drops support for numbered procedures, that feels like a lot of work for a relatively low priority issue.

[sualeh]

If we change the driver to return function names without the numbered procedure identifier, we end support for numbered procedures in the driver.

Not if you include the numbered procedure identifier in SPECIFIC_NAME.

This would be a breaking driver change and would only happen on a major version bump. Even moving the numbered identifier to the SPECIFIC_NAME field would be a breaking change.

What is the resistance to a major version bump? It seems that this would be a relatively easy code change to make, and would align to what is returned in INFORMATION_SCHEMA.

[sualeh]

Ah - I get it - the next major version is 13! :wink:

[David-Engel]

What is the resistance to a major version bump?

Bumping the major version implies we are introducing a breaking change. We don't make breaking changes lightly.

It seems that this would be a relatively easy code change to make, and would align to what is returned in INFORMATION_SCHEMA.

You seem to be saying you just want us to ignore users who are using numbered procedures and break them. That's not going to happen.

As I said, this isn't as simple as it seems. We don't want to break users who use numbered procedures. INFORMATION_SCHEMA doesn't expose numbered procedures correctly. We would have to write new metadata queries for any APIs affected in order to "run without support for numbered procedures". We would maintain a logic path that continued to support numbered procedures. This implies additional test paths and test scenarios we would need to cover. There may be other things we don't know about that could be affected by moving to a different metadata query across all supported database types. Like I said, it's not such a simple change and the new code would become pointless when SQL Server actually removes support for numbered procedures since the existing queries (presumably) would stop returning ;1 at that point.