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

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

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

Войти
Новости: Доступно и просто о работе в офисных пакетах
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1   Вниз
  Печать  
Автор Тема: Как поместить результат запроса к Base в ячейку таблицы Calc?  (Прочитано 2135 раз)
0 Пользователей и 1 Гость смотрят эту тему.
niko-rybalko
Новичок
*
Offline Offline

Сообщений: 2


« Стартовое сообщение: 23 Май 2017, 00:08 »

База подключена в источниках данных.
Пусть в базе имеется таблица с полями id (первичный ключ), sname, ... Размер базы - ~1 500 000 записей
Как сделать, чтобы при вводе в ячейку таблицы LO Calc какого либо значения из поля id в другой ячейке появлялось соответствующее ему значение sname?
т.е. результат

SELECT
    sname
FROM
    cnsi
WHERE
     id = 123;
где 123 - значение, введенное в ячейку.
Как это '123' передать в запрос? Можно "штатными средствами" или только макрос?

Спасибо.
Записан
rami
Гуру
*******
Offline Offline

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


MacBook Pro, LibreOffice и Apache OpenOffice


« Ответ #1: 23 Май 2017, 09:21 »

Как это '123' передать в запрос? Можно "штатными средствами" или только макрос?
Томагавками что ли  Шокирован

Если речь о вставке значения из базы в произвольную ячейку таблицы Calc, то лучше сделать пользовательскую функцию на запросе к базе.
Сама пользовательская функция (макрос):
Код:
Function sname(id)
Dim dbContext, oDataSource, db, pstmt, oResult, s$
dbContext=createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource=dbContext.getByName("ab")
db=oDataSource.getConnection("","")
pstmt=db.prepareStatement("SELECT ""sname"" FROM ""cnsi"" WHERE ""id"" = :x")
pstmt.setLong(1,id)
oResult=pstmt.executeQuery()
db.Close
db.dispose()
Do while oResult.Next
s=oResult.getString(1)
Loop
sname=s
End Function

Если в ячейке A1 записано значение id, то формула в другой ячейке =SNAME(A1) вернёт искомое.

Обратите внимание на названия базы, полей и регистры.
Записан

economist
Форумчанин
***
Offline Offline

Сообщений: 965


« Ответ #2: 23 Май 2017, 14:06 »

Несколько дополнений, если можно:

- Название базы данных должно быть без .ODB, она должна быть "зарегистрирована", то есть видна в Сервис-Параметры-OLOBase-Базы данных. Сам часто "попадал" по запарке.

- Если база данных не "встроенная" HSQL, а внешняя (SQLite, PostgreSQL, Access, любая ODBC итд) - иногда выходит совершенно непонятная ошибка, и тогда нужно программно "нажать" на неприметную кнопку Native SQL, чтобы парсер запроса Base не испортил сам запрос. Для этого надо добавить перед oResult=
строку pstmt.EscapeProcessing=FALSE

 
« Последнее редактирование: 23 Май 2017, 14:26 от economist » Записан

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

Сообщений: 2


« Ответ #3: 24 Май 2017, 03:21 »

Спасибо за ответы. Подключаю (пытаюсь) Вашу функцию.

P.S. Жаль, что в Calc нет встроенной функции, принимающей в параметрах SQL запрос.
Осознал, что для понимания нужно изучить:
- "Статьи" Эндрю Питоньяка,
- API LibreOffice,
- LibreOffice Basic,
- ну и конечно, английский язык Улыбка  Плачущий

Кажется, в задуманой связке MySQL (~20 таблиц) <-> Base -> Calc|Writer
последние два звена (реализация интерфейса) придется заменить чем то другим. Что бы попроще, чтоб в свободное от работы время.
Вот только чем? Qt c++, Python, HTML ... Сейчас всё работает на Excel+Word в (полу)ручном режиме, а хочется...


* модель.png (227.49 Кб, 998x1401 - просмотрено 33 раз.)
* model.txt (22.75 Кб - загружено 9 раз.)
« Последнее редактирование: 24 Май 2017, 06:35 от niko-rybalko » Записан
economist
Форумчанин
***
Offline Offline

Сообщений: 965


« Ответ #4: 24 Май 2017, 09:38 »

В OpenOffice Calc от Инфры в 2008-м входила функция SQL.REQUEST, правда она у меня всё равно не работала, и было это очень давно. Даже следов не найти (нашел другую, см. в конце, не проверял). Но функция от rami - работает у меня с SQLite и PostgreSQL. Пробуйте, получится 100%.

