I have an app with a java backend that retrieves the contents of a file (PDF, XLSX, etc) to send to the front-end upon request.

Each file is stored as a single record in a database table, with a VARBINARY field holding the actual contents of the file as a blob.

Currently the app just retrieves one file at a time by calling a stored procedure that fetches the contents for a single file as a byte array.

This is generally how the stored procedure ‘doc_retrieve’ works to retrieve the contents of the VARBINARY field ‘content’ from the table ‘document_table’ using the primary key field ‘doc_id’

CREATE OR ALTER PROCEDURE [dbo].[doc_retrieve] 

    @DOC_ID                 INT

AS

    select  content 
    from    document_table  
    where   doc_id = @DOC_ID

The field ‘doc_id’ is a unique primary key, the stored procedure is always only going to return a single record.

The java code calls the stored procedure and stuffs the content of the file into a byte[] array to display to the user like so:

try (var statement = connection.prepareCall("{call dbo.doc_retrieve (?)}")) {
    statement.setObject(DOC_ID, 1234);
    ResultSet resultSet = statement.executeQuery();
    List<byte[]> contentList = new ArrayList<>();
    while (resultSet.next()) {
        contentList.add((byte[])resultSet.getObject(1));
    }
    if(contentList != null && !contentList.isEmpty()) {
        return contentList.get(0);
    }
    return null;
} catch (SQLServerException e) {
            
}

What I would like to do is have it return the contents of more than one file at a time.

For example, if the user wanted to return all PDF files, a new stored procedure would be created:

CREATE OR ALTER PROCEDURE [dbo].[doc_retrieve_by_file_extension] 

    @FILE_EXTENSION     VARCHAR(20)         

AS

    select  content 
    from    document_table  
    where   file_extension = @FILE_EXTENSION

and would be called in the java like so:

try (var statement = connection.prepareCall("{call dbo.doc_retrieve_by_file_extension (?)}")) {
    statement.setObject(FILE_EXTENSION, '.PDF');
    ResultSet resultSet = statement.executeQuery();

Then more than one file would be retrieved and displayed to the users.

Does anyone know if BCP the way to go?

Thanks much.