Nothing happen when call to executeSql

[cladikzone]

Sometime in Android device, txn.executeSql not return anything, ( even error or success functions are not triggering ) . this is my code.

namespace DbConnection {
    export function getInstance() {
        return SQLite.openDatabase('test.db', '1.0', 'Test DB', 200000);
    }
}

class AdsDB {

    adsQueue: AdsQueue;

    constructor() {

    }

    successCB() {
        alert('DONE');
    }

    openCB() {
        // alert('DB Opened');
    }

    errorCB(error) {
        alert(JSON.stringify(error));
        return false;
    }


    getNextTrack(round: number, callback: (error: any, response?: any) => void) {
        DbConnection.getInstance().transaction((txn) => {

        -------------> GETTING TO HERE
    
            txn.executeSql(`SELECT * FROM PlayList WHERE round=:round AND playing_status=:playing_status
            AND played_count <= max_playable_count ORDER BY priority limit 1`, [round, 'PENDING'], (tx, res) => {

                ------------>  NOT GETTING TO HERE ..

                callback(undefined, res.rows.item(0));
            }, (error) => {
                ------------>  NOT GETTING TO HERE AS WELL ..
                callback(error);
            });
        });
    }
}

[craftzdog]

Please provide me enough information to reproduce it.
You can reuse the test code here: https://github.com/craftzdog/react-native-sqlite-2/blob/master/test/app.js
Don't forget to put your android version.

[JosefButzke]

Try change the name of database within ".db".

{
name: 'Database',
createFromLocation: '~/www/Database.db',
}

[rajan-keypress]

not work for me

[mrrobotisreal]

Did anyone ever figure this bug out? I'm hitting this exact same problem and it's super annoying. Works perfectly every single time on Android simulator, but never once works or fires either the success callback or the error callback on my physical android device.

@B-Evans99 that issue is not the same. My db is configured and opened properly as the solution already described in the problem.
@craftzdog Any updates on this issue? It definitely seems to be a reproducable bug on a physical android device, but cannot be reproduced on Android simulator.

Physical Android Device:
Samsung Galaxy S21
OneUI version = 5.1
Android version = 13
Android Security Patch Level = June 1, 2023

