Plugin for Android stuck at executing CREATE TABLE statements

[ranjithrd]

Describe the bug A clear and concise description of what the bug is.

The plugin on Android is stuck on a call for await db.execute(CREATE TABLE....).

To Reproduce Steps to reproduce the behavior:

1. Create any Ionic React project
2. Add this package using the instructions provided in README and in the Ionic-React.md doc
3. Use a CREATE TABLE statement
4. See error

Expected behavior A clear and concise description of what you expected to happen.

The statement shouldn't have taken more than a second or so. or, see iOS screenshot below

Screenshots If applicable, add screenshots to help explain your problem.

Some problems with my installation right now make it impossible for me to share the debug output. But I'm attaching expected behaviour (iOS) and actual behaviour (Android)

Expected Behaviour, working on iOS

Actual Behaviour, Android

Code

App.tsx

import { useSQLite } from "react-sqlite-hook/dist"
import { useState } from "react"

// Singleton SQLite Hook
export let sqlite: any
// Existing Connections Store
export let existingConn: any
const App: React.FC = () => {
    const [existConn, setExistConn] = useState(false)
    existingConn = { existConn: existConn, setExistConn: setExistConn }
    const {
        echo,
        getPlatform,
        createConnection,
        closeConnection,
        isConnection,
        retrieveConnection,
        retrieveAllConnections,
        closeAllConnections,
        addUpgradeStatement,
        importFromJson,
        isJsonValid,
        copyFromAssets,
        isAvailable,
    } = useSQLite({})
    sqlite = {
        echo: echo,
        getPlatform: getPlatform,
        createConnection: createConnection,
        closeConnection: closeConnection,
        isConnection: isConnection,
        retrieveConnection: retrieveConnection,
        retrieveAllConnections: retrieveAllConnections,
        closeAllConnections: closeAllConnections,
        addUpgradeStatement: addUpgradeStatement,
        importFromJson: importFromJson,
        isJsonValid: isJsonValid,
        copyFromAssets: copyFromAssets,
        isAvailable: isAvailable,
    }

     return ...

}

Page.tsx

import ...
import { sqlite } from "../App"

function Page() {
    const [res, setRes] = useState({})

    useEffect(() => {
        ;(async () => {
            try {
                let db
                const connectionName = "steward"
                try {
                    db = await sqlite.retrieveConnection(connectionName)
                } catch (error) {
                    console.log("retrieveConnection error", error)
                }

                try {
                    if (!db) {
                        await sqlite.closeConnection(connectionName)
                    }
                } catch (error) {
                    console.log("closeConnection error", error)
                }

                try {
                    if (!db) {
                        setRes("creating connection on next line")
                        db = await sqlite.createConnection(connectionName)
                        setRes("created connection")
                    }
                } catch (error) {
                    console.log("createConnection error", error)
                }

                if (!db) {
                    throw new Error("unable to create or retrieve connection")
                }

                setRes("opening db on next line")
                await db.open()
                setRes("opened db")

                setRes("executing create statements on next line") // HERE IS WHERE IT'S STUCK
                await db.execute(`
        DROP TABLE chores;
        DROP TABLE items;

        CREATE TABLE IF NOT EXISTS chores (
          id INTEGER PRIMARY KEY,
          name TEXT,
          next_due DATE,
          completed BOOLEAN,
          repeats BOOLEAN,
          frequency INT
        );

        CREATE TABLE IF NOT EXISTS items (
          id INTEGER PRIMARY KEY,
          name TEXT,
          image TEXT,
          next_buy DATE,
          buy_from TEXT,
          supplied BOOLEAN
        );
        `)
                setRes("executed create statements")

                setRes("executing insert on next line")
                await db.execute(
                    "INSERT INTO chores VALUES(null, 'Clean room 2', '2021-12-11', false, false, 1)"
                )
                setRes("executed insert")

                setRes("selecting from table")
                setRes(await db.query("SELECT * FROM chores;"))
            } catch (e) {
                console.error(e)
            }
        })()
    }, [])

       return ...
}

Desktop (please complete the following information):

• OS: MacOS
• Version 11

Smartphone (please complete the following information):

• Device: RealMe GT Master Edition
• OS: Android (ColourOS)
• Version: 11

Additional context Add any other context about the problem here.

The code for instantiating the database is from #153 with very little modification.

This is a debug APK build, through Android studio and ionic capacitor build android. I couldn't use emulators unfortunately. I'll try my best to check emulators or other debug builds, but I'd like to know if there's anything wrong with the code that I'm using.

Note: I'm 100% sure that this is a mistake on my end, but there doesn't seem to be a platform that I know of where I can ask this other than here on issues. Besides, I think posting this here might help other folks who might get stuck in a similar situation.

