Tedious: Get id of inserted item

[ghost]

I am making an insert and the ID is auto generated in the DB. I need that auto-generated ID on the callback from the request. I tried listening to the doneInProc event, but nothing returns from that event.

[patriksimek]

You should add select @@identity to your t-sql command.

var connection = new Connection(config);
connection.on('connect', function(err) {
    if (err) {
        console.log(err);
    } else {
        request = new Request("insert into insert_test (text) values ('foo'); select @@identity", function(err, rowCount) {
            if (err) {
                console.log(err);
            } else {
                console.log('Insert complete.');
            }

            connection.close();
        });

        request.on('row', function(columns) {
            console.log('New id: %d', columns[0].value);
        });

        connection.execSql(request);
    }
});

[ghost]

Thanks this works.

[sarahpy]

what if i would like to use the result from sql (columns[0].value) outside the connection.on('connect', function(err) {) what should i do?

[cortfritz]

If you want to run outside of that callback you will need to ensure it has first completed. I suggest using async (npm async to install) to force synchronous behavior.

Alternatively you can set a property in that callback and check the property in other asynch functions.

But I live within that callback. I use it as an initializer and pass the connection I receive to my modules that need the connection. That seems more inline with the way node works.

(Sent from mobile so may contain thumbed errors)

On Feb 4, 2014, at 5:55 PM, sarahpy [email protected] wrote:

what if i would like to use the result from sql (columns[0].value) outside the connection.on('connect', function(err) {) what should i do?

—
Reply to this email directly or view it on GitHub.

[SyRenity]

Just wanted to add my 2c that it works great with the Promise pattern (Q), allowing to use the result via resolve().

[arthurschreiber]

Another, and in my opinion better, alternative is to use the OUTPUT clause in your INSERT statement.

[lee-houghton]

Just be careful using @@identity. It will work fine if you don't use triggers, but if you (or your DBA) add a trigger, you may find that @@identity returns what was created by the trigger. You can use scope_identity() to refer to the original ID.

[SyRenity]

@arthurschreiber @lee-houghton thanks for the tip, scope_identity() is a better choice indeed.

[Nedlinin]

@arthurschreiber : Wanting to go the route of the output clause but I can't seem to get it to play nicely with Tedious.

Following statement works in SQL Management Studio:

INSERT INTO dbo.direct(employeeNumber, job,phase, chargeCode)
OUTPUT INSERTED.ID
VALUES(1, 2 3, 4)

However, if I try to convert that to a Tedious call with parameters:

    const sqlStatement = "INSERT INTO " +
        "dbo.direct(employeeNumber, job, phase, chargeCode) " +
        "OUTPUT @ID=INSERTED.ID " +
        "VALUES(@employeeNumber, @job, @phase, @chargeCode)";

    const request = db.createReq(sqlStatement, cb);

    request.addParameter("employeeNumber", TYPES.Int, dlInfo.employeeID);
    request.addParameter("job", TYPES.Int, dlInfo.job);
    request.addParameter("phase", TYPES.Int, dlInfo.phase);
    request.addParameter("chargeCode", TYPES.Int, dlInfo.chargeCode);
    request.addOutputParameter("ID", TYPES.UniqueIdentifier);

     request.on("returnValue", function(parameterName, value) {
        console.log(value); // Always returns null?
    });

My console log always returns null for the value.

Mind pointing me at where I'm going wrong?

[arthurschreiber]

Is there a reason you're trying to use an output variable?

[Nedlinin]

@arthurschreiber : Yes. Stupidity :smile:

Went this route using on row and had no issues.

    request.on("row", function(columns) {
        console.log(columns[ 0 ].value);
    });

Sorry to bug ya!