Teradata .NET Paramaters: Can I use Output parameters with SQL (ie, not using a stored procedure)?

Question

I'd like to do something like this:

Dim pId As TdParameter = New TdParameter With {.ParameterName = "p_id", .SourceColumn = "id", .TdType = TdType.Integer, .Direction = ParameterDirection.Output}

And then go like:

cmd.Parameters.Add(pId);

cmd.CommandText = "insert into employee (id, name, age) values (?, 'bob', 34)"

cmd.ExecuteNonQuery();

And then have pId.Value return the value of the new record's Id column (which is an identity column). If I could do that, that would be great.

score 1 · Answer 1 · answered Jul 14 '12 at 16:59

1

The execution of the INSERT statement would never have a resultset returned. At most you may get an activity count returned to the client. To obtain the value of the identity column would require a SELECT statement after the INSERT has completed.

answered Jul 14 '12 at 16:59

Rob Paller

7,506
23
23

Just now getting back to this. I ended up trying your method and it works, but only intermittently. Sometimes, I get the exception `Transaction ABORTed due to Deadlock`. All of this is occurring within a transaction in VB.NET code. Within the same transaction after I insert the record, I do this: `locking row for access select id from dbname.employee where emailaddress = ?`. Is the row locking what's causing the problem? Should I remove it? I am still learning Teradata and haven't yet learn when I should and shouldn't use row locking. – oscilatingcretin Jul 30 '12 at 19:14
The LOCKING modifier is not the reason for the error message you are seeing. Do you have Query Logging enabled so you can see what other transactions are running at the same time you are attempting to modify the EMPLOYEE table? – Rob Paller Jul 30 '12 at 20:49
Check out the recent update to my question. I haven't gotten the locking issue since then. Maybe the locking in my SQL wasn't causing it, but perhaps doing a select in the middle of the transaction was causing issues. Dunno. – oscilatingcretin Jul 31 '12 at 14:35
If you look at my code, it begs the question of whether or not you can select the row you just inserted using raw SQL or if my example is made possible only by the Teradata .NET provider. – oscilatingcretin Jul 31 '12 at 15:07
ACCESS locking was introduced with the intent to allow dirty reads of the table while is it being modified by another process. If you attempt to read a record (or records) that is part of a transaction that has not yet been committed you run the risk of a deadlock or being blocked. I bet if you had used a READ lock instead of an ACCESS lock you may have ended up blocked instead of deadlocked. – Rob Paller Jul 31 '12 at 16:22

score 0 · Accepted Answer · answered Oct 20 '15 at 11:16

Assume a table with:

ID integer (identity column generated by default no cycle)
Name varchar(20)
Email varhcar(50)

Instead of using .ExecuteNonQuery() for your insert statement, use .ExecuteReader() instead. It will actually return the record that was just inserted, complete with the Identity column's value!

Using cmd As TdCommand = MyConnection.CreateCommand
    cmd.GeneratedDataBehavior = GeneratedDataBehavior.IdentityColumn
    cmd.CommandText = "insert into mydb.employee (name, email) values ('bob', 'bob@snob.com')"
    Using reader As TdDataReader = cmd.ExecuteReader
        While reader.Read
            Id = reader.GetInt64(reader.GetOrdinal("Id"))
            iRowsInserted += 1
        End While
    End Using
End Using

Teradata .NET Paramaters: Can I use Output parameters with SQL (ie, not using a stored procedure)?

2 Answers2