Expo Client version = 2.28.8 (supports SDK 46, 47, 48 and I'm using 48 for development)
DeviceID = 87ab-b534

Package.json

{
  "name": "flash-fire-mobile",
  "version": "1.0.0",
  "main": "node_modules/expo/AppEntry.js",
  "scripts": {
    "start": "expo start",
    "android": "expo start --android",
    "ios": "expo start --ios",
    "web": "expo start --web"
  },
  "dependencies": {
    "@expo/vector-icons": "^13.0.0",
    "@react-native-async-storage/async-storage": "1.17.11",
    "@react-navigation/native": "^6.1.6",
    "@react-navigation/native-stack": "^6.9.12",
    "@react-oauth/google": "^0.11.0",
    "axios": "^1.4.0",
    "expo": "~48.0.15",
    "expo-application": "~5.1.1",
    "expo-asset": "~8.9.1",
    "expo-auth-session": "~4.0.3",
    "expo-av": "~13.2.1",
    "expo-crypto": "~12.2.1",
    "expo-file-system": "~15.2.2",
    "expo-font": "^11.1.1",
    "expo-image": "~1.0.1",
    "expo-splash-screen": "~0.18.2",
    "expo-sqlite": "~11.1.1",
    "expo-status-bar": "^1.4.4",
    "expo-system-ui": "~2.2.1",
    "expo-web-browser": "~12.1.1",
    "isaac": "^0.0.5",
    "react": "18.2.0",
    "react-intl": "^6.4.4",
    "react-native": "0.71.8",
    "react-native-bcrypt": "^2.4.0",
    "react-native-crypto": "^2.2.0",
    "react-native-elements": "^3.4.3",
    "react-native-gesture-handler": "~2.9.0",
    "react-native-modal": "^13.0.1",
    "react-native-reanimated": "~2.14.4",
    "react-native-safe-area-context": "4.5.0",
    "uuid": "^9.0.0",
    "expo-image-picker": "~14.1.1",
    "expo-checkbox": "~2.3.1",
    "expo-sharing": "~11.2.2"
  },
  "devDependencies": {
    "@babel/core": "^7.20.0"
  },
  "private": true
}

DbContext.js

import { useState, useEffect, createContext } from 'react';
import * as SQLite from 'expo-sqlite';
import * as FileSystem from 'expo-file-system';
import { Asset } from 'expo-asset';
import * as Sharing from 'expo-sharing';

export const DbContext = createContext();

const openDB = () => {
  return SQLite.openDatabase('flashFire.db');
};

const checkShare = async () => {
  await Sharing.shareAsync(
    FileSystem.documentDirectory + 'SQLite/main',
    {dialogTitle: 'share or copy your DB via'}
 ).catch(error =>{
    console.log(error);
 })
}

export const DbContextProvider = ({ children }) => {
  const [db, setDb] = useState(SQLite.openDatabase('flashFire.db'));
  const [dbItem, setDbItem] = useState('this is just a placeholder for now');
  const [users, setUsers] = useState([]);
  const [currentUser, setCurrentUser] = useState();
  const [collections, setCollections] = useState();
  const [currentCollection, setCurrentCollection] = useState();
  const [flashcards, setFlashcards] = useState();
  const [currentFlashcards, setCurrentFlashcards] = useState();
  const [textsCollection, setTextsCollection] = useState();

  useEffect(() => {
    if (!db) {
      setDb(openDB());
    }
  }, []);

  useEffect(() => {
    db.transaction(tx => {
      tx.executeSql('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, password TEXT, email TEXT)')
    },
    () => {},
    () => {});

    // db.transaction(tx => {
    //   tx.executeSql('CREATE TABLE IF NOT EXISTS collections (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, collectionname TEXT, category TEXT');
    // });

    // db.transaction(tx => {
    //   tx.executeSql('CREATE TABLE IF NOT EXISTS flashcards (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, collectionname TEXT, question TEXT, answer TEXT, audiouri TEXT, imageuri TEXT');
    // });

    db.transaction(tx => {
      tx.executeSql('SELECT * FROM users', null,
        (txObj, resultSet) => setUsers(resultSet.rows._array),
        (txObj, error) => {
          console.error(error);
          return true;
        },
      );
    });
  }, [db]);

  const getUsers = () => {
    db.transaction(tx => {
      tx.executeSql('SELECT * FROM users', null,
        (txObj, resultSet) => setUsers(resultSet.rows._array),
        (txObj, error) => {
          console.error(error);
          return true;
        },
      );
    })
  };

  /**
   * Google
   */
  const getUserWithGoogle = () => {};

  const addUserWithGoogle = () => {};

  /**
   * Username and Password (NEITHER console.log in success or error callback get logged, indicating they are never being called....
   */
  const getUserWithUnameAndPword = async (username, password) => {
    console.log('checking uname and pword:', username, password);
    db.transaction(tx => {
      tx.executeSql('SELECT * FROM users WHERE username = ?', [username],
        (txObj, resultSet) => {
          console.log('get resultSet:', resultSet.rows._array);
          let user = resultSet.rows._array[0];
          console.log('user.password', user.password);
          console.log('password', password);
          user.isAuthenticated = checkUnamePword(username, user.password, password);
          console.log('USER after auth:', user)
          setCurrentUser(user);
        },
        (txObj, error) => {
          console.log('error looking up username and password during login');
          console.error(error);
          return true;
        },
      );
    });
  };

// NEITHER console.log is logged in success or error callback here either
  const addUserWithUnameAndPword = (username, password, email) => {
    console.log('ADDING USER!');
    db.transaction(tx => {
      tx.executeSql(`INSERT INTO users (username, password, email) VALUES (?, ?, ?)`, [username, hashedPassword, email],
        (txObj, resultSet) => {
          console.log('ADD USER resultSet:', resultSet.rows._array);
          getUserWithUnameAndPword(username, password);
        },
        (txObj, error) => {
          console.error(error);
          return true;
        },
      );
    })
  };

  const checkUnamePword = (username, userPassword, password) => {
    return userPassword === password;
  };

  /**
   * Storing / Retrieving collections
   */
  const getAllCollections = async () => {
    db.transaction(tx => {
      tx.executeSql('SELECT * FROM collections WHERE username = ?', [currentUser.username],
        (txObj, resultSet) => setCollections(resultSet.rows._array),
        (txObj, error) => {
          console.error(error);
          return true;
        }
      );
    });
  };

  const storeCollection = async (collection) => {
    // store collection and cards
    const name = collection.name;
    const category = collection.category;
    const cards = collection.cards;
    db.transaction(tx => {
      tx.executeSql('INSERT INTO collections (username, collectionname, category) VALUES (?, ?, ?)', [currentUser?.username, name, category],
        (txObj, resultSet) => {},
        (txObj, error) => {
          console.error(error);
          return true;
        },
      );
    });
    cards.forEach(card => {
      db.transaction(async tx => {
        await tx.executeSql('INSERT INTO flashcards (username, collectionname, question, answer, audiouri, imageuri) VALUES (?, ?, ?, ?, ?, ?)', [currentUser?.username, name, card.question, card.answer, card.audioUri ? card.audioUri : null, card.imageUri ? card.imageUri : null],
          (txObj, resultSet) => {},
          (txObj, error) => {
            console.error(error);
            return true;
          },
        );
      });
    });
  };

  const getCollection = async (collectionName) => {
    console.log('currentUser in getCollection:', currentUser);
    db.transaction(tx => {
      tx.executeSql('SELECT * FROM collections WHERE collectionname = ? AND username = ?', [collectionName, currentUser?.username],
        (txObj, resultSet) => setCurrentCollection(resultSet.rows._array[0]),
        (txObj, error) => {
          console.error(error);
          return true;
        },
      );
    });
    db.transaction(tx => {
      tx.executeSql('SELECT * FROM flashcards WHERE collectionname = ? AND username = ?', [collectionName, currentUser?.username],
        (txObj, resultSet) => setCurrentFlashcards(resultSet.rows._array),
        (txObj, error) => {
          console.error(error);
          return true;
        }
      );
    });
  };

  /**
   * Texts
   */
  const addText = () => {};

  const fetchGoogleTexts = () => {};

  /**
   * My tools
   */
  const manualDelete = async (table, collectionName) => {
    db.transaction(tx => {
      tx.executeSql('DELETE FROM flashcards WHERE collectionname = ?', [collectionName],
        (txObj, resultSet) => {},
        (txObj, error) => {
          console.error(error);
          return true;
        }
      );
    })
    db.transaction(tx => {
      tx.executeSql('DELETE FROM collections WHERE collectionname = ?', [collectionName],
        (txObj, resultSet) => {},
        (txObj, error) => {
          console.error(error);
          return true;
        }
      );
    })
  };

  const checkTheDB = async () => {
      ..........
  };

  const values = {
    db: db,
    currentUser,
    getUsers,
    getUserWithGoogle,
    addUserWithGoogle,
    getUserWithUnameAndPword,
    addUserWithUnameAndPword,
    storeCollection,
    manualDelete,
    textsCollection,
    addText,
    fetchGoogleTexts,
    checkTheDB,
    getAllCollections,
    currentCollection,
    currentFlashcards,
    getCollection,
    collections,
  }

  return (
    <DbContext.Provider value={values}>
      {children}
    </DbContext.Provider>
  )
};

As I said before, if I could share a reprocudable snack of this I would, but I have never been able to reproduce the issue on an android simulator, it always works perfectly. It is only specifically when dealing with a physical android device that none of the executesql callbacks get called at all, or db.transaction for that matter as far as I know. I've never seen logs for those either.

I've tried making a const db = SQLite.openDatabase('FlashFire.db'); outside of the context provider and inside the context provider instead of using state, but the state seems to work the best at least when it all works on the simulator so I've kept it that way. Absolutely nothing works at all on a physical android device when it comes to using expo-sqlite.

Should I be using a different npm package for my sqlite queries? I just figured expo-sqlite would be the best option since I am using expo, but apparently it does not work on a physical device.

Any help is appreciated, thanks!

P.S.
Just for clarification too, I've also tried clearing the bundle cache numerous times, that doesn't seem to make any difference with this issue.