Два почти одинаковых запроса выполняются по разному

← →
И. Павел © (2010-08-10 09:13) [0]

Здравствуйте.
Я работаю с Access в Delphi 7 через ADO, JET.
Первоначальный запрос (SELECT с несколькими LEFT JOIN) выполняется нормально и возвращает 3 записи. Если заменить прямую подстановку двух чисел в строку запроса (с помощью “+ IntToStr(…) +”) на параметры, то запрос возвращает пустую выборку (но если использовать любой из этих двух параметров, а второй IntToStr оставить, то запрос опять будет работать правильно).

Вот первоначальный запрос (он выполняется нормально – возвращает 3 записи):

DMQ.ADODS.Active := false;
DMQ.ADODS.CommandText :=
"SELECT j.kvartira, j.schet, j.familia + "" "" + j.imia + "" "" + j.otchestvo AS FIO, s1.summa AS s1_summa, n.summa AS n_summa, sum(o.summa) AS sum_o_summa, s2.summa AS s2_summa " +
"FROM (((nachisl AS n " +
"LEFT JOIN jilci AS j ON (j.id = n.IDJilci AND j.IDDoma = " + IntToStr(id) + ")) " +
"LEFT JOIN saldo AS s1 ON (s1.IDJilci = n.IDJilci AND s1.god = " + IntToStr(god) + " AND s1.mes = " + IntToStr(mes) + ")) " +
"LEFT JOIN saldo AS s2 ON (s2.IDJilci = n.IDJilci AND s2.god = " + IntToStr(NextGod) + " AND s2.mes = " + IntToStr(NextMes) + ")) " +
"LEFT join oplati AS o ON (o.IDJilci = n.IDJilci AND o.god = " + IntToStr(god) + " AND o.mes = " + IntToStr(mes) + ") " +
"WHERE n.god = " + IntToStr(god) + " AND n.mes = " + IntToStr(mes) + " " +
"GROUP BY n.id, j.kvartira, j.schet, j.familia + "" "" + j.imia + "" "" + j.otchestvo, s1.summa, n.summa, s2.summa;";
DMQ.ADODS.Parameters.ParseSQL(DMQ.ADODS.CommandText, true);
DMQ.ADODS.Open;

Вот запрос, который возвращает пустую выборку (этот запрос отличается от первоначального только тем, что два “+IntToStr(…)+” я заменил на параметры – в коде я их выделил жирным шрифтом):

DMQ.ADODS.Active := false;
DMQ.ADODS.CommandText :=
"SELECT j.kvartira, j.schet, j.familia + "" "" + j.imia + "" "" + j.otchestvo AS FIO, s1.summa AS s1_summa, n.summa AS n_summa, sum(o.summa) AS sum_o_summa, s2.summa AS s2_summa " +
"FROM (((nachisl AS n " +
"LEFT JOIN jilci AS j ON (j.id = n.IDJilci AND j.IDDoma = :PIDDoma)) " +
"LEFT JOIN saldo AS s1 ON (s1.IDJilci = n.IDJilci AND s1.god = " + IntToStr(god) + " AND s1.mes = " + IntToStr(mes) + ")) " +
"LEFT JOIN saldo AS s2 ON (s2.IDJilci = n.IDJilci AND s2.god = " + IntToStr(NextGod) + " AND s2.mes = " + IntToStr(NextMes) + ")) " +
"LEFT join oplati AS o ON (o.IDJilci = n.IDJilci AND o.god = " + IntToStr(god) + " AND o.mes = " + IntToStr(mes) + ") " +
"WHERE n.god = :PNGod AND n.mes = " + IntToStr(mes) + " " +
"GROUP BY n.id, j.kvartira, j.schet, j.familia + "" "" + j.imia + "" "" + j.otchestvo, s1.summa, n.summa, s2.summa;";
DMQ.ADODS.Parameters.ParseSQL(DMQ.ADODS.CommandText, true);
with DMQ.ADODS.Parameters.ParamByName("PIDDoma") do
begin
DataType := ftInteger;
Direction := pdInput;
Value := id;
end;
with DMQ.ADODS.Parameters.ParamByName("PNGod") do
begin
DataType := ftInteger;
Direction := pdInput;
Value := god;
end;

