{"action":"opened","issue":{"url":"https://api.github.com/repos/tediousjs/node-mssql/issues/710","repository_url":"https://api.github.com/repos/tediousjs/node-mssql","labels_url":"https://api.github.com/repos/tediousjs/node-mssql/issues/710/labels{/name}","comments_url":"https://api.github.com/repos/tediousjs/node-mssql/issues/710/comments","events_url":"https://api.github.com/repos/tediousjs/node-mssql/issues/710/events","html_url":"https://github.com/tediousjs/node-mssql/issues/710","id":363403556,"node_id":"MDU6SXNzdWUzNjM0MDM1NTY=","number":710,"title":"Request ETIMEOUT with long running stored procedure depite rasing timeout to 1 Day","user":{"login":"markddrake","id":7787425,"node_id":"MDQ6VXNlcjc3ODc0MjU=","avatar_url":"https://avatars0.githubusercontent.com/u/7787425?v=4","gravatar_id":"","url":"https://api.github.com/users/markddrake","html_url":"https://github.com/markddrake","followers_url":"https://api.github.com/users/markddrake/followers","following_url":"https://api.github.com/users/markddrake/following{/other_user}","gists_url":"https://api.github.com/users/markddrake/gists{/gist_id}","starred_url":"https://api.github.com/users/markddrake/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/markddrake/subscriptions","organizations_url":"https://api.github.com/users/markddrake/orgs","repos_url":"https://api.github.com/users/markddrake/repos","events_url":"https://api.github.com/users/markddrake/events{/privacy}","received_events_url":"https://api.github.com/users/markddrake/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-09-25T04:15:13Z","updated_at":"2018-09-25T04:15:13Z","closed_at":null,"author_association":"NONE","body":"`\"use strict\";\r\n \r\nconst fs = require('fs');\r\nconst common = require('./common.js');\r\nconst sql = require('mssql');\r\nconst stream = require('stream');\r\n\t \r\nasync function loadStagingTable(dbConn,stagingTable,dumpFilePath) {    \t\r\n\r\n  async function dropStagingTable(request,target) {\r\n    try {\r\n      const statement = `drop table \"${target.schema === undefined ? target.table_name : target.schema + '\".\"' + target.tableName}\"`;\r\n      const results = await request.batch(statement)\r\n      return results;\r\n    } catch (e) {}\r\n  }\r\n\t\r\n  async function createStagingTable(request,target) {\r\n   const statement = `create table \"${target.schema === undefined ? target.table_name : target.schema + '\".\"' + target.tableName}\" (\"${target.column_name}\" NVARCHAR(MAX))`;\r\n   const results = await request.batch(statement)\r\n   return results;\r\n  } \r\n\r\n  async function initializeStagingTable(request,target) {\r\n   const statement = `insert into \"${target.schema === undefined ? target.table_name : target.schema + '\".\"' + target.tableName}\" values ('')`;\r\n   const results = await request.batch(statement)\r\n   return results;\r\n  } \r\n  \r\n  class fileLoader extends stream.Writable {\r\n\t  \r\n    constructor(request,statement,options) {\r\n\t  super(options)\r\n\t  this.request = request;\r\n\t  this.statement = statement;\r\n\t}\r\n\t   \r\n    async _write(chunk, encoding, next) {\r\n      try {\r\n\t\tconst data = chunk.toString()\r\n\t\tvar results = await this.request.input('data',sql.NVARCHAR,data).batch(this.statement);\r\n\t\tnext(null,results);\r\n      } catch (e) {\r\n\t\tnext(e);\r\n\t  }  \t   \r\n\t} \r\n  }\t  \r\n\r\n  const request = new sql.Request(dbConn);\t  \r\n  const statement = `update \"${stagingTable.schema === undefined ? stagingTable.table_name : stagingTable.schema + '\".\"' + stagingTable.tableName}\" set \"${stagingTable.column_name}\" .write(@data,null,null)`;\r\n     \r\n  const inputStream = fs.createReadStream(dumpFilePath);\r\n  const loader = new fileLoader(request,statement);\r\n  \r\n  let results = await dropStagingTable(request,stagingTable);\r\n  results = await createStagingTable(request,stagingTable);\r\n  results = await initializeStagingTable(request,stagingTable);\r\n\r\n  let startTime = undefined;\r\n  return new Promise(function(resolve, reject) {\r\n\tloader.on('finish',function(chunk) {resolve(new Date().getTime() - startTime)})\r\n\tinputStream.on('error',function(err){reject(err)});\r\n\tloader.on('error',function(err){reject(err)});\r\n\tstartTime = new Date().getTime();\r\n    inputStream.pipe(loader);\r\n  })\r\n}\r\n\r\nasync function verifyDataLoad(dbConn,stagingTable) {    \t\r\n    const statement = `select ISJSON(\"${stagingTable.column_name}\") \"VALID_JSON\" from \"${stagingTable.schema === undefined ? stagingTable.table_name : stagingTable.schema + '\".\"' + stagingTable.tableName}\"`;\r\n\tconst startTime = new Date().getTime();\r\n    const results = await dbConn.query(statement);\r\n\tconsole.log(`${new Date().toISOString()}: Upload succesful: ${results.recordsets[0][0].VALID_JSON === 1}. Elapsed time ${new Date().getTime() - startTime}ms.`);\r\n\treturn results;\r\n}\r\n\r\nasync function processStagingTable(dbConn,schema) {    \t\r\n  const request = new sql.Request(dbConn);\t  \r\n  request.input('TARGET_DATABASE',sql.VARCHAR,schema);\r\n  const results = await request.execute('IMPORT_JSON');\r\n  return results.recordsets[0][0];\r\n}\r\n\r\nasync function createTargetDatabase(dbConn,schema) {    \t\r\n\tconst sqlStatement = `CREATE DATABASE IF NOT EXISTS \"${schema}\"`;\t\t\t\t\t   \r\n\tconst results = await query(dbConn,sqlStatement,schema);\r\n\treturn results;\r\n}\r\n\r\nasync function main(){\r\n\t\r\n  let parameters = undefined;\r\n  let logWriter = process.stdout;\r\n\t\r\n  try {\r\n\r\n    let results = undefined;\r\n    parameters = common.processArguments(process.argv,'import');\r\n\r\n\tif (parameters.LOGFILE) {\r\n\t  logWriter = fs.createWriteStream(parameters.LOGFILE);\r\n    }\r\n\t\r\n\tconst config = {\r\n      server    : parameters.HOSTNAME\r\n     ,user      : parameters.USERNAME\r\n     ,database  : parameters.DATABASE\r\n     ,password  : parameters.PASSWORD\r\n     ,port      : parameters.PORT\r\n\t ,options: {\r\n        encrypt: false // Use this if you're on Windows Azure\r\n      }\r\n    }\r\n\r\n    const dbConn = new sql.ConnectionPool(config);\r\n\tawait dbConn.connect()\r\n\tawait dbConn.query(`SET QUOTED_IDENTIFIER ON`);\r\n\t\r\n    const schema = parameters.TOUSER;\r\n\t// results = await createTargetDatabase(dbConn,schema);\r\n\t\r\n    const dumpFilePath = parameters.FILE;\t\r\n\tconst stats = fs.statSync(dumpFilePath)\r\n    const fileSizeInBytes = stats.size\r\n\r\n\tconst stagingTable = { table_name : 'JSON_STAGING', column_name : 'DATA'}\r\n\r\n\tconst startTime = new Date().getTime();\r\n\tresults = await loadStagingTable(dbConn,stagingTable,dumpFilePath);\r\n\tconst elapsedTime = new Date().getTime() - startTime;\r\n    logWriter.write(`${new Date().toISOString()}: Import Data file \"${dumpFilePath}\". Size ${fileSizeInBytes}. Elapsed Time ${elapsedTime}ms.  Throughput ${Math.round((fileSizeInBytes/elapsedTime) * 1000)} bytes/s.\\n`)\r\n\r\n\tresults = await verifyDataLoad(dbConn,stagingTable);\r\n\tresults = await processStagingTable(dbConn,schema);\r\n\tconst jsonKey = Object.keys(results)[0];\r\n\tresults = JSON.parse(results[jsonKey])\r\n\t\r\n\tresults.forEach( function(logEntry) {\r\n  \t                  logWriter.write(`${new Date().toISOString()}: Table \"${logEntry.tableName}\". Rows ${logEntry.rowCount}. Elapsed Time ${Math.round(logEntry.elapsedTime)}ms. Throughput ${Math.round((logEntry.rowCount/Math.round(logEntry.elapsedTime)) * 1000)} rows/s.\\n`)\r\n\t})\r\n\r\n\tawait dbConn.close();\r\n\tlogWriter.write('Import operation successful.\\n');\r\n    if (logWriter !== process.stdout) {\r\n\t  console.log(`Import operation successful: See \"${parameters.LOGFILE}\" for details.`);\r\n    }\r\n  } catch (e) {\r\n    if (logWriter !== process.stdout) {\r\n\t  console.log(`Import operation failed: See \"${parameters.LOGFILE}\" for details.`);\r\n  \t  logWriter.write('Import operation failed.\\n');\r\n\t  logWriter.write(e.stack);\r\n    }\r\n\telse {\r\n    \tconsole.log('Import operation Failed.');\r\n        console.log(e);\r\n\t}\r\n\tif (sql !== undefined) {\r\n      await sql.close();\r\n\t}\r\n  }\r\n  \r\n  if (logWriter !== process.stdout) {\r\n\tlogWriter.close();\r\n  }  \r\n  process.exit()\r\n}\r\n\r\nmain();\r\n`\r\nEven though I believe the time has been set to 24 hours I still get this when calling the IMPORT_JSON stored procedure\r\n`C:\\Development\\MSSQL>node node\\import --USERNAME=sa --PASSWORD=oracle --HOSTNAME=192.168.1.250 --DATABASE=clone --TOUSER=\\\"IX\\\" --FILE=c:\\\\Development\\\\MySQL\\\\JSON\\\\oracle\\\\IX.json\r\n2018-09-25T04:11:58.276Z: Import Data file \"c:\\\\Development\\\\MySQL\\\\JSON\\\\oracle\\\\IX.json\". Size 55624. Elapsed Time 18ms.  Throughput 3090222 bytes/s.\r\n2018-09-25T04:11:58.317Z: Upload succesful: true. Elapsed time 6ms.\r\nImport operation Failed.\r\n{ RequestError: JSON text is not properly formatted. Unexpected character '.' is found at position 288.\r\n    at handleError (C:\\Development\\MSSQL\\node\\node_modules\\mssql\\lib\\tedious.js:862:15)\r\n    at Connection.emit (events.js:182:13)\r\n    at Parser.<anonymous> (C:\\Development\\MSSQL\\node\\node_modules\\tedious\\lib\\connection.js:611:16)\r\n    at Parser.emit (events.js:182:13)\r\n    at Parser.<anonymous> (C:\\Development\\MSSQL\\node\\node_modules\\tedious\\lib\\token\\token-stream-parser.js:54:15)\r\n    at Parser.emit (events.js:182:13)\r\n    at addChunk (C:\\Development\\MSSQL\\node\\node_modules\\readable-stream\\lib\\_stream_readable.js:291:12)\r\n    at readableAddChunk (C:\\Development\\MSSQL\\node\\node_modules\\readable-stream\\lib\\_stream_readable.js:278:11)\r\n    at Parser.Readable.push (C:\\Development\\MSSQL\\node\\node_modules\\readable-stream\\lib\\_stream_readable.js:245:10)\r\n    at Parser.Transform.push (C:\\Development\\MSSQL\\node\\node_modules\\readable-stream\\lib\\_stream_transform.js:148:32)\r\n  code: 'EREQUEST',\r\n  number: 13609,\r\n  lineNumber: 46,\r\n  state: 4,\r\n  class: 16,\r\n  serverName: 'OAKTHWAITE-DB',\r\n  procName: 'IMPORT_JSON',\r\n  originalError:\r\n   { Error: JSON text is not properly formatted. Unexpected character '.' is found at position 288.\r\n       at handleError (C:\\Development\\MSSQL\\node\\node_modules\\mssql\\lib\\tedious.js:860:19)\r\n       at Connection.emit (events.js:182:13)\r\n       at Parser.<anonymous> (C:\\Development\\MSSQL\\node\\node_modules\\tedious\\lib\\connection.js:611:16)\r\n       at Parser.emit (events.js:182:13)\r\n       at Parser.<anonymous> (C:\\Development\\MSSQL\\node\\node_modules\\tedious\\lib\\token\\token-stream-parser.js:54:15)\r\n       at Parser.emit (events.js:182:13)\r\n       at addChunk (C:\\Development\\MSSQL\\node\\node_modules\\readable-stream\\lib\\_stream_readable.js:291:12)\r\n       at readableAddChunk (C:\\Development\\MSSQL\\node\\node_modules\\readable-stream\\lib\\_stream_readable.js:278:11)\r\n       at Parser.Readable.push (C:\\Development\\MSSQL\\node\\node_modules\\readable-stream\\lib\\_stream_readable.js:245:10)\r\n       at Parser.Transform.push (C:\\Development\\MSSQL\\node\\node_modules\\readable-stream\\lib\\_stream_transform.js:148:32)\r\n     info:\r\n      { number: 13609,\r\n        state: 4,\r\n        class: 16,\r\n        message:\r\n         'JSON text is not properly formatted. Unexpected character \\'.\\' is found at position 288.',\r\n        serverName: 'OAKTHWAITE-DB',\r\n        procName: 'IMPORT_JSON',\r\n        lineNumber: 46,\r\n        name: 'ERROR',\r\n        event: 'errorMessage' } },\r\n  name: 'RequestError',\r\n  precedingErrors: [] }`\r\n\r\nApologies in advance if I am missing something obvious....\r\n\r\nThe code basically uploads a largish JSON (60Mb) to the server and then uses a stored procedure and openjson to populate relational tables from the uploaded document.\r\n\r\n\r\n"}}
{"actor":{"display_login":"markddrake"}}