[prog] Открыл для себя команду OUTPUT в T-SQL

Как-то так сложилось, что с реляционными БД дело приходилось иметь не часто и не серьезно. Поэтому знания SQL у меня самые поверхностные и общие. Посему прошу опытных людей сильно надо мной не смеяться, полагаю, что для многих из вас то, что я расскажу ниже давно знакомо.

Если мне приходится работать с РСУБД, то довольной распространенной операцией является такая – поднять N строк из какой-то таблицы по какому-то условию, затем для этих N строк обновить столбец, который хранит дату и время обработки строки.

Пользуясь обычным SQL эта операция выполняется так: сначала выбирается N строк, на затем выполняется UPDATE. Причем, второй шаг можно делать по разному. Можно тупо в цикле для каждой строки вызывать UPDATE, а можно сначала ключи обновляемых строк поместить во вспомогательную временную таблицу, после чего запустить один UPDATE (этот подход, по моим наблюдениям, обычно работает быстрее, по крайней мере, в условиях работы с MSSQL через ODBC.).

Но недавно таки заглянул в MS-овскую документацию по T-SQL и обнаружил там интересную штуку, которая изрядно облегчает жизнь и ускоряет работу с БД – это команда OUTPUT, которая может использоваться внутри T-SQL-ых SELECT, INSERT, UPDATE и DELETE.

С ее помощью приведенный выше пример с выборкой и обновлением метки времени вырождается в более эффективную последовательность действий: сначала вызывается UPDATE для N первых строк, удовлетворяющих нужному условию, а в секции OUTPUT этого UPDATE предписывается поместить содержимое нужных мне столбцов измененных строк во временную таблицу. После чего делается простой SELECT из временной таблицы. Что-то вроде:

UPDATE TOP(N)   SET processed_at = GETDATE()   OUTPUT        inserted.column_1,        inserted.column_2,        ...        inserted.column_N     INTO #tmp_table   WHERE ...; SELECT * FROM #tmp_table; TRUNCATE TABLE #tmp_table;

Получается очень удобно, намного удобнее, чем через “обобщенный SQL”. Правда тут происходит более жесткая привязка к конкретной БД. Плюс еще особенности (в частности, порядок следования столбцов в OUTPUT должен в точности соответствовать порядку следования столбцов во временной таблице). Но если нет необходимости поддерживать разные типы БД, то вполне работоспособно.

PS. А еще в T-SQL есть какие-то table variables, с которыми я пока еще не разобрался, руки еще не дошли.

[prog] Нашел баг в OTL

Некоторые 32-битовые ODBC-драйвера (например, производства Oracle) не поддерживают 64-битовых целых чисел (т.е. не могут работать с bigint-ами). В библиотеке OTL для таких случаев есть обходной маневр: группа макросов OTL_BIGINT, OTL_BIGINT_TO_STR и OTL_STR_TO_BIGINT. Если их должным образом определить, то OTL автоматически заменяет работу с 64-битовыми числами на работу с их строковыми представлениями. И приложение даже не замечает, что ему приходится работать с ущербным ODBC-шным драйвером.

К сожалению, в версиях OTL до 4.0.257 включительно, комбинация из OTL_BIGINT/OTL_BIGINT_TO_STR/OTL_STR_TO_BIGINT и OTL_ODBC/OTL_ODBC_MULTI_MODE не обрабатывалась должным образом. Не производилось преобразования чисел в строку и обратно.

К счастью, OTL – это OpenSource-библиотека. И хотя там достаточно своеобразный подход к организации кода (вся библиотека – это один большой h-файл), но удалось весьма быстро найти как причину проблемы, так и способ ее устранения.

Сразу же после этого описание найденной ошибки были отправлены автору библиотеки, Сергею Кучину, который уже опубликовал версию 4.0.258 с исправлениями. За что ему респект и уважуха!

PS. Все-таки библиотеки с полными исходниками есть рулез! :)

PPS. А по поводу качества Oracle-вских продукт пердело поделок я еще скажу пару ласковых.

[prog] OTL4, MSSQL, bulk insert, триггер на вставку и ошибка 24000

Нужно было сделать быструю вставку записей в табличку MSSQL. Задействовал нестандартную SQL-ную фичу с конструкцией insert into … select … union all … select … union all. Работал через OTL4 и сделал пул otl_nocommit_stream-ом (с разным количеством строк внутри). Например, если в пуле были otl-потоки для 20, 10, 5, 2 и 1-ой строки, а нужно было вставить 59 строк, то два раза использовался поток на 20 записей, один раз поток для 10 строк, один раз поток для 5 строк и два раза поток для 2-х строк.

Все работало хорошо до тех пор, пока в базе не появился триггер на вставку в эту таблицу. Триггер должен был срабатывать на insert и добавлять строки в другую таблицу. Но после того, как он срабатывал, у меня при выборе следующего otl-потока из пула возникала ошибка 24000: [Microsoft][SQL Server Native Client 10.0]Invalid cursor state.

Поиск по Интернету показал, что такие вещи при работе через ODBC уже случались. И в качестве workaround-а рекомендовали после каждого SQLExecute для вставки в основную таблицу делать обращение к SQLMoreResults.

Легко это сделать когда ты сам осуществляешь вызовы SQLExecute. Но за меня это делает OTL. Да еще делает это неявно для меня, внутри своих otl-потоков. Так что как применить данную рекомендацию поначалу было непонятно.

К счастью, OTL доступен в исходных текстах (более того, весь OTL – это один большой заголовочный файл). Посредством поиска удалось выяснить, что обращение к SQLMoreResults в OTL есть. Происходит оно внутри метода sql_row_count(), а sql_row_count() вызывается после обращения к SQLExecute.

Но sql_row_count() использует SQLMoreResults не всегда, а только когда определен макрос OTL_ODBC_ALTERNATE_RPC. И в документации к OTL_ODBC_ALTERNATE_RPC сказано, что он предназначен для PostgreSQL:

This #define should be used with the PostgreSQL ODBC driver. The driver returns as many row counts via SQLRowCount() calls as there are rows in a batch INSERT statement. The #define enables a loop that fetches all individual row counts and sums them up (+=). As a result, otl_stream::get_rpc() returns the total, which is correct for PostgreSQL. Normally, commercially available ODBC drivers return a single row count on a batch INSERT.

Тем не менее, определил макрос OTL_ODBC_ALTERNATE_RPC и все заработало!

Мораль сей басни – доступные исходники сторонних библиотек – это must have. :)

[prog] Маленький хинт: MS SQL Server, NT Authentification и ODBC

Из C++ных программ работаем с MS SQL Server через ODBC. Обычно настраиваем строку подключения вручную (т.е. с указанием параметров DRIVER, SERVER и пр.), хотя иногда используется и настройка через системный DSN. Но всегда раньше подключались к БД через SQL Server-аутентификацию.

А тут потребовалось для подключения к MS SQL-ю использовать NT Authentification. Попробовали настроить системный DSN (из под другого пользователя-администратора), но при попытке подключиться к БД натыкались на ошибку: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

Честно скажу, фиг знает почему не удавалось найти DSN. Но в результате гугления нашелся более простой способ. Оказывается, в параметрах подключения нужно указать “Trusted_Connection=yes”. Например, строка подключения к БД может иметь вид:

DRIVER={SQL Native Client};SERVER=somehost;DATABASE=somedb;Trusted_Connection=yes

И все, и не нужно DSN-ы настраивать.

PS. Источник.