如何将对象（从MySQL临时表）返回到Coldfusion存储过程？ [英] how to return an object (from a MySQL temporary table) into a Coldfusion stored procedure?

问题描述

我在Coldfusion8 / MySQL中调用存储过程，从产品表中获取3种类型的最低和最高价格。

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

我遇到问题，返回临时表回到MySQL。下面的代码只返回第一个 foundMin 值而不是临时表本身。

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.

如果我在MySQL中运行这个结果是

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.

这是我的代码：

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?

推荐答案

IMO的真正的问题是你的mySQL过程返回多个结果集（而不是结构体）。虽然在某些情况下这是有用的，你的程序是无意的。由于您不是在您的 CF代码中使用这些结果，更好的解决方案是消除它们，并且只返回一个结果。也就是说，没有必要浪费资源来返回你不需要的数据。

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.

你的过程返回多个结果的原因是因为循环中有SELECT语句。每个 SELECT 生成单独的结果集（即查询对象）。您可以通过删除两个 SELECT 语句并将MIN / MAX值直接插入到临时表中来消除这些：

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

然后程序应该生成一个resultset SELECT * FROM MinMax; 。

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

这篇关于如何将对象（从MySQL临时表）返回到Coldfusion存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！