ADO Connection - по udl-файлу открывается, по строке из него - нет

[DesertBrowser]

Добрый день.
Подскажите пожалуйста, в чём может быть проблема.
Создаю udl-файл, указываю параметры подключения (в т.ч. аутентификаця на сервере БД - Windows). Копирую строку подключения из него в код X++. Однако вызов метода Connection.Open() выдаёт ошибку - SQL Server не существует, или отсутствует доступ. Пробую открыть ADOConnection из Excel с использованием той же строки подключения. Коннект открывается. Пробую открыть коннект из Аксапты, указав в строке подключения тот самый файл udl - коннект открывается!

PHP код:

    COM cnnDB;
    str dbCnnString;
    ;
    dbCnnString = "File Name=test.udl;";//с этой строкой коннект открывается
    //dbCnnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=ATest;Data Source=TESTSERVER";//а с этой - нет
    cnnDB = new COM("ADODB.Connection");
    cnnDB.connectionString(dbCnnString);
    cnnDB.Open();//ошибка на этой строке 


PS.DAX v3.0 SP3, SQL Server 2000, ОС клиента WindowsXP; на клиентской машине установлены ADO v2.1 - 2.8

PPS. Предполагается часть тяжёлых аналитических запросов перенести на сервер БД в виде хранимых процедур. Вызываться они будут из Аксапты через ADO. Как лучше поступить с аутентификацией пользователей на сервере БД - всем давать Windows-аутентификацию и каждому задавать права? установить аутентификацию SQL Server и завести один логин и одного пользователя на все ADO-подключения? завести логины и пользователей, аналогичные пользователям Аксапты?открывать подключение с сервера приложений по имени пользователя, которое использует для подключения к БД AOS? Прошу поделиться опытом и советами, в Аксапте я новичок.

[jonny]

Если процедуры будут храниться в той же базе, которую использует аксапта, то пользуйтесь стандартными классами Connection и Statment.
В этом случае вы будете подключаться к БД под теми же правами что и аксапта.

[Владимир Максимов]

Синтаксис строки подключения к MS SQL 2000

Connection strings for SQL Server 2000, 7.0

Отличие от приведенного Вами синтаксиса в том, что нет цифры в имени провайдера.

Хотя, согласен с jonny. Если Вы работаете с родной базой, то зачем нужно внешнее подключение? Вполне достаточно штатного Connection.

[DesertBrowser]

Перед тем, как пробовать ADO-подключение, я использовала классы UserConnection и Statement, но столкнулась с проблемой: считывать значения из полей записи можно только последовательно, в порядке следования полей, Поэтому (а также в результате чтения форума по этой тематике) решила попробовать ADO, и теперь хотелось бы добиться результата в этом направлении.
Если не сложно, Вы могли бы в двух словах объяснить, в чём различия функционала классов Connection и UserConnection?

[Владимир Максимов]

Connection использует внутрение механизмы Axapta подключения к базе. По сути, просто подхватывает уже созданное соединение. Как следствие, наследует все установленные для этого соединения права. Как правило, нового соединения с сервером в этом случае не создается.

UserConnection - это создание нового соединения с сервером, но с теми же самыми настройками и правами, что и Connection.

ODBCConnection - а это уже создание соединения с сервером с указанными реквизитами. Вне зависимости от настроек текущего соединения Axapta с сервером.

PS: Что с ADO, что с ODBC придется создавать набор переменных памяти, чтобы считать результат выборки и "перевести" его в вид, понятный Axapta. Поэтому вопрос последовательного чтения полей при использовании Connection - это настолько незначительная проблема, что нет смысла из-за этого делать какие-то далеко идущие выводы.

[DesertBrowser]

Спасибо за объяснения и советы. Далеко идущих выводов пока нет - только эксперименты и сравнение преимуществ и проблем. И всё же хотелось бы понять, почему не открывается ADO Connection

[DesertBrowser]

После сравнения скорости считывания данных (4000 записей, 25 полей) из результирующего набора получены следующие данные:
ODBC ResultSet - 41 сек
ADO RecordSet - 7 сек.
Учитывая, что сам набор данных формируется примерно 25 сек, гораздо привлекательнее в этом случае показывает себя ADO.
Поэтому первоначальный вопрос о том, как организовать ADO-подключение (своё для каждого пользователя, одно для всех под правами Аксапты, и т.п.?) для нужд выполнения аналитических запросов, остаётся открытым.

