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"); |
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. 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?
(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
|
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.
OK, Here's a sample:
The proc:
And the java code: