Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Informix Новый топик    Ответить
 Dynamic SQL, проблема с курсором  [new]
Fet Frumos
Member

Откуда:
Сообщений: 113
Нужно выбрать данные из нескольких баз. Решил использовать Dynamic SQL в хранимой процедуре. D Столкнулся с следующей проблемой, часть кода ХП
foreach
  select id_val  into i_val
  from  mytable 
    let  i1, i2, i3 = 0,0,0;
    let sql ="execute procedure  "||  alias||"myspl("||i_val||"); ";
    PREPARE tmp  FROM sql;
    DECLARE cur cursor FOR tmp;
    OPEN cur;
    FETCH  cur INTO i_i1, i_i2, i_i3;
    CLOSE cur;
    FREE tmp;
    FREE cur;
    trace 'end value='||i_i1||'  '||i_i2||'  '||i_i3; 
    --дальше использую значения  i_i1, i_i2, i_i3
end foreach	
Проблем в том что значения i_i1, i_i2, i_i3(те что вижу в trace) одинаковы на протяжении всего цикла. Т.е. первое значение i_val подставляю в myspl она возвращает значения, в следующем шаге новое значение i_val а на выходе те же значения. Очень похоже что я неправильно использую курсор. Подскажите в чем может быть ошибка? (alias - алиас BDE)
14 апр 10, 09:56    [8625736]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Ikir
Member

Откуда:
Сообщений: 271
Fet Frumos,

Во-первых, поставить в трейс i_val, чтоб убедиться , что оно разное.
Во-вторых, не видно, где вы в цикле меняете alias, то есть вызываете одну и ту же процедуру.
В-третьих, поставить в трейс alias, и показать процедуру myspl для каждого alias.
14 апр 10, 10:56    [8626231]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Fet Frumos
Member

Откуда:
Сообщений: 113
автор

Во-первых, поставить в трейс i_val, чтоб убедиться , что оно разное.
Во-вторых, не видно, где вы в цикле меняете alias, то есть вызываете одну и ту же процедуру.
В-третьих, поставить в трейс alias, и показать процедуру myspl для каждого alias.

1. i_val трейсил оно разное
ставил TRACE после
let sql ="execute procedure  "||  alias||"myspl("||i_val||"); ";
входные значения меняются
2,3 alias передаю как входной параметр в процедуру, на время отладки использую один алиас прописанный в переменной.
14 апр 10, 11:16    [8626475]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Fet Frumos, лучше бы полный "работоспособный" текст для теста привёл....

Штатный dynamic sql в informix ещё новый - вряд ли кто-то активно польз[уется/овался].

"Всё приходится делать самому..." :)
Привожу пример для остальных, на котором ощутимо видна проблема.
По крайне мере у меня на 11.50FC6TL.

create temp table tmp_table1(id_val INT) lock mode row;
insert into tmp_table1 values(1);                        
insert into tmp_table1 values(2);
insert into tmp_table1 values(3);

create procedure tmp_pair(
  i int
) returning
   int
 , int
 ;

  return i, i;
 
end procedure; 

execute procedure tmp_pair(2);
execute procedure tmp_pair(3);
 
create procedure tmp_test_dsql(
) returning
    int
  , int
  ;

  define i_val int;
  define i_i1, i_i2 int;
  define s_sql varchar(120);
  foreach
    select id_val into i_val
      from tmp_table1 
         order by 1 desc

    let  i_i1, i_i2 = 0,0;
    let s_sql ="execute procedure tmp_pair("||i_val||"); ";
    PREPARE tmp FROM s_sql;
    DECLARE cur cursor FOR tmp;
    OPEN cur;
    FETCH  cur INTO i_i1, i_i2;
    CLOSE cur;
    FREE tmp;
    FREE cur;
    return i_i1, i_i2 
      with resume; 
  end foreach	
end procedure;

-- вот здесь видна проблема
execute procedure tmp_test_dsql();
-- возвращается 3 записи и все с одинаковыми полями = 3...

-- чистим за собой
drop procedure tmp_test_dsql();
drop procedure tmp_pair(int);
drop table tmp_table1;
14 апр 10, 16:10    [8629608]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
На нижеприведённом варианте ХП для того же теста видно что собака где-то с переподготовкой cur2.
Вполне может быть багом - или вскоре оказаться "недооформленной фичей" :)

