How to convert sqlite2 to sqlite3 and what are the differences between both versions?

Question

I need to convert from sqlite2 db to sqlite3, is there any tutorial that shows how to do it? And if I migrate correctly what to expect as I start the project?

you should add your own answer and then accept it, instead of an edit. there is no problem :) — Ovidiu Latcu, Feb 24 '12 at 14:46

score 19 · Accepted Answer · edited May 19 '14 at 13:44

19

The SQLite website says:

sqlite OLD.DB .dump | sqlite3 NEW.DB

where sqlite is version 2 and sqlite3 is version 3.

edited May 19 '14 at 13:44

user1251007

15,891
14
50
76

answered Mar 29 '12 at 18:53

WrednaMenda

206
1
3

When the sqlite2 Database contains large numeric strings these strings are converted into floats which probably slice the data – Marco Kinski Feb 22 '18 at 10:49
Works in the opposite direction as well. `sqlite3 OLD.DB .dump | sqlite NEW.DB` – Bálint Sass Feb 02 '23 at 21:06

score 5 · Answer 2 · answered Jun 14 '14 at 11:11

Converting an SQLite2 database to an SQLite3 database on Windows

-- Step-by-step instructions --

1- Download SQLite2 and SQLite3 command-line tools

Download links:

sqlite.exe http://web.archive.org/web/20031203050807/http://sqlite.org/download.html (Precompiled Binaries For Windows)

sqlite3.exe http://www.sqlite.org/download.html (Precompiled Binaries for Windows)

2- Copy sqlite.exe, sqlite3.exe and the db to convert to the same folder (i.e.: D:\TEMP)

3- Open a Windows command prompt ( [WINDOWS KEY] + [R], then type "cmd" or via the Start Menu)

4- Browse to the folder where you've just copied the files (i.e.: type "D:", then "cd temp") 5- Type "sqlite OLD.DB .dump | sqlite3 NEW.DB" (without the quotes), where OLD.DB is the db file you want to convert and NEW.DB is the name of the converted file that will be created.

Hope this helps.

score 2 · Answer 3 · edited May 23 '17 at 12:17

2

EDITED: ANSWER TO THE QUESTION

To the conversion

Go to http://www.sqlite.org/download.html
Download sqlite-dll file (which stays in Precompiled Binaries For Windows)
Unpack it and copy to C:\Windows\System32 folder

What are the differences? (quoted from PHP - SQLite vs SQLite3)

SQLite2 internally stores every value as a string, regardless of its type.
Upgrading to SQLite3 will certainly shrink the database size since numbers and BLOBS get stored in their native formats, which could make things run faster.
starting from 3.6.23 version it supports foreign keys.

edited May 23 '17 at 12:17

Community

1
1

answered Feb 24 '12 at 14:47

Mustafa Güven

15,526
11
63
83

1

to anyone finding this thread to solve this problem still, note that this answer doesn't actually answer anything, with http://stackoverflow.com/a/9931615/740553 having the right answer. – Mike 'Pomax' Kamermans Apr 21 '16 at 17:36

How to convert sqlite2 to sqlite3 and what are the differences between both versions?

3 Answers3

Linked