msnodesqlv8 has full compatibility with MS SQL Server using an MS ODBC driver. Many functions e.g. open, query, connection pool, prepare, transactions, close will work with any ODBC compatible driver with its repsective database.
This library only works with Node versions greater than 10.0 or electron greater than 5.0
1. Initialize the project and install the dependencies:
npm init -y
npm install express msnodesqlv8
2. Create an index.js file in the root directory and replace its content with the following:
const express = require("express");
const app = express();
const port = process.env.PORT || 3000;
const sql = require("msnodesqlv8");
// Replace the following parameters with your actual database information.
const server = "SQLxxx.site4now.net";
const database = "DB_xxx_xxx";
const userName = "DB_xxx_xxx_admin";
const password = "your_password";
const connectionString = `Server=${server};Database=${database};UID=${userName};PWD=${password};Driver={SQL Server Native Client 11.0}`;
const query = "SELECT xxx FROM [dbo].[table_name]";
app.get("/", (req, res, next) => {
sql.query(connectionString, query, (err, rows) => {
if (err) {
console.log(err);
} else if (rows) {
res.json(rows);
} else {
res.send("nothing here");
}
});
});
app.listen(port, () => {
console.log(`running at http://localhost:${port}`);
});
3. Run your project locally:
node index.js
4. Access http://localhost:3000 to check the result.
5. Stop local development server and archive all folders (including "node_modules") and files to a .zip file.
7. Unzip the .zip file to the target site path, i.e. site3
9. Change the content of site3/web.config file to:
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<system.webServer>
<handlers>
<add name="iisnode" path="index.js" verb="*" modules="iisnode" />
</handlers>
<rewrite>
<rules>
<rule name="DynamicContent">
<match url="/*" />
<action type="Rewrite" url="index.js"/>
</rule>
</rules>
</rewrite>
</system.webServer>
</configuration>
Troubleshoot
Error: Application has thrown an uncaught exception and is terminated:
Error: Module did not self-register: '\\?\h:\root\home\xxx\www\site3\node_modules\msnodesqlv8\build\Release\sqlserverv8.node'.
Description: The node version you created the project does not match the version that installed on the production server.
Solutions: a). Install the same Node.js version on your local machine. You can find the server-side Node.js version by outputing the value of 'process.version' in application code. b). Set a custom Node.js version for your website by following the instructions in this knowledge base article.
Article ID: 2276, Created: February 2 at 12:42 AM, Modified: February 2 at 12:42 AM