Retrieving generated keys

I have the following scenario:
MyTable
id number entity(1, 1)
name nvarchar
sniplet of Java code:
String sql = "Insert Into MyTable (name) Values( 'MyName' )";
int updateCnt = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
The executeUpdate() method throws an java.lang.AbstractMethodError exception.
How do I get the value of "id" after running my insert statement?
The microsoft example goes through a lot of object instantiation with
prepared calls and all that I don't necessarily want to complicate the
previous code by doing all that. But if I do, could someone please help me
with the conversion? I always use the above approach.
Thanks.
The MS driver is a JDBC 2.0 implementation.
Statement.RETURN_GENERATED_KEYS is a JDBC 3.0 feature. Either use a
JDBC 3.0 driver or append SELECT SCOPE_IDENTITY() to all your INSERT
statements.
Alin,
The jTDS Project.
|||If youre using SQL Server why not do the insert through a stored
procedure?...this way you can use the Scope_Identity function to return
back the value of the newly inserted identity to Java..let me know if
you need some sample code...
|||Jimbo wrote:
> If youre using SQL Server why not do the insert through a stored
> procedure?...this way you can use the Scope_Identity function to
return
> back the value of the newly inserted identity to Java..let me know if
> you need some sample code...
You can use scope_identity() without a stored procedure. Just execute
something like "INSERT ... SELECT scope_identity()". The problem is
that the JDBC code won't work across databases.
Alin.

Retrieving and storing file timestamp

I have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.

So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?

Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?

Thanks,

Phil

Philsky wrote:

I have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.

So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?

No! That'd be nice wouldn't it. I've requested similar functionality in the past although not formally via the Product Feedback Center. Perhaps you could submit it?

Philsky wrote:

Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?

Thanks,

Phil

There's some code here that shows how you can access this information: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

Check lines 7 & 8 of the first code block.

-Jamie

retrieve result set generated by a stored procedure

I want to call other stored procedures in my stored procedure.
However, when I tried to call a stored procedure which return a result set rather than a single record, I don't know how to catch it in my stored procedures?

Are there some methods to catch up a result set returned by a stored procedure in another stored procedure?

Thanks!

Thomasinsert the resultset of the stored procedure into a table

create table
#tbl(spid int
, ecid int
, status varchar(15)
, loginame varchar(20)
, hostname varchar(15)
, blk int
, dbname varchar(15)
, cmd varchar(25))

insert into #tbl exec sp_who

select * From #tbl

Retrieve Guid after inserting recrod with NEWID()

Hi There,

I'm having a problem retreiving the auto generated Guid after inserting anew record with NEWID(), my stored proc is as follows:

SET @.uiTransactionID = NEWID()INSERT INTO Transactions (uiTransactionID) VALUES (@.uiTransactionID)IF @.@.ERROR = 0 AND @.@.ROWCOUNT = 1BEGIN SELECT @.uiTransactionID AS'@.@.GUID' RETURN 0END

And the return on my insert statement is:

command.ExecuteNonQuery();
m_uiTransactionID = (Guid

)command.Parameters["RETURN_VALUE"].Value;

I can never retreive the newly generated Guid, can onyone spot where i'm going wrong?

Many thanks

Ben

Hi,

i have tested your stored procedure and i think the stored procedure is correct. Please check your calling code for the stored procedure:

protected void CallStoredProcedure(){ Guid m_uiTransactionID; SqlCommand cmd =new SqlCommand("Test",new SqlConnection(ConfigurationManager.ConnectionStrings["DataBase"].ToString()));cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add("@.uiTransactionID", SqlDbType.UniqueIdentifier);cmd.Parameters["@.uiTransactionID"].Direction = ParameterDirection.Output;try { cmd.Connection.Open(); cmd.ExecuteNonQuery();m_uiTransactionID = (Guid)cmd.Parameters["@.uiTransactionID"].Value; }catch (Exception exc) { }finally { cmd.Connection.Close(); }}

And you can also check the stored procedure execute permissons.

Hope this can help you.

Regards
Marc André

|||

Hi Marc,

Thanks for your reply, you've helped me agreat deal and it is now working.

Many thnks

Ben