Приемы взаимодействия Visual Basic и SQL
При создании приложений, оперирующих базами данных, нередко возникают проблемы, связанные с организацией взаимодействия языка программирования с языком 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. Требуется выбрать записи адресатов, проживающих, например, во Владивостоке.
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
Весь запрос заключен в двойные кавычки. Чтобы процессор БД "выявил" наличие в запросе переменной, ее следует вывести за кавычки. В нашем случае это делается так:
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).
Private Function Quote(strVariable As String) As String Quote = " " " & strVariable & " " " End Function
Это особенно удобно тогда, когда приходится передавать несколько строковых переменных в различные запросы. Итак, наш запрос примет вид:
Set rs = db.OpenRecordset ("SELECT * FROM
[tblAdres] WHERE [fieldCity] =
" & Quote(strCity))
Данная строка кода, конечно, работает, но только нужно будет добавить в проект функцию Quote, а если вы не хотите это делать, то достаточно слегка "модернизировать" переменную strCity:
strCity = "Владивосток"
strCity = " " " & StrCity & " " "
Set rs = db.OpenRecordset ("SELECT * FROM
[tblAdres] WHERE [fieldCity] = " & strCity)
Теперь о том, как передать в запрос несколько переменных. Допустим, требуется выбрать записи по жителям Владивостока, носящим фамилию Иванов.
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-директива находилась на новой строке. Это упростит редактирование запроса и поиск в нем ошибок.
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-запрос полностью состоит из переменных (кроме директив самого запроса).
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.
Dim strCompanyName As String
If txtCompanyName.Text <> Empty Then
strCompanyName = "=" & Quote(txtCompanyName.Text)
Else
strCompanyName = "<> Null" "если, конечно,
вы уверены, что
в базе данных нет пустых записей
End If
Set rs.OpenDatabase ("SELECT * &
"FROM tblOperations " & _
"WHERE [fieldNameCompany] "& strCompanyName)
Если текстовое поле txtCompanyName не будет заполнено, то приведенный код программы выберет все записи таблицы. А если его заполнить, то recordset станет выбирать записи по критерию совпадения со значением, введенным в поле txtCompanyName. О том, как выполнить выборку по дате, расскажем чуть ниже, а теперь вернемся к приведенному примеру. Обратите внимание, что в запросе между директивой WHERE [fieldNameCompany] и переменной нет математического знака - он выносится за пределы запроса и становится частью переменной. Важно знать, что процессор баз данных интерпретирует пустое поле таблицы как Null, а не как Empty, и потому нельзя использовать значение Empty при работе с базами данных.
В одном из приведенных примеров мы уже присваивали строковой переменной весь SQL-запрос. То же самое допустимо выполнить с любой частью этого запроса, в том числе с включающей директивы SQL. Вот как в таком случае будет выглядеть предыдущий пример:
Dim strCompanyName As String
If txtCompanyName.Text <> Empty Then
strCompanyName = "WHERE
[fieldNameCompany] = " &
Quote(txtCompanyName.Text)
Else
strCompanyName = "WHERE
[fieldNameCompany] <> Null"
End If
Set rs.OpenDatabase ("SELECT * " & _
"FROM tblOperations " & _
"strCompanyName)
В поле нашего зрения находятся преимущественно строковые переменные. Дело в том, что кроме них передать в SQL-запрос можно только переменные типа byte, integer и long. Все же остальное придется переводить именно в строковый тип. Поэкспериментируем с переменными типа integer немного позже, а сначала посмотрим, как проще всего перевести переменную в строковый тип.
Для переменных single, double и currency имеет смысл применить встроенную функцию преобразования в строковый вид str(variable).
Dim curPrice As Currency Dim strPrice As String curPrice = 44.56 strPrice = str(curPrice) "не забудьте заключить ее в одинарные скобки
Когда требуется сделать выборку данных по полю, содержащему логические значения, иногда бывает необходимо применить в запросе переменную типа boolean. Для этого можно использовать функцию IIf. Если переменная содержит значение True, то функция вернет строковое значение "True", и наоборот.
Dim varBoolean As Boolean
varBoolean = True
Set rs.OpenDatabase ("SELECT * " &
"FROM [tblAdres] " & _
"WHERE [fieldGood] = " & IIf(varBoolean,
"True", "False")
В итоге переменную типа date придется также передавать как строковую переменную с учетом ее собственного формата. Дата передается в SQL-запрос как #dd/mm/yy#, поэтому нужно собрать и строковую переменную. Обратите внимание, что даже если в операционной системе формат даты установлен с разделителями в виде точек, отображающихся в полях базы данных, формат переменной, передаваемой в SQL-запрос, все равно должен иметь косую в качестве разделителя.
Dim strDate As String
strDate = "#27/08/2002#"
Set rs.OpenDatabase ("SELECT * & _
"FROM tblOperations " & _
"WHERE [fieldDate] = " & strDate)
Как вариант, в поле базы данных, куда будет заноситься дата, можно поставить текстовый тип этого поля и работать только со строковыми переменными. Следует помнить, что переменная типа date занимает 8 байт, а переменная типа string - 10 байт плюс длина строки.
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. Вот как будет выглядеть наш код:
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 существенно повышает гибкость программы. Вам достаточно будет написать одну процедуру с правильно организованным запросом, и тогда вы сможете просто вызывать ее, передавая переменные в качестве параметров. Таким образом вы уменьшите свой код и повысите продуктивность его использования.