Если параметрами заменить не оба, а только один из “+IntToStr(…)+”, то запрос опять начнет работать правильно, возвращая 3 строки.

Подскажите, пожалуйста, в чем здесь ошибка? Скорее всего, это я что-то сделал не так, но, может быть, это все же Access или ADO?

Заранее спасибо.

← →
И. Павел © (2010-08-10 09:14) [1]

Здравствуйте.
Я работаю с Access в Delphi 7 через ADO, JET.
Первоначальный запрос (SELECT с несколькими LEFT JOIN) выполняется нормально и возвращает 3 записи. Если заменить прямую подстановку двух чисел в строку запроса (с помощью “+ IntToStr(…) +”) на параметры, то запрос возвращает пустую выборку (но если использовать любой из этих двух параметров, а второй IntToStr оставить, то запрос опять будет работать правильно).

Вот первоначальный запрос (он выполняется нормально – возвращает 3 записи):

DMQ.ADODS.Active := false; DMQ.ADODS.CommandText := "SELECT j.kvartira, j.schet, j.familia + "" "" + j.imia + "" "" + j.otchestvo AS FIO, s1.summa AS s1_summa, n.summa AS n_summa, sum(o.summa) AS sum_o_summa, s2.summa AS s2_summa " + "FROM (((nachisl AS n " + "LEFT JOIN jilci AS j ON (j.id = n.IDJilci AND j.IDDoma = " + IntToStr(id) + ")) " + "LEFT JOIN saldo AS s1 ON (s1.IDJilci = n.IDJilci AND s1.god = " + IntToStr(god) + " AND s1.mes = " + IntToStr(mes) + ")) " + "LEFT JOIN saldo AS s2 ON (s2.IDJilci = n.IDJilci AND s2.god = " + IntToStr(NextGod) + " AND s2.mes = " + IntToStr(NextMes) + ")) " + "LEFT join oplati AS o ON (o.IDJilci = n.IDJilci AND o.god = " + IntToStr(god) + " AND o.mes = " + IntToStr(mes) + ") " + "WHERE n.god = " + IntToStr(god) + " AND n.mes = " + IntToStr(mes) + " " + "GROUP BY n.id, j.kvartira, j.schet, j.familia + "" "" + j.imia + "" "" + j.otchestvo, s1.summa, n.summa, s2.summa;"; DMQ.ADODS.Parameters.ParseSQL(DMQ.ADODS.CommandText, true); DMQ.ADODS.Open;

Вот запрос, который возвращает пустую выборку (этот запрос отличается от первоначального только тем, что два “+IntToStr(…)+” я заменил на параметры – в коде я их выделил жирным шрифтом):

DMQ.ADODS.Active := false; DMQ.ADODS.CommandText := "SELECT j.kvartira, j.schet, j.familia + "" "" + j.imia + "" "" + j.otchestvo AS FIO, s1.summa AS s1_summa, n.summa AS n_summa, sum(o.summa) AS sum_o_summa, s2.summa AS s2_summa " + "FROM (((nachisl AS n " + "LEFT JOIN jilci AS j ON (j.id = n.IDJilci AND j.IDDoma = :PIDDoma)) " + "LEFT JOIN saldo AS s1 ON (s1.IDJilci = n.IDJilci AND s1.god = " + IntToStr(god) + " AND s1.mes = " + IntToStr(mes) + ")) " + "LEFT JOIN saldo AS s2 ON (s2.IDJilci = n.IDJilci AND s2.god = " + IntToStr(NextGod) + " AND s2.mes = " + IntToStr(NextMes) + ")) " + "LEFT join oplati AS o ON (o.IDJilci = n.IDJilci AND o.god = " + IntToStr(god) + " AND o.mes = " + IntToStr(mes) + ") " + "WHERE n.god = :PNGod AND n.mes = " + IntToStr(mes) + " " + "GROUP BY n.id, j.kvartira, j.schet, j.familia + "" "" + j.imia + "" "" + j.otchestvo, s1.summa, n.summa, s2.summa;"; DMQ.ADODS.Parameters.ParseSQL(DMQ.ADODS.CommandText, true); with DMQ.ADODS.Parameters.ParamByName("PIDDoma") do begin DataType := ftInteger; Direction := pdInput; Value := id; end; with DMQ.ADODS.Parameters.ParamByName("PNGod") do begin DataType := ftInteger; Direction := pdInput; Value := god; end;

