Solved

Should a database function always return boolean?

Posted on 2003-11-11
21
244 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

809 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