как вернуть объект (из временной таблицы MySQL) в хранимую процедуру Coldfusion?

Я вызываю хранимую процедуру в Coldfusion8 / MySQL, которая получает 3 типа минимальных и максимальных цен из таблицы продуктов.

У меня проблемы с возвратом временной таблицы в MySQL. Приведенный ниже код возвращает только первое значение foundMin, но не саму временную таблицу.

Если я запустил это внутри MySQL, результаты будут

foundmin 1st price > returned to Coldfusion
foundmax 1st price
foundmin 2nd price
foundmax 2nd price
foundmin 3rd price
foundmax 3rd price
temporary 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

Значения рассчитываются правильно и также вставляются в временную таблицу, где они должны быть. Единственная проблема заключается в том, что приведенное выше возвращает как записи таблицы, так и таблицу.

Вопрос:
Как мне вернуть только временную таблицу в виде набора результатов / структуры, с которой я затем могу работать в Coldfusion?


person frequent    schedule 16.06.2012    source источник


Ответы (3)


ИМО, настоящая проблема в том, что ваша процедура mySQL возвращает несколько наборов результатов (не структур). Хотя в некоторых случаях это может быть полезно, ваша процедура выполняется непреднамеренно. Поскольку вы не используете эти результаты в своем CF-коде, лучшим решением является их устранение и возврат только одного результата. т.е. нет смысла тратить ресурсы на возврат ненужных данных.

Причина, по которой ваша процедура возвращает несколько результатов, заключается в операторах SELECT внутри вашего цикла. Каждый SELECT генерирует отдельный набор результатов (т. Е. Объект запроса). Вы можете устранить их, удалив два оператора SELECT и вместо этого вставив значения MIN / MAX непосредственно в вашу временную таблицу:

    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

Затем процедура должна сгенерировать один набор результатов, то есть SELECT * FROM MinMax;.

person Leigh    schedule 16.06.2012
comment
выглядит неплохо. благодаря. также за то, что указал мне в этом направлении. Я отредактирую структуру до объекта завтра. - person frequent; 17.06.2012
comment
Кроме того, вы убедились, что предложение where действительно работает так, как вы ожидали? Потому что я не думаю, что вы можете использовать переменные для изменения sql таким образом. В большинстве баз данных требуется динамический sql. - person Leigh; 17.06.2012
comment
хм хотел сегодня опробовать. это лучше работает, потому что у меня есть тонна cfqueries, которые живут, создавая такие параметры, и которые я хотел переместить в mysql. например переменная filterIln берется из цикла в cf, который создает строку действительных идентификаторов пользователей, таких как iln = 123 или iln = 456 или iln = 789 ... которые я извлекаю из другого общего сохраненного процесса и добавляю сюда. Я думаю, я мог бы запустить это здесь, но я сделал filterIln util-variable-storedproc, потому что он используется во всем приложении, которое я унаследовал - person frequent; 17.06.2012
comment
Я в основном использую MS SQL, но с большинством баз данных вы используете для сравнения переменную, то есть WHERE ColumnName = variableName. Но вы не можете поместить sql внутрь переменной и ожидать, что она будет оценена. Для этого вам понадобится динамический sql. - person Leigh; 17.06.2012
comment
в порядке. Если я сделаю это согласно вашему предложению, мои имена переменных не сработают. В моем объекте с 7 результатами они вроде бы работают. Я не знаю ... пока - person frequent; 19.06.2012
comment
Получил, чтобы работать. Вы были правы, невозможно использовать синтаксис SQL в значениях параметров ... Мне удалось обойтись без динамического SQL (если кому-то нужен усваиваемый учебник для новичков (здесь)), так что счастливого туриста на данный момент - person frequent; 19.06.2012
comment
Круто, рад, что у тебя все заработало. Просто к сведению, это динамический sql. Просто более безопасный вариант с переменными привязки :) - person Leigh; 19.06.2012
comment
Я знаю. Я обнаружил, что эту статью легко понять, и в конце концов остановился на простом старом И ((x = 123) И сделай это) ИЛИ И сделай это), плюс я перешел с цепочки с ИЛИ на использование IN (x, y, z) и передачу x , у, z. - person frequent; 19.06.2012

Я считаю, что нет механизма, чтобы процедура / функция возвращала всю таблицу (TABLE не является типом AFAIK). С другой стороны, вы можете SELECT * FROM MinMax из своего приложения сразу после завершения вашей процедуры, если вы делаете это в том же сеансе (~ в том же соединении с БД).

person RandomSeed    schedule 16.06.2012
comment
Должно быть возможно. По крайней мере, это то, что мне сказали в другом вопросе: stackoverflow.com/questions/11004020/. - person frequent; 16.06.2012
comment
Интересный хак: stackoverflow.com/questions/273929/. Хотя пахнет. - person RandomSeed; 16.06.2012
comment
хм У вас есть еще одна идея, как я могу вывести свои значения в один дамп? Я не хочу вызывать store-proc 3x, и я всегда пытался использовать out-параметры. Просто не работай ... - person frequent; 16.06.2012
comment
также я думаю, что проблема в том, что я ВЫБИРАЮ значения перед добавлением их в таблицу. Похоже, что SELECT добавляет его к выделению. Есть ли способ выбрать без SELECT в этом случае? - person frequent; 16.06.2012

Понятно. Мне нужно использовать атрибут resultset в Coldfusion следующим образом:

<!--- 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>

Таким образом, можно вывести структуру из MySQL! Просто нужно знать, где это ...

person frequent    schedule 16.06.2012
comment
На самом деле процедура возвращает наборы результатов (то есть объекты запроса), а не структуры не. См. Мой ответ ниже. - person Leigh; 17.06.2012