sql.queryWithTrans = function (trans, sqlStr, params, callback) {
if (!sqlConfg) {
var error = new Error("There is no sql config!");
callback(error);
}
else if (!sqlStr) {
var error = new Error("The Parameter sqlStr is null");
callback(error);
}
else if (!trans) {
sql.query(sqlStr, params, callback);
}
else {
trans.begin(function (err) {
if (err) {
trans.rollback();
callback && callback(err);
}
else {
var request = sql.Request(trans);
request.multiple = true;
if (params) {
params.forEach(function (param) {
request.input(param.name, param.value);
}, this);
}
request.query(sqlStr, function (err, recordSet) {
if (err) {
trans.rollback();
callback && callback(err);
}
else {
trans.commit(function (err, recordSet) {
if (err) {
trans.rollback();
callback && callback(err);
}
else {
callback && callback(null, recordSet);
}
});
}
});
}
});
}
};
async mssql() {
const config = {
user: '******',
password: '******',
server: '127.0.0.1\\DUZJ2016', // You can use 'localhost\\instance' to connect to named instance
database: 'anfang',
// options: {
// encrypt: true // Use this if you're on Windows Azure
// }
}
const pool = await sql.connect(config)
const result = await sql.query("SELECT a.BatchId,a.ResultTime FROM batchinfo a where DATE_FORMAT(a.ResultTime,'%Y%m%d') between ? and ?", ['20171201', '20171217'])
await sql.close();
console.log(result);
this.ctx.body = result;
}
sync.block(function (params) {
var countResult = sync.wait(sql.query(countSqlString, null, sync.cb('err', 'count')));
if (!countResult.err) {
var count = countResult.count;
if (totalNum == 0) {
totalNum = count;
}
if (startNum > count) return '';
var topNum = startNum + totalNum - 1;
if (count < topNum) {
totalNum = count - startNum + 1;
if (totalNum < 1) {
return _scope.getSelectSqlStringWithoutStartNum(tableName, totalNum, columns, whereStr, orderStr, callback);
}
}
var orderByStringOpposite = sql.GetOrderByStringOpposite(orderStr);
var sqlString = '';
sqlString += " SELECT " + columns;
sqlString += " FROM(SELECT TOP " + totalNum + " " + columns;
sqlString += " FROM (SELECT TOP " + topNum + " " + columns;
sqlString += " FROM " + tableName + " " + whereStr + " " + orderStr + ") tmp ";
sqlString += orderByStringOpposite + ") tmp ";
sqlString += orderStr;
callback && callback(null, sqlString);
}
});