stored procedure call is failing

stored procedure call is failing

Postby msimko » Fri 25 Jul 2014 15:12

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.
msimko
 
Posts: 9
Joined: Thu 06 Feb 2014 09:40

Re: stored procedure call is failing

Postby MariiaI » Mon 28 Jul 2014 12:33

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/?MethodMapping.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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: stored procedure call is failing

Postby msimko » Mon 28 Jul 2014 16:11

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

Thank you for help.
msimko
 
Posts: 9
Joined: Thu 06 Feb 2014 09:40

Re: stored procedure call is failing

Postby MariiaI » Tue 29 Jul 2014 04:51

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for PostgreSQL