Как поместить результат запроса к Base в ячейку таблицы Calc?

[niko-rybalko]

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

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

Спасибо.

[rami]

Как это '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]

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

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

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

[niko-rybalko]

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

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

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

[economist]

В 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