db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tiago Espinha" <ti...@espinhas.net>
Subject Re: Stored Procedures (what is it really used for?)
Date Fri, 04 Apr 2008 06:57:21 GMT
Well, to understand the relevance and the need for procedures you pretty
much have to backtrace it to the why we encapsulate code in methods in the
first place. For instance, in procedural programming, say in C, you could
pretty much just write a big bunch of code in the main() function, but you
don't, or you shouldn't. We don't because it's messy and because it's very
hard to read.

This is pretty much the same, if in Java you can, say, add a customer to the
database by invoking a remote procedure that takes you one line of code,
then that proves as an advantage over having like 10 lines of code required
to do the same task, should you not have it in a procedure. In the end it
may also decrease network overhead if that's of any concern to you, as if
there's the need to work with big blocks of data, these won't be getting
bounced between the client and the DB server, everything will happen
server-side.

On the other hand there's the issue of server-load. And perhaps that's the
reason why procedures aren't a panacea for all your problems. Since the
procedure will be running server-side, you are basically shifting some of
the CPU load that would have belonged to the client otherwise. Now, as long
as there's just one client, this isn't relevant, but if you have 10000
clients hammering the server by invoking a wide-range of cumbersome
procedures, then you might have a problem.

Hope this helped,
Tiago

On Fri, Apr 4, 2008 at 7:31 AM, rico16135 <rico_16135@hotmail.com> wrote:

>
> So from a tutorial, I successfully created a stored procedure, stored it,
> and
> made code to use it.  I think.
> If I run my code, it seems to work fine, regardless of whether or not I
> have
> entered that stored procedure into the database.
>
> Is my code suppose to work regardless?  Is storing a procedure just meant
> to
> make my code, which repeats quite often, more efficient?  Or are stored
> procedures created for different reasons? Are they there to let me run my
> java code from an sql query?  I'm at my wits end trying to understand the
> significance of a stored procedure.  I've posted my code, if it helps.
>
>
>  public static void callSortTwo(Connection conn, CallList cl) {
>        try {
>            Timestamp ts = new Timestamp(System.currentTimeMillis() );
>            System.out.println("Executed at: " + ts);
>            String insert_query = "INSERT INTO SWEATY_BALLSAK.CALLS (" +
>                    "beat," +
>                    "call_descr," +
>                    "priority," +
>                    "date," +
>                    "time," +
>                    "call_num," +
>                    "location," +
>                    "status," +
>                    "entry_id) " +
>                    "VALUES(?,?,?,?,?,?,?,?,?)";
>
>            PreparedStatement pstmt = conn.prepareStatement(insert_query);
>
>            for (int counter = 0; counter < cl.getSize(); counter++) {
>                String current_event[] = cl.getElementArray(counter);
>
>                pstmt.setString(1,current_event[0]);
>                pstmt.setString(2,current_event[1]);
>                pstmt.setString(3,current_event[2]);
>                pstmt.setString(4,current_event[3]);
>                pstmt.setString(5,current_event[4]);
>                pstmt.setString(6,current_event[5]);
>                pstmt.setString(7,current_event[6]);
>                pstmt.setString(8,current_event[7]);
>                pstmt.setTimestamp(9, ts);
>
>                pstmt.executeUpdate();
>                }
>        }
>
>        catch(SQLException sqle_callSort) {
>            System.out.println("SQL Exception" + sqle_callSort.getMessage()
> );
>        }
> --
> View this message in context:
> http://www.nabble.com/Stored-Procedures-%28what-is-it-really-used-for-%29-tp16485873p16485873.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>

Mime
View raw message