create procedure tmp_test_dsql(
) returning
    int
  , int
  ;

  define i_val int;
  define i_i1, i_i2 int;
  define s_sql varchar(120);
  foreach
    select id_val into i_val
      from tmp_table1
        order by 1 desc 
 
    let  i_i1, i_i2 = 0,0;
    let s_sql ="execute procedure tmp_pair("||i_val||"); ";
    if i_val = 3 then
      PREPARE tmp FROM s_sql;
      DECLARE cur cursor FOR tmp;
      OPEN cur;
      FETCH cur INTO i_i1, i_i2;
      CLOSE cur;
      FREE tmp;
      FREE cur;
    else
      PREPARE tmp2 FROM s_sql;
      DECLARE cur2 cursor FOR tmp2;
      OPEN cur2;
      FETCH  cur2 INTO i_i1, i_i2;
      CLOSE cur2;
      FREE tmp2;
      FREE cur2;
    end if
    return i_i1, i_i2 
      with resume; 
  end foreach	
end procedure;
14 апр 10, 16:14    [8629659]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
С одной стороны:
RTFM
Resources allocated to the prepared statement can be released later using the FREE statement.

С другой:
RTFM
A statement identifier can represent only one SQL statement or (in ESQL/C) one semicolon-separated list of SQL statements at a time.

A new PREPARE statement can specify an existing statement identifier if you want to bind the identifier to a different SQL statement text.


Т.е. исходя из доки тестовый пример выглядит корректным.
Похоже на баг.
14 апр 10, 16:19    [8629709]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
ОФФ: 1313 сообщений. Жду бана :)
14 апр 10, 16:20    [8629718]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
АнатоЛой
ОФФ: 1313 сообщений. Жду бана :)

И подсказывают, что в ветке Informix с утра было 666 сообщений :)
14 апр 10, 16:26    [8629773]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
vasilis
Member

Откуда: Украина, Киев
Сообщений: 2205
АнатоЛой
ОФФ: 1313 сообщений. Жду бана :)

Ну, этим сообщением ты всю магию цифр и сломал (уже стало 1314).
Но если есть большое желание могу профилактический бан выдать на денек - просто никого раньше не банил, аж руки чешутся :))
14 апр 10, 16:29    [8629818]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Ikir
Member

Откуда:
Сообщений: 271
Fet Frumos
Нужно выбрать данные из нескольких баз. Решил использовать Dynamic SQL в хранимой процедуре. D Столкнулся с следующей проблемой, часть кода ХП
foreach
  select id_val  into i_val
  from  mytable 
    let  i1, i2, i3 = 0,0,0;
    let sql ="execute procedure  "||  alias||"myspl("||i_val||"); ";
    PREPARE tmp  FROM sql;
    DECLARE cur cursor FOR tmp;
    OPEN cur;
    FETCH  cur INTO i_i1, i_i2, i_i3;
    CLOSE cur;
    FREE tmp;
    FREE cur;
    trace 'end value='||i_i1||'  '||i_i2||'  '||i_i3; 
    --дальше использую значения  i_i1, i_i2, i_i3
end foreach	
Проблем в том что значения i_i1, i_i2, i_i3(те что вижу в trace) одинаковы на протяжении всего цикла. Т.е. первое значение i_val подставляю в myspl она возвращает значения, в следующем шаге новое значение i_val а на выходе те же значения. Очень похоже что я неправильно использую курсор. Подскажите в чем может быть ошибка? (alias - алиас BDE)


Надо модифицировать процедуру:
    let sql ="execute procedure  "||  alias||"myspl( ? ); ";
    PREPARE tmp  FROM sql;
foreach
  select id_val  into i_val
  from  mytable 
    let  i1, i2, i3 = 0,0,0;
    DECLARE cur cursor FOR tmp;
    OPEN cur using i_val;
    FETCH  cur INTO i_i1, i_i2, i_i3;
    CLOSE cur;
    FREE cur;
    trace 'end value='||i_i1||'  '||i_i2||'  '||i_i3; 
    --дальше использую значения  i_i1, i_i2, i_i3
end foreach	
    FREE tmp;

14 апр 10, 17:00    [8630251]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Ikir

Надо модифицировать процедуру:
...
...
    let sql ="execute procedure  "||  alias||"myspl( ? ); ";
...

