Getting phone number by name with sqlite - can't figure out the working syntax

[robert roszak]

Hi, 

I'm trying to fetch a contact's number from contact's name. I'm doing this with run shell action and a sql input. Here's the exact string I'm putting as sql command (in bold):

/system/xbin/sqlite3 /data/data/com.android.providers.contacts/databases/contacts2.db 'SELECT number FROM view_v1_phones WHERE name LIKE 'ContactName';'

ContactName is the name of contact for which I'm trying to get the phone number.

Currently this command gets the error: Too many options: "ContactName;"

Any ideas how to work it around?

[Mike L]

Is "ContactName" a variable? You are missing the % in front of it if so. The following works on my phone, where %name is obviously a contacts name:

sqlite3 /data/data/com.android.providers.contacts/databases/contacts2.db "SELECT normalized_number FROM phone_lookup WHERE raw_contact_id =(SELECT raw_contact_id FROM data WHERE data1 LIKE '%name') LIMIT 1;"

[Matt R]

Looks like you're quoting wrong. Try replacing one pair of single quotes with a pair of double quotes. Reference: http://www.grymoire.com/Unix/Quote.html

Matt

[robert roszak]

No, "ContactName" is not tasker variable, it's just a name of the contact that physically exists in my phonebook. Of course later on I would replace it with a tasker variable but for now i've put a real name for the sake of simplicity.

I've changed single quotes to double quotes but it still brings an error (a different one though). Here's the exact syntax in bold:

/system/xbin/sqlite3 /data/data/com.android.providers.contacts/databases/contacts2.db "SELECT number FROM view_v1_phones WHERE name LIKE 'ContactName';"

Error: no such function: _PHONE_NUMBER_STRIPPED_REVERSED

Any ideas guys? Must say I've been trying to work it around for some time but my command of SQL is just lame.

[robert roszak]

OK. I did some additional testing and it turned out, that the problem is not with the syntax but with contacts2.db itself. I tried to list all tables within the database and it worked, so I knew I had sqlite correctly installed. I decided to modify the command to get data from other table and "SELECT * FROM view_groups" does retrieve proper data (whereas "SELECT * FROM view_v1_phones" brings _PHONE_NUMBER_STRIPPED_REVERSED error). I've tried some other tables within contacts2.db, some of them work and some bring that _PHONE... error. It's strange because apps like SQL DEBBUGER or ROM TOOLBOX can read all tables in Contacts2.db just fine.

I have no idea what's wrong. I thought about using other table to get the phone number e.g. view_v1_people but it returns the same strange error.  I'd really appreciate any help.

Robert

[Jose Angel Alvarez]

Same problem here.

Robert, have you found any solution?

[Bob Hansen]

I saw someone else with this problem and they changed the version of sqlite and solved the problem. You might try installing this: https://play.google.com/store/apps/details?id=ptSoft.util.sqlite3forroot

I am using it and I don't have that issue.

[Jose Angel Alvarez]

Thanks Bob ;)

I tried the app you mention from market but it's not working with 4.4.2 :(

I solved the problem installing the sqlite3 that is downloaded from romtoolbox (rootbrowser).

[Ironhead65]

What version number was it that you installed?

[Jose Angel Alvarez]

SQLite version 3.7.11 2012-03-20 11:35:50

[Mike Yin]

1.Get the id of this contact and save it in %Cid,%tap_label is contact's name
sqlite3 /data/data/com.android.providers.contacts/databases/contacts2.db " SELECT _id FROM raw_contacts WHERE display_name LIKE'%tap_label'";

2.get the number by this id and save it to %Cnum
sqlite3 /data/data/com.android.providers.contacts/databases/contacts2.db "SELECT normalized_number FROM phone_lookup WHERE raw_contact_id=%Cid";

3.in case the contact have many phone numbers, split the %Cnum and get the first one cause it's the primary number

variable set %newline to \n .\n is carriage-return.
variable split %Cnum splitter %newline

that's it, %Cnum(1) is what you want

[Robert Szabo]

Solution for _PHONE_NUMBER_STRIPPED_REVERSD problem!!

Because there aren't any VIEW in my contact2 db, sqlite3 throw this silly error mesage. (takes a day to realize this :) )

But there is workaround:  :)

Run this shell command from an android terminal:

Phone=12345678; sqlite3 /data/data/com.android.providers.contacts/databases/contacts2.db "SELECT c.display_name FROM raw_contacts c, phone_lookup p WHERE p.raw_contact_id=c._id and p.normalized_number LIKE '%$Phone';"

Thats it! 

:) 

I have  Samsung Note 3 and Lolipop and there was no sqlite3 at all in Path

Get latest SQLITE3 from XDA in my case armv7-pie.

Copy to /system/xbin/sqlite3

Change permissions to 755

then run this shell command from terminal.

Next step is runing it from Tasker. 

:)

[Robert Szabo]

Sorry the ritght query is:

NameSearchFor='Viktoria'; sqlite3 contacts2.db "SELECT p.normalized_number FROM raw_contacts c, phone_lookup p WHERE p.raw_contact_id=c._id and c.display_name LIKE '%$NameSearchFor' ;"

Previously I copied the reverse query ie find a name based on number search....