isql to xml Linux

Столкнулся давича с необходимостью выгрузки результата консольного запроса к BD Firebird в XML.
Выполнить запрос к FB из консоли можно при помощи утилиты isql.
Оказалось, что линуховый isql штатными средствами этого не умеет (выньдовый, к стати, умеет цуко).

Погуглив вдоволь, нашел 2 варианта решения. Первый, быстрый и универсальный: не зависит от текста запроса и количества строк в результате запроса. Второй, рассчитан только на элементарные тексты запросов и "долгий" , при большом количестве строк в результате запроса.

Итак, вот, собственно, оба решения:


Вариант 1. Воспользоваться утилитой odbcsql (спасибо hellcat) :


Утилиту можно скачать здесь. Утилита кросс-платформенная. Не чувствительна к битности ОС. Инструкция по установке и необходимые драйвера в комплекте.

Принцип работы с qdbcsql такой:

$odbcsql DSN=mydatabaseDSN Query.sql Output.xml Error.log

где
        DSN=mydatabaseDSN  - DSN или строка подключения к БД
        Query.sql - файл, в котором лежит текст SQL-запроса, который необходимо выполнить
        Output.xml - файл, в который необходимо положить результат запроса (в формате XML)
        Error.log - файл, в котором будут лежать логи


Вариант 2. Добавляем в БД две хранимых процедуры (stored procedure):

XML_GENERATOR_FIELDS:

SET TERM ^ ;

CREATE PROCEDURE XML_GENERATOR_FIELDS(
FIELDS VARCHAR(1024),
SQL_RIGHT VARCHAR(1024))
RETURNS (
RECORD INTEGER,
XML_TEXT VARCHAR(1024))
AS
declare variable i integer;
declare variable len integer;
declare variable c char(1);
declare variable field varchar(100);
BEGIN

/* lenght fields */
len = char_length(fields);

/* loop in fields string */
i = 1;
field = ''; /* clear field */
while (i <= len) do
begin

/* current char */
c = substring(fields from :i for 1 );

/* if comma, select values of current field */
if (c = ',') then
begin

/* trim fields */
field = trim(:field);

/* order by record in the sp (xml_generator) */
record = 0;

/* values of current field */
for execute statement
cast( 'select ' || :field || ' from ' ||
:sql_right as varchar(1024))
into :xml_text do
begin

/* formatting the xml line */
/* <field>value</field>    */
xml_text = '<' || :field || '>' ||
:xml_text || '</' || :field || '>';

/* order by in xml_generator */
record = record + 1;
suspend;

end

/* clear field */
field = '';

end

else

/* char by char... */
field = field || c;

/* next char */
i = i + 1;

end

END^

SET TERM ; ^

XML_GENERATOR:

SET TERM ^ ;SET TERM ^ ;

CREATE PROCEDURE XML_GENERATOR(
PHEADER VARCHAR(1024),
PSQL VARCHAR(1024))
RETURNS (
XML_TEXT VARCHAR(1024))
AS
declare variable field varchar(30);
declare variable fields varchar(1024);
declare variable fields_begin smallint;
declare variable fields_end smallint;
declare variable from_end smallint;
declare variable sql_end smallint;
declare variable sql_right varchar(1024);
declare variable i integer;
declare variable records integer;
declare variable table_name varchar(30);
BEGIN

/* uppercase and trim psql parameter */
psql = upper(trim(psql));

/* extract fields from psql parameter */
fields_begin = position(' ',:psql)+1;
fields_end = position(' FROM ', psql);
fields = substring(psql from :fields_begin
for :fields_end-:fields_begin) ||
','; /* for extract the last field */

/* extract table name, where, order by, etc... */
from_end = fields_end + 6;
sql_end = char_length(psql)-from_end+1;
sql_right = substring(psql from :from_end for sql_end) || ' ';

/* extract only table name for "all fields" and */
/* open/close tags in xml */
table_name = substring(sql_right from 1
for position(' ',sql_right)-1);

/* if fields = '*', fields = "all fields" of the table */
if (fields = '*,') then
begin
fields = '';
for select trim(rdb$field_name) from rdb$relation_fields
where rdb$relation_name = :table_name order by rdb$field_id
into :field do fields = fields || :field || ',';
end

/* if pheader xml isnull... */
if (:pheader is null) then
pheader = '<?xml version="1.0" encoding="UTF-8" ?>';

/* returns pheader to client... */
xml_text = :pheader;
suspend;

/* open table */
xml_text = '<' || :table_name || '>';
suspend;

/* record count... */
execute statement
cast('select count(*) from ' || :sql_right as varchar(1024))
into :records;

/* fields from another stored procedure */
/* loop first to last record */
i = 1;
while ( i <= records ) do
begin

/* open record */
xml_text = '<RECORD>';
suspend;

/* fields and values in another stored procedure */
for select xml_text from xml_generator_fields(:fields, :sql_right)
where record = :i into :xml_text do
suspend;

/* close record */
xml_text = '</RECORD>';
suspend;

/* next record */
i = i + 1;

end

/* close table */
xml_text = '</' || :table_name || '>';
suspend;

END^

SET TERM ; ^

Собственно эти две процедуры генерируют нам на выходе результат запроса уже в готовой XML схеме.

Выгружаем результат запроса в XML:

Создаем SQL-скрипт и пишем в него запрос:

$touch Script.sql
$echo "SELECT XML_TEXT FROM XML_GENERATOR('<?xml version="1.0" encoding="windows-1251" ?>',' select a from ADMINUSERS' )"

Здесь, параметр 1 ('<?xml version="1.0" encoding="windows-1251" ?>') - это загаловок нашего XML-файла, а параметр 2 (' select a from ADMINUSERS') - это интересующий нас запрос

Далее даем шыйд команду на выполнение нашего запроса и сохранение его результатов в файл:

$isql MixPos -b -x0x0A < Script.sql > Result.xml

где

- "MixPos" - это DSN для нашей БД

- параметр "-b" отключает лишние комментарии командной строки isql, чтоб остался только результат запроса без дополнительной служебной информации

- параметр "-x0x0A" заменяет разделитель колонок в результате запроса на "Enter"

- "Script.sql" - это файл, где лежит тест запроса

- "Result.xml" - это необходимы нам XML-файл, куда вернется результат запроса



Проверим выполнение:

$isql MixPos -b -x0x0A < Script.sql > Result.xml

Получим такой вот Result.xml:

<ADMINUSERS>
         <RECORD>
                <ADMIN_ID>1</ADMIN_ID>
                <PASSWD>********</PASSWD>
                <PUBLISHED>P</PUBLISHED>
                <LOGIN>admin</LOGIN>
         </RECORD>
</ADMINUSERS>