Skip to content

NpgsqlBatch throws an error when NpgsqlBatchCommand parameter direction is output #5642

Description

@caglayandev

Hello Everyone!

After the recommendation given in issue #5641, I switched to use NpgsqlBatch to keep my trips to database single. However, I am having a different error this time. When my batch is prepared and application executes code line await batch.ExecuteNonQueryAsync();, it throws below exception.

System.NotSupportedException: 'Output parameters are not supported in positional mode (unless used with CommandType.StoredProcedure)'

I do not think I am doing a mistake which would trigger this exception since I explicity state that the command is stored procedure. Could someone please point out where am I making mistake or is it an actual issue? Thanks in advance for your answer.

Best regards.

Application code:

public async Task<long> AddAsync(string username, string email, byte[] passwordHash, byte[] passwordSalt)
{
	var currentInstant = _clock.GetCurrentInstant();

	await using var connection = await _npgsqlDataSource.OpenConnectionAsync();
	await using var transaction = await connection.BeginTransactionAsync(IsolationLevel.Serializable);
	await using var batch = new NpgsqlBatch(connection, transaction)
	{
		BatchCommands =
		{
			new("public.usp_user_create")
			{
				CommandType = CommandType.StoredProcedure,
				Parameters =
				{
					new() { Value = username },
					new() { Value = email },
					new() { Value = passwordHash },
					new() { Value = passwordSalt },
					new() { Value = EmailConfirmationEncryptor.Encrypt(email + currentInstant.ToString()) },
					new() { Value = currentInstant.Plus(Duration.FromDays(7)) },
					new() { Value = currentInstant },
					new() { DbType = DbType.Int64, Direction = ParameterDirection.Output }
				}
			},
			new ("COMMIT")
		}
	};

	await batch.ExecuteNonQueryAsync();
	return (long)batch.BatchCommands[0].Parameters[7].Value;
}

Stored Procedure:

CREATE OR REPLACE PROCEDURE public.usp_user_create
(
    p_username TEXT,
    p_email TEXT,
    p_password_hash BYTEA,
    p_password_salt BYTEA,
    p_hash UUID,
    p_expiration_timestamp TIMESTAMP(6) WITH TIME ZONE,
    p_create_timestamp TIMESTAMP(6) WITH TIME ZONE,
    OUT p_user_id BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO public."user" (username, email, password_hash, password_salt, is_activated_user, create_timestamp, update_timestamp)
    VALUES (p_username, p_email, p_password_hash, p_password_salt, false, p_create_timestamp, p_create_timestamp)
    RETURNING id INTO p_user_id;

    INSERT INTO public.user_email_confirmation (id, hash, expiration_timestamp, create_timestamp, update_timestamp)
    VALUES (p_user_id, p_hash, p_expiration_timestamp, p_create_timestamp, p_create_timestamp);
END;
$$;

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

Fields

No fields configured for Bug.

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions