Spectrum Spatial (SSA/LIM)

I have created some predefined queries in SSA 20.1 all works correctly using mapinfo native tab files.

I also have some tables in sqlserver 2019, that I wish to query eg: "search for a property owner name" I can set up the search and it works ok until I click on one of the rows from the selection list that appears in the left panel, as soon as I select a record  it gives a server error and freezes the interface, you cannot do anything until the browser is refreshed and you start again. Sometimes a restart of the platform is required to fix the issue.

Error from the log file
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-Jun-24 11:55:20,851 [ConnectSpatialRestProxyAction] [tenant=analyst, host=GIS, thread=http-nio-8010-exec-5]
[ERROR] Response body: {"type":"com.mapinfo.midev.service.feature.ws.v1.ServiceException","message":"java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int."}
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
There is no NVARCHAR field in the view I am querying. but here is a field that has a name concatenation in it of, [surname], [first name] etc.
could it be that this is causing the issue.

I have been trying to get several sql server queries works for days and have had no success.
If I recreate the table same table as a native mapinfo table, and test the same query, it works perfectly with out issue.




------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Hi Russell,

Can you share the SQL query you are using? 

I have seen this kind of error in SQL Server before when I forgot to enclose a number (in varchar column) in single quotes. Wondering if it is something similar here.

Cheers,

Nigel Lester
The Spatial Distillery Co.

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC
------------------------------

Original Message:
Sent: 06-23-2021 23:55
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

I have created some predefined queries in SSA 20.1 all works correctly using mapinfo native tab files.

I also have some tables in sqlserver 2019, that I wish to query eg: "search for a property owner name" I can set up the search and it works ok until I click on one of the rows from the selection list that appears in the left panel, as soon as I select a record  it gives a server error and freezes the interface, you cannot do anything until the browser is refreshed and you start again. Sometimes a restart of the platform is required to fix the issue.

Error from the log file
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-Jun-24 11:55:20,851 [ConnectSpatialRestProxyAction] [tenant=analyst, host=GIS, thread=http-nio-8010-exec-5]
[ERROR] Response body: {"type":"com.mapinfo.midev.service.feature.ws.v1.ServiceException","message":"java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int."}
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
There is no NVARCHAR field in the view I am querying. but here is a field that has a name concatenation in it of, [surname], [first name] etc.
could it be that this is causing the issue.

I have been trying to get several sql server queries works for days and have had no success.
If I recreate the table same table as a native mapinfo table, and test the same query, it works perfectly with out issue.




------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Yes I sure can, It will be a bit complex to follow though as it comes from a few tables.
I am grateful for any assistance I can get with this thank you.

The "dbo.Associates_Links_Basic.Item_ID" is the primary key in the table.
The table displays fine and works ok. until I try to query it then select the object from the list in spectrum spatial.

As an example I might query a owner name like "Ball" this component works and returns 6 entries from the table.
I scroll to the one I want to select and show on the map that when it fails.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.Associates_Links_Basic.Item_ID, CAST(LTRIM(RTRIM(REPLACE(ISNULL(dbo.Associates_Basic.Name_Value_1, ' ') + ' ' + ISNULL(dbo.Associates_Basic.Name_Value_2, ' '), ' ', ' '))) AS Varchar(255)) AS Name,
CAST(dbo.Properties_Basic.Property_Id AS Varchar(20)) AS Property_Id, CAST(dbo.Associates_Links_Basic.Ownership_Percentage AS Varchar(10)) + '%' AS Owner_Percentage,
dbo.Associates_Basic.Contact_Email_Address_Home AS email, dbo.Associates_Basic.Contact_Phone_Number_Home AS HomePhone, dbo.Associates_Basic.Contact_Phone_Number_Mobile AS Mobile,
dbo.Properties_Basic.Full_Street_Address AS Address, dbo.Associates_Basic.Type AS AssociateType, 'Property Owners' AS FeatureType, 'Property-Parcel' AS MapGroup, 'Region' AS FeatureClass,
dbo.Properties.SP_GEOMETRY
FROM dbo.Associates_Basic INNER JOIN
dbo.Associates_Links_Basic ON dbo.Associates_Basic.Item_ID = dbo.Associates_Links_Basic.Associate_Id INNER JOIN
dbo.Properties_Basic ON dbo.Associates_Links_Basic.Linked_ID = dbo.Properties_Basic.Item_ID INNER JOIN
dbo.Properties ON dbo.Properties_Basic.Property_Id = dbo.Properties.PropNum
WHERE (dbo.Associates_Links_Basic.Type = 'Property') AND (dbo.Properties_Basic.Defunct = 1) AND (dbo.Associates_Links_Basic.Ownership_Percentage > 0)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Original Message:
Sent: 06-24-2021 00:48
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

Can you share the SQL query you are using?

I have seen this kind of error in SQL Server before when I forgot to enclose a number (in varchar column) in single quotes. Wondering if it is something similar here.

Cheers,

Nigel Lester
The Spatial Distillery Co.

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-23-2021 23:55
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

I have created some predefined queries in SSA 20.1 all works correctly using mapinfo native tab files.

I also have some tables in sqlserver 2019, that I wish to query eg: "search for a property owner name" I can set up the search and it works ok until I click on one of the rows from the selection list that appears in the left panel, as soon as I select a record  it gives a server error and freezes the interface, you cannot do anything until the browser is refreshed and you start again. Sometimes a restart of the platform is required to fix the issue.

Error from the log file
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-Jun-24 11:55:20,851 [ConnectSpatialRestProxyAction] [tenant=analyst, host=GIS, thread=http-nio-8010-exec-5]
[ERROR] Response body: {"type":"com.mapinfo.midev.service.feature.ws.v1.ServiceException","message":"java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int."}
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
There is no NVARCHAR field in the view I am querying. but here is a field that has a name concatenation in it of, [surname], [first name] etc.
could it be that this is causing the issue.

I have been trying to get several sql server queries works for days and have had no success.
If I recreate the table same table as a native mapinfo table, and test the same query, it works perfectly with out issue.




------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Hi Russell,

I can't see an obvious error in the SQL. Were you running the same SQL Server query in the previous version of SSA?

Cheers,

Nigel

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC
------------------------------

Original Message:
Sent: 06-24-2021 01:09
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Yes I sure can, It will be a bit complex to follow though as it comes from a few tables.
I am grateful for any assistance I can get with this thank you.

The "dbo.Associates_Links_Basic.Item_ID" is the primary key in the table.
The table displays fine and works ok. until I try to query it then select the object from the list in spectrum spatial.

As an example I might query a owner name like "Ball" this component works and returns 6 entries from the table.
I scroll to the one I want to select and show on the map that when it fails.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.Associates_Links_Basic.Item_ID, CAST(LTRIM(RTRIM(REPLACE(ISNULL(dbo.Associates_Basic.Name_Value_1, ' ') + ' ' + ISNULL(dbo.Associates_Basic.Name_Value_2, ' '), ' ', ' '))) AS Varchar(255)) AS Name,
CAST(dbo.Properties_Basic.Property_Id AS Varchar(20)) AS Property_Id, CAST(dbo.Associates_Links_Basic.Ownership_Percentage AS Varchar(10)) + '%' AS Owner_Percentage,
dbo.Associates_Basic.Contact_Email_Address_Home AS email, dbo.Associates_Basic.Contact_Phone_Number_Home AS HomePhone, dbo.Associates_Basic.Contact_Phone_Number_Mobile AS Mobile,
dbo.Properties_Basic.Full_Street_Address AS Address, dbo.Associates_Basic.Type AS AssociateType, 'Property Owners' AS FeatureType, 'Property-Parcel' AS MapGroup, 'Region' AS FeatureClass,
dbo.Properties.SP_GEOMETRY
FROM dbo.Associates_Basic INNER JOIN
dbo.Associates_Links_Basic ON dbo.Associates_Basic.Item_ID = dbo.Associates_Links_Basic.Associate_Id INNER JOIN
dbo.Properties_Basic ON dbo.Associates_Links_Basic.Linked_ID = dbo.Properties_Basic.Item_ID INNER JOIN
dbo.Properties ON dbo.Properties_Basic.Property_Id = dbo.Properties.PropNum
WHERE (dbo.Associates_Links_Basic.Type = 'Property') AND (dbo.Properties_Basic.Defunct = 1) AND (dbo.Associates_Links_Basic.Ownership_Percentage > 0)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 00:48
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

Can you share the SQL query you are using?

