How to correct syntax error created by using escape for single quote (\') in sqlite3

pizzipie · 11-16-2019, 09:21 PM

I am trying to use an INSERT statement in an sqlite3 database that came from MySql.

Code:

INSERT INTO `pdata` (`id`, `First`, `Last`, `Name`, `CatId`, `Sub`, `Location`, `Phone`, `Cell`, `Email`, `Notes`) VALUES
(3, 'Beth', 'Jean\'s Friend', 'Jean\'s Friend, Beth', 6, 0, '', '208.255.8015', '', '', 'Jeans Friend');

The escapes (\') create syntax errors in sqlite3.

How do I get around this in sqlite3? I am trying to

Code:

.read insertpdata.sql

The file insertpdata.sql was created by phpmyadmin.

Thanks for your help in advance.

R

scasey · 11-16-2019, 09:27 PM

Just a guess...use double quotes and don’t escape the apostrophes.

NevemTeve · 11-17-2019, 12:57 AM

Please quote the complete error message you got. (Eg `backticking` fieldnames might be a MySql-specific feature.)
Edit: https://www.sqlite.org/faq.html#q14

bigearsbilly · 11-18-2019, 10:13 AM

Don't need to use quotes, in fact single quotes are strings, only "" will be interpolated.
You do NOT need to backslash the single quotes you can do: "John's"

Code:

sqlite> INSERT INTO "pdata"
   ...>         ("id", "First", "Last", "Name", "CatId", "Sub",
   ...>         "Location", "Phone", "Cell", "Email", "Notes")
   ...> 
   ...>         VALUES (3, "Beth", "Jean's Friend", "Jean's Friend, Beth",
   ...>         6, 0, "", "208.255.8015", "",
   ...>         "", "Jean's Friend");
sqlite> select first, last, name from pdata
   ...> ;
First       Last                  Name                                                                            
----------  --------------------  --------------------------------------------------------------------------------
Beth        Jean's Friend         Jean's Friend, Beth                                                             
sqlite>

I have given up using quotes, I do an awful lot of SQLite..

observe:

Code:

sqlite> select tile_data, "tile_data", 'tile_data'   from tiles limit 1;

  Give your very best today.  Heaven knows it's little enough.
  Give your very best today.  Heaven knows it's little enough.
  tile_data