javascript - 循环通过 api 请求时插入失败仅执行第一个查询

Question

我试图从 sql server 中的表中检索名称列表，然后使用 axios 和 node 一次发出 100 个或更少名称的 API 请求，然后我试图将返回的数据插入到新的 sql 表中.虽然我已经能够让它工作到 API 调用，但插入部分对我来说还没有用。当我完成有限数量的插入 4 或 5 时，它只插入了第一个查询，而当我尝试更大的插入时，它完全失败了。

SQL 查询

const selectStatment = "SELECT [CandidateId] AS id, ( CASE LEN(REPLACE([CandidateName],' ','')) WHEN LEN([CandidateName]) - 1 then PARSENAME(REPLACE([CandidateName],' ','.'), 2) ELSE PARSENAME(REPLACE([CandidateName],' ','.'), 3) END ) AS firstName, PARSENAME(REPLACE([CandidateName],' ','.'), 1) AS lastName ";
const cleanWherequery = 'WHERE NOT firstName IS NULL OR NOT firstName IS NULL ANd NOT lastName IS NULL';
const sqlTable = '##apiTable';
const tempQuery = `DROP TABLE IF EXISTS ##apiTable, ##clean; ${selectStatment} INTO ##clean FROM [dbo].[DimCandidate]; SELECT * INTO ${sqlTable} FROM ##clean ${cleanWherequery}`;
let insertValues = '';
const insertQuery = `INSERT INTO sql_GetGender (id, firstName, lastName, likelyGender, score, probabilityCalibrated) VALUES`;

API调用和插入

const getGender = (data) => axios.post('/api2/json/genderBatch', data)
.then(function(data){
  return res = data.data;
})
.then(function(res){
  for(let index = 0; index < res.personalNames.length; index++){
    if((index + 1) == res.personalNames.length){
      insertValues += `(${res.personalNames[index]['id']}, '${res.personalNames[index]['firstName']}', '${res.personalNames[index]['lastName']}', '${res.personalNames[index]['likelyGender']}', ${res.personalNames[index]['score']}, ${res.personalNames[index]['probabilityCalibrated']});`;
       let pass = insertValues;
       return pass;
       insertValues = ' ';
    }else{
      insertValues += `(${res.personalNames[index]['id']}, '${res.personalNames[index]['firstName']}', '${res.personalNames[index]['lastName']}', '${res.personalNames[index]['likelyGender']}', ${res.personalNames[index]['score']}, ${res.personalNames[index]['probabilityCalibrated']}),`;
    }
  }
  console.log(insertValues);
}).then(function(res){
  new sql.Request().query(`${insertQuery} ${res}`, (err, result) => {
        console.dir(result)
    })
})
.catch(function(e){
  console.log(e);
});

数据库调用

(async function(){
  try {
    let pool = await sql.connect(dbConfig);
    let createTemp = await pool.request()
                               .query(`${tempQuery}`);

    let fetchQuery = await pool.request()
                                 .query(`SELECT TOP 3 id, firstName, lastName FROM ${sqlTable}`);

    let resData = await fetchQuery.recordset;

    let count = 0

    let elemlength = resData.length;

    resData.forEach(function(elem, i){
      printData.push(elem)
      count += 1;
      if(printData.length == 2){
        console.log(`${printData.length} Reached`);
        let personalNamesJson = {
          'personalNames' : printData
        }
        getGender(personalNamesJson);
        printData = [];
      }else if(printData.length < 2 && count == elemlength){
        console.log(`${printData.length} was left`);
        let personalNamesJson = {
          'personalNames' : printData
        }
        getGender(personalNamesJson);
      }
    })

  } catch (e) {

    console.log(e);
    sql.close()

  } finally {
    sql.close()
    console.log('connection closed')
  }
})()

我期待这样的事情有一个不同的 rowAffected 数字{ 记录集: [], 记录集:未定义， 输出: {}， 受影响的行数:[ 4 ]

我在 2nd+ 插入的内容是未定义

Answer 1

您将插入值放入 insertValues 变量中，但之后从未使用它。您需要在第三个“then”子句中使用 ${insertValues} 而不是 ${res}。