Two SQLITE RevBD questions

[Peter Haworth]

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 <james at thehales.id.au> 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 at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>