загрузка .dbf файлов отчетности в sql server express

[ck]

Добрый день, коллеги!

Кто хоть раз пытался анализировать базу отчетности Банка России, тот точно должен понимать, какой геморрой получается в процессе загрузки. Особенно если использовать бесплатную версию SQL Server Express. Типа такой, как у меня:

Код:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64>(Build 7601: Service Pack 1)

Начну с того, что бывают 32- и 64-разрядные версии этой программы. Исключительно по причине моего скудоумия я заказал себе под 64-разрядную W7 столько же разрядный SQL Server в надежде, наверно, на умопомрачительную скорость работы. Хотя, конечно же, все ограничения бесплатной версии SQL никуда не делись, а именно - он все так же использует максимально только 1Гб оперативной памяти и ограничение на размер базы тоже с нами. Подробнее с параметрами различных версий можно ознакомиться тут.

Итак, с 32-разрядной версией все было более-менее. Для загрузки используется драйвер Microsoft OLE DB Provider for Visual FoxPro 9.0, который можно быстро загрузить по ссылке с MSDN.
*Сделаю важную оговорку по поводу кодировки при использовании данного решения. В настройках драйвера ее изменить НЕ РЕАЛЬНО. Не поможет ни смена Collating sequence с mashine на russian, ни CODEPAGE с 866 на 1251 в свойствах канала передачи данных. Дело в том, что в те далекие времена, когда пилили 101 и 102 формы, видимо, забыли про страшно неприятную штуку - 29й байт, который, собственно, кодировку и обозначает. По причине его отсутствия, записать данный параметр драйвер пытается, но не может. Получаем примерно такие каракули "└тЄюЁ" вместо русских букв. Победить это, в принципе, можно, изменив значение 29-го байта в каждом файле физически. Писать как я не буду. т.к. считаю это не есть путь настоящего джедая, да и погуглить можно. В относительно новых формах (например, 134, которая выкладывается с середины 2010 года), байт на месте, проблема DOS-кодировок отсутствует.

Теперь 64-разрядная версия. С ней все хуже. Начнем с того, что фокспрошный драйвер под таким количеством разрядов не работает, иных бесплатных дров я не нашел. Есть следующий фишак, который я вычитал тут. Ставим на комп 32-разрядный сервер (да, он нормально будет работать даже с 64 битной осью), линкуем его с нашим текущим сервером. В x32 грузим через VFP OLE DB, из него тянем в x64. Но это как-то уж чересчур!

Хорошо, что для загрузки чего-то в SQL можно, в принципе, и не иметь ничего, кроме SQL. Оказывается теперь наше все - это .Net Framework Data Provider for ODBC. НО! До каких-либо действий по загрузке таблиц, необходимо создать новую базу и изменить ей кодировку с default на какую-нить Cyrillic, см. рисунок 1 (возможно я ошибаюсь, но сменить кодировку уже созданной базы нельзя. По крайней мере, это справедливо для express версии). В противном случае мы получим те же иероглифы вместо русских букв.

Теперь можно добавить таблицы. Включаем визард (ПКМ на имени базы - Tasks - Import Data). В Data source выбираем .Net. В поле СonnectionString надо прописать строку подключения (Ваш К.О.). Я использовал вот такую (вместо c:\Temp надо указать путь к папке, где лежат DBF-ки):

Код:

Driver={Microsoft dBase Driver (*.dbf)};sourcetype=DBF;DefaultDir=c:\Temp;exclusive=No;null=No;deleted=No;backgroundfetch=No

К слову, постарайтесь указать максимально короткий путь к папке и английскими буквами. Почему? Драйвер, который мы используем, не сильно быстрый и не сильно умный. Не быстрый, это вы увидите и сами, а вот про ум.. он воспринимает имена файлов в формате DOS 8.3 что для непосвященного пользователя (читай, для меня) ничерта не значит. Однако ошибка на этапе загрузки любых форм, кроме 101 и 102 в 90% случаев означает то, что имя DBF-файла слишком длинное! Должно быть не более 8 символов. А если такое ограничение на имя, то что же с основным путем? (не проверял, тут с первого раза прокатило). Есть неприятная проблема со 135 формой. Она разбита на разделы, содержание которых меняется от даты к дате. Кроме того, дата отчета в большинстве разделов зашифрована только в названии. Задачу кодирования даты и признака отчета в данных форм я оставляю на Ваше усмотрение. Тем более, это не последняя проблема!

Конец света приблизился, когда у меня вылетели ошибки типа тех, что на рисунке 2. Собственно там все из него и следует. Но для меня, после стольких мучений, формат поля DT с типом "23" явился чем-то вроде пощечины. Я более не мог думать над автоматизацией процесса и руками менял названия файлов на восьмисимвольные, а также руками проставлял типы поля дата каждом отчете, в котором возникала приведенная ошибка. После этого настал чудный миг, и за каких-то полчаса все отчеты попали в мой сервер.

