При создании приложений, оперирующих базами данных, нередко возникают проблемы, связанные с организацией взаимодействия языка программирования с языком SQL. В статье раскрываются приемы использования SQL-запросов при работе Visual Basic 6.0 с библиотекой Microsoft DAO 3.51 Object Library.
Применение SQL-запросов в коде программы, работающей с базой данных, имеет свои преимущества — это просто, наглядно и весьма эффективно. Однако из-за различия типов данных Visual Basic и SQL при передаче переменных из кода программы в SQL-запрос могут возникать неприятные нюансы.
Начнем с простого случая. Предположим, что у нас имеется база данных, хранящаяся в файле DataBase. mdb, и в ней есть таблица tblAdres с полями fieldCity, fieldStreet, fieldHouse, fieldSurname и fieldTelefon. Требуется выбрать записи адресатов, проживающих, например, во Владивостоке.
Код Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Dim db As Database
'во всех примерах используется Microsoft DAO 3.51 Object LibraryDim rs As recordset
'в последующих примерах (для экономии места)'эти переменные не будут объявленыSet db = OpenDatabase (App.Path & "DataBase.mdb")
Set rs = db.OpenRecordset ("SELECT * FROM [tblAdres] WHERE [fieldCity] = Владивосток")
DoUntil rs.EOF
'предполагается, что в наличии есть элемент управления ListBox1,'куда мы и будем передавать записи
ListBox1.AddItem rs.Fields("fieldCity") & " " & rs.Fields("fieldStreet") & " " & _
rs.Fields("fieldHouse")& " " & _
rs.Fields("fieldSurname") & " " & rs.Fields("fieldTelefon") & vbCrLf
rs.MoveNext
Loop'в последующих примерах эти операторы будут опущены
rs.CloseSet rs = Nothing
db.CloseSet db = Nothing
Весь запрос заключен в двойные кавычки. Чтобы процессор БД «выявил» наличие в запросе переменной, ее следует вывести за кавычки. В нашем случае это делается так:
Код Visual Basic
1
2
3
4
5
6
Dim db As Database
Dim rs As Recordset
Dim strCity AsString
StrCity = "Владивосток"Set db = OpenDatabase (App.Path & "DataBase.mdb")
Set rs = db.OpenRecordset ("SELECT * FROM [tblAdres] WHERE [fieldCity] = " & strCity)
Данный запрос уже близок к готовности, но если вы попытаетесь его применить, то, скорее всего, получите сообщение об ошибке № 3061: Too few parameters. Expected 1. («Слишком мало параметров. Ожидался один»). Дело в том, что перед тем, как передать строковую переменную в SQL-запрос, ее нужно ограничить одинарными кавычками, для чего Дж. Мак-Манус рекомендует соответствующую функцию (Джеффри П. Мак-Манус. Обработка баз данных на Visual Basic 6. М.: Диалектика, 1999).
Это особенно удобно тогда, когда приходится передавать несколько строковых переменных в различные запросы. Итак, наш запрос примет вид:
Код Visual Basic
1
Set rs = db.OpenRecordset ("SELECT * FROM [tblAdres] WHERE [fieldCity] = " & Quote(strCity))
Данная строка кода, конечно, работает, но только нужно будет добавить в проект функцию Quote, а если вы не хотите это делать, то достаточно слегка «модернизировать» переменную strCity:
Теперь о том, как передать в запрос несколько переменных. Допустим, требуется выбрать записи по жителям Владивостока, носящим фамилию Иванов.
Код Visual Basic
1
2
3
4
5
6
7
8
Dim strCity AsStringDim strSurname AsStringDim strSQL AsString
strCity = "Владивосток"
strSurname = "Иванов"
strSQL = "SELECT * FROM [tblAdres] WHERE [fieldCity] = " & Quote(strCity) & _
" AND [fieldSurname] = " & Quote(strSurname)
Set rs = db.OpenRecordset(strSQL)
Строка запроса несколько усложнилась, но ее структура осталась такой же. В подобных случаях целесообразно разбить строку на несколько частей, иначе она не поместится на экране и работать с ней будет неудобно. Рекомендую разделить ее так, чтобы каждая SQL-директива находилась на новой строке. Это упростит редактирование запроса и поиск в нем ошибок.
Обратите внимание на пробел между кавычками и директивой AND. В случае использования переменных в запросе порой трудно понять, зачем он нужен, но без него вы получите сообщение об ошибке № 3131: Syntax error in FROM clause («Синтаксическая ошибка в пункте FROM»). Действительно, если еще раз взглянуть на SQL-запрос, написанный в одну строку (см. предыдущий пример), то нетрудно заметить, что без этого пробела директива AND «прилипнет» к переменной Quote(strCity) и приведет к ошибке. Чтобы оградить себя от подобных проблем, рекомендую ставить пробел перед всеми SQL-директивами.
Теперь посмотрим, как можно применить переменную в названии таблицы или поля, особенно если в этом названии имеются пробелы. Ограничить их одинарными кавычками не удастся — поступит сообщение об ошибке № 3450: Syntax error in query. Incomplete query clause («Синтаксическая ошибка в запросе. Неполная строка запроса»). В этом случае с помощью той же конкатенации строк нужно заключить переменную в квадратные скобки. Пусть наша таблица называется [tbl Adres] и имеет поля [field Surname], [field City], [field Street], [field House]. Нам потребуется выбрать адреса граждан c определенной фамилией, проживающих в городе, указанном пользователем. Добавим также в проект текстовые поля: txtPeople и txtCity. Как несложно увидеть, SQL-запрос полностью состоит из переменных (кроме директив самого запроса).
Рассмотрим еще несколько примеров. Допустим, у нас есть база данных, включающая таблицу tblOperations с полями [fieldNumber], [fieldData], [fieldNameCompany] и [fieldSum]. Первое поле содержит номер заказа, второе — дату, третье — имя компании, четвертое — сумму операции. Необходимо сформировать критерии выборки по имени компании, причем если поле не заполнено, то recordset должен включать полный набор записей, а если заполнено, — то и критерий выборки. В последнем случае вполне можно обойтись одним SQL-запросом. Опишем подробнее процесс формирования критерия. «Нарисуем» в форме текстовое поле txtCompanyName.
Код Visual Basic
1
2
3
4
5
6
7
8
9
10
Dim strCompanyName AsStringIf txtCompanyName.Text <> EmptyThen
strCompanyName = "=" & Quote(txtCompanyName.Text)
Else
strCompanyName = "is not Null"'если, конечно, вы уверены, что 'в базе данных нет пустых записейEndIfSet rs.OpenDatabase ("SELECT * " & " FROM tblOperations " & _" WHERE [fieldNameCompany] " & strCompanyName)
Если текстовое поле txtCompanyName не будет заполнено, то приведенный код программы выберет все записи таблицы. А если его заполнить, то recordset станет выбирать записи по критерию совпадения со значением, введенным в поле txtCompanyName. О том, как выполнить выборку по дате, расскажем чуть ниже, а теперь вернемся к приведенному примеру. Обратите внимание, что в запросе между директивой WHERE [fieldNameCompany] и переменной нет математического знака — он выносится за пределы запроса и становится частью переменной. Важно знать, что процессор баз данных интерпретирует пустое поле таблицы как Null, а не как Empty, и потому нельзя использовать значение Empty при работе с базами данных.
В одном из приведенных примеров мы уже присваивали строковой переменной весь SQL-запрос. То же самое допустимо выполнить с любой частью этого запроса, в том числе с включающей директивы SQL. Вот как в таком случае будет выглядеть предыдущий пример:
Код Visual Basic
1
2
3
4
5
6
7
8
9
10
Dim strCompanyName AsStringIf txtCompanyName.Text <> EmptyThen
strCompanyName = " WHERE [fieldNameCompany] = " & Quote(txtCompanyName.Text)
Else
strCompanyName = " WHERE [fieldNameCompany] IS NOT Null"EndIfSet rs.OpenDatabase ("SELECT * " & _
" FROM tblOperations " & _
strCompanyName)
В поле нашего зрения находятся преимущественно строковые переменные. Дело в том, что кроме них передать в SQL-запрос можно только переменные типа byte, integer и long. Все же остальное придется переводить именно в строковый тип. Поэкспериментируем с переменными типа integer немного позже, а сначала посмотрим, как проще всего перевести переменную в строковый тип.
Для переменных single, double и currency имеет смысл применить встроенную функцию преобразования в строковый вид str(variable).
Код Visual Basic
1
2
3
4
5
Dim curPrice AsCurrencyDim strPrice AsString
curPrice = 44.56
strPrice = str(curPrice)
'не забудьте заключить ее в одинарные скобки
Когда требуется сделать выборку данных по полю, содержащему логические значения, иногда бывает необходимо применить в запросе переменную типа boolean. Для этого можно использовать функцию IIf. Если переменная содержит значение True, то функция вернет строковое значение «True», и наоборот.
В итоге переменную типа date придется также передавать как строковую переменную с учетом ее собственного формата. Дата передается в SQL-запрос как #dd/mm/yy#, поэтому нужно собрать и строковую переменную. Обратите внимание, что даже если в операционной системе формат даты установлен с разделителями в виде точек, отображающихся в полях базы данных, формат переменной, передаваемой в SQL-запрос, все равно должен иметь косую в качестве разделителя.
Как вариант, в поле базы данных, куда будет заноситься дата, можно поставить текстовый тип этого поля и работать только со строковыми переменными. Следует помнить, что переменная типа date занимает 8 байт, а переменная типа string — 10 байт плюс длина строки.
Теперь рассмотрим переменные типа byte, integer и long, которые не требуется обязательно обрамлять одинарными кавычками. Предположим, у нас есть база данных с несколькими таблицами, носящими имена 1, 2, 3, 4, 5. Нужно выбрать из всех таблиц данные и разнести их по разным элементам отображения. Создадим пять экземпляров элемента управления ListBox с одинаковым названием и индексами от 0 до 4. Вот как будет выглядеть наш код:
Данный код пять раз выберет набор записей и разнесет его по различным элементам. То же самое можно сделать и с полями таблицы, если в их названии присутствуют последовательные целые числа. Если имена таблицы или ее полей более сложны, то можно «собрать» их имя в строковую переменную путем конкатенации. Пусть в таблице tbl1 имеются поля: [field 1], [field 2], [field 3]. Тогда выборку из нее можно сделать с помощью кода:
Применение переменных при организации связи языка программирования с SQL существенно повышает гибкость программы. Вам достаточно будет написать одну процедуру с правильно организованным запросом, и тогда вы сможете просто вызывать ее, передавая переменные в качестве параметров. Таким образом вы уменьшите свой код и повысите продуктивность его использования.
