tediousjs / tedious

Node TDS module for connecting to SQL Server databases.
http://tediousjs.github.io/tedious/
MIT License
1.58k stars 438 forks source link

RequestError: Error converting data type varchar to numeric. #919

Closed AlexanderTunick closed 5 years ago

AlexanderTunick commented 5 years ago

Hi! Stuck on the issue with tedious due to this error. Spent already almost half of the day to resolve it but not successfully.

I user knex.js query builder for making queries to DB. I send exactly the same query that works in DataGrip IDE. The only specificness of this query that in a condition I use a column with decimal values.

My Query: class.js

baseQuery(recordsType, fileTypes, supplementalDataTypeId = 7, descriptor = 'Total Minority') {
        const knex = this.knex;
        return knex('dbo.Person as p')
            .select()
            .countDistinct(recordsType)
            .leftJoin('dbo.OrganizationDetail as od', 'od.OrganizationUniqueID', 'p.OrganizationUniqueID')
            .leftJoin('dbo.PIDXrefLinks as x', 'x.OrganizationUniqueID', 'p.OrganizationUniqueID')
            .leftJoin('dbo.Organization as o', 'o.OrganizationUniqueID', 'p.OrganizationUniqueID')
            .leftJoin('dbo.PersonCombinedK12AndCollegeJobs as pc', 'p.personId', 'pc.PersonId')
            .leftJoin('dbo.OrganizationSupplementalData as dosd', function () {
                this.on('dosd.OrganizationUniqueID', '=', 'o.OrganizationUniqueID')
                    .on('dosd.SupplementalDataTypeId', knex.raw('?', [supplementalDataTypeId]))
                    .on('dosd.Descriptor', knex.raw('?', [descriptor]))
            })
            .leftJoin('dbo.Address as a', 'a.OrganizationUniqueID', 'o.OrganizationUniqueID')
            .whereIn('o.fileType', fileTypes)
            .andWhere('pc.JobID', 'in',
                knex('dbo.OMSJobCodePackages_BAL as a')
                    .select()
                    .distinct('b.jobId')
                    .innerJoin('valJob as b', function () {
                        this.on('a.historicAlphaCode', '=', 'b.historicAlphaCode')
                            .andOn('a.historicNumericCode', '=', 'b.historicNumericCode')
                    })
                    .whereIn('b.status', [0, 1, 3])
                    .andWhere('b.priority', '!=', '8')
            )
    }

test.js

it('New student selects ', async function () {
            csdbCount = await queries.baseQuery('p.personId', ['07'], 7, 'Asian')
                .andWhere(queries.knex.raw('CAST(dosd.value as DECIMAL(10,3)) between 20 and 30'));
            csdbCount = await csdbCount[0][''];
        });

Receiving error (with consoled query and bindings from knex)

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 7, 'Asian', '07', 0, 1, 3, '8' ],
  __knexQueryUid: 'e1d5a479-1f13-4128-8809-c458cb1d45f8',
  sql:
   'select count(distinct [p].[personId]) from [dbo].[Person] as [p] left join [dbo].[OrganizationDetail] as [od] on [od].[OrganizationUniqueID] = [p].[OrganizationUniqueID] left join [dbo].[PIDXrefLinks] as [x] on [x].[OrganizationUniqueID] = [p].[OrganizationUniqueID] left join [dbo].[Organization] as [o] on [o].[OrganizationUniqueID] = [p].[OrganizationUniqueID] left join [dbo].[PersonCombinedK12AndCollegeJobs] as [pc] on [p].[personId] = [pc].[PersonId] left join [dbo].[OrganizationSupplementalData] as [dosd] on [dosd].[OrganizationUniqueID] = [o].[OrganizationUniqueID] and [dosd].[SupplementalDataTypeId] = ? and [dosd].[Descriptor] = ? left join [dbo].[Address] as [a] on [a].[OrganizationUniqueID] = [o].[OrganizationUniqueID] where [o].[fileType] in (?) and [pc].[JobID] in (select distinct [b].[jobId] from [dbo].[OMSJobCodePackages_BAL] as [a] inner join [valJob] as [b] on [a].[historicAlphaCode] = [b].[historicAlphaCode] and [a].[historicNumericCode] = [b].[historicNumericCode] where [b].[status] in (?, ?, ?) and [b].[priority] != ?) and CAST(dosd.value as DECIMAL(10,3)) between 20 and 30' }

