README
data-access
database data access now just support oracle & sqlserver,other dbbase is in developing... 数据库访问集成,现在只支持oracle & sqlserver,其他数据库(可自行扩展)持续开发中...
typescript study for myself.
use by typescript eg.
import { IDbOptions, DbType,dbFactory, DataTypes } from "../db-access";
let gcoa: IDbOptions = { dbType: DbType.oracle, dbConfig: { user: "gzgc", password: "***", connectString: "10.10.20.25:1521/mx25", externalAuth: false } };
let yf_gcgs: IDbOptions = { dbType: DbType.mssql, dbConfig: { user: "sa", password: '***', server: '10.10.20.15', database: 'GCGS', options: { encrypt: true } } };
async function ora_query_test() { console.log('ora_test');
let db = dbFactory(gcoa);
let db_gcgs = dbFactory(yf_gcgs);
let sql = `select mv001,mv002 from cmsmv
where mv001=:mv001`;
let cmd = await db.sql(sql);
cmd.parameter({
name: 'mv001',
value: '000057'
});
let data = await cmd.queryMany();
let r;
await db.useTransaction(true);
try {
sql = `update cmsmv set mv002='王绍伟1' where mv001='000057'`;
cmd = await db.sql(sql);
r = await cmd.execute();
await db.commit();
}
catch (err) {
await db.rollback();
throw (err);
}
finally {
try {
await db.useTransaction(false);
} catch (err) {
console.error(err);
}
}
sql = `select f_bianhao,f_mingcheng from gimquyuhed where f_youxiaoma='Y'`;
cmd = await db.sql(sql);
let data1 = await cmd.queryMany();
sql = `select MV001,MV002 from CMSMV`;
cmd = await db_gcgs.sql(sql);
let data2 = await cmd.queryMany();
console.log(data);
console.log(r);
console.log(data1);
console.log(data2);
} async function ora_insert_test() { console.log('ora_insert_test'); let db = dbFactory(gcoa); let result; await db.useTransaction(true); try { // let insert = await db.insert('itfwsqdhed'); // insert.column('ID', 123456789) // .column('F004', '000057') // .column('F005', '10199') // .column('F006', 'nodejs测试插入') // .column('CREATEDBY', '000057') // .column('LASTMODIFIEDBY', '000057') // .column('CREATORBM', 45); // result = await insert.execute();
// console.dir(result);
let insert2 = await db.insert('itfwsqdhed');
insert2.column('ID', 223456789)
.column('F002', '2018/12/24 16:19:00', DataTypes.DateTime)
.column('F004', '000057')
.column('F005', '10199')
.column('F006', 'nodejs测试插入1231231')
.column('CREATEDBY', '000057')
.column('LASTMODIFIEDBY', '000057')
.column('CREATORBM', 45);
let id = await insert2.executeReturnLastId<number>('ID');
console.dir(id);
await db.commit();
}
catch (err) {
await db.rollback();
throw (err);
}
finally {
try {
await db.useTransaction(false);
} catch (err) {
console.error(err);
}
}
} async function ora_update_test() { console.log('ora_update_test'); let db = dbFactory(gcoa); await db.useTransaction(true); try { let update = await db.update('itfwsqdhed'); update.where('ID', 223456789) .column('F002', new Date(), DataTypes.DateTime) .column('F006', 'nodejs测试update'); let r = await update.execute(); console.dir(r);
await db.commit();
}
catch (err) {
await db.rollback();
throw (err);
}
finally {
await db.useTransaction(false);
}
} async function ora_del_test() { console.log('ora_del_test'); let db = dbFactory(gcoa); await db.useTransaction(true); try { let del = await db.delete('itfwsqdhed'); //del.where('ID', 223456789); del.where('ID', 1417); let r = await del.execute(); console.dir(r);
await db.commit();
}
catch (err) {
await db.rollback();
throw (err);
}
finally {
await db.useTransaction(false);
}
} async function ora_storedProcedure_test() { console.log('ora_storedProcedure_test'); let db = dbFactory(gcoa);
//注:node-oracledb 执行存储过程使用的是PL/SQL方式,需要事务提交才生效
await db.useTransaction(true);
try {
let proc = await db.storedProcedure('proc_nodejs_test');
proc.parameter('en', 'wangshw')
.parameter('cn', '王绍伟')
.parameterOut('fullname');
let r = await proc.execute();
console.dir(r);
await db.commit();
}
catch (err) {
await db.rollback();
throw (err);
}
finally {
await db.useTransaction(false);
}
}
async function mssql_query_test() {
console.log('mssql_test');
let db = dbFactory(yf_gcgs);
let sql = select MV001,MV002 from CMSMV where MV001=@mv001
;
let cmd = await db.sql(sql);
cmd.parameter({
name: 'mv001',
value: 'WANGSW'
});
let data = await cmd.queryMany();
console.log(data);
}
async function mssql_insert_test() {
console.log('mssql_insert_test');
let db = dbFactory(yf_gcgs);
let result;
await db.useTransaction(true);
try {
let insert = await db.insert('INVMB_MT');
insert.column('MB001', '9999999999')
.column('MB002', 'nodejs-mssql测试')
.column('MB003', '测试数据')
.column('MB109', 'N')
.column('CREATEDBY', '000057')
.column('LASTMODIFIEDBY', '000057')
.column('CREATORBM', 45);
result = await insert.execute();
console.dir(result);
let insert2 = await db.insert('INVMB_MT');
insert2.column('MB001', '88888888')
.column('MB002', 'nodejs-mssql测试')
.column('MB003', '测试数据')
.column('MB004', '12')
.column('MB109', 'N')
.column('CREATEDBY', '000057')
.column('LASTMODIFIEDBY', '000057')
.column('LASTMODIFIEDDATE', '', DataTypes.DateTime)
.column('CREATORBM', 45);;
let id = await insert2.executeReturnLastId<number>('ID');
console.dir(id);
await db.commit();
}
catch (err) {
await db.rollback();
throw (err);
}
finally {
try {
await db.useTransaction(false);
} catch (err) {
console.error(err);
}
}
} async function mssql_update_test() { console.log('mssql_update_test'); let db = dbFactory(yf_gcgs); await db.useTransaction(true); try { let update = await db.update('INVMB_MT'); update.where('ID', 17) .column('CREATEDBY', '000057') .column('LASTMODIFIEDDATE', new Date()) .column('CREATORBM', 45); let r = await update.execute(); console.dir(r);
await db.commit();
}
catch (err) {
await db.rollback();
throw (err);
}
finally {
await db.useTransaction(false);
}
} async function mssql_del_test() { console.log('mssql_del_test'); let db = dbFactory(yf_gcgs); await db.useTransaction(true); try { let del = await db.delete('INVMB_MT'); del.where('ID', 37); let r = await del.execute(); console.dir(r);
await db.commit();
}
catch (err) {
await db.rollback();
throw (err);
}
finally {
await db.useTransaction(false);
}
} async function mssql_storedProcedure_test() { console.log('mssql_storedProcedure_test'); let db = dbFactory(yf_gcgs); try { let proc = await db.storedProcedure('proc_nodejs_test'); proc.parameter('en', 'wangshw') .parameter('cn', '王绍伟') .parameterOut('fullname'); let r = await proc.execute(); console.dir(r); } catch (err) { throw (err); } }
async function run() { try { await ora_query_test(); //await ora_insert_test(); //await ora_update_test(); //await ora_del_test(); //await ora_storedProcedure_test();
//await mssql_query_test();
//await mssql_insert_test();
//await mssql_update_test();
//await mssql_del_test();
//await mssql_storedProcedure_test();
}
catch (err) {
console.dir(err);
//console.log(err.message.split('\n')[0]);
}
}
run();