I have seen this kind of error in SQL Server before when I forgot to enclose a number (in varchar column) in single quotes. Wondering if it is something similar here.

Cheers,

Nigel Lester
The Spatial Distillery Co.

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-23-2021 23:55
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

I have created some predefined queries in SSA 20.1 all works correctly using mapinfo native tab files.

I also have some tables in sqlserver 2019, that I wish to query eg: "search for a property owner name" I can set up the search and it works ok until I click on one of the rows from the selection list that appears in the left panel, as soon as I select a record  it gives a server error and freezes the interface, you cannot do anything until the browser is refreshed and you start again. Sometimes a restart of the platform is required to fix the issue.

Error from the log file
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-Jun-24 11:55:20,851 [ConnectSpatialRestProxyAction] [tenant=analyst, host=GIS, thread=http-nio-8010-exec-5]
[ERROR] Response body: {"type":"com.mapinfo.midev.service.feature.ws.v1.ServiceException","message":"java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int."}
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
There is no NVARCHAR field in the view I am querying. but here is a field that has a name concatenation in it of, [surname], [first name] etc.
could it be that this is causing the issue.

I have been trying to get several sql server queries works for days and have had no success.
If I recreate the table same table as a native mapinfo table, and test the same query, it works perfectly with out issue.




------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Unfortunately Not this is a new server and new install and a new sql server behind it too.

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Original Message:
Sent: 06-24-2021 01:57
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

I can't see an obvious error in the SQL. Were you running the same SQL Server query in the previous version of SSA?

Cheers,

Nigel

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-24-2021 01:09
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Yes I sure can, It will be a bit complex to follow though as it comes from a few tables.
I am grateful for any assistance I can get with this thank you.

The "dbo.Associates_Links_Basic.Item_ID" is the primary key in the table.
The table displays fine and works ok. until I try to query it then select the object from the list in spectrum spatial.

As an example I might query a owner name like "Ball" this component works and returns 6 entries from the table.
I scroll to the one I want to select and show on the map that when it fails.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.Associates_Links_Basic.Item_ID, CAST(LTRIM(RTRIM(REPLACE(ISNULL(dbo.Associates_Basic.Name_Value_1, ' ') + ' ' + ISNULL(dbo.Associates_Basic.Name_Value_2, ' '), ' ', ' '))) AS Varchar(255)) AS Name,
CAST(dbo.Properties_Basic.Property_Id AS Varchar(20)) AS Property_Id, CAST(dbo.Associates_Links_Basic.Ownership_Percentage AS Varchar(10)) + '%' AS Owner_Percentage,
dbo.Associates_Basic.Contact_Email_Address_Home AS email, dbo.Associates_Basic.Contact_Phone_Number_Home AS HomePhone, dbo.Associates_Basic.Contact_Phone_Number_Mobile AS Mobile,
dbo.Properties_Basic.Full_Street_Address AS Address, dbo.Associates_Basic.Type AS AssociateType, 'Property Owners' AS FeatureType, 'Property-Parcel' AS MapGroup, 'Region' AS FeatureClass,
dbo.Properties.SP_GEOMETRY
FROM dbo.Associates_Basic INNER JOIN
dbo.Associates_Links_Basic ON dbo.Associates_Basic.Item_ID = dbo.Associates_Links_Basic.Associate_Id INNER JOIN
dbo.Properties_Basic ON dbo.Associates_Links_Basic.Linked_ID = dbo.Properties_Basic.Item_ID INNER JOIN
dbo.Properties ON dbo.Properties_Basic.Property_Id = dbo.Properties.PropNum
WHERE (dbo.Associates_Links_Basic.Type = 'Property') AND (dbo.Properties_Basic.Defunct = 1) AND (dbo.Associates_Links_Basic.Ownership_Percentage > 0)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 00:48
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

Can you share the SQL query you are using?

I have seen this kind of error in SQL Server before when I forgot to enclose a number (in varchar column) in single quotes. Wondering if it is something similar here.

Cheers,

Nigel Lester
The Spatial Distillery Co.

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-23-2021 23:55
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

I have created some predefined queries in SSA 20.1 all works correctly using mapinfo native tab files.

I also have some tables in sqlserver 2019, that I wish to query eg: "search for a property owner name" I can set up the search and it works ok until I click on one of the rows from the selection list that appears in the left panel, as soon as I select a record  it gives a server error and freezes the interface, you cannot do anything until the browser is refreshed and you start again. Sometimes a restart of the platform is required to fix the issue.

Error from the log file
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-Jun-24 11:55:20,851 [ConnectSpatialRestProxyAction] [tenant=analyst, host=GIS, thread=http-nio-8010-exec-5]
[ERROR] Response body: {"type":"com.mapinfo.midev.service.feature.ws.v1.ServiceException","message":"java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int."}
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
There is no NVARCHAR field in the view I am querying. but here is a field that has a name concatenation in it of, [surname], [first name] etc.
could it be that this is causing the issue.

I have been trying to get several sql server queries works for days and have had no success.
If I recreate the table same table as a native mapinfo table, and test the same query, it works perfectly with out issue.




------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Hi Russell,

I've seen a problem where a field called 'Date' existed in the table I was trying to display.  I'm just wondering if the use of the 'Name' field causes a similar problem.

Otherwise, perhaps you can simplify the View you are trying to query to narrow down where the issue lies.

------------------------------
James Nolet
Dooley Mitchell & Morrison Pty Ltd
Mentone, VIC, Australia
------------------------------

Original Message:
Sent: 06-24-2021 02:51
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Unfortunately Not this is a new server and new install and a new sql server behind it too.

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 01:57
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

I can't see an obvious error in the SQL. Were you running the same SQL Server query in the previous version of SSA?

Cheers,

Nigel

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-24-2021 01:09
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Yes I sure can, It will be a bit complex to follow though as it comes from a few tables.
I am grateful for any assistance I can get with this thank you.

The "dbo.Associates_Links_Basic.Item_ID" is the primary key in the table.
The table displays fine and works ok. until I try to query it then select the object from the list in spectrum spatial.

As an example I might query a owner name like "Ball" this component works and returns 6 entries from the table.
I scroll to the one I want to select and show on the map that when it fails.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.Associates_Links_Basic.Item_ID, CAST(LTRIM(RTRIM(REPLACE(ISNULL(dbo.Associates_Basic.Name_Value_1, ' ') + ' ' + ISNULL(dbo.Associates_Basic.Name_Value_2, ' '), ' ', ' '))) AS Varchar(255)) AS Name,
CAST(dbo.Properties_Basic.Property_Id AS Varchar(20)) AS Property_Id, CAST(dbo.Associates_Links_Basic.Ownership_Percentage AS Varchar(10)) + '%' AS Owner_Percentage,
dbo.Associates_Basic.Contact_Email_Address_Home AS email, dbo.Associates_Basic.Contact_Phone_Number_Home AS HomePhone, dbo.Associates_Basic.Contact_Phone_Number_Mobile AS Mobile,
dbo.Properties_Basic.Full_Street_Address AS Address, dbo.Associates_Basic.Type AS AssociateType, 'Property Owners' AS FeatureType, 'Property-Parcel' AS MapGroup, 'Region' AS FeatureClass,
dbo.Properties.SP_GEOMETRY
FROM dbo.Associates_Basic INNER JOIN
dbo.Associates_Links_Basic ON dbo.Associates_Basic.Item_ID = dbo.Associates_Links_Basic.Associate_Id INNER JOIN
dbo.Properties_Basic ON dbo.Associates_Links_Basic.Linked_ID = dbo.Properties_Basic.Item_ID INNER JOIN
dbo.Properties ON dbo.Properties_Basic.Property_Id = dbo.Properties.PropNum
WHERE (dbo.Associates_Links_Basic.Type = 'Property') AND (dbo.Properties_Basic.Defunct = 1) AND (dbo.Associates_Links_Basic.Ownership_Percentage > 0)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 00:48
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

Can you share the SQL query you are using?

I have seen this kind of error in SQL Server before when I forgot to enclose a number (in varchar column) in single quotes. Wondering if it is something similar here.

Cheers,

Nigel Lester
The Spatial Distillery Co.

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-23-2021 23:55
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

I have created some predefined queries in SSA 20.1 all works correctly using mapinfo native tab files.

I also have some tables in sqlserver 2019, that I wish to query eg: "search for a property owner name" I can set up the search and it works ok until I click on one of the rows from the selection list that appears in the left panel, as soon as I select a record  it gives a server error and freezes the interface, you cannot do anything until the browser is refreshed and you start again. Sometimes a restart of the platform is required to fix the issue.

Error from the log file
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-Jun-24 11:55:20,851 [ConnectSpatialRestProxyAction] [tenant=analyst, host=GIS, thread=http-nio-8010-exec-5]
[ERROR] Response body: {"type":"com.mapinfo.midev.service.feature.ws.v1.ServiceException","message":"java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int."}
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
There is no NVARCHAR field in the view I am querying. but here is a field that has a name concatenation in it of, [surname], [first name] etc.
could it be that this is causing the issue.

I have been trying to get several sql server queries works for days and have had no success.
If I recreate the table same table as a native mapinfo table, and test the same query, it works perfectly with out issue.




------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

The word Date is a reserved word and should not be used alter the name of the field to say something like "DateCreated" or "DateXXX" for what ever the date is used for.


I think I have found my problem with SSA, Sql server shows the integer as "123456" while SSA is  showing the integer as "123,456" thus the error saying that "com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int.. The query seems to think that the integer is an NVARCHAR as it has a comma in the format, therefore it cannot convert it to an Integer.

I have checked all the settings on the server and they are correct numbers are not grouped or use a comma.
My question now is how do I fix it and is this specific to Spectrum Spatial, is there a setting that I can use, to globally change to integer format. Or a map basic function to change the format.
Anyone have any ideas?

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Original Message:
Sent: 06-24-2021 03:28
From: James Nolet
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

I've seen a problem where a field called 'Date' existed in the table I was trying to display.  I'm just wondering if the use of the 'Name' field causes a similar problem.

Otherwise, perhaps you can simplify the View you are trying to query to narrow down where the issue lies.

------------------------------
James Nolet
Dooley Mitchell & Morrison Pty Ltd
Mentone, VIC, Australia

Original Message:
Sent: 06-24-2021 02:51
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Unfortunately Not this is a new server and new install and a new sql server behind it too.

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 01:57
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

I can't see an obvious error in the SQL. Were you running the same SQL Server query in the previous version of SSA?

Cheers,

Nigel

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-24-2021 01:09
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Yes I sure can, It will be a bit complex to follow though as it comes from a few tables.
I am grateful for any assistance I can get with this thank you.

The "dbo.Associates_Links_Basic.Item_ID" is the primary key in the table.
The table displays fine and works ok. until I try to query it then select the object from the list in spectrum spatial.

As an example I might query a owner name like "Ball" this component works and returns 6 entries from the table.
I scroll to the one I want to select and show on the map that when it fails.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.Associates_Links_Basic.Item_ID, CAST(LTRIM(RTRIM(REPLACE(ISNULL(dbo.Associates_Basic.Name_Value_1, ' ') + ' ' + ISNULL(dbo.Associates_Basic.Name_Value_2, ' '), ' ', ' '))) AS Varchar(255)) AS Name,
CAST(dbo.Properties_Basic.Property_Id AS Varchar(20)) AS Property_Id, CAST(dbo.Associates_Links_Basic.Ownership_Percentage AS Varchar(10)) + '%' AS Owner_Percentage,
dbo.Associates_Basic.Contact_Email_Address_Home AS email, dbo.Associates_Basic.Contact_Phone_Number_Home AS HomePhone, dbo.Associates_Basic.Contact_Phone_Number_Mobile AS Mobile,
dbo.Properties_Basic.Full_Street_Address AS Address, dbo.Associates_Basic.Type AS AssociateType, 'Property Owners' AS FeatureType, 'Property-Parcel' AS MapGroup, 'Region' AS FeatureClass,
dbo.Properties.SP_GEOMETRY
FROM dbo.Associates_Basic INNER JOIN
dbo.Associates_Links_Basic ON dbo.Associates_Basic.Item_ID = dbo.Associates_Links_Basic.Associate_Id INNER JOIN
dbo.Properties_Basic ON dbo.Associates_Links_Basic.Linked_ID = dbo.Properties_Basic.Item_ID INNER JOIN
dbo.Properties ON dbo.Properties_Basic.Property_Id = dbo.Properties.PropNum
WHERE (dbo.Associates_Links_Basic.Type = 'Property') AND (dbo.Properties_Basic.Defunct = 1) AND (dbo.Associates_Links_Basic.Ownership_Percentage > 0)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 00:48
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

