dotConnectPostgreSQL using procedure

[ratm]

Hi I'm trying to use an imported procedure on a linqModel (lqml) but it gives me an error.

This is the definition of the procedure in dataContext:

[Function(Name=@"icacode.verificatoken")]
public void Verificatoken([Parameter(Name="in_id_token", DbType="CHARACTER VARYING")] string in_id_token, [Parameter(Name="n_matricola_operatore", DbType="CHARACTER VARYING")] ref string n_matricola_operatore, [Parameter(Name="n_cod_centro_costo", DbType="CHARACTER VARYING")] ref string n_cod_centro_costo, [Parameter(Name="n_id_paz", DbType="CHARACTER VARYING")] ref string n_id_paz, [Parameter(Name="n_visit_number", DbType="CHARACTER VARYING")] ref string n_visit_number)
{
IExecuteResult _VerificatokenResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), in_id_token, n_matricola_operatore, n_cod_centro_costo, n_id_paz, n_visit_number);
n_matricola_operatore = ((string)(_VerificatokenResult.GetParameterValue(1)));
n_cod_centro_costo = ((string)(_VerificatokenResult.GetParameterValue(2)));
n_id_paz = ((string)(_VerificatokenResult.GetParameterValue(3)));
n_visit_number = ((string)(_VerificatokenResult.GetParameterValue(4)));
}

This is calling procedure:

string matricola = "";
string cdc = "";
string IdPaziente = "";
string visit = "";

ctx.Verificatoken("111", ref matricola, ref cdc, ref IdPaziente, ref visit);

Verificatoken is a procedure with output parameters

but when call this return error:

[PgSqlException (0x80004005): icacode.verificatoken(unknown, unknown, unknown, unknown, unknown) è una procedura]
Devart.Data.PostgreSql.e.f(r A_0) +217
Devart.Data.PostgreSql.r.n() +138
Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords) +1107
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery) +911
Devart.Common.DbCommandBase.ExecuteNonQuery() +31
Devart.Data.Linq.DataProvider.a(av A_0, Object[] A_1) +2686

[LinqCommandExecutionException: Error on executing DbCommand.]
Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e) +75
Devart.Data.Linq.DataProvider.a(av A_0, Object[] A_1) +5037
ICACODE.Core.DBDataContext.Verificatoken(String in_id_token, String& n_matricola_operatore, String& n_cod_centro_costo, String& n_id_paz, String& n_visit_number) +322
ICACODE.Web.Login.Page_Load(Object sender, EventArgs e) in C:\Users\Matteo\Documents\Visual Studio 2017\Projects\Crediti08\IcaCode_TEST\ICACODE\ICACODE.Web\Login.aspx.cs:41
System.Web.UI.Control.OnLoad(EventArgs e) +106
System.Web.UI.Control.LoadRecursive() +68
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3785

Can anyone help me?

[Shalex]

Please specify the DDL script of your procedure and all related database objects so that we can reproduce the issue in our environment.

[ratm]

this is my procedure:

-- PROCEDURE: icacode.verificatoken(character varying, character varying, character varying, character varying, character varying)

-- DROP PROCEDURE icacode.verificatoken(character varying, character varying, character varying, character varying, character varying);

CREATE OR REPLACE PROCEDURE icacode.verificatoken(
in_id_token character varying,
INOUT n_matricola_operatore character varying,
INOUT n_cod_centro_costo character varying,
INOUT n_id_paz character varying,
INOUT n_visit_number character varying)
LANGUAGE 'plpgsql'

AS $BODY$begin
SELECT matricola_operatore, cod_centro_costo, id_paz, visit_number FROM portale.auth_token
INTO n_matricola_operatore, n_cod_centro_costo, n_id_paz, n_visit_number WHERE id_token=in_id_token;
end;
$BODY$;

in this procedure I pass him a token and this returns me 4 values:

• matricola_operatore,

• cod_centro_costo,

• id_paz,

• visit_number.

Which I have to go after.

string matricola = "32";
string cdc = "41";
string IdPaziente = "5";
string visit = "test";

ctx.Verificatoken("111", ref matricola, ref cdc, ref IdPaziente, ref visit);
Utente utente = (from u in ctx.Utente
where !u.Eliminato
&& u.Id == int.Parse(matricola)
//&& u.UserPassword == password
select u).FirstOrDefault();

[Shalex]

Thank you for your report. We have reproduced the issue and will notify you when it is fixed.

[Shalex]

The bug with running stored procedure in PostgreSQL Server 11 or higher is fixed. We will notify you when the new public build of dotConnect for PostgreSQL is available for download.

[ratm]

Thanks to timeliness, the problem has been solved

[Shalex]

New build of dotConnect for PostgreSQL 7.13.1402 is available for download now: viewtopic.php?f=3&t=38882.