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;
$$;
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.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:
Stored Procedure: