OUT parameters for Stored Procedure Calls

[louis77]

Since issue #656 was closed without progress, I was trying to get OUT parameters for stored procedure calls working. This is what I did:

1. Add clientPSMultiResults to writeHandshakeResponsePacket in packets.go:

func (mc *mysqlConn) writeHandshakeResponsePacket(authResp []byte, plugin string) error {
	// Adjust client flags based on server support
	clientFlags := clientProtocol41 |
		clientSecureConn |
		clientLongPassword |
		clientTransactions |
		clientLocalFiles |
		clientPluginAuth | 
		clientMultiResults |
		clientPSMultiResults |     // Tell MySQL that we want OUT Parameters as a separate result set
		clientConnectAttrs |
		mc.flags&clientLongFlag

What happens then is that MySQL sends a separate result set with one row and each OUT parameter as a column in order as they are defined in the Procedure signature. Since support for multiple result sets is already implemented, I could do an example implementation of how to call a stored procedure with in and out parameters:

func CallGet(funcName string, params ...any) ([]byte, error) {
	outParams := []sql.Out{}
	placeholders := make([]string, len(params))
	allParams := make([]any, len(params))

	for idx, param := range params {
		pout, ok := param.(sql.Out)
		if ok {
			outParams = append(outParams, pout)
			if pout.In {
				allParams[idx] = pout.Dest
			} else {
				allParams[idx] = nil
			}
		} else {
			allParams[idx] = param
		}
		placeholders[idx] = "?"
	}

	var result []byte

	rows, err := Conn.Query(fmt.Sprintf("CALL %s(%s)", funcName, strings.Join(placeholders, ",")), allParams...)
	if err != nil {
		return nil, fmt.Errorf("cannot query rows: %w", err)
	}

	// Scan result row
	rows.Next()
	if err := rows.Scan(&result); err != nil {
		return nil, fmt.Errorf("unable to scan row: %v", err)
	}

        // Scan out params
	if len(outParams) > 0 {
		outDests := make([]any, len(outParams))
		for idx, o := range outParams {
			outDests[idx] = o.Dest
		}

		if !rows.NextResultSet() {
			return nil, fmt.Errorf("too many out parameters defined")
		}
		if !rows.Next() {
			return nil, fmt.Errorf("unable to move to out params")
		}
		if err := rows.Scan(outDests...); err != nil {
			return nil, fmt.Errorf("unable to scan rows: %w", err)

		}
	}

	return result, nil
}

And I can simply do:

var myOutValue int

result, err := CallGet("add_one", 1, sql.Out{ Dest: &myOutValue, In: false })
// myOutValue will now be 2

given the following stored procedure:

CREATE PROCEDURE add_one (
	IN v_val INT,
	OUT v_result INT)
    DETERMINISTIC
BEGIN
        # set out param
        SELECT v_val + 1 INTO v_result;

        # plain old result
        SELECT 'hello world';
END

The upside is, that with a single line of code change it will be possible to support Out params, but it still has to be parsed on the side of the using application. I'm sure with a little more research it could be implemented as part of the driver package to do the work of my example CallGet, since MySQL is flagging the result set with SERVER_PS_OUT_PARAMS:

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_command_phase_sp.html#sect_protocol_command_phase_sp_multi_resultset_out_params

I'm sure it can be done if there is interest in this from anyone?

[YK-peng]

good