0

I am in the process of trying to copy a table from SQL server to MySQL using a NodeJS API. I need to function to run roughly every 30 minutes or so (I am using setInterval to do this).

I am encountering an issue when trying to write to the MySQL table, the column values are coming back null so nothing is being written and then an error is thrown. However, when I console.log that same data, I receive the entire table from SQL Server. I will attach the code below - there is only one file in the API so I will include it in its entirety.

import { createPool } from "mysql2";
import pkg from "mssql";
const { connect, query } = pkg;
const sql = pkg;

// MySQL Server
export const db = createPool({
  host: "IP Address",
  user: "root",
  password: "Password",
  database: "demo",
  port: "3306",
  connectionLimit: 300,
  waitForConnections: true,
  multipleStatements: true,
});

db.getConnection((err, connection) => {
  if (err) throw err;
  console.log("Database connected successfully");
  connection.release();
});

// SQL Server
const config = {
  user: "user",
  password: "Password",
  server: "Server Name",
  database: "demo",
  port: 1433,
  options: {
    trustedConnection: true,
    encrypt: true,
    enableArithAbort: true,
    trustServerCertificate: true,
  },
};

// Transfer function
const Transfer = () => {
  sql.connect(config, function (err) {
    if (err) console.log(err);

    let sqlRequest = new sql.Request();
    let sqlQuery = "Select * From access";

    sqlRequest.query(sqlQuery, function (err, data) {
      if (err) console.log(err);
      if (data) {
        db.query(
          "INSERT IGNORE INTO attendance ID = ?, dateTime = ?, date = ?, time = ?, direction = ?, deviceName = ?, deviceSerial = ?, personName = ?, cardNumber = ?",
          [
            data.userID,
            data.dateTime,
            data.date,
            data.time,
            data.direction,
            data.deviceName,
            data.deviceSerial,
            data.personName,
            data.cardNumber,
          ],
          (err) => {
            if (err) {
              console.log(err);
            } else {
              return;
            }
          }
        );
      }
    });
  });
};

setInterval(Transfer, 2000);

const port = 3001;
app.listen(port, () => console.log(`Listening on port ${port}`));

The table are exactly the same on both servers, even down to the datatype.

Please let me know if I can provide any other details. I would appreciate any help/guidance with this!

cameronErasmus
  • 180
  • 8
  • `an error is thrown`...it would be likely to help a lot if you tell us what the error is. Although I'd expect one problem could be that `data` in `qlRequest.query(sqlQuery, function (err, data) {` is likely to be an array containing multiple rows, whereas your code appears to treat it like it's a single object. There's no attempt to loop through the data and insert each row you've received from the SQL Server recordset. – ADyson Oct 21 '22 at 11:04
  • 1
    BTW there is almost certainly a better / more efficient way to do bulk data transfer like this, especially if there are a lot of rows. e.g. you could use a SSIS package, or export from SQL to flat file and then import using LOAD DATA into MySQL, something like that. Any reason you chose this specific approach? – ADyson Oct 21 '22 at 11:04
  • Anything is **several** orders of magnitude better than this. As in 10^4 times faster and easier, and that's a very conservative estimate. I just copied 800K rows between databases over a VPN in about 1 minute. – Panagiotis Kanavos Oct 21 '22 at 11:06
  • Yeah so the sqlQuery returns an array @ADyson – cameronErasmus Oct 21 '22 at 11:07
  • @PanagiotisKanavos how did you do it? I’m just looking for a simple solution that efficient and robust. – cameronErasmus Oct 21 '22 at 11:08
  • `Yeah so the sqlQuery returns an array`...so why aren't you looping it, then? And again, what exact error are you getting? – ADyson Oct 21 '22 at 11:09
  • @ADyson I'll loop it with a forEach - thanks for the tip. The exact error I get is "Error: { code: 'ER_PARSE_ERROR', errno: 1064, sqlState: '42000', sqlMessage: "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 'ID = NULL, dateTime = NULL, date = NULL, time = NULL, direction = NULL, deviceNa' at line 1", sql: 'INSERT IGNORE INTO attendance ID = NULL, dateTime = NULL, date = NULL, time = NULL, direction = NULL, deviceName = NULL, deviceSerial = NULL, personName = NULL, cardNumber = NULL' }" – cameronErasmus Oct 21 '22 at 11:16
  • 1
    Ah. That's because you forgot the `SET` keyword. Take a look at https://stackoverflow.com/questions/861722/mysql-insert-into-table-values-vs-insert-into-table-set (for example). And obviously the values are all `null` because `data` is an array and doesn't directly contain any of the properties you're trying to read from it. – ADyson Oct 21 '22 at 11:21
  • Welp... I don't know whether I should laugh hysterically or cry incessantly. The function works now... Thank you so much @ADyson. I owe you a coffee and an apology for wasting your time! – cameronErasmus Oct 21 '22 at 11:26

0 Answers0