Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005
    Posts
    25

    Unanswered: AHH - invalid input syntax for type boolean



    Hey all,

    Has anyone come accross the 'invalid input syntax for type boolean' error - its driving me crazy trying to figure out what is wrong with my function/java code!!!

    The postgres (using 8.1) function is:

    CREATE OR REPLACE FUNCTION selectstaffroles("varchar")
    RETURNS refcursor AS
    $BODY$
    DECLARE
    ResultSet refcursor;
    whereClause ALIAS FOR $1;
    BEGIN
    OPEN ResultSet FOR
    EXECUTE 'SELECT role.* FROM role left join staffrole on staffrole.roleid=role.roleid WHERE ' || whereClause;
    RETURN Resultset;
    END;
    $BODY$
    LANGUAGE 'plpgsql' VOLATILE;

    WhereClause sent via java code =

    c.colStaffRoleStaffID+"="+staff.getUserStaffID() -which basically is staffid=2

    Once this query is executed via the java code i get the 'invalid input syntax for type boolean: "staffid=2" ' error. However if I type select selectstaffroles('staffid=2') using pgAdmin SQL window - no problems!

    ANY HELP or suggestions will be greatly appreciated! - Thanks in advance!

    S.

  2. #2
    Join Date
    May 2005
    Posts
    25

    Problemo resolved

    No worries! Have sorted it - just needed some sleep!

    The code above is correct - no problems with it at all!

    Just incase anyone else encounters this problem - the error was in fact lurking elsewhere in my code! Within another method which was used in my java code I had enclosed the whereClause within quotes! - Removed them and problemo solved!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •