SQL Statment fails to run with Syntax errors using Flutter/Dart and PostgreSql 12, even though exact same query runs to the same db from python

Hi Flutter/Dart/PostgreSQL noob here;

I have an SQL statement which is running fine from a python/Flask application, I am using some variables within it which are added in at runtime to the statement before it is sent to the db.This is the python version;

latenight_sql = "case when '23:00:00' >={} AND {}<= '04:00:00' then 1 else 0 end as latenight, {} as latenight_checked,".format(dayclose, dayclose, latenight_cb)
    food_avail_sql = "case when LOCALTIME(0) BETWEEN {} AND {} then 1 else 0 end as food_avail, {} as food_avail_checked,".format(restopen,restclose,food_avail_cb)
    livemusic_sql = "case when LOCALTIME(0) BETWEEN {} AND {} then 1 end as livemusic, {} as livemusic_checked,".format(musicbegin,musicfinish,livemusic_cb)
    camra_sql = "case when camra = True then 1 else 0 end as camra, {} as camra_checked".format(camra_cb)

location = geolocator.geocode(inpbx_)
    lonbx = (str(location.longitude))
    latbx = (str(location.latitude))
    miles_= str(miles_)

query = """SELECT name_, address, postcode, latitude, longitude, pubid, monclose,
                 monopen, tueopen, tueclose, wedopen, wedclose, thuropen, thurclose, friopen, friclose, satopen,
                 satclose, sunopen, sunclose,
                 restopenmon, restclosemon, restopentue, restopenwed, restopenthur, restopenfri, restopensat,
                 restopensun, restclosetue, restclosewed, restclosethur, restclosefri, restclosesat, restclosesun,
                 active, musicbeginmon, musicbegintue,latelogo,
                 musicbeginwed, musicbeginthur, musicbeginfri, musicbeginsat, musicbeginsun, musicfinishmon,
                 musicfinishtue, musicfinishwed, musicfinishthur, musicfinishfri, musicfinishsat, musicfinishsun,
                 ROUND(distance::NUMERIC , 2) distance,
                 """+latenight_sql+"""
                 """+food_avail_sql+"""
                 """+livemusic_sql+"""
                 """+camra_sql+"""
                   FROM (
                   SELECT z.name_,
                   z.address,z.tueopen,
                   z.camra, z.monopen,
                   z.postcode, z.monclose,
                   z.satclose, z.pubid,
                   z.latitude, z.longitude,
                   z.tueclose, z.wedopen, z.wedclose,
                   z.thuropen, z.thurclose, z.friopen,
                   z.friclose, z.satopen, z.sunopen, z.sunclose,
                   z.restopenmon, z.restclosemon, z.restopentue, z.restopenwed, z.restopenthur, 
                   z.restopenfri, z.restopensat, z.restopensun, z.restclosetue, z.restclosewed, z.restclosethur, 
                   z.restclosefri, z.restclosesat, z.restclosesun, z.active, z.musicbeginmon, z.musicbegintue,
                   z.musicbeginwed, z.musicbeginthur, z.musicbeginfri, z.musicbeginsat, z.musicbeginsun, z.musicfinishmon,
                   z.musicfinishtue, z.musicfinishwed, z.musicfinishthur, z.musicfinishfri, z.musicfinishsat, 
                   z.musicfinishsun,z.latelogo,
                   p.radius,
                   p.distance_unit
                            * DEGREES(ACOS(LEAST(1.0, COS(RADIANS(p.latpoint))
                            * COS(RADIANS(z.latitude))
                           * COS(RADIANS(p.longpoint - z.longitude))
                           + SIN(RADIANS(p.latpoint))
                           * SIN(RADIANS(z.latitude))))) AS distance
                   FROM pubs AS z 
                   JOIN (SELECT """ + latbx + """ AS latpoint, """ + lonbx + """AS longpoint,
                        100 AS radius,      69 AS distance_unit
                   ) AS p ON 1=1 
                   WHERE z.latitude
                   BETWEEN p.latpoint  - (p.radius / p.distance_unit)
                   AND p.latpoint  + (p.radius / p.distance_unit)
                   AND z.longitude
                   BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
                   AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
                    ) AS d
                   WHERE distance <= radius AND distance <""" + miles_ + """
                   ORDER BY distance """

    conn = psycopg2.connect("dbname='dbname' user='user' password='password' host='overhere' port='5432'")
    cur = conn.cursor()
    cur.execute(query)
    desc = cur.description
    column_names = [col[0] for col in desc]
    dictresult = [dict(zip(column_names, row))
                  for row in cur.fetchall()]
    conn.close()

As I said this works fine apologises if I have not added all the variables, that is a copy and paste issue.

I have changed the SQL code to reflect Dart’s need to have slight changes so the code now looks like this;

    """SELECT name_, address, postcode, latitude, longitude, pubid, monclose,
     monopen, tueopen, tueclose, wedopen, wedclose, thuropen, thurclose, friopen, friclose, satopen,
     satclose, sunopen, sunclose,
     restopenmon, restclosemon, restopentue, restopenwed, restopenthur, restopenfri, restopensat,
     restopensun, restclosetue, restclosewed, restclosethur, restclosefri, restclosesat, restclosesun,
     active, musicbeginmon, musicbegintue,latelogo,
     musicbeginwed, musicbeginthur, musicbeginfri, musicbeginsat, musicbeginsun, musicfinishmon,
     musicfinishtue, musicfinishwed, musicfinishthur, musicfinishfri, musicfinishsat, musicfinishsun,
     ROUND(distance::NUMERIC , 2) distance, ${global.latenight_sql},
                 ${global.food_avail_sql},
                 ${global.livemusic_sql},
                 ${global.camra_sql},
     
       FROM (
       SELECT z.name_,
       z.address,z.tueopen,
       z.camra, z.monopen,
       z.postcode, z.monclose,
       z.satclose, z.pubid,
       z.latitude, z.longitude,
       z.tueclose, z.wedopen, z.wedclose,
       z.thuropen, z.thurclose, z.friopen,
       z.friclose, z.satopen, z.sunopen, z.sunclose,
       z.restopenmon, z.restclosemon, z.restopentue, z.restopenwed, z.restopenthur,
       z.restopenfri, z.restopensat, z.restopensun, z.restclosetue, z.restclosewed, z.restclosethur,
       z.restclosefri, z.restclosesat, z.restclosesun, z.active, z.musicbeginmon, z.musicbegintue,
       z.musicbeginwed, z.musicbeginthur, z.musicbeginfri, z.musicbeginsat, z.musicbeginsun, z.musicfinishmon,
       z.musicfinishtue, z.musicfinishwed, z.musicfinishthur, z.musicfinishfri, z.musicfinishsat,
       z.musicfinishsun,z.latelogo,
       p.radius,
       p.distance_unit
                * DEGREES(ACOS(LEAST(1.0, COS(RADIANS(p.latpoint))
                * COS(RADIANS(z.latitude))
               * COS(RADIANS(p.longpoint - z.longitude))
               + SIN(RADIANS(p.latpoint))
               * SIN(RADIANS(z.latitude))))) AS distance
       FROM pubs AS z
       JOIN (SELECT ${global.latitude} AS latpoint, ${global.longitude} AS longpoint,
            100 AS radius,      69 AS distance_unit
       ) AS p ON 1=1
       WHERE z.latitude
       BETWEEN p.latpoint  - (p.radius / p.distance_unit)
       AND p.latpoint  + (p.radius / p.distance_unit)
       AND z.longitude
       BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
       AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
        ) AS d
       WHERE distance <= radius AND distance <$miles 
       ORDER BY distance """;

  var uri = 'postgres://user:password@overhere:5432/db';
  var sql = sqlquery;
  print(sql);
  await connect(uri).then((conn) {
    conn.query(sql).toList()
        .then((result) {
      print('result: $result');
    })
        .whenComplete(() {
      conn.close();
    });
  });
}

the variable miles =2 as testing and keeping it stable and is a local variable for now.

I am getting the error :[ERROR:flutter/lib/ui/ui_dart_state.cc(186)] Unhandled Exception: ERROR 42601 syntax error at or near “{“. I am printing the created SQL statement and it is odd as it seems to stop half way through the process; I get this:

                        SELECT name_, address, postcode, latitude, longitude, pubid, monclose,
I/flutter (14357):      monopen, tueopen, tueclose, wedopen, wedclose, thuropen, thurclose, friopen, friclose, satopen,
I/flutter (14357):      satclose, sunopen, sunclose,
I/flutter (14357):      restopenmon, restclosemon, restopentue, restopenwed, restopenthur, restopenfri, restopensat,
I/flutter (14357):      restopensun, restclosetue, restclosewed, restclosethur, restclosefri, restclosesat, restclosesun,
I/flutter (14357):      active, musicbeginmon, musicbegintue,latelogo,
I/flutter (14357):      musicbeginwed, musicbeginthur, musicbeginfri, musicbeginsat, musicbeginsun, musicfinishmon,
I/flutter (14357):      musicfinishtue, musicfinishwed, musicfinishthur, musicfinishfri, musicfinishsat, musicfinishsun,
I/flutter (14357):      ROUND(distance::NUMERIC , 2) distance, case when '23:00:00' >={} AND {}<= '04:00:00' then 1 else 0 end as latenight, {} as latenight_checked,.format(dayclose, dayclose, latenight_cb),
I/flutter (14357):                  case when LOCALTIME(0) BETWEEN {} AND {} then 1 else 0 end as food_avail, {} as food_avail_checked,.format(restopen,restclose,food_avail_cb),
I/flutter (14357):                  
E/flutter (14357): [ERROR:flutter/lib/ui/ui_dart_state.cc(186)] Unhandled Exception: ERROR 42601 syntax error at or near "{"
    E/flutter (14357): 
    Application finished.

I cant see the issue, please point me in the right direction.

I am running PostgreSQL 12, Flutter version 2.0.2 at C:flutter • Framework revision 8962f6dc68 (4 days ago), 2021-03-11 13:22:20 -0800 • Engine revision 5d8bf811b3 • Dart version 2.12.1

Side note just before posting: I have taken the this statement out and done the usual ‘select * from db’ and query particular columns for data and this all worked fine. Sorry another side note: I know the database is a right mess, it will be rebuilt and changed before use it is literally a dumping server while I figure out what I fully need for my app.

Answer

I believe issue is that you are using “{}” as parameter placeholders in your query (see this part “case when LOCALTIME(0) BETWEEN {} AND {} then” as example). You should either inline them with values (like you are doing with $miles for example) or better pass them as variables @varName and then pass their values along with query, like this:

conn.query(sql, substitutionValues: {"varName": "varValue"});