Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

overloaded procedure - what am I doing wrong?

Help
Mar
2014-03-11
2014-03-12
  • Mar
    Mar
    2014-03-11

    I have 2 procedures with different argument types but the same number of arguments.

    According to
    http://hsqldb.org/doc/guide/sqlroutines-chapt.html
    overloaded functions are allowed so I am assuming what I am trying to do should work.

    what am I doing wrong? I cannot change the procedure names. I am running hsqldb in memory for unit testing.

    In Oracle I have:

    FUNCTION xyzSearch(
      code            IN    VARCHAR2,
      id              IN    VARCHAR2,
      products     OUT   SYS_REFCURSOR ) RETURN VARCHAR2;
    
    FUNCTION xyzSearch (
      guid            IN    column1%TYPE, --> VARCHAR2(32 TYPE)
      type            IN    column2%TYPE, --> NUMBER
      products     OUT   SYS_REFCURSOR ) RETURN VARCHAR2;
    

    So in hsqldb I have created:

    CREATE PROCEDURE xyzSearch (
        IN code VARCHAR(30), 
        IN id VARCHAR(30), 
        OUT result_code LONGVARCHAR)
    
    CREATE PROCEDURE xyzSearch (
        IN guid VARCHAR(32), 
        IN type NUMERIC, 
        OUT result_code LONGVARCHAR)
    

    The inmemory database will load if I have 1 of these procedures but not both.

    With both I get :

    java.sql.SQLSyntaxErrorException: 
        SQL routine exception: routine signature already exists in statement [CREATE PROCEDURE
    
    Caused by: org.hsqldb.HsqlException: 
        SQL routine exception: routine signature already exists
    

    What am I doing wrong?

    Thanks

     
  • Fred Toussi
    Fred Toussi
    2014-03-12

    While CREATE FUNCTION tolerates this kind of overloading, CREATE PROCEDURE requires different number of arguments for overloaded signatures.

     
  • Fred Toussi
    Fred Toussi
    2014-03-12

    You should be able to use a function like this:

    CREATE FUNCTION xyzSearch (
    code VARCHAR(30),
    id VARCHAR(30)) RETURNS LONGVARCHAR

     
    Last edit: Fred Toussi 2014-03-12
  • Mar
    Mar
    2014-03-12

    so overloading with the same number of params is not supported in procedures only in functions.
    :(

    Yeah both have cursors "returned" so I cant change one to a function.

    Maybe I'll add conditional code to the DAO... hmmm. Thanks for responding now I know to look for another solution :)