Есть программы на 2013 аксессе. В них прилинкованы таблицы из 2008 ms sql сервера. В программах есть формы, где люди редактируют данные и изменения посредством Update-запросов на прилинкованные таблицы сохраняются на сервер. Проблема в том, что одно из полей должно записываться в зашифрованном виде. Например, человек вводит в форме значение 1342, а записаться оно должно как Lr$k#yi!&
На ms sql сервере есть функции ID_Decrypt, ID_Encrypt (не хранимые процедуры, а, подчеркиваю, скалярные функции), которые как раз и занимаются этим шифрованием. Принимают один параметр типа varchar расшифровывают\шифруют его и возвращают полученное, тоже varchar, значение.
Вопрос в том, как мне применить эти функции к нужному полю в аксессе?
Что я пробовал:
1. Делать в аксессе passthrough update-запрос. В этом случае на незнакомую функцию не ругается, значит видит. Более того, если не дать входной параметр, так и говорит, что нужен параметр;
2. Кодить в VBA через ADO. Использовал штук пять разных вариантов, нагугленных на стековерфлоу, ни один не сработал. Слишком много вариантов и комбинаций внутри них. Кто-то делал через Recordset, кто-то через Command + к этому добавляются разные вариации вызова функции, а-ля "select ID_Decrypt('1234') as res", "exec ID_Decrypt '123'" и иже с ними.
Если может быть у вас завалялся рабочий (т.е. у вас самих он гарантированно работал в похожей ситуации) вариант кода, прошу поделиться.
Поместил в событие "После обновления" формы следующий код:
Dim qdef As DAO.QueryDef
Dim id As String
Dim guest_id As String
'взял из формы номер, который нужно зашифровать, и поместил в переменную
id = [Forms]![anketa]![Pass_N]
guest_id = [Forms]![anketa]![gid]
'создал объект "запрос"
Set qdef = CurrentDb.CreateQueryDef("")
'поскольку это pass-through запрос, нужно заполнить свойство Connect. Я взял его от одной из прилинкованных таблиц
qdef.Connect = CurrentDb.TableDefs("dbo_guest_names").Connect
'мой запрос - на обновление, записей не возвращает
qdef.ReturnsRecords = False
qdef.SQL = "UPDATE [hostel].[dbo].[guest_names] " _
& "SET id = hostel.dbo.IDEncrypt('" & id & "') " _
& "WHERE guest_id = '" & guest_id & "'"
qdef.Execute
Соответственно, dbo.IDEncrypt - имя скалярной функции на ms sql server, а hostel - база данных, в которых эта функция находится. Код точно работает, id сохраняется в базу в зашифрованном виде.
Доброго.
В такой постановке: 1 вариант прост и не требует дополнительных телодвижений. Что в нем не нравится, зачем нужен второй?
Меня лично смущает: "к полю в акцессе". А почему, собственно, не сделать stored procedure с параметрами, из акцесса формировать строку, и выполнять вообще всё, а не конкретно апдейт одного поля, на стороне сервера?
В первом случае, хотя запрос выполняется (по крайней мере, VBA не спотыкается на строке его вызова), изменения не сохраняются. На сервере есть таблица dbo.guest_names, в аксессе она же прилинкована под именем dbo_guest_names. Какую нужно указывать? Если dbo.guest_names, то нужно ли писать перед именем таблицы еще и имя сервера?
Вообще в таком виде запрос видит только серверные таблицы или локальные тоже? То есть могу ли я использовать его для получения доступа к функциям с ms sql сервера, и вставлять обработанные ими данные в локальные таблицы?
Можно и через хп попробовать. Но тогда мне все равно нужен пример как это технически правильно реализовать. Вызвать хп из запроса или из VBA и работающий пример. Потому что как я уже говорил примеры со стековерфлоу не заработали. Может быть человек увидел там пример, как-то его доработал и отметил решением, хотя сам пример мог быть не до конца правильным.
Евгений: а, т.е. это не идеологический подход. Окей, все там просто. Идеология должна быть такая: не нужно таскать в акцесс то, что должно выполнить на стороне сервера.
Т.е. делаете сторку, в нее вставляете вашу функцию, на стороне акцесса:
--------
Public Function SQLExecute(QueryText As String)
On Error GoTo err
Dim qdf As QueryDef
Set qdf = CreateTempQueryDef(QueryText)
'кверидеф из функции возвращает записи...
qdf.ReturnsRecords = False
'выполнили
qdf.Execute
'не забыли закрыться...
qdf.Close
Exit Function
err:
MsgBox "No connections with the server, please call the developer ", vbInformation, "Server error"
qdf.Close
End Function
-- здесь потребуется указать имя существующего passthrough запроса.
-- в него пишете хотя бы select top 1 from mytable и называете его RunServerSQLHelper
Private Function CreateTempQueryDef(strSQL As String, Optional strQdfNamePrefix As String = "") As QueryDef
'
Dim strQdfName As String
If Len(strQdfNamePrefix) > 0 Then
strQdfName = RandomName("_" & strQdfNamePrefix)
Else
'если использовать такое имя то кверидеф будет создан в памяти
strQdfName = ""
End If
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef(strQdfName, "SELECT 1 FROM RunServerSQLHelper;")
'скопировал коннекшен стринг из существующего квери дефа
qdf.Connect = CurrentDb.QueryDefs("RunServerSqLHelper").Connect
qdf.SQL = strSQL
qdf.ReturnsRecords = True
qdf.ODBCTimeout = CurrentDb.QueryDefs("RunServerSqLHelper").ODBCTimeout
'вернул результат
Set CreateTempQueryDef = qdf
End Function
--
'генерирует случайное имя для ацессного объекта
Public Function RandomName(Optional strPrefix As String = "_tmp") As String
'перевел время с точностью до сотой секунды в строку
Dim strTimeAsString As String
strTimeAsString = CStr(Int(CDbl(Now) * 10000000))
'случайная строка на случай если вызовы пойдут в слишком близкое время
Dim strRndString As String
strRndString = CStr(Int(100000 * Rnd + 1))
Вот эти три функции швыряете в отдельный модуль. их можно упростить, но это уж самостоятельно.
И еще: когда формируете строку - не забывайте параметры обрамлять кавычками. причем строковые/даты - так сразу двумя двойными, внутри которых одинарные.
Например, вызов сторки с одним параметром:
QueryText = "exec dbo.mycoolprocedure @mycoolparam = " & "'" & mycooltextvalue & "';"
Евгений: ну. Один в один то, что я вам давал, только вы это таки упростили. Причем в процессе отломили еще обработку ошибок непонятно зачем. Сервер недоступен будет - что ваш код скажет?
А теперь еще раз послушайте меня: это работающий, но НЕправильный вариант. Когда часть обновления идет на стороне акцесса, а часть на стороне сервера - вы пользуетесь костылем. Кривым.
Просто подумать - "а вот когда я делаю сначала вставку данных, а потом еще дополнительно шлифую ее апдейтом - уж не делаю ли я криво? Может, мне надо сразу же инсерт делать правильно?" Все надо делать на стороне сервера. Сделайте сторку и передавайте в нее параметры.
Средний
