Page 1 of 1
[Solved] Remote Database as Source for PivotTables
Posted: Wed Jan 09, 2019 3:07 pm
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?
Re: Remote Database as Source for PivotTables
Posted: Wed Jan 09, 2019 8:34 pm
by Villeroy
Have you ever tried the normal path from databases to pivot tables?
Why do you do this with macros?
Re: Remote Database as Source for PivotTables
Posted: Thu Jan 10, 2019 9:04 am
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?
Re: Remote Database as Source for PivotTables
Posted: Thu Jan 10, 2019 11:01 am
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.
Re: Remote Database as Source for PivotTables
Posted: Thu Jan 10, 2019 1:29 pm
by Villeroy
Re: Remote Database as Source for PivotTables
Posted: Thu Jan 10, 2019 3:46 pm
by blacksheriff
hubert lambert wrote:Hi,
Here's an example in python, adapted from the official documentation
here:
Regards.
Thanx a lot! It works!
Re: Remote Database as Source for PivotTables
Posted: Thu Jan 10, 2019 3:53 pm
by blacksheriff
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.
Re: Remote Database as Source for PivotTables
Posted: Fri Jan 11, 2019 9:53 am
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
Re: Remote Database as Source for PivotTables [NEW problem]
Posted: Fri Jan 11, 2019 4:20 pm
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")
Re: Remote Database as Source for PivotTables [NEW problem]
Posted: Fri Jan 11, 2019 7:28 pm
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.
Re: Remote Database as Source for PivotTables [NEW problem]
Posted: Mon Jan 14, 2019 7:21 am
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.
Re: Remote Database as Source for PivotTables [NEW problem]
Posted: Mon Jan 14, 2019 7:35 am
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.
Re: [Solved] Remote Database as Source for PivotTables
Posted: Mon Jan 14, 2019 5:33 pm
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.
Re: [Solved] Remote Database as Source for PivotTables
Posted: Tue Jan 15, 2019 8:33 am
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...