Insert with SELECT@@IDENTITY (sqljdbc 1.2)

Hi!

We are porting a database from DB2 to SQL-Server 2005. We use WAS 5.1. We have problems with insert staments followed by an SELECT @.@.IDENTITY. The sql error is that no result set is returned. The syntax is "<insert statment>;\nSELECT @.@.IDENTITY.
We have no problems with this in the 1.1 version (there we have the known "read date" error).

Joachim

Joachim,

Thank-you for notifying us of this issue. We are investigating this problem.

Jimmy

|||

Hello Joachim,

Can you provide us with a standalone java repro. I'm interested in learning which of the Statement's execute method you are using and how you are using it. I tried to author a quick repro in house but was unsuccessful.

boolean returnValue = stmt.execute("insert into GenKeys values('text')", Statement.RETURN_GENERATED_KEYS);

System.out.println("Returned gen keys: " + returnValue);

rs = stmt.getGeneratedKeys();

while(rs.next())

{

System.out.println("Got back Generated Key = " + rs.getInt(1));

}

BTW: I am also curious to learn the intent of using select @.@.identity. JDBC provides functionality for accessing generated keys via the Statement.getGeneratedKeys() method.

Thanks,

Jaaved

|||

Hi!

We used a PreparedStatment with executeQuery(). It worked well with 1.21. The getGeneratedKeys() method is not supported by websphere 5.1 (wich is an IBM problem...). In 6.0 i think this is fixed. Until then we did find an other solution to our problem:

Code Snippet

public long getGeneratedKey(PreparedStatement ps) throws SQLException {
ResultSet rs = null;
try {
if (ps.getMoreResults()) {
rs = ps.getResultSet();
}
if (rs != null && rs.next()) {
return rs.getLong(1);
}
return -1;
} catch (SQLException e) {
try {
rs.close();
} catch (Exception ignore){}
throw e;
}
}

It′s not perfect but it seems to work (in cases with only one insert followed by an SELECT @.@. IDENTITY). Is the trouble with executeQuery() a bug or is that how it should be?

Joachim

insert or replace

Hi,
I'm porting an application using an SQLite database. In SQLite there is
a sql-statement named "INSERT OR REPLACE" which insert a record if it's
not already exist, and update it if it's already there. Is there
something similar in SQL Server? Or do I have to first do a SELECT to
see if the record already exist and then do either an INSERT or UPDATE?Hi,
In SQL Server you will have to use IF Exists. Use the below syntax...
IF Exists(Select statement)
Update
Else
Insert
Thanks
Hari
SQL Server MVP
<nyhetsgrupper@.gmail.com> wrote in message
news:1159729668.855789.212000@.h48g2000cwc.googlegroups.com...
> Hi,
> I'm porting an application using an SQLite database. In SQLite there is
> a sql-statement named "INSERT OR REPLACE" which insert a record if it's
> not already exist, and update it if it's already there. Is there
> something similar in SQL Server? Or do I have to first do a SELECT to
> see if the record already exist and then do either an INSERT or UPDATE?
>|||On 1 Oct 2006 12:07:48 -0700, nyhetsgrupper@.gmail.com wrote:
>Hi,
>I'm porting an application using an SQLite database. In SQLite there is
>a sql-statement named "INSERT OR REPLACE" which insert a record if it's
>not already exist, and update it if it's already there. Is there
>something similar in SQL Server? Or do I have to first do a SELECT to
>see if the record already exist and then do either an INSERT or UPDATE?
Hi nyhetsgrupper,
There is (unfortunately) no single keyword for INSERT OR UPDATE. I am
one of the many people who'd love to see Microsoft implement a MERGE or
UPSERT operation.
For now, we have to make do with one of these techniques:
A) For processing multiple (zero, one, or more) rows:
UPDATE d
SET DataCol1 = s.DataCol1,
DataCol2 = s.DataCol2
FROM Source AS s
INNER JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key,
-- or else unexpected things might happen.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT KeyCol1, KeyCol2, DataCol1, DataCol2
FROM Source AS s
LEFT JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key above.
-- Only one of the key columns suffices below.
WHERE d.KeyCol1 IS NULL;
B) For processing a single row if most rows are expected to be inserted.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT @.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2
WHERE NOT EXISTS
(SELECT *
FROM Dest
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2);
IF @.@.ROWCOUNT = 0
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
C) For processing a single row if most rows are expected to be updated.
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
IF @.@.ROWCOUNT = 0
INSERT INTO Dest
(KeyCol1, KeyCol2, DataCol1, DataCol2)
VALUES (@.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2);
Hugo Kornelis, SQL Server MVP

insert or replace

Hi,
I'm porting an application using an SQLite database. In SQLite there is
a sql-statement named "INSERT OR REPLACE" which insert a record if it's
not already exist, and update it if it's already there. Is there
something similar in SQL Server? Or do I have to first do a SELECT to
see if the record already exist and then do either an INSERT or UPDATE?
Hi,
In SQL Server you will have to use IF Exists. Use the below syntax...
IF Exists(Select statement)
Update
Else
Insert
Thanks
Hari
SQL Server MVP
<nyhetsgrupper@.gmail.com> wrote in message
news:1159729668.855789.212000@.h48g2000cwc.googlegr oups.com...
> Hi,
> I'm porting an application using an SQLite database. In SQLite there is
> a sql-statement named "INSERT OR REPLACE" which insert a record if it's
> not already exist, and update it if it's already there. Is there
> something similar in SQL Server? Or do I have to first do a SELECT to
> see if the record already exist and then do either an INSERT or UPDATE?
>
|||On 1 Oct 2006 12:07:48 -0700, nyhetsgrupper@.gmail.com wrote:

>Hi,
>I'm porting an application using an SQLite database. In SQLite there is
>a sql-statement named "INSERT OR REPLACE" which insert a record if it's
>not already exist, and update it if it's already there. Is there
>something similar in SQL Server? Or do I have to first do a SELECT to
>see if the record already exist and then do either an INSERT or UPDATE?
Hi nyhetsgrupper,
There is (unfortunately) no single keyword for INSERT OR UPDATE. I am
one of the many people who'd love to see Microsoft implement a MERGE or
UPSERT operation.
For now, we have to make do with one of these techniques:
A) For processing multiple (zero, one, or more) rows:
UPDATE d
SET DataCol1 = s.DataCol1,
DataCol2 = s.DataCol2
FROM Source AS s
INNER JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key,
-- or else unexpected things might happen.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT KeyCol1, KeyCol2, DataCol1, DataCol2
FROM Source AS s
LEFT JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key above.
-- Only one of the key columns suffices below.
WHERE d.KeyCol1 IS NULL;
B) For processing a single row if most rows are expected to be inserted.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT @.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2
WHERE NOT EXISTS
(SELECT *
FROM Dest
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2);
IF @.@.ROWCOUNT = 0
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
C) For processing a single row if most rows are expected to be updated.
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
IF @.@.ROWCOUNT = 0
INSERT INTO Dest
(KeyCol1, KeyCol2, DataCol1, DataCol2)
VALUES (@.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2);
Hugo Kornelis, SQL Server MVP

insert or replace

Hi,
I'm porting an application using an SQLite database. In SQLite there is
a sql-statement named "INSERT OR REPLACE" which insert a record if it's
not already exist, and update it if it's already there. Is there
something similar in SQL Server? Or do I have to first do a SELECT to
see if the record already exist and then do either an INSERT or UPDATE?Hi,
In SQL Server you will have to use IF Exists. Use the below syntax...
IF Exists(Select statement)
Update
Else
Insert
Thanks
Hari
SQL Server MVP
<nyhetsgrupper@.gmail.com> wrote in message
news:1159729668.855789.212000@.h48g2000cwc.googlegroups.com...
> Hi,
> I'm porting an application using an SQLite database. In SQLite there is
> a sql-statement named "INSERT OR REPLACE" which insert a record if it's
> not already exist, and update it if it's already there. Is there
> something similar in SQL Server? Or do I have to first do a SELECT to
> see if the record already exist and then do either an INSERT or UPDATE?
>|||On 1 Oct 2006 12:07:48 -0700, nyhetsgrupper@.gmail.com wrote:

>Hi,
>I'm porting an application using an SQLite database. In SQLite there is
>a sql-statement named "INSERT OR REPLACE" which insert a record if it's
>not already exist, and update it if it's already there. Is there
>something similar in SQL Server? Or do I have to first do a SELECT to
>see if the record already exist and then do either an INSERT or UPDATE?
Hi nyhetsgrupper,
There is (unfortunately) no single keyword for INSERT OR UPDATE. I am
one of the many people who'd love to see Microsoft implement a MERGE or
UPSERT operation.
For now, we have to make do with one of these techniques:
A) For processing multiple (zero, one, or more) rows:
UPDATE d
SET DataCol1 = s.DataCol1,
DataCol2 = s.DataCol2
FROM Source AS s
INNER JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key,
-- or else unexpected things might happen.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT KeyCol1, KeyCol2, DataCol1, DataCol2
FROM Source AS s
LEFT JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key above.
-- Only one of the key columns suffices below.
WHERE d.KeyCol1 IS NULL;
B) For processing a single row if most rows are expected to be inserted.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT @.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2
WHERE NOT EXISTS
(SELECT *
FROM Dest
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2);
IF @.@.ROWCOUNT = 0
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
C) For processing a single row if most rows are expected to be updated.
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
IF @.@.ROWCOUNT = 0
INSERT INTO Dest
(KeyCol1, KeyCol2, DataCol1, DataCol2)
VALUES (@.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2);
Hugo Kornelis, SQL Server MVP