HOME BLOG

Archive for October, 2020

How to inspect an Android app SQLite databases

Posted on: October 30th, 2020 by Olu No Comments

Hi folks,

In this post I talk about how to view an Android app’s SQLite database. You may need to do this for debugging purpose if develoing and Android app that stores data.

You can do this using Android Studio.

Go to View -> Tool Windows -> Device File Explorer.

In the Device File Explorer pane, navigate to dat/data/<your.app.package.name>/databases.

In here you should see three files for your database: one for the database, one for the -shm file and another for the -wal file. So if your database is called somedb in code, you would see files somedb-db, somedb-db-shm, somedb-db-wal. Cmd+click on all three files.

Then right-click and click Save As.

Select where to save them on your computer.

Once saved, you can open the main database on your computer using your favourite SQLite browser e.g. DB Browser for SQLite.

That’s all for now.

Happy software development.

 

 

How to import SQLite tables quickly

Posted on: October 24th, 2020 by Olu No Comments

Hi folks,

In this post I’ll sharemy recent experience with importing a large SQLite table. By large, I mean a table with very many rows.

Usually I use DB Browser for SQLite to manage the database.

So at first I thought, maybe I should use the export and import functionality from DB Browser for SQLite.

I exported to an SQL file, made my changes to the dump, and then tried importing that dump to a new database using DB Browser for SQLite.

Lo and behold, it was super duper slow. On realizing it would take forever, I decided there must be a faster way.

More research showed that there’s an SQLite command-line tool that can carry out imports. I learnt that it may be a good idea to export to CSV format, then import it using the SQLite command-line tool. To export to csv, use the following command:

 

sqlite3 -header -csv your_db.db "select * from your_table;" > your_csv_file.csv

 

To import a csv file use the following commands:

 

sqlite3 your_db_file.db
.mode csv
.import /path/to/your/csv_file.csv your_table_name

 

So I tried it. Boy, was it much faster.

So, the takeaway is that if you must export and later import large SQLite table, first export the database to a CSV file, and use the SQLite command-line utility to import the file(s) into a new database.

That’s all for now. Happy software development.

How to resolve error ‘The item you were attempting to purchase could not be found’ when testing subscriptions using Google Play Billing library

Posted on: October 14th, 2020 by Olu No Comments

Hi foliks,

Here I share a simple solution to a issue found when testing subscriptions with Google Play Billing library:

The error message was as follows:

The item you were attempting to purchase could not be found.

The root cause is that that the test user had not been authorized to test the app.

Solution:

Make sure you send the opt-in url to test users and that the users opt in to it.

How to create table literals in Oracle

Posted on: October 9th, 2020 by Olu No Comments

Hi folks,

In this post I talk about how to create literal tables in Oracle.

You may have a list of values and want to run an Oracle query against those values.

A convenient way to do this is to create a literal table in an Oracle SQL using those values.

 

Single-column

If you want to create a single-column literal table,

you can use either dbms_debug_vc2coll or sys.ODCIVarchar2List

e.g.

select * from table (sys.ODCIVarchar2List('AAA', 'BBB', 'CCC'));

This will return an inline table with a single column named COLUMN_VALUE and rows with values AAA, BBB and CCC.

i.e.

COLUMN_VALUE
==
AAA
BBB
CCC

Second example

select column_value from table(sys.dbms_debug_vc2coll(‘AAA’, ‘BBB’, ‘CCC’))

 

Multiple colums

If you want to generate a literal table with two columns, you can use a query like:

select objectschema m, objectname n
from
table(sys.ODCIObjectList(
sys.odciobject('APPLE', 'FRUIT'),
sys.odciobject('CARROT', 'VEGGIE')
));

For more tahan 2 columns, you can use a query like:

create type t as object (a varchar2(10), b varchar2(10), c number);
create type tt as table of t;

select * from table( tt (
    t('APPLE', 'FRUIT', 1),
    t('APPLE', 'FRUIT', 1122), 
    t('CARROT', 'VEGGIE', 3),
    t('PEACH', 'FRUIT', 104),
    t('CUCUMBER', 'VEGGIE', 5),
    t('ORANGE', 'FRUIT', 6) ) )

That’s all for now. Happy database querying.

 

Sources

Oracle SQL Tip: Using dbms_debug_vc2coll and sys.ODCIVarchar2List. https://training.fusionapplied.com/2017/08/23/oracle-sql-tip-using-dbms_debug_vc2coll-and-sys-odcivarchar2list/

Is there a non-ugly way to use a multi-column, multi-row table literal in an Oracle 11g query? https://stackoverflow.com/questions/29903397/is-there-a-non-ugly-way-to-use-a-multi-column-multi-row-table-literal-in-an-ora/29904128