Code Self Study Forum

Node and SQL questions

SOLVED! :slight_smile:

I am trying to use node to update a MySQL table without using express.
What I want to do is query my table. Then loop through every item and then convert several of the serving sizes into a JSON object.

After that I want to update the table with the new JSON object, but I want to do that in the loop so I am sure I am updating it for the right item.

Everything is working well so far, except I can’t seem to be able to call the UPDATE statement from the loop. I tried to put it in a function, but it doesn’t work.

I tried the update statement outside of the function and the loop and it works. (But it won’t work in the loop either)

How can I both select and update to a table with Node and MySQL?

Here is what I have so far:

require(“dotenv”).config();
const mysql = require(“mysql”);

// Prepare to connect to MySQL with your secret environment variables
const connection = mysql.createConnection({
    host: process.env.MYSQL,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DB,
});


const sql = "SELECT * FROM MYTABLE where fdc_id=170567";

connection.query(sql, function (err, results, fields) {
    if (err) throw err;
    

for (i=0; i < results.length; i++) 
    {   
        console.log(results[i].fdc_id);
        servingsize={};
        if (results[i].GmWt_1) {servingsize["wt1"] = [results[i].GmWt_1, results[i].GmWt_Desc1];}
        if (results[i].GmWt_2) {servingsize["wt2"] = [results[i].GmWt_2, results[i].GmWt_Desc2];}
        servingsize["100g"] = [100, "100 grams"];
        console.log(servingsize);
        servUpdate(servingsize);
    } // End loop
   
});

function servUpdate(servingsize) {
    const sqlupdate = "UPDATE MAIN set servingsizes='{ \"wt5\": [500,\"500c\"] }' where fdc_id=170567";
    connection.query(sqlupdate, function (err, result) {
      if (err) throw err;
      console.log(result.affectedRows + " record(s) updated");
    });
}

connection.end();

Thanks for any help! :slight_smile:

Solved it! The DB connection was getting closed too soon. So moving connection.end();

to

 } // End loop
    connection.end();
});

Let the function execute! :slight_smile:

1 Like

That looks like the first example from the tutorial that was a script that ran once and then exited. If you look at the second example from the tutorial (under the header “Using Express”), the code uses pool instead of connection. It should then manage the database connections for you.

You can use this file as a guide. Change all of the instances of connection to pool and remove the connection.end() line. I think that will work. :slight_smile:

1 Like

Hmm, it seems even with pool Node is running out of memory if I try to loop through 200,000 + rows:


<--- Last few GCs --->
on[24184:0x3d6d950]     6389 ms: Mark-sweep 1396.7 (1431.1) -> 1396.6 (1435.6) MB, 78.8 / 0.0 ms  (+ 48.8 ms in 62 steps since start of marking, biggest step 9.1 ms, walltime since start of marking 143 ms) (average mu = 0.187, current mu = 0.116) allocation[24184:0x3d6d950]     6638 ms: Mark-sweep 1400.7 (1435.6) -> 1396.8 (1435.1) MB, 236.5 / 0.0 ms  (+ 0.0 ms in 58 steps since start of marking, biggest step 0.0 ms, walltime since start of marking 249 ms) (average mu = 0.105, current mu = 0.050) allocation

<--- JS stacktrace --->

==== JS stack trace =========================================

    0: ExitFrame [pc: 0x3e1cde95be1d]
    1: StubFrame [pc: 0x3e1cde95731d]
    2: StubFrame [pc: 0x3e1cde958168]
Security context: 0x17f556e9e6e9 <JSObject>
    3: new Uint8Array(aka Uint8Array) [0x17f556e93819](this=0x36c326f02801 <the_hole>,0x15aaabc797f9 <ArrayBuffer map = 0xe6201105591>)
    4: ConstructFrame [pc: 0x3e1cde90d145]
    5: StubFrame [pc: 0x3e1cdec26d11]
    6: new FastBuffer(aka FastBuffer) [0xb4bfc6779c9] [buffer.js:79...

FATAL ERROR: Ineffective mark-compacts near heap limit Allocation failed - JavaScript heap out of memory
 1: 0x8fa0c0 node::Abort() [node]
 2: 0x8fa10c  [node]
 3: 0xb0026e v8::Utils::ReportOOMFailure(v8::internal::Isolate*, char const*, bool) [node]
 4: 0xb004a4 v8::internal::V8::FatalProcessOutOfMemory(v8::internal::Isolate*, char const*, bool) [node]
 5: 0xef49b2  [node]
 6: 0xef4ab8 v8::internal::Heap::CheckIneffectiveMarkCompact(unsigned long, double) [node]
 7: 0xf00b92 v8::internal::Heap::PerformGarbageCollection(v8::internal::GarbageCollector, v8::GCCallbackFlags) [node]
 8: 0xf014c4 v8::internal::Heap::CollectGarbage(v8::internal::AllocationSpace, v8::internal::GarbageCollectionReason, v8::GCCallbackFlags) [node]
 9: 0xf04131 v8::internal::Heap::AllocateRawWithRetryOrFail(int, v8::internal::AllocationSpace, v8::internal::AllocationAlignment) [node]
10: 0xecd5b4 v8::internal::Factory::NewFillerObject(int, bool, v8::internal::AllocationSpace) [node]
11: 0x116d73e v8::internal::Runtime_AllocateInNewSpace(int, v8::internal::Object**, v8::internal::Isolate*) [node]
12: 0x3e1cde95be1d 
Aborted (core dumped)

Looks like the solution is to increase the max memory node can use when running the program from command line:
node --max-old-space-size=4096 yourFile.js

Did that work?

Are you looping through 200,000 rows just once, or is that going in a controller? There might be an easier way. :slight_smile:

It is just a one time thing to build JSON out of the serving sizes and update the table for every food.

But now I have questions around modifying JSON, so I will start a new thread.

I am running into a problem with the API request to query names.
The result I would like to display on the API endpoint is a simple list of names with no special characters like quotes.

So something like this:

Ham, Tea, Kale, Peas, Salt, Okra, Taro, Oats, Pears, Steak, Pizza, Basil, Vodka, Alpen, Rowal, Syrah, Sugar, Apples, Salmon, Quinoa, Garlic, Grapes, Mangos, Papaya, Pecans

However, the way the API is set up, it wants to resolve with JSON, so quotes get added to the beginning and end of the string like this:

"Ham, Tea, Kale, Peas, Salt, Okra, Taro, Oats, Pears, Steak, Pizza, Basil, Vodka, Alpen, Rowal, Syrah, Sugar, Apples, Salmon, Quinoa, Garlic, Grapes, Mangos, Papaya, Pecans"

I want to return the string without quotes.

Here is the API request/response



function autoCompleteQuery(req, res) {
    const keyword = req.params.q;
    db.getAutoComplete(keyword)
    .then(data => res.json(data)) // if successful
    .catch(err => res.status(500).json(err)); // if error
}

And the SQL query:

function getAutoComplete(keyword) {
    return new Promise((resolve, reject) => {
        // This is an alternate way to sanitize user input for the query
        const sql =
            "SELECT `name` FROM NAMES WHERE name LIKE " +
            pool.escape(`%${keyword}%`) + "LIMIT 25";

        pool.query(sql, [keyword], function (err, results, fields) {
            if (err) {
                return reject(err);
            }
                
                var i; 
                var output=results[0]["name"]; //Get first name, then add commas
                for (i = 1; i < results.length; i++) {
                    output += ","+results[i]["name"];
                }
                
                console.log(output);
                return resolve(output);
            

        });
    });
}

I loop through the SQL result to make a new array since a JSON object gets return from the query, such as:

[{"name":"Ham"},{"name":"Peas"}...]

So I am confused what is the best way to display a simple comma separated list of names…
Should I pass JSON from the SQL query to the API controller and process it there in a loop? Or did I get the name values in a stupid way?

Happy for any input! :slight_smile:

res (or response if you named it that) is an object that has a few methods for sending back data. The res.send method should be able to do it.

Here are the most common ways to send things back to the browser.

// Send back a rendered HTML template
res.render("templateName", data);

// Send back JSON
res.json(aJavaScriptObject);

// Send back anything
res.send("some,list,of,words");

JSON is always a string and the fields have double quotes on them. That’s why res.json is adding the quotes.

2 Likes

Awesome, res.send works! Thanks Josh! :slight_smile: