0

I'm using MySQL with NodeJS with asyncs and awaits. I'm trying to get the last insertid from my inserted row but keep getting errors.

Here's the async function;

function makeDb( config ) {
  const connection = mysql.createConnection( config );  return {
    query( sql, args ) {
      return util.promisify( connection.query )
        .call( connection, sql, args );
    },
    close() {
      return util.promisify( connection.end ).call( connection );
    }
  };
}

And here's the code which is failing on the queries;

try {       
    if(tag1){

            row_b = await db.query( "SELECT tagid FROM tags WHERE tagname = ?", [tag1]);    
            const onetagid1 = row_b[0].tagid;               
                console.log('onetagid1 = ' + onetagid1);    
       
    if (row_b > 0){         
            row_c = await db.query("
                INSERT INTO entitytag (tagid1, audioid) VALUES (?,?) 
                ON DUPLICATE KEY UPDATE tagid1 = ?" [onetagid1, audioid, onetagid1]
            );              
    } else {    
            row_d = await db.query( 'INSERT IGNORE INTO tags (tagname) VALUES (?)', [tag1]);            
                var twotagid1 = row_d.insertId;             
                    console.log('twotagid1 2nd = ' + twotagid1); 
        
            row_e = await db.query(
                "INSERT INTO entitytag (tagid1, audioid) VALUES (?,?) 
                ON DUPLICATE KEY UPDATE tagid1 = ?" [twotagid1, audioid, twotagid1]
            );                  
    }  
        res.json('json success!');  
    }                   
}

And here's the error;

onetagid1 = 30
twotagid1 2nd = 0
ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'I' at line 1

The error is twotagid1 2nd = 0 which should not be zero.

Meggy
  • 1,491
  • 3
  • 28
  • 63
  • 1
    Good code indentation would help us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](https://www.php-fig.org/psr/psr-12/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. – RiggsFolly Jul 06 '21 at 16:15
  • 1
    Which line number is that error on, there are 2 INSERTS – RiggsFolly Jul 06 '21 at 16:16
  • twotagid1 2nd = 0. This should not be zero. – Meggy Jul 06 '21 at 16:17
  • https://stackoverflow.com/questions/31371079/retrieve-last-inserted-id-with-mysql – RiggsFolly Jul 06 '21 at 16:38
  • 1
    Does the `tags` table have a column defined as AUTO-INCREMENT ?? – RiggsFolly Jul 06 '21 at 16:47
  • You have asked several questions about `mysql` in `node` over the last few days and I didn't see which npm package specifically you are using. Please update your question to include which package you are using (`mysqljs/mysql`, or `sidorares/node-mysql2`, or something else?). The questions you have asked are covered in the documentation for these packages and we can point you to those resources if we know what package you are using. – dusthaines Jul 06 '21 at 17:50
  • I thought I had stated I was using NodeJS with MySQL. – Meggy Jul 06 '21 at 19:13
  • 1
    [There are many packages](https://www.npmjs.com/search?q=node%20mysql&ranking=popularity) that allow you to use MySQL with NodeJS. Which package(s) specifically are you using in your project (via `require()` in your code base). If you are using the most popular package `mysqljs/mysql`, it does not support `async` and `await` in the way you are using it in your examples. However, some other packages do. Please include all the necessary details others will need to help you. – dusthaines Jul 06 '21 at 19:44
  • My code says simply; const mysql = require( 'mysql' ); The async await code seems to work ok my other pages. – Meggy Jul 06 '21 at 20:27
  • In package.json I've got dependencies": { "body-parser": "^1.12.4", "express": "^4.17.1", "jsonwebtoken": "^5.4.1", "MD5": "~1.2.2", "mysql": "^2.18.1", – Meggy Jul 06 '21 at 20:28
  • Yes RiggsFolly the tag table has an auto-increment primary column called "tagid"; – Meggy Jul 06 '21 at 20:31
  • I figured out that each part needed res.json(''); to stop the code from continuing. As such I've isolated that the problem is not with the insertId but in the ON DUPLICATE KEY UPDATE statements. – Meggy Jul 06 '21 at 21:03
  • 1
    Now that you have confirmed you are using the `mysqljs/mysql` package - we can say with confidence that it does not include native support for `async` and `await`. So, if the code examples you provided 'sometimes work on other pages' then you must have tried to extend or modify the package via a module or class of your own making so it would support `async`. Is that the case? If so, you need to share those details as well because that is probably where your issue lies. Until you are proficient with a package's core features and functions you should avoid complicating matters with abstraction. – dusthaines Jul 07 '21 at 02:25
  • I've edited the async function into the original quesiton. Do you know if mysql2 has support for async? – Meggy Jul 07 '21 at 09:15
  • 1
    Yes, `sidorares/node-mysql2` includes support for asynchronous queries. [It is well explained and documented on the package repo and a number of examples are provided](https://github.com/sidorares/node-mysql2#using-promise-wrapper). – dusthaines Jul 07 '21 at 13:28
  • I've just swapped over to mysql2 and everything still seems to work including the new code which works for reasons I don't understand. – Meggy Jul 07 '21 at 21:24

1 Answers1

1

I'm not sure why this works when the other didn't. But I'll post it here hoping someone will be able to spot it;

try {

    if(tag1){   
    
        row_c = await db.query( "SELECT tagid FROM tags WHERE tagname = ?", [tag1]);    
    
    if (row_c.length > 0){  
        console.log('tag exists in database ');
            const tagid1 = row_c[0].tagid;
               console.log('tagid1 = ' + tagid1);

    row_f = await db.query(
           "INSERT INTO entitytag (tagid1, audioid) VALUES (?,?) 
               ON DUPLICATE KEY UPDATE tagid1 = ?", [tagid1, audioid, tagid1 ]);
    } else {
        console.log('tag does not exist in database ');
    
    row_d = await db.query( 'INSERT IGNORE INTO tags (tagname) VALUES (?)', [tag1]);    
        const tagInsertId = row_d.insertId; 
            console.log('tagInsertId = ' + tagInsertId);

    row_e = db.query(
        'INSERT INTO entitytag (tagid1, audioid) VALUES (?,?) 
             ON DUPLICATE KEY UPDATE tagid1 = ?', [tagInsertId, audioid, tagInsertId ]);
    }   
    }
         console.log('success!');
             res.json(tag1);    
    }
Meggy
  • 1,491
  • 3
  • 28
  • 63