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();
        }

asked 30 May '14, 06:32

Janne's gravatar image

Janne
46336
accept rate: 0%

edited 30 May '14, 09:03

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275

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.

(30 May '14, 12:47) Breck Carter

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);
    }
    }
}
(02 Jun '14, 01:44) Janne

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.
permanent link

answered 02 Jun '14, 08:40

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

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

(02 Jun '14, 11:44) Janne
Replies hidden

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

SET OPTION PUBLIC.on_tsql_error = 'Stop'
(02 Jun '14, 12:09) Volker Barth

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();

(03 Jun '14, 10:57) Janne

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

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

(04 Jun '14, 03:56) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×33
×18
×2

question asked: 30 May '14, 06:32

question was seen: 2,063 times

last updated: 04 Jun '14, 03:56