Как заставить работать VBA-шный "CopyFromRecordset" в LibreOffice.

[ost]

Доброго.

Ошибка времени выполнения Basic на строке

Код:

ThisWorkbook.Sheets("Aliases").Range("A2").CopyFromRecordset rst

В модуле указаны

Код:

Option VBASuppor
Option Compatible

Подскажите, пож., можно ли как-нибудь победить сие малой кровью. Много кода на VBA, мало времени на переписывание специально под LO.
Спасибо.

На борту:
Version: 7.0.4.2 (x64)
Build ID: dcf040e67528d9187c66b2379df5ea4407429775
CPU threads: 2; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); ИП: ru-RU
Calc: threaded

Подключение ODBC (драйвер sqliteodbc-0.9998, версия х64. см. http://www.ch-werner.de/sqliteodbc) к SQLite3.

[sokol92]

Можно так попытаться:

Код:

Sub TestRecordset
  Dim arr, arr2, i As Long, j As Long, oFA, oRange

  ' моделирование массива arr
  'ReDim arr(1, 100)
  'For i=0 To Ubound(arr,1)
  'For j=0 To Ubound(arr,2)
  '  arr(i,j)=10*i+j
  'Next j
  'Next i
   
  arr=rst.GetRows()     ' получаем двумерный массив: первое измерение - номер поля, второе - номер записи
                        ' Метод ADO.GetRows  https://docs.microsoft.com/ru-ru/office/client-developer/access/desktop-database-reference/getrows-method-ado
 
  oFA = createUnoService( "com.sun.star.sheet.FunctionAccess" )
  arr2=oFA.CallFunction("TRANSPOSE", Array(arr))   ' транспонировали массив
  oRange=ThisComponent.Sheets("Aliases").getCellRangeByPosition(0, 1, uBound(arr2(0)), Ubound(arr2)+1)   ' присваиваем, начиная с ячейки "A2"
  oRange.setDataArray arr2
 
End Sub

[ost]

Спасибо, Владимир.
Таким образом элементы длиной более 255 похерятся? И Recordset у меня может быть очень объемным. =(

[sokol92]

Одна из альтернатив - выгружайте результат запроса в .csv-файл, затем импортируйте в Calc. Либо регистрируйте базу данных в LO.

[ost]

Связываться с выгрузкой/загрузкой .csv очень не хочется.
А по поводу

Либо регистрируйте базу данных в LO.

не понял.
База .odb зарегистрирована в LO. Через кою и осуществляется общение Calc с базой SQLite. Не понятно, как регистрация базы в LO позволит победить проблемы использования VBA-шного "CopyFromRecordset" в LibreOffice? Растолкуйте, пож.

[economist]

Таблицу/Запрос/View из базы ODB можно легко отражать в Листе Calc самым быстрым способом из возможных, это doImport в DataBaseRanges. Повторю себя:

Код:

oDBR = thisComponent.DataBaseRanges.getByName("Импорт1")
oDesc() = oDBR.getImportDescriptor()
oDesc(0) = "ДебиторыODB" ' легко заменить и движок FB/SQLite/CSV/PG итд, и таблицу, и запрос"!
oDesc(2) = "SELECT * FROM tablename WHERE..."
oDBR.getReferredCells.doImport(oDesc())

Затык может быть из-за EscapeProcessing, но его можно совместными усилиями тут победить.

[sokol92]

Согласен с коллегой.  Использование метода DoImport представляется наиболее эффективным.

[sokol92]

Протестировал doImport на "боевых" данных.

Код:

Sub TestImpor2
 Dim oDoc, oRange, oDesc, t As Long
 oDoc = StarDesktop.LoadComponentFromUrl("private:factory/scalc", "_blank", 0, Array())
 oRange=oDoc.Sheets(0).getCellRangeByName("A1")
 oDesc=oRange.createImportDescriptor(False)
 oDesc(0).value="Postgre"
 oDesc(1).value=1
 oDesc(2).value="Мой SQL-запрос"
 t=getSystemTicks
 oRange.doImport oDesc
 msgbox getSystemTicks-t
End Sub

База данных PostgreSQL, драйвер - родной от LO. Запрос возвращает 100 000 строк и 10 столбцов (полей).
Время выполнения составило менее 3 секунд, что лучше (моих) ожиданий.

[economist]

При скорострельной "серии" запросов - может помочь пул подключений:  Alt+F12 - Base - Подключения

Чем еще хорош doImport - так это тем что легко сменить движок, таблицу, запрос, набор столбцов (но включить "неотключаемые" столбцы, которые нужны почти всегда). И все это конструируется в коде довольно простыми строковыми методами, особливо ежли вставить в модуле Option VBASupport 1 и использовать его ограниченно - только для [имен_диапазонов] и [A1:A6] синтаксиса, а все остальное писать - на "чистом" LO Basic API. В Calc удобно хранить и собирать тонны запросов, это не Base c одноуровневым неудобным списком.

Своеобразный антипод doImport - RecordSet. Вот он, похоже, нужен теперь редко - только если надо "ползать" по соседним данным. Да и то, с появлением в SQL оконных функций CTE WITH - можно всегда обойтись без него, затянув нужное через doImport и отобрав уже в Calc формулами итп. Жаль что JDBC-драйвер для SQLite 3.7 (не требует установки, просто копируется в LO, включая portable) не умеет в CTE, а новый - у меня не собирается c http://ch-werner.de

У DataBaseRange есть еще параметры Строка итогов, и возможность автокопирования формул Calc (справа от диапазона, при изменении числа строк), что позволяет "табличникам" легко компенсировать недостаток SQL или знаний по нему.  

[ost]

Огромное спасибо, sokol92, economist.
sokol92, ткните, пож., носом в то место, где можно почитать о том, что означает в 7 посте строчка "oDesc(1).value=1" , где вообще можно почитать о параметрах doImport, приемах  работы. Спасибо.

Add
Методом научного тыка   определил, что, похоже:
в случае oDesc(1).value= 2 осуществляется попытка получить данные из базы по запросу вида "SELECT * "  к таблице из oDesc(2).value
в случае oDesc(1).value= 3 осуществляется попытка получить хранимый запрос из oDesc(2).value
все остальные циферки в oDesc(1).value отсылают текст запроса из oDesc(2).value

В, общем, буду оч. признателен, за любую информацию.

Add
При использовании doImport в лист Calc вставляются и заголовки с именем таблицы вида "<ИмяТаблицы>.<ИмяПоля>". Можно ли получить в лист заголовки без имени листа, т.е. просто "<ИмяЛиста>"?

[sokol92]

Добрый день! Указанный Вами метод часто дает верные результаты.
Метод createImportDescriptor возвращает массив структур PropertyValue c именами свойств: "DatabaseName", "SourceType", "SourceObject", "IsNative". Назначение этих свойств описано здесь. Мой пример носит чисто ознакомительный характер, в "серьезных" макросах так писать нельзя, посколько число и порядок свойств в последующих версиях могут измениться. Кстати, в указанном примере возвращаются только имена полей, без указания имени таблицы.
Дальнейших успехов в изучении LO!

[economist]

вставляются и заголовки с именем таблицы вида "<ИмяТаблицы>.<ИмяПоля>"

Таб.Поле реально мешает, это задает ODBC/JDBC-драйвер SQLite, но выход есть:
используем aliases - псеводнимы:

SELECT Поле1 Поле1, Поле2 Поле2 FROM Табл

Если же хочется SELECT * FROM Табл - то имена таблиц вылезут непременно.
Если лень вместо * набирать все имена полей, как в примере выше - то можно считать имена процедурно и спарсить их оттуда:

SELECT sql FROM sqlite_master where name='Табл'     

Либо использовать pragma function: PRAGMA table_info (Табл)

[economist]

Покопался и нашел одну досадную багу с этим распрекрасным и быстрым doImport.

В документации написано что IsNative при значени False - не использует парсер Base (т.е. не портит SQL-запрос, а передает его движку базы данных напрямую, на исполнение). Ровно это же делает и параметр EscapeProcessing = False в коде Basic.

Так вот, оказалось что IsNative - на самом деле не работает (LO 5-6-7)

Т.е. любой SQL-запрос при doImport все-таки парсится и иногда "ломается". А это значит что и при False, и при True - запрос, использующий специфические не SQL-92 фичи (например в SQLite/PostrgreSQL - это datetime-функции итд) - будет вызывать такую ошибку:

Код:

SELECT * FROM Табл WHERE Дата >= date('now', 'start of month')

Синтаксическая ошибка в выражении SQL

Обход бага все-таки есть, с небольшим геморроем размером в кулак:

1) макросом "налету" создаем в ODB "хранимый" именованный запрос (да, мусор, но можно просто затирать один и тот же)

2) ставим ему признак EscapeProcessing = False

3) передаем методу doImport не "SELECT...", а имя именованного запроса. И вот он уже работает ОК.

Код тут:
https://lists.freedesktop.org/archives/libreoffice/2019-March/082285.html

Парсер запроса иногда полезен, например для параметрических запросов из Форм Base. Поэтому возможность управлять всем этим - полезна.

[sokol92]

Спасибо за интересную информацию!

Повторил эксперимент из #7 в отношении запроса:

Код:

Select json_object('{a, b}', '{1,2}') from dual

Такой конструкции точно нет во встроенном парсере LO. Запрос успешно выполняется, на выходе:

Код:

{"a" : "1", "b" : "2"}

[economist]

Ломаются запросы очень выборочно, системы пока не нашел. Возможно нативный коннектор к PostgreSQL просто пропускает всё как есть. А вот в SQLite через JDBC/ODBC - не всё. Но часть datetime функций - работает нормально.

Вероятность ошибки, кмк, растет, если использовать сложные запросы с подзапросами, UNION , с оконными функциями итп. В любом случае хорошо что есть обходной путь и что ядро у LO - общее, запрос в Base сохраняется/вызывается обратно - мгновенно.