Page 1 of 1

"Bad SQL syntax" error for a fine SQL statement

PostPosted: Thu May 02, 2019 4:39 pm
by c.groefsema
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:

Code: Select all
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!

Re: "Bad SQL syntax" error for a fine SQL statement

PostPosted: Thu May 02, 2019 10:10 pm
by ROCLASI
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.

Re: "Bad SQL syntax" error for a fine SQL statement

PostPosted: Fri May 03, 2019 9:21 am
by c.groefsema
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:

Code: Select all
    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:
Code: Select all
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!

Re: "Bad SQL syntax" error for a fine SQL statement

PostPosted: Fri May 03, 2019 10:32 am
by ROCLASI
Hi,
c.groefsema wrote:But when I paste the value of 'query' in the interactive console, I discover an escape slash in front of the quotes:
Code: Select all
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?

Re: "Bad SQL syntax" error for a fine SQL statement

PostPosted: Fri May 03, 2019 10:45 am
by c.groefsema
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.