importFromJson: "Stringify Json Object not Valid" on Android | {"changes": 0} on iOS

[whitnessme]

Hello! Thank you for this plugin!

Intro

I'm trying to figure this out using different tutorials and the documentation. However, most tutorials I've found are from a while ago or using Ionic; which I've just been trying to use just Capacitor. And I must admit that this is my first time using Capacitor and utilizing XCode & Android Studio. So please forgive me if I've missed some obvious things. Thank you!

My question is very close to another Q/A from three years ago; the solution did not work for me, unfortunately. I replied to it there but decided to give more info on this new post.

Stack

Next.js, TypeScript, and Capacitor. Making Andoid & iOS app.

Problem

This is just a tiny testing app and I have been trying to get a database open and fill it with seed data with importFromJson. However, when looking at the logs on Android Studio I get the error:

*** ERROR ImportFromJson: importFromJson : Stringify Json Object not Valid

However, on iOS I just get back this:

{
  "changes": {
    "changes": 0
  }
}

I added in a check with const isValid = await CapacitorSQLite.isJsonValid({ jsonstring: data });. On Android it is false but on iOS it is true.

Sidenote: I also am sometimes running into this error intermediately: *** ERROR CreateConnection: Connection chaodb already exists but not always (something I am still debugging as well); but I would like to at least get the db working.

Code

This is the initialization code I've inserted into my home page component.

// pages/home/index.tsx
import { CapacitorSQLite, SQLiteConnection } from "@capacitor-community/sqlite"
import { useEffect, useState } from "react";
import Link from "next/link";
import * as chaoData from '../../seed-data.json';

const mySQLite = new SQLiteConnection(CapacitorSQLite)

const Home: React.FC = () => {

    const [db, setDb] = useState<any>(null);
    const [chaos, setChaos] = useState<any>(null);

    const loadJSON = async () => {
        let data = JSON.stringify(chaoData)
        const isValid = await CapacitorSQLite.isJsonValid({ jsonstring: data });
        console.log("ISVALID-----------", isValid.result)
        return await mySQLite.importFromJson(data)
    }

    const initializeDB = async () => {
    const ret = await mySQLite.checkConnectionsConsistency();
    const isConn = (await mySQLite.isConnection("chaodb", false)).result;
    
    let database: any;

    if (ret.result && isConn) {
        database = await mySQLite.retrieveConnection("chaodb", false)
    } else if (ret && !ret.result && !isConn) {
        database = await mySQLite.createConnection(
            "chaodb",
            false,
            "no-encryption",
            1,
            false
            );
        }
        setDb(database);
        return database;
    }

    useEffect(() => {
        initializeDB().then((db:any) => {
            console.log("useEffect initialized " + JSON.stringify(db, null, 2))
            return loadJSON();
        }).then((res: any) => {
            console.log("after loading...", JSON.stringify(res, null, 2));
            db.open()
            return db.query("SELECT * FROM chaos")
        }).then ((data:any) => {
            console.log("DATA", data)
            setChaos(data.values)
        }).catch((res:any) => {

        })
    }, [])

    return (
        <div className="mt-10">
            <h1>Home</h1>
            <Link href="/">Back</Link>
        </div>
    )
}

export default Home;

This is my seed data that gets JSON.stringify-ed in the above initializeDB function:

{
    "database": "chaodb",
    "version": 1,
    "encrypted": false,
    "mode": "full",
    "tables": [
        {
            "name": "chaos",
            "schema": [
                { "column": "id", "value": "INTEGER PRIMARY KEY NOT NULL" },
                { "column": "name", "value": "TEXT NOT NULL" },
                { "column": "hatch_day", "value": "TEXT NOT NULL" },
                { "column": "color", "value": "TEXT NOT NULL" },
                { "column": "alignment", "value": "TEXT NOT NULL" },
                { "column": "evolution", "value": "TEXT NOT NULL" },
                { "column": "life_cycle", "value": "TEXT NOT NULL" },
                { "column": "personality", "value": "TEXT NOT NULL" },
                {
                    "column": "last_modified",
                    "value": "INTEGER DEFAULT (strftime('%s', 'now'))"
                }
            ],
            "values": [
                [1, "Noodles", "2023-04-02", "Two-toned Brown", "Neutral", "None", "Child", "Big eater", 1604396241],
                [2, "Jewel", "2023-01-03", "Monotone Pink", "Hero", "Swim", "Adult", "Chatty", 1604396241],
                [3, "Bubba", "2023-02-01", "Two-toned Black", "Dark", "Power", "Adult", "Curious", 1604396241],
                [4, "Sparky", "2023-05-03", "Monotone Sky-blue", "Neutral", "Fly", "Adult", "Naughty", 1604396241],
                [5, "Sparky", "2023-05-03", "Monotone Sky-blue", "Neutral", "Fly", "Adult", "Naughty", 1604396241]
            ]
        }
    ]
}

tsconfig.json

{
  "compilerOptions": {
    "target": "es5",
    "lib": ["dom", "dom.iterable", "esnext"],
    "allowJs": true,
    "skipLibCheck": true,
    "strict": true,
    "forceConsistentCasingInFileNames": true,
    "noEmit": true,
    "esModuleInterop": true,
    "module": "esnext",
    "moduleResolution": "node",
    "resolveJsonModule": true,
    "allowSyntheticDefaultImports": true,
    "isolatedModules": true,
    "jsx": "preserve",
    "incremental": true,
    "paths": {
      "@/*": ["./src/*"]
    }
  },
  "include": ["next-env.d.ts", "**/*.ts", "**/*.tsx"],
  "exclude": ["node_modules"]
}

Relevant Dependencies

    "@capacitor-community/sqlite": "^4.8.0-1",
    "@capacitor/android": "^5.0.2",
    "@capacitor/core": "5.0.3",
    "@capacitor/ios": "^5.0.2",
 
    "@capacitor/cli": "5.0.3"

Conclusion

Any thoughts are helpful, thank you! Please let me know if you require any more information!

[jepiqueau]

@whitnessme Several things were not correct.

• you cannot use @capacitor-community/[email protected] with capacitor 5
• the import of the json object is wrong
• in your schema you have 10 columns and you gave only 9 data for each row
• in your values the id 4 and id 5 got the same values
• importFromJson must be used without connection opened.

i change the code of the Home index.tsx as below I have replace the useState by useRef as for unknown reasons i cannot make them working. I have pure react app with Ionic framework and they work well.

import { CapacitorSQLite, SQLiteConnection } from "@capacitor-community/sqlite"
import { useEffect, useState, useRef, useCallback } from "react";
import Link from "next/link";
import chaoData from '../../seed-data.json' assert {type: 'json'};

const mySQLite = new SQLiteConnection(CapacitorSQLite)

const Home: React.FC = () => {

    const myRef = useRef(false);
    const mDb: any = useRef(null);
    const mChaos: any = useRef([]);
//    const [db, setDb] = useState<any>(null);
//    const [chaos, setChaos] = useState<any[]>([]);

    
    const loadJSON = async () => {
        let data = JSON.stringify(chaoData)
        console.log(`dataJson String: ${data}`);
        const isValid = await CapacitorSQLite.isJsonValid({ jsonstring: data });
        console.log("ISVALID-----------", isValid.result)
        return await mySQLite.importFromJson(data)
    
    }
    const initImportJson = useCallback(async () => {
        const ret = await mySQLite.checkConnectionsConsistency();
        const isConn = (await mySQLite.isConnection("chaodb", false)).result;
        let res: any = {changes: {changes: 0}};
        if (ret.result && isConn) {
            // close the connection
            await mySQLite.closeConnection("chaodb", false);
        }
        // check if the database exists
        const isExist = await mySQLite.isDatabase("chaodb");
        if (!isExist.result) {
            // load json
            res = await loadJSON();
        }
        return res;
    },[])

    const initializeDB = useCallback (async () => {
    
        let database: any = await mySQLite.createConnection(
            "chaodb",
            false,
            "no-encryption",
            1,
            false
        );
        console.log(`after createConnection ${database}`)
//        setDb(database);
        mDb.current = database;
        // open database
        await database.open();
        return;
    }, [mDb])

    useEffect(() => {
        if (myRef.current === false) {
            myRef.current = true;
        
            initImportJson().then( (res) => {
                if(res.changes.changes >= 0) {
                    initializeDB().then(() => {
                        return mDb.current.isDBOpen();
                    }).then ((isOpen) => {
                        let data: any = {};
                        if(isOpen.result) {
                            data =  mDb.current.query("SELECT * FROM chaos");
                        } else {
                            throw new Error(`Database is not opened`);
                        }
                        return data;
                    }).then ((data:any) => {
//                        setChaos(data.values);
                        mChaos.current = data.values;
                        console.log(`mChaos: ${JSON.stringify(mChaos.current)}`);
                    }).catch((err) => {
                        console.error(err);
                    })    
                } else {
                    throw new Error(`ImportFromJson number of changes wrong`);
                }
            }).catch((err) => {
                console.error(err);
            }) 
        }   
    },[mChaos, initImportJson, initializeDB, mDb])


    return (
        <div className="mt-10">
            <h1>Home</h1>
            <Link href="/">Back</Link>
        </div>
    )
}

export default Home;

like this it works fine

[jepiqueau]

@whitnessme did you make it working now

[jepiqueau]

@whitnessme no feedback so i assume you fix it so i close the discussion