Can you share the SQL query you are using?

I have seen this kind of error in SQL Server before when I forgot to enclose a number (in varchar column) in single quotes. Wondering if it is something similar here.

Cheers,

Nigel Lester
The Spatial Distillery Co.

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-23-2021 23:55
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

I have created some predefined queries in SSA 20.1 all works correctly using mapinfo native tab files.

I also have some tables in sqlserver 2019, that I wish to query eg: "search for a property owner name" I can set up the search and it works ok until I click on one of the rows from the selection list that appears in the left panel, as soon as I select a record  it gives a server error and freezes the interface, you cannot do anything until the browser is refreshed and you start again. Sometimes a restart of the platform is required to fix the issue.

Error from the log file
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-Jun-24 11:55:20,851 [ConnectSpatialRestProxyAction] [tenant=analyst, host=GIS, thread=http-nio-8010-exec-5]
[ERROR] Response body: {"type":"com.mapinfo.midev.service.feature.ws.v1.ServiceException","message":"java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int."}
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
There is no NVARCHAR field in the view I am querying. but here is a field that has a name concatenation in it of, [surname], [first name] etc.
could it be that this is causing the issue.

I have been trying to get several sql server queries works for days and have had no success.
If I recreate the table same table as a native mapinfo table, and test the same query, it works perfectly with out issue.




------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Here are a couple of images that show the filed type and the Data that displays

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Original Message:
Sent: 06-24-2021 18:35
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

The word Date is a reserved word and should not be used alter the name of the field to say something like "DateCreated" or "DateXXX" for what ever the date is used for.


I think I have found my problem with SSA, Sql server shows the integer as "123456" while SSA is  showing the integer as "123,456" thus the error saying that "com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int.. The query seems to think that the integer is an NVARCHAR as it has a comma in the format, therefore it cannot convert it to an Integer.

I have checked all the settings on the server and they are correct numbers are not grouped or use a comma.
My question now is how do I fix it and is this specific to Spectrum Spatial, is there a setting that I can use, to globally change to integer format. Or a map basic function to change the format.
Anyone have any ideas?

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 03:28
From: James Nolet
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

I've seen a problem where a field called 'Date' existed in the table I was trying to display.  I'm just wondering if the use of the 'Name' field causes a similar problem.

Otherwise, perhaps you can simplify the View you are trying to query to narrow down where the issue lies.

------------------------------
James Nolet
Dooley Mitchell & Morrison Pty Ltd
Mentone, VIC, Australia

Original Message:
Sent: 06-24-2021 02:51
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Unfortunately Not this is a new server and new install and a new sql server behind it too.

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 01:57
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

I can't see an obvious error in the SQL. Were you running the same SQL Server query in the previous version of SSA?

Cheers,

Nigel

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-24-2021 01:09
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Yes I sure can, It will be a bit complex to follow though as it comes from a few tables.
I am grateful for any assistance I can get with this thank you.

The "dbo.Associates_Links_Basic.Item_ID" is the primary key in the table.
The table displays fine and works ok. until I try to query it then select the object from the list in spectrum spatial.

As an example I might query a owner name like "Ball" this component works and returns 6 entries from the table.
I scroll to the one I want to select and show on the map that when it fails.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.Associates_Links_Basic.Item_ID, CAST(LTRIM(RTRIM(REPLACE(ISNULL(dbo.Associates_Basic.Name_Value_1, ' ') + ' ' + ISNULL(dbo.Associates_Basic.Name_Value_2, ' '), ' ', ' '))) AS Varchar(255)) AS Name,
CAST(dbo.Properties_Basic.Property_Id AS Varchar(20)) AS Property_Id, CAST(dbo.Associates_Links_Basic.Ownership_Percentage AS Varchar(10)) + '%' AS Owner_Percentage,
dbo.Associates_Basic.Contact_Email_Address_Home AS email, dbo.Associates_Basic.Contact_Phone_Number_Home AS HomePhone, dbo.Associates_Basic.Contact_Phone_Number_Mobile AS Mobile,
dbo.Properties_Basic.Full_Street_Address AS Address, dbo.Associates_Basic.Type AS AssociateType, 'Property Owners' AS FeatureType, 'Property-Parcel' AS MapGroup, 'Region' AS FeatureClass,
dbo.Properties.SP_GEOMETRY
FROM dbo.Associates_Basic INNER JOIN
dbo.Associates_Links_Basic ON dbo.Associates_Basic.Item_ID = dbo.Associates_Links_Basic.Associate_Id INNER JOIN
dbo.Properties_Basic ON dbo.Associates_Links_Basic.Linked_ID = dbo.Properties_Basic.Item_ID INNER JOIN
dbo.Properties ON dbo.Properties_Basic.Property_Id = dbo.Properties.PropNum
WHERE (dbo.Associates_Links_Basic.Type = 'Property') AND (dbo.Properties_Basic.Defunct = 1) AND (dbo.Associates_Links_Basic.Ownership_Percentage > 0)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 00:48
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

