Hi Rick,

I'm working on new Rest API using Web Connection. As far as I understand, I can get a cursor from SQL Server and return it as json automatically.

However, I would like to use SQL stored procedures, which will directly return json and then I'd like to pass this json directly to the customer app. So my big questions is, how to do this?

SQL Server includes JSON functionality in recent versions (I think 2016 and later), but those features are extremely arkane to use. It also doesn't work for to return JSON for random SQL queries AFAIK - it works only for data that you store in SQL Server as JSON specific fields (sort of a NoSQL hack).

I looked into the JSON features in SQL Server some time ago and dismissed them pretty much due to:

• Seriously arkane syntax
• Slow perf

I think it's better to just do normal SQL data retrieval and handle JSON conversion on the way out to HTTP rather than letting SQL Server do it. It just ends up as extra load on SQL Server, which is the most central and most performance sensitive component of most applications.

+++ Rick ---

Thanks Rick. My main concern is if I need to create nested json - for example orders and per each order all items included in this order, the only way I seem to achieve this is to get all orders into a cursor and then per each order I need to run sql query to get all items per order. If I have 200 or more orders, I'll need to make 200 or more sql queries, so isn't this slower performance compared to just simple join in sql stored procedure and then json string returned?

Like I said - the built in SQL Server JSON features will not create JSON for you from arbitrary SQL queries - they are meant for working with fields that contain JSON.

Whatever query you run that you want to turn into query it has to return a result set. You can turn that resultset - from sql data - into JSON in Web Connection. Yes you'll have to parse and walk the table but at that point you're using local cursors which should be fast.

You're not gaining that much by doing that in SQL as the data returned would be considerably larger as JSON than returned as a data result (or a multi-cursor result).

--- later ---

Ok, looks like they actually added functionality that does let you turn SQL results into JSON using FOR JSON.

Looking at that syntax, I'd say this is not going to be trivial to get the JSON formatted as you may needed for related results.

IAC, there are options - you can use FOR JSON but then you need to make sure you use a driver that works and that also works with the other features (ODBC issues abound unfortunately). Alternately you can just return the data and format it on the Fox end.

+++ Rick ---

Rick, In fact using FOR JSON AUTO or FOR JSON PATH returns json by joining 2 tables and this works very fast. If I need to create same json on Web Connection, I'll need to get all Orders first from SQL Server and then per each order I'll need to get related items so this means 200 or more requests to SQL Server - one per each order. The main issue I'm having so far with getting json from SQL Server is how to extract it, without being truncated. Apart of this, I get the json from SQL Server stored procedure and I'm getting it in output parameter of stored procedure.

-- later --

if JSON is small, I receive it OK - all I need to do is to deserialize it with wwJsonSerializer and then return it from my Rest API method - this automatically serializes it correctly. The main issue I'm having so far is with big json data, which is being truncated and I'm still not able to identify weather SQL Server truncates it or this is done in VFP.

You can run multiple queries in one pass, or create a single denormalized result set. Whatever you do in SQL Server to get the data you should also be able to do in FoxPro (ie. same result set).

I know I've played around with the JSON features (not in Fox but in .NET) and using the results can be a pain because the result isn't a string but a special data type. I guess you can run into a string on the SQL End though...

+++ Rick ---

Rick, I receive a string from Sql Server, which I can deserialize first and then when I return it from Rest API method, it returns correct json. So far the issue I have is I can receive up to 8000 charachers in my output parameter, which I send to sql stored procedure. If I have bigger json, it seems to be truncated to 8000 characters.

After lots of testing, I've finally came to conclusion, that getting json directly from sql server is not a good idea. First of all, I should run stored procedure with output parameter, but result in this output parameter is up to 8000 characters. If I want big json file, I'll need to use many output parameters, to split json into them and then read them in my Rest API method and concatenate them back to whole json. Another issue is sql stored procedure, which is extremely slow if it need to return nested json with many records e.g. >200. So finally I've decided Rick's way to extract data into cursor and then return it as json from Rest API is the best possible way.

Nested json for items included in each order could be loaded on customer action when he need to see them so no need to load huge nested json at once.

If you pull the data into local cursors you can also create nested objects with child collections using CursorToCollection() or CursorToArray() which can serialize the child items.

It requires walking the hiearchy of data you've retrieved but if you retrieve multiple cursors of semi-denormalized data it can be done fairly efficiently on the client using the retrieve cursors.

As to returning your JSON you probably have to find the right way to cast it. You can cast it to TEXT in SQL Server and that should get you the full string.

This is the problem with the aging ODBC drivers and non-support of FoxPro's ODBC handler for certain data types var char sizes. It works with some drivers but not with others. And all of the drivers have some issue with one type or another. it's frustrating as heck, unless you stick to older types (like Text which is semi-unsupported by SQL now).

+++ Rick ---

Thanks Rick, Text is now replaced by varchar(max), however as you say ODBC driver cuts the string to up to 8000 characters. Anyway, I will go with standard way to get data into cursor/collection and return it from my Rest method. I have another issue now - I cannot find how security need to be implemented in Rest API. I've searched through all documents and help files, but cannot find this. How I can use Web Connection to generate some kind of token (JWT or another), which I can return to user and expect in each request to the API to validate user credentials?

Re: Security: You can create an endpoint on your rest service that takes some sort of authentication (username/password or some other user identification) and then return a token on success. You store the token along with an expire time in a table and map it to a user. On every request that requires auth - you check the table for the token, map it to a user and there's your authorization.

For every client request you make to the service you then pass the token - either as part of the URL or as a Bearer Authorization header:

Authorization: Bearer token1234

There are more complicated ways using proper oAuth authentication through a full login UI (ie. what Google or Microsoft do for single signon for example) but that's a lot more complicated.

+++ Rick ---

Rick, I was thinking for a token, which is actually encrypted json, containing user Id, expiration time and other useful information. This way Rest API will not need to store token, but read and decrypt it each time to make sure user is authenticated. I think this approach is more suitable for Rest API as it will only get requests and return response without taking care for users logged etc. Could you please suggest the best way to encrypt json so I can generate the token?

A few ways you can do that, but I'm not so sure that's a good idea because using that approach you can't revoke tokens. Once it's out there it'll be valid...

As to encryption you can create a structure in serialized form and then encrypt the string using two-way encryption. You can use wwEncryption or something else.

Another approach is to create an actual JSON Web Token. For that you can find a library in .NET most likely and call it with wwDotnetBridge. However, I see little benefit for that if you're not using a solution that actually requires explicit JWT's since most of what they contain is protocol related crap. Most oAuth based systems use JWTs which is a semi-standard format that can be read across systems and different software (assuming you have the signing key).

For further discussion on this, please start a new thread with the appropriate topic...

+++ Rick ---

https://support.west-wind.com/Thread5ZF192XLQ.wwt?rl=1#5ZF192XLR