英文:
SQLite: near "false": syntax error...how to solve single quoate throwing error
问题
我正在使用capacitor-community sqlite在自定义构建的Ionic Android插件中，执行execute查询时抛出错误，错误消息为near "false": 语法错误，其中表列类型为varchar。
以下是查询：
insert or replace into FormSchema (SchemaId,SchemaName,Schema,Parent,Application,ModifiedDateTime,Version,PageType,favorite,CategoryType,IsDeleted) values('cc313544-cbcf-4b63-8e79-2181f5d6a3b3','AddPopup','{...}','null','Construction','2020-08-31T03:58:54.3466667','65','favorites','false','','false');
同时在SQLite数据库浏览器中也出现了相同的错误。
DB Browser for SQLite错误消息：
执行完成并出现错误。
结果：near "false": 语法错误
在第1行：
insert or replace into FormSchema (SchemaId,SchemaName,Schema,Parent,Application,ModifiedDateTime,Version,PageType,favorite,CategoryType,IsDeleted) values('cc313544-cbcf-4b63-8e79-2181f5d6a3b3','AddPopup','{...}','null','Construction','2020-08-31T03:58:54.3466667','65','favorites','false','','false');
即使我已经尝试添加了单斜杠和双斜杠，例如.... where (favorites.favorite= \'false\`` 和 .... where (favorites.favorite= \'false\``，但仍然没有解决问题。
请问我需要在查询中进行哪些修改？如何解决上述错误？
英文:
I am using capacitor-community sqlite in custom builded ionic andorid plugin and
while doing execute query throwing an error like near "false": syntax error where table column type
is varchar.
here is the query:
insert or replace into FormSchema (SchemaId,SchemaName,Schema,Parent,Application,ModifiedDateTime,Version,PageType,favorite,CategoryType,IsDeleted) values('cc313544-cbcf-4b63-8e79-2181f5d6a3b3','AddPopup','{"schema":{"type":"object","properties":{"favoritespanel":{"virtualKey":true,"roles":[],"type":"object","buttonEvent":"route","properties":{"favorites":{"schemaRoute":"favorites.name","addable":false,"deleteable":false,"roles":[],"query":"Favorites","type":"array","buttonEvent":"route","items":{"schemaRoute":"favorites.name","query":"Favorites","type":"object","buttonEvent":"route","properties":{"parent":{"actualName":"Parent","type":"string"},"entitytimezone":{"actualName":"EntityTimeZone","hide":true,"format":"date-time","placeholder":"EntityTimeZone","title":"EntityTimeZone","type":"string","required":false},"level":{"type":"number"},"entityid":{"actualName":"EntityID","hide":true,"placeholder":"EntityID","title":"EntityID","type":"string","required":false},"uuid":{"actualName":"UUID","type":"string"},"treeStatus":{"default":"collapsed","type":"string"},"loaded":{"type":"string"},"createdby":{"actualName":"CreatedBy","hide":true,"placeholder":"CreatedBy","title":"CreatedBy","type":"string","required":false},"modifieddatetime":{"actualName":"ModifiedDateTime","hide":true,"format":"date-time","placeholder":"ModifiedDateTime","title":"ModifiedDateTime","type":"string","required":false},"name":{"actualName":"name","hide":false,"readonly":false,"type":"string","required":false},"modifiedby":{"actualName":"ModifiedBy","hide":true,"placeholder":"ModifiedBy","title":"ModifiedBy","type":"string","required":false},"createddatetime":{"actualName":"CreatedDateTime","hide":true,"format":"date-time","placeholder":"CreatedDateTime","title":"CreatedDateTime","type":"string","required":false},"favorite":{"actualName":"favorite","view":"favorite","hide":false,"readonly":false,"type":"boolean","buttonEvent":"favorite","routeOnSelect":true,"required":false}},"routeOnSelect":true,"primary":false,"tableName":"favorites"},"routeOnSelect":true,"primary":false,"tableName":"favorites"}}},"availablereportspanel":{"virtualKey":true,"roles":[],"type":"object","buttonEvent":"route","properties":{"availablereports":{"schemaRoute":"availablereports.name","addable":false,"deleteable":false,"roles":[],"query":"AvailableReports","type":"array","buttonEvent":"route","items":{"schemaRoute":"availablereports.name","query":"AvailableReports","type":"object","buttonEvent":"route","properties":{"parent":{"actualName":"Parent","type":"string"},"entitytimezone":{"actualName":"EntityTimeZone","hide":true,"format":"date-time","placeholder":"EntityTimeZone","title":"EntityTimeZone","type":"string","required":false},"level":{"type":"number"},"entityid":{"actualName":"EntityID","hide":true,"placeholder":"EntityID","title":"EntityID","type":"string","required":false},"uuid":{"actualName":"UUID","type":"string"},"treeStatus":{"default":"collapsed","type":"string"},"loaded":{"type":"string"},"createdby":{"actualName":"CreatedBy","hide":true,"placeholder":"CreatedBy","title":"CreatedBy","type":"string","required":false},"modifieddatetime":{"actualName":"ModifiedDateTime","hide":true,"format":"date-time","placeholder":"ModifiedDateTime","title":"ModifiedDateTime","type":"string","required":false},"name":{"actualName":"name","hide":false,"readonly":false,"type":"string","required":false},"modifiedby":{"actualName":"ModifiedBy","hide":true,"placeholder":"ModifiedBy","title":"ModifiedBy","type":"string","required":false},"createddatetime":{"actualName":"CreatedDateTime","hide":true,"format":"date-time","placeholder":"CreatedDateTime","title":"CreatedDateTime","type":"string","required":false},"favorite":{"actualName":"favorite","view":"favorite","hide":false,"readonly":false,"type":"boolean","buttonEvent":"favorite","routeOnSelect":true,"required":false}},"routeOnSelect":true,"primary":false,"tableName":"favorites"},"routeOnSelect":true,"primary":false,"tableName":"favorites"}}}}},"app":"Construction","unitsOfMeasurement":"","queries":[{"sheetName":"AvailableReports","query":"select FormSchema.schemaid as entityid, FormSchema.schemaid as uuid, favorites.userid as userid, favorites.favorite as favorite, FormSchema.schemaname as name FROM FormSchema LEFT JOIN favorites ON FormSchema.schemaid = favorites.entityid where (favorites.favorite='false' OR favorites.favorite is null) and (FormSchema.isDeleted='false' or FormSchema.isDeleted='0' )and FormSchema.pagetype='param2' and FormSchema.categorytype='param1'","property":"AvailableReports"},{"sheetName":"Favorites","query":"select schemaname as name, schemaid as entityid, favorite as favorite from formschema where favorite='true' and isDeleted='false' and pagetype='param2' and categorytype='param1'","property":"Favorites"},{"sheetName":"Panel","query":"select pageType as entityname, schemaid as entityid, favorite as favorite,schemaname as name,categoryType as categoryType from formschema where (isDeleted='false' or isDeleted='0') group by entityname","property":"DropdownQueryWithoutParams"},{"sheetName":"Panel","query":"select pageType as entityname, schemaid as entityid, favorite as favorite,schemaname as name,categoryType as categoryType from formschema where categoryType='param1' and (isDeleted='false' or isDeleted ='0') group by entityname","property":"DropdownQuery","queryParameter":"param1=favorites.categorytype"}],"popupHeader":{"properties":{"formType":"favorites","dropdownValue":"param2=entityname;param1=categoryType","width":"500px","save":false,"refresh":"favorites,availablereports","dropdownQueryWithoutParams":"DropdownQueryWithoutParams","title":"Category","close":true,"dropdown":true,"dropdownQuery":"DropdownQuery","height":"700px"}},"tableName":"Formschema","layout":[{"fxLayoutAlign":"flex-start","fxLayoutWrap":"wrap","justify-content":"space-between","type":"flex","items":[{"fxFlex":"0 0 calc(100%)","expanded":false,"type":"expansion-panel","title":"Favorites","collapsible":true,"items":[{"fxLayoutWrap":"wrap","fxLayoutGap":"10px","type":"flex","items":[{"listItems":0,"headerHeight":0,"type":"table","items":[{"width":"350","type":"string","key":"favoritespanel.favorites[].name"},{"width":"50","type":"favorite","key":"favoritespanel.favorites[].favorite"}],"key":"favoritespanel.favorites","rowHeight":50,"height":"500px"}],"fxLayout":"row"}],"key":"favoritespanel"},{"fxFlex":"0 0 calc(100%)","expanded":true,"type":"expansion-panel","title":"Available Items","collapsible":true,"items":[{"fxLayoutWrap":"wrap","fxLayoutGap":"10px","type":"flex","items":[{"listItems":0,"headerHeight":0,"type":"table","items":[{"width":"350","type":"string","key":"availablereportspanel.availablereports[].name"},{"width":"50","type":"favorite","key":"availablereportspanel.availablereports[].favorite"}],"key":"availablereportspanel.availablereports","rowHeight":50,"height":"500px"}],"fxLayout":"row"}],"key":"availablereportspanel"}],"fxLayout":"row"}],"categoryType":"","parentPage":"","TableName":"AddPopup","pageType":"favorites","$state":"","Version":"65","options":{"submit":false},"readDataFromOtherSource":{"favorites":{"fieldMapping":{}},"availablereports":{"fieldMapping":{}}},"page":"AddPopup","tenant":"All","breadcrumbDisplayName":""}','null','Construction','2020-08-31T03:58:54.3466667','65','favorites','false','','false');
And also same in sqlite db browser
DB Browser for SQLite error msg:
Execution finished with errors.
Result: near "false": syntax error
At line 1:
insert or replace into FormSchema (SchemaId,SchemaName,Schema,Parent,Application,ModifiedDateTime,Version,PageType,favorite,CategoryType,IsDeleted) values('cc313544-cbcf-4b63-8e79-2181f5d6a3b3','AddPopup','{"schema":{"type":"object","properties":{"favoritespanel":{"virtualKey":true,"roles":[],"type":"object","buttonEvent":"route","properties":{"favorites":{"schemaRoute":"favorites.name","addable":false,"deleteable":false,"roles":[],"query":"Favorites","type":"array","buttonEvent":"route","items":{"schemaRoute":"favorites.name","query":"Favorites","type":"object","buttonEvent":"route","properties":{"parent":{"actualName":"Parent","type":"string"},"entitytimezone":{"actualName":"EntityTimeZone","hide":true,"format":"date-time","placeholder":"EntityTimeZone","title":"EntityTimeZone","type":"string","required":false},"level":{"type":"number"},"entityid":{"actualName":"EntityID","hide":true,"placeholder":"EntityID","title":"EntityID","type":"string","required":false},"uuid":{"actualName":"UUID","type":"string"},"treeStatus":{"default":"collapsed","type":"string"},"loaded":{"type":"string"},"createdby":{"actualName":"CreatedBy","hide":true,"placeholder":"CreatedBy","title":"CreatedBy","type":"string","required":false},"modifieddatetime":{"actualName":"ModifiedDateTime","hide":true,"format":"date-time","placeholder":"ModifiedDateTime","title":"ModifiedDateTime","type":"string","required":false},"name":{"actualName":"name","hide":false,"readonly":false,"type":"string","required":false},"modifiedby":{"actualName":"ModifiedBy","hide":true,"placeholder":"ModifiedBy","title":"ModifiedBy","type":"string","required":false},"createddatetime":{"actualName":"CreatedDateTime","hide":true,"format":"date-time","placeholder":"CreatedDateTime","title":"CreatedDateTime","type":"string","required":false},"favorite":{"actualName":"favorite","view":"favorite","hide":false,"readonly":false,"type":"boolean","buttonEvent":"favorite","routeOnSelect":true,"required":false}},"routeOnSelect":true,"primary":false,"tableName":"favorites"},"routeOnSelect":true,"primary":false,"tableName":"favorites"}}},"availablereportspanel":{"virtualKey":true,"roles":[],"type":"object","buttonEvent":"route","properties":{"availablereports":{"schemaRoute":"availablereports.name","addable":false,"deleteable":false,"roles":[],"query":"AvailableReports","type":"array","buttonEvent":"route","items":{"schemaRoute":"availablereports.name","query":"AvailableReports","type":"object","buttonEvent":"route","properties":{"parent":{"actualName":"Parent","type":"string"},"entitytimezone":{"actualName":"EntityTimeZone","hide":true,"format":"date-time","placeholder":"EntityTimeZone","title":"EntityTimeZone","type":"string","required":false},"level":{"type":"number"},"entityid":{"actualName":"EntityID","hide":true,"placeholder":"EntityID","title":"EntityID","type":"string","required":false},"uuid":{"actualName":"UUID","type":"string"},"treeStatus":{"default":"collapsed","type":"string"},"loaded":{"type":"string"},"createdby":{"actualName":"CreatedBy","hide":true,"placeholder":"CreatedBy","title":"CreatedBy","type":"string","required":false},"modifieddatetime":{"actualName":"ModifiedDateTime","hide":true,"format":"date-time","placeholder":"ModifiedDateTime","title":"ModifiedDateTime","type":"string","required":false},"name":{"actualName":"name","hide":false,"readonly":false,"type":"string","required":false},"modifiedby":{"actualName":"ModifiedBy","hide":true,"placeholder":"ModifiedBy","title":"ModifiedBy","type":"string","required":false},"createddatetime":{"actualName":"CreatedDateTime","hide":true,"format":"date-time","placeholder":"CreatedDateTime","title":"CreatedDateTime","type":"string","required":false},"favorite":{"actualName":"favorite","view":"favorite","hide":false,"readonly":false,"type":"boolean","buttonEvent":"favorite","routeOnSelect":true,"required":false}},"routeOnSelect":true,"primary":false,"tableName":"favorites"},"routeOnSelect":true,"primary":false,"tableName":"favorites"}}}}},"app":"Construction","unitsOfMeasurement":"","queries":[{"sheetName":"AvailableReports","query":"select FormSchema.schemaid as entityid, FormSchema.schemaid as uuid, favorites.userid as userid, favorites.favorite as favorite, FormSchema.schemaname as name FROM FormSchema LEFT JOIN favorites ON FormSchema.schemaid = favorites.entityid where (favorites.favorite='false
Even i have added single and double slash like .... where (favorites.favorite= \'false\ and .... where (favorites.favorite= \\'false\\ but it is not working.
what modification i need to do in query .how to solve above error?
Thanks.
答案1
得分: 2
看起来 'false' 是字符串字面量的一部分，或者至少第一个 ' 终止了字符串字面量。
在字符串字面量内使用 '' 来转义单引号，例如 ''false''。
更好的方法是使用 SQL 变量并绑定字面量值，这样就不需要处理字符串转义。
英文:
Looks like the 'false' is part of a string literal, or at least the first ' terminates a string literal.
Use '' to escape a single quote inside a string literal, e.g ''false''.
Even better, use sql variables to and bind the literal value so you don't have to deal with string escaping.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论