| View previous topic :: View next topic |
| Author |
Message |
europe72
Blue Pill
Joined: Jan 13, 2009
Posts: 3
|
 Posted: Tue Jan 13, 2009 7:44 pm Post subject: Writing Macros + Database Connectivity |
|
Hello,
This is my first post on this forum...glad it is here.
I am in the process of spec'ing out a Macro (Rhino JS) that will run from within Neo's spreadsheet program. The goal of the macro would be as follows (assuming a spreadsheet file is already open):
1). Iterate over rows within the spreadsheet file, and for each row, use data from a certain cell and run a query against a Database using that data as a constraint. e.g. ... WHERE dbcolumn = myspreadsheetcolumn data etc.
2). Depending on the data received from the query write out a string to another cell.
Not much to it really but my main question is how to connect to the DB. I don't think that I can connect directly from within the Macro editor/end-user scripting environment, but I thought that perhaps I could setup the connection in the Neooffice database application, then script the macro to use that connection?
Thanks in advance for any direction.
PS Did a quick search on the forum for this or a similar topic, but didn't see much. With that said, if there is a similar post somewhere please direct me to it.
Last edited by europe72 on Tue Jan 13, 2009 7:53 pm; edited 1 time in total |
|
| Back to top |
|
 |
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Tue Jan 13, 2009 7:53 pm Post subject: Re: Writing Macros + Database Connectivity |
|
| europe72 wrote: | | Not much to it really but my main question is how to connect to the DB. I don't think that I can connect directly from within the Macro editor/end-user scripting environment, but I thought that perhaps I could setup the connection in the Neooffice database application, then script the macro to use that connection? |
So one question: do you already have a data source for your database connection setup? In other words, is the missing piece that you need to know how to embed database queries within a Calc spreadsheet?
Patrick |
|
| Back to top |
|
|
europe72
Blue Pill
Joined: Jan 13, 2009
Posts: 3
|
| Posted: Tue Jan 13, 2009 7:56 pm Post subject: |
|
Hi Patrick,
Thanks for the prompt reply. I do already have a working JDBC connection to the datasource. I guess the missing piece for me is what syntax do I use in the end-user scripting environment (Rhino / JS) to:
1). Get the existing connection as an OBJ
2). Fire off a select query againsts the DB in the connection
3). Get the result(s) obj
thx |
|
| Back to top |
|
|
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Tue Jan 13, 2009 8:33 pm Post subject: |
|
| europe72 wrote: | Thanks for the prompt reply. I do already have a working JDBC connection to the datasource. I guess the missing piece for me is what syntax do I use in the end-user scripting environment (Rhino / JS) to:
1). Get the existing connection as an OBJ
2). Fire off a select query againsts the DB in the connection
3). Get the result(s) obj |
Unfortunately, not many of the support volunteers here are macro writers and even after many years being a C/C++/Java programmer, I still find the macro syntax in NeoOffice's underlying OpenOffice.org StarBasic scripting language to be quite incomprehensible. Worse, the JavaScript and Python binding are mostly just the same StarBasic functions in JavaScript or Python form.
What I can do is recommend taking a look at the following NeoWiki article. This article gives a short overview on the "record macro" feature which may be useful for giving you some rough code. It also has some links to some of OpenOffice.org's StarBasic documentation so that you can start tweaking your recorded macros with specific macro functions:
http://neowiki.neooffice.org/index.php/Using_Macros
Hope that helps,
Patrick |
|
| Back to top |
|
|
europe72
Blue Pill
Joined: Jan 13, 2009
Posts: 3
|
| Posted: Tue Jan 13, 2009 8:47 pm Post subject: |
|
Thanks again for the info...
I will poke around and assuming I get something useful done, I will post the resulting macro code on the forum for others.
At the 10,000 ft level I think what needs to happen is I need to locate the package(s) [within com.sun.star...] that are required to get a connection obj and work with that obj. I have enough time invested in Rhino and Java so I should be able to get thru the Javadocs and script something useful.
What would really be helpful though is if anyone on this forum knew what com.sun.star packages I should import, and which classes from within those packages I should use to create a JS obj from an existing database connection.
Anyone?
 |
|
| Back to top |
|
|
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Tue Jan 13, 2009 9:10 pm Post subject: |
|
| europe72 wrote: | | What would really be helpful though is if anyone on this forum knew what com.sun.star packages I should import, and which classes from within those packages I should use to create a JS obj from an existing database connection. |
This sample Basic code from ooo-forums.org might help. Although their sample code adds a new data source, you should be able to use the same data source package name to get a list of and select an existing data source.
Patrick |
|
| Back to top |
|
|
Lorinda
Captain Mifune
Joined: Jun 20, 2006
Posts: 2051
Location: Midwest, USA
|
|
| Back to top |
|
|
|
