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

[JohnRitsko]

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

[EvelynHernandez]

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.

[JohnRitsko]

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.

[MarcBate]

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

[EvelynHernandez]

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)

[JohnRitsko]

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.

[JeffJacobson]

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

[JohnRitsko]

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

[JohnRitsko]

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

[MarcBate]

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