Question: retrieving result-code and output -- howto?

[sdonovanuk]

Greetings. Please consider the following T/SQL:

exec xp_cmdshell 'robocopy path1 path2 file'

That call actually has two pieces of output: (i) the result-code from robocopy, and (ii) output -- which is the stdout from the call, returned as 1x column.

Question/Problem: I cannot find JDBC calls (or Spring's JdbcTemplate) that allow me to extract both pieces of data.

You can either (i) use a CallableStatement:

jdbcTemplate.execute("{? = call xp_cmdshell(?)}",
    (CallableStatement cs) -> {
        cs.registerOutParameter(1, Types.INTEGER);
        cs.setString(2, "robocopy " +
            filenames.getSourcePath() + " " +
            filenames.getTargetPath() + " " +
            filenames.getSourceFile() + " /mt");
        cs.execute();
        final var returnCode = cs.getInt(1);
        if (returnCode != 0 && returnCode != 1) {
            throw new ApplicationException("robocopy failed (code " + returnCode + ")");
        }
        return 0;
    });

. . . . to get the return-code (and any output parameter(s)), or, (ii) you can issue a regular query (e.g. jdbcTemplate.queryForList(..)) to get the stdout.

I cannot find a way of retrieve both sets of data. It's possible I haven't looked hard enough, or it's also possible the JDBC spec doesn't deal with this case. I'm not sure what the correct answer would be: multiple result-sets?

Any ideas? Thank you!

[barryw-mssql]

Hi sdonvanuk,

I would proceed with your option #1 to get the return code as you have currently identified in your code example. The stdout is a little more problematic as JDBC does not recognize stdout. stdout is an OS feature and so the best way to capture that data is to modify your T-SQL to use the Windows command prompt redirection operator (i.e. ">") to redirect to a file. Once the CallableStatement completes, the OS should have completed writing stdout to a file which you can read into your application like any other file:

    EXEC xp_cmdshell 'robocopy path1 path2 file > robocopy_out.txt';

It should be noted that ROBOCOPY has it's own logging to file feature that you can use in addition to stdout or to complement the stdout log file. You can find details here:

   https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/robocopy

Please let us know if this helps.

Thx,
Barry

[Jeffery-Wasty]

Hi @sdonovanuk,

Please let us know if you have any further questions. If not, we will move forward with closing this issue.

[sdonovanuk]

Greetings. Sorry for the slow response. I was a little confused by the comment "as JDBC does not recognize stdout". Again, if I use jdbcTemplate.queryForList(..) or similar, the result-set comes back (1x column, called "output"). What @barryw-mssql gave was a workaround. What I was hoping to find out was: is there a variant of some JDBC method that doesn't require the workaround, and can expose both the (i) return-code, and (ii) the result-set (with the "output" column)?

I should add: I'm 100% aware <cough, cough> that we shouldn't be using xp_cmdshell, and we're working to fix that. However, the challenge is that each call to xp_cmdshell takes, minimum, around 120-150ms, so we're trying to avoid round-trips to retrieve results.

Thank you!

[barryw-mssql]

According to the docs for xp_cmdshell the data directed to stdout is returned in the stored proc result set (https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver16#arguments). In the example code you provided you did not attempt to read the callable statement result set. Please give this a try.

We will be closing this question now.