JS 操作数据库操作【集成MDB，SQL，MYSQL】
你提供的代码示例展示了如何使用JavaScript操作不同的数据库，包括Access和SQL Server。以下是对每个操作的简要总结和一些改进建议：
Access数据库
查询操作
var conn = new ActiveXObject("ADODB.Connection"); conn.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_your_database.accdb;"); var rs = new ActiveXObject("ADODB.Recordset"); var sql = "SELECT * FROM YourTable"; rs.open(sql, conn); while (!rs.EOF) { document.write(rs.Fields("FieldName").Value + "<br>"); rs.MoveNext(); } rs.close(); conn.close(); 插入操作
var conn = new ActiveXObject("ADODB.Connection"); conn.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_your_database.accdb;"); var sql = "INSERT INTO YourTable (FieldName) VALUES ('FieldValue')"; conn.Execute(sql); conn.close(); 更新操作
var conn = new ActiveXObject("ADODB.Connection"); conn.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_your_database.accdb;"); var sql = "UPDATE YourTable SET FieldName='NewValue' WHERE ID=1"; conn.Execute(sql); conn.close(); 删除操作
var conn = new ActiveXObject("ADODB.Connection"); conn.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_your_database.accdb;"); var sql = "DELETE FROM YourTable WHERE ID=1"; conn.Execute(sql); conn.close(); SQL Server数据库
查询操作
var conn = new ActiveXObject("ADODB.Connection"); conn.Open("Driver={SQL Server};Server=localhost;Database=MySchool;UID=sa;PWD=ok;"); var rs = new ActiveXObject("ADODB.Recordset"); var sql = "SELECT * FROM Student"; rs.open(sql, conn); while (!rs.EOF) { document.write(rs.Fields("StudentId").Value + " " + rs.Fields("studentName").Value + "<br>"); rs.MoveNext(); } rs.close(); conn.close(); 插入操作
var conn = new ActiveXObject("ADODB.Connection"); conn.Open("Driver={SQL Server};Server=localhost;Database=MySchool;UID=sa;PWD=ok;"); var sql = "INSERT INTO Student (studentName) VALUES ('NewStudent')"; conn.Execute(sql); conn.close(); 更新操作
var conn = new ActiveXObject("ADODB.Connection"); conn.Open("Driver={SQL Server};Server=localhost;Database=MySchool;UID=sa;PWD=ok;"); var sql = "UPDATE Student SET studentName='UpdatedName' WHERE StudentId=1"; conn.Execute(sql); conn.close(); 删除操作
var conn = new ActiveXObject("ADODB.Connection"); conn.Open("Driver={SQL Server};Server=localhost;Database=MySchool;UID=sa;PWD=ok;"); var sql = "DELETE FROM Student WHERE StudentId=1"; conn.Execute(sql); conn.close(); 改进建议
- 错误处理：添加错误处理代码以捕获和处理可能的异常。
- 安全性：避免在SQL语句中直接使用用户输入，以防止SQL注入攻击。可以使用参数化查询。
- 资源管理：确保正确关闭数据库连接和记录集，以释放资源。
示例：使用参数化查询
var conn = new ActiveXObject("ADODB.Connection"); conn.Open("Driver={SQL Server};Server=localhost;Database=MySchool;UID=sa;PWD=ok;"); var cmd = new ActiveXObject("ADODB.Command"); cmd.ActiveConnection = conn; var sql = "SELECT * FROM Student WHERE StudentId=?"; cmd.CommandText = sql; cmd.Parameters.Append(cmd.CreateParameter("@StudentId", 200, 1, -1, studentId)); var rs = cmd.Execute(); while (!rs.EOF) { document.write(rs.Fields("StudentId").Value + " " + rs.Fields("studentName").Value + "<br>"); rs.MoveNext(); } rs.close(); conn.close(); 通过这些改进，可以使代码更加健壮和安全。
