how to return an object (from a MySQL temporary table) into a Coldfusion stored procedure?

Question

I'm calling stored procedure in Coldfusion8/MySQL which gets 3 types of min and max prices from products table.

I'm having problems returning the temp table back to MySQL. The below code only returns the first foundMin value and not the temp table itself.

If I run this inside MySQL the results are

foundmin 1st price > returned to Coldfusion
foundmax 1st price
foundmin 2nd price
foundmax 2nd price
foundmin 3rd price
foundmax 3rd price
temporary table

So I'm returning all individual table entries plus the table, when I only want the table.

Here is my code:

BEGIN
    DECLARE filterILN  vARCHAR(100);
    DECLARE localILN   vARCHAR(100);
    DECLARE orderILN   vARCHAR(55);
    #search strings
    DECLARE p_e        vARCHAR(55) DEFAULT 'art.preis_ek';
    DECLARE p_a        vARCHAR(55) DEFAULT 'art.preis_aktuell';
    DECLARE p_r        vARCHAR(55) DEFAULT 'art.rabatt';
    DECLARE strLen     INT DEFAULT 4;
    DECLARE strCount   INT DEFAULT 1;
    DECLARE searchFor  vARCHAR(55);
    DECLARE foundMin     DECIMAL(12,2);
    DECLARE foundMax   DECIMAL(12,2);

    # temp table
    DROP TEMPORARY TABLE IF EXISTS MinMax;
    CREATE TEMPORARY TABLE MinMax (
        price  vARCHAR(50) DEFAULT ''
      , minVal DECIMAL(12,2) DEFAULT 0.00      
      , maxVal DECIMAL(12,2) DEFAULT 0.00    
    ) ENGINE=MEMORY;

    # FILTER 1
    IF param_reference_iln = 'A' THEN SET filterILN = 'B'
    ELSEIF param_reference_iln = 'C' THEN SET filterILN = 'D'
    END IF;

    # FILTER 2
    IF param_filter IS NOT NULL AND param_filter != ""
    THEN SET localILN = CONCAT('AND (iln = "', param_filter, '")');
    ELSE SET localILN = '*';
    END IF;

    # FILTER 3
    IF param_preorder = 'ja'
    THEN SET orderILN = CONCAT('AND vororder = "',param_preorder, '"');
    ELSE SET orderILN = '*';
    END IF;

    #loop over strIDs
    getPrice:
      LOOP
        IF ELT(strCount, p_e, p_a, p_r) = 'art.rabatt'
        THEN SET searchFor = 'art.preis_ek - art.preis_aktuell)/art.preis_ek';
        ELSE SET searchFor = ELT(strCount, p_e, p_a, p_r);
        END IF;

        #min
        SELECT MIN(searchFor) AS foundMin
        FROM artikelstammdaten AS art
           WHERE art.aktiv = "ja"
           AND art.bestand != "0"
           AND filterILN
           AND art.modus = CONCAT('OPEN ', param_unlocked_iln)
           AND localILN
           AND orderILN
           LIMIT 1;
        #max
        SELECT MAX(searchFor) AS foundMax
        FROM artikelstammdaten AS art
           WHERE art.aktiv = "ja"
           AND art.bestand != "0"
           AND filterILN       
           AND art.modus = CONCAT('OPEN ', param_unlocked_iln)
           AND localILN
           AND orderILN
           LIMIT 1;

        # insert into temp table
        INSERT INTO MinMax ( price, minVal, maxVal )
        VALUES( ELT(strCount, p_e, p_a, p_r), foundMin, foundMax );

        # increate counter by 1, end if strLen reached
        SET strCount = strCount+1;
        IF strCount = strLen
        THEN LEAVE getPrice;
        END IF;

      END LOOP getPrice;

    #output table
    SELECT * FROM MinMax;

    #destroy
    DROP TABLE MinMax;

END

The values are calculated correctly and also inserted in the temp table where they should be. The only problem is the above returns both the table entries AND the table.

Question:
How do I return just the temp table as a resultset/struct, which I can then work with in Coldfusion?

Leigh · Accepted Answer · 2012-06-16T20:34:06.480

2

IMO the real problem is your mySQL procedure returns multiple resultsets (not structs). While that can be useful in some cases, your procedure is doing it unintentionally. Since you are not using those results in your CF code, the better solution is to eliminate them and only return a single result. ie There is no point wasting resources to return data you do not need.

The reason your procedure returns multiple results is because of the SELECT statements inside your loop. Each SELECT generates a separate resultset (ie query object). You can eliminate those by removing the two SELECT statements and insert the MIN/MAX values directly into your temp table instead:

    INSERT INTO MinMax ( price, minVal, maxVal )
    SELECT ELT(strCount, p_e, p_a, p_r), MIN(searchFor), MAX(searchFor)
    FROM   artikelstammdaten AS art
    WHERE  art.aktiv = "ja"
       AND art.bestand != "0"
       AND filterILN
       AND art.modus = CONCAT('OPEN ', param_unlocked_iln)
       AND localILN
       AND orderILN

