Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

6 Март 2021, 13:41 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Часто задаваемые вопросы по LibreOffice и Apache OpenOffice.org
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1   Вниз
  Печать  
Автор Тема: Как заставить работать VBA-шный "CopyFromRecordset" в LibreOffice.  (Прочитано 1167 раз)
0 Пользователей и 1 Гость смотрят эту тему.
ost
Форумчанин
***
Offline Offline

Сообщений: 130


« Стартовое сообщение: 19 Январь 2021, 18:26 »

Доброго.

Ошибка времени выполнения 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.
« Последнее редактирование: 19 Январь 2021, 18:28 от ost » Записан
sokol92
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 308


WWW
« Ответ #1: 19 Январь 2021, 19:51 »

Можно так попытаться:
Код:
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
Форумчанин
***
Offline Offline

Сообщений: 130


« Ответ #2: 23 Январь 2021, 05:03 »

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

Записан
sokol92
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 308


WWW
« Ответ #3: 23 Январь 2021, 13:53 »

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

Владимир.
ost
Форумчанин
***
Offline Offline

Сообщений: 130


« Ответ #4: 27 Январь 2021, 20:14 »

Связываться с выгрузкой/загрузкой .csv очень не хочется.
А по поводу
Либо регистрируйте базу данных в LO.
не понял.
База .odb зарегистрирована в LO. Через кою и осуществляется общение Calc с базой SQLite. Не понятно, как регистрация базы в LO позволит победить проблемы использования VBA-шного "CopyFromRecordset" в LibreOffice? Растолкуйте, пож.
Записан
economist
Форумчанин
***
Offline Offline

Сообщений: 1 396


« Ответ #5: 27 Январь 2021, 21:38 »

Таблицу/Запрос/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, но его можно совместными усилиями тут победить.
« Последнее редактирование: 27 Январь 2021, 21:42 от economist » Записан

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...
sokol92
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 308


WWW
« Ответ #6: 28 Январь 2021, 15:20 »

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

Владимир.
sokol92
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 308


WWW
« Ответ #7: 30 Январь 2021, 16:49 »

Протестировал 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
Форумчанин
***
Offline Offline

Сообщений: 1 396


« Ответ #8: 31 Январь 2021, 16:14 »

При скорострельной "серии" запросов - может помочь пул подключений:  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 или знаний по нему.  
« Последнее редактирование: 31 Январь 2021, 16:19 от economist » Записан

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...
ost
Форумчанин
***
Offline Offline

Сообщений: 130


« Ответ #9: 28 Февраль 2021, 05:19 »

Огромное спасибо, 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 вставляются и заголовки с именем таблицы вида "<ИмяТаблицы>.<ИмяПоля>". Можно ли получить в лист заголовки без имени листа, т.е. просто "<ИмяЛиста>"?


« Последнее редактирование: 28 Февраль 2021, 05:43 от ost » Записан
sokol92
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 308


WWW
« Ответ #10: 28 Февраль 2021, 14:39 »

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

Владимир.
economist
Форумчанин
***
Offline Offline

Сообщений: 1 396


« Ответ #11: 28 Февраль 2021, 20:53 »

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

Таб.Поле реально мешает, это задает 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
Форумчанин
***
Offline Offline

Сообщений: 1 396


« Ответ #12: 1 Март 2021, 12:24 »

Покопался и нашел одну досадную багу с этим распрекрасным и быстрым 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. Поэтому возможность управлять всем этим - полезна.
« Последнее редактирование: 1 Март 2021, 12:29 от economist » Записан

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...
sokol92
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 308


WWW
« Ответ #13: 1 Март 2021, 15:47 »

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

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

Код:
Select json_object('{a, b}', '{1,2}') from dual

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

Код:
{"a" : "1", "b" : "2"}

« Последнее редактирование: 1 Март 2021, 18:30 от sokol92 » Записан

Владимир.
economist
Форумчанин
***
Offline Offline

Сообщений: 1 396


« Ответ #14: 1 Март 2021, 21:27 »

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

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

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...
Страниц: 1   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!