Thank you so much for your work on the package. I hope I can get this resolved ASAP.

[jepiqueau]

@ranjithrd can you share your package.json file. you should look at https://github.com/jepiqueau/react-sqlite-app-starter which is an updated app to help you starting. the execute is wrong

INSERT INTO chores VALUES(null, 'Clean room 2', '2021-12-11', false, false, 1)

replace with

INSERT INTO chores(name,next_due,completed,repeats,frequency) VALUES ('Clean room 2', '2021-12-11', false, false, 1);

and add AUTOINCREMENT in id INTEGER PRIMARY KEY,

this should work

[ranjithrd]

@jepiqueau thanks for responding. Here's my package.json:

{
  "name": "testionic",
  "version": "0.0.1",
  "private": true,
  "dependencies": {
    "@capacitor/android": "3.2.4",
    "@capacitor/app": "1.0.3",
    "@capacitor/core": "3.2.4",
    "@capacitor/haptics": "1.1.0",
    "@capacitor/ios": "3.2.4",
    "@capacitor/keyboard": "1.1.0",
    "@capacitor/status-bar": "1.0.3",
    "@ionic/react": "^5.5.0",
    "@ionic/react-router": "^5.5.0",
    "@testing-library/jest-dom": "^5.11.9",
    "@testing-library/react": "^11.2.5",
    "@testing-library/user-event": "^12.6.3",
    "@types/jest": "^26.0.20",
    "@types/node": "^12.19.15",
    "@types/react": "^16.14.3",
    "@types/react-dom": "^16.9.10",
    "@types/react-router": "^5.1.11",
    "@types/react-router-dom": "^5.1.7",
    "ionicons": "^5.4.0",
    "react": "^17.0.1",
    "react-dom": "^17.0.1",
    "react-router": "^5.2.0",
    "react-router-dom": "^5.2.0",
    "react-scripts": "4.0.2",
    "typescript": "^4.1.3",
    "web-vitals": "^0.2.4",
    "workbox-background-sync": "^5.1.4",
    "workbox-broadcast-update": "^5.1.4",
    "workbox-cacheable-response": "^5.1.4",
    "workbox-core": "^5.1.4",
    "workbox-expiration": "^5.1.4",
    "workbox-google-analytics": "^5.1.4",
    "workbox-navigation-preload": "^5.1.4",
    "workbox-precaching": "^5.1.4",
    "workbox-range-requests": "^5.1.4",
    "workbox-routing": "^5.1.4",
    "workbox-strategies": "^5.1.4",
    "workbox-streams": "^5.1.4"
  },
  "scripts": {
    "start": "react-scripts start",
    "build": "react-scripts build",
    "test": "react-scripts test",
    "eject": "react-scripts eject"
  },
  "eslintConfig": {
    "extends": [
      "react-app",
      "react-app/jest"
    ]
  },
  "browserslist": {
    "production": [
      ">0.2%",
      "not dead",
      "not op_mini all"
    ],
    "development": [
      "last 1 chrome version",
      "last 1 firefox version",
      "last 1 safari version"
    ]
  },
  "devDependencies": {
    "@capacitor-community/sqlite": "^3.0.0-rc.2",
    "@capacitor/cli": "3.2.4",
    "react-sqlite-hook": "^2.0.2"
  },
  "description": "An Ionic project"
}

I've not added jeep-sqlite or followed web instructions since I'm only using SQLite in Android and iOS.

The changes in the SQL statements have unfortunately not worked. :-(

Here's the screenshot, the code except for the SQL is exactly the same.

[jepiqueau]

@ranjithrd Hi, you cannot Drop a table if it does not exist so make the following modifications

                    DROP TABLE IF EXISTS chores;
                    DROP TABLE IF EXISTS items;

and it should work

[ranjithrd]

@jepiqueau While that seemed to be an error, it didn't seem to do anything... Updated code in Page.tsx

try {
...
                setRes("executing create statements on next line")
                await db.execute(`
        CREATE TABLE IF NOT EXISTS chores (
          id AUTOINCREMENT INTEGER PRIMARY KEY,
          name TEXT,
          next_due DATE,
          completed BOOLEAN,
          repeats BOOLEAN,
          frequency INT
        );

        CREATE TABLE IF NOT EXISTS items (
          id AUTOINCREMENT INTEGER PRIMARY KEY,
          name TEXT,
          image TEXT,
          next_buy DATE,
          buy_from TEXT,
          supplied BOOLEAN
        );
        `)
         setRes("executed create statements")
...
} catch (error) {
        setError(error)
}

...
{res}
<br />
{error}
...

Attaching screenshot of this as well.

[jepiqueau]

@ranjithrd Look at https://github.com/jepiqueau/react-sqlite-app-starter i have added a test for this issue and it works fine. So i do not know what is going on on your app. Can you share it ?

[ranjithrd]

@jepiqueau Sure, here you go. https://github.com/ranjithrd/ion-test

[jepiqueau]

@ranjithrd Thanks,

• npm i --save-dev @capacitor-community/sqlite@3.2.3 react-sqlite-hook@2.1.0
• delete folder android, ios
• npm run build
• npx cap add android
• npx cap add ios
• i modified App.tsx

  
  ...
  // Imports
  import { SQLiteHook, useSQLite } from "react-sqlite-hook/dist"
  import { useState } from "react"

// Singleton SQLite Hook export let sqlite: SQLiteHook // Existing Connections Store export let existingConn: any const App: React.FC = () => { const [existConn, setExistConn] = useState(false) existingConn = { existConn: existConn, setExistConn: setExistConn } sqlite = useSQLite() return ( ...

- then i modified `Page.tsx`

import { useEffect, useState } from "react" import { useParams } from "react-router" import { sqlite } from "../App" import "./Page.css" import BaseLayout from "../layouts/BaseLayout"

const Page: React.FC = () => { const { name } = useParams<{ name: string }>() const [res, setRes] = useState({}) const [error, setError] = useState()

useEffect(() => {
    ;(async () => {
        try {
            let db
            const connectionName = "steward"
            try {
                db = await sqlite.retrieveConnection(connectionName)
            } catch (error) {
                console.log("retrieveConnection error", error)
            }

            try {
                if (!db) {
                    await sqlite.closeConnection(connectionName)
                }
            } catch (error) {
                console.log("closeConnection error", error)
            }

            try {
                if (!db) {
                    setRes("creating connection on next line")
                    db = await sqlite.createConnection(connectionName)
                    setRes("created connection")
                }
            } catch (error) {
                console.log("createConnection error", error)
            }

            if (!db) {
                throw new Error("unable to create or retrieve connection")
            }

            setRes("opening db on next line")
            await db.open()
            setRes("opened db")

            setRes("executing create statements on next line")
            await db.execute(`
                DROP TABLE IF EXISTS chores;
                                     DROP TABLE IF EXISTS items;

                CREATE TABLE IF NOT EXISTS chores (
                id INTEGER PRIMARY KEY,
                name TEXT,
                next_due DATE,
                completed BOOLEAN,
                repeats BOOLEAN,
                frequency INT
                );

                CREATE TABLE IF NOT EXISTS items (
                id INTEGER PRIMARY KEY,
                name TEXT,
                image TEXT,
                next_buy DATE,
                buy_from TEXT,
                supplied BOOLEAN
                );
                `)
            setRes("executed create statements")

            setRes("executing insert on next line")
            await db.query(
                "INSERT INTO chores (name,next_due,completed,repeats,frequency) VALUES ('Clean room 2', '2021-12-11', false, false, 1);"
            )
            await db.query(
                "INSERT INTO chores (name,next_due,completed,repeats,frequency) VALUES ('Wash room 2', '2021-12-12', false, false, 1);"
            )

            setRes("executed insert")

            setRes("selecting from table")
            setRes(await db.query("SELECT * FROM chores;"))
        } catch (e) {
            console.error(e)
            setError(e)
        }
    })()
}, [])

return (
    <BaseLayout title={name} tab="home">
        {name}
        <br />
        {JSON.stringify(res)}
        <br />
        {JSON.stringify(error)}
    </BaseLayout>
)

}

export default Page

- npm run build
- npx cap copy
- npx cap open android

and it works fine. 

[jepiqueau]

@ranjithrd make a new import in Page.tsx

import { SQLiteDBConnection } from "react-sqlite-hook/dist"

you can replace all the first lines of Page.tsx where you create the connection by

                let db: SQLiteDBConnection
                const connectionName = "steward"

                const ret = await sqlite.checkConnectionsConsistency();
                const isConn = (await sqlite.isConnection(connectionName)).result;
                if (ret.result && isConn) {
                  db = await sqlite.retrieveConnection(connectionName);
                } else {
                  db = await sqlite.createConnection(connectionName);
                }

                setRes("opening db on next line")
                await db.open()

Voilà

[jepiqueau]

@ranjithrd Hi, if that solve your problem, can you close the issue

[ranjithrd]

@jepiqueau Hey Jean, sorry for not responding earlier. I tried this approach, and I'm very, very, very happy to say it worked.

Thank you so much for helping me out and maintaining this package. Closing this issue. Thanks again.