VPF::[InterBase] Поисковый запрос с параметрами

Gamber

Дата 21.10.2008, 08:32 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 60
Регистрация: 24.3.2008

Репутация: нет
Всего: нет

Всем доброго времени суток помогите пожалуйста разобраться...

Есть БД "Библиотека" в оболочке которая реализуется под эту базу я создаю параметризированный SELECT-запрос для поиска книги, в качестве параметров могут использоваться: название книги, имя автора, название издательства, название книготорговой фирмы значение параметров задается пользователем в обычных edit полях...

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

Код


      IBQSearchByName.Params[0].Value := fmSearchQuery.edBook.Text;
      IBQSearchByName.Params[1].Value := fmSearchQuery.edAuthor.Text;
      IBQSearchByName.Params[2].Value := fmSearchQuery.edPublisher.Text;
      IBQSearchByName.Params[3].Value := fmSearchQuery.edFirm.Text;      
      IBQSearchByName.Close;
      IBQSearchByName.Open;
      if fmSearchQuery.ehSearchQuery.SumList.RecordCount = 0 then
      ShowMessage('По Вашему запросу ничего не найдено');

А вот с SQL запросом возникили проблемы, сначала писал:

Код


SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book
FROM books, authors, publishes, firms
WHERE
authors.kod_avt = books.kod_avt AND
publishes.kod_izd = books.kod_izd AND
firms.kod_firm = books.kod_firm AND
(name_book = :name_book OR
name_avt = :name_avt OR
name_izd = :name_izd OR
name_firm = :name_firm)
ORDER BY name_book

В этом случае получал из-за "OR" получался не уточняющий поиск, а обобщающий (весь Пушкин, весь Питер), при замене на "AND" работает лучше, но необходимо вводить все параметры иначе поиск не осуществится...

Предложили попробовать сделать так:

Код


SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book
FROM books, authors, publishes, firms
WHERE
authors.kod_avt = books.kod_avt AND
publishes.kod_izd = books.kod_izd AND
firms.kod_firm = books.kod_firm AND
(name_book = :name_book or :name_book is null) and 
(name_avt = :name_avt or :name_avt  is null) and 
(name_izd = :name_izd or :name_izd is null) and 
(name_firm = :name_firm or :name_firm is null)
ORDER BY name_book

Сделал, но теперь получаю ошибку от сервера:

Код

'Dynamic SQL Error SQL error code =-804 Data type unknown'

