[Solved] Remote Database as Source for PivotTables

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
blacksheriff
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

[Solved] Remote Database as Source for PivotTables

Post by blacksheriff »

I honestly looked through many topics on the forum and read most of the documentation on Macros, but did not find the answer to the question of how to use an external database as a source for the pivot table without translation data to the sheet.
Maybe someone has already worked with this problem and can suggest a solution?
My Code on C#, but it doesn't matter:

Code: Select all

var ooType = Type.GetTypeFromProgID("com.sun.star.ServiceManager");
var ooApp = Activator.CreateInstance(ooType); 
var desktop = ooApp.Invoke("createInstance", BindingFlags.InvokeMethod, new Object[1] { "com.sun.star.frame.Desktop" });

var driverManager = ooApp.Invoke("createInstance", BindingFlags.InvokeMethod, new Object[1] { "com.sun.star.sdbc.DriverManager" });
var sURL = "sdbc:odbc:PostgreSQL35W";
var oCon = driverManager.Invoke("getConnection", BindingFlags.InvokeMethod, new Object[1] { sURL });
var oStatement = oCon.Invoke("CreateStatement", BindingFlags.InvokeMethod);

var sSQL = "SELECT * FROM \"myTable\"";
var oResult = oStatement.Invoke("executeQuery", BindingFlags.InvokeMethod, new Object[1] { sSQL });   
It work's fine. Than I Create PivotTable and Descriptor

Code: Select all

var pars = new Object[4] { "private:factory/scalc", "_blank", 0, new Object[] { hidden } };               
var workBook = desktop.Invoke("loadComponentFromUrl", BindingFlags.InvokeMethod, pars);
var sheets = workBook.Invoke("getSheets");                
var sheet = sheets.Invoke("getByIndex", par: new Object[] { 0 });
var pivotTables = sheet.Invoke("getDataPilotTables");
var pivotsDesc = pivotTables.Invoke("createDataPilotDescriptor");
But I don't know, how should I tell to descriptor or pivotTable use my query Result as data source for the Pivot ?
I find only Method pivotsDescriptor.SetRange("A1:A10') but can't find method SetQuery or SetQueryResult or smth like this?
Last edited by blacksheriff on Fri Jan 11, 2019 9:39 am, edited 2 times in total.
OpenOffice 4.1.6 on Microsoft Windows [10.0.14393]
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remote Database as Source for PivotTables

Post by Villeroy »

Have you ever tried the normal path from databases to pivot tables?
Why do you do this with macros?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
blacksheriff
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

Re: Remote Database as Source for PivotTables

Post by blacksheriff »

Villeroy wrote:Have you ever tried the normal path from databases to pivot tables?
Why do you do this with macros?
Of course I am. And it works.
I develope dynamic creation olap analysis forms, from my system to popular office packets.
In MS Excel and King office it works fine. I create connection and give sql text to DataPilotDescriptor, then define which fileds is row, which is column, which is aggregation data - and roc'n'roll! PivotTable is ready.
But for the ooCalc i can not find the solution! I can only download data from database table to sheet and only after that, i can create pivot table from range on sheet, but it works very slowly!
In Excel right way looks like:

Code: Select all

pivotCache.CommandType = XlCmdType.xlCmdSql;
pivotCache.CommandText = "SELECT * FROM MyTable";
pivotTables = sheet.PivotTables();
pivotTable = pivotTables.Add(pivotCache, excelApp.ActiveCell, "", null, null);
Does any pivotCache or smth like this in ooCalc? Or any other solution without copy data from DB to sheet?
OpenOffice 4.1.6 on Microsoft Windows [10.0.14393]
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Remote Database as Source for PivotTables

Post by hubert lambert »

Hi,

Here's an example in python, adapted from the official documentation here:

Code: Select all

from com.sun.star.beans import PropertyValue
from com.sun.star.sheet.DataImportMode import SQL
from com.sun.star.sheet.DataPilotFieldOrientation import ROW, DATA
from com.sun.star.sheet.GeneralFunction import COUNT
def createpivottablefromdb(event=None):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.Sheets.getByIndex(0)
    datapilottables = sheet.DataPilotTables
    pivotdescriptor = datapilottables.createDataPilotDescriptor()
    sql = sheet.getCellRangeByName('sql').String
    importdescriptor = (PropertyValue("DatabaseName", 0, "Bibliography", 0),
                        PropertyValue("SourceType", 0, SQL, 0),
                        PropertyValue("SourceObject", 0, sql, 0),
                        PropertyValue("IsNative", 0, False, 0))
    pivotdescriptor.ImportDescriptor = importdescriptor
    pivotfields = pivotdescriptor.DataPilotFields
    author = pivotfields.getByName("Author")
    author.Orientation = ROW
    title = pivotfields.getByName("Title")
    title.Orientation = DATA
    title.Function = COUNT
    pivotposition = sheet.getCellByPosition(0, 3).CellAddress
    pivotname = "pivot_biblio"
    if datapilottables.hasByName(pivotname):
        datapilottables.removeByName(pivotname)
    datapilottables.insertNewByName(pivotname, pivotposition, pivotdescriptor) 
Regards.
Attachments
blacksheriff.ods
(12.09 KiB) Downloaded 170 times
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remote Database as Source for PivotTables

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
blacksheriff
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

Re: Remote Database as Source for PivotTables

Post by blacksheriff »

hubert lambert wrote:Hi,

Here's an example in python, adapted from the official documentation here:

Regards.
Thanx a lot! It works!
OpenOffice 4.1.6 on Microsoft Windows [10.0.14393]
blacksheriff
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

Re: Remote Database as Source for PivotTables

Post by blacksheriff »

Villeroy wrote:You may be interested in https://extensions.libreoffice.org/exte ... -extension
Thank you for replay, but it's not exactly what i tried to find. I write integration from three-tier system with "Entities to sql constructor" and export reports to pivot tables in any office application that end-user has installed on their workstations.
OpenOffice 4.1.6 on Microsoft Windows [10.0.14393]
blacksheriff
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

Re: Remote Database as Source for PivotTables

Post by blacksheriff »

hubert lambert wrote:Hi,

Here's an example in python, adapted from the official documentation here:

Code: Select all

   pivotdescriptor = datapilottables.createDataPilotDescriptor()
    sql = sheet.getCellRangeByName('sql').String
    importdescriptor = (PropertyValue("DatabaseName", 0, "Bibliography", 0),
                        PropertyValue("SourceType", 0, SQL, 0),
                        PropertyValue("SourceObject", 0, sql, 0),
                        PropertyValue("IsNative", 0, False, 0))
    pivotdescriptor.ImportDescriptor = importdescriptor
    pivotfields = pivotdescriptor.DataPilotFields
Regards.
I've got a new problem. It perfectly works with registered database "Bibliography".
But i don't understand what should I write in "ConnectionResource" property of DatabaseImportDescriptor structure to access remote database with your macros example?
i try to write "sdbc:odbc:PostgreSQL35W" but it fires "error connect to dataSource sdbc:odbc:PostgreSQL35W", "com.sun.star.lang.WrappedTargetException sdbc:odbc:PostgreSQL35W"
It's very strange because when i try to connect with "getConnection" in code like this

Code: Select all

var desktop = ooApp.Invoke("createInstance", BindingFlags.InvokeMethod, new Object[1] { "com.sun.star.frame.Desktop" });
var driverManager = ooApp.Invoke("createInstance", BindingFlags.InvokeMethod, new Object[1] { "com.sun.star.sdbc.DriverManager" });
var sURL = "sdbc:odbc:PostgreSQL35W";                
var oCon = driverManager.Invoke("getConnection", BindingFlags.InvokeMethod, new Object[1] { sURL });
var oStatement = oCon.Invoke("CreateStatement", BindingFlags.InvokeMethod);
var sSQL = "select * from \"Company\"";
var oResult = oStatement.Invoke("executeQuery", BindingFlags.InvokeMethod, new Object[1] { sSQL });
 
no problem fires
OpenOffice 4.1.6 on Microsoft Windows [10.0.14393]
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Remote Database as Source for PivotTables [NEW problem]

Post by hubert lambert »

The easiest way I think:
- create somewhere an odb file connected to your database;
- use that odb url as value for the "DatabaseName" property, using the uno file protocol:

Code: Select all

url = uno.systemPathToFileUrl("/path/to/the/dbfile.odb") 
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remote Database as Source for PivotTables [NEW problem]

Post by Villeroy »

The URL of a working database connection is indicated in the status bar of the database window and in dialog menu:Edit>Database>Connection...

The URL of a database file on Windows is file:///C:/Path%20With%20Spaces/Name%20With%20Spaces.odb as shown in a browser with forward slashes and special characters encoded.
This translates to system notation "C:\Path With Spaces\Name With Spaces.odb"
StarBasic provides builtin functions systemPathToFileUrl and systemPathFromFileUrl to convert between the system notation and the system independent notation.
These functions are also implemented in the uno module of the Python bridge.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
blacksheriff
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

Re: Remote Database as Source for PivotTables [NEW problem]

Post by blacksheriff »

hubert lambert wrote:The easiest way I think:
- create somewhere an odb file connected to your database;
- use that odb url as value for the "DatabaseName" property, using the uno file protocol:

Code: Select all

url = uno.systemPathToFileUrl("/path/to/the/dbfile.odb")
Thanks for replay. It's the same as just register database. Idea was in dynamic connectiion to database via connection string like it works in other office packets.
OpenOffice 4.1.6 on Microsoft Windows [10.0.14393]
blacksheriff
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

Re: Remote Database as Source for PivotTables [NEW problem]

Post by blacksheriff »

Villeroy wrote:The URL of a working database connection is indicated in the status bar of the database window and in dialog menu:Edit>Database>Connection...

The URL of a database file on Windows is file:///C:/Path%20With%20Spaces/Name%20With%20Spaces.odb as shown in a browser with forward slashes and special characters encoded.
This translates to system notation "C:\Path With Spaces\Name With Spaces.odb"
StarBasic provides builtin functions systemPathToFileUrl and systemPathFromFileUrl to convert between the system notation and the system independent notation.
These functions are also implemented in the uno module of the Python bridge.
And? How it can help me?
I'm sorry, may be my English not good enough? Look.
I have remote database on the server in the net. Postgres, mssql, mysql it doesn't matter!
User working on his workstation on windows OS.
I have to install odbc driver.
I have to create some odb file
I have to use it to connect to database some interersting way for just get "select sql" result??????
I think it's to much for so simple task.
In any office packets i've ever seen before, i just give to the pivot cache - connection string. Smth like this:
"ODBC;DRIVER={PostgreSQL Unicode(X64)};DATABASE=MyBase;SERVER=192.168.234.118;PORT=5432;Uid=login;Pwd=mypassword"
And it's enough.
I just look for the simplest way to show sql result in pivot table from the database without copy data to the sheet, to prevent loss of time.
OpenOffice 4.1.6 on Microsoft Windows [10.0.14393]
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Remote Database as Source for PivotTables

Post by Villeroy »

blacksheriff wrote:In any office packets i've ever seen before,
You mean MS Excel?

If you want to write directly into the pivot cache, you should download the source code and get in contact with the core developers. This is a user forum with some expertise on macro programming and extension building.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
blacksheriff
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

Re: [Solved] Remote Database as Source for PivotTables

Post by blacksheriff »

Villeroy wrote:
blacksheriff wrote:In any office packets i've ever seen before,
You mean MS Excel?
If you want to write directly into the pivot cache, you should download the source code and get in contact with the core developers. This is a user forum with some expertise on macro programming and extension building.
KIng office for example...
I don't won't to write directly into the cache. I just want to give to Calc ConnectionString to any server, if i've installed any drivers to access data(oledb, odbc etc) and that's all
Well, i implement Hubert lumbert's suggestion to create *.odb file and it's work. Many thanx to him and to you. Without your help i'd never solved this problem. You spend your time explaining common truths to beginners in OpenOffice, like me.
I was just a little surprised at how much extra action I should take to simple access to data in Calc.
I install odbc driver, create DSN programmaticaly (Last time I heard about DSN in 90s previous century) !!!, because in odt file i can not write driverName directly, only via dsn, why? I don't know. Than programmaticaly create *.odt file, with link to dsn in it, than give it to pivotCahce. Fantastic!
I think it's to much to say to pivotCache "select Name, sum(Amount) from Sales group by name"...
Anyway question is close. Thank you for help...
OpenOffice 4.1.6 on Microsoft Windows [10.0.14393]
Post Reply