Если бы не переменная "alias||", присутствующая перед именем ХП, было бы совсем хорошо...
Но есть подозрение, что alias либо префикс, изменяющий собственно имя ХП, которую вызываем, либо ещё веселее - имя другой БД [а то и сервера]. А по тесту видно, что смена строки с выражением не приводит к "перекомпиляции" запроса. Итого: если alias - это параметр тестируемой ХП, то должно заработать, а если alias меняется внутри ХП (есть внешний цикл - скорее всего не заработает)...
ТС, ау?! :)
14 апр 10, 17:41    [8630641]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Fet Frumos
Member

Откуда:
Сообщений: 113
Большое спасибо, вариант предложенный Ikir работает . Алиас - имя другой базы, пока что на одном сервере(на время тестов), а так планирую на разных. Алиас передается в ХП входным параметром. О результате экспериментов напишу :)
14 апр 10, 18:10    [8630840]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Лена Владыкина
Member

Откуда:
Сообщений: 2
может быть, поможет. :-)

... statement text that [SQL statement for Dynamic Cursor in an SPL Routine] specifies can include question mark (? ) symbols as placeholders for values that the user supplies at runtime, but these placeholders in the PREPARE statement can represent only data values, not SQL
identifiers.


(c) http://publibfp.dhe.ibm.com/epubs/pdf/c2377515.pdf

Насколько я понимаю, эта проблема была с данными,
название сервера или имя хранимки ("alias") - это идентификтор.
19 апр 10, 11:45    [8649103]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Лена Владыкина, не совсем так. К "знаку вопроса" пришли позже: как к обходному решению выявленной проблемы. А выявленная проблема звучит так: "Несмотря на возможность (декларированную в документации) повторно использовать одну и ту же переменную внутри ХП для выполнения разных SQL-утверждений, в действительности смена в цикле SQL утверждения не приводит к реальному выполнению разных SQL-утверждений, а выполняется всё время то, которое было подготовлено первым. К счастью, механизм параметров оказался работающим - что и устроило топик-стартера".
19 апр 10, 22:24    [8653554]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Лена Владыкина
Member

Откуда:
Сообщений: 2
АнатоЛой, я понимаю, что ты имеешь ввиду... Но вопрос, мне кажется, в другом.. Дело в том, что в документации написано только то, что переменная для SQL выражения должна быть типа character. А можно ли ее формировать приведением типов или нет, видимо, решается самостоятельно, исходя из богатого опыта формирования значений таких переменных. И это, по-моему, вполне нормально. На самом же деле, немного ниже того места, что ты цитировал из документации, есть целый список условий по формированию текста SQL выражения, включая это:

In some statements, parameters are unknown when the statement is prepared
because a different value can be inserted each time the statement is executed. In
these statements, you can use a question-mark ( ? ) placeholder where a parameter
must be supplied when the statement is executed.


Собственно, вопрос тогда вот в чем.. Насколько это этично, давать программистам возможность пользоваться динамическим SQL, но при этом предполагать возможность, что они будут спотыкаться?
Как по мне, ну и ничего особо страшного в этом нет:-)

p.s. Толик, уточняю на всякий случай, что я остаюсь преданной поклоннице всех твоих талантов, с которыми знакома:-)))
20 апр 10, 18:50    [8659067]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Лена Владыкина
А можно ли ее формировать приведением типов или нет, видимо, решается самостоятельно, исходя из богатого опыта формирования значений таких переменных.
...

Именно с формированием строки никаких проблем и не возникало...
Ещё раз обращаю внимание на упомянутую цитату:

RTFM
A new PREPARE statement can specify an existing statement identifier if you want to bind the identifier to a different SQL statement text.

Теперь я выделил только одно слово, хотя важным является всё предложение.
Fet Frumos (не перевелись богатыри в земле русской, пусть даже и болгарские! :) ),
как раз и попробовал на разных итерациях цикла с помощью повторного PREPARE связывать (bind) один и тот же идентификатор (identifier: "tmp") с разными SQL утверждениями в строке "sql" (different SQL statement text).
Тяжело предположить, что "new" из документации предполагает только несколько утверждений PREPARE, и не предполагает использование одного и того же утверждения с PREPARE на разных итерациях цикла .

Лена Владыкина

...you can use a question-mark ( ? ) placeholder where a parameter
must be supplied when the statement is executed.


"can use" - не "must use"...

Лена Владыкина

