6

I am executing a stored procedure via standard JDBC Connection using MS SQL Driver version 3.0.

I have found that when I create and insert data into a temporary table the stored procedure doesn't execute properly.

The Java code won't throw a exception, but the javax.sql.ResultSet will be null.

The point of failure in the stored procedure is when I un-comment the INSERT INTO #TBL CLM_NAME VALUES('VAL')

When I execute the statement using SQL Studio Manager it executes without hassle and the data as expected.

Has anyone come across this or know why its the case?

Initially I thought its because of the SQL driver and I still think it is?

Thanks.

Koekiebox
  • 5,793
  • 14
  • 53
  • 88
  • Can you make sure you are creating and loading the temp table and reading from it all in the same connection? – CoolBeans Jan 18 '11 at 20:20
  • Yes, the temporary table is created from the stored procedure I created. I only call the stored procedure from the Java code, example connect.executeQuery("{procName(?)}"). – Koekiebox Jan 18 '11 at 20:56
  • 1
    Is the offending `INSERT` instruction located in the same stored procedure that creates the temporary table? – Andriy M Feb 27 '11 at 18:18
  • Which method do you use for calling the stored procedure? – MRalwasser Feb 27 '11 at 21:36

3 Answers3

14

Maybe this will help:

It is recommended that if you want to use temp tables, you should not call "prepareStatement". You can directly execute the query from the statement object.

For example: 

String sql = "select uuid, name from Component";

Statement stmt = dbCon.createStatement();
ResultSet rs = stmt.executeQuery(sql);

If you must call "prepareStatement", then you will need to create a real table and, if needed, delete the table afterwards.

John Kugelman
  • 349,597
  • 67
  • 533
  • 578
Radu Caprescu
  • 983
  • 1
  • 8
  • 20
  • Of course, if you have any parameters, you should use prepareStatement, not createStatement, or you should be very, very careful about sql injection: http://stackoverflow.com/questions/1582161/how-does-a-preparedstatement-avoid-or-prevent-sql-injection – Guy Schalnat Jul 09 '15 at 21:03
6

The executeQuery() is intended to be used with queries that return a ResultSet, typically SELECT statements.

The executeUpdate() is intended for INSERT, UPDATE, DELETE or DDL statements that return update counts.

Both of the above (ResultSets and update counts) are considered by JDBC to be as "results". For queries that return multiple results it requires we invoke execute().

If stored procedure makes use of temp tables, probably it returns an update count first, followed by a ResultSet. You should use execute() to run query, call getMoreResults() to skip the update count and then call getResultSet() to obtain the ResultSet you want. The problem here is we have to do trial and error by calling getMoreResults() multiple times to fetch result set we want by calling getResultSet().

Instead of above way of trial and error, you can suppress all the extra "query results" by specifying "SET NOCOUNT ON".

Changes required,

  1. Put your logic in SP "SPCHILD". This Stored procedure will have logic along with temp table creation.

  2. Create a SP "SPPARENT" as below,

     CREATE PROCEDURE [dbo].[SPPARENT]    @Id int = NULL
 AS
 BEGIN
 SET NOCOUNT ON;
 EXEC(' SPCHILD @Id = ' + @Id)
 END

    From parent SP "SPPARENT" you have to call your actual SP i.e. "SPCHILD".

  3. From your JDBC code make a SP call to "SPPARENT".

Prakhyat
  • 989
  • 8
  • 17
2

I am also facing the same issue. In order to fix this, I am going setup a trace on my sql server and see all the statements that are being executive by the MS JDBC driver before executing the actual SP call. This should help me clear things up. Unfortunately our DBA is out today so I will have to do it tomorrow with her help. I will let u know what happens and what is the fix.

Chirag Nirmal
  • 21
  • 1