Динамический запрос и переменные.

[kibernetics]

Парни подскажите строчки кода для решения проблемы с созданием динамических запросов. Есть АкцессБД управляемая из VB-клиента.

В клиенте есть четыре текстовых поля, которые играют роль в запросе. Изначально, если ни одно из полей незаполнено, то запрос выглядит так:

Код: Выделить всё

"SELECT * FROM [main]"

Дальше я пытаюсь проверять нет ли текста в text1, и если есть то запрос нужно изменить.

Код: Выделить всё


Dim strParam1 as String
Dim strSQL as String
strSQL = "SELECT * FROM [main]"
If Text1.Text <> "" Then
strParam1 = Text1.Text
strSQL = strSQL & "WHERE [partNum] Like " & strParam1
End If

Так вроде код работает, незнаю правильный ли синтаксис конечно использую... Проблема в дальнейшем. Как прикрутить еще параметры к запросу в ввиде переменных?
Допустим, если есть текст еще и в Text2, то strParam2 = Text2.Text и теперь мне нужно к запросу strSQL = strSQL & "WHERE [partNum] Like " & strParam1 прибавить еще "фильтр" в поле [Descr] Like strParam2.
Т.е. по идее должно получиться что-то вроде:

Код: Выделить всё


"SELECT * FROM [main] WHERE [partNum] Like " & strParam1 & WHERE [Descr] Like strParam2"

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

[GSerg]

Тебе знаком оператор AND?

[kibernetics]

знаком, знаком... но какого же его применение? GSerg будьте любезны - приведите пример. Я уже пробовал и AND и & но такое ощущение, что у меня проблема с кавычками и апострофами

[GSerg]

Ты умеешь срастить две строки в одну, вставив между ними пробел?

[kibernetics]

"string " & "string"

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

[Andrey Fedorov]

Код: Выделить всё

"SELECT * FROM [main] WHERE [partNum] Like " & strParam1 & WHERE [Descr] Like strParam2"

Следи за ручками (то бишь скобками):

Код: Выделить всё

s = "SELECT * FROM [main] WHERE [partNum] Like "" & Replace(strParam1, """", """""") & "" AND [Descr] Like "" &  Replace(strParam2, """", """""") & """"

[kibernetics]

о-о...
надо обмозговать...
и тем неменее уже что-то похоже видимо..., спасибо.

а еще интересно, почему если у меня в запросе:
txtPN.Text = "ZEW5699*"
strSQL & " WHERE [partNum] Like " & "'" & txtPN.Text & "'"
то ничего не запрашивается почему-то. но, если ввести весь партномер(ZEW569934578, без звёздочки), то всё работает отлично. такое чувство, что не прорабатывается *(звёздочка).
как правильно запрашивать по началу слова?

[GSerg]

R, наконец, TFM, kibernetics.
http://bbs.vbstreets.ru/viewtopic.php?p=159428#159428


А также http://bbs.vbstreets.ru/viewtopic.php?t=20384

[kibernetics]

[offtopic]а чё это вы в субботу и не отдыхаете?[/offtopic]

[kibernetics]

спасибо большое. наконец-то дело двинулось.
а то я приболел, пришлось работу на дом брать.

[GSerg]

А мы и в воскресенье не отдыхаем.

[tyomitch]

[оффтоп]
А мы и не напрягаемся...
[/оффтоп]

[kibernetics]

странный всё-таки этот Replace. он получается, что просто высвобождает от кавычек. а требуется заключение в одинарные кавычки внутри двойных. Вот что есть для задачи:
1. Константа "[partNum]"
2. Константа "[Descr]"
3. Переменная "strParam1"
4. Переменная "strParam2"
5. Переменная "strSQL"

Нужно чтоб на выходе переменная strSQL имела примерно такой вид:

Код: Выделить всё

strSQL = "SELECT * FROM [main] WHERE [partNum] Like 'strParam1' AND WHERE [Descr] Like 'strparam2'"

Как это реализовать в виде переменных с сохранением синтаксиса?

[GSerg]

kibernetics, давай ты сразу будешь делать всё правильно?
Раз у тебя сращение двух строк в одну вызывает проблемы, может правильный подход поможет.
Да ещё и WHERE повторил, ужас какой... Не пробовал читать документацию?

Код: Выделить всё

dim c as adodb.command, r as adodb.recordset

set c=new adodb.command
set c.activeconnection = conn
c.commandtype=adcmdtext
c.commandtext="SELECT * FROM [main] WHERE [partNum] Like ? AND [Descr] Like ?"
c.parameters.add c.createparameter("strParam1", advarwchar, adparaminput, 255, strParam1)
c.parameters.add c.createparameter("strParam2", advarwchar, adparaminput, 255, strParam2)

set r=new adodb.recordset
r.open c,,adopenstatic,adlockreadonly

[Andrey Fedorov]

Как это реализовать в виде переменных с сохранением синтаксиса?

А если ушлый пользователь введет в строке запроса одинарную или двойную кавычки - что у тебя в итоге будет?

[kibernetics]

GSerg
я же и прошу помощи в этом направление. да, у меня вызывает трудности сращение двух строк, как оказалось. собственно, если бы изначально видеть причину проблему, то и пост бы не создавал видимо. я и пытался подвести форумчан именно к реализации варианта создания переменной строки для запроса. и тут как оказалось есть супер "набивочный" вариант. ведь, кто-то повидал поболее моего и сталкивался явно с подобным. извини.

А если ушлый пользователь введет в строке запроса одинарную или двойную кавычки - что у тебя в итоге будет?

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

[GSerg]

я же и прошу помощи в этом направление. да, у меня вызывает трудности сращение двух строк, как оказалось.

Так правильно, надо сначала устранить их, в отрыве от SQL. А потом вернуться к SQL.

я и пытался подвести форумчан именно к реализации варианта создания переменной строки для запроса.

"Мы стояли на краю пропасти - с тех пор мы сделали большой шаг вперёд!"
Создание переменной строки для запроса так, как ты пытаешься создать её в этом топике есть самая главная, притом что самая простая, дыра в безопасности, через которую с твоей базой сделают всё, что хотят.
Подводить к этому кого-либо неполезно.

Андрон, это мелочи жизни

Гы гы гы...
См, к примеру, http://blogs.gotdotnet.ru/personal/gaid ... 2FAADF2445
Или http://www.joelonsoftware.com/items/2006/11/01.html

[Andrey Fedorov]

Гы гы гы...

Да, кстати, у меня уже exe-шник и все компоненты при запуске программы на CRS32 проверяются - во избежание их любой модификации...

[kibernetics]

Unfortunately it's a gigantic security hole called SQL injection.

возбуждено к сведению.

[kibernetics]

GSerg
а в параметры можно также добавлять и поля в которых будет выполнятся поиск параметра?

как в вашем примере задействовать также и имя поля (например, [partNum]) в качестве параметра? и как определить какой ?принадлежит какому по счету параметру? извините. но уж очень интересно

Код: Выделить всё

c.commandtext="SELECT * FROM [main] ? Like ? AND [Descr] Like ?"
c.parameters.append c.createparameter("strParam0", advarwchar, adparaminput, 255, WHERE [partNum])
c.parameters.append c.createparameter("strParam1", advarwchar, adparaminput, 255, strParam1)
c.parameters.append c.createparameter("strParam2", advarwchar, adparaminput, 255, strParam2)
' можно ли прикрутить здесь [partNum] таким образом?

ничего не могу найти из информации по использованию. может R TFM ните?

[Oxygen]

Ты вообще понимаешь, что ты пишешь? Почитай вначале учебник по SQL.
По существу:
1. Задать имя в качестве параметра нельзя.Если ты внимательно прочитаешь описание функции createparameter, то тебе это и самому станет понятно. Для этой цели все же придется сращивать строки...
2. Использование Like увеличивает время выполнения запроса. Если ты ищешь конкретное значение, то лучше использовать столбец=значение, при этом, создай индекс.
3. WHERE так использовать нельзя! Опять же читай описание функции createparameter.

Искать в яндексе по запросу adodb createparameter Visual Basic . На первой же странице будет описание функции и допустимых параметров к ней.

[kibernetics]

я вижу, что уже начал раздражать тут некотрых личностей. хорошо, тогда скажите, может я неправильно всё изначально затеял?!
у меня есть примерно такая форма:


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

Код: Выделить всё

"SELECT * FROM [main]"

. Потом, допустим, в поле partNum юзер ввёл ZEW5699, теперь запрос должен иметь вид:

Код: Выделить всё

"SELECT * FROM [main] WHERE [partNum] Like" & 'значение из текстового поля'

.

так вот у меня вопрос. как предпочтительнее (и, главное, как?) реализовать алгоритм, который проверял бы наличие текста в текстовых строках и по результатам бы собирал строку для запроса. Это делать лучше конкатенацией или забивать командами с параметрами?

[alibek]

"SELECT * FROM [main] WHERE [partnum] LIKE '" & Replace$(Text1.Text,"'","''") & "'"

Только в таком виде LIKE бессмысленен. Используй лучше =.

[kibernetics]

alibek
а что значит:

Только в таком виде LIKE бессмысленен. Используй лучше =.

чем в корне отличаются Like и =?

если делать запрос с четырёх полей в таблице и искать в полях содержащие определённые буквы в слове, то что лучше применять Like или =?

[Ennor]

чем в корне отличаются Like и =?

Равенство, как следует из его названия, включает в финальную выборку только точные совпадения (+/- разница в регистре символов, но в данном случае это к делу не относится). Если твой юзер вколачивает в качестве критерия поиска точное название, скажем, модели - XYZ/123456 - то и в результатах он ожидает увидеть только эту модель.
А вот если ему нужны все модели, начинающиеся, допустим, с ABC1, то тогда нужно использовать Like, только в маску поиска следует добавить символы подстановки:

Код: Выделить всё

-- Begins with ABC1
where partnum like 'ABC1%'

-- Ends with ABC1
where partnum like '%ABC1'

-- May contain ABC1 anywhere inside the string
where partnum like '%ABC1%'

Соответственно, тебе как программеру нужно дать пользователю возможность как-то донести до программы, какой именно из этих 4 случаев его интересует. Посмотри, как в MS Excel реализован фильтр по столбцу - не самый лучший вариант, но в качестве отправной точки и для понимания базовых концепций сойдет.

Ну и конечно же, настоятельно рекомендуется открыть справку по той СУБД, с которой ты работаешь, и внимательно изучить синтаксис, используемый с Like. В каждом конкретном случае он может варьироваться.

[Oxygen]

2Ennor, не обязательно. Можно просто использовать partnum='%ABC1%'. Если верить тому, что написано в одной достаточно хорошей книжке по Oracle SQL - Запрос с Like работает в несколько раз медленнее, чем запрос с =, использованный с символами подстановки. Не знаю, как в других СУБД, но в Оракле это действительно так (экспериментально проверяла, когда оптимизировала свои запросы).

[alibek]

Ну это и логично. Особенно для %expr%, там даже индексы не используются. Для LIKE использовать выражения, отличные от expr%, нежелательно, особенно если выборка применяется к большим объемам данных.

[S397]

Интересно как продвинутый оракл определяет где использовать % в качестве шаблонного символа, а где как процент:
например
partnum = 'Васил%'
или
partnum = 'НДС 20%' ?

[Ennor]

Интересно как продвинутый оракл определяет где использовать % в качестве шаблонного символа, а где как процент

Не знаю, как в оракле, но в MSSQL в последнем случае символ процента (как и любой другой символ, имеющий специальное значение в маске) нужно заключить в квадратные скобки:

Код: Выделить всё

-- All strings beginning with 'ABC%11'
where PartNum Like 'ABC[%]11%'

Все это есть в документации.

[S397]

Ennor
Спасибо,но речь шла не об опреаторе "Like", а об операторе "=", относительно него мне не очень понятно, как использовать шаблонные символы с оператором сравнения.