"Bad SQL syntax" error for a fine SQL statement

Questions and answers regarding general SQL and backend databases

"Bad SQL syntax" error for a fine SQL statement

Postby c.groefsema » Thu May 02, 2019 4:39 pm

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!
User avatar
c.groefsema
 
Posts: 26
Joined: Fri Feb 22, 2019 12:40 pm

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

Postby ROCLASI » Thu May 02, 2019 10:10 pm

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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5385
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

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

Postby c.groefsema » Fri May 03, 2019 9:21 am

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!
User avatar
c.groefsema
 
Posts: 26
Joined: Fri Feb 22, 2019 12:40 pm

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

Postby ROCLASI » Fri May 03, 2019 10:32 am

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?
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5385
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

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

Postby c.groefsema » Fri May 03, 2019 10:45 am

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.
User avatar
c.groefsema
 
Posts: 26
Joined: Fri Feb 22, 2019 12:40 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 6 guests