[MikeR]

на мой взгляд просто ODBC имеет больше примеров использования, хотя надо отдать должное тормозит основательно.
Я пользовал такой пример

 CCADOConnection     connection = new CCADOConnection();
    CCADOCommand        ccADOCommand;
    CCADORecordSet      record;
    int                 i=1;
    //str connectStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;"+
    //"Persist Security Info=False;Initial Catalog=Yourbase;Data Source=Yoursever";
    str connectStr = strFmt('Provider=SQLOLEDB.1;Persist Security Info=False;User ID=%1;Password=%2;Initial Catalog=%3;Data Source=%4'

            , 'username'  //username

            , 'Pa$$w0rd'  //pwd

            , 'database'  //database

            , 'server' //server

            );
    COM recordSet; 
    ;
    // Executing a SQL Statement
    try
    {
        connection.open(connectStr);
        ccADOCommand = new CCADOCommand();
//отправляемый запрос
        ccADOCommand.commandText("Select * from CLIENT");
        ccADOCommand.activeConnection(connection);
        record = ccADOCommand.execute();
        recordSet = record.recordSet();
        while (!record.EOF())
        {
            print record.fields().itemIdx(1).value();
            if(i>10) break;
            recordSet.moveNext();
            i++;
        }
        pause;
    }
    catch
    {
        error("An Exception has occurred");
    }
    connection.close();

более скоростных вариантов не видел

ЗЫ заведенный пользователь - под кем крутится АОС так и не привязанный к нему.

[Владимир Максимов]

Цитата:

Сообщение от DesertBrowser

После сравнения скорости считывания данных (4000 записей, 25 полей) из результирующего набора получены следующие данные:
ODBC ResultSet - 41 сек
ADO RecordSet - 7 сек.

"Не верю!"

Что именно Вы вкладываете в понятие "считывание данных"? Покажите тот тестовый код при помощи которого Вы тестировали для ADO и ODBC. Если Вы вообще не можете подключиться по ADO, то как же Вы тестировали-то?

Внутренние механизмы ADO и ODBC примерно одинаковые. Используется примерно одинаковый набор API-функций. Поэтому скорость закачки принципиальной разницы не имеет. Однако ADO возвращает результирующий набор в виде COM-объекта, который еще надо адаптировать под текущую среду.

Поэтому, скорее следует ожидать, что ADO будет работать чуть медленее, чем ODBC именно за счет дополнительной конвертации. Но это именно "чуть". Не думаю, что разница будет такой уж принципиальной.

[DesertBrowser]

Ещё в самом первом сообщении я писала, что

Цитата:

Пробую открыть коннект из Аксапты, указав в строке подключения тот самый файл udl - коннект открывается!

Таким образом и тестировала.
Под считыванием данных понимается перебор в цикле записей уже полученного ResultSet или RecordSet, операция получения значения первого - n-ого поля текущей записи (соответственно ResultSet.getString(n) и RecordSet.fields.item(n-1).value()) и вставка этих значений во временную таблицу.
Чтобы понять, на каком именно этапе возникает замедление при работе через ODBC на указанном выше объёме данных, последовательно исключались:
1. вставка во временную таблицу. Общее время операции не изменилось - 41 сек.
2. вставка во временную таблицу и считывание значений полей, кроме первого (т.е. оставила только перебор записей ResultSet в цикле и getString(1) с помещением результата в переменную). Итог - 3 сек.
3. для полноты эксперимента оставила перебор в цикле и вставку во временную таблицу констант, исключила resultSet.get...(). Результат <= 1сек.
Код для сравнения могу привести, но немного позже.
Для ADO использовала тот же подход, что и привёл MikeR, с двумя несущественными отличиями: из классов CCADO* использовала только CCADOField (из-за преобразования значений в методе value()) b после получения RecordSet делала recordSet.moveLast()+recordSet.moveFirst() (для обеспечения корректной работы с RecordSet, в частности получения корректного количества записей RecordCount() в наборе).

[Владимир Максимов]

Что-то я засомневался и решил тоже провести тестирование. Подключения в режиме доверительного соединения (Windows-аутентификация). Результаты такие

