-1

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.

cj devin
  • 1,045
  • 3
  • 13
  • 48
  • I believe you struggling with json insert so looks like you are using wrong type of single quote, I hope this link can help you https://stackoverflow.com/questions/40930896/how-to-create-and-insert-a-json-object-using-mysql-queries – greencrest Aug 31 '20 at 11:51
  • No,@greencrest...my table column type is varchar...i am storing queries as string inside column not json object as string....for example `insert into table where name ='xyz'` – cj devin Aug 31 '20 at 11:57

1 Answers1

2

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.

laalto
  • 150,114
  • 66
  • 286
  • 303
  • Thanks.after replaced single quatoation with single single quote manually...it's working in sqlite browser...but how to use sqlite variable in java any refrence? – cj devin Aug 31 '20 at 12:10
  • Depends on the libraries you're using. In standard Android SQLiteDatabase, `ContentValues` would be an easy way to do variable binding (but it's not the only one, e.g. `execSQL()` can accept an array of bind arguments). – laalto Aug 31 '20 at 12:14
  • @r08 Depends on how you building the statement and executing it. – Andreas Aug 31 '20 at 12:14
  • i m using `db.execSQL(cmd)` where is cmd as argument passing above mention query as string. – cj devin Aug 31 '20 at 12:31
  • Then you can use `execSQL(String, Object[])` overload. Replace literal values with `?` variables in the SQL and supply a value for each in the array. – laalto Aug 31 '20 at 12:34
  • Okay got it ... i will try it..Thanks. – cj devin Aug 31 '20 at 12:41