[Solved] Remote Database as Source for PivotTables

Creating a macro - Writing a Script - Using the API

[Solved] Remote Database as Source for PivotTables

Postby blacksheriff » Wed Jan 09, 2019 3:07 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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]
blacksheriff
 
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

Re: Remote Database as Source for PivotTables

Postby Villeroy » Wed Jan 09, 2019 8:34 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remote Database as Source for PivotTables

Postby blacksheriff » Thu Jan 10, 2019 9:04 am

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   Expand viewCollapse view
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]
blacksheriff
 
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

Re: Remote Database as Source for PivotTables

Postby hubert lambert » Thu Jan 10, 2019 11:01 am

Hi,

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

Code: Select all   Expand viewCollapse view
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 9 times
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
hubert lambert
 
Posts: 107
Joined: Mon Jun 13, 2016 10:50 am

Re: Remote Database as Source for PivotTables

Postby Villeroy » Thu Jan 10, 2019 1:29 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remote Database as Source for PivotTables

Postby blacksheriff » Thu Jan 10, 2019 3:46 pm

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

Postby blacksheriff » Thu Jan 10, 2019 3:53 pm

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

Postby blacksheriff » Fri Jan 11, 2019 9:53 am

hubert lambert wrote:Hi,

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

Code: Select all   Expand viewCollapse view
   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   Expand viewCollapse view

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]
blacksheriff
 
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

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

Postby hubert lambert » Fri Jan 11, 2019 4:20 pm

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   Expand viewCollapse view
url = uno.systemPathToFileUrl("/path/to/the/dbfile.odb"
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
hubert lambert
 
Posts: 107
Joined: Mon Jun 13, 2016 10:50 am

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

Postby Villeroy » Fri Jan 11, 2019 7:28 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby blacksheriff » Mon Jan 14, 2019 7:21 am

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   Expand viewCollapse view
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]

Postby blacksheriff » Mon Jan 14, 2019 7:35 am

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]
blacksheriff
 
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm

Re: [Solved] Remote Database as Source for PivotTables

Postby Villeroy » Mon Jan 14, 2019 5:33 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Remote Database as Source for PivotTables

Postby blacksheriff » Tue Jan 15, 2019 8:33 am

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]
blacksheriff
 
Posts: 8
Joined: Wed Jan 09, 2019 2:52 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests