MsSQL - JSON Result Question

1

I’m pretty comfortable with using Clarion and SQL.

However, I was thinking about using FOR JSON PATH in {PROP:SQL} to get a JSON result. So far so good.

Question is, how do I parse the result on the Clarion side? (If possible.)

Thanks!

2

Without actually testing.
I’d say you need either

  1. dummy table with a large cstring column or a Blob
  2. use some form of SQL direct ODBC class, like UltimateSQL.

I don’t think you’ll be able to use a dictionary table that matches the sql table’s actual definition.

3

We’re on the same sheet of music.

I almost always use a dummy file.

Here’s an error I received:

Error The FOR JSON clause is not allowed in a CURSOR statement.Error on Prop:SQL

This Query works in SQL Management Studio:

SELECT ROW_NUMBER() OVER(ORDER BY YEAR(ArrestDate) ASC) AS Point,
Year = YEAR(ArrestDate),
Count = count(* )
FROM SS_ALRecords_Incident.dbo.tblIncidentArrest AS p
JOIN SS_ALRecords_Incident.dbo.tblIncidentArrestOffense AS v
ON (p.PrimaryKey = v.ForeignKey) WHERE Year(ArrestDate) Between ‘2017’ and ‘2022’ and (OffenseType = ‘M’ or OffenseType = ‘F’) and ArrestType <> ‘3’ AND WarrantNumber IS NULL
GROUP BY Year(ArrestDate)
ORDER BY Year
FOR JSON PATH;

Result:

[
{
“Point”:1,
“Year”:2017,
“Count”:968
},
{
“Point”:2,
“Year”:2018,
“Count”:982
},
{
“Point”:3,
“Year”:2019,
“Count”:861
},
{
“Point”:4,
“Year”:2020,
“Count”:992
},
{
“Point”:5,
“Year”:2021,
“Count”:746
},
{
“Point”:6,
“Year”:2022,
“Count”:5
}
]

4

Wrap up the query into a VIEW on server side, and do SELECT from that VIEW on Clarion side.

1 Like
5

Ah….very interesting!!

Thanks Mike!!!

6

Read the docs on MsSQL views. Very cool!

Great suggestion Mike!

So basically, if I understand it correctly, I construct the VIEW to return one row as JSON and read that row on the Clarion side as normal.

1 Like
7

Just for testing, have you tried Prop:SQLRowSet?

8

Yes sir. I have.

Got the same error.

9

Hi Donald, alternatively you could try to solve that specific error.

As it mentions problem with cursors, you could try the technique to not use cursors, like setting a dummy file to open on Read Only mode (on your app Global Properties / Actions / Individual File Overrides / File Open Mode Other / Read Only / Deny None).

It was discussed on Clarion11 Newsgroup on 2020-08-25 thread “MSSQL Driver Cursors” by Glenn Rose

Federico

1 Like