Выводы: SQL сервак - хорошая, быстрая, удобная и бесплатная штука. Конечно, если речь идет о его 32-разрядной версии! И не идет о данных Банка России!

Надеюсь, кому-то я сумею данным постом сэкономить рабочий день
ck

Использованы материалы 1, 2, 3

Последний раз редактировалось ck 07 авг 2014, 16:00, всего редактировалось 2 раз(а).

[ck]

Рисунок 1

Вложения

ex1.jpg (160.29 КБ) Просмотров: 11903

[ck]

Рисунок 2

Вложения

ex2.jpg (144.85 КБ) Просмотров: 11903

[kuapru]

Сейчас на ЦБ РФ можно пользоваться веб-сервисами SOAP. Надо отметить, что они не позволяют получить некоторую информацию (например, число точек продаж - только список филиалов). Но большое спасибо программистам регулятора и на этом. Что касается технологии в целом - можно послать на сервер XML-запрос, получив такой же XML-ответ.

1) У меня необходимости разбираться именно под SQL Server не было, но попробуйте копнуть сюда - тут разбирают процедуру обращения к сервису:

http://www.sql.ru/forum/576338/konnekto ... te#5933931

2) Наряду с бесчисленным множеством языков, можно использовать стороннюю приблуду - SOAPUi - http://www.soapui.org/Getting-Started/y ... oject.html

Она позволяет посылать запросы и даже после некоторых танцаев с бубном сохранять результаты в БД, на сайте есть туториалы.

3) Конкретный пример - запрос на PHP для получения информации по КО по коду.
Качаем библиотеку nuSOAP - http://sourceforge.net/projects/nusoap/
Устанавливаем, пишем скрипт:

Код:

require_once('nusoap/lib/nusoap.php');

$proxyhost = isset($_POST['proxyhost']) ? $_POST['proxyhost'] : '';
$proxyport = isset($_POST['proxyport']) ? $_POST['proxyport'] : '';
$proxyusername = isset($_POST['proxyusername']) ? $_POST['proxyusername'] : '';
$proxypassword = isset($_POST['proxypassword']) ? $_POST['proxypassword'] : '';

//тут указываем адрес WSDL
$client = new nusoap_client('[b]http://www.cbr.ru/CreditInfoWebServ/CreditOrgInfo.asmx?WSDL[/b]', 'wsdl',$proxyhost, $proxyport, $proxyusername, $proxypassword);
$err = $client->getError();

$client->decode_utf8 = false;

if ($err) {
   echo '<h2>Constructor error</h2><pre>' . $err . '</pre>';
}

$call = "CreditInfoByIntCodeXML";
[b]$IntCode = "350000004;[/b] //это внутренний код с сайта ЦБ
      
[b]$request = '<CreditInfoByIntCodeXML  xmlns="http://web.cbr.ru/">
      <InternalCode>placeholder</InternalCode>
    </CreditInfoByIntCodeXML >';[/b] //это текст XML-запроса

      $request = str_replace("placeholder", $IntCode, $request);
      
      $result = $client->call($call, $request);
      // Check for a fault
      if ($client->fault) {
         echo '<h2>Fault</h2><pre>';
         print_r($result);
         echo '</pre>';
      } else {
         // Check for errors
         $err = $client->getError();
         if ($err) {
            // Display the error
            echo '<h2>Error</h2><pre>' . $err . '</pre>';
         } else {
            // Display the result
            echo '<h2>Результат</h2><pre>';
            print_r($result);

            echo '</pre>';
         }
      }

[ck]

по SOAPUi - глянул сайт, в принципе, прикольная штука. Жаль что не шаровая. Триал 14 дней меня мало интересует, а покупать.. да проще использовать имеющиеся средства.

[kuapru]

Не-Pro версия вообще бесплатная. Она умеет грузить в БД через внутренние скрипты, как я понял. Была где-то ссылка на туториал, поищу.

[ck]

Не-Pro версия вообще бесплатная. Она умеет грузить в БД через внутренние скрипты, как я понял. Была где-то ссылка на туториал, поищу.

Сори тогда)) Посмотрю поподробнее.

[kuapru]

Вот:

http://dunmanifestin.blogspot.ru/2012/1 ... -jdbc.html

ключевое слово - groovy SQL, по нему есть и официальный soapUI-шный туториал, но тут человек достаточно подробно разобрал.

[ost]

какой геморрой получается в процессе загрузки. Особенно если использовать бесплатную версию SQL Server Express

Да, если использовать продукты MS, то гемора не оберешься.
Лучше посмотреть в сторону опен-сорс.
mySQL есть....
А перловый скриптик dbf2oracle (читай конвертилка dbf в SQL insert) ещё в 96-ом году написан и расшарен.
Короче, реальные пацаны все эти муки прошли ещё до 2000-го года. Мог бы и спросить, подсказали б...