"Bad SQL syntax" error for a fine SQL statement

Classic Servoy
1

Hi all,

I have this query, giving me the names of gynaecologists in a hospital plus the number of natural births (i.e. no c-section) they were involved in:

SELECT 
	caregiver.Name AS Caregiver,

	(SELECT 
		COUNT(DISTINCT qnnaire1.PregnancyId)
		FROM BmaCare.Questionnaire qnnaire1
		LEFT JOIN BmaCare.Questionnaire qnnaire2 ON qnnaire1.PregnancyId = qnnaire2.PregnancyId

		CROSS APPLY OPENJSON(qnnaire1.Data, '$.actions')
		WITH (
			entries nvarchar(max) '$.entries' AS JSON
		) json1

		CROSS APPLY OPENJSON(json1.entries) 
		WITH (
			[key] nvarchar(100) '$.key',
			[value] nvarchar(100) '$.value'
		) birthtimedata

		CROSS APPLY OPENJSON(json1.entries) 
		WITH (
			[key] nvarchar(100) '$.key',
			[value] nvarchar(100) '$.value'
		) birthtypedata
		
		CROSS APPLY OPENJSON(qnnaire2.Data, '$.actions')
		WITH (
			entries nvarchar(max) '$.entries' AS JSON
		) json2
		CROSS APPLY OPENJSON(json2.entries) 
		WITH (
			[key] nvarchar(100) '$.key',
			[value] nvarchar(100) '$.value'
		) gynedata

		WHERE
			birthtimedata.[key] = 'Child.BirthTime' AND
			birthtimedata.[value] is not null AND
			birthtypedata.[key] = 'Child.BirthType' AND
			birthtypedata.[value] = 'Natural' AND
			qnnaire2.Name = 'Intake' AND
			gynedata.[key] = 'DeliveryCaregivers.Gynaecologist' AND
			gynedata.[value] = caregiver.Id
	) AS [Total natural births]

FROM BmaCare.Caregiver careGiver

WHERE careGiver.Type = 'Gynaecologist'

When I execute this query in SQL Server Management Studio 2017, I get the desired result. However, I need to do something with this result in Servoy, and I use databaseManager.getDataSetByQuery() to retrieve the same records. Which I do not get: Servoy gives me a “Bad SQL syntax” error instead. Is there anything else I need to take into consideration here so that the query executes fine? And if not: are there any coding alternatives to databaseManager.getDataSetByQuery() that do come up with the correct records here?

Thanks!

2

Hi,

I am not sure but I have a suspicion that the syntax has something todo with this error.
Can you try replacing the square brackets with double quotes?
So [key] becomes “key”, etc. .

Double quotes are a (SQL) standard way of quoting database objects. Like column names that are reserved words, mixed case or have spaces (like in your query).

Hope this helps.

3

Hi Robert,

Thanks for your contribution!
Double quotes are used to surround the string that contains the SQL, so in order to have them in the SQL itself, I need to add a variable that contains double quotes, and use that instead.
Like so:

    var q = String.fromCharCode(34); // "
    var query = "";
    query += "USE BmaOne ";
    query += "SELECT ";
    query += "caregiver.Name AS Caregiver, ";

    query += "(SELECT ";
    query += "        COUNT(DISTINCT qnnaire1.PregnancyId) ";
    query += "        FROM BmaCare.Questionnaire qnnaire1 ";
    query += "        LEFT JOIN BmaCare.Questionnaire qnnaire2 ON qnnaire1.PregnancyId = qnnaire2.PregnancyId ";

    query += "        CROSS APPLY OPENJSON(qnnaire1.Data, '$.actions') ";
    query += "        WITH ( ";
    query += "            entries nvarchar(max) '$.entries' AS JSON ";
    query += "        ) json1 ";

    query += "        CROSS APPLY OPENJSON(json1.entries)  ";
    query += "        WITH ( ";
    query += "            " + q + "key" + q + " nvarchar(100) '$.key', ";

But when I paste the value of ‘query’ in the interactive console, I discover an escape slash in front of the quotes:

WITH ( \"key\" nvarchar(100) '$.key', \"value\" nvarchar(100) '$.value'

which will obviously give me a “Bad SQL syntax” error.
Any suggestions on this?

Thanks once more!

4

Hi,

c.groefsema:
But when I paste the value of ‘query’ in the interactive console, I discover an escape slash in front of the quotes:

WITH ( \"key\" nvarchar(100) '$.key', \"value\" nvarchar(100) '$.value'

which will obviously give me a “Bad SQL syntax” error.

If you paste the string in the interactive console you get indeed the backslashes. But in getDataSetByQuery() it should work fine. Did you try that yet?

5

Hi Robert,
Yes, I did try it. But it made no difference.
And you are right: q.length appeared to be 1. So the backslash indeed only turns up in the interactive console.