Two SQLITE RevBD questions
I am moving my app from LC6 to LC7 (in prep for LC8) and have a couple of questions regarding some changes I have made in the way I use SQLITE. 1. Escaping quotes when using binding variables Originally I was doing the standard INSERT into the DB where I constructed my SQL and then used "revExecuteSQL" put "INSERT INTO content "& sq_cols & sq_values into tsql revExecuteSQL db_conn_id,tsql Where sq_cols was a string of the columns in the content table and sq_vals was a concatenated string of the actual values (each enclosed in quotes). I also escaped any quotes within these values. I decided to change this to the binding form where I now placed the values into an array and simply bound them to the column names as in... put "INSERT INTO content "&sq_cols&" VALUES(:1,:2,:3,:4,:5,:6)" into tsql revExecuteSQL db_conn_id,tsql,"varray" Where "varray" is an array with 6 values. My question is: Do I need to escape quotes in these values? 2. All text data us UTF8 encoded having been read in from their original source via "textDecode(variable,"UTF8") (LC7 version) The values I store and read into my db will only be read by my app. Do I need to encode them before storing and then decode them on reading given my app is the only access? James ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Two SQLITE RevBD questions
Hi James, No need to escape quotes when using bound variables. You'll still need to encode your date before inserting it and decode it when selecting it or you'll lose its utf8-ness. On Sat, Dec 26, 2015, 6:44 AM James Hale wrote: > I am moving my app from LC6 to LC7 (in prep for LC8) and have a couple of > questions regarding some changes I have made in the way I use SQLITE. > > 1. Escaping quotes when using binding variables > > Originally I was doing the standard INSERT into the DB where I constructed > my SQL and then used "revExecuteSQL" >put "INSERT INTO content "& sq_cols & sq_values into tsql >revExecuteSQL db_conn_id,tsql > > Where sq_cols was a string of the columns in the content table and sq_vals > was a concatenated string of the actual values (each enclosed in quotes). > I also escaped any quotes within these values. > > I decided to change this to the binding form where I now placed the values > into an array and simply bound them to the column names as in... > > put "INSERT INTO content "&sq_cols&" VALUES(:1,:2,:3,:4,:5,:6)" into > tsql > revExecuteSQL db_conn_id,tsql,"varray" > > Where "varray" is an array with 6 values. > > My question is: Do I need to escape quotes in these values? > > 2. All text data us UTF8 encoded having been read in from their original > source via "textDecode(variable,"UTF8") (LC7 version) > The values I store and read into my db will only be read by my app. > Do I need to encode them before storing and then decode them on reading > given my app is the only access? > > > James > > > > > > ___ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Two SQLITE RevBD questions
Thanks Peter, I was hoping that was the case. James -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/Two-SQLITE-RevBD-questions-tp4699831p4699834.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode