Keep getting "Invalid parameter" error on a function call

Question

I'm new to SQL Anywhere. I'm porting a database that we developed in PostgreSQL 9.1 to SQL Anywhere 12.0.1. I have a function that returns all combinations of a pattern as a result set. The pattern is a series of letters and number, with groups surrounded by square brackets. For example, "A1[0O][0O][0OU]Z1" is one such pattern that's possible. What the function is supposed to do is copy any characters not in square brackets as is, then return one string for each combination of all of the characters in square brackets. So one value returned by the function for the exmple should be "A1000Z1"; another would be "A1O00Z1", and so on.

Whenever I call the function, I get the message below from SQL Anywhere:

Coult not execute statement.  Function 'AllCombinations' has invalid parameter 'Combination' ('OUT')

Here's the source of the function:

CREATE OR REPLACE PROCEDURE "AllCombinations" (
    IN Plate VARCHAR(50)
) RESULT ( Combination VARCHAR(50) )
BEGIN
    DECLARE @Combinations    VARCHAR(8000);
    DECLARE @Combination    VARCHAR(50);
    DECLARE i                INT        DEFAULT 1;

    -- Create the temporary table to hold all of the combinations
    CREATE TABLE #Combinations (
        Combination     VARCHAR(50) NOT NULL
    );

    -- Get all of the combinations AS a big string
    SET @Combinations = "NextDigit"( Plate, 1, '' );

    -- Begin a loop
    BuildCombinations:
    LOOP
        -- Find the i-th combination
        SELECT  row_value INTO @Combination
        FROM    sa_split_list( @Combinations, '|')
        WHERE   line_num = i;

        -- Do we have a string?
        IF @Combination <> '' THEN
            -- We do.  Add it to the Combinations table
            INSERT INTO #Combinations ( Combination ) VALUES ( @Combination );
        ELSE
            -- We do not.  Exit the loop
            LEAVE BuildCombinations;
        END IF;

        -- Move on to the next combination
        SET i = i + 1;
    END LOOP BuildCombinations;

    -- Return all of the combinations we built
    SELECT Combination FROM #Combinations;
END;

I don't believe the problem is in the NextDigit stored procedure. When I call that, I get a correct return value back. It's just this one won't return the proper values.

What is wrong with my code?

Tony

I doubt it's what's causing your issue, but you may want to change `CREATE TABLE` to `DECLARE LOCAL TEMPORARY TABLE` to avoid headaches down the line... — sybkar, Sep 13 '12 at 16:02
Without specifying the table as a temporary table, the procedure will attempt to create it each time it is run. This will only work the first time. If you take a look at `DECLARE LOCAL TEMPORARY TABLE`, it only declares the table to exist for the duration of the stored procedure...I suspect this is the behaviour you're after. http://dcx.sybase.com/index.html#1201/en/dbreference/create-table-statement.html http://dcx.sybase.com/index.html#1201/en/dbreference/declare-local-temporary-table-statement.html — sybkar, Sep 13 '12 at 16:39
I thought the hash mark in the table name made it a temporary table. I've run the stored procedure several times and there have been no errors because of that table. — Tony Vitabile, Sep 13 '12 at 16:52
Sorry, I missed the hash mark. That will still cause problems, if the procedure is called multiple times in the same connection, but between connections you should be okay. Still worth considering, to avoid a potential error later on... — sybkar, Sep 13 '12 at 16:57

Tony Vitabile · Accepted Answer · 2012-09-13T16:51:20.670

I've discovered the problem wasn't in the stored procedure, it was in the statement that called the stored procedure.

The call was written like this:

SELECT "AllCombinations"( 'A1[0O][0O][0OU]Z1' );

This produced the error. If, on the other hand, I write the call like this:

SELECT Combination FROM "AllCombinations"( 'A1[0O][0O][0OU]Z1' );

Then it works. The first syntax was how it was called in PostgreSQL; it also used PostgreSQL's RETURN NEXT statement to return the values. Different database, different syntax.

Keep getting "Invalid parameter" error on a function call

1 Answers1