Can you share the SQL query you are using?

I have seen this kind of error in SQL Server before when I forgot to enclose a number (in varchar column) in single quotes. Wondering if it is something similar here.

Cheers,

Nigel Lester
The Spatial Distillery Co.

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-23-2021 23:55
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

I have created some predefined queries in SSA 20.1 all works correctly using mapinfo native tab files.

I also have some tables in sqlserver 2019, that I wish to query eg: "search for a property owner name" I can set up the search and it works ok until I click on one of the rows from the selection list that appears in the left panel, as soon as I select a record  it gives a server error and freezes the interface, you cannot do anything until the browser is refreshed and you start again. Sometimes a restart of the platform is required to fix the issue.

Error from the log file
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-Jun-24 11:55:20,851 [ConnectSpatialRestProxyAction] [tenant=analyst, host=GIS, thread=http-nio-8010-exec-5]
[ERROR] Response body: {"type":"com.mapinfo.midev.service.feature.ws.v1.ServiceException","message":"java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int."}
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
There is no NVARCHAR field in the view I am querying. but here is a field that has a name concatenation in it of, [surname], [first name] etc.
could it be that this is causing the issue.

I have been trying to get several sql server queries works for days and have had no success.
If I recreate the table same table as a native mapinfo table, and test the same query, it works perfectly with out issue.




------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Well I am very pleased to say it's fixed after a few days of testing.
The fix was as follows for anyone else that may come across this issue using sql server views. (Spatial and A_Spatial Data views)
Two things I did that fixed the Issue were as follows, something of note in this though, is the select statement can only reference tables,(not other views). The select statement can have multiple tables and joins but not full or outer joins.

I did Raise a case with Precisely to get this resolved, and I have passed on my findings and explanation to them.
They got back to me fairly quickly after I raised the case, and were somewhat baffled by the situation too, but very helpful in the process of  testing some options to fix it.
Thanks Guy's, Awesome.

Cheers and I hope you find it a bit easier to resolve than I did, if you have this issue.

1. CREATE VIEW <view name>
   With SCHEMABINDING
   As
   SELECT <statement goes here>  FROM <statement>  WHERE <statement>
2. CREATE UNIQUE CLUSTERED INDEX
   ucidx3 ,<index name>
   ON dbo.<view name> (field name)

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------

Original Message:
Sent: 06-24-2021 19:02
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)


Here are a couple of images that show the filed type and the Data that displays

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 18:35
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

The word Date is a reserved word and should not be used alter the name of the field to say something like "DateCreated" or "DateXXX" for what ever the date is used for.


I think I have found my problem with SSA, Sql server shows the integer as "123456" while SSA is  showing the integer as "123,456" thus the error saying that "com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int.. The query seems to think that the integer is an NVARCHAR as it has a comma in the format, therefore it cannot convert it to an Integer.

I have checked all the settings on the server and they are correct numbers are not grouped or use a comma.
My question now is how do I fix it and is this specific to Spectrum Spatial, is there a setting that I can use, to globally change to integer format. Or a map basic function to change the format.
Anyone have any ideas?

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 03:28
From: James Nolet
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

I've seen a problem where a field called 'Date' existed in the table I was trying to display.  I'm just wondering if the use of the 'Name' field causes a similar problem.

Otherwise, perhaps you can simplify the View you are trying to query to narrow down where the issue lies.

------------------------------
James Nolet
Dooley Mitchell & Morrison Pty Ltd
Mentone, VIC, Australia

