I want T-SQL to Throw exception

[Former Member]

Hi! We have hundreds of procedures written in T-SQL. If we in a proceures for ex violates a primary key I want an exception thrown to our java code, we use "stmt.executeQuery(sqlStr)" to call the procedures.

If our procedure ends with a select xxx we DON't get any error messaga thrown....

Played with option on_tsql_error without success.

Any help is appreciated

Below is code showing how we connect to the database:

        String _coninfo = machinename;
        Properties _props = new Properties();
        _props.put("user", userID);
        _props.put("password", password);
        _props.put("servicename", dbName);
        _props.put("CANCEL_ALL", "false");
        _props.put("SESSION_TIMEOUT", -1 + "");
        _props.put("CHARSET", "cp1252");
        //  _props.put("LANGUAGE","us_english");
        _props.put("JCONNECT_VERSION", "5");
        _props.put("pbuf","256k");
//      _props.put("MaxPooledStatements", "0");
//      _props.put("BatchPerformanceWorkaround", "true");
//      _props.put("InsensitiveResultsSetBufferSize", "8192");
//      _props.put("SelectMehod", "Cursor");
    // Load the Sybase Driver
    try
    {
        try
        {
            Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
        }

[Breck_Carter]

Please show us the stored procedure that has the primary key violation. Also, show us the application code that calls the procedure and does not detect any exception when the procedure has the primary key violation. The probability that the problem lies somewhere in that code is 0.99999 or thereabouts.

[Former Member]

OK, Here's a sample:

The proc:

DROP TABLE IF EXISTS ajs_table
CREATE TABLE "ajs_table" (
  "a" integer not null,
  PRIMARY KEY ("a")
)
insert into ajs_table values(1)  --Insert 1:st row to make exception
DROP PROCEDURE IF EXISTS ajs_proc
go
create PROCEDURE ajs_proc() 
as
begin
  insert into ajs_table values(1) -- Throws exception in ISQL
  select * from ajs_table
end
go
--exec ajs_proc

And the java code:

/**
 * 
 * Project          :   DaVision
 * File                 :   ajs.java
 * First issue  :   2 jun 2014
 * Author               :   janne
 *
 * Notes:
 * 
 * 
 */
package se.whatever;

import java.sql.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class ajs
{

    public static void main(String[] args)
    {
        Driver driverManager = null;

        String _coninfo = "THE_SERVER";
        Properties _props = new Properties();
        _props.put("user", "THE_USER");
        _props.put("password", "THE_PASSWORD");
        _props.put("servicename", "THE_DATABASE");
        _props.put("CANCEL_ALL", "false");
        _props.put("SESSION_TIMEOUT", -1 + "");
        _props.put("CHARSET", "cp1252");
        _props.put("JCONNECT_VERSION", "5");
        _props.put("pbuf","256k");

        // Load the Sybase Driver
        try
        {
            try
            {
                Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
            }
            catch (Exception e)
            {
                Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
            }
            StringBuffer temp = new StringBuffer();
            // Use the Sybase jConnect driver...
            temp.append("jdbc:sybase:Tds:");
            // to connect to the supplied machine name...
            temp.append(_coninfo);
            // on the default port number for ASA...
            temp.append(":" + 2638);

            if (driverManager == null)
                driverManager = DriverManager.getDriver(temp.toString());
            Connection conn = null;
            // Create Connections
            conn = DriverManager.getConnection(temp.toString(), _props);


            Statement stmt = conn.createStatement();
            String sqlStr = "exec ajs_proc()";
            ResultSet res = stmt.executeQuery(sqlStr);
            while (res.next()) 
            {
                System.out.println(res.getInt(1));
            }

    }
    catch (Exception e)
    {
// We do not get to this poin as long as proc. ends with select
        System.out.println(e);
    }
    }
}

[Breck_Carter · Accepted solution]

When you "Played with option on_tsql_error without success" did you try the following?

SET OPTION PUBLIC.on_tsql_error = 'Stop';

The default for TDS connections (e.g., Jconnect) is 'Continue', whereas the default for other connections (e.g., dbisql) is 'Conditional'.

Please note that these evil defaults are usually set by a stored procedure in your database called sp_login_environment (see the login_procedure option) that calls sp_tsql_environment, and together they do great damage to the safety of SQL operations (e.g., set temporary option "chained" = 'OFF').

ALTER PROCEDURE "dbo"."sp_login_environment"()
begin
  if "connection_property"('CommProtocol') = 'TDS' then
    call "dbo"."sp_tsql_environment"()
  end if
end

ALTER PROCEDURE "dbo"."sp_tsql_environment"()
begin
  if "db_property"('IQStore') = 'Off' then
    -- SQL Anywhere datastore
    set temporary option "close_on_endtrans" = 'OFF'
  end if;
  set temporary option "ansinull" = 'OFF';
  set temporary option "tsql_variables" = 'ON';
  set temporary option "ansi_blanks" = 'ON';
  set temporary option "chained" = 'OFF';
  set temporary option "quoted_identifier" = 'OFF';
  set temporary option "allow_nulls_by_default" = 'OFF';
  set temporary option "on_tsql_error" = 'CONTINUE';
  set temporary option "isolation_level" = '1';
  set temporary option "date_format" = 'YYYY-MM-DD';
  set temporary option "timestamp_format" = 'YYYY-MM-DD HH:NN:SS.SSS';
  set temporary option "time_format" = 'HH:NN:SS.SSS';
  set temporary option "date_order" = 'MDY';
  set temporary option "escape_character" = 'OFF'
end

You can stop this insanity as follows:

SET OPTION PUBLIC.LOGIN_PROCEDURE = ''; -- turn off any possibility sp_tsql_environment will be called.

[Former Member · Accepted solution]

Thank',s Now it works! I prefer to keep the login proc and set the on_tsql_error after login. Regards Janne

[VolkerBarth · Accepted solution]

So just for the record - the solution was to add Breck's suggestion?

SET OPTION PUBLIC.on_tsql_error = 'Stop'

[Former Member · Accepted solution]

Yes as I wrote, We still let login_procedure do it's rowk but afterwards calling SET OPTION PUBLIC.on_tsql_error = 'Stop' worked fine for us. We have wrapped our dbonnections i a pool so the change was easy to implement since all connections are created at one place in our library. Statments are not used i appl. code. : OurConnection c = OurPool(poolname).getConnection(max time to wait for conn and so on) ResultSet res = c.dbQuery(" exec som proc"); .. ... res.close();

[VolkerBarth · Accepted solution]

OK, so I turned Breck's comment into an answer...

Feel free to "accept" that answer to show the problem is solved.