A pusher querying a SQL Server database (MSSQL)
I am trying to execute a simple query on SQL Server, but when I run it with the protractor it just runs quickly and returns nothing (logs). I would appreciate any hints, working examples, or pointers on what I am doing wrong and how to execute a SQL query against SQL Server using a protractor.
var sql = require('mssql');
describe('test db connection', function () {
it('tests db connection', function () {
ConnectDB()
})
function ConnectDB() {
var config = {
user: 'user',
password: 'password',
server: 'xyz.database.windows.net',
database: 'dbdev',
options: {
encrypt: true
}
}
var connection = new sql.Connection(config)
connection.connect(function (err) {
console.log(err)
})
var request = new sql.Request(connection);
request.query('select * from Config where [Key] like \'HidePreop%\'', function (err, recordeset) {
var res = recordeset;
console.log(res)
});
source to share
it
Push Testing - Blocks will wait for webDriverJS commands in the Protractor control flow and any other asynchronous operation you must manually do in order for the block wait to it
be used by done
.
In this case -
describe('test db connection', function () {
it('tests db connection', function (done) {
// Any method that returns a promise. Similary if your method returns a callback you can handle accordingly
ConnectDB().then(function _onSuccess(){
done();
}).catch(function _onFailure(err){
done.fail(err);
})
})
});
And I would have changed your funcion - ConnectDB()
, promise to return, based on the callback authorization by mssql npm package , see here. How to convert callback Promises. its an awesome tutorial.
function ConnectDB() {
return new Promise(function (fulfill, reject) {
var config = {
user: 'user',
.............
};
var connection = new sql.Connection(config);
connection.connect(function (err) {
reject(err);
});
var request = new sql.Request(connection);
request.query('select * from Config where [Key] like \'HidePreop%\'', function (err, recordeset) {
if (err) reject(err);
else fulfill(recordeset);
});
});
}
source to share
I am accessing an MSSQL database using this configuration in my own file and then importing it into a test file, so I can use it a bit like a fake page object. I highly recommend storing any database information in an external file outside the repository. I am using the .env file from the npm library dotenv which can be installed withnpm install --save-dev dotenv
//db.js
const sql = require('mssql');
require('dotenv').config();
var config = {
user: process.env.MSSQL_USERNAME,
password: process.env.MSSQL_PASSWORD,
server: process.env.MSSQL_SERVER,
database: 'YOURDB',
options: {
encrypt: true
}
};
module.exports = {
/** Define sql queries here */
deleteEmployeeByLastName(lastName) {
let my_query = `DELETE FROM dbo.Employee WHERE LastName='${lastName}'`;
sql.connect(config).then(function () {
new sql.Request()
.query(my_query).then(function (recordset) {}).catch(function (err) {
console.log(err);
});
});
}
}
The test file should look something like this:
//test.js
var db = require('db.js');
describe('Employee Management', function () {
it('Deleting an employee', function (done) {
db.deleteEmployeeByLastName('Pmurt');
//REST OF CODE HERE
//...
//...
done();
})
});
source to share
Working example posting for mssql@4.0.4
var sql = require('mssql');
describe('test db connection', function () {
it('tests db connection', function (done) {
ConnectDB().then(function _onSuccess(_returned){
console.log(_returned.recordset[0].FirstPSPOrderId)
done();
}).catch(function _onFailure(err){
done.fail(err);
})
})
});
function ConnectDB() {
return new Promise(function (fulfill, reject) {
var config = {
user: 'xxx',
password: 'xxx',
server: 'xxx',
port: 'xxx',
database: 'xxxxxx',
options: {
encrypt: true
}
};
var connection = new sql.ConnectionPool(config);
connection.connect((err) => {
if (err) reject(err);
//});
let query = "select [FirstPSPOrderId] from XYZ"
connection.request()
.query(query, (err, recordeset) => {
console.dir('Record set: ' + recordeset)
console.dir('Err: ' + err)
if (err) reject(err);
else fulfill(recordeset);
});
});
});
}
source to share