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)
- Dynamic Where query not working HOT 3
- SQL Query Error: 'Invalid usage of the option NEXT in the FETCH statement' with Paginated Fetch Query HOT 1
- mssql 6 incompatible with node v12 HOT 1
- IN clause parametrize issue HOT 1
- How to connect to SQL Server using tedious (Windows Authentication) HOT 1
- Issue connecting to MSSQL DB in Linux environment HOT 3
- How to use the user-defined data types?
- you have a typo in your readme sql.query`some query` HOT 1
- ConnectionError: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified HOT 1
- Potential impact of disabling connection validation when released from the pool HOT 1
- RequestError: Incorrect syntax near '='. Unable to query to table when values containing equal sign '=', HOT 7
- The "config.server" property is required and must be of type string. HOT 1
- @azure/core-rest-pipeline breaking support for node.js 14 HOT 2
- Does node-mssql support multiSubnetFailover? HOT 9
- Suspected memory leak when streaming data HOT 4
- arrayRowMode returns a function for the column type HOT 6
- Problem with Persian/Arabic characters when using input() HOT 4
- Testing library-using code in Jest results in SyntaxError: Unexpected token 'export' HOT 1
- Request timeouts in mssql/msnodesqlv8 when using default Pool size HOT 4
- Bulk Insert row lock not supported HOT 1
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.