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.
Advertisement
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"});