I'm building an app for proof of concept. I have an endpoint which reads data from a JSON file and the JSON data should then be bulk inserted into the database. The table in the database gets created correctly but data doesn't get added into the table.

I'm getting the following error message:

Invalid column type from bcp client for colid 1.

JSON file format

[
    {"giver_id": "2343212"}
]

Endpoint

app.get('/givers', async(req, res) => {
    const fs = require('fs');
    let raw = fs.readFileSync('C:\\data\\givers.json');
    let response = JSON.parse(raw);
    try{
        let pool = await sql.connect(connectionConfig)
        const table = new sql.Table('givers')
        table.create = true
        table.columns.add('giver_id', sql.VarChar, { nullable: true })

        response.forEach((row) => {
            table.rows.add.apply(table.rows, row)
        })
        
        const request = new sql.Request(pool)
        request.bulk(table, (err, result) => {
            if(err){
                console.log(err.message) //Invalid column type from bcp client for colid 1.
            }
            if(result){
                console.log(result)
            }
        })
    } catch(err){
        if(err){
            console.log(err.message)
        }
    }
})

I'm using node-mssql package.

CodePudding user response：

Invalid column type from bcp client for colid 1.

The above error message can occur when the table already exists in the target database and the table definition in Node via table.columns.add(...) does not match the actual definition in SQL Server.

In the comments it was mentioned that the giver column is varchar(max) so a matching definition in Node would be:

table.columns.add('giver_id', sql.VarChar(sql.MAX), { nullable: false });