Для работы с источниками данных существуют различные способы и технологии. Наиболее распространенные из них — DAO, RDO, ADO и ADO.NET. В настоящий момент используются преимущественно ADO и ADO.NET для .NET-приложений.
При работе с базами данных часто возникает ситуация, когда выполняемая операция занимает относительно долгое время и в течении того времени, пока она выполняется, основная программа ожидает ее завершения, не давая возможности пользователям с ней работать. И тогда у программиста возникает вопрос: а как бы сделать так, чтобы длительные операции с БД не блокировали работу основной программы? Для этого в ADO предусмотрены асинхронные операции, а именно, асинхронное подключение, асинхронное выполнение команды и асинхронное получение данных.
Следует учитывать, что во-первых, асинхронность не ускоряет выполнение операций с БД, она просто делает работу пользователя более комфортной, и во-вторых, асинхронность усложняет логику программы. Если вы делаете асинхронный запрос, вы не можете в следующей строке крутить холостой бесконечный цикл с DoEvents до тех пор, пока статус объекта не изменится на "завершено". При использовании асинхронных операций поведение программы должно управляться событиями и флагами.
Асинхронное подключение — задается константой adAsyncConnect в методе ADODB.Connection.Close. При этом клиентская система не будет ждать, пока подключение будет завершено, а сразу будет работать дальше. Состояние подключения нужно отслеживать в событиях WillConnect (событие возникает перед тем, как начнет осуществляться подключение), ConnectComplete (подключение завершено, успешно или с ошибкой) и InfoMessage (в этом событии принимаются различные сообщения от СУБД).
Асинхронное выполнение — задается константой adAsyncExecute в методах, открывающих набор данных или выполняющих инструкции SQL, т.е. ADODB.Connection.Execute, ADODB.Recordset.Open и ADODB.Command.Execute. Статус операции можно отслеживать в событиях подключения (ADODB.Connection) WillExecute (событие возникает перед тем, как будет запущена операция) и ExecuteComplete (операция завершена, успешно или с ошибкой). Следует учитывать, что для одного подключения (ADODB.Connection) в один момент времени может выполняться только одна операция, независимо от того, асинхронно она выполняется или нет. Проверить, имеется ли для подключения какая-нибудь запущенная операция, можно с помощью свойства ADODB.Connection.State, adStateConnecting означает, что выполняется подключение, adStateExecuting означает, что выполняется операция и adStateFetching означает, что производится получение данных.
После того, как ваш асинхронный запрос отработает, вы можете использовать полученный набор данных (если запрос возвращает данные). Но прежде чем с этими данными можно будет работать, их вначале надо передать с серверной стороны на клиентскую сторону. И если этих данных много, то процесс может занять много времени. Чтобы в это время клиентская программа не блокировалась, используется асинхронное получение данных. Чтобы получать данные асинхронно, нужно указать константу adAsyncFetch или adAsyncFetchNonBlocking при открытии набора данных или выполнении команды (т.е. в ADODB.Connection.Execute, ADODB.Recordset.Open и ADODB.Command.Execute).
Действие adAsyncFetch и adAsyncFetchNonBlocking несколько различается. adAsyncFetch означает, после того, как будет получен первый блок данных (размер которых задается свойством CacheSize), остальные данные будут приниматься с сервера асинхронно, не блокируя клиентской программы. Но если клиентская программа попытается обратится к данным, которые еще не были получены, то она будет заблокирована до завершения операции. Указание adAsyncFetchNonBlocking задает, что клиентская программа никогда не будет блокироваться при получении данных; если запрошенные данные еще не были получены с сервера, то курсор будет установлен в конец набора записей (EOF). adAsyncFetchNonBlocking неприменим к таблицам, открываемым как adCmdTableDirect, и к наборам данных, открываемым из потока, содержащего сохраненный набор данных. Процесс получения данных можно отслеживать в событиях набора данных (ADODB.Recordset) FetchProgress (данные извлекаются) и FetchComplete (извлечение данных завершено).
Асинхронные операции также применимы к записям (ADODB.Record) и потокам (ADODB.Stream).
Ссылка: Фоновый режим работы программы
Есть процедуру подключения к удаленному SQL серверу, которая просто вешает приложение на несколько минут. Возможно ли ее выполнить в фоновом режиме.
Описанная задача решается довольно элементарно. Самый простой код будет таким:
Private Sub AsyncConnectToDatabase()
Dim objADOConnection As New ADODB.Connection
objADOConnection.Open "Provider=sqloledb;Data Source=dbserver;Initial Catalog=dbname;User Id=username;Password=userpassword;", , , adAsyncConnect
Do While objADOConnection.State = adStateConnecting
DoEvents
Loop
If objADOConnection.State = adStateOpen Then
MsgBox "Соединение установлено."
Else
MsgBox "Соединение не установлено."
End If
End Sub
Преимуществом данного кода является его простота (при желании, его можно уместить в три строки). Но у него есть и ряд недостатков. Во-первых, имеется холостой цикл ожидания с DoEvents внутри, что само по себе неправильно. Во-вторых, нигде не задается время таймаута или другие параметры. И в-третьих, не отслеживаются ошибки подключения, их причина.
Более правильным был бы следующий код:
Option Explicit
...
Private WithEvents objADOConnection As ADODB.Connection
...
Private Sub ConnectionDone(pConnection As ADODB.Connection)
If pConnection.State = ADODB.ObjectStateEnum.adStateOpen Then
MsgBox "Подключение выполнено. Версия: " & pConnection.Version
'Далее выполняются необходимые операции, загрузка справочников и данных и т.п.
Else
MsgBox "Подключение было закрыто."
End If
End Sub
Private Sub objADOConnection_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
Select Case adStatus
Case ADODB.EventStatusEnum.adStatusOK
'Подключение выполнено успешно
If pError Is Nothing Then
ConnectionDone pConnection
Else
MsgBox "В процессе подключения произошла ошибка." & vbNewLine & pError.Description & vbNewLine & "Подключение не выполнено."
End If
Case ADODB.EventStatusEnum.adStatusCancel
'Подключение было отменено в WillConnect
MsgBox "Подключение было отменено."
Case ADODB.EventStatusEnum.adStatusErrorsOccurred
'При подключении произошла ошибка
If Not pError Is Nothing Then
MsgBox "В процессе подключения произошла ошибка." & vbNewLine & pError.Description & vbNewLine & "Подключение не выполнено."
End If
End Select
End Sub
Private Sub objADOConnection_WillConnect(ConnectionString As String, UserID As String, Password As String, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
'Если имя пользователя locked-user или БД открывается в эксклюзивном режиме, отменить подключение
If UserID = "locked-user" Or pConnection.Mode = adModeShareExclusive Then
End Sub
Private Sub AsyncConnectToDatabase()
Dim sConnectionString As String
sConnectionString = "Provider=sqloledb;Data Source=dbserver;Initial Catalog=dbname;User Id=username;Password=userpassword;"
Set objADOConnection = New ADODB.Connection
objADOConnection.ConnectionTimeout = 10
objADOConnection.CommandTimeout = 10
objADOConnection.Mode = adModeReadWrite
objADOConnection.Open sConnectionString, , , adAsyncConnect
End Sub
Данный код, несмотря на его громоздкость и избыточность, имеет то преимущество, что обрабатывает большинство ситуаций, которые могут возникнуть при подключении к СУБД. В практическом использовании можно удалить ненужные фрагменты кода.
Напоминание. Нельзя выполнять операцию, если ранее запущенная асинхронная операция еще не завершилась. Проверяйте состояние ADODB.Connection.State.
Ссылка: Асинхронная обработка в ADO
Как можно сделать, чтобы при вызове AODDB.Connection.execute "my_procedure" чтобы прога не висла.
Ссылка: MS SQL Асинхронная команда
Visual Basic 6
в программе выполняю SQL запрос с помощью ExecuteWithResults
программа выполняет его минут за 10
все это время окно "дохлое" - типа зависшее
нужно запустить запрос асинхронно, чтобы прога и дальше "жила", а запрос выполнялся
реально ли это?
Задача решается аналогично предыдущей, только вместо асинхронного подключения теперь необходимо асинхронное выполнение. Самый простой код:
objADOConnection.Execute "delete * from [table1]", , ADODB.ExecuteOptionEnum.adAsyncExecute
Этот код выполнит удаление всех записей в таблице table1, не блокируя при этом основной поток. Но у данного кода есть ряд ограничений. Такой код можно использовать только для запросов, не возвращающих данных (SQL DDL, INSERT, UPDATE, DELETE), для SELECT-запросов этот код неприменим. Кроме того, при использовании этого кода невозможно узнать, когда операция будет завершена. Например, если следующей строкой потребуется удалить данные в другой таблице, придется использовать цикл, ожидая пока objADOConnection.State не перестанет быть adStateExecuting.
Следующий код более удобен:
Option Explicit
Private WithEvents objADOConnection As ADODB.Connection
...
Sub AsyncExecute()
objADOConnection.Execute "delete * from [table1]", , ADODB.ExecuteOptionEnum.adAsyncExecute
End Sub
...
Private Sub objADOConnection_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
Select Case adStatus
Case ADODB.EventStatusEnum.adStatusOK
'Запрос выполнен успешно
If pError Is Nothing Then
MsgBox "Запрос выполнен. Количество записей, к которым был применен запрос: " & RecordsAffected
Else
MsgBox "В процессе выполнения запроса произошла ошибка." & vbNewLine & pError.Description & vbNewLine & "Запрос не выполнен."
End If
Case ADODB.EventStatusEnum.adStatusCancel
'Запрос был отменен в WillExecute
MsgBox "Выполнение было отменено."
Case ADODB.EventStatusEnum.adStatusErrorsOccurred
'При выполнении произошла ошибка
If Not pError Is Nothing Then
MsgBox "В процессе выполнения запроса произошла ошибка." & vbNewLine & pError.Description & vbNewLine & "Запрос не выполнен."
End If
End Select
End Sub
В ряде случаев бывает удобнее пользоваться не ADODB.Connection.Execute, а ADODB.Recordset.Open (для получения набора данных) или ADODB.Command.Execute (для выполнения хранимых процедур, параметрических запросов и многого другого). При этом меняться будет только код AsyncExecute, но сами события по прежнему следует обрабатывать в событиях ADODB.Connection.
Option Explicit
Private WithEvents objADOConnection As ADODB.Connection
Private WithEvents objADORecordset As ADODB.Recordset
Private objADOCommand As ADODB.Command
...
Private Const sqlGetData As String = "select * from [table1]"
Private Const sqlClearData As String = "delete * from [table1]"
...
Private ExecuteMode As Long
...
Sub AsyncExecute()
ExecuteMode = 1
Set objADORecordset = New ADODB.Recordset
Set objADORecordset.ActiveConnection = objADOConnection
objADORecordset.CursorLocation = adUseClient
objADORecordset.Open Source:=sqlGetData, Options:=adAsyncExecute
End Sub
...
Sub RecordsetOpsDone()
MsgBox "Данные получены. Всего записей: " & objADORecordset.RecordCount
ExecuteMode = 2
Set objADOCommand = New ADODB.Command
Set objADOCommand.ActiveConnection = objADOConnection
objADOCommand.CommandType = adCmdText
objADOCommand.CommandText = sqlClearData
objADOCommand.Execute Options:=adAsyncExecute
End Sub
...
Sub CommandOpsDone(ByVal RecordsAffected As Long)
MsgBox "Таблица очищена, удалено строк: " & RecordsAffected
End Sub
...
Private Sub objADOConnection_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
Select Case adStatus
Case ADODB.EventStatusEnum.adStatusOK
'Запрос выполнен успешно
If pError Is Nothing Then
Select Case ExecuteMode
Case 1: RecordsetOpsDone
Case 2: CommandOpsDone RecordsAffected
End Select
Else
MsgBox "В процессе выполнения запроса произошла ошибка." & vbNewLine & pError.Description & vbNewLine & "Запрос не выполнен."
End If
Case ADODB.EventStatusEnum.adStatusCancel
'Запрос был отменен в WillExecute
MsgBox "Выполнение было отменено."
Case ADODB.EventStatusEnum.adStatusErrorsOccurred
'При выполнении произошла ошибка
If Not pError Is Nothing Then
MsgBox "В процессе выполнения запроса произошла ошибка." & vbNewLine & pError.Description & vbNewLine & "Запрос не выполнен."
End If
End Select
End Sub
Следует учитывать, что при выборке данных (SELECT-запросы) требуется время не только на выполнение запроса (Execute), но и на передачу данных от сервера клиенту (Fetch). При больших объемах данных это хорошо заметно; для Fetch времени может потребоваться гораздо больше, чем для Execute.
Следует отметить, что даже если вы выполняете запрос (получаете набор данных) не с помощью ADODB.Connection.Execute, а с помощью ADODB.Recordset.Open или ADODB.Command.Execute, события все-равно будут вызываться в связанном с ними (свойство .ActiveConnection) подключении ADODB.Connection.
Ссылка: Долговременные Sql запросы
Здравствуйте, проблема в следующем: В программе используются Sql запросы, возвращающие большие объемы данных, один запрос может работать в раойне 15-30 сек., во время работы программа зависает и невозможно нажать отмену, выход, выводить счетчик времеи выполнения запроса или просто перейти к окну программы по alt+tab, можно ли как-то решить эту проблему?
Ссылка: Асинхронный запрос
Запускаю запрос:
...
По идее ведь прогрмма должна "жить", на самом деле она замирает (Not responding) и висит так пока запрос не выполнится (минут 5), после чего вываливает данные в датагрид.
Ссылка: Проследить процесс записи BLOB (тела файла) в базу
Действия:
Записываю файл в базу данных ACCESS. Тело файла в районе от 10 до 80 мегабайт.
Через AppendChunk добавляю данные.
Но после вызова метода UPDATE процесс сохранения в базу зависает.
Вопрос:
Можно ли как нибудь процесс записи в базу сделать параллельным ? Так чтобы он продолжался "фоново" ? Или показать пользователю на прогрессбар с информацие о результате сохранения ?
Довольно часто перед программистом возникает задача фоновой загрузки большого объема данных. Задача решается аналогично предыдущей, но помимо асинхронного выполнения запросов теперь нужно и асинхронное получение данных.
Для большей наглядности будем исходить из того, что на форме есть список List1, который заполняется данными при нажатии на кнопку Command1. В окно Debug выводится вспомогательная информация.
Option Explicit
Private WithEvents objADOConnection As ADODB.Connection
Private WithEvents objADORecordset As ADODB.Recordset
...
Private Sub Command1_Click()
Debug.Print "Connection..."
Set objADOConnection = New ADODB.Connection
objADOConnection.Open "Provider=sqloledb;Data Source=dbserver;Initial Catalog=dbname;User Id=username;Password=userpassword;"
Debug.Print "Preparing..."
Set objADORecordset = New ADODB.Recordset
Set objADORecordset.ActiveConnection = objADOConnection
objADORecordset.CursorLocation = adUseClient
objADORecordset.Properties("Initial Fetch Size") = 0
objADORecordset.Properties("Background Fetch Size") = 100
objADORecordset.Open Source:="select * from [big_table]", Options:=ADODB.ExecuteOptionEnum.adAsyncExecute Or ADODB.ExecuteOptionEnum.adAsyncFetchNonBlocking
Debug.Print "Execute..."
End Sub
...
Private Sub objADOConnection_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
Debug.Print "Execute Complete"
List1.Clear
End Sub
Private Sub objADORecordset_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
Debug.Print "Fetch Complete"
End Sub
Private Sub objADORecordset_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
Static StoredPosition As Long
Debug.Print "Fetching... " & " (" & Progress & "/" & MaxProgress & ")"
If StoredPosition > 0 Then pRecordset.AbsolutePosition = StoredPosition + 1
Do Until pRecordset.EOF
List1.AddItem pRecordset.Fields(0)
If pRecordset.AbsolutePosition < Progress Then
pRecordset.MoveNext
If pRecordset.AbsolutePosition = Progress And Progress = MaxProgress Then Exit Do
Else
Exit Do
End If
Loop
StoredPosition = pRecordset.AbsolutePosition
End Sub
Код, показанный в примере, избыточен, для практического применения можно убрать до 50% кода, в зависимости от конкретных нужд. Кроме того, в большинстве случаев гораздо проще (и удобнее) бывает загружать данные в FetchComplete, а в FetchProgress просто отображать индикатор прогресса.
Следует помнить о следующих моментах. Когда сервер возвращает запрос, на клиенте принимается первый блок данных, равный Initial Fetch Size + Background Fetch Size, после чего создается новый поток, ожидающий нового блока данных, размер которых будет равен Background Fetch Size. Сделано это для того, чтобы уменьшить время отклика (не ждать, пока будет создан асинхронный поток). Но при этом может случится так, что этот первый блок данных вернет все записи, передаваемые сервером. В этом случае события FetchProgress и FetchComplete могут и не произойти.
Для того, чтобы события FetchProgress и FetchComplete происходили всегда, размер начального буфера Initial Fetch Size надо задать равным 0. Размер фонового буфера Background Fetch Size может быть любым, если он больше нуля; его надо подбирать, исходя из ожидаемого объема данных. При большом объеме данных слишком маленький буфер будет несколько снижать производительность, при маленьких объемах данных большой буфер сведет на нет все удобства асинхронной выборки данных.
При размере фонового буфера, равного 1, я столкнулся с непонятным поведением курсора. В этом случае позиция курсора (.AbsolutePosition) может не сохраняться в промежутке между вызовами FetchProgress. Именно поэтому в коде используется статическая переменная StoredPosition, при размере фонового буфера большем, чем 1, эту переменную можно убрать.
Иногда перед программистом возникает обраткая задача — не фоновая загрузка данных, а фоновое сохранение данных на сервер. К сожалению, мне неизвестено, каким образом можно организовать асинхронную выгрузку данных на сервер подобно тому, как это происходит при adAsyncFetchNonBlocking. Но можно поступить по другому, загружать данные на сервер блоками, с обработкой событий между блоками. Но для этого требуется серверный курсор.
Option Explicit
Private objADOConnection As ADODB.Connection
Private objADORecordset As ADODB.Recordset
...
Private Sub AsyncPutData()
Set objADOConnection = New ADODB.Connection
objADOConnection.Open "Provider=sqloledb;Data Source=dbserver;Initial Catalog=dbname;User Id=username;Password=userpassword;"
Set objADORecordset = New ADODB.Recordset
Set objADORecordset.ActiveConnection = objADOConnection
objADORecordset.CursorLocation = adUseServer
objADORecordset.Open "bulk_data", objADOConnection, adOpenDynamic, adLockOptimistic, ADODB.CommandTypeEnum.adCmdTable
Debug.Print "Reading file..."
Dim buff() As Byte, FN As Integer, P As Long, L As Long
Const BuffSize As Long = 1024& * 256&
FN = FreeFile
Open "big_file" For Binary Access Read As #FN
Debug.Print "Prepare recordset..."
P = 1
With objADORecordset
Debug.Print "Add record..."
.AddNew
With .Fields("FILE")
Do Until P > LOF(FN)
L = BuffSize
If P + L - 1 > LOF(FN) Then L = LOF(FN) - P + 1
ReDim buff(1 To L)
Get #FN, P, buff()
.AppendChunk buff()
Debug.Print "Chunk (" & Format$(P / LOF(FN), "0.0%") & ") -- " & Format$(P, "#,0") & "/" & Format$(LOF(FN), "#,0")
DoEvents
P = P + L
Loop
End With
Debug.Print "Put record..."
.Update
Debug.Print "Put complete."
End With
Close #FN
End Sub
Этот код сохраняет в базу данных файл big_file (в таблицу bulk_data), при этом основной поток приложения не замораживается. Обработка необработанных событий происходит при выполнении DoEvents, там можно указать и Me.Refresh (или что-то аналогичное), если нужно просто отобразить индикатор прогресса. На всякий случай повторю, что курсор обязательно должен быть серверным (adUseServer), если указать клиентский курсор, то процедура работать будет, но при выполнении .Update (между "Put record" и "Put complete") провайдер источника данных начнет передавать на сервер данные и приложение будет заморожено, пока процесс не завершиться. Способа сделать этот процесс (.Update) асинхронным я не знаю.
RecordsAffected. В событии ExecuteComplete (объект ADODB.Connection) есть аргумент RecordsAffected. Также этот аргумент имеется в методе Execute (объекты ADODB.Connection, ADODB.Command). В этом аргументе возвращается количество записей, к которым была применена операция. Под операцией понимаются INSERT, DELETE и UPDATE запросы; SELECT запросы не возвращают значение RecordsAffected. Для определения количества записей используйте запрос SELECT COUNT(*) или свойство ADODB.Recordset.RecordCount (для клиентского курсора).
ADODB.Command представляет собой объект-команду. Данный объект характеризуется следующими основными свойствами: CommandText, CommandType, Parameters, Prepared. Довольно часто ADODB.Command используется для вызова параметрических запросов (запросов с параметрами), но есть и другие причины его использования, например, необходимость вызова хранимых процедур и получения возвращаемого значения и выходных параметров. Также ADODB.Command удобен, когда многократно вызывается один и тот же запрос с различными параметрами.
Свойство CommandText содержит в себе текст команды и интерпретируется в зависимости от типа команды. Тип команды указывается в свойстве CommandType, основные типы это таблица (представление), хранимая процедура и SQL-запрос. В коллекции Parameters содержаться параметры (аргументы) запроса. Свойство Prepared используется, когда предполагается часто вызывать один и тот же запрос с различными параметрами. Установка данного свойства в True указывает, что данный запрос надо подготовить (скомпилировать) и сохранить перед первым использованием. Это замедлит первый запуск запроса, но все последующие будут исполняться быстрее.
Отдельно можно остановиться на параметрах запроса. Эти параметры храняться в коллекции Parameters. Основные свойства параметра: Name (имя параметра), Type (тип параметра), Value (значение параметра), Direction (направление параметра). Среди направлений параметров стоит отметить adParamReturnValue, которое указывает, что данный параметр принимает значения, возвращаемые хранимой процедурой.
Также следует помнить, что типы данных, используемые в ADO, не всегда соответствуют типам данных, используемых в VB и не все из них можно привести друг к другу. Кроме того, в конкретных СУБД могут быть свои типы данных, которые обычно можно привести к типам данных ADO, но которые могут называться по разному.
| Тип ADO | Описание | Соответствие типов данных | ||||
| OLEDB | VB | ANSI SQL | MS Access | MS SQL Server | ||
| adArray | Флаг, комбинируемый с другими типами данных, указывающий, что параметр является массивом. | - | - | - | - | - |
| adEmpty | - | DBTYPE_EMPTY | Variant (Null) | - | - | - |
| adBoolean | Логический тип (булево значение). | DBTYPE_BOOL | Boolean | BIT | Логический | bit |
| adTinyInt | Короткое целое (один байт, знаковое). | DBTYPE_I1 | - | TINYINT | - | - |
| adSmallInt | Целое (два байта, знаковое). | DBTYPE_I2 | Integer | SMALLINT | Числовой (Целое) | smallint |
| adInteger | Длинное целое (четыре байта, знаковое). | DBTYPE_I4 | Long | INTEGER | Числовой (Длинное целое) | int |
| adBigInt | Сверхдлинное целое (восемь байт, знаковое). | DBTYPE_I8 | - | BIGINT | - | bigint |
| adUnsignedTinyInt | Короткое беззнаковое целое (один байт, беззнаковое). | DBTYPE_UI1 | Byte | - | Числовой (байт) | tinyint |
| adUnsignedSmallInt | Беззнаковое целое (два байта, беззнаковое). | DBTYPE_UI2 | - | - | - | - |
| adUnsignedInt | Беззнаковое длинное (четыре байта, беззнаковое). | DBTYPE_UI4 | - | - | - | - |
| adUnsignedBigInt | Беззнаковое сверхдлинное (восемь байт, беззнаковое). | DBTYPE_UI8 | - | - | - | - |
| adDecimal | Число с фиксированной запятой. | DBTYPE_DECIMAL | - | DECIMAL | Числовой (Действительное) | decimal |
| adNumeric | Число с фиксированной запятой. | DBTYPE_NUMERIC | - | NUMERIC | Числовой (Действительное) | numeric |
| adVarNumeric | Аналогично adNumeric, но значение не имеет фиксированного размера. Обычно используют внутри ADODB.Command. | - | - | NUMERIC | Числовой (Действительное) | numeric |
| adSingle | Вещественное число с плавающей запятой (четыре байта, знаковое). | DBTYPE_R4 | Single | REAL | Числовой (Одинарное с плавающей запятой) | real |
| adDouble | Вещественное число с плавающей запятой повышенной точности (восемь байта, знаковое). | DBTYPE_R8 | Double | FLOAT | Числовой (Двойное с плавающей запятой) | float |
| adCurrency | Валютные единицы, числа с фиксированной запятой. | DBTYPE_CY | Currency | - | Денежный | money, smallmoney |
| adDate | Значение даты (число дней, прошедших с 30 декабря 1899 г. | DBTYPE_DATE | Date | - | Дата/время | datetime, smalldatetime |
| adDBDate | Дата в формате yyyymmdd. | DBTYPE_DBDATE | - | DATE | Дата/время | datetime, smalldatetime |
| adDBTime | Дата в формате hhmmss. | DBTYPE_DBTIME | - | TIME | Дата/время | datetime, smalldatetime |
| adDBTimeStamp | Дата в формате yyyymmddhhmmss. | DBTYPE_DBTIMESTAMP | - | TIMESTAMP | Дата/время | datetime, smalldatetime |
| adFileTime | Значение даты (число сотых наносекунды, прошедших со времени 1 января 1601 г. | DBTYPE_FILETIME | - | - | - | - |
| adBinary | Бинарные данные. Обычно файлы или шифрованные файлы. | DBTYPE_BYTES | Byte() | BINARY- | Поле объекта OLE | binary |
| adVarBinary | Аналогично adBinary, но значение не имеет фиксированного размера. Обычно используют внутри ADODB.Command. | - | Byte() | VARBINARY | Поле объекта OLE | varbinary |
| adLongVarBinary | Аналогично adBinary, но значение не имеет фиксированного размера. Обычно используют внутри ADODB.Command. | - | Byte() | BLOB | Поле объекта OLE | image |
| adChar | Строковое значение в ANSI. | DBTYPE_STR | String | CHAR | Текстовый | char |
| adVarChar | Аналогично adChar, но значение не имеет фиксированного размера. Обычно используют внутри ADODB.Command. | - | String | VARCHAR | Текстовый | varchar |
| adWChar | Строковое значение в Unicode. | DBTYPE_WSTR | String | - | Текстовый | nchar |
| adVarWChar | Аналогично adWChar, но значение не имеет фиксированного размера. Обычно используют внутри ADODB.Command. | - | String | - | Текстовый | nvarchar |
| adLongVarChar | Аналогично adBinary, но значение не имеет фиксированного размера. Обычно используют внутри ADODB.Command. | - | String | CLOB | Поле МЕМО | text |
| adLongVarWChar | Аналогично adWChar, но значение не имеет фиксированного размера. Обычно используют внутри ADODB.Command. | - | String | - | Поле МЕМО | ntext |
| adBSTR | Строковое значение. | DBTYPE_BSTR | String | - | Текстовый | nchar, nvarchar, ntext |
| adVariant | Произвольное значение. | DBTYPE_VARIANT | Variant | - | - | sql_variant |
| adGUID | Значение глобального уникального илентификатора. | DBTYPE_GUID | String | - | - | uniqueidentifier |
| adChapter | Четырехбайтовые данные. | DBTYPE_HCHAPTER | - | - | - | - |
| adIUnknown | Указатель на интерфейс IUnknown. Не используется. | DBTYPE_IUNKNOWN | - | - | - | - |
| adIDispatch | Указатель на интерфейс IDispatch. Не используется. | DBTYPE_IDISPATCH | - | - | - | - |
| adError | Значение 32-битного кода ошибки. | DBTYPE_ERROR | - | - | - | - |
| adPropVariant | Произвольное значение. | DBTYPE_PROP_VARIANT | - | - | - | - |
| adUserDefined | Пользовательский тип данных. | DBTYPE_UDT | - | - | - | - |
Ссылка: сравнение по дате (access)
в таблице есть поле (тип Краткая дата: dd.mm.yyyy)
делаю выборку так: ask = ask & " AND [Date_Insert] => '" & Start_Date & "' AND [Date_Insert] <= '" & Finish_Date & "'"
, где Start_Date-начальная дата; Finish_Date-конечная дата
выдаёт ошибку: ошибка выражения
почему?
Этот вопрос очень типичный. Многие начинающие формируют запросы именно таким образом (формируя динамический SQL из фрагментов) и сталкиваются с проблемами, когда в тексте запроса нужно указать текстовые выражения или передать дату. В случае с текстовыми параметрами сложности относятся к необходимости экранирования специальных символов (кавычек, escape-символов) и необходимости исключить инъекцию кода (SQL-injection). В случае с параметрами типа "дата" сложности связаны с тем, что в разных БД дата представляется в разных форматах. Данный вопрос можно решить таким образом:
Start_Date = #2001-12-31#
Finish_Date = Format$(Now(), "\#yyyy\-mm\-dd\#")
ask = ask & " AND [Date_Insert] => '" & Start_Date & "' AND [Date_Insert] <= '" & Finish_Date & "'"
Данный код будет работать именно так, как это нужно программисту. Связано это с тем, что Access (вернее провайдер Jet) умеет правильно интерпретировать дату, передаваемую в виде строки в формате #yyyy-mm-dd#, #yyyy/mm/dd#. Также можно использовать формат #mm/dd/yyyy#, но делать это не рекомендуется, т.к. в некоторых случаях Access может перепутать месяц и день. При использовании этого кода в других СУБД он может оказаться неработоспособным, т.к. СУБД будет ожидать дату в другом формате.
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Connection
cmd.CommandType = adCmdText
cmd.CommandText = "PARAMETERS parStartDate DateTime, parFinishDate DateTime; " & vbNewLine & _
"SELECT * FROM [Table1] WHERE [Date_Insert] BETWEEN [parStartDate] AND [parFinishDate]; "
cmd.NamedParameters = True
cmd.Parameters.Append cmd.CreateParameter("parStartDate", adDate, adParamInput, , #12/31/2001#)
cmd.Parameters.Append cmd.CreateParameter("parFinishDate", adDate, adParamInput, , Now())
Set rs = cmd.Execute()
В данном примере использованы именованные параметры (NamedParameters = True). Если этого не указывать, тогда параметры должны передаваться в набор параметров в том порядке, в котором они встречаются в тексте запроса. Еще один способ выполнения запроса:
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = "PARAMETERS parStartDate DateTime, parFinishDate DateTime; " & vbNewLine & _
"SELECT * FROM [Table1] WHERE [Date_Insert] BETWEEN [parStartDate] AND [parFinishDate]; "
cmd.NamedParameters = True
cmd.Parameters.Append cmd.CreateParameter("parStartDate", adDate, adParamInput, , #12/31/2001#)
cmd.Parameters.Append cmd.CreateParameter("parFinishDate", adDate, adParamInput, , Now())
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open cmd, Connection, adOpenStatic, adLockReadOnly
Такой способ выполнения запроса бывает нужен, когда имеются какие-либо требования к набору данных (серверный или клиентский курсор, тип курсора, тип блокировки и т.п.). Кроме того, это один из способов вызова хранимой процедуры, если она возвращает набор данных и выходные параметры.
Следует отменить, что метод Execute можен возвращать набор данных (ADODB.Recordset) либо поток (ADODB.Stream). Если в аргументе Options указать флаг adExecuteStream, поток будет доступен из свойства .Properties("Output Stream").
Ссылка: хранимая процедура MS SQL Server
Подскажите пожалуйста, как обратиться и передать параметры к хранимой процедуре созданной в MS SQL Server из VB.
Ссылка: output parameter
Подскажите пожалуйста как вызвать хранимую процедуру с output parameter из vb с использованием ADO
Ссылка: output параметры процедуры и RecordSet
...
как получить и RecordSet и выходные параметры с помощью команды Execute
В простейшем случае для вызова хранимой процедуры можно использовать метод Execute объекта ADODB.Connection. Но этот метод применим не во всех случаях, гораздо удобнее для этого использовать объект ADODB.Command. Вот один из наиболее простых способов использования:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_myproc1"
cmd.NamedParameters = True
cmd.Parameters.Append cmd.CreateParameter("arg1", adInteger, adParamInput, , 1234)
cmd.Parameters.Append cmd.CreateParameter("arg2", adBSTR, adParamInput, , "sample text")
cmd.Execute
Для хранимых процедур и параметрических запросов есть один нюанс. Если объекту ADODB.Command уже было присвоено активное подключение, тип команды и текст команды, то можно использовать метод cmd.Parameters.Refresh, чтобы получить список параметров, вместо того, чтобы формировать этот список параметров вручную (cmd.Parameters.Append).
Кроме того, возможен и более простой вариант использования:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_myproc1"
cmd.Execute , Array(1234, "sample text"), adExecuteNoRecords
Для того, чтобы получить возвращаемое хранимой процедурой значение и выходные параметры, можно указывать соответствующее свойство параметра. Для выходных параметров используется adParamOutput, для значения, возвращаемого процедурой, используется adParamReturnValue.
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "mysp1"
cmd.Parameters.Append cmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("@Arg1", adSmallInt, adParamInput, , 1234)
cmd.Parameters.Append cmd.CreateParameter("@Arg2", adBSTR, adParamInput, , "test string")
cmd.Parameters.Append cmd.CreateParameter("@Arg3", adDate, adParamInput, , Now())
cmd.Parameters.Append cmd.CreateParameter("@Result1", adBSTR, adParamOutput)
cmd.Parameters.Append cmd.CreateParameter("@Result2", adBoolean, adParamOutput)
cmd.Execute , , adExecuteNoRecords
MsgBox "Код возврата процедуры: " & cmd.Parameters("RETURN_VALUE").Value & vbNewLine & _
"Параметр Result1 равен: " & cmd.Parameters("@Result1").Value & vbNewLine & _
"Параметр Result2 равен: " & cmd.Parameters("@Result2").Value
Set cmd = Nothing
Хранимая процедура может принимать один или несколько аргументов. Хранимая процедура также может возвращать один или несколько аргументов, может возвращать код возврата процедуры и может возвращать один или несколько наборов данных. Если процедура возвращает и параметры, и набор(ы) данных, то вызывать ее надо следующим способом:
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "mysp1"
cmd.Parameters.Append cmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("@Arg1", adSmallInt, adParamInput, , 1234)
cmd.Parameters.Append cmd.CreateParameter("@Arg2", adBSTR, adParamInput, , "test string")
cmd.Parameters.Append cmd.CreateParameter("@Arg3", adDate, adParamInput, , Now())
cmd.Parameters.Append cmd.CreateParameter("@Result1", adBSTR, adParamOutput)
cmd.Parameters.Append cmd.CreateParameter("@Result2", adBoolean, adParamOutput)
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.Open cmd
If cmd.Parameters("RETURN_VALUE").Value < 0 Then
MsgBox "Процедура вернула ошибку" & vbNewLine & _
"Параметр Result1 равен: " & cmd.Parameters("@Result1").Value & vbNewLine & _
"Параметр Result2 равен: " & cmd.Parameters("@Result2").Value
Else
MsgBox "Процедура отработала успешно. Получено строк: " rs.RecordCount & vbNewLine & _
"Параметр Result1 равен: " & cmd.Parameters("@Result1").Value & vbNewLine & _
"Параметр Result2 равен: " & cmd.Parameters("@Result2").Value
Do Until rs.EOF
Debug.Print " - строка " & rs.AbsolutePosition & "; " & rs.Fields(0).Value
rs.MoveNext
Loop
End If
Подготовленные запросы. Подготовленные (prepared) запросы удобны, когда надо многократно вызывать одну и ту же процедуру или параметрический запрос, передавая ему разные параметры. Для того, чтобы использовать подготовленный запрос, ему надо задать свойство Prepared = True. Это свойство указывает, что провайдер данных должен будет сохранить подготовленную (скомпилированную) версию запроса перед первым выпонением запроса. Это несколько замедлит первый вызов запроса, зато дальнейшие вызовы будут производиться быстрее, т.к. будет использоваться подготовленный (скомпилированный) запрос.
'BeginPreparedVB
'To integrate this code
'replace the data source and initial catalog values
'in the connection string
Public Sub PreparedX()
Dim Cnxn As ADODB.Connection
Dim cmd1 As ADODB.Command
Dim cmd2 As ADODB.Command
Dim strCnxn As String
Dim strCmd As String
Dim sngStart As Single
Dim sngEnd As Single
Dim sngNotPrepared As Single
Dim sngPrepared As Single
Dim intLoop As Integer
' Open a connection
strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn
' Create two command objects for the same
' command - one prepared and one not prepared
strCmd = "SELECT title, type FROM Titles ORDER BY type"
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = Cnxn
cmd1.CommandText = strCmd
Set cmd2 = New ADODB.Command
Set cmd2.ActiveConnection = Cnxn
cmd2.CommandText = strCmd
cmd2.Prepared = True
' Set a timer, then execute the unprepared
' command 20 times
sngStart = Timer
For intLoop = 1 To 20
cmd1.Execute
Next intLoop
sngEnd = Timer
sngNotPrepared = sngEnd - sngStart
' Reset the timer, then execute the prepared
' command 20 times
sngStart = Timer
For intLoop = 1 To 20
cmd2.Execute
Next intLoop
sngEnd = Timer
sngPrepared = sngEnd - sngStart
' Display performance results
MsgBox "Performance Results:" & vbCr & _
" Not Prepared: " & Format(sngNotPrepared, _
"##0.000") & " seconds" & vbCr & _
" Prepared: " & Format(sngPrepared, _
"##0.000") & " seconds"
' clean up
Cnxn.Close
Set Cnxn = Nothing
End Sub
'EndPreparedVB
CommandStream. Текст команды можно задавать в свойстве CommandText или CommandStream. Свойства эти взаимоисключающие, при установке значения CommandStream свойство CommandText присваивается к пустой строке. ADODB.Stream чаще всего используется в ASP-страницах, но может использоваться и в обычных запросах, когда объем принимаемых или передаваемых данных большой и требуется фрагментирование.
Dim cmd As New ADODB.Command
Dim StreamIn As New ADODB.Stream
Dim StreamOut As New ADODB.Stream
Dim SQL As String
...
SQL = ...
...
StreamIn.Open
StreamIn.WriteText SQL
StreamIn.Position = 0
Set cmd.ActiveConnection = Connection
Set cmd.CommandStream = StreamIn
StreamOut.Open
StreamOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = StreamOut
cmd.Execute , , adExecuteStream
StreamOut.Position = 0
Debug.Print StreamOut.ReadText
При составлении данной статьи были использованы вопросы и примеры с форума VBStreets.