Sql connection with javascript

Шаг 3. Подтверждение концепции, подразумевающее подключение к SQL с помощью Node.js

Этот пример следует рассматривать только как подтверждение концепции. Пример кода упрощен для ясности и он не обязательно рекомендуется к использованию корпорацией Майкрософт. Другие примеры, в которых используются те же важные функции, можно получить на сайте GitHub:

Шаг 1. Подключение

Функция new Connection используется для подключения к базе данных SQL.

 var Connection = require('tedious').Connection; var config = < server: 'your_server.database.windows.net', //update me authentication: < type: 'default', options: < userName: 'your_username', //update me password: 'your_password' //update me >>, options: < // If you are on Microsoft Azure, you need encryption: encrypt: true, database: 'your_database' //update me >>; var connection = new Connection(config); connection.on('connect', function(err) < // If no error, then good to proceed. console.log("Connected"); >); connection.connect(); 

Шаг 2. Выполнение запроса

Все операторы SQL выполняются с помощью функции new Request() . Если оператор, например select, возвращает строки, их можно извлечь с помощью функции request.on() . Если строк нет, функция request.on() возвращает пустые списки.

 var Connection = require('tedious').Connection; var config = < server: 'your_server.database.windows.net', //update me authentication: < type: 'default', options: < userName: 'your_username', //update me password: 'your_password' //update me >>, options: < // If you are on Microsoft Azure, you need encryption: encrypt: true, database: 'your_database' //update me >>; var connection = new Connection(config); connection.on('connect', function(err) < // If no error, then good to proceed. console.log("Connected"); executeStatement(); >); connection.connect(); var Request = require('tedious').Request; var TYPES = require('tedious').TYPES; function executeStatement() < var request = new Request("SELECT c.CustomerID, c.CompanyName,COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC;", function(err) < if (err) < console.log(err);>>); var result = ""; request.on('row', function(columns) < columns.forEach(function(column) < if (column.value === null) < console.log('NULL'); >else < result+= column.value + " "; >>); console.log(result); result =""; >); request.on('done', function(rowCount, more) < console.log(rowCount + ' rows returned'); >); // Close the connection after the final event emitted by the request, after the callback passes request.on("requestCompleted", function (rowCount, more) < connection.close(); >); connection.execSql(request); > 

Шаг 3. Вставка строки

В этом примере показано, как безопасно выполнить инструкцию INSERT и передать параметры для защиты от внедрения кода SQL.

 var Connection = require('tedious').Connection; var config = < server: 'your_server.database.windows.net', //update me authentication: < type: 'default', options: < userName: 'your_username', //update me password: 'your_password' //update me >>, options: < // If you are on Microsoft Azure, you need encryption: encrypt: true, database: 'your_database' //update me >>; var connection = new Connection(config); connection.on('connect', function(err) < // If no error, then good to proceed. console.log("Connected"); executeStatement1(); >); connection.connect(); var Request = require('tedious').Request var TYPES = require('tedious').TYPES; function executeStatement1() < var request = new Request("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES (@Name, @Number, @Cost, @Price, CURRENT_TIMESTAMP);", function(err) < if (err) < console.log(err);>>); request.addParameter('Name', TYPES.NVarChar,'SQL Server Express 2014'); request.addParameter('Number', TYPES.NVarChar , 'SQLEXPRESS2014'); request.addParameter('Cost', TYPES.Int, 11); request.addParameter('Price', TYPES.Int,11); request.on('row', function(columns) < columns.forEach(function(column) < if (column.value === null) < console.log('NULL'); >else < console.log("Product id of inserted item is " + column.value); >>); >); // Close the connection after the final event emitted by the request, after the callback passes request.on("requestCompleted", function (rowCount, more) < connection.close(); >); connection.execSql(request); > 

Источник

Step 3: Proof of concept connecting to SQL using Node.js

This example should be considered a proof of concept only. The sample code is simplified for clarity, and does not necessarily represent best practices recommended by Microsoft. Other examples, which use the same crucial functions are available on GitHub:

Step 1: Connect

The new Connection function is used to connect to SQL Database.

 var Connection = require('tedious').Connection; var config = < server: 'your_server.database.windows.net', //update me authentication: < type: 'default', options: < userName: 'your_username', //update me password: 'your_password' //update me >>, options: < // If you are on Microsoft Azure, you need encryption: encrypt: true, database: 'your_database' //update me >>; var connection = new Connection(config); connection.on('connect', function(err) < // If no error, then good to proceed. console.log("Connected"); >); connection.connect(); 

Step 2: Execute a query

All SQL statements are executed using the new Request() function. If the statement returns rows, such as a select statement, you can retrieve them using the request.on() function. If there are no rows, the request.on() function returns empty lists.

 var Connection = require('tedious').Connection; var config = < server: 'your_server.database.windows.net', //update me authentication: < type: 'default', options: < userName: 'your_username', //update me password: 'your_password' //update me >>, options: < // If you are on Microsoft Azure, you need encryption: encrypt: true, database: 'your_database' //update me >>; var connection = new Connection(config); connection.on('connect', function(err) < // If no error, then good to proceed. console.log("Connected"); executeStatement(); >); connection.connect(); var Request = require('tedious').Request; var TYPES = require('tedious').TYPES; function executeStatement() < var request = new Request("SELECT c.CustomerID, c.CompanyName,COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC;", function(err) < if (err) < console.log(err);>>); var result = ""; request.on('row', function(columns) < columns.forEach(function(column) < if (column.value === null) < console.log('NULL'); >else < result+= column.value + " "; >>); console.log(result); result =""; >); request.on('done', function(rowCount, more) < console.log(rowCount + ' rows returned'); >); // Close the connection after the final event emitted by the request, after the callback passes request.on("requestCompleted", function (rowCount, more) < connection.close(); >); connection.execSql(request); > 

Step 3: Insert a row

In this example you will see how to execute an INSERT statement safely, passing parameters, which protect your application from SQL injection values.

 var Connection = require('tedious').Connection; var config = < server: 'your_server.database.windows.net', //update me authentication: < type: 'default', options: < userName: 'your_username', //update me password: 'your_password' //update me >>, options: < // If you are on Microsoft Azure, you need encryption: encrypt: true, database: 'your_database' //update me >>; var connection = new Connection(config); connection.on('connect', function(err) < // If no error, then good to proceed. console.log("Connected"); executeStatement1(); >); connection.connect(); var Request = require('tedious').Request var TYPES = require('tedious').TYPES; function executeStatement1() < var request = new Request("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES (@Name, @Number, @Cost, @Price, CURRENT_TIMESTAMP);", function(err) < if (err) < console.log(err);>>); request.addParameter('Name', TYPES.NVarChar,'SQL Server Express 2014'); request.addParameter('Number', TYPES.NVarChar , 'SQLEXPRESS2014'); request.addParameter('Cost', TYPES.Int, 11); request.addParameter('Price', TYPES.Int,11); request.on('row', function(columns) < columns.forEach(function(column) < if (column.value === null) < console.log('NULL'); >else < console.log("Product id of inserted item is " + column.value); >>); >); // Close the connection after the final event emitted by the request, after the callback passes request.on("requestCompleted", function (rowCount, more) < connection.close(); >); connection.execSql(request); > 

Feedback

Submit and view feedback for

Источник

Node.js MySQL

To be able to experiment with the code examples, you should have MySQL installed on your computer.

You can download a free MySQL database at https://www.mysql.com/downloads/.

Install MySQL Driver

Once you have MySQL up and running on your computer, you can access it by using Node.js.

To access a MySQL database with Node.js, you need a MySQL driver. This tutorial will use the «mysql» module, downloaded from NPM.

To download and install the «mysql» module, open the Command Terminal and execute the following:

Now you have downloaded and installed a mysql database driver.

Node.js can use this module to manipulate the MySQL database:

Create Connection

Start by creating a connection to the database.

Use the username and password from your MySQL database.

var con = mysql.createConnection( host: «localhost»,
user: «yourusername«,
password: «yourpassword»
>);

con.connect(function(err) if (err) throw err;
console.log(«Connected!»);
>);

Save the code above in a file called «demo_db_connection.js» and run the file:

Which will give you this result:

Now you can start querying the database using SQL statements.

Query a Database

Use SQL statements to read from (or write to) a MySQL database. This is also called «to query» the database.

The connection object created in the example above, has a method for querying the database:

con.connect(function(err) <
if (err) throw err;
console.log(«Connected!»);
con.query(sql, function (err, result) <
if (err) throw err;
console.log(«Result: » + result);
>);
>);

The query method takes an sql statements as a parameter and returns the result.

Learn how to read, write, delete, and update a database in the next chapters.

Read more about SQL statements in our SQL Tutorial.

Источник