Then the procedure should generate a single resultset ie SELECT * FROM MinMax;.

edited Jun 16 '12 at 20:34

answered Jun 16 '12 at 20:23

Leigh

28,765
10
55
103

looks good. thanks. also for pointing me in this direction. i will edit struct to object tomorrow. – frequent Jun 16 '12 at 22:10
1

Also, did you verify the `where` clause is actually working as you expect? Because I do not think you can use variables to alter the sql that way. In most databases it requires dynamic sql. – Leigh Jun 16 '12 at 23:34
hm. wanted to test today. it better work because i have a ton of cfqueries who live by creating parameters like this and which i wanted to move into mysql. e.g. the filterIln variable is from a loop in cf which creates a string of valid user ids like "iln=123 or iln=456 or iln=789..." which i'm pulling from another general stored proc and adding here. i guess i could run this here to but i made filterIln a util-variable-storedproc, cause it's used throughout the application i inherited – frequent Jun 17 '12 at 07:16
I primarily use MS SQL, but with most db's you use a variable in a comparison ie `WHERE ColumnName = variableName`. But you cannot place sql *inside* a variable and expect it to be evaluated. For that you need dynamic sql. – Leigh Jun 17 '12 at 07:27
ok. If I do it as per your suggestion, my variable names fail. In my 7-result object, they seem to work. I'm clueless... for now – frequent Jun 19 '12 at 10:01
Got it to work. You were correct, it's not possible to use SQL syntax in parameter values... I managed to get by without dynamic-SQL (if anyone needs a newbie digestable primer ([here](http://rpbouman.blogspot.co.at/2005/11/mysql-5-prepared-statement-syntax-and.html))), so happy camper for now – frequent Jun 19 '12 at 12:04
Cool, glad you got it to work. Just fyi, that is dynamic sql. Just the safer variety with bind variables :) – Leigh Jun 19 '12 at 12:06
I know. I found that article easy to grasp and eventually settled for plain old AND( (x = "123" ) AND do this ) OR AND do that ) plus I changed from chainging with OR to using IN(x,y,z) and passing in x,y,z. – frequent Jun 19 '12 at 12:12

score 0 · Answer 2 · answered Jun 16 '12 at 11:20

0

I believe there is no mechanism to have a procedure/function return a whole table (TABLE is not a type AFAIK). On the other hand, you could SELECT * FROM MinMax from your application immediately after your procedure completes, as long as you do it within the same session (~ on the same DB connection).

answered Jun 16 '12 at 11:20

RandomSeed

29,301
6
52
87

Should be possible. At least that's what I have been told in another question: http://stackoverflow.com/questions/11004020/can-i-call-a-stored-procedure-in-a-cfloop-and-output-dynamic-out-parameters-in-c/11005545#11005545. – frequent Jun 16 '12 at 11:21
Interesting hack : http://stackoverflow.com/questions/273929/what-is-the-equivalent-of-oracles-ref-cursor-in-mysql-when-using-jdbc/445434#445434. It smells, though. – RandomSeed Jun 16 '12 at 11:27
hm. So do you have another idea how I can output my values in a single dump? I don't want to call the stored-proc 3x and I have tried forever to use out-parameters. Just don't work... – frequent Jun 16 '12 at 11:29
also I think the problem is I'm SELECTing values before adding them to the table. Seems like SELECT adds it to the selection. Is there a way to "select without SELECT" in this case? – frequent Jun 16 '12 at 11:37

score 0 · Answer 3 · answered Jun 16 '12 at 12:16

Got it. I need to use the resultset attribute in Coldfusion like so:

<!--- my stored proc --->
<cfstoredproc procedure="proc_search_select_minmax" datasource="db">
    <cfprocparam type="in" value="#A#" cfsqltype="cf_sql_varchar" maxlength="13">
    <cfprocparam type="in" value="#B#" cfsqltype="cf_sql_varchar" maxlength="13">
    <cfprocparam type="in" value="#C#" cfsqltype="cf_sql_text">
    <cfprocparam type="in" value="#D#" cfsqltype="cf_sql_char" maxlength="4">
    <!--- 7 results (3 min/3 max/temp table, I need to get the 7th resultset --->
    <cfprocresult name="min_max_struct" resultSet=7>
</cfstoredproc>

So it is possible to output a struct from MySQL! You just have to know where it is...

Actually the procedure returns resultsets (ie query objects), *not* structs. See my response below. — Leigh, Jun 16 '12 at 20:24

how to return an object (from a MySQL temporary table) into a Coldfusion stored procedure?

3 Answers3

Linked