Насколько это этично, давать программистам возможность пользоваться динамическим SQL, но при этом предполагать возможность, что они будут спотыкаться?
Как по мне, ну и ничего особо страшного в этом нет:-)


Никто и не спорит, фича новая, разработчик же должен был споткнуться :) Чуть-чуть обидно, что это оказался не разработчик из IBM :).

Лена Владыкина

p.s. Толик, .... я остаюсь ... поклонницей ... :-)))

Блин, где здесь кнопка с красными щёчками?!
21 апр 10, 09:56    [8660835]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Daugava
Member

Откуда: Riga-Kiev-Gurzuf
Сообщений: 718
Вообще-то, персонажа румынских народных сказок Фет Фрумоса можно назвать молдаванином. С натяжкой даже украинцем (есть на Буковине и украинские сказки о нем). Ну никак не болгарин :).
21 апр 10, 15:22    [8664222]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Daugava и Fet Frumos, я каюсь за столь раннее обретение склероза .
Я, правда, в районе обеда даже вспомнил про эту разницу - но мини-командировка по Киеву не дала мне вовремя извиниться.

В общем, оговорка получилась прям по фильму:

к/ф Брат
- Киркоров мне не нравится - весь слащавый, напудренный как баба, одним словом - румын.
- Так ведь он же болгарин!
- Да?! А какая разница? ..


П.С.:
Съездить на майские праздники в Европу и посмотреть в чём разнмица, что-ли?! :)
21 апр 10, 17:36    [8665704]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Fet Frumos
Member

Откуда:
Сообщений: 113
Был в отпуске. Дальнейший результат экспериментов неутешителен, на одном сервере ХП работает, а если ХП запускаю на одном сервере, а обращаюсь к базе расположенной на другом то процедура вываливается с ошибкой "-9791 SQL error: User Defined Routine (importdb_t2) execution failed." :(. Видимо придется отказаться от использования в моей задаче Dynamic SQL .
7 май 10, 14:59    [8742664]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Fet Frumos
Member

Откуда:
Сообщений: 113
Кстати, Fet Frumos - древнеукраиский богатырь (согласно исследований украинских историков) .
7 май 10, 15:04    [8742750]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Fet Frumos
Member

Откуда:
Сообщений: 113
Дополнение: ошибка появляется как раза при выполнении "проблемного участка"
 let sql ="execute procedure  "||  alias||"myspl( ? ); ";
    PREPARE tmp  FROM sql;
foreach
  select id_val  into i_val
  from  mytable 
    let  i1, i2, i3 = 0,0,0;
    DECLARE cur cursor FOR tmp;
    OPEN cur using i_val;
    FETCH  cur INTO i_i1, i_i2, i_i3;
    CLOSE cur;
    FREE cur;

если его закоментить то работает ок
7 май 10, 15:29    [8742951]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Fet Frumos
Дополнение: ошибка появляется как раза при выполнении "проблемного участка"
...
если его закоментить то работает ок


весь отображённый участок - проблемный?
7 май 10, 16:51    [8743701]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
ОФ:
Fet Frumos
Кстати, Fet Frumos - древнеукраиский богатырь (согласно исследований украинских историков) .


Он же - молдавский :). Старые добрые сказки...

Шварц - богатырь предыдущего поколения, и Фредди - его ужас :) (хотя похоже, и нынешнему достанется :)
7 май 10, 16:55    [8743738]     Ответить | Цитировать Сообщить модератору
 Re: Dynamic SQL, проблема с курсором  [new]
Fet Frumos
Member

Откуда:
Сообщений: 113
Ошибка вываливается при вызове ХП с параметром. Получаю ошибки
05/07/10 11:58:59  Assert Failed: Exception Caught. Type: MT_EX_OS, Context: mem
05/07/10 11:58:59  IBM Informix Dynamic Server Version 11.50.FC6TL
05/07/10 11:58:59   Who: Session(9356, my@my, 4040, 0x98f87b60)
                Thread(60191, sqlexec, 98f49918, 1)
                File: mtex.c Line: 417
05/07/10 11:58:59   Action: Please notify IBM Informix Technical Support.
05/07/10 12:05:12  (-9791): ERROR: Routine execution trap -- procname=<myspl> procid=1123
    reason: mem
12 май 10, 10:56    [8758702]     Ответить | Цитировать Сообщить модератору
Все форумы / Informix Ответить