ODBC (Connection) = около 20 минут - если запуск в 3-х уровневой конфигурации на стороне клиента
ODBC (Connection) = около 5 минут - если запуск в 2-х уровневой конфигурации или в 3-х уровневой конфигурации на стороне сервера
ODBC (DRIVER=SQL Server) = около 5 минут
ADO (Provider=SQLNCLI) = около 13 минут - это Native 2005
ADO (Provider=sqloledb) = около 16 минут

Другими словами, результат вполне ожидаемый. ODBC работает быстрее, чем ADO. Однако при использовании объекта Connection в случае работы в 3-х уровневой конфигурации следует обратить внимание на то, на какой стороне выполняется код. На стороне клиента или на стороне сервера.

Я тестировал на таблице InventTable. У нас в ней около 50 тысяч записей и эта таблица виртуализирована, поэтому нет фильтра по DataAreaId. Всего в таблице более 150 полей, но я считывал только первые 81 поле, а в примере, приведенном ниже отобразил только первые 22

// Сравнение скорости работы ODBC по сравнению с ADO
static void JOB_ODBC_vs_ADO(Args _args)
{
    int             timeNowBegin;
    Connection      connection;
    OdbcConnection  connectBase;
    LoginProperty   lp;
    str             strConnectionString;
    Statement       statement;
    ResultSet       resultSet;

    CCADOConnection adoConnection;
    CCADOCommand    adoCommand;
    CCADORecordSet  adoRecordSet;
    CCADOFields     fields;
    CCADOField      field;

    InventTable     inventTableTmp,
                    inventTableTmpADO;

    int             totalI,
                    nextI;
    SysOperationProgress    sysOperaionProgress;
    ;

    // Количество для индикатора прогресса
    totalI = (select count(RecId) from inventTable).RecId;
    sysOperaionProgress = new SysOperationProgress(2);
    #aviFiles
    sysOperaionProgress.setAnimation(#aviTransfer);
    sysOperaionProgress.setCaption("Сравнение ODBC и ADO");
    sysOperaionProgress.setTotal(totalI, 1);
    sysOperaionProgress.setText("ODBC",1);
    sysOperaionProgress.setTotal(totalI, 2);
    sysOperaionProgress.setText("ADO",2);


    timeNowBegin = timeNow();

    strConnectionString = strFmt("DRIVER=SQL Server;SERVER=%1;DataBase=%2;Trusted_Connection=Yes","MyServerName","MyBaseName");
    lp = new LoginProperty();
    lp.setOther(strConnectionString);

    connectBase = new OdbcConnection(LP);
    statement = connectBase.createStatement();

/*
    // Это для тестирования прмого подключения
    connection = new Connection();
    statement = connection.createStatement();
*/
    resultSet = statement.executeQuery("SELECT * FROM inventTable with (nolock)");

    nextI = 0;
    inventTableTmp.setTmp();
    while (resultSet.next())
    {
        sysOperaionProgress.setText(strFmt("ODBC всего %1 осталось %2",totalI,totalI-nextI),1);
        sysOperaionProgress.incCount(1,1);
        nextI++;

        inventTableTmp.clear();
        InventTableTmp.ItemGroupId          = resultSet.getString(1);
        InventTableTmp.ItemId               = resultSet.getString(2);
        InventTableTmp.ItemName             = resultSet.getString(3);
        InventTableTmp.ItemType             = resultSet.getInt(4);
        InventTableTmp.PurchModel           = resultSet.getInt(5);
        InventTableTmp.Height               = resultSet.getReal(6);
        InventTableTmp.Width                = resultSet.getReal(7);
        InventTableTmp.SalesModel           = resultSet.getInt(8);
        InventTableTmp.CostGroupId          = resultSet.getString(9);
        InventTableTmp.ReqGroupId           = resultSet.getString(10);
        InventTableTmp.PrimaryVendorId      = resultSet.getString(11);
        InventTableTmp.NetWeight            = resultSet.getReal(12);
        InventTableTmp.Depth                = resultSet.getReal(13);
        InventTableTmp.UnitVolume           = resultSet.getReal(14);
        InventTableTmp.BOMUnitId            = resultSet.getString(15);
        InventTableTmp.Density              = resultSet.getReal(16);
        InventTableTmp.ScrapTypeId          = resultSet.getString(17);
        InventTableTmp.Dimension[1]         = resultSet.getString(18);
        InventTableTmp.Dimension[2]         = resultSet.getString(19);
        InventTableTmp.Dimension[3]         = resultSet.getString(20);
        InventTableTmp.Dimension[4]         = resultSet.getString(21);
        InventTableTmp.Dimension[5]         = resultSet.getString(22);
        // Все поля перечислять не буду

        inventTableTmp.insert();
    }   //     while (resultSet.next())

    info("ODBC="+time2str(timeNow()-timeNowBegin,1,1));

    timeNowBegin = timeNow();

    adoConnection    = new CCADOConnection();
// Это драйвер NATIVE 2005
//    adoConnection.open("Provider=SQLNCLI;Server=MySereverName;Database=MyBaseName;Trusted_Connection=yes;");
    adoConnection.open("Provider=sqloledb;Data Source=MyServerName;Initial Catalog=MyBaseName;Integrated Security=SSPI;");
    adoCommand = new CCADOCommand();
    adoCommand.activeConnection(adoConnection);
    adoCommand.commandType(1);
    adoCommand.commandText("SELECT * FROM inventTable with (nolock)");
    adoRecordSet = adoCommand.execute();
    fields = adoRecordSet.fields();

    nextI = 0;
    inventTableTmpADO.setTmp();
    while (! adoRecordSet.EOF())
    {
        sysOperaionProgress.setText(strFmt("ADO всего %1 осталось %2",totalI,totalI-nextI),2);
        sysOperaionProgress.incCount(1,2);
        nextI++;

        inventTableTmpADO.clear();
        inventTableTmpADO.ItemGroupId           = fields.itemIdx(0).value();
        inventTableTmpADO.ItemId                = fields.itemIdx(1).value();
        inventTableTmpADO.ItemName              = fields.itemIdx(2).value();
        inventTableTmpADO.ItemType              = fields.itemIdx(3).value();
        inventTableTmpADO.PurchModel            = fields.itemIdx(4).value();
        inventTableTmpADO.Height                = fields.itemIdx(5).value();
        inventTableTmpADO.Width                 = fields.itemIdx(6).value();
        inventTableTmpADO.SalesModel            = fields.itemIdx(7).value();
        inventTableTmpADO.CostGroupId           = fields.itemIdx(8).value();
        inventTableTmpADO.ReqGroupId            = fields.itemIdx(9).value();
        inventTableTmpADO.PrimaryVendorId       = fields.itemIdx(10).value();
        inventTableTmpADO.NetWeight             = fields.itemIdx(11).value();
        inventTableTmpADO.Depth                 = fields.itemIdx(12).value();
        inventTableTmpADO.UnitVolume            = fields.itemIdx(13).value();
        inventTableTmpADO.BOMUnitId             = fields.itemIdx(14).value();
        inventTableTmpADO.Density               = fields.itemIdx(15).value();
        inventTableTmpADO.ScrapTypeId           = fields.itemIdx(16).value();
        inventTableTmpADO.Dimension[1]          = fields.itemIdx(17).value();
        inventTableTmpADO.Dimension[2]          = fields.itemIdx(18).value();
        inventTableTmpADO.Dimension[3]          = fields.itemIdx(19).value();
        inventTableTmpADO.Dimension[4]          = fields.itemIdx(20).value();
        inventTableTmpADO.Dimension[5]          = fields.itemIdx(21).value();
        // Все поля перечислять не буду

        inventTableTmpADO.insert();

        adoRecordSet.moveNext();
    }

    info("ADO="+time2str(timeNow()-timeNowBegin,1,1));
    sysOperaionProgress.kill();

}

[DesertBrowser]

Тестирование для описанного мной выше примера проводилось для ODBC с использованием объектов Connection и UserConnection на стороне клиента в 3-х уровневой конфигурации.
Решила попробовать ODBCConnection (подключение в режиме доверительного соединения, Windows-аутентификация, сторона клиента в 3-х уровневой конфигурации).

strConnectionString = strFmt("DRIVER=SQL Server;SERVER=%1;DataBase=%2;Trusted_Connection=Yes","MyServerName","MyBaseName");//заменила на свои названия базы и сервера
    lp = new LoginProperty();
    lp.setOther(strConnectionString);
    connectBase = new OdbcConnection(LP); //здесь ошибка

И опять получаю ошибку - "Не удаётся пройти авторизацию при подключении к базе данных. Источник данных не найден и не указан драйвер, используемый по умолчанию". Создание файлового источника данных test.dsn и добавление в строку подключения "DSN=test;" не помогло, хотя должно работать и с ним и без него. Как быть?

[Владимир Максимов]

Попробуй выполнить тот же самый код, но на стороне сервера.

Сомневаюсь я, что доверительное соединение (т.е. Windows-аутентификация) будет разрешена для любого клиента. Вот для AOS (сервера приложения) возможно. Хотя тоже не факт. Это надо спрашивать администратора базы данных.

[DesertBrowser]

Дело в том, что системный администратор разрешил моей машине подключение к серверу по Win-аутентификации. И она проходит нормально для: ADO из Excel, при тестировании dsn-файла, при тестировании udl-файла, при подключении из Аксапты по udl-файлу (только для ado).
А при указании полной строки подключения в Аксапте как для ado, так и для odbc Win-аутентификация не проходит.
Код, который на стороне клиента не проходит, на стороне сервера не проходит тоже.

[Владимир Максимов]

В принципе, строгий синтаксис предполагает указание фигурных скобок

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;

Хотя, как правило, работает и без них. Попробуйте из Excel подключится не через ADO, а через ODBC соединение. Возможно просто поврежден драйвер ODBC и его надо переустановить. На сайте Microsoft поищите. Должен быть в бесплатном доступе для скачивания.

Еще один вариант подключения, это явное указание отдельных реквизитов, примерно так

    lp = new LoginProperty();
    lp.setDatabase("MyBase");
    lp.setServer("MyServer");
    // Фигураные скобки не обязательны
    lp.setOther("DRIVER={SQL Server};Trusted_Connection=Yes");

    connectBase = new OdbcConnection(LP);

Хотя, опять же, разницы быть не должно...

В крайнем случае, для целей тестирования, можно воспользоваться созданным вручную системным DSN через свойство LoginProperty.setDSN()

[DesertBrowser]

Строгий синтаксис с фигурными скобками и полной строкой уже пробовала, не помогло.
Отдельное указание реквизитов тоже пробовала и тоже не помогало, но...
Решила вывести в info lp.getOther() (для полной строки подключения) и увидела интересную вещь: в имени сервера есть обратный слэш "\", и если я его задаю единожды, то lp.getOther() возвращает строку без него. Если задаю в строке подключения двойной слеш, подключение всё равно не проходит, хотя и lp.getOther() в этом случае возвращает корректную строку.
А вот при отдельном указании ревизитов, если указать в имени сервера два слеша, подключение создаётся!
Так что спасибо за подсказку, буду тестировать дальше.

[DesertBrowser]

При использовании ODBCConnection на тех же данных время считывания данных сократилось до 1 сек. Странно, почему такая разница ODBCConnection с Connection и UserConnection?
С одной стороны, хорошо, что быстро. А с другой - не очень, т.к. ADO-рекордсет можно присвоить сводной таблице в Excel, а ODBC-рекордсет - нельзя.

[Владимир Максимов]

Так Вы же сами сказали, что запускали Connection и UserConnection в 3-х уровневой на стороне клиента. Запустите их на стороне сервера и время выполнения существенно сократится

На всякий случай : "На стороне сервера" означает, что у класса свойство RunOn = Server или используется статический метод с ключевым словом server

PS: Если имя сервера содержит служебные символы (обратный слеш), то это, скорее всего, и служит причиной того, что установить соединение не удается. Тут надо экспериментировать с экранированием (два обратных слеша подряд) или использовать дополнительные свойства (если есть).

Хотя, конечно, в идеале желательно не использовать в именах служебные символы.

[DesertBrowser]

1. После того, как в строке ADO-подключения вместо одного поставила двойной слеш, подключение создалось.
2. Переделала свой метод в статический и попеременно пробовала его вызов с модификатором server и без него, каждый для 3 вариантов - UserConnection, ODBCConnection, ADO (Win-аутентификация во всех, 3500 записей, 20 полей).
3. Результаты:
server client
UserConnection 4 27
ODBCConnection 4 1
ADO 9 6

Т.о., на 1 месте Client-ODBCConnection
на 2 - Server-ODBC/UserConnection
на 3 месте - Client-ADO (результат не из первых, но удобен в том случае, если требуется сводная таблица Excel)