Skip to main content
Question

SQL Server JDBC issue in SQL Executor

oliver.morris
Contributor
Forum|alt.badge.img+12

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

ravenkopelman
Safer
Forum|alt.badge.img+1
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.

 

oliver.morris
Contributor
Forum|alt.badge.img+12
  • oliver.morrisContributor
  • Author
  • Contributor
  • July 16, 2018

Fme 2018.0 thanks

ravenkopelman
Safer
Forum|alt.badge.img+1
oliver.morris wrote:

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!

 

 

oliver.morris
Contributor
Forum|alt.badge.img+12
  • oliver.morrisContributor
  • Author
  • Contributor
  • July 20, 2018
ravenkopelman wrote:
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.

 

 

xiaomengatsafe
Safer
Forum|alt.badge.img+3
oliver.morris wrote:
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!
oliver.morris
Contributor
Forum|alt.badge.img+12
  • oliver.morrisContributor
  • Author
  • Contributor
  • July 24, 2018
xiaomengatsafe wrote:
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

 

 

 

xiaomengatsafe
Safer
Forum|alt.badge.img+3
oliver.morris wrote:
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


Helpful Members This Week

Community Stats

30,890
Posts
117,075
Replies
38,698
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.