Что касается Writer - он в связке незаменим, если:
- нужно красивые тексты договоров, счетов, компредложений итп
- нельзя травмировать нежные души секретарш и шефов.
Просто посмотрите как отвратительно выглядят договоры из 1С, Галактики, всяких разных CRM - и вы сразу поймете, насколько прекрасен Writer со стилями, умеющий переносить по слогам содержимое полей баз данных.

Заменять Writer программой Word я не соглашусь даже с доплатой мне стоимости ПО (+10 тыс. руб.), поскольку вижу БДСМ-мучения закоснелых пользователей Word со стилями в нём, и откровенно ржу над ними.

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

Впрочем, Calc можно заменить Excel-ем :-)

Найденная функция, не проверял (она предлагается как функция массива).  
Код:
' A basic macro for use in Calc for performing arbitrary SQL queries as
' spreadsheet functions.
'
' Usage
' - You must register database connection in Base or through Tools->Options.
' - Add this macro to Tools->Macros->Organize Macros->OpenOffice.org Basic
' - In a Calc cell, type something =OOOCalcSQL("select foo from bar", "mydsn")
' - Press CTRL+SHIFT+ENTER to make the function an array (to get all the data)
'
' Reference:
' http://codesnippets.services.openoffice.org/Database/Database.QueryDatabase.snip


function OOOCalcSQL(sSQLCommand, sDSN)
   ' Ref: http://dba.openoffice.org/howto/IgnoreDriverPrivileges.html
   Dim aContext as Object
    aContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )

    If ( Not aContext.hasByName( sDSN ) ) Then
        OOOCalcSQL = "There is no data source named " + sDSN + "!"
        Exit Function
    Endif

    ' Create a row-set to query the database
    RowSet = createUnoService("com.sun.star.sdb.RowSet")
    RowSet.DataSourceName = sDSN
    RowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
    RowSet.Command = sSQLCommand
' the following two statements may be removed under certain situations
    RowSet.User = "root"
    RowSet.Password = "secret"
    On Local Error Goto SQLError
    RowSet.execute()
    On Local Error Goto 0
 
    nColumns = RowSet.GetColumns().GetCount()
    nRows = RowSet.RowCount()  ' I don't think this is right - нужно еще перед RowSet.Count сделать RowSet.Last
    Dim result(1 to nRows, 1 to nColumns)
  
    nRow = 1
    while RowSet.next()
       if (nRow > nRows) then ' a hack because RowCount() is too low
          ReDim Preserve Result(1 to nRow, 1 to nColumns)
       endif
       for n = 1 to nColumns
      
         ' Ref: http://api.openoffice.org/docs/DevelopersGuide/Database/DataTypesGetXXX.png
          Select Case RowSet.GetMetaData().GetColumnType(n)
         Case com.sun.star.sdbc.DataType.TINYINT
            Result(nRow, n) = RowSet.GetByte(n)
         Case com.sun.star.sdbc.DataType.SMALLINT              
            Result(nRow, n) = RowSet.GetShort(n)
         Case com.sun.star.sdbc.DataType.INTEGER        
            Result(nRow, n) = RowSet.GetInt(n)
         Case com.sun.star.sdbc.DataType.BIGINT
            Result(nRow, n) = RowSet.GetLong(n)
         Case com.sun.star.sdbc.DataType.REAL  
            Result(nRow, n) = RowSet.GetFloat(n)                  
         Case com.sun.star.sdbc.DataType.FLOAT            
         Case com.sun.star.sdbc.DataType.DOUBLE        
            Result(nRow, n) = RowSet.GetDouble(n)
         Case com.sun.star.sdbc.DataType.BIT      
            Result(nRow, n) = RowSet.GetBoolean(n)
         Case com.sun.star.sdbc.DataType.DECIMAL
            Result(nRow, n) = Val(RowSet.GetString(n))
'         Case com.sun.star.sdbc.DataType.SQLNULL
'            Result(nRow, n) = "NULL"        
            Case Else
               Result(nRow, n) = RowSet.GetString(n)
          End Select
      next n
      nRow = nRow + 1
    wend

'   RowSet.Dispose()
  
    OOOCalcSQL=Result()
  
SQLError:
   If Err <> 0 Then
'      MsgBox("Exception executing SQL")
       OOOCalcSQL=Error(Err)
   Endif
End function
« Последнее редактирование: 24 Май 2017, 09:46 от economist » Записан

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

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