OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Embedded macro in SQL Query?
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
a2c39a
General User
General User


Joined: 06 Apr 2007
Posts: 20
Location: Norwich, Norfolk, UK

PostPosted: Mon Apr 23, 2007 9:00 pm    Post subject: Embedded macro in SQL Query? Reply with quote

Hi Folks,

Is it possible to use the result from running a user written macro in an OOo Base SQL Query column?
I have searched the forum but found nothing similar to this question.

I have a database that holds position locator values of many sites and a macro that calculates the distance between two locator values. I have used the macro to populate a Distance field by using one fixed locator value and the field value from the input text file, in my database load macro.

However I would like to be able to run a parameterised query that will display a field showing the distance between the locations in the database and another location entered at query run time?

The macro is written in OOo Basic and the Database is an embedded HSQL database.
I am using latest Linux version of OOo (2.2.0) and Ubuntu 7.04.

Many thanks for any help, John.
Back to top
View user's profile Send private message
Mark B
Super User
Super User


Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

PostPosted: Mon Apr 23, 2007 10:12 pm    Post subject: Reply with quote

Hi

You can't use a macro as part of a query - that is, of course, because the database use SQL not Basic. You can, however, use the results from the query in a macro - and do your processing that way.

Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Tue Apr 24, 2007 10:53 am    Post subject: Reply with quote

Hi,

Mark is correct that you can not use a Basic macro in a base query.

You might like to know however that you can add user defined functions to an embedded HSQL database, as long as those functions are written in java and conform to the HSQLdb requirements. These can be found, at the HSQLdb site, http://hsqldb.org

After you produce a jar file, you add an alias to the Base file by using the HSQLdb Add Alias command in the SQL window.

I have successfully added new functions to an embedded Base database using the add alias function. I have NOT tried using one of these as part of a parameterized query however, so can't say for sure if you will have problems with the query analyzer in Base of not.

Drew Jensen
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
a2c39a
General User
General User


Joined: 06 Apr 2007
Posts: 20
Location: Norwich, Norfolk, UK

PostPosted: Tue Apr 24, 2007 11:35 am    Post subject: Reply with quote

Hi Both,

Many thanks for the replies.
That has given me two different approaches to the problem.
I will give it some thought and let you know how I get on.

Best wishes, John.
Back to top
View user's profile Send private message
Mark B
Super User
Super User


Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

PostPosted: Tue Apr 24, 2007 12:57 pm    Post subject: Reply with quote

Drew's solution got me thinking - if you set up MySQL as the backend to Base then you can create your own functions and then use these in your queries. For example here's just a quick example:
Code:

CREATE FUNCTION testconcat( str CHAR(20) )
RETURNS CHAR(50)
DETERMINISTIC
BEGIN
  SET str = CONCAT( str, ' ', 'suffix' );
  RETURN str;
END;

Now in Base I can run a query:
Code:

select testconcat(user_name) from users;

and get the result:
admin suffix
chris suffix
jim suffix
max suffix
sally suffix
sarah suffix
will suffix

Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Tue Apr 24, 2007 10:43 pm    Post subject: Reply with quote

Howdy,

That is a great example of using a stored procedure written for MySQL.

I figured I aught to show how to add a procedure to HSQL. As it happens I want to calculate a Body Mass Index for a group of people. I would like to generate this on the fly, in other words as a calculated field in query. ( duh, that is this threads subject right )
Now I want to be lazy and not write my own java class. Instead I will use a time honored tradition of borrowing. After a quick google search I found this Public Domain library of java routines at http://www.ee.ucl.ac.uk/~mflanaga/java . ( Thank you Mr. Flanagan )

Checking the library documentation I find two functions that do the job:
calcBMImetric and calcBMIimperial, both take as parameters two doubles, height and weight.

Here are the steps to integrate into Base.

Download the java jar file, flanagan.jar per the instructions at the site.

Now add this jar file into OOo.
    Tools>Options, under OpenOffice.org select the java page.
    Click the Class Path button
    Click the Add Archive button
    Now browse to where flanagan.jar was downloaded to.
    Close the options dialog
    At this point you must restart OpenOffice for the change to take effect


Now I need to tell my Base file to use these new functions. Actually, since I live in USA I am only going to to use calcBMIimperial.

Open the Base file with the embedded HSQLdb database.
Open the SQL window. Tools>SQL
Enter the following command
Code:

CREATE ALIAS calcBMIimperial FOR "flanagan.math.Fmath.calcBMIimperial"


I was too quick with my typiong Sad in my last post when I erroneously said to use the ADD ALIAS command.

That is it. The java function is now available for use.

For example the query



Drew Jensen
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
a2c39a
General User
General User


Joined: 06 Apr 2007
Posts: 20
Location: Norwich, Norfolk, UK

PostPosted: Wed Apr 25, 2007 2:09 pm    Post subject: Reply with quote

Hi again Both,

Thanks for the extra information, very helpful.
I have not been able to find my calculation in a .jar so far. It looks like I'll have to learn enough java to write (convert) it myself.
The trouble is, most tutorials seem to assume that I want to learn the language from the bottom up!! OOOh nooo! All I want to do with any language is learn just a teeny weeny liddle bit so's I can solve the current problem! I may come back and learn more later. I may learn more on the way. Most of all I just want an example of how to code something similar and I'll copy the general gist of it for my purpose.
For building proper applications, classes, objects and all that hoo hah are probably very necessary (certainly for programmers to prove to non programmers that it's way too complicated for them to ever understand). For just some basic string manipulation and a bit of trigonometry it's way ott!
Once I've got through the clutter and can see the minimum that is required I'm sure it will all fall into place.

Thanks again folks, John.
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed Apr 25, 2007 3:04 pm    Post subject: Reply with quote

*chuckling*...there seems to be a lot of 'hoo ha' there alright.

Ok John,

Well, the only thing I am really left wondering about is this. You already have a macro that does what you need to do, right. So why worry about this parameterized query. But that is besides the point.

From the sound of it however, the easiest and fastest road would be to use that macro and look at how to build a UI for a Base application using it, that acts they way you want it to...it may be that you don't have to use a parameterized query to get what you want.

After that - Mark's suggestion of using MySQL 5 with a stored function is going to be the next fastest - I say this given that you apparently are comfortable with procedural style languages such as Base and SQL statements. Heck thinking about this more, it is probably the fastest and easiest of the three approaches.

But if you really want an embedded Database and you want the query then then extending HSQL with a java libary is it. A bit more work. But it is not black magic and no you don't have to learn it all to do...a teeeny bit for this...

If the later is you choice I'll see if I can't point in the right direction for some simple examples and enough of a tutorial to get you up and running.

EDIT - and if I can't make it clear enough for you, I can guarantee you there are plenty of people associated with the OOo project and on the forum here that are dyed in the wool java experts...I am sure they can be brought along for the fun
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
a2c39a
General User
General User


Joined: 06 Apr 2007
Posts: 20
Location: Norwich, Norfolk, UK

PostPosted: Wed Apr 25, 2007 8:48 pm    Post subject: Reply with quote

Hi Drew,

Yes, I like the idea of the function call from SQL. So I want to try coding it in java.
(I'm glad you saw the joke as I missed the "hi" (dit dit dit dit , dit dit) at the end of the bracketed section!)
I've been able to learn enough of various languages in the past (machine code, assembly language, basic, pascal, c+, fortran, algol) in order to achieve what I wanted to do at the time.
As you have realised I am happier with procedures and can fairly easily understand what is required to code them. It is just how I have to wrap the procedures in classes and objects that will give me problems.
If you could point me to somewhere that shows source code for something like your BMI example (in my case a function that takes a couple of arguments, calls a few other functions [some custom, some built in] and then returns a value) it would help a lot.

Thanks again, John.
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Fri Apr 27, 2007 7:57 am    Post subject: Reply with quote

OK, did not want you to think I forgot about this.

I did a quick search for some java tutorials...I see what you mean they all tend to get into application servers, or servlets or whatever else in about 2 pages.

For what you are looking to do keep this in mind:

You don't need to write a java program - only a library with a static procedure that takes a value as input and returns a single value as output.

You will need to create a class for this procedure to belong to.

You don't have to create a jar file - but It is probably worth the effort.

Now, I will be dead honest with you - Java is a language I have spent many years avoiding...for my own reasons...but as life and the fates often have it a number of things are converging to force my hand...LOL...after ~20 years SUN and an old mentor of mine will finally win. I should have known that when I started working with OOo.

To start perhaps we should both have the same tools.

If you don't have a Java development kit you will need one. I installed the JDK 1.6.0.1 files from the SUN site today, I figure we might as well use the latest stuff - right. If you don't mind can you go ahead and do the same.

Then we need a decent little piece of code we can use as our template and change it to do what is needed here. I spent an hour or so looking at three locations - the HSQLdb source code itself, the Report Wizard java source in the OOo distro ( It may be that I will be getting to know that piece of code very intimately here soon ) and the examples in the JDK distro. The report wizard code is way to closely coupled with the OOo api for what we need - the examples from SUN go from Hello World to Create a bank transaction in your application server in the blink of an eye it seems - the code for the library used by HSQLdb however looks like it may give us what we want.

OK - so I don't look like a complete fool doing this ( not that I haven't before...LOL ) I am going to create a very simple little library with one class and one method that will take as input an integer and give us back the string HEX representation of this value. I picked this because the Java runtime already has the functionality - I just need to put a wrapper around it for our use in a query. I'll do this over the next day or two...actually probably later this weekend. Then give you a simple blow by blow list of steps to do it yourself.

After that we can look at building the method you need for your application.

EDIT - unless of course some one else who is already a Java wiz kid wants to jump in here - you know who you are, and I know you are reading this forum...don't be shy...
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
a2c39a
General User
General User


Joined: 06 Apr 2007
Posts: 20
Location: Norwich, Norfolk, UK

PostPosted: Fri Apr 27, 2007 11:53 am    Post subject: Reply with quote

Hi Drew,

Many thanks for taking the time to help me.
As for a java wiz stepping in and taking over....he would probably confuse me totally by not taking things slowly enough!
First sun java. Last night I installed the sun java6 JDK & JRE & examples. Oh! Good. You are thinking. (Ubuntu gave me warnings when I chose to install them.)
Once installed I tried to play with the trivial "Hello World" and failed!!
Eventually I found that ubuntu was still using JRE 1.5 and this was the cause of my problem. A reboot may have solved the problem but due to some of the ubuntu system relying (apparently) on JRE 1.5 and the warnings during installation, I took the cowards way out and un-installed java6 and installed JDK 1.5.
Should I un-install all of 1.5 and have another try with 6?

With best wishes, John.
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Fri Apr 27, 2007 1:14 pm    Post subject: Reply with quote

Hi John,

hmmm...you may have just saved me a little time. One of the things on my 'Honey Do' list for this weekend is to upgrade my sweeties PC from Ubuntu 6.10 to Fiesty Fawn and then I was going to add it to my PC also. When I have the time I try to check what I do for the forum here, on Linus also - so putting the JDK on Ubuntu was on my mind...anyway I am wondering with this post already.

My understanding is that the 1.6.0.1 java compiler has a configuration switch that allows the compiled code to be Java 1.5 compatible...I'll try it and let you know
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
a2c39a
General User
General User


Joined: 06 Apr 2007
Posts: 20
Location: Norwich, Norfolk, UK

PostPosted: Fri Apr 27, 2007 9:30 pm    Post subject: Reply with quote

Hi Drew,

I decided (in my wisdom....or lack of it!) to un-install 1.5 and install 6, last night.
It may be me but all I did was use Synaptic package manager. Once 6 was installed none of the java commands were found at linux terminal prompt. Tried reboot, re-install etc. Eventually found that the symbolic links in /usr/bin still pointed to the 1.5 files (which no longer existed)! After more 'completely remove' and install of 6 there was no change to the symbolic links. Is there a problem with Ubuntu's installer for 6 or did I do something wrong?
I even 'unlinked' the /usr/bin/java symbolic link in case the existance of the 1.5 link was preventing the installer from adding the 6 link but no luck.
As there are quite a lot of links and I was not sure how many, I decided, once again, to put back 1.5.
Most things then worked again from their old links that were still present but java did not. The symbolic link I had deleted in /usr/bin was still missing and had not been reinserted by the 1.5 install!
This morning I have renewed that link manually and, I think, 1.5 is working OK again.
Should it be this difficult?
Why didn't the 6 install update/renew all the symbolic links?
What was the 'unverified package' or whatever, warning about?

With that we end up some way off topic!!

Best wishes, John.
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed May 02, 2007 7:42 am    Post subject: Reply with quote

Sorry, I got pulled in some other unexpected directions and never got to this..I am carving out a few hours this evening for just this...talk to you in the morning.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
a2c39a
General User
General User


Joined: 06 Apr 2007
Posts: 20
Location: Norwich, Norfolk, UK

PostPosted: Wed May 02, 2007 12:23 pm    Post subject: Reply with quote

OK, thanks Drew.

BTW, it's not Ubuntu 7.04 system that uses sun java 1.5 but some of my installed software.....I think!
I'm still not sure how to change to 6 but it's probably not necessary. 1.5 is surely OK for this purpose anyway?

Best wishes, John.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group