Re: Getting result of odbc's INSERT INTO command
- From: "Severian [MVP]" <severian@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 23 Apr 2005 17:32:33 GMT
On Sat, 23 Apr 2005 16:23:43 +0200, Joe <joerider@xxxxxxxxx> wrote:
>Hi everybody,
>
>I am currently writing a library for adding/deleting/querying records of
>a database. In this special case I have used the ODBC-C-Functions.
>
>What I need is the resultset of an INSERT INTO operation. For example
>there should be the id-column returned, if I add a new record.
>
>What I've right now:
>std::string sql_string="INSERT INTO table1 (field1) VALUES ('value1')";
>sr=SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
>sr=SQLExecDirect(hstmt,(SQLCHAR*) sql_string.data(),SQL_NTS);
>sr=SQLNumResultCols(hstmt, &nCols);
>
>In this situation I get no columns as result (nCols == 0)
>
>Can anybody make suggestions on how to solve a problem like this one?
INSERT INTO does not *return* any rows, thus the number of columns
returned is zero.
Assuming there is some unique way to identify the row besides the ID,
just SELECT id FROM table WHERE... to retrieve the just-inserted ID.
This is the most general solution, though some databases may have
other ways to do what you want.
Depending on the transaction isolation level, and assuming you're
using transactions and auto-increment IDs, you *may* be able to:
BEGIN TRANSACTION
INSERT INTO...
SELECT MAX(id) FROM table
END TRANSACTION
But I don't if this is failsafe under any specific conditions. The
same isolation levels that make this work may allow you to receive an
ID inserted by another connection, and not all databases support all
isolation levels.
--
Phillip Crews aka Severian
Microsoft MVP, Windows SDK
Posting email address is real, but please post replies on the newsgroup.
.
- Follow-Ups:
- References:
- Prev by Date: Questions of CSIDL
- Next by Date: Re: Questions of CSIDL
- Previous by thread: Getting result of odbc's INSERT INTO command
- Next by thread: Re: Getting result of odbc's INSERT INTO command
- Index(es):
Relevant Pages
|
Loading
