22.05.2009, 17:54. Показов 22336. Ответов 0
При создании приложений, оперирующих базами данных, нередко возникают проблемы, связанные с организацией взаимодействия языка программирования с языком 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 Library
Dim rs As recordset
'в последующих примерах (для экономии места)
'эти переменные не будут объявлены
Set db = OpenDatabase (App.Path & "DataBase.mdb")
Set rs = db.OpenRecordset ("SELECT * FROM [tblAdres] WHERE [fieldCity] = Владивосток")
Do Until 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.Close
Set rs = Nothing
db.Close
Set db = Nothing |
|
Весь запрос заключен в двойные кавычки. Чтобы процессор БД «выявил» наличие в запросе переменной, ее следует вывести за кавычки. В нашем случае это делается так:
| Visual Basic |
1
2
3
4
5
6
| Dim db As Database
Dim rs As Recordset
Dim strCity As String
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
2
3
| Private Function Quote(strVariable As String) As String
Quote = "'" & strVariable & "'"
End Function |
|
Это особенно удобно тогда, когда приходится передавать несколько строковых переменных в различные запросы. Итак, наш запрос примет вид:
| Visual Basic |
1
| Set rs = db.OpenRecordset ("SELECT * FROM [tblAdres] WHERE [fieldCity] = " & Quote(strCity)) |
|
Данная строка кода, конечно, работает, но только нужно будет добавить в проект функцию Quote, а если вы не хотите это делать, то достаточно слегка «модернизировать» переменную strCity:
| Visual Basic |
1
2
3
| strCity = "Владивосток"
strCity = "'" & StrCity & "'"
Set rs = db.OpenRecordset ("SELECT * FROM [tblAdres] WHERE [fieldCity] = " & strCity) |
|
Теперь о том, как передать в запрос несколько переменных. Допустим, требуется выбрать записи по жителям Владивостока, носящим фамилию Иванов.
| Visual Basic |
1
2
3
4
5
6
7
8
| Dim strCity As String
Dim strSurname As String
Dim strSQL As String
strCity = "Владивосток"
strSurname = "Иванов"
strSQL = "SELECT * FROM [tblAdres] WHERE [fieldCity] = " & Quote(strCity) & _
" AND [fieldSurname] = " & Quote(strSurname)
Set rs = db.OpenRecordset(strSQL) |
|
Строка запроса несколько усложнилась, но ее структура осталась такой же. В подобных случаях целесообразно разбить строку на несколько частей, иначе она не поместится на экране и работать с ней будет неудобно. Рекомендую разделить ее так, чтобы каждая SQL-директива находилась на новой строке. Это упростит редактирование запроса и поиск в нем ошибок.
| Visual Basic |
1
2
3
4
5
| strSQL = "SELECT * " & _
"FROM [tblAdres] " & _
"WHERE [fieldCity] = " & Quote(strCity) & _
"AND [fieldSurname] = " & Quote(strSurname)
Set rs = db.OpenRecordset(strSQL) |
|
Обратите внимание на пробел между кавычками и директивой 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-запрос полностью состоит из переменных (кроме директив самого запроса).
| Visual Basic |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| Dim strTable As String
Dim strFieldPeople As String
Dim strFieldCity As String
Dim strFieldStreet As String
Dim strFieldHouse As String
Dim strPeople As String
Dim strCity As String
strTable = "[tbl Adres]" 'наименование таблицы
strFieldPeople = "[field Surname]"
'наименование поля таблицы ‘Фамилия’
strFieldCity = "[field City]"
'наименование поля таблицы ‘Город’
strFieldStreet = "[field Street]"
'наименование поля таблицы ‘Улица’
strFieldHouse = "[field House]"
'наименование поля таблицы ‘Дом’
strPeople = Trim(txtPeople.Text)
'даем возможность ввести критерии выборки,
strCity = Trim(txtCity.Text)
'и сразу отсекаем лишние пробелы
Set rs = db.OpenRecordset("SELECT " & strFieldStreet & "," & strFieldHouse & _
" FROM " & strTable & _
" WHERE " & strFieldPeople & "=" & Quote(strPeople) & _
" AND " & strFieldCity & “=“ & Quote(strCity)) |
|
Рассмотрим еще несколько примеров. Допустим, у нас есть база данных, включающая таблицу tblOperations с полями [fieldNumber], [fieldData], [fieldNameCompany] и [fieldSum]. Первое поле содержит номер заказа, второе — дату, третье — имя компании, четвертое — сумму операции. Необходимо сформировать критерии выборки по имени компании, причем если поле не заполнено, то recordset должен включать полный набор записей, а если заполнено, — то и критерий выборки. В последнем случае вполне можно обойтись одним SQL-запросом. Опишем подробнее процесс формирования критерия. «Нарисуем» в форме текстовое поле txtCompanyName.
| Visual Basic |
1
2
3
4
5
6
7
8
9
10
| Dim strCompanyName As String
If txtCompanyName.Text <> Empty Then
strCompanyName = "=" & Quote(txtCompanyName.Text)
Else
strCompanyName = "is not Null"
'если, конечно, вы уверены, что
'в базе данных нет пустых записей
End If
Set 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 As String
If txtCompanyName.Text <> Empty Then
strCompanyName = " WHERE [fieldNameCompany] = " & Quote(txtCompanyName.Text)
Else
strCompanyName = " WHERE [fieldNameCompany] IS NOT Null"
End If
Set 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 As Currency
Dim strPrice As String
curPrice = 44.56
strPrice = str(curPrice)
'не забудьте заключить ее в одинарные скобки |
|
Когда требуется сделать выборку данных по полю, содержащему логические значения, иногда бывает необходимо применить в запросе переменную типа boolean. Для этого можно использовать функцию IIf. Если переменная содержит значение True, то функция вернет строковое значение «True», и наоборот.
| Visual Basic |
1
2
3
4
5
| Dim varBoolean As Boolean
varBoolean = True
Set rs.OpenDatabase ("SELECT * " &
"FROM [tblAdres] " & _
"WHERE [fieldGood] = " & IIf(varBoolean, "True", "False") |
|
В итоге переменную типа date придется также передавать как строковую переменную с учетом ее собственного формата. Дата передается в SQL-запрос как #dd/mm/yy#, поэтому нужно собрать и строковую переменную. Обратите внимание, что даже если в операционной системе формат даты установлен с разделителями в виде точек, отображающихся в полях базы данных, формат переменной, передаваемой в SQL-запрос, все равно должен иметь косую в качестве разделителя.
| Visual Basic |
1
2
3
4
5
| Dim strDate As String
strDate = "#27/08/2002#"
Set rs.OpenDatabase ("SELECT * " & _
"FROM tblOperations " & _
"WHERE [fieldDate] = " & strDate) |
|
Как вариант, в поле базы данных, куда будет заноситься дата, можно поставить текстовый тип этого поля и работать только со строковыми переменными. Следует помнить, что переменная типа date занимает 8 байт, а переменная типа string — 10 байт плюс длина строки.
| Visual Basic |
1
2
3
4
5
6
7
8
| Dim intTable As Integer
For intTable = 1 To 5
Set rs.OpenDatabase ("SELECT * " & "FROM " & intTable)
Do Until rs.EOF
LisBox1(intTable - 1).AddItem rs.Fields("field1") & " " & vbCrLf
rs.MoveNex
Loop
Next intTable |
|
Теперь рассмотрим переменные типа byte, integer и long, которые не требуется обязательно обрамлять одинарными кавычками. Предположим, у нас есть база данных с несколькими таблицами, носящими имена 1, 2, 3, 4, 5. Нужно выбрать из всех таблиц данные и разнести их по разным элементам отображения. Создадим пять экземпляров элемента управления ListBox с одинаковым названием и индексами от 0 до 4. Вот как будет выглядеть наш код:
| Visual Basic |
1
2
3
4
5
6
7
8
9
10
| Dim intField As Integer
Dim strField As String
For intField = 1 To 3
strField = "[field" & intField & "]"
Set rs.OpenDatabase ("SELECT " & Quote(strField ) & " FROM [tbl1]")
Do Until rs.EOF
LisBox1(intField).AddItem rs.Fields("field1") & " " & vbCrLf
rs.MoveNex
Loop
Next intField |
|
Данный код пять раз выберет набор записей и разнесет его по различным элементам. То же самое можно сделать и с полями таблицы, если в их названии присутствуют последовательные целые числа. Если имена таблицы или ее полей более сложны, то можно «собрать» их имя в строковую переменную путем конкатенации. Пусть в таблице tbl1 имеются поля: [field 1], [field 2], [field 3]. Тогда выборку из нее можно сделать с помощью кода:
Применение переменных при организации связи языка программирования с SQL существенно повышает гибкость программы. Вам достаточно будет написать одну процедуру с правильно организованным запросом, и тогда вы сможете просто вызывать ее, передавая переменные в качестве параметров. Таким образом вы уменьшите свой код и повысите продуктивность его использования.
Евгений Немец 20.10.2002
Мир ПК, #10/2002
(
