Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

OUT parameters for Stored Procedure Calls #1515

Open
louis77 opened this issue Nov 26, 2023 · 1 comment
Open

OUT parameters for Stored Procedure Calls #1515

louis77 opened this issue Nov 26, 2023 · 1 comment

Comments

@louis77
Copy link

louis77 commented Nov 26, 2023

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
Copy link

YK-peng commented Aug 19, 2024

good

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants