This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

Bug 144503 - Add support for Sybase raiserror
Summary: Add support for Sybase raiserror
Status: RESOLVED DUPLICATE of bug 127597
Alias: None
Product: db
Classification: Unclassified
Component: Show Data (show other bugs)
Version: 6.x
Hardware: All All
: P3 blocker (vote)
Assignee: _ ahimanikya
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-08-20 01:06 UTC by David Vancouvering
Modified: 2008-08-20 17:10 UTC (History)
1 user (show)

See Also:
Issue Type: ENHANCEMENT
Exception Reporter:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description David Vancouvering 2008-08-20 01:06:06 UTC
Sybase stored procedures have a concept of "raiserror" which is a SQLWarning that is just a print statement. 

Attached is Lance's code to support this.  Some day...

Here is the print and the raiseerror samples i wrote in 1996

hth

-lance

/**
* *
* PrintExample class demonstrates how to use the SQLWarning class
* to process the T-SQL Print command<p>
*
* Note:  This example will also have a SQLWarning occur with a
* message and state of 010P4.  This occurs due to the fact that
* I executed a stored procedure and ignored the return status. This
* is normal behaviour when I execute a sproc via Statement.execute().<p>
*
* Due to permissions restricting a guest from creating procedures,
* we will not be creating a stored procedure, only executing it.
* The required stored procedures have been pre-loaded
* onto our demo server, and included in pubs2_sql.sql or pubs2_any.sql
* for you to be able to run them from your server.
* We have included comments on how you would actually execute the
* creation of a stored procedure and drop within the *CREATE PROCEDURE*
* comments throughout the sample.
*
* PrintExample may be invoked with the optional parameters:<br>
* -U username<br>
* -P password<br>
* -D debuglibraries<br>
* -S server<p>
*
*  @see Sample
*/

class PrintExample extends Sample
{

   PrintExample()
   {
       super();
   }

   public void sampleCode()
   {


       String procName = "sp_printExampleSample";

       /*   *CREATE PROCEDURE*
          String dropProc = "drop proc " + procName;
          String createProc =
              "create proc " + procName  +
              " as " +
              "print 'print statement 1'"  +
              "print 'print statement 2'"  +
              "\nselect au_id,au_fname, au_lname from pubs2..authors "  +
              "print 'print statement 3'"  +
              "\nselect title_id, type, price from pubs2..titles"  ;
       */


       try
       {


           // Demonstrate the use of the print SQLWarning
           execDDL("print 'hello world'");

           /* *CREATE PROCEDURE*
              //  Create the Proc
              execDDL(createProc);
           */

           Statement stmt = _con.createStatement();;
           output("Executing: " + procName + "\n");
           int rsnum = 0;                   // Number of Result Sets processed
           int rowsAffected = 0;
           boolean results = false;
           ResultSet rs = null;

           try
           {
               results = stmt.execute (procName);
               checkForWarning(stmt.getWarnings());
               stmt.clearWarnings();
           }
           catch(SQLException ex)
           {
               output("Exception on execute()\n");
               displaySQLEx( ex);

               // Because we received an SQLException, we need to poll to see
               // If there are more results to process.
               results = stmt.getMoreResults();
               checkForWarning(stmt.getWarnings());
               stmt.clearWarnings();
           }
           do
           {

               if(results)
               {
                   try
                   {
                       rs = stmt.getResultSet();
                       checkForWarning(stmt.getWarnings());
                       stmt.clearWarnings();
                   }
                   catch(SQLException ex)
                   {
                       output("Exception while invoking getResultSet\n");
                       displaySQLEx(ex);
                   }
                   output("\n\nDisplaying ResultSet: " + rsnum + "\n");
                   dispResultSet(rs);
                   rsnum++;

                   rs.close();
               }
               else
               {
                   rowsAffected = stmt.getUpdateCount();
                   if (rowsAffected >= 0)
                   output(rowsAffected + " rows Affected.\n");
               }
               results = stmt.getMoreResults();
               checkForWarning(stmt.getWarnings());
               stmt.clearWarnings();
           }
           while (results || rowsAffected != -1);

           stmt.close();

           /*  *CREATE PROCEDURE*
              //  drop the Proc
              execDDL(dropProc);
           */


       }
       catch (SQLException ex)
       {
           displaySQLEx(ex);
       }
   }
   /**
   * Overload checkForWarning
   * Checks for and displays warnings.  Returns true if a warning
   * existed
   * @param warn   SQLWarning object
   * @return       True if we displayed a warning
   */

   public boolean checkForWarning (SQLWarning warn) throws SQLException
   {
       boolean rc = false;

       // If a SQLWarning object was given, display the
       // warning messages.  Note that there could be
       // multiple warnings chained together

       if (warn != null)
       {
           rc = true;
           while (warn != null)
           {
               if(warn.getErrorCode() == 0 && warn.getSQLState() == null)
               {

                   // Have encountered a t-sql print command
                   output (warn.getMessage () + "\n");
               }
               else
               {
                   output("\n *** Warning ***\n");
                   output ("Error:   " + warn.getErrorCode () +"\n");
                   output ("Message:  " + warn.getMessage () + "\n");
                   output ("SQLState: " + warn.getSQLState () + "\n");
               }
               warn = warn.getNextWarning ();
           }
       }
       return rc;
   }

}






package sample2;
import java.io.*;
import java.sql.*;
import java.util.*;

/**
* Raiserror class demonstrates how to process raiserror messages
* that are raised as SQLExceptions<p>
*
* Due to permissions restricting a guest from creating procedures,
* we will not be creating a stored procedure, only executing it.
* The required stored procedures have been pre-loaded
* onto our demo server, and included in pubs2_sql.sql or pubs2_any.sql
* for you to be able to run them from your server.
* We have included comments on how you would actually execute the
* creation of a stored procedure and drop within the *CREATE PROCEDURE*
* comments throughout the sample.
*
* Raiserror may be invoked with the optional parameters:<br>
* -U username<br>
* -P password<br>
* -D debuglibraries<br>
* -S server<p>
*
*  @see Sample
*/
public class Raiserror extends Sample
{

   Raiserror()
   {
       super();
   }

   public void sampleCode()
   {


       String procName = "sp_raiserrorSample";
       /* *CREATE PROCEDURE*
          String dropProc = "drop proc " + procName;
          String createProc =
              "create proc " + procName  +
              " as " +
              "raiserror 24000 'I raised this error'"  +
              "raiserror 24001 'I raised this error'"  +
              "raiserror 25000 'I raised this error'"  +
              "\nselect au_id,au_fname, au_lname from authors "  +
              "raiserror 26000 'I raised this error'"  +
              "\nselect title_id, type, price from titles"  ;
       */

       try
       {
           /* *CREATE PROCEDURE*
              output("Going to execute the create proc\n");
              // Create the Proc
              execDDL(createProc);
           */

           output("Going to create stmt \n");
           Statement stmt = _con.createStatement();;
           output("Executing: " + procName + "\n");
           int rsnum = 0;                   // Number of Result Sets processed
           int rowsAffected = 0;
           boolean results = false;
           ResultSet rs = null;

           try
           {
               results = stmt.execute(procName);
           }
           catch(SQLException ex)
           {
               output("Exception on execute()\n");
               processSQLException( ex);

               // Because we received an SQLException, we need to poll to see
               // If there are more results to process.
               results = getMoreResults(stmt);
           }
           do
           {

               if(results)
               {
                   try
                   {
                       rs = stmt.getResultSet();
                   }
                   catch(SQLException ex)
                   {
                       output("Exception while invoking getResultSet\n");
                       processSQLException( ex);
                   }
                   output("\n\nDisplaying ResultSet: " + rsnum + "\n");
                   dispResultSet(rs);
                   rsnum++;

                   rs.close();
               }
               else
               {
                   rowsAffected = stmt.getUpdateCount();
                   if (rowsAffected >= 0)
                   output(rowsAffected + " rows Affected.\n");
               }
               //  Created a Raiserror.getMoreResults() method to handle Multiple
               //  raiserrors in a row.  Would be more elegant to extend
               //  the ResultSet class.
               results = getMoreResults(stmt);
           }
           while (results || rowsAffected != -1);

           stmt.close();


           /* *CREATE PROCEDURE*
              //  drop the Proc
              execDDL(dropProc);
           */
       }
       catch (SQLException ex)
       {
           processSQLException(ex);
       }
   }

   /**
    * Process a SQLException.
    * @param ex  SQLException object
    * @return    A boolean stating whether we are here due to Raiserror
    */
   public boolean processSQLException(SQLException ex)
   {
       // A SQLException was generated.  Catch it and
       // display the error information.  Note that there
       // could be multiple error objects chained
       // together

       boolean gotRaiserror = false;


       while (ex != null)
       {
           if(ex.getSQLState()== null && ex.getErrorCode() >= 17000)
           {
               // The SQLException is due to a raiserror command.

               output("***Raiserror encountered***\n");
               output ("Error:   " + ex.getErrorCode () + "\n");
               output ("Message:  " + ex.getMessage () + "\n");
               gotRaiserror = true;

           }
           else
           {
               output ("\n*** SQLException caught ***\n");
               output ("Error:   " + ex.getErrorCode ()+ "\n");
               output ("Message:  " + ex.getMessage () + "\n");
               output ("SQLState: " + ex.getSQLState () + "\n\n");
           }
           ex = ex.getNextException ();
       }

       return(gotRaiserror);

   }
   /**
    * Execute ResultSet.getMoreResults().  If an exception was raised due
    * to a raiserror command, call ResultSet.getMoreResults() a second time
    * @param stmt     Statement object to use
    * @return         boolean which indicates whether a raiserror command
    *                 was encountered
    */

   public boolean getMoreResults(Statement stmt)
   {
       boolean gotRaiserror = false;
       boolean results = false;

       try
       {
           results = stmt.getMoreResults();
       }
       catch(SQLException ex)
       {
           output("Exeception invoking getMoreResults\n");
           gotRaiserror= processSQLException( ex);
           if(gotRaiserror)
           {
               results = getMoreResults(stmt);
           }
       }
       return (results);
   }


}
Comment 1 Andrei Badea 2008-08-20 10:31:34 UTC
Looks like a duplicate of, or at least very similar to issue 127597.
Comment 2 David Vancouvering 2008-08-20 17:10:00 UTC
Yes, same issue.  

*** This issue has been marked as a duplicate of 127597 ***