PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL stored procedure fails with dynamic parameters for date in Dapper

I’m using PostgreSQL with a .NET project, and I’m facing an issue executing a stored procedure through Dapper when using dynamic parameters to pass a DATE parameter.

The stored procedure is defined as follows:

CREATE OR REPLACE PROCEDURE simplemenuproc(
    appcode IN VARCHAR, 
    myDtTm DATE, 
    ref1 INOUT REFCURSOR
)
LANGUAGE plpgsql
AS $BODY$
-- Procedure body
$BODY$;

Here’s the C# code I’m using to call it:

NpgsqlTransaction tran = connection.BeginTransaction();
var parameters = new DynamicParameters();
parameters.Add("appcode", "PPM", direction: ParameterDirection.Input);
parameters.Add("myDtTm", DateOnly.Parse(DateTime.UtcNow.ToString("yyyy-MM-dd")), DbType.Date, direction: ParameterDirection.Input);
parameters.Add("ref1", direction: ParameterDirection.InputOutput);

// Execute the stored procedure
await connection.ExecuteAsync(storedProcedureName, parameters, commandType: CommandType.StoredProcedure);

When I try to execute this, I get the following error:

42883: procedure simplemenuproc(appcode => text, myDtTm => date, ref1 => unknown) does not exist

What I’ve tried

Changing how the date parameter is passed:

parameters.Add("myDtTm", DateTime.UtcNow.Date, direction: ParameterDirection.Input);

This resulted in:

42883: procedure simplemenuproc(appcode => text, myDtTm => timestamp with time zone, ref1 => unknown) does not exist

Verifying the stored procedure name and parameter types. The myDtTm parameter is defined as DATE, but it seems like the data type is mismatched when passed.

My environment:

  • Database: PostgreSQL
  • ORM: Dapper
  • Driver: Npgsql

Question

  • How do I correctly pass a DATE parameter to a PostgreSQL stored procedure using dynamic parameters in Dapper and Npgsql?

  • Why is the procedure not found even when the parameter types seem to match?

Tags:

2

Answers


  1. 0

    if you need now you can just do

    SELECT NOW() AT TIME ZONE 'UTC' AS utc_now;

    in .net

    NpgsqlTransaction tran = connection.BeginTransaction();
var parameters = new DynamicParameters();
parameters.Add("appcode", "PPM", direction: ParameterDirection.Input);
parameters.Add("myDtTm", DateTime.UtcNow.Date, DbType.Date, direction: ParameterDirection.Input);
parameters.Add("ref1", direction: ParameterDirection.InputOutput);

// Execute the stored procedure
await connection.ExecuteAsync(storedProcedureName, parameters, commandType: CommandType.StoredProcedure);
    Login or Signup to reply.
  2. 0

    You must know the fact that in PostgreSQL, name is insensitive. As your proc declaration, myDtTm parameter name will become mydttm. You need to use lowercase parameter name instead.

    parameters.Add("myDtTm".ToLower(), DateOnly.Parse(DateTime.UtcNow.ToString("yyyy-MM-dd")), DbType.Date, direction: ParameterDirection.Input);

    As my experience, you should use snake_case for naming, so that reverse engineering can understand and scaffolding Db-Context with names of classes and props are Pascal Case.

    Furthermore, as my finding, Npgsql dropped support for refcursors. In the case that you want to read data from refcursors, you need to write raw query to manually fetch all data. If you remove ref1 from DynamicParameters and proc declaration, the call should be fine.

    using System.Data;

using Dapper;

using Npgsql;

var connectionString = "Host=localhost:5433;Username=postgres;Password=postgres;Database=postgres";
await using var dataSource = NpgsqlDataSource.Create(connectionString);
var connection = await dataSource.OpenConnectionAsync();
using var transaction = await connection.BeginTransactionAsync();

var storedProcedureName = "simplemenuproc4";
/*
CREATE OR REPLACE PROCEDURE simplemenuproc4(
    appcode IN VARCHAR, 
    myDtTm DATE
)
LANGUAGE plpgsql
AS $BODY$
BEGIN

END;
$BODY$;
*/
var parameters = new DynamicParameters();
parameters.Add("appcode", "PPM", direction: ParameterDirection.Input);
parameters.Add("myDtTm".ToLower(), DateOnly.Parse(DateTime.UtcNow.ToString("yyyy-MM-dd")), DbType.Date, direction: ParameterDirection.Input);

await connection.ExecuteAsync(storedProcedureName, parameters, commandType: CommandType.StoredProcedure);

    I implemented successfully that using Dapper to fetch all data from several cursors returned from User-defined functions.

    I cannot share the code, but if you are interested in, I will create a minimum repro later.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search