Question

SQL Server JDBC issue in SQL Executor


Badge +10

Morning,

When I run the following using sql executor using the SQL Server Native client I get back a result.

EXEC	[dbo].[SP_API_VALID_SUBSCRIBER]
@modules = N'1,2,3,4,5,6,7,8,9',
        @clippolygon = N'POLYGON ((-168.046875 -85.62206859340185,183.515625 -85.62206859340185,183.515625 83.35951133035452,-168.046875 83.35951133035452,-168.046875 -85.62206859340185))';

When I run the same query using the JDBC driver for SQL Server no spatial I get no results back, although the sql execution was successful no results are returned. I have tried with and withougt the 'commit;' at the end of the script and this has no effect.

Does anyone have any ideas why this might be?

Many Thanks,

Oliver


7 replies

Badge
Hi @olivermorris, what version of FME did you see this in? Something similar was reported against FME 2015 but we could not reproduce against FME 2017 and did not pursue further.

 

Badge +10

Fme 2018.0 thanks

Badge

Fme 2018.0 thanks

There was a big driver upgrade in FME 2018.1 that might impact this. We will take another run at reproducing and see what we can do. Thanks for reporting!

 

 

Badge +10
There was a big driver upgrade in FME 2018.1 that might impact this. We will take another run at reproducing and see what we can do. Thanks for reporting!

 

 

Tried with 2018.1 this morning and still get the same result. Works with native client not with jdbc.

 

 

Badge +2
Tried with 2018.1 this morning and still get the same result. Works with native client not with jdbc.

 

 

Hi @olivermorris, our team is working on trying to reproduce the issue. Just a couple of questions:

 

  1. Can you confirm that you've tried the query using Microsoft SQL Server Spatial (JDBC), in SQLExceutor? If you haven't can you give it a try?
  2. If the above attempt doesn't work, can you send us the stored procedure? We tried to reproduce this issue using a simple mock stored procedure, but got the result back. So knowing what your stored procedure is like will help us reproduce the problem.
Thank you!
Badge +10
Hi @olivermorris, our team is working on trying to reproduce the issue. Just a couple of questions:

 

  1. Can you confirm that you've tried the query using Microsoft SQL Server Spatial (JDBC), in SQLExceutor? If you haven't can you give it a try?
  2. If the above attempt doesn't work, can you send us the stored procedure? We tried to reproduce this issue using a simple mock stored procedure, but got the result back. So knowing what your stored procedure is like will help us reproduce the problem. 
Thank you!
Hi,

 

 

I tried point 1. no change no response back.

 

The stored procedure should just return a field called subscriber and the value is 1

 

 

EXEC	[dbo].[SP_API_VALID_SUBSCRIBER]
@modules = N'1,2,3,4,5,6,7,8,9,10,11,12,13,20,21,22,23,24,26,27,28,29,30,32,34,36,38,40,41,43,45,46,48,49,51,53,55,57,59,61,62,63,64,65,66,67,68,69,70,71,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,159,160,161,162,163,164,165,166,167,168,197,201,206,207,208,209,210,211,212,213,214,215,216,217,218,219,221,224,225,243,244,251,252,253,254,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,274,275,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,298,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,348,349,350,351,354,356',
        @clippolygon = N'POLYGON ((-168.046875 -85.62206859340185,183.515625 -85.62206859340185,183.515625 83.35951133035452,-168.046875 83.35951133035452,-168.046875 -85.62206859340185))';

 

The full stored procedure is attached.sql.txt

 


 

Thank you for all your help, if you have any questions please let me know.

 

 

Thanks,

 

 

Oliver

 

 

 

Badge +2
Hi,

 

 

I tried point 1. no change no response back.

 

The stored procedure should just return a field called subscriber and the value is 1

 

 

EXEC	[dbo].[SP_API_VALID_SUBSCRIBER]
@modules = N'1,2,3,4,5,6,7,8,9,10,11,12,13,20,21,22,23,24,26,27,28,29,30,32,34,36,38,40,41,43,45,46,48,49,51,53,55,57,59,61,62,63,64,65,66,67,68,69,70,71,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,159,160,161,162,163,164,165,166,167,168,197,201,206,207,208,209,210,211,212,213,214,215,216,217,218,219,221,224,225,243,244,251,252,253,254,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,274,275,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,298,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,348,349,350,351,354,356',
        @clippolygon = N'POLYGON ((-168.046875 -85.62206859340185,183.515625 -85.62206859340185,183.515625 83.35951133035452,-168.046875 83.35951133035452,-168.046875 -85.62206859340185))';

 

The full stored procedure is attached.sql.txt

 


 

Thank you for all your help, if you have any questions please let me know.

 

 

Thanks,

 

 

Oliver

 

 

 

Thank you very much @olivermorris, for the details you provided! Our team will be investigating this issue, and we'll post updates here. 

 

Reply