Creating multiple SQL statement queries for app.POST server

Question

I am currently stuck on trying to run multiple insert SQL statements on my app.post function in server.js. This is for a Video Game database. First, I was trying the method that is commented out but would receive an error saying "column index out of range" when trying to insert a new game. So then I tried the uncommented functions to see if I could do one at a time instead, but the error I receive now is "cannot set headers after they are sent to the client"

//Server.js

// videogames.insertNewGame(data.gameTitle, data.year, data.genre, data.publisher, data.developer, data.platformCB)
    //     .then(insertNew => {
    //         res.json({
    //             "message":`Success! New Game Added`,
    //             "data":insertNew
    //         })
    //     })
    //     .catch(err => {
    //         res.status(400).json({"error":err.message})
    //     })

    videogames.insertNewGame(data.gameTitle, data.year, data.genre)
        .then(insertNew => {
            res.json({
                "message":`Success! New Game Added`,
                "data":insertNew
            })
        })
        .catch(err => {
            res.status(400).json({"error":err.message})
        })
    videogames.updateNewGame(data.gameTitle, data.platformCB)
        .then(insertNew => {
            res.json({
                "message":`Success! Updated exKey`,
                "data":insertNew
            })
        })
        .catch(err => {
            res.status(400).json({"error":err.message})
        })

//database.js

// insertNewGame(gTitle, gYear, gGenre, pPub, dDev, pfCB){
    //     return this.all(
    //         "INSERT INTO Games (g_title, g_year, g_genre) VALUES(?, ?, ?); " + 
    //         "UPDATE Games SET g_exkey = (SELECT pf_exkey FROM Platform WHERE pf_system = ?) WHERE g_title = ?; " +
    //         "INSERT INTO Publisher (p_name) VALUES(?); " +
    //         "INSERT INTO Developer (d_name) VALUES(?); " +
    //         "INSERT INTO Contracts (c_gameID, c_pubkey, c_devkey) " + 
    //             "SELECT g_gameID, p_pubkey, d_devkey " +
    //                     "FROM Games, Publisher, Developer " +
    //                     "WHERE g_title = ? AND " +
    //                         "p_name = ? AND " +
    //                         "d_name = ?", [gTitle, gYear, gGenre, pfCB, gTitle, pPub, dDev, gTitle, pPub, dDev])
    // }

    insertNewGame(gTitle, gYear, gGenre){
        return this.all(
            "INSERT INTO Games (g_title, g_year, g_genre) VALUES(?, ?, ?)", [gTitle, gYear, gGenre]
        )
    }
    updateNewGame(gTitle, pCB){
        return this.all(
            "UPDATE Games SET g_exkey = (SELECT pf_exkey FROM Platform WHERE pf_system = ?) WHERE g_title = ?", [gTitle, pCB]
        )
    }
    insertPublisher(pPub){

"this.all" calls this function

//database.js

class DB{
    constructor(){
        this.db = new sqlite3.Database(DBSOURCE, (err) => { 
            if(err){    //callback 
                // Cannot open Database
                console.error(err.message)
                throw err
            }else{
                console.log('Connnected to the SQLite database.')
            }
        })
    }

    all(sql, params = []) {
        return new Promise((resolve, reject) => {
            this.db.all(sql, params, (err, rows) => {
                if(err){
                    console.log('Error running sql: ' + sql)
                    console.log(err)
                    reject(err)
                }   else{
                    resolve(rows)
                }
            })
        })
    }
    ...
}

Just to clarify, I get all of my data from my HTML form data section and then set it to what you see here, "data.publisher, data.developer, ..."

I cannot do that since I am a new user and Stack overflow requires a certain amount of reputation points. — GameOn_Jon, Apr 16 '21 at 01:56
In that case, I think, at least, code samples can be added by new users. — FerdousTheWebCoder, Apr 16 '21 at 03:24
There's nothing wrong with your first function that you want to use. Usually that error `column out of range` means that you're trying to insert to a column that doesn't exist (ie, you have a typo are are referencing a different table by accident). Also, obviously, you'd need to update your `videogames.insertNewGame()` function with the new INSERT query and to accept all the parameters you want to pass to it. — rook218, Apr 16 '21 at 12:28
It would also be best practice to break that one huge query into multiple queries - it will be easier to debug and read. — rook218, Apr 16 '21 at 12:44
@GameOn_Jon I think the issue of this error "cannot set headers after they are sent to the client" lies in your function `this.db.all()`. According to your code sample, you called it in `database.js`. Can you also show the code where `this.db.all()` is defined? — FerdousTheWebCoder, Apr 17 '21 at 05:10
@rook218 You were right. When I was passing the values to my function in database.js, I used them in the same order, when the SQL code required it reversed — GameOn_Jon, Apr 19 '21 at 12:26
@BattleHawk just updated it, not sure if this is what you're asking for. — GameOn_Jon, Apr 19 '21 at 12:28

score 0 · Answer 1 · answered Apr 26 '21 at 22:30

So I think I figured it out. The reason that I keep getting "cannot set headers after they are sent to the client" is because I keep trying to res.json after every function call. The fix I did for this was remove all the .then statements with res.json and moved it to only the last function. res.json is basically a finishing statement for the request call which is why the headers are sent, and cannot be done again after that. Heres the solution which works

videogames.insertNewGame(data.gameTitle, data.year, data.genre)
        .catch(err => {
            res.status(400).json({"error":err.message})
        })
videogames.updateNewGame(data.gameTitle, data.platformCB)
    .catch(err => {
        res.status(400).json({"error":err.message})
    })
videogames.insertPublisher(data.publisher)
    .catch(err => {
        res.status(400).json({"error":err.message})
    })
videogames.insertDeveloper(data.developer)
    .catch(err => {
        res.status(400).json({"error":err.message})
    })
videogames.insertContract(data.gameTitle, data.publisher, data.developer)
    .then(() => {
        res.json({
            "message":`Success! inserted new game`,
            "data":data.gameTitle
        })            
    })
    .catch(err => {
        res.status(400).json({"error":err.message})
    })

Creating multiple SQL statement queries for app.POST server

1 Answers1