На данный момент для реализации поставленной задачи получается использовать только отдельные запросы с одним параметром (поиск по названию книги - один запрос с одним параметром типа:

Код

SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book
FROM books, authors, publishes, firms
WHERE
authors.kod_avt = books.kod_avt AND
publishes.kod_izd = books.kod_izd AND
firms.kod_firm = books.kod_firm AND
name_book = :name_book
ORDER BY name_book

и т.д

Минусы очевидны: много практически идентичных запросов, нет возможности уточняющего поиска по нескольким параметрам (т.е нельзя искать все книги Пушкина изданные издательством "Питер", к примеру, а можно искать только все книги Пушкина или все книги изданные издательством "Питер")...

Может все-таки есть решение этой проблемы? Подскажите пожалуйста...

Это сообщение отредактировал(а) Gamber - 21.10.2008, 08:33

Deniz

Дата 21.10.2008, 08:58 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1251
Регистрация: 16.10.2004
Где: Новый Уренгой

Репутация: 8
Всего: 44

Код

...
and
(name_book = :name_book or :name_book is null)
and
...

нормальный вариант, и он работает(напиши его в IBExpert и посмотри), ищи причины ошибки.
Минусы: использование индексов зависит от ума оптимизатора.

Есть еще вариант динамически формировать запрос на клиенте:

Код


  if fmSearchQuery.edBook.Text <> '' then
    IBQSearchByName.SQL.Add('  and (name_book = :name_book) ');
  ...
  IBQSearchByName.Prepare;
  if fmSearchQuery.edBook.Text <> '' then
    IBQSearchByName.ParamByName('name_book').AsString := fmSearchQuery.edBook.Text;
  ...

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

--------------------

"Для того чтобы сделать шаг вперед, достаточно пинка сзади" (с)

vovk

Дата 21.10.2008, 10:18 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 157
Регистрация: 28.2.2008

Репутация: 6
Всего: 6

А Like не подходит?

Frees

Дата 21.10.2008, 10:55 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 2233
Регистрация: 2.12.2005
Где: Екатеринбург

Репутация: 23
Всего: 54

Цитата(Gamber @ 21.10.2008, 10:32

)

'Dynamic SQL Error SQL error code =-804 Data type unknown'

Код


SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book
FROM books, authors, publishes, firms
WHERE
authors.kod_avt = books.kod_avt AND
publishes.kod_izd = books.kod_izd AND
firms.kod_firm = books.kod_firm AND
(name_book = cast(:name_book as varchar(100)) or :name_book is null) and 
(name_avt = cast(:name_avt as varchar(100)) or cast(:name_avt as varchar(100))   is null) and 
-- и тд...
ORDER BY name_book

ошибка должна пропасть

Добавлено через 4 минуты и 20 секунд

Цитата(Deniz @ 21.10.2008, 10:58

)

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

думеш есть смысл индексировать по этим полям?

Это сообщение отредактировал(а) Frees - 21.10.2008, 10:58

--------------------

Кольцов Виктор Владимирович

Gamber

Дата 21.10.2008, 11:05 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 60
Регистрация: 24.3.2008

Репутация: нет
Всего: нет

Цитата(Deniz @ 21.10.2008, 08:58)

[code=sql]...
Есть еще вариант динамически формировать запрос на клиенте:

Код


  if fmSearchQuery.edBook.Text <> '' then
    IBQSearchByName.SQL.Add('  and (name_book = :name_book) ');
  ...
  IBQSearchByName.Prepare;
  if fmSearchQuery.edBook.Text <> '' then
    IBQSearchByName.ParamByName('name_book').AsString := fmSearchQuery.edBook.Text;
  ...

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

Решил сделать динамически... Для начала для пары полей (книга и автор):

Код


// Код на открытие формы запрса (грид, поля ввода параметров, кнопки)

begin 
  gbBooks.Refresh;
  fmSearchQuery.ShowModal;
  fmSearchQuery.edit1.Clear;
  fmSearchQuery.edit2.Clear;
end;

// Код на кнопку выполнить запрос

 begin
      fmSearchQuery.ehSearchQuery.DataSource := dmBase.dsSearchByName;
      IBQSearchByName.Close;
          if fmSearchQuery.Edit1.Text <> '' then
               begin
                   IBQSearchByName.SQL.Add(' AND name_avt = :name_avt ');
                   IBQSearchByName.ParamByName('name_avt').AsString := fmSearchQuery.Edit1.Text;
               end;
          if fmSearchQuery.Edit2.Text <> '' then
               begin
                   IBQSearchByName.SQL.Add(' AND name_book = :name_book ');
                   IBQSearchByName.ParamByName('name_book').AsString := fmSearchQuery.Edit2.Text;
               end;
          IBQSearchByName.Prepare;
          IBQSearchByName.Open;
          fmSearchQuery.Edit2.Text := '';
          fmSearchQuery.Edit1.Text := '';
          if fmSearchQuery.ehSearchQuery.SumList.RecordCount = 0 then
               ShowMessage('По Вашему запросу ничего не найдено');

SQL Запрос вот такой:

Код


SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book
FROM books, authors, publishes, firms
WHERE
authors.kod_avt = books.kod_avt AND
publishes.kod_izd = books.kod_izd AND
firms.kod_firm = books.kod_firm

Столкнулся с двумя проблемами:

1. При открытии окна запроса, сразу исполняется SQL запрос, полная выборка из базы... Можно ли это предотвратить и сделать так, чтобы выводились данные в грид только после нажатия на кнопку "Выполнить запрос"?

2. Допустим я ввел: "Сказки", пустой ответ (ничего не найдено), хотя две таких книги есть Пушкина и Гоголя, ввел в поле автора Гоголь увидел книгу, ввел Пушкина тоже OK...
И например так ввел Пушкин увидел все его книги, ввел Сказки увидел его книгу, поменял на гоголя тоже нормально, а начинаю вновь вводить просто Пушкин, вижу только Сказки...

Вообщем что-то я запутался... Подскажите пожалуйста где я мог ошибиться?

Frees, та же самая ошибка...

Это сообщение отредактировал(а) Gamber - 21.10.2008, 11:14

Frees

Дата 21.10.2008, 11:27 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 2233
Регистрация: 2.12.2005
Где: Екатеринбург

Репутация: 23
Всего: 54

Цитата(Gamber @ 21.10.2008, 13:05

)

та же самая ошибка...

cast может не у всех параметров стоит
зы динамичесски лучше

Цитата(Gamber @ 21.10.2008, 13:05

)

1. При открытии окна запроса, сразу исполняется SQL запрос, полная выборка из базы... Можно ли это предотвратить и сделать так, чтобы выводились данные в грид только после нажатия на кнопку "Выполнить запрос"?

IBQSearchByName.active false в свойствах поставь

когда на кнопку нажмеш он выполниться

по поводу 2 вопрса попробуй так -

Код


// Код на кнопку выполнить запрос

 begin
      fmSearchQuery.ehSearchQuery.DataSource := dmBase.dsSearchByName;
      IBQSearchByName.Close;
     IBQSearchByName.SQL.text := 'SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book FROM books, authors, publishes, firms  '
          if fmSearchQuery.Edit1.Text <> '' then
               begin
                   IBQSearchByName.SQL.Add(' where name_avt = '#39+ fmSearchQuery.Edit1.Text+#39);
               end;
          if fmSearchQuery.Edit2.Text <> '' then
               begin
                  if fmSearchQuery.Edit1.Text <> '' 
                    then BQSearchByName.SQL.Add(' AND ')
                    else BQSearchByName.SQL.Add(' where ')
                   IBQSearchByName.SQL.Add(' name_book = '#39+fmSearchQuery.Edit2.Text+#39);
               end;
          IBQSearchByName.Prepare;
          IBQSearchByName.Open;
         
         // fmSearchQuery.Edit2.Text := ''; также не удобна если условия поиска будут очищаться
         // fmSearchQuery.Edit1.Text := '';
          if IBQSearchByName.RecordCount = 0 then
               ShowMessage('По Вашему запросу ничего не найдено');

Это сообщение отредактировал(а) Frees - 21.10.2008, 11:33

--------------------

Кольцов Виктор Владимирович

Gamber

Дата 21.10.2008, 11:42 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 60
Регистрация: 24.3.2008

Репутация: нет
Всего: нет

Код


 IBQSearchByName.SQL.text := 'SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book FROM books, authors, publishes, firms  '

А разве не нужно еще дописать связь?

Код


WHERE
authors.kod_avt = books.kod_avt AND
publishes.kod_izd = books.kod_izd AND
firms.kod_firm = books.kod_firm

Frees

Дата 21.10.2008, 11:45 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 2233
Регистрация: 2.12.2005
Где: Екатеринбург

Репутация: 23
Всего: 54

Цитата(Gamber @ 21.10.2008, 13:42

)

А разве не нужно еще дописать связь?

да нужно не заметил просто

Добавлено @ 11:47
вот так значит

Код


//......
     IBQSearchByName.SQL.text := 'SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book FROM books, authors, publishes, firms  WHERE '+
' authors.kod_avt = books.kod_avt AND '+
 ' publishes.kod_izd = books.kod_izd AND '+
' firms.kod_firm = books.kod_firm '
          if fmSearchQuery.Edit1.Text <> '' then
               begin
                   IBQSearchByName.SQL.Add(' AND name_avt = '#39+ fmSearchQuery.Edit1.Text+#39);
               end;
          if fmSearchQuery.Edit2.Text <> '' then
               begin
                   IBQSearchByName.SQL.Add(' AND name_book = '#39+fmSearchQuery.Edit2.Text+#39);
               end;
//.....

Это сообщение отредактировал(а) Frees - 21.10.2008, 11:48

--------------------

Кольцов Виктор Владимирович

Gamber

Дата 21.10.2008, 12:07 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 60
Регистрация: 24.3.2008

Репутация: нет
Всего: нет

Код


begin
   if fmSearchQuery.SearchMode.ItemIndex = 1 then
    begin
     fmSearchQuery.ehSearchQuery.DataSource := dmBase.dsSearchByName;
     IBQSearchByName.Close;
     IBQSearchByName.SQL.text := 'SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book FROM books, authors, publishes, firms  WHERE ' + ' authors.kod_avt = books.kod_avt AND '+ ' publishes.kod_izd = books.kod_izd AND ' + ' firms.kod_firm = books.kod_firm ';
     if fmSearchQuery.Edit1.Text <> '' then
        begin
          IBQSearchByName.SQL.Add(' AND name_avt = '#39+ fmSearchQuery.Edit1.Text+#39);
        end;
     if fmSearchQuery.edValue.Text <> '' then
        begin
          IBQSearchByName.SQL.Add(' AND name_book = '#39+fmSearchQuery.edValue.Text+#39);
        end;
     if fmSearchQuery.edValue.Text <> '' then
        begin
          if fmSearchQuery.Edit1.Text <> '' then
            IBQSearchByName.SQL.Add(' AND ')
          else
            IBQSearchByName.SQL.Add(' where ');
          IBQSearchByName.SQL.Add(' name_book = '#39+fmSearchQuery.edValue.Text+#39);
        end;
     IBQSearchByName.Prepare;
     IBQSearchByName.Open;
     if IBQSearchByName.RecordCount = 0 then
        ShowMessage('По Вашему запросу ничего не найдено');
  end;

Не хочет работать... Запрос вообще не выполняется... Ругается "Empty SQL Statment"

Frees

Дата 21.10.2008, 12:12 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 2233
Регистрация: 2.12.2005
Где: Екатеринбург

Репутация: 23
Всего: 54

Код


begin
   if fmSearchQuery.SearchMode.ItemIndex = 1 then
    begin
     fmSearchQuery.ehSearchQuery.DataSource := dmBase.dsSearchByName;
     IBQSearchByName.Close;
     IBQSearchByName.SQL.text := 'SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book FROM books, authors, publishes, firms  WHERE ' + ' authors.kod_avt = books.kod_avt AND '+ ' publishes.kod_izd = books.kod_izd AND ' + ' firms.kod_firm = books.kod_firm ';
     if fmSearchQuery.Edit1.Text <> '' then
        begin
          IBQSearchByName.SQL.Add(' AND authors.name_avt = '#39+ fmSearchQuery.Edit1.Text+#39);
        end;
     if fmSearchQuery.edValue.Text <> '' then
        begin
          IBQSearchByName.SQL.Add(' AND books.name_book = '#39+fmSearchQuery.edValue.Text+#39);
        end;
 {    if fmSearchQuery.edValue.Text <> '' then //это уже не надо
        begin
          if fmSearchQuery.Edit1.Text <> '' then
            IBQSearchByName.SQL.Add(' AND ')
          else
            IBQSearchByName.SQL.Add(' where ');
          IBQSearchByName.SQL.Add(' name_book = '#39+fmSearchQuery.edValue.Text+#39);
        end; }
     IBQSearchByName.Prepare;
     IBQSearchByName.Open;
     if IBQSearchByName.RecordCount = 0 then
        ShowMessage('По Вашему запросу ничего не найдено');
  end;

IBQSearchByName - это что за компонент?

Это сообщение отредактировал(а) Frees - 21.10.2008, 12:14

--------------------

Кольцов Виктор Владимирович

Gamber

Дата 21.10.2008, 12:13 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 60
Регистрация: 24.3.2008

Репутация: нет
Всего: нет

Цитата(Frees @ 21.10.2008, 12:12)

IBQSearchByName - это что за компонент?

IBQuery

vovk

Дата 21.10.2008, 12:14 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 157
Регистрация: 28.2.2008

Репутация: 6
Всего: 6

Код


SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book
FROM books, authors, publishes, firms
WHERE
authors.kod_avt = books.kod_avt AND
publishes.kod_izd = books.kod_izd AND
firms.kod_firm = books.kod_firm AND
(name_book Like "'+fmSearchQuery.edBook.Text+'%") and 
(name_avt Like "'+fmSearchQuery.edAuthor.Text+'%") and 
(name_izd Like "'+ fmSearchQuery.edPublisher.Text+'%") and 
(name_firm Like "'+fmSearchQuery.edFirm.Text+'%")
ORDER BY name_book

А в таком виде не пойдёт?

Единственно что будет отсеивать записи с пустыми значениями из этих полей

Это сообщение отредактировал(а) vovk - 21.10.2008, 12:17

Gamber

Дата 21.10.2008, 12:19 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 60
Регистрация: 24.3.2008

Репутация: нет
Всего: нет

Цитата(vovk @ 21.10.2008, 12:14)

Код


SELECT name_book, name_avt, name_izd, name_firm, god_izd, price_book
FROM books, authors, publishes, firms
WHERE
authors.kod_avt = books.kod_avt AND
publishes.kod_izd = books.kod_izd AND
firms.kod_firm = books.kod_firm AND
(name_book Like "'+fmSearchQuery.edBook.Text+'%") and 
(name_avt Like "'+fmSearchQuery.edAuthor.Text+'%") and 
(name_izd Like "'+ fmSearchQuery.edPublisher.Text+'%") and 
(name_firm Like "'+fmSearchQuery.edFirm.Text+'%")
ORDER BY name_book

А в таком виде не пойдёт?

Единственно что будет отсеивать записи с пустыми значениями из этих полей

Нет врядли...

Gamber

Дата 21.10.2008, 12:44 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 60
Регистрация: 24.3.2008

Репутация: нет
Всего: нет

ВСЕ! Вроде бы заработало smile

Frees, Большое спасибо!

Это сообщение отредактировал(а) Gamber - 21.10.2008, 12:50

Deniz

Дата 21.10.2008, 13:34 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1251
Регистрация: 16.10.2004
Где: Новый Уренгой

Репутация: 8
Всего: 44

Цитата(Frees @ 21.10.2008, 15:12

)

IBQSearchByName.SQL.Add(' AND books.name_book = '#39+fmSearchQuery.edValue.Text+#39);

вот такое категорически не надо делать.

Цитата(Gamber @ 21.10.2008, 15:44

)

ВСЕ! Вроде бы заработало

введи в поле поиска по книге одинарную кавычку

--------------------

"Для того чтобы сделать шаг вперед, достаточно пинка сзади" (с)

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Delphi: Базы данных и репортинг \| Следующая тема »