Embedded macro in SQL Query?

a2c39a · Posted: Mon May 07, 2007 2:25 pm Post subject:

Hi Drew,

I've got most of my java6 install problems resolved now (with help from the ubuntu forum).
So I'm ready and waiting for the next installment of building my java function library for use with hsql database queries.

Many thanks for your help, John.

a2c39a · Posted: Sat May 19, 2007 12:29 pm Post subject:

Hi Drew (& any readers of this thread),

Many thanks for your help. I've got my java functions built, placed in a jar and working in OOo base SQL, much easier than I expected. For anyone else trying to do this just follow Mr Flanagan's excellent examples, pointed out by Drew and linked in his posts above. At first I did not realise the source code was available as well as the jar but it is there (in some cases it's even in the jar as well as the class files!). The OOo basic/VBA macros I was using converted easily to java.
My main stumbling block was the alias command. Took me all week to get it to work! Trouble was I did not understand what it meant.
In Drews example above:-
CREATE ALIAS calcBMIimperial FOR "flanagan.math.Fmath.calcBMIimperial"
I could see that calcBMIimperial was the function and that Fmath was the class but I did not know where the flanagan.math came from.
I now know it was the file path (directories) to the Fmath.class file when Mr Flanagan put it in a jar (flanagan/math/Fmath.class).
So, from the directory above the flanagan directory, he would have issued the command:- jar cvf flanagan.jar flanagan
Where cvf tells the jar program to create a new jar file with the following name; flanagan.jar is the filename to use; flanagan is the directory to start from and add it and all it's sub directories and their contents to the archive.
I know this seems easy and obvious to anyone who knows but when you don't know...................

So all this leaves is parameterising the query!?
Like Drews example, I can call my function from SQL with field names in double quotes: distSphericMi("Locator1", "Locator2")
I can also call it with one fixed text literal (single quotes) and one field name:- distSphericMi('IO20ME', "Locator")
However I cannot get it to work with a parameter:- distSphericMi(:Local_Locator, "Locator2") gives a Null value error and no parameter prompt.
Setting one column in design view to :Local_Locator with an alias of Locator and:- distSphericMi("Locator", "Locator2") gives a prompt for Local_Locator but when entered the function produces an unknown column name error.
NB. If I remove the function and just run the query with the parameter then it produces an extra column called Locator and with the value I entered at the parameter prompt in every record, just as I wanted.
Is there any way i can get it to pass this to my function just as though it was a real database field value?

Many thanks & best wishes, John.

DrewJensen · Posted: Sat May 19, 2007 12:52 pm Post subject:

I haven't finished reading the post you just made, just the first line, but I didn't really help you..I kind of abandoned you...sorry.

One thing always seems to lead to another with me anymore..

Did you know that the new NetBeans IDE has a plugin for OOo that allows you to easily create new components, extensions and applications to work with OOo in java...well, they say it does..and that is where I got off to on this...

Of course I couldn;t use the stable NetBeans IDE...no, I had to go and grab the 6.0 version and that lead to the fact that I can build my own JDK from source...LOL..and that has lead to a real learning experience...

I will be back to this thread - really I will - and hopefully show how to not only make those UDF's but also how to use the NetBeans IDE to let you step through the code live while OOo runs...they swear it works..and I think I am getting close.

Sorry again.

EDIT -------------

Did just read the whole post...hmmm, don;t really have a clue - but as life has it - I am in the middle of a conversation with the lead developer of Base ( as in, in the other window ) and talking about issues with parameters to queries and the new report designer...

EDIT 2 ------------------

Then again, there might be a work around...be back later ( as in the next 24 hours ) with what I can come up with...
I don't think you are going to get this to work.
_________________
Blog - http://baseanswers.spaces.live.com/

a2c39a · Posted: Sat May 19, 2007 1:34 pm Post subject:

Hi Drew,

Yes you did help, you helped a lot. All I needed was examples and Mr Flanagans were just right. It was just that I didn't realise the source was there at first. Once I did realise, I was away.

Re the parameterised query, any workaround will do. (I miss the things I used to do with access: action queries, manipulating the database directly from VBA without using SQL and things like that).

Best wishes, John.

DrewJensen · Posted: Sat May 19, 2007 1:38 pm Post subject:

Well, as for manipulating the database with VBA, pretty much if you could do it with VBA you can do it with StarBasic.

If you have a specific example post it up, really.

This way the MSA-FAQ page on the wiki can be updated with the example from VBA to StarBasic.
_________________
Blog - http://baseanswers.spaces.live.com/

a2c39a · Posted: Sat May 19, 2007 1:50 pm Post subject:

OK.
I'll have to dig out an old HDD and hook it up via USB to remind me of how to do it!!
As I remember there were two ways (one used something.somethingelse the other used >>) to address fields in a record, one was the way in the access VBA reference manual the other way was the true Visual Basic method but both worked under VBA.
The only thing I can remember off hand is the command to move from the current record to the next record:- record.moveNext

Will be back later (as in during the next 7 days) with more.

Best wishes, John.

DrewJensen · Posted: Sat May 19, 2007 2:06 pm Post subject:

For me...I am off, with my pot of "Lost in Idaho" chille to a chille taste off at the local VVA...Uh Rah...

later
_________________
Blog - http://baseanswers.spaces.live.com/

a2c39a · Posted: Sun May 20, 2007 8:41 pm Post subject:

Hi Drew,

OK! Here is the VBA example (edited to shorten it and remove some clutter). I expect this will be an Access only method of manipulating a database but I can hope!

DrewJensen · Posted: Sun May 20, 2007 9:27 pm Post subject:

LOL...I was just thinking about you..

I tried to get my workaround idea to work...instead ended up opening a new issue for the QiQ feature.

If you want to see what I was thinking the issue is #77635 in the OOo issue tracker.

For now - it is 1:20 AM here..and I think sleep is in order. I'll take a long look at that vba code tomorrow. First glance at it...I don;t see anything that can't be done in StarBasic.
_________________
Blog - http://baseanswers.spaces.live.com/

a2c39a · Posted: Sun May 20, 2007 9:36 pm Post subject:

HI Drew,

Sounds good to me. Do you mean using an HSQL database?
Good night Drew. I've just had breakfast and will soon leave for work.

Best wishes, John.

zmija2a · Newbie Joined: 04 Nov 2008 Posts: 1

Hi

I'm doing everything as in the post DrewJensen.
1. Downloading flanagan.jar
2.

sara_criss · Guest

thanks for providing all this lovely information

AlfSma · Newbie Joined: 18 Mar 2009 Posts: 1

ditto

SQL Status: S1000
Error code: -33

Access is denied: flanagan.math.Fmath.clacBMImetric in statement [SELECT "Name", "Height", "Weight", CalcBMImetric( "Height", "Weight" ) FROM "Clients" AS "Clients"]

Windows Vista - admin user
OpenOffice.org 3.0.1
OOO300m15 (Build:9379)
Sad

Villeroy · Posted: Wed Mar 18, 2009 12:34 pm Post subject:

They removed the option to extend HSQL in version 2.3 or 2.4.
Now you have to wrap your Java in OOo extensions which also restrict the scope of the contained functions to certain databases.
I've never seen such an extension in the wild and I feel extremely reluctant to write such a wrapper for the functions I got already working. I consider this feature as non-existent (and Base as a dead horse).
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org