Если параметрами заменить не оба, а только один из “+IntToStr(…)+”, то запрос опять начнет работать правильно, возвращая 3 строки.

Подскажите, пожалуйста, в чем здесь ошибка? Скорее всего, это я что-то сделал не так, но, может быть, это все же Access или ADO?

Заранее спасибо.

PS: уважаемые администраторы, удалите, пожалуйста, первый мой пост. Я там забыл отформатировать код.

← →
12 © (2010-08-10 09:22) [2]

тупое уточнение, а
DMQ.ADODS.Open; есть во второй раз?

← →
И. Павел © (2010-08-10 09:24) [3]

> а
> DMQ.ADODS.Open; есть во второй раз?

Да, есть. Я просто забыл его указать.

← →
sniknik © (2010-08-10 09:28) [4]

> DMQ.ADODS.Parameters.ParseSQL(DMQ.ADODS.CommandText, true);
эта строка в обоих случаях лишняя. в первом параметров нет, во втором есть но не отключено (не показано) авто определение. т.е. в одном случае работает вхолостую, в втором (судя по результату) создает дополнительный набор параметров.

> DataType := ftInteger;
> Direction := pdInput;
это тоже лишнее, тип получается из варианта т.е. присвоение Value := id; если id интеджер даст нужный тип. (вот если бы оно было строкой (?), то предварительное указание типа приведет к конвертации при присвоении другого. насколько помню)

← →
12 © (2010-08-10 09:29) [5]

попробуй пробелы везде добавить

> "[сюда]FROM (((nachisl AS n " +
> "[сюда]LEFT JOIN jilci AS j ON (j.id = n.IDJilci AND j.IDDoma

или подсветить окончательный sql в мемо, перетащить в А и выполнить

идея два
Все это
with DMQ.ADODS.Parameters.ParamByName("PNGod") do
begin
DataType := ftInteger;
Direction := pdInput;
Value := god;
end;
переписать просто как DMQ.ADODS.Parameters.ParamByName("PNGod").Value := god;

← →
И. Павел © (2010-08-10 09:35) [6]

> [4] sniknik © (10.08.10 09:28)

Спасибо. Просто у меня для задания текста запроса и параметров есть специальный модуль с функциями. Поэтому я вынес их код полностью.
Я установил ParamCheck = false и удалил задания типа и направления параметров, но ошибка осталась. Такой запрос тоже возвращает пустую выборку:\\

DMQ.ADODS.Active := false; DMQ.ADODS.CommandText := "SELECT j.kvartira, j.schet, j.familia + "" "" + j.imia + "" "" + j.otchestvo AS FIO, s1.summa AS s1_summa, n.summa AS n_summa, sum(o.summa) AS sum_o_summa, s2.summa AS s2_summa " + "FROM (((nachisl AS n " + "LEFT JOIN jilci AS j ON (j.id = n.IDJilci AND j.IDDoma = :PIDDoma)) " + "LEFT JOIN saldo AS s1 ON (s1.IDJilci = n.IDJilci AND s1.god = " + IntToStr(god) + " AND s1.mes = " + IntToStr(mes) + ")) " + "LEFT JOIN saldo AS s2 ON (s2.IDJilci = n.IDJilci AND s2.god = " + IntToStr(NextGod) + " AND s2.mes = " + IntToStr(NextMes) + ")) " + "LEFT join oplati AS o ON (o.IDJilci = n.IDJilci AND o.god = " + IntToStr(god) + " AND o.mes = " + IntToStr(mes) + ") " + "WHERE n.god = :PNGod AND n.mes = " + IntToStr(mes) + " " + "GROUP BY n.id, j.kvartira, j.schet, j.familia + "" "" + j.imia + "" "" + j.otchestvo, s1.summa, n.summa, s2.summa;"; DMQ.ADODS.Parameters.ParseSQL(DMQ.ADODS.CommandText, true); with DMQ.ADODS.Parameters.ParamByName("PIDDoma") do Value := id; with DMQ.ADODS.Parameters.ParamByName("PNGod") do Value := god; DMQ.ADODS.Open;

PS: еще пробовал удалить ParseSQL и выставить ParamCheck - но результат тот же.

← →
И. Павел © (2010-08-10 09:42) [7]

> [5] 12 © (10.08.10 09:29)

Поставил пробелы (в том числе и между параметром и скобкой, на всякий случай) и задал только Value для параметра - все равно ошибка осталась.

← →
sniknik © (2010-08-10 09:46) [8]

> Просто у меня для задания текста запроса и параметров есть специальный модуль с функциями.
не пользуйся модулем для тестов... тест это должно быть ровно то же самое что ты здесь показываешь.

> но результат тот же.
проверь еще раз. где то что то отличается. можно проверить прямо в дизайне, прописать один запрос увидеть свою пару записей, после прямо в нем заменить на параметры у них (в дизайне) поставить те же значения и посмотреть.

ну и из экзотики (было как то. глюк парсера в какой то версии дельфи видать. сам не получал но обсуждалось)
> = :PIDDoma)
вот тут попробуй поставить пробел между параметром и кавычкой.

← →
sniknik © (2010-08-10 09:48) [9]

> все равно ошибка осталась.
не ошибка, раз запрос выполняется. что то не так задал, раз результат не устраивает.

← →
И. Павел © (2010-08-10 10:35) [10]

Я попробовал ввести запрос в Access. Он спросил у меня значения обоих параметров и выполнил запрос. Вернул 3 строки, т.е. как и нужно.

Потом я создал новый проект и поместил на него только ADOConnection, ADODataSet, ADODataSource и Table. В ADODataSet.CommandText задал запрос (вместо параметров подставил свои значения: god = 2010, PIDDoma = 1) – запрос тоже выполнился нормально (вернул 3 записи).

Потом я заменил в CommandText числа ‘2010’ и ‘1’ на параметры, а значение и тип параметров указал в Design Time. В инспекторе объектов установил ADODataSet.Active:=true. И получилась пустая выборка...

Если у кого-нибудь из участников форума найдется время, пожалуйста, посмотрите мою тестовую программу. Она содержит два ADODatsSet, все параметры которых задаются в Design Time. Один из них (где запрос задается без параметров) возвращает 3 строки, а второй (где используются 2 параметра) - возвращает пустую выборку.

Тестовую программку я сохранил здесь:
http://narod.ru/disk/23584869000/Тестовая%20программа%20для%20Access.rar.html

← →
12 © (2010-08-10 10:37) [11]

> значение и тип параметров указал в Design Time

а если не указывать?

← →
И. Павел © (2010-08-10 10:39) [12]

> а если не указывать?

Пробовал не указывать тип параметров - оставить ftString - тогда тоже возвращается пустая выборка.
А если не указать значения параметров, то ADO ругается: "Отсутствует значение для одного или нескольких параметров".

← →
И. Павел © (2010-08-10 10:41) [13]

> > а если не указывать?

А если просто оставить параметры, созданные ADODataSet автоматически нетронутыми (т.е. со значением null), то тоже возвращается пустая выборка, как и в том случае, если задать значения параметров.

← →
12 © (2010-08-10 10:52) [14]

а если вообще никуда Design Time не смотреть,
создать динамически,
спросить что-то тупое (чтоб понять, что работает)
потом перезаписать sql с параметрами и спросить?

← →
И. Павел © (2010-08-10 11:14) [15]

>[14] 12 © (10.08.10 10:52)

