17

Sqlite is kinda frustrating. Each time I run a command, I'm not able to use the up down left right arrows to retrieve my previously typed commands. Is there any way to enable this?

Another question: I have the following table

CREATE TABLE resource (
    resourceID INTEGER PRIMARY KEY AUTOINCREMENT,
    resourceType STRING,
    userID INTEGER DEFAULT -1
);

and I insert as follows:

insert into resource values(null, "razor");

but its not allowing me to do so because I have only inserted into 2 columns and not specified anything for the userID column. But I thought the point of DEFAULT was to default the values to -1 if nothing was inserted. Am I missing something here?

Improve this question
0

6 Answers 6

Reset to default
32

A quick solution is to launch SQLite with ReadLine support like following:

rlwrap sqlite3 database.db

If rlwrap is not installed, you can do so on Ubuntu using the command:

sudo apt-get install rlwrap

For other Linux versions, check here.

rlwrap is a ReadLine wrapper, a small utility that uses the GNU readline library to allow the editing of keyboard input for any other command.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Excellent alternative. Recompiling can cause collateral damage you don´t want to have if you don´t want to be responsable for the server maintenance.
This option worked the best for me. On Redhat/Centos 7 the package is also named rlwrap.
Brilliant solution. I had to recompile SQLite3 to get a current version and after then the arrow keys stopped working. This fixed it without having to figure out what to change in the compilation settings. I also added a line to my .bashrc file to automatically add rlwrap, like this: alias sqlite3='rlwrap sqlite3' . With this I don't have to remember to add it in front of the command.
29

For the arrow keys to work within the sqlite3 command prompt, the sqlite3 binary needs to be linked to the readline library. You can check that with:

$ ldd $(which sqlite3)

In my case, I found out that the sqlite3 command was actually using the sqlite3 version in the installed Android SDK, by running:

$ which sqlite3
/opt/google/android-sdk/tools/sqlite3

That version does not have the readline library link, while the /usr/bin/sqlite3 one does. So what I did is move sqlite3 in the Android SDK out of the way (I still want to be able to access my other tools in that directory - but then I'd better make symlinks from /usr/local/bin or ~/bin), and running sqlite3 now uses the /usr/bin/sqlite3 one with readline linked into the executable, and the up/down arrow keys will work in there.

Improve this answer

2 Comments

This was the cause of the problem for me. I fixed mine by just making sure the android SDK was after "/usr/bin" in my PATH. You can check the order of yours with "echo $PATH". I modified mine in ~/.bash_profile
Similarly, if you use anaconda as your default python installation, it will point sqlite3 to ~/anaconda3/bin/sqlite3, which does not have the readline link. So either write out /usr/bin/sqlite3 every time or append the alias alias sqlite3='/usr/bin/sqlite3' to your .bashrc.
11

I had the same problem on OS X.

In order to fix it, you need to compile the sqlite3 yourself. The precompiled binaries can't assume that you've got readline support, so they left that out.

If you download the "autoconf" source from http://www.sqlite.org/download.html (second link, currently to http://www.sqlite.org/sqlite-autoconf-3071300.tar.gz ):

then you can compile it via the terminal (cd to the directory the tarball extracts to):

$ ./configure
$ make
$ sudo make install

and it will automatically recognise that OS X has readline and curses support and your new sqlite3 binary will respond as expected to the arrows, delete key etc.

Improve this answer

3 Comments

Your solution is consistent with what I'm finding elsewhere via Google, but for me it required removing the old one first (installed to a different location later in the path), and then soft-linking to the old path where OS X kept looking for it. Works now.
Thank you, Ambrose. This fixed the issue for me too. I had installed the precompiled sqlite update earlier today and could not retrieve previously compiled commands. Your solution resolved that.
justi n case anyone is on osx with anaconda Anaconda3-4.3.1 for os x has sqlite 3.13.0 which comes prebuilt with readline, arrow keys, delete working.
1

Here are two answers for two questions:

  1. I find that using the sqlite3 command line client my arrow keys work without trouble.

  2. You have two errors in your INSERT statement:

    • You should not supply a value for the AUTOINCREMENT column.

    • You should list the columns and the order in which to map the values. This is required when you do not have the same amount of values as columns, but it's good practice even when you do, because later changes to the table's structure may change the order or number of columns.

    • Also, single quotes are more standard in SQL databases. SQLite will accept the double quotes, some other programs won't.

      INSERT INTO Resource (ResourceType) VALUES ('razor')

Improve this answer

3 Comments

thanks larry. btw, i'm using a mac osx, so each time i press the up arrow or something, ^[[A comes up instead of the previous command. this is in the bash terminal.
Sorry, I know nothing about command line prompts on the Mac so I can't help there. Command recall works great on my Windows machine. I know there are a gazillion SQLite UIs for Windows and I suspect some of them must support command lines. Perhaps there's something similar for Mac?
I am on windows, adb shell to connect to emulator. I am having the same issue with up/down keys.
1

First problem: Your insert statement contains 2 fields but your table has 3 fields, so it's ambiguous. SQLite can't determine which fields you want to set. The second problem is: don't set your resourceID to null if you want to use the autoincrement.

Try this:

insert into resource(resourceType) values ("razor");

This will set the resourceID to the next value of the autoincrement and the userID to the default value.

Improve this answer

Comments

1

I'm using Linux Mint and had the same sqlite3 issue, but solved it partly with the answers here. I was initially using the sqlite3 found in anaconda, which didn't have readline support.

After I renamed the sqlite3 file in anaconda, reinstalling using the autoconf as mentioned above still didn't fix the issue. The ldd command didn't show a link to readline.so:

$ ldd $(which sqlite3)

linux-vdso.so.1 =>  (0x00007ffebb3a4000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f8aceb9e000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f8ace980000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f8ace5bb000)
/lib64/ld-linux-x86-64.so.2 (0x00007f8aceda2000)

Then,

$ sudo dpkg -r sqlite3

followed by a reinstall did the trick.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.