Original Message:
Sent: 06-24-2021 02:51
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Unfortunately Not this is a new server and new install and a new sql server behind it too.

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 01:57
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

I can't see an obvious error in the SQL. Were you running the same SQL Server query in the previous version of SSA?

Cheers,

Nigel

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-24-2021 01:09
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Yes I sure can, It will be a bit complex to follow though as it comes from a few tables.
I am grateful for any assistance I can get with this thank you.

The "dbo.Associates_Links_Basic.Item_ID" is the primary key in the table.
The table displays fine and works ok. until I try to query it then select the object from the list in spectrum spatial.

As an example I might query a owner name like "Ball" this component works and returns 6 entries from the table.
I scroll to the one I want to select and show on the map that when it fails.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.Associates_Links_Basic.Item_ID, CAST(LTRIM(RTRIM(REPLACE(ISNULL(dbo.Associates_Basic.Name_Value_1, ' ') + ' ' + ISNULL(dbo.Associates_Basic.Name_Value_2, ' '), ' ', ' '))) AS Varchar(255)) AS Name,
CAST(dbo.Properties_Basic.Property_Id AS Varchar(20)) AS Property_Id, CAST(dbo.Associates_Links_Basic.Ownership_Percentage AS Varchar(10)) + '%' AS Owner_Percentage,
dbo.Associates_Basic.Contact_Email_Address_Home AS email, dbo.Associates_Basic.Contact_Phone_Number_Home AS HomePhone, dbo.Associates_Basic.Contact_Phone_Number_Mobile AS Mobile,
dbo.Properties_Basic.Full_Street_Address AS Address, dbo.Associates_Basic.Type AS AssociateType, 'Property Owners' AS FeatureType, 'Property-Parcel' AS MapGroup, 'Region' AS FeatureClass,
dbo.Properties.SP_GEOMETRY
FROM dbo.Associates_Basic INNER JOIN
dbo.Associates_Links_Basic ON dbo.Associates_Basic.Item_ID = dbo.Associates_Links_Basic.Associate_Id INNER JOIN
dbo.Properties_Basic ON dbo.Associates_Links_Basic.Linked_ID = dbo.Properties_Basic.Item_ID INNER JOIN
dbo.Properties ON dbo.Properties_Basic.Property_Id = dbo.Properties.PropNum
WHERE (dbo.Associates_Links_Basic.Type = 'Property') AND (dbo.Properties_Basic.Defunct = 1) AND (dbo.Associates_Links_Basic.Ownership_Percentage > 0)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC

Original Message:
Sent: 06-24-2021 00:48
From: Nigel Lester
Subject: Question about sql queries in SSA (I'm tearing my hair out)

Hi Russell,

Can you share the SQL query you are using?

I have seen this kind of error in SQL Server before when I forgot to enclose a number (in varchar column) in single quotes. Wondering if it is something similar here.

Cheers,

Nigel Lester
The Spatial Distillery Co.

------------------------------
Nigel Lester
The Spatial Distillery
Fitzroy VIC

Original Message:
Sent: 06-23-2021 23:55
From: Russell Ferguson
Subject: Question about sql queries in SSA (I'm tearing my hair out)

I have created some predefined queries in SSA 20.1 all works correctly using mapinfo native tab files.

I also have some tables in sqlserver 2019, that I wish to query eg: "search for a property owner name" I can set up the search and it works ok until I click on one of the rows from the selection list that appears in the left panel, as soon as I select a record  it gives a server error and freezes the interface, you cannot do anything until the browser is refreshed and you start again. Sometimes a restart of the platform is required to fix the issue.

Error from the log file
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-Jun-24 11:55:20,851 [ConnectSpatialRestProxyAction] [tenant=analyst, host=GIS, thread=http-nio-8010-exec-5]
[ERROR] Response body: {"type":"com.mapinfo.midev.service.feature.ws.v1.ServiceException","message":"java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'undefined' to data type int."}
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
There is no NVARCHAR field in the view I am querying. but here is a field that has a name concatenation in it of, [surname], [first name] etc.
could it be that this is causing the issue.

I have been trying to get several sql server queries works for days and have had no success.
If I recreate the table same table as a native mapinfo table, and test the same query, it works perfectly with out issue.




------------------------------
Russell Ferguson
Ararat Rural City Council
Ararat VIC
------------------------------