Несколько запросов так переписывал - нормально работают. Да и сам этот запрос работает, если заменить параметрами все IntToStr, кроме последнего или первого.
Например, такой запрос нормально выполняется как с параметрами, так и без них:

"SELECT d.nazvanie, d.adres, sum(s1.summa) AS sum_s1_summa, sum(n.summa) AS sum_n_summa, sum(o.summa) AS sum_o_summa, sum(s2.summa) AS sum_s2_summa " "FROM ((((doma AS d " + "LEFT JOIN jilci AS j ON j.IDDoma = d.id) " + "LEFT JOIN saldo AS s1 ON (s1.IDJilci = j.id AND s1.god = :PS1God AND :PS1Mes)) " + "LEFT JOIN saldo AS s2 ON (s2.IDJilci = j.id AND s2.god = :PS2God AND s2.mes = :PS2Mes)) " + "LEFT join nachisl AS n ON (n.IDJilci = j.id AND n.god = :PNGod AND n.mes = :PNMes)) " + "LEFT join oplati AS o ON (o.IDJilci = j.id AND o.god = :POGod AND o.mes = :POMes) " + "GROUP BY d.id, d.nazvanie, d.adres;"

← →
sniknik © (2010-08-10 11:24) [16]

проблема скорее всего в том, что условие ограничения внесено в условие объединения...

т.е. j.IDDoma = :PIDDoma надо вынести в WHERE, тогда все работает.

← →
sniknik © (2010-08-10 11:25) [17]

> Например, такой запрос нормально выполняется как с параметрами, так и без них:
и все таки лучше разделять, "ограничители" от "обьеденителей".

sniknik ©
Большое Вам спасибо. Вынес в where и все заработало!
Но глобальное "опараметривание" запросов я все же делать не буду. Оказывается, это может привести к неожиданным результатам.

12 ©
Спасибо.

> Оказывается, это может привести к неожиданным результатам.
сам удивился. вообще считал подобное смешение "типов" влияющим только на понимание/ясность запросов, а получается может приводить и к логическим ошибкам... (хорошо, что не допускаю подобной путаницы... т.к. работаю с аксесс (mssql подобное понимает)

> и все таки лучше разделять, "ограничители" от "обьеденителей".

А как тогда можно будет построить запрос, эквивалентный такому:

SELECT *
FROM a
LEFT JOIN b ON b.id = a.id AND b.value = 100

Ведь если написать

SELECT *
FROM a
LEFT JOIN b ON b.id = a.id
WHERE b.value = 100

То этот запрос не вернет ничего, если не найдет строку с b.value = 100, а первый запрос все равно вернет запись.
Если написать:

SELECT *
FROM a
RIGHT JOIN b ON b.id = a.id
WHERE b.value = 100

то тоже может не вернутся ни одна запись, если найдется b.id = a.id, но, например, с b.value = 200, и эта запись отсеяться в предложении WHERE.

> SELECT *
> FROM a
> LEFT JOIN b ON b.id = a.id
> WHERE b.value = 100
>
> То этот запрос не вернет ничего, если не найдет строку с
> b.value = 100, а первый запрос все равно вернет запись.

какую!?!

> А как тогда можно будет построить запрос, эквивалентный такому:

> SELECT *
> FROM a
> LEFT JOIN b ON b.id = a.id AND b.value = 100

SELECT * FROM a LEFT JOIN (SELECT * FROM b WHERE ID = 1) b ON b.ID = a.ID

в принципе, такой даже работать должен быстрее (проверить бы на больших таблицах).

> какую!?!
;) вот поэтому и считаю подобные непонятными/"мутными"

> sniknik ©

Спасибо. Буду делать так. Для интереса протестирую - что быстрее.

> какую!?!

Я имел ввиду, что если таблица a имеет записи (пусть и с a.id <> b.id), то первый запрос вернет для каждой строки таблицы "a" по записи, у которой b.id = NULL.

> LEFT JOIN b ON b.id = a.id AND b.value = 100

плохая запись, имхо
на месте компилятора/интерпретатора я бы такие только за деньги пропускал :)

Два почти одинаковых запроса выполняются по разному Найти похожие ветки