Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Error calling function in Postgres Database using Call DB procedure step.

  1. #1

    Default Error calling function in Postgres Database using Call DB procedure step.

    Hi, I am facing issue while calling a function stored in postgres Database using call DB procedure step. I have attached the sample .ktr file. Can anyone help me in finding out where i am doing wrong.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You are using an undefined variable for the hostname in your database connection.
    The name of the result field is not configured, as well as the type of the parameter.
    So long, and thanks for all the fish.

  3. #3

    Default

    For hostname i am passing the parameter value at runtime. The name of the result field i have not given as it is a function.

    The error message which i am getting is "Unable to call procedure Function sample_fn(bigint) does not exist" which is coming whether i mention the type of parameter (integer) or Not.

    This is my function which i am calling:-

    CREATE OR REPLACE FUNCTION sample_fn(m_id integer)
    RETURNS void AS
    $BODY$
    BEGIN
    DELETE from emp where emp_id = m_id;

    End;
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    ALTER FUNCTION sample_fn(integer)
    OWNER TO postgres;


    Can you please help!!

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    What about using a qualified name for the function?
    So long, and thanks for all the fish.

  5. #5

    Default

    sorry couldn't get you. Can you please elaborate. Anyways i have tried giving proc-name as sample_fn (integer) , sample_fn(1) but still getting error. FYI the function runs fine whe i am running it through a database connecting tool pgadmin on giving this command "select sample_fn(1);"

  6. #6
    Join Date
    Jul 2009
    Posts
    476

    Default

    A couple of ideas:

    Your error message says it can't find a function "sample_fn(bigint)" and the function you defined takes an integer parameter. Postgres is complaining because the parameter you are passing has the wrong type. In your Call DB Procedure step, in the Parameter list, you listed Type "None" for m_id. Try changing that to Integer, which is how you defined it.

    If you did that already and it still did not work, you could try changing your Postgres function to return a value instead of just void, and fill in the Result name Result type. I haven't used this step, so I don't know if it really wants a return value or if it's OK with a void return.

  7. #7

    Default

    Tried that too but same error.
    One more thing .. I tried exploring the database connection from pentaho to view all the schemas and tables present within the database. But I could not find my function through this feature. Is it so that pentaho is not able to read the functions present in Postgres DB..?

  8. #8
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    If I only had read your DDL more carefully.
    Your sample function doesn't qualify as compatible with the requirements of the lookup step "Call DB Procedure".
    The Wiki page states, that your function must communicate through parameters only, no cursor allowed.
    As far as the cursor is concerned, you did well, but it hardly is a lookup function.
    You can execute your function using one of the EXECUTE SQL steps.
    So long, and thanks for all the fish.

  9. #9
    Join Date
    Sep 2013
    Posts
    235

    Default

    I am not sure and I did not check ValueMetaInteger conversions when perform db inserts/calls (which is kettle Integer representation) - but in kettle ValueMetaInteger is a 'Long' java object under the hood (on run-time). This may not work at all, but finally if there is no other options - just try to declare function with 'bigint' instead of 'int' value. That would be a surprise if it will be passed. Sorry I don't have postgres just for now to test.

  10. #10

    Default

    Thanks Dzmitry function got executed on changing the type to bigint from int !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.