topic Re: Help getting data from SQLServer with custom query in my JavaScript Application in ArcGIS JavaScript Maps SDK Questions

Help getting data from SQLServer with custom query in my JavaScript Application

JohnRitsko — Sat, 11 Dec 2021 23:16:39 GMT

Here is my current scenario.

I need to gain access to a SQL Server called say, SERVER1. Below is the custom SQL Query that I Need to utilize in order to get the most recent GPS record. I can't seem to figure out how to utilize this in my JavaScript Application to get this data. Of course once I get the data I need to dig into it to get the lat/lon, convert it, then map it. One step at a time. Perhaps using $.ajax to do a GET but not quite sure how to go about this.

SQL Query:

SELECT S.MSGTEXT
FROM [dbo].[Syslogd] S
INNER JOIN
(SELECT T.[MsgDate] AS 'MSGDATE' ,MAX(L.[MsgTime]) AS 'MSGTIME'
FROM [KiwiSysLog].[dbo].[Syslogd] L
INNER JOIN
(SELECT MAX(MSGDATE) AS 'MSGDATE' FROM [dbo].[Syslogd]) T
ON L.MsgDate = T.MSGDATE
GROUP BY T.MSGDATE) DT
ON S.MsgDate = DT.MSGDATE AND S.MsgTime = DT.MSGTIME

Results in Microsoft SQL Server Management Studio Express:

$GPGGA,222020.0,3609.839478,N,11509.243300,W,1,08,0.8,618.9,M,-18.0,M,,,9999*4F $GPRMC,222020.0,A,3609.839478,N,11509.243300,W,1.1,302.7,270416,0.0,E,A,9999*34

I appreciate the help.

John

Re: Help getting data from SQLServer with custom query in my JavaScript Application

EvelynHernandez — Thu, 28 Apr 2016 14:54:23 GMT

Make a service with the sql query that u need and publish it (as data table) and then u can access the data using the normal query and querytask.

Re: Help getting data from SQLServer with custom query in my JavaScript Application

JohnRitsko — Thu, 28 Apr 2016 15:00:02 GMT

My only issue with this is I cannot connect to this server in ArcCatalog so I can't access the database or the table I need. I have read only access to it through SQL Server Management Studio. Going to work with my database admin to see how to resolve this. If I can get it into a map then I'll do what you said Evelyn. Thank you.

Re: Help getting data from SQLServer with custom query in my JavaScript Application

MarcBate — Thu, 28 Apr 2016 15:00:45 GMT

There are several ways to do it, but one of the easiest would be to create a web service that returns a CSV file with your query results, and use the CSVLayer to map it.

CSVLayer | API Reference | ArcGIS API for JavaScript

Re: Help getting data from SQLServer with custom query in my JavaScript Application

EvelynHernandez — Thu, 28 Apr 2016 15:05:48 GMT

The other option is:

In ur sql database make a view with the query that u show me.

Then u go to ur arcgis and publish the view with a where option that u need, in this case u will need a where clausule to get the last record.

Cuz arcgis cannot make complex queries, i do this when i need more complex data. (so the view works as a table where i can access that data)

Re: Help getting data from SQLServer with custom query in my JavaScript Application

JohnRitsko — Thu, 28 Apr 2016 22:40:48 GMT

OK, I now have access to the table in Catalog but when I try to publish it, it wants to copy the database to server. I don't want that as the View will not be refreshed each time I want to look at it. Open to ideas. Thank you.

Re: Help getting data from SQLServer with custom query in my JavaScript Application

JeffJacobson — Thu, 28 Apr 2016 23:13:31 GMT

You'll need to register your data with the server, as described here: Make your data accessible to ArcGIS Server—Documentation | ArcGIS for Server

Re: Help getting data from SQLServer with custom query in my JavaScript Application

JohnRitsko — Thu, 28 Apr 2016 23:59:01 GMT

Thank you all for the input. I believe I'm almost there.

Re: Help getting data from SQLServer with custom query in my JavaScript Application

JohnRitsko — Sat, 11 Dec 2021 23:16:42 GMT

O.K. I've been struggling with this for a bit and need to get some further input. This is what I was thinking but it's not working out for me. I've utilized the example for the TableDataSource from ESRI but can't seem to get this. The SDE table has two records in it, a lat and lon value. I need to simply get access to these so I can manipulate the values from NMEA decimal degrees to degrees minutes seconds.

This is what I have so far. Simply trying to get these values from table:

# records: 2

instance: 3

val: 3609.842234

instance: 5

val: 11509.241511

var gpsTable = new TableDataSource();

= "GISMO.CFDBSRV.sde";

= "KiwiSysLog.dbo.uv_WIFIGPSLog";

var layerSource = new LayerDataSource();

= gpsTable;
 
var featureLayer = new FeatureLayer("https://sampleserver6.arcgisonline.com/arcgis/rest/services/USA/MapServer/dynamicLayer", {

"*"],

    source: layerSource

Re: Help getting data from SQLServer with custom query in my JavaScript Application

MarcBate — Tue, 03 May 2016 22:04:24 GMT

You need to create a service that has a dynamic layer in it and then put in your dynamic layer URL on the ArcGIS Server that has your GISMO.CFDBSRV.sde connection registered

Re: Help getting data from SQLServer with custom query in my JavaScript Application

JohnRitsko — Tue, 03 May 2016 22:19:55 GMT

I way overthought what I was doing. I registered the database on Server then utilized the QueryTask as stated. I then created the query for the values and then pushed those into an array and converted them into lat/lon as needed then created a graphic for them and added them to the map. Problem solved. Thank you all for the input.