How to handle UTF-8 characters in sqlite2 to sqlite3 migration

Question

Trying the easy approach:

sqlite2 mydb.db .dump | sqlite3 mydb-new.db

I got this error:

SQL error near line 84802: no such column: Ð

In that line the script is this:

INSERT INTO vehiculo VALUES(127548,'21K0065217',Ñ,'PA007808',65217,279,1989,3,468,'1998-07-30 00:00:00.000000','14/697/98-07',2,'',1);

My guess is that the 'Ñ' without quotes is the problem.

any idea?

PD: I'm under Windows right now and I would like to use the command-line so it can be automatized (this process will be done on daily basis by a server).

Answer 1

Simply open the v2 database with the sqlite3 binary CLI, and then save it. The database file will be transparently migrated to v3.

$ sqlite3 v2database.db
sqlite> .quit
$

Note: you may need to insert/delete a row before quitting to force an update.

Answer 2

Simply open the v2 database with the sqlite3 binary CLI, and then save it. The database file will be transparently migrated to v3.

It doesn't work.

$sqlite3 db2
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
Error: file is encrypted or is not a database
sqlite> .q

And the file is not changed (apparently sqlite3 couldn't read it). I think the original problem is a bug in sqlite2.

Answer 3

Well nobody answer... at the end I end up modifying my original script(the one that created the sqlite2 database in the first place) to create the database directly in sqlite3.

I think that a big string processing script(big because mi databases are 800mb and 200mb each) can do the job, but generating the database directly was easier for me.

Answer 4

I tried to do it without windows intervention:

*by calling sqlite2 on old.db, and send the dump directly to a file

*and then call sqlite3 on new.db and loading the dump directly from the file.

Just in case windows was messing with the characters on the command-line.

Same Result.