InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples

CVS Repositories

Who We Are
News, Articles & Events
Getting Involved
Contact Us

Case Studies
On The Edge! -NEW-
Commercial Vendors

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

SV: InstantDB: Stored Procedures

I'm sorry if I offended someone with my posting but I see some very well
things for Stored Procedures to be implemented in a database (please note
english is not my native language):

* "Write-once run-anywhere" for remote accessing. That is, if I as a
supplied some new functionality inside the database within a package (class)
then all the remotely connected users could immediately start using it
through their
already connected JDBC sessions.

* Provides some very "nifty" (i believe that's the word) functionality for
3rd party tools. Like supplying packages ("classes") for in-db-encryption,
fulltext-altavista-like-searching among others... I have myself
written a numerous of packages in PL/SQL for the Oracle database.

* The InstandDB is a you say an "embeded database" but what is that from
it to be a general database which can reside on, lets say, a server and then
have numerous users connecting to it remotely fron their own applications?

* If the database internally was to support the execution of compiled
(classes) then those packages should supposedly (is that really a word?) run
much faster than writing compiled program units in the remotely calling
since the database-package is run "internally within the database" it could
use a jdbc-approach to the current db-connection which can be extremely
and just map straight into the alreay defined InstantDB database-API model
should be possible).

* One could write "webserver-mappers" that for instance would map
"http://myserver/mypackage/myfunction?myString=HelloWorld"; directly into the
InstantDB package/method which with connection-pooling techniques and the
fact that it runs
within the same context as the InstantDB database itself should execute
fairly quickly with
the least overhead possibly (instead of executing the class in the webserver
context and
then for each SQL-call generate remote JDBC-calls).

* When developing in groups we have found that working with "server side
stored procedures"
helps us tremendously because several developers can work simultenously with
compiling their
respective packages at the same time and also take direct usage of
previously compiles

* New InstantDB-features could be implemented as new Stored Procedures
packages which
could then easily be updated without re-installing or upgrading the database

* Most of all; you can place a new sticker on the box saying:
  "Now supports Java Stored Procedures!"
  And all features are cool I guess..

This is just some of the things I come think of which are cool with Stored
within a database. And since your database is written in Java it sound like
using the
Java-language itself for generating Stored Procedures is not a bad idea,
with the help if the Reflection-API the IntantDB can itself cache
information concerning
method-calls directly from the class-file, without the need for some
"descriptor file"
for InstantDB. That would be cool.

As I place myself and my company in the group that produces 3rd party
for databases the Stored Procedures as implemented in the Oracle database
works great
since I can wrote an PL/SQL package and distribute it to ANY database
knowing that it will
work. The Oracle handicap is their boring PL/SQL language though... True
Java-support would be nice!

Hope this helps you all see why I posted my first posting?

/Charlie Elgholm

-----Ursprungligt meddelande-----
Fran: Scott Plante []
Skickat: den 17 april 2001 21:12
Amne: Re: InstantDB: Stored Procedures

Stored procedures are of much less value in a database like InstantDB
than with ones like Oracle. One of the main values of a stored procedure
in Oracle is that you can make one call to the database, say over a
socket connection, and execute several SQL commands. So, you don't have
a lot of data going back and forth across a socket connection. But since
instantdb is an embedded database, everything happens in the same
process as the database anyway. You might just as well make two
independent calls as one to a "stored procedure." If you want to reuse
it, just encapsulate it in a class. Because the database is in-process,
your entire system is like one big collection of stored procedures. At
least, the tier that's accessing the database is.

What advantage do you see in stored procedures for InstantDB, besides
the fact that it's the way you did it in Oracle?


Charlie Elgholm wrote:

> Hi!
> Hos does InstantDB handle "Stored Procedures" in the database. Is there
> way to
> "compile" some kind of "package" with procedures/functions (methods) other
> than the global
> functions already implemented (i.e the "upper"-function). Does InstantDB
> perhaps support
> the option to compile Java-classes inline in the database that can then be
> called on
> with dot-notation like if I have:
> ----
> create or replace package/class myPackage
> {
> 	public String myFunction(String s){
> 		return s;
> 	}
> 	public String mySecondFunction(String s){
> 		return s;
> 	}
> }
> ---
> then I would be able to do
> ---
> select myPackage.myFunction(COLUMN_A) from TABLE_A
> ---
> The difference from your already implemented approach for the
> anonymous-functions (those not within
> a package/"class") is not that much. The only thing I see needed for this
> work is that the database
> can spawn some kind of compilation-process when issuing a "create or
> package/class" command, and
> this could perfectly be the standard SUN JDK compiler. And then there must
> be some kind of way to append
> the context in which the database is in at the moment of method-calling,
> with context I mean a JDBC-connection-type
> and perhaps other useful information about in what state the database is
> Why I'm bothering you is that I have worked a lot with the Oracle database
> over the years and with their
> PL/SQL Stored Procedures. And I really like it a lot and I was thinking of
> doing the same kind
> of implementations within the InstantDB database.
> How about it, is this already in process, or have a stumbled upon
> that I can help implement?
> Cheers!
> Charlie Elgholm
> --------------------------------------------------------------------------
> To unsubscribe from this mailing list, send email to
> with the text "unsubscribe instantdb" in the body of the email.
> If you have other questions regarding this mailing list, send email to
> the list admin at

To unsubscribe from this mailing list, send email to
with the text "unsubscribe instantdb" in the body of the email.
If you have other questions regarding this mailing list, send email to
the list admin at