select count(distinct [p].[personId]) from [dbo].[Person] as [p] left join [dbo].[OrganizationDetail] as [od] on [od].[OrganizationUniqueID] = [p].[OrganizationUniqueID] left join [dbo].[PIDXrefLinks] as [x] on [x].[OrganizationUniqueID] = [p].[OrganizationUniqueID] left join [dbo].[Organization] as [o] on [o].[OrganizationUniqueID] = [p].[OrganizationUniqueID] left join [dbo].[PersonCombinedK12AndCollegeJobs] as [pc] on [p].[personId] = [pc].[PersonId] left join [dbo].[OrganizationSupplementalData] as [dosd] on [dosd].[OrganizationUniqueID] = [o].[OrganizationUniqueID] and [dosd].[SupplementalDataTypeId] = @p0 and [dosd].[Descriptor] = @p1 left join [dbo].[Address] as [a] on [a].[OrganizationUniqueID] = [o].[OrganizationUniqueID] where [o].[fileType] in (@p2) and [pc].[JobID] in (select distinct [b].[jobId] from [dbo].[OMSJobCodePackages_BAL] as [a] inner join [valJob] as [b] on [a].[historicAlphaCode] = [b].[historicAlphaCode] and [a].[historicNumericCode] = [b].[historicNumericCode] where [b].[status] in (@p3, @p4, @p5) and [b].[priority] != @p6) and CAST(dosd.value as DECIMAL(10,3)) between 20 and 30 - Error converting data type varchar to numeric.
RequestError: Error converting data type varchar to numeric.
    at handleError (node_modules/mssql/lib/tedious.js:566:15)
    at Parser.tokenStreamParser.on.token (node_modules/mssql/node_modules/tedious/lib/connection.js:716:12)
    at Parser.parser.on.token (node_modules/mssql/node_modules/tedious/lib/token/token-stream-parser.js:27:14)
    at addChunk (node_modules/mssql/node_modules/readable-stream/lib/_stream_readable.js:297:12)
    at readableAddChunk (node_modules/mssql/node_modules/readable-stream/lib/_stream_readable.js:279:11)
    at Parser.Readable.push (node_modules/mssql/node_modules/readable-stream/lib/_stream_readable.js:240:10)
    at Parser.Transform.push (node_modules/mssql/node_modules/readable-stream/lib/_stream_transform.js:139:32)
    at doneParsing (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:80:14)
    at token (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:48:5)
    at call.lineNumber (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:13:19)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:179:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readUInt32LE (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:176:10)
    at parser.readBVarChar.procName (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:12:90)
    at readBuffer.data (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:316:9)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:308:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readBuffer (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:305:10)
    at readUInt8.length (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:315:12)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:123:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readUInt8 (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:120:10)
    at Parser.readBVarChar (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:314:10)
    at parser.readBVarChar.serverName (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:11:22)
    at readBuffer.data (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:316:9)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:308:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readBuffer (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:305:10)
    at readUInt8.length (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:315:12)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:123:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readUInt8 (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:120:10)
    at Parser.readBVarChar (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:314:10)
    at parser.readUsVarChar.message (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:10:20)
    at readBuffer.data (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:325:9)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:308:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readBuffer (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:305:10)
    at readUInt16LE.length (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:324:12)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:147:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readUInt16LE (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:144:10)
    at Parser.readUsVarChar (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:323:10)
    at parser.readUInt8.clazz (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:9:18)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:123:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readUInt8 (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:120:10)
    at parser.readUInt8.state (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:8:16)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:123:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readUInt8 (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:120:10)
    at parser.readUInt32LE.number (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:7:14)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:179:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readUInt32LE (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:176:10)
    at parser.readUInt16LE (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:6:12)
    at awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:147:7)
    at Parser.awaitData (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:103:7)
    at Parser.readUInt16LE (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:144:10)
    at parseToken (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:5:10)
    at Object.errorParser (node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:45:3)
    at Parser.parseTokens (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:89:27)
    at Parser._transform (node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:66:12)
    at Parser.Transform._read (node_modules/mssql/node_modules/readable-stream/lib/_stream_transform.js:177:10)
    at Parser.Transform._write (node_modules/mssql/node_modules/readable-stream/lib/_stream_transform.js:164:83)
    at doWrite (node_modules/mssql/node_modules/readable-stream/lib/_stream_writable.js:405:139)
    at writeOrBuffer (node_modules/mssql/node_modules/readable-stream/lib/_stream_writable.js:394:5)
    at Parser.Writable.write (node_modules/mssql/node_modules/readable-stream/lib/_stream_writable.js:303:11)
    at Parser.addBuffer (node_modules/mssql/node_modules/tedious/lib/token/token-stream-parser.js:37:24)
    at Connection.sendDataToTokenStreamParser (node_modules/mssql/node_modules/tedious/lib/connection.js:1265:35)
    at Connection.data (node_modules/mssql/node_modules/tedious/lib/connection.js:2081:26)
    at Connection.dispatchEvent (node_modules/mssql/node_modules/tedious/lib/connection.js:1084:36)
    at MessageIO.messageIo.on.data (node_modules/mssql/node_modules/tedious/lib/connection.js:981:14)
    at Message.message.on.chunk (node_modules/mssql/node_modules/tedious/lib/message-io.js:29:14)
    at addChunk (node_modules/mssql/node_modules/readable-stream/lib/_stream_readable.js:297:12)
    at readableAddChunk (node_modules/mssql/node_modules/readable-stream/lib/_stream_readable.js:279:11)
    at Message.Readable.push (node_modules/mssql/node_modules/readable-stream/lib/_stream_readable.js:240:10)
    at Message.Transform.push (node_modules/mssql/node_modules/readable-stream/lib/_stream_transform.js:139:32)
    at Message.afterTransform (node_modules/mssql/node_modules/readable-stream/lib/_stream_transform.js:88:10)
    at Message.PassThrough._transform (node_modules/mssql/node_modules/readable-stream/lib/_stream_passthrough.js:38:3)
    at Message.Transform._read (node_modules/mssql/node_modules/readable-stream/lib/_stream_transform.js:177:10)
    at Message.Transform._write (node_modules/mssql/node_modules/readable-stream/lib/_stream_transform.js:164:83)
    at doWrite (node_modules/mssql/node_modules/readable-stream/lib/_stream_writable.js:405:139)
    at writeOrBuffer (node_modules/mssql/node_modules/readable-stream/lib/_stream_writable.js:394:5)
    at Message.Writable.write (node_modules/mssql/node_modules/readable-stream/lib/_stream_writable.js:303:11)
    at Message.Writable.end (node_modules/mssql/node_modules/readable-stream/lib/_stream_writable.js:559:51)
    at IncomingMessageStream.processBufferedData (node_modules/mssql/node_modules/tedious/lib/incoming-message-stream.js:58:19)
    at IncomingMessageStream._transform (node_modules/mssql/node_modules/tedious/lib/incoming-message-stream.js:83:10)
    at IncomingMessageStream.Transform._read (node_modules/mssql/node_modules/readable-stream/lib/_stream_transform.js:177:10)
    at IncomingMessageStream.Transform._write (node_modules/mssql/node_modules/readable-stream/lib/_stream_transform.js:164:83)
    at doWrite (node_modules/mssql/node_modules/readable-stream/lib/_stream_writable.js:405:139)
    at writeOrBuffer (node_modules/mssql/node_modules/readable-stream/lib/_stream_writable.js:394:5)
    at IncomingMessageStream.Writable.write (node_modules/mssql/node_modules/readable-stream/lib/_stream_writable.js:303:11)
    at Socket.ondata (_stream_readable.js:667:20)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
MichaelSun90 commented 5 years ago

Hi @AlexanderTunick, I am wondering this error is thrown from SQL server-side. Is this "CAST(dosd.value as DECIMAL(10,3)" the place causing the error. If you are sure that dosd.value is a decimal type data, I found a page may be related to this issue. https://www.mssqltips.com/sqlservertip/4008/handling-error-converting-data-type-varchar-to-numeric-in-sql-server/ Maybe you check this link, see if this helps.

AlexanderTunick commented 5 years ago

Hi @MichaelSun90! Thank you for your response. I guess the error is called because of these types of data in the column. It's real garbage. It may occur because of "3:00:00 PM". Don't you know how to handle this case?

2017
2017
2017
2017
3:00:00 PM
7:45:00 AM
0
0
0.47
1.16
0.81
0.58
AlexanderTunick commented 5 years ago

Resolved. For anyone who has struggled, see: https://stackoverflow.com/questions/57027840/error-converting-data-type-varchar-to-numeric-in-table-with-different-data-types/57027851#57027851