Solved

Should a database function always return boolean?

Posted on 2003-11-11
21
242 Views
Last Modified: 2010-03-31
Dear experts,

I just wonder if we should always define a function which accesses database return boolean? Therefore, the calling object can decide if the the database access is successful.

But, how do we pass the error to the calling function? By catch and try? What is the best "expert" way of doing this? Thanks.

0
Comment
Question by:cindyli
  • 7
  • 7
  • 2
  • +2
21 Comments
 
LVL 2

Expert Comment

by:Tols
ID: 9729047
maybe:

public boolean someMethod() throws SQLException
{
...
}

return value shows succes or failure, exception on error.
0
 
LVL 92

Accepted Solution

by:
objects earned 23 total points
ID: 9729056
I think you'd be better off throwing an exception if the db access fails, thats what they are for.
0
 

Author Comment

by:cindyli
ID: 9730762
thanks. SO, how do I display a user friendly error message on the User Interface if exception is caught?
0
 
LVL 2

Expert Comment

by:Tols
ID: 9730931

...
boolean rval = false;
try
{
   rval = someMethod();
}catch (SQLException e)
{
   //code for notification ex:
   String message = ex.getMessage();  //error message
   JOptionPane.showErrorMessage(..,message,..); //fill in parameters
}
0
 

Assisted Solution

by:dhaya
dhaya earned 22 total points
ID: 9731729
Yes, i think a boolean will always help you to debug back , sometimes it doesn't matter if your resultset always returns something

good example

   public ResultSet GetClaimLines(String active_clcl_id)
    {
        try
        {
             rsGetResults = (ResultSet)DBUtils.selectClaimLineDetails(dbconn, active_clcl_id );
        }
        catch(SQLException sqle)
        {
            Reporter.easyReport(ReportType.EXCEPTION,"SQLException has occured when calling GetClaimLines() method: "
                                                     + sqle.getMessage());
            Reporter.easyReport(ReportType.EXCEPTION,"SQL State: " + sqle.getSQLState());
            Reporter.easyReport(ReportType.EXCEPTION,"SQL Error Code: " + sqle.getErrorCode());
            JOptionPane.showMessageDialog(mainPanel,"SQLException has occured when calling GetClaimLines() method: "
                                                          + "\n " + sqle.getMessage()
                                                          + "\n SQL State: "+ sqle.getSQLState()
                                                          + "\n SQL Error Code: " + sqle.getErrorCode() );

        }
        return rsGetResults;
    }
------------------------
    /**
     * This procedure searches a set of fields based on a input clcl_id
     * @param conn
     * @param clcl_id
     * @return Resultset
     * @throws SQLException
     */
    public ResultSet selectByClaimID(Connection conn, String clcl_id) throws SQLException
    {
        CallableStatement sproc_stmt = conn.prepareCall("{call " + dbName + ".." + strClaimInfo1SP + " (?)}");
        sproc_stmt.setString(1,clcl_id);
        ResultSet sproc_result = DatabaseProxy.toResultSet(sproc_stmt);
        return sproc_result;
    }
------------------------------------------------------------------------------------------
use DUMMYDATABASE

go

if object_id('lkpenv01..LEP_PDD919_CLCL_S1') is not null
BEGIN
      drop procedure LEP_PDD919_CLCL_S1
END

go

create procedure LEP_PDD919_CLCL_S1

   @pCLCL_ID             char           (012) = NULL  


as

BEGIN


/**********************************************************************
**
**      Procedure Name  :       LEP_PDD919_CLCL_S1
**
**      Date Written    :       10/24/2003
**
**      Author          :       dhaya
**
**      System          :       DUMMY Application
**
**      Purpose         :       This stored procedure selects information from
**                              CLCL, SBSB, MEME for a given claim ID
**            
**      Input Parameters:       pCLCL_ID
**
**      Output Parameters:      Resultset
**
**      Called from     :       SAMPLEextDBUtils.class
**      
**      Procedures Called:      None
**
**      Errors Raised   :
**
**      Return Statuses :      
**
**      Source code file:       LEP_PDD919_CLCL_S1.sqp
**
**      Change History      :
**      Date         Name             Change
**      ----------   ------------     ----------------------------------
**      10/24/2003   dhaya        Original
**
**********************************************************************/      

declare @lnRetCd int

SELECT  @lnRetCd = 0

IF @lnRetCd = 0

   if object_id('#TEMP_LEP_PDD919_CLCL_S1') is not null
   begin
      drop table #TEMP_LEP_PDD919_CLCL_S1
   end

/***create the temp table containing all the outputs fields required****/
 create table #TEMP_LEP_PDD919_CLCL_S1
      (
            CLCL_ID  char(12),
            MEME_CK int,
            SBSB_CK int,
            PRPR_ID char(12),
            CLCL_CUR_STS char(2),
            CLCL_TOT_CHG money,
            CLCL_LOW_SVC_DT datetime,
            SBSB_ID char(9),
            MEME_SFX smallint
      )

/***select from CLCL into the temp table****/

   IF object_id('#TEMP_LEP_PDD919_CLCL_S1') is not null
   BEGIN
      INSERT INTO #TEMP_LEP_PDD919_CLCL_S1
      SELECT
            CLCL.CLCL_ID ,
            CLCL.MEME_CK ,
            CLCL.SBSB_CK ,
            CLCL.PRPR_ID ,
            CLCL.CLCL_CUR_STS ,
            CLCL.CLCL_TOT_CHG ,
            CLCL.CLCL_LOW_SVC_DT,
            ''  AS SBSB_ID,
            0 AS MEME_SFX

         FROM
             fctenv01ar..CMC_CLCL_CLAIM   CLCL

         WHERE
             CLCL.CLCL_ID = @pCLCL_ID
   END

   IF @@error <> 0 or @@rowcount = 0
   BEGIN
      SELECT  @lnRetCd = 1
      RETURN @lnRetCd
   END

/***create a clustered index on contrive keys****/
   IF object_id('#TEMP_LEP_PDD919_CLCL_S1') is not null
   BEGIN
      create nonclustered index IDX_PDD919_SBSB on #TEMP_LEP_PDD919_CLCL_S1(SBSB_CK)
      create nonclustered index IDX_PDD919_MEME on #TEMP_LEP_PDD919_CLCL_S1 (MEME_CK)
   END

   IF @@error <> 0
   BEGIN
      SELECT  @lnRetCd = 1
      RETURN @lnRetCd
   END

/***join for SBSB details ****/
   
   if object_id('#TEMP_LEP_PDD919_CLCL_S1') is not null
   BEGIN
      UPDATE  
            #TEMP_LEP_PDD919_CLCL_S1
      SET
            CLCL.SBSB_ID = SBSB.SBSB_ID
      FROM
            #TEMP_LEP_PDD919_CLCL_S1 CLCL
            INNER JOIN fctenv01..CMC_SBSB_SUBSC SBSB
            ON CLCL.SBSB_CK = SBSB.SBSB_CK
   END

   IF @@error <> 0
   BEGIN
      SELECT  @lnRetCd = 1
      RETURN @lnRetCd
   END

/***join for MEME details ****/
   if object_id('#TEMP_LEP_PDD919_CLCL_S1') is not null
   BEGIN
      UPDATE  
            #TEMP_LEP_PDD919_CLCL_S1
      SET
            CLCL.MEME_SFX = MEME.MEME_SFX
      FROM
            #TEMP_LEP_PDD919_CLCL_S1 CLCL
            INNER JOIN fctenv01..CMC_MEME_MEMBER MEME
            ON CLCL.MEME_CK = MEME.MEME_CK
   END

   IF @@error <> 0
   BEGIN
      SELECT  @lnRetCd = 1
      RETURN @lnRetCd
   END

/***select from the temp table***/
   if object_id('#TEMP_LEP_PDD919_CLCL_S1') is not null
   BEGIN
      SELECT
            CLCL_ID ,
            SBSB_ID,
            MEME_SFX,
            PRPR_ID,
            CLCL_LOW_SVC_DT,            
            CLCL_CUR_STS,
            CLCL_TOT_CHG
      FROM
            #TEMP_LEP_PDD919_CLCL_S1
   END

   IF @@error <> 0
   BEGIN
      SELECT  @lnRetCd = 1
      RETURN @lnRetCd
   END

RETURN @lnRetCd

END

go

IF object_id('lkpenv01..LEP_PDD919_CLCL_S1') is not null
      BEGIN
      PRINT 'CREATED PROC LEP_PDD919_CLCL_S1'
      END
ELSE
      BEGIN
      PRINT 'FAILED CREATING PROC LEP_PDD919_CLCL_S1'
      END




0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 10049913
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:


[points to Tols]


Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
sudhakar_koundinya
EE Cleanup Volunteer
---------------------
If you feel that your question was not properly addressed, or that none of the comments received were appropriate answers, please post your concern in THIS thread.
0
 
LVL 92

Expert Comment

by:objects
ID: 10049978
How would db access fail without an exception being thrown?
0
 

Expert Comment

by:dhaya
ID: 10052363
sudhakar,

   i share the same view as objects, tols answer is not correct here.

0
 
LVL 2

Expert Comment

by:Tols
ID: 10052743
>>return value shows succes or failure, exception on error.

This is only a model.
is 'failure' equals 'error' ?

ERRORS - It could be any error (is there only db access errors possible?) Suppose we expects some data, but there isn't any data - that could be treated like an error. Am I right? It all depends on body of method.

:-)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 92

Expert Comment

by:objects
ID: 10054957
Whatever the error an exception would be far more useful as it would allow the details of the error to the caller, instead of just a flag saying 'something' went wrong.
Plus what if your method needed to return the results of the database call, this would not be possible if the return status was used for a success flag.
0
 
LVL 2

Expert Comment

by:Tols
ID: 10059648
If we only want to get some data from database - You are absolutely right.
I thought that JAVA could be used not only for executing SQL queries but for more complicated operations. ;-)

Those method is generally a model of methods performing some operations (not getter or setter methods).
During those operations (ex. authorization procedures, managing data etc.) may occur such situations that are not an error (ex. authorization failure, not enough data processed etc.).

Methods returning some data are usually submethods of above mehod model. If we want to post here all possible method definitions ...

But generally if we concern only on errors than .. yes, exception is the best way to notify the caller about it.

PS: Didn't You wonder why most of JDBC api methods (that are not for getting something) return boolean values?
0
 
LVL 92

Expert Comment

by:objects
ID: 10059672
And one of the advantages of having exceptions is so methods don't need to return error flags/codes.

> PS: Didn't You wonder why most of JDBC api methods (that are not for getting something) return boolean values?

not for indicating errors they dont :)
0
 
LVL 2

Expert Comment

by:Tols
ID: 10059746
>not for indicating errors they dont :)

But what for?
0
 
LVL 92

Expert Comment

by:objects
ID: 10059775
depends on the method but certainly not errors which is what is being disussed here.
0
 
LVL 2

Expert Comment

by:Tols
ID: 10059896


Maybe for indicating success or failure of operation (failure of operation is somekind of error, isn't it?)

PS. Methods are not only for indicating errors
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 10060263
My thoughts on Tols' answer is like this


>>public boolean someMethod() throws SQLException

He gave the answer that returns boolean value depends on the situation and at the same time he is throwing exception also which i think is most relevant for handling database functions

returning boolean and throwing execption depends on situation is acceptable . Isn't it?
0
 
LVL 2

Expert Comment

by:Tols
ID: 10060425
That's the point!
:-)
0
 
LVL 92

Expert Comment

by:objects
ID: 10065155
That approach breaks down as soon as the method actually needs to return a value.
Guess it comes down to the difference between failure and exception.
Thanks for the chat :)

0
 
LVL 92

Expert Comment

by:objects
ID: 10065168
Sorry one last point, the q asks if it should *always* return a boolean, my answer: no :)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now