Creating multiple tables in SQLite using React Native SQLite Storage Module using Transaction function

Question

What I want to do is create multiple tables for my react-native android app but it keeps returning an unknown error each time the code execute(but it works fine with creating a single table).

This is the code I have

initDB() {
    let db;
    return new Promise((resolve) => {
        console.log("Plugin integrity check ...");
        SQLite.echoTest()
            .then(() => {
                console.log("Integrity check passed ...");
                console.log("Opening database ...");
                SQLite.openDatabase(
                    database_name,
                    database_version,
                    database_displayname,
                    database_size
                )
                    .then(DB => {
                        db = DB;
                        console.log("Database OPEN");
                        db.executeSql('SELECT 1 FROM Feed LIMIT 1').then(() => {
                            console.log("Database is ready ... executing query ...");
                        }).catch((error) =>{
                            console.log("Received error: ", error);
                            console.log("Database not yet ready ... populating data");
                            db.transaction((tx) => {
                                tx.executeSql('CREATE TABLE IF NOT EXISTS Feed (feedId, feedName, feedDesc, feedPrice)');
                                tx.executeSql('CREATE TABLE IF NOT EXISTS Comment (commentId, feedId, commentDesc)');
                                tx.executeSql('CREATE TABLE IF NOT EXISTS User (userId, userName, userPass, userAdmin)');
                            }).then(() => {
                                console.log("Tables created successfully");
                            }).catch(error => {
                                console.log(error);
                            });
                        });
                        resolve(db);
                    })
                    .catch(error => {
                        console.log(error);
                    });
            })
            .catch(error => {
                console.log("echoTest failed - plugin not functional");
            });
    });
}

How can I make it create multiple tables

MikeSherm · Accepted Answer · 2021-01-16 14:24:29Z

 // I believe this could help,
// The top-level 'executeSql' a single SELECT stmt with a catch for creating tables
db.executeSql('SELECT 1 FROM Feed LIMIT 1').then(() => {
    console.log("Database is ready ... executing query ...");
}).catch((error) =>{// note- currently all CREATE tables are accually housed in this catch
    console.log("Received error: ", error);
    console.log("Database not yet ready ... populating data");
    db.transaction((tx) => {// This is where you are currently creating all your tables. 
        // Note- the creation of these tables are actually dependent upon the top-level executeSql.
        // In my opinion, I would only keep tables here if they are dependent on a primary table (one-to-many). 
        // It might be a good idea to move independent tables into top-level executeSql method
        //
        tx.executeSql('CREATE TABLE IF NOT EXISTS Feed (feedId, feedName, feedDesc, feedPrice)');
        // Through observation Comment most likely depends on Feed.
        // I would leave Feed and Comment together in this situation.
        tx.executeSql('CREATE TABLE IF NOT EXISTS Comment (commentId, feedId, commentDesc)');
        // Then, I would separate out User as it looks indepentent from the other tables.
        tx.executeSql('CREATE TABLE IF NOT EXISTS User (userId, userName, userPass, userAdmin)');
    }).then(() => {
        console.log("Tables created successfully");
    }).catch(error => {
        console.log(error);
    });
});


// Possible Solution
// Feed & Comment
db.executeSql('SELECT 1 FROM Feed LIMIT 1').then(() => {
    console.log("Database is ready ... executing query ...");
}).catch((error) =>{
    console.log("Received error: ", error);
    console.log("Database not yet ready ... populating data");
    db.transaction((tx) => {
        tx.executeSql('CREATE TABLE IF NOT EXISTS Feed (feedId, feedName, feedDesc, feedPrice)');
        // by observation, Feed -< Comment (one-to-many) relationship
        // so I would say keeping this here is a good idea
        tx.executeSql('CREATE TABLE IF NOT EXISTS Comment (commentId, feedId, commentDesc)');
    }).then(() => {
        console.log("Table created successfully");
    }).catch(error => {
        console.log(error);
    });
});
// User - indepentent table, create own SELECT>>catch
db.executeSql('SELECT 1 FROM Feed LIMIT 1').then(() => {
    console.log("Database is ready ... executing query ...");
}).catch((error) =>{
    console.log("Received error: ", error);
    console.log("Database not yet ready ... populating data");
    db.transaction((tx) => {
        tx.executeSql('CREATE TABLE IF NOT EXISTS User (userId, userName, userPass, userAdmin)');
    }).then(() => {
        console.log("Table created successfully");
    }).catch(error => {
        console.log(error);
    });
});

// Please let me know if this helps. Thanks.

Collectives™ on Stack Overflow

Creating multiple tables in SQLite using React Native SQLite Storage Module using Transaction function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related