Error insert to mssql server

Help
1 
import sql from 'mssql';

const sqlConfig = {
  user: 'test',
  password: 'test',
  database: 'test',
  server: 'test.test.com',
  options: {
    encrypt: false,
    trustServerCertificate: true // change to true for local dev / self-signed certs
  }
}

export default defineComponent({
  async run({ steps, $ }) {
    try {

        let pool = await sql.connect(sqlConfig);
        await pool.request()
           .input('a', sql.VarChar(50), steps.trigger.event.body.string1)
           .input('b', sql.VarChar(50), steps.trigger.event.body.string2)
           .input('c', sql.VarChar(50), steps.trigger.event.body.string3)
           .query('insert test_order(field1, field2, field3) values(@a, @b, @c)');
      
        pool.close;
        sql.close;
      
    } catch (err) {
      console.log(err)
    }
  },
})

I’ve got this error when connecting to sql server.

at null.null (/tmp/__pdg__/dist/code/e7743027e05bf27bed02d89916a793eddf1d96c6905e3846cb9d234cea53bff3/node_modules/.pnpm/mssql@9.1.0/node_modules/mssql/lib/tedious/connection-pool.js:70:17)
at Connection.onConnect (/tmp/__pdg__/dist/code/e7743027e05bf27bed02d89916a793eddf1d96c6905e3846cb9d234cea53bff3/node_modules/.pnpm/tedious@15.1.2/node_modules/tedious/lib/connection.js:1012:9)
at Object.onceWrapper (events.js:520:26)
at Connection.emit (events.js:400:28)
at Connection.emit (/tmp/__pdg__/dist/code/e7743027e05bf27bed02d89916a793eddf1d96c6905e3846cb9d234cea53bff3/node_modules/.pnpm/tedious@15.1.2/node_modules/tedious/lib/connection.js:1040:18)
at Connection.connectTimeout (/tmp/__pdg__/dist/code/e7743027e05bf27bed02d89916a793eddf1d96c6905e3846cb9d234cea53bff3/node_modules/.pnpm/tedious@15.1.2/node_modules/tedious/lib/connection.js:1254:10)
at Timeout.null (/tmp/__pdg__/dist/code/e7743027e05bf27bed02d89916a793eddf1d96c6905e3846cb9d234cea53bff3/node_modules/.pnpm/tedious@15.1.2/node_modules/tedious/lib/connection.js:1199:12)
at null.listOnTimeout (internal/timers.js:557:17)
at process.processTimers (internal/timers.js:500:7)

{
code: ‘ETIMEOUT’,
originalError: at Connection.connectTimeout (/tmp/pdg/dist/code/e7743027e05bf27bed02d89916a793eddf1d96c6905e3846cb9d234cea53bff3/node_modules/.pnpm/tedious@15.1.2/node_modules/tedious/lib/connection.js:1254:26)
at Timeout.null (/tmp/pdg/dist/code/e7743027e05bf27bed02d89916a793eddf1d96c6905e3846cb9d234cea53bff3/node_modules/.pnpm/tedious@15.1.2/node_modules/tedious/lib/connection.js:1199:12)
at null.listOnTimeout (internal/timers.js:557:17)
at process.processTimers (internal/timers.js:500:7)
{
code: ‘ETIMEOUT’,
isTransient: undefined
},
name: ‘ConnectionError’
}

2

Hi @steve,

First off, welcome to Pipedream! Happy to have you!

I think the database configuration is not quite correct

const sqlConfig = {
  user: 'test',
  password: 'test',
  database: 'test',
  server: 'test.test.com',
  options: {
    encrypt: false,
    trustServerCertificate: true // change to true for local dev / self-signed certs
  }
}

Maybe you should change it to a real server, where you can connected to it from your machine?

3

I have a real server. I just hide the detail.

image
image1191×843 34.2 KB

4

I see. Could you confirm that you can connect to the server from your local machine. And also have you tried to test the code in your local machine?

Assuming your code is correct, could you check from your side that your database is allowing the connection from Pipedream? Maybe this section might be able to help.

5

it works if the query is a “select” statement. but I can only get it works randomly for a “insert” statement.

6

Maybe you’ll need to close the connection here?

        pool.close();
        sql.close();

I’m not really familiar with mssql library. If you’d like to have a 1-1 support, you can subscribe to Pipedream’s team plan, or you can hire an expert for you usecase using this link: Connect with a Pipedream Partner