Returning key field from INSERT in SQL
Owen Watson (7/21/08 6:32PM)
David Dancy (7/22/08 8:55AM)
4D, Inc. (7/22/08 9:55AM)
Owen Watson (7/22/08 3:17PM)
David Dancy (7/22/08 3:53PM)
David Dancy (7/22/08 4:30PM)
David Dancy (7/23/08 8:58AM)
Owen Watson (7/23/08 10:27AM)
David Dancy (7/23/08 1:13PM)
Owen Watson (7/21/08 6:32 PM)
<23b8d990807202332h3ce1ea2av72ab177cce9980d3@...
I'm playing round with 4D2004, ODBC, and SQL Server (data going from
4D to SQL Server).
I've got the INSERT statement going fine; what I'd like to do is find
the key field value for the inserted record. I suppose I could do a
SELECT after I did the INSERT with the freshly created values, but I'd
really like to get the value from the INSERT call. I've looked thru
the SQL Server docs and can't quite see how to do it.
Any clues?
David Dancy (7/22/08 8:55 AM)
<650f557e0807211555q21f1c186r9a66a650880264e7@...
On Tue, Jul 22, 2008 at 12:09 AM, Pierre Leblanc <pierre@...
wrote:
That seems to be MS-SQL specific.
Yes, it is. Owen said he is working with SQL Server. Other DBs do
things differently.
DB2 is particularly powerful:
SELECT * FROM NEW TABLE (
UPDATE mytable
SET lastname = UPPER ( lastname )
WHERE keyvalue = 1
);
"NEW TABLE" for an UPDATE statement returns all the new values set by
the UPDATE. "OLD TABLE" returns all the old values from before the
UPDATE.
"NEW TABLE" for an INSERT returns all the inserted values; "OLD TABLE"
for a DELETE returns all the deleted values.
ORACLE and PostgreSQL use a RETURNING clause (available for UPDATE,
DELETE and INSERT) that does much the same thing.
I'd love for 4D's SQL engine to be able to do something similar!
I got the example from O'Reilly's "SQL Pocket Guide", which is full of
comparisons of how 5 major SQL DBs do all the things that SQL DBs do.
It's a great resource.
David
--
David Dancy
Sydney, Australia
4D, Inc. (7/22/08 9:55 AM)
As David already illustrated, you must make sure to fetch the result
in
ODBC programming.
Owen Watson wrote:
ODBC LOGIN("connection";"user";"pwd")
$vt_Text_ODBC:="INSERT INTO news (title, date) VALUES
('Attempt4','20080731');"
ODBC EXECUTE($vt_Text_ODBC)
$vt_Text_ODBC:="SELECT id from news where id = SCOPE_IDENTITY();"
ARRAY LONGINT(value;0)
ODBC EXECUTE($vt_Text_ODBC;value)
ODBC LOGOUT
Whether or not the above SQL works, the result(s) are in a result set
so
you need to do ODBC LOAD RECORD to get it/them.
Kind regards,
Josh Fletcher
--
Josh Fletcher
Technical Services Team Member
4D, Inc.
4D Server v11 SQL has arrived!
Buy it NOW at <http://store.4ddepot.com/>
Owen Watson (7/22/08 3:17 PM)
<23b8d990807212017x3c88e481q620bf8fada034e02@...
Yes, I'm working with SQL Server.
The following code seems to not be returning anything in the value
variable, whatever variable type I'm using. id is the keyfield.
ODBC LOGIN("connection";"user";"pwd")
$vt_Text_ODBC:="INSERT INTO news (title, date) VALUES
('Attempt4','20080731');"
ODBC EXECUTE($vt_Text_ODBC)
$vt_Text_ODBC:="SELECT id from news where id = SCOPE_IDENTITY();"
ARRAY LONGINT(value;0)
ODBC EXECUTE($vt_Text_ODBC;value)
ODBC LOGOUT
The OK var shows 1 throughout,
David Dancy (7/22/08 3:53 PM)
<650f557e0807212253g1aa3bc9etd114c87c1db37ae0@...
Owen
On Tue, Jul 22, 2008 at 1:17 PM, Owen Watson <o.m.watson@...
wrote:
Yes, I'm working with SQL Server.
The following code seems to not be returning anything in the value
variable, whatever variable type I'm using. id is the keyfield.
ODBC LOGIN("connection";"user";"pwd")
$vt_Text_ODBC:="INSERT INTO news (title, date) VALUES
('Attempt4','20080731');"
ODBC EXECUTE($vt_Text_ODBC)
$vt_Text_ODBC:="SELECT id from news where id = SCOPE_IDENTITY();"
ARRAY LONGINT(value;0)
ODBC EXECUTE($vt_Text_ODBC;value)
ODBC LOGOUT
The OK var shows 1 throughout,
I think you may need to join the two statements into one and execute
them all at once. I believe that the default setting of ODBC is to
have "auto-committed transactions" which may affect the value of
SCOPE_IDENTITY() since it will be called outside the scope of the
original INSERT transaction. I'm guessing here, since I've never
actually tried it. Sorry!
I would try re-working your insert/query combo to say:
ARRAY LONGINT(value;0)
$vt_Text_ODBC:="INSERT INTO news (title, date) VALUES
('Attempt4','20080731');"
$vt_Text_ODBC:=$vt_Text_ODBC + "SELECT id from news where id =
SCOPE_IDENTITY();"
ODBC EXECUTE($vt_Text_ODBC;value)
Let us know if that works!
Cheers
David
--
David Dancy
Sydney, Australia
David Dancy (7/22/08 4:30 PM)
<650f557e0807212330s9420fcfk39d4415a063ba8bf@...
Correction again! It _does_ work.
Here's what to do:
C_LONGINT(value)
$vt_Text_ODBC:="INSERT INTO news (title, date) VALUES
('Attempt4','20080731');"
$vt_Text_ODBC:=$vt_Text_ODBC + "SELECT SCOPE_IDENTITY();"
ODBC EXECUTE($vt_Text_ODBC;value)
ODBC LOAD RECORD( ODBC All records)
That should do it!
--
David Dancy
Sydney, Australia
David Dancy (7/23/08 8:58 AM)
<650f557e0807221558v3099ff9ag51e8504cfe350647@...
Owen
Have you made sure that you cancelled the previous attempt to load
records on the connection? You have to issue ODBC CANCEL LOAD commands
religiously after each query to re-initialise the ODBC commands.
Thusly:
C_LONGINT(value)
$vt_Text_ODBC:="INSERT INTO news (title, date) VALUES
('Attempt4','20080731');"
$vt_Text_ODBC:=$vt_Text_ODBC + "SELECT SCOPE_IDENTITY();"
ODBC EXECUTE($vt_Text_ODBC;value)
ODBC LOAD RECORD( ODBC All records)
ODBC CANCEL LOAD `essential to reset ODBC system for next query
If you don't do this, ODBC can get its knickers in knots.
Another issue is the ODBC End selection command, which I'm not
convinced works properly. The manual has an example where it uses ODBC
End selection to detect if the record cursor has gone beyond the end
of the record set, but I found on my system that generated the "there
is no ODBC cursor" error, so I don't use that command any more.
Fortunately, you can just keep issuing ODBC LOAD RECORD(N) commands to
load N record(s) at a time, and test the OK variable to detect when
there are no more values to load (OK=0).
HTH
David
Owen Watson (7/23/08 10:27 AM)
<23b8d990807221527x18273eecq61680d0a0b414155@...
mmm. getting the ODBC error: there is no ODBC cursor.
2008/7/22 David Dancy <david.dancy@...
Correction again! It _does_ work.
Here's what to do:
C_LONGINT(value)
$vt_Text_ODBC:="INSERT INTO news (title, date) VALUES
('Attempt4','20080731');"
$vt_Text_ODBC:=$vt_Text_ODBC + "SELECT SCOPE_IDENTITY();"
ODBC EXECUTE($vt_Text_ODBC;value)
ODBC LOAD RECORD( ODBC All records)
David Dancy (7/23/08 1:13 PM)
<650f557e0807222013o1d0d9b7em8805b2b45ad92bc4@...
Owen
I suspect you'll have to go back to basics and see if a SELECT
COUNT(*) FROM ... WHERE ... statement will work with ODBC LOAD RECORD.
That's the simplest SQL command in terms of results coming back to 4D,
so it shouldn't have any problems.
If it works, the problem is in your code.
If it doesn't, the problem is somewhere in the ODBC driver/database/4D
command set combination. Not sure where I'd start to look.
If you'd like me to take a look at the code against my SQL Server, I'd
be happy to do so. Just email me off-list with the 4D program and a
backup of the relevant part of your SQL Server database.
Hopefully you'll get to the bottom of it soon.
David
Reply to this message
Summary created 7/23/08 at 2:07AM by Intellex Corporation
Comments welcome at: feedback@intellexcorp.com