Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    8
    Rep Power
    0

    Post gre sql stored procedure with java server faces


    HI,
    I have searched the internet for how to call function(stored proc) in post gre sql using jsf 2.0 and tired of finding solution for that.
    please provide an example(snippet) or a way where we can call stored procedure(Function) in jsf 2.0.

    Thanks in advance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    To call a function in Postgres (note: it's either Postgres or PostgreSQL but never Postgre or PostGre or post gre or anything similar) you just run a select on that function:

    Code:
    select my_function(42)
    or if it's a set returning function
    Code:
    select * from my_function(42)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    8
    Rep Power
    0
    It is not that,i mean.
    Here is the old code where "P_RMG_SPOC__SEL_Count"
    is a stored procedure in my sql which retrieves the count of spoc based on given id.
    I am using sql row set and spring jdbc template to execute the query.

    public Integer RmgSpoc(Employee empId) {

    Integer rmgSpocCount = 0;
    SqlRowSet srs = jdbcTemplate.queryForRowSet(
    "CALL P_RMG_SPOC__SEL_Count(?)", "123456");
    while (srs.next()) {
    rmgSpocCount = srs.getInt("RMG_SPOC_ID");
    }
    return rmgSpocCount;
    }

    Can u specify the code or way to proceed with postgres with the use of jdbc template and result set.

    Thanks in Advance.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    I showed you how to call a function that returns a result set.
    Just use that in the JDBC call
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    PostgreSQL doesn't use CALL to start a procedure, PostgreSQL does not even have stored procedures, it has stored functions and stored functions are selected like values or like tables.

    So, CALL function() becomes SELECT function()

    The result is also read asif it is a regular query, no special syntax required.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    8
    Rep Power
    0
    Now i am facing a new problem,
    I have declared two input parameters in stored function one is integer and another is charcater varying.

    While from java side as i pass String it throws me an error like the query you are searching for does not exist.

    Can u pleae tell me how can i convert my string value to postgresql charcater varying .

    Thanks in advance.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    8
    Rep Power
    0
    Sorry for not providing the example

    The code is
    SELECT P_RMG_SPOC__SEL_Count(?)", "123456");

    But it throws an error like P_RMG_SPOC__SEL_Count(String)function does not exist.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    You can probably cast the string to an integer:

    SELECT P_RMG_SPOC__SEL_Count(CAST(? AS INTEGER))", "123456");
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    8
    Rep Power
    0
    For sample i have used that code.
    Every time it can't be integer.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Every time it can't be integer.
    Every function has a hardcoded datatype for the parameters so if you want a function that accepts strings then you must define a new function that has varchar defined as the attribute type, and cast your java data to varchar.

    Have you looked at examples of how to program stored functions? because this feature is one of the first things everbody mentions.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    8
    Rep Power
    0
    This is the way i have tried out

    SqlRowSet srs = jdbcTemplate.queryForRowSet(
    "SELECT P_RMG_SPOC__SEL_Count(?)",new SqlParameter("vinod", Types.VARCHAR));
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    And did that work?
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    8
    Rep Power
    0
    NO it is throwing an error that the query doest n't exist
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    POST the actual error, I can't tell what's wrong just from "it says it cant find the query".
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    8
    Rep Power
    0
    Sorry this is the error

    org.postgresql.util.PSQLException: ERROR: function P_RMG_SPOC__SEL_Count(character varying) does not exist
    Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo