android.database.sqlite.SQLiteException: no such column: bucket_display_name

Question

I was trying to get buckets having audio files using MediaStore. This is working fine on Android 10 API 29 but not working previous Android versions. I've attached a screenshot of working example on Android 10 API 29.

Caused by: android.database.sqlite.SQLiteException: no such column: bucket_display_name (code 1 SQLITE_ERROR): , while compiling: SELECT bucket_display_name, bucket_id FROM audio ORDER BY date_added ASC

logcat.

Caused by: android.database.sqlite.SQLiteException: no such column: bucket_display_name (code 1 SQLITE_ERROR): , while compiling: SELECT bucket_display_name, bucket_id FROM audio ORDER BY date_added ASC
        at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:179)
        at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:135)
        at android.content.ContentProviderProxy.query(ContentProviderNative.java:418)
        at android.content.ContentResolver.query(ContentResolver.java:802)
        at android.content.ContentResolver.query(ContentResolver.java:752)
        at android.content.ContentResolver.query(ContentResolver.java:710)
        at com.aisar.mediaplayer.fragments.AudioFolderFragment$AsyncVideoFolderLoader.doInBackground(AudioFolderFragment.java:148)
        at com.aisar.mediaplayer.fragments.AudioFolderFragment$AsyncVideoFolderLoader.doInBackground(AudioFolderFragment.java:130)
        at android.os.AsyncTask$2.call(AsyncTask.java:333)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:245) 
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167) 
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641) 
        at java.lang.Thread.run(Thread.java:764) 

Code:

class AsyncVideoFolderLoader extends AsyncTask<String, String, List<ModelAudioFolder>> {

    private String sortBy;

    public AsyncVideoFolderLoader(String sortBy) {
        this.sortBy = sortBy;
    }

    @Override
    protected List<ModelAudioFolder> doInBackground(String... strings) {
        List<ModelAudioFolder> videoItems = new ArrayList<>();
        videoItems.clear();

        final HashMap<String, ModelAudioFolder> output = new HashMap<>();
        final Uri contentUri = MediaStore.Audio.Media.EXTERNAL_CONTENT_URI;

        final String[] projection = {MediaStore.Audio.Media.BUCKET_DISPLAY_NAME, MediaStore.Audio.Media.BUCKET_ID};

        try (final Cursor cursor = getActivity().getContentResolver().query(
                contentUri,
                projection,
                null,
                null,
                "" + sortBy)) {
            if ((cursor != null) && (cursor.moveToFirst())) {
                final int columnBucketName = cursor.getColumnIndex(MediaStore.Audio.Media.BUCKET_DISPLAY_NAME);
                final int columnBucketId = cursor.getColumnIndex(MediaStore.Audio.Media.BUCKET_ID);


                do {
                    final String bucketName = cursor.getString(columnBucketName);
                    final String bucketId = cursor.getString(columnBucketId);

                    if (!output.containsKey(bucketId)) {
                        final int count = getCount(contentUri, bucketId);

                        final ModelAudioFolder item = new ModelAudioFolder(
                                "" + bucketId,
                                "" + bucketName,
                                "",
                                "" + getPath(bucketId),
                                "" + count
                        );

                        output.put(bucketId, item);
                        videoItems.add(item);

                    }

                } while (cursor.moveToNext());
            }
        }
        return videoItems;
    }

    private int getCount(@NonNull final Uri contentUri, @NonNull final String bucketId) {
        try (final Cursor cursor = getActivity().getContentResolver().query(contentUri,
                null, MediaStore.Audio.Media.BUCKET_ID + "=?", new String[]{bucketId}, null)) {
            return ((cursor == null) || (cursor.moveToFirst() == false)) ? 0 : cursor.getCount();
        }
    }

    private String getPath(String BUCKET_ID) {
        String path = "";
        String selection = null;
        String[] projection = {
                MediaStore.Audio.Media.DATA,
                MediaStore.Audio.Media.BUCKET_ID
        };
        Cursor cursor = getActivity().getContentResolver().query(
                MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,
                projection,
                selection,
                null,
                null);
        while (cursor.moveToNext()) {
            if (BUCKET_ID.equals(cursor.getString(1))) {
                //add only those videos that are in selected/chosen folder
                path = cursor.getString(0);
            }
        }
        return path;
    }

    @Override
    protected void onPostExecute(List<ModelAudioFolder> audioFolderList) {
        super.onPostExecute(audioFolderList);

        if (audioFolderList.size() <= 0) {
            noFoldersRl.setVisibility(View.VISIBLE);
            foldersRl.setVisibility(View.GONE);
        } else {
            noFoldersRl.setVisibility(View.GONE);
            foldersRl.setVisibility(View.VISIBLE);
        }

        Log.d("FoldersSize", "onPostExecute: " + audioFolderList.size());

        adapterAudioFolder = new AdapterAudioFolder(getActivity(), audioFolderList, dashboardActivity);
        foldersRv.setAdapter(adapterAudioFolder);
    }
}

...

Answer 1

I did it my self now. First retrieved all audio files, then separated audio URIs. From audio URIs, I've got Folder Names. I'm posting my solution here so maybe someone else can get benefit from it.

class AsyncVideoFolderLoader extends AsyncTask<String, String, List<ModelAudioFolder>> {

    private Cursor cursor;
    List<ModelAudioTe> audioList;

    private String sortBy;

    public AsyncVideoFolderLoader(String sortBy) {
        this.sortBy = sortBy;
    }

    @Override
    protected List<ModelAudioFolder> doInBackground(String... strings) {

        String selection = null;
        String[] projection;
        projection = new String[]{
                MediaStore.Audio.Media._ID,
                MediaStore.Audio.Media.TITLE,
                MediaStore.Audio.Media.ARTIST,
                MediaStore.Audio.Media.DURATION,
                MediaStore.Audio.Media.ALBUM,
                MediaStore.Audio.Media.DATA,
                MediaStore.Audio.Media.SIZE,
                MediaStore.Audio.Media.ALBUM_ID
        };
        cursor = getActivity().getContentResolver().query(
                MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,
                projection,
                selection,
                null,
                null);
        audioList = new ArrayList<>();
        ModelAudioTe modelAudio;
        while (cursor.moveToNext()) {


            modelAudio = new ModelAudioTe(
                    "" + cursor.getString(0),
                    "" + cursor.getString(1),
                    "" + cursor.getString(2),
                    "" + cursor.getString(3),
                    "" + cursor.getString(4),
                    "" + cursor.getString(5),
                    "" + cursor.getString(6),
                    "" + cursor.getString(7));
            audioList.add(modelAudio);

        }


        //creating audio paths/uris list
        ArrayList<String> pathsList = new ArrayList<>();
        pathsList.clear();
        for (int i = 0; i < audioList.size(); i++) {
            String folderName = new File(audioList.get(i).getDATA()).getParentFile().getName();
            String folderId = new File(audioList.get(i).getDATA()).getParentFile().getParent();
            pathsList.add(folderId + "/" + folderName);
        }


        //generating folder names from audio paths/uris
        List<ModelAudioFolder> folderList = new ArrayList<>();
        folderList.clear();
        for (int i = 0; i < audioList.size(); i++) {

            String folderName = new File(audioList.get(i).getDATA()).getParentFile().getName();
            String folderId = new File(audioList.get(i).getDATA()).getParentFile().getParent();
            int count = Collections.frequency(pathsList, folderId + "/" + folderName);

            String folderRoot;
            String folderRoot1 = "";
            if (audioList.get(i).getDATA().contains("emulated")) {
                folderRoot = "emulated";
            } else {
                folderRoot = "storage";
            }

            if (i > 0) {
                if (audioList.get(i - 1).getDATA().contains("emulated")) {
                    folderRoot1 = "emulated";
                } else {
                    folderRoot1 = "storage";
                }
            }
            if (i == 0) {

                ModelAudioFolder model = new ModelAudioFolder("" + folderId + "/" + folderName, "" + folderName, "", "" + folderId + "/" + folderName, "" + count);
                folderList.add(model);
                Log.d("The_Tag1", "onCreate: " + folderName + " " + folderRoot + " " + folderId + "/" + folderName + " " + count);

            } else if (
                    folderName.equals(new File(audioList.get(i - 1).getDATA()).getParentFile().getName())
                            &&
                            folderRoot.equals(folderRoot1)

            ) {
                //exclude
            } else {
                ModelAudioFolder model = new ModelAudioFolder("" + folderId + "/" + folderName, "" + folderName, "", "" + folderId + "/" + folderName, "" + count);
                folderList.add(model);

                Log.d("The_Tag1", "onCreate: " + folderName + " " + folderRoot + " " + folderId + "/" + folderName + " " + " " + count);

            }

        }


        return folderList;
    }

    @Override
    protected void onPostExecute(List<ModelAudioFolder> audioFolderList) {
        super.onPostExecute(audioFolderList);

        Log.d("ModelAudioFolder_Size", "Count:" + audioFolderList.size());

        try {
            if (audioFolderList.size() <= 0) {
                noFoldersRl.setVisibility(View.VISIBLE);
                foldersRl.setVisibility(View.GONE);
            } else {
                noFoldersRl.setVisibility(View.GONE);
                foldersRl.setVisibility(View.VISIBLE);
            }

            Log.d("FoldersSize", "onPostExecute: " + audioFolderList.size());

            adapterAudioFolder = new AdapterAudioFolder(getActivity(), audioFolderList, dashboardActivity);
            foldersRv.setAdapter(adapterAudioFolder);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Answer 2

The reason of the exception is BUCKET_DISPLAY_NAME. It is added in API 29. Before this we were using DISPLAY_NAME for API 28 & below. Please refer to the docs BUCKET_DISPLAY_NAME.

For solution you can write conditions according to current API level. And for getting folder name, you can use RELATIVE_PATH.

Answer 3

In API verions <= 28 BUCKET_DISPLAY_NAME only exists for images and videos, so, accesible via MediaStore.Images.ImageColumns.BUCKET_DISPLAY_NAME or MediaStore.Vidos.VideoColumns.BUCKET_DISPLAY_NAME.

That said, the solution to get the BUCKET_DISPLAY_NAME for audios in versions bellow 29, is to parse the MediaStore.Files.FileColumns.DATA. This field is deprecated in 29, but still valid in previous versions.

The field contains the actual path + filename, and since a BUCKET_DISPLAY_NAME is in reality the actual parent folder name where a file is located, what you need to do is to remove the file name, and then get the sub-string starting at the last found backward-slash from the path.