Comments (8)
Just for the record, as you cannot warranty you are using the same connection of the pool, for both the insert and the select statement, you may choose to use a PreparedStatement or simply use the same query for both of them. Use it like this:
var query = "INSERT INTO demoTable(someValue) values ('someValue')";
query = query + ';select @@IDENTITY AS \'identity\''; // ---- this is what worked
request.query(str, function(suberr, subdata){
console.log("Querying @@IDENTITY : ");
console.log(subdata[0].identity); // -> RETURNED VALUE
});
from node-mssql.
Is anyone else having the same problem? Or is this not a bug?
Any response is appreciated.
Cheers.
from node-mssql.
I would like to recommend POST these type of questions in StackOverflow, there is tag for this project:
http://stackoverflow.com/questions/tagged/node-mssql
I did this I created this table:
CREATE TABLE demoTable( id INT IDENTITY(1,1) NOT NULL, someValue VARCHAR(20) NOT NULL, )
Then I created this demo code and it worked fine:
var connection = new self.sql.Connection(DBConfig, function (err) { console.log("Demo Query"); var query = "INSERT INTO demoTable(someValue) values ('someValue') "; var request = new sql.Request(connection); request.query(query, function(err, data) { console.log("Request Query 1 - Insert"); if (!err) { var str = "SELECT @@IDENTITY AS 'identity'"; request = new sql.Request(connection); request.query(str, function(suberr, subdata) { console.log("Querying @@Indentity : "); console.log(subdata); // -> RETURNED NOTHING }); } else{ console.log(err); } }); });
from node-mssql.
Closing due to inactivity.
from node-mssql.
Hi guys,
I know this issue is closed but still decided to ask. I currently need the same thing as the author of the thread. I need to insert a row and as a result get back its id.
Now, I see that the answers here would do the job but is it really true that this functionality is not part of the mssql library? I mean I kind of expected when I insert a row to get back what's been inserted in the result along with any fields that were set upon inserting (like id).
@ocpuso , you probably did research before posting this so what do you think?
from node-mssql.
I am having the same question. there are no ways to return the last insert id
nomatter the select statement is execute in the same connection with insert query, it always return nothing. So how can your guys get the last insert id????
var dbConn = new sql.Connection(dbIntra,function (err) {
var tran = new sql.Transaction(dbConn);
tran.begin(function (error) {
var rollBack = false;
tran.on('rollback',function (aborted) {
rollBack = true;
});
var query="INSERT INTO [dbo].[voices] ([userid],[type],[number],[recording_date],[remark],[create_time]) VALUES (" + req.user.user_id + ", '" + req.body.type + "','" + req.body.number+ "',convert(datetime, '" + req.body.recording_date + " 00:00:00', 120) ,'" + req.body.remark + "', GETDATE());";
//query+="SELECT SCOPE_IDENTITY() AS id;";
new sql.Request(tran).query(query,function (err, recordset) {
//console.log(recordset);
if (err) {
if (!rollBack) {
tran.rollback(function (err) {
res.json({"success": false, "msg":"Rollback:" + err});
});
}
} else {
var str = "SELECT SCOPE_IDENTITY() AS id";
request = new sql.Request(dbConn);
request.query(str, function(suberr, subdata){
console.log(subdata); // -> RETURNED NOTHING
});
tran.commit().then(function (recordset) {
console.log(recordset);
res.json({"success": true, "msg":"Data is inserted successfully!"});
}).catch(function (err) {
res.json({"success": false, "msg":'Error in transaction commit ' + err});
});
}
});
});
});
from node-mssql.
sequelize/sequelize#5499 (comment)
from node-mssql.
This was answered 3 years ago #32 (comment)
from node-mssql.
Related Issues (20)
- Committing a transaction early, within a transaction ? HOT 4
- [Question] Does node-mssql support Kerberos authentication HOT 1
- ConnectionError: Connection not yet open. HOT 1
- How to bind a multidimensional array HOT 3
- How to add order by from user input? HOT 1
- (node:1009) MaxListenersExceededWarning: Possible EventEmitter memory leak detected. 11 error listeners added to [ConnectionPool]. Use emitter.setMaxListeners() to increase limit HOT 1
- Bulk Insert Time Out HOT 1
- Bulk upload with a DateTime2 column HOT 3
- error self signed certificate - Lambda function Nodejs connect to mssql on ec2 instance after migrating from 6.2.0 to 10.0.2 HOT 6
- node-mssql only works if TCP/IP for SQL Server is turned on HOT 3
- mssql suddenly stopped working with INSERTS to sql server db from AWS Lambda, with no changes anywhere. Had been working fine for years. HOT 1
- error:0A000102 when linking to database HOT 1
- Update Tedious to 18.2.1 to resolve Azure Identity vulnerabilities HOT 5
- Query execution takes more than 30 seconds using Tedious while same query using Microsoft JDBC Driver for SQL Server on DBeaver takes only 200 ms HOT 3
- mssql@v10 dropped support for Node v14 partially HOT 1
- Enable GitHub Discussions HOT 2
- Does msnodesqlv8 driver support Azure Managed Identity authentication mode HOT 1
- `CREATE DATABASE` Failing When Using the ES6 Tagged Template Literal Approach HOT 1
- Would there be memory leaks if we create a new new sql.ConnectionPool with each query and then call .close() to close all connections in the pool after the query? HOT 1
- FailureException: recvmsg EINVALStack: ConnectionError: recvmsg EINVAL - EINSTLOOKUP fails in Azure ASE v3 environment HOT 5
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from node-mssql.