stored procedure call is failing

[msimko]

Hello,

when calling stored procedure using DotConnect for Postgres, I have the following exception:
PgSqlException : could not determine data type of parameter $1

Test setup:

Prepare Model first Data Context that creates the following table:

Code: Select all

CREATE TABLE public."Tests" (
   "Id" SERIAL NOT NULL,
   "Status" INT4 NOT NULL,
   "TestSuiteId" INT4 NOT NULL,
   CONSTRAINT "PK_Tests" PRIMARY KEY ("Id"),
);

--insert some data:

Code: Select all

INSERT INTO "Tests"("Id", "Status", "TestSuiteId") VALUES (1, 0, 1);
INSERT INTO "Tests"("Id", "Status", "TestSuiteId") VALUES (2, 0, 1);
INSERT INTO "Tests"("Id", "Status", "TestSuiteId") VALUES (3, 0, 1);
INSERT INTO "Tests"("Id", "Status", "TestSuiteId") VALUES (4, 0, 2);
INSERT INTO "Tests"("Id", "Status", "TestSuiteId") VALUES (5, 0, 2);

--prepare stored procedure:

Code: Select all

create or replace function public."GetCreatedTestForTestSuiteAndMarkItAsRunning"(testSuiteId int)
returns int
as $$
declare 
	testId int;
	updatedRows int;
begin
	raise info 'selecting created test for test suite with id %', testSuiteId;

	select "Id" into testId from public."Tests"
	where "TestSuiteId" = testSuiteId
	and "Status" = 0
	fetch first 1 row only;

	update public."Tests"
	set "Status" = 1 --Running
	where "Id" = testId and "Status" = 0; --Created

	get diagnostics updatedRows = row_count;

	raise info 'updatedRows: %', updatedRows;

	if updatedRows = 1 then
		return testId;
	else
		return -1;
	end if;
end;
$$ language plpgsql;

It is possible to manually call the stored procedure by

Code: Select all

select * from public."GetCreatedTestForTestSuiteAndMarkItAsRunning"(1);

Now, add a method to the DataContext diagram (name it GetCreatedTestForTestSuiteAndMarkItAsRunning)
- return type System.Int32
- one input parameter named testSuiteId, System.Int32


When I call the method

Code: Select all

using (var ctx = new DataContext())
{
	var result = ctx.GetCreatedTestForTestSuiteAndMarkItAsRunning(1);
}

I got the following exception:

Code: Select all

Devart.Data.Linq.LinqCommandExecutionException : Error on executing DbCommand.
  ----> Devart.Data.PostgreSql.PgSqlException : could not determine data type of parameter $1

Could you please help me?

Thank you.

[MariiaI]

Most likely, you didn't map this method to the corresponding stored procedure.
Please open the Method Editor for the "GetCreatedTestForTestSuiteAndMarkItAsRunning" method and make sure that the Source is set to "GetCreatedTestForTestSuiteAndMarkItAsRunning". Please refer to http://www.devart.com/linqconnect/docs/ ... pping.html.

The generated code should look like this:

Code: Select all

[Function(Name=@"""GetCreatedTestForTestSuiteAndMarkItAsRunning""", IsComposable=true)]
        public System.Nullable<System.Int32> GetCreatedTestForTestSuiteAndMarkItAsRunning([Parameter(Name="testSuiteId", DbType="INTEGER")] System.Nullable<int> testSuiteId)
        {
            IExecuteResult _GetCreatedTestForTestSuiteAndMarkItAsRunningResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), testSuiteId);
            return ((System.Nullable<System.Int32>)(_GetCreatedTestForTestSuiteAndMarkItAsRunningResult.ReturnValue));
        }

Please also refer to this section in the LinqConnect documentation.

Please tell us if this information helps.

[msimko]

I've filled in method Source as you suggested. Now it works fine.

Thank you for help.

[MariiaI]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.