AdoDaoSupport (AdoTemplate) with output parameters

[SilvioSantoZ]

Hello all,

I am sucessfully using AdoDaoSupport as base class for my DAO classes, invoking such methods as ExecuteNonQuery, QueryWithResultSetExtractor, etc on a MS-SQL server.
Now I am trying to implement two methods I can't figure out: Save() and StoredProcedure execution with output parameters.

In the Save() case, I must code a pseudo-procedure in which I fist INSERT the record and then retrieve the generated ID via SCOPE_IDENTITY() or something like that via output parameter.

In the SP-with-OUTPUT case, I need to do a SP invocation (which returns a resultset, so I use ResultSetExtractor), but also need to be able to get at the output parameters.

Is there a way to do these things?
Thanks a lot.

[Mark Pollack]

Hi,

Getting at the generated value is a specific case that I would like to have more explicity support for in the API, as in the Java version via the KeyHolder interface. This will be addressed for the RC1 release. I've made a JIRA issue to keep track of it.

However, for now there are two options I can think of. The first is to use the method


IList QueryWithCommandCreator(IDbCommandCreator commandCreator,
IResultSetExtractor resultSetExtractor,
IDictionary returnedParamters,
IDbParameters declaredParameters)


or



IDictionary QueryWithCommandCreator(IDbCommandCreator commandCreator,
IList resultProcessors,
IDbParameters declaredParameters)

I realize using IDbCommandCreator is less convenient than the other "one-liner" methods. In the second QueryWithCommandCreator case the list contains an instance of NamedResultSetProcessor which associates a name (key) with either a IRowMapper, IResultSetExtractor, or IRowCallback (value). You get the results from the result processor via the returned IDictionary in addition to any other output or input-output parameters.

If you are saving in the stored procedure you can return SCOPE_IDENTITY() as an output parameter or you can use Sql Server batch sql if you are using CommandText. This should work for both the cases you mention.

Another alternative is to use the class StoredProcedure which uses QueryWithCommandCreator in its implementation. In this case you create an instance of the class declare the in/out parameters (either explicitly or via DeriveParameters), then call AddResultSetExtractor(string name, IResultSetExtractor resultSetExtractor), and finally "Compile". See the reference docs for a basic example. Using the StoredProcedure class can be a real time-saver if you use DeriveParameters and pass in the input parameters in the same order as they are declared in the stored procedure to one of the ExecuteXXX methods.

Mark

[SilvioSantoZ]

Hello Mark, thanks for your reply.

I came back to this subject after a few weeks. I found a little inconvenience: I'm using the Generic version of AdoDaoSupport/AdoTemplate, and it does not have the QueryWithCommandCreator methods. No matter, I realized I can get to the non-generic version via the ClassicAdoTemplate property.

But it seems my brain doesn't allow me to comprehend IDbCommandCreator and QueryWithCommandCreator; I tried to find an example looking through the source code but can't find one. To be truthful, I found some usages in the Spring.Data.Objects namespace (AdoQuery/StoredProcedure as you mentioned), but those are using an IDbCommandCreatorFactory - which looks like an interface but seems to be a class, adding to my confusion.

Could you please provide an example for a simple batch-sql INSERT+SCOPE_IDENTITY-as-output-parameter case?

Thanks a lot.

[Mark Pollack]

Hi,
Ya know, I don't know wht the hell I was thinking before - what I suggested is probably the ultimate in flexibility but hard to use and in fact the QueryWithCommandCreator is essentially a method to support the Spring.Data.Objects functionality and not for typical spring data access code. BTW, there probably will be a version of that method in the generic version of AdoTemplate so that the generic delegates for rowmapper's etc can be specified instead of the interface based definitions.

Here is an easier approach. You can find the code in the Spring.Data.Integration.Tests assembly, namespace Spring.Data.Northwind, class AdoTemplaetShipperDao.



public Shipper Create(string name, string phone)
{
string sql = "INSERT INTO Shippers (CompanyName, Phone) VALUES (@CompanyName, @Phone) SET @ShipperID = SCOPE_IDENTITY()";

IDbParameters dbParameters = AdoTemplate.CreateDbParameters();
dbParameters.Add("CompanyName", SqlDbType.NVarChar, 40).Value = name;

if (phone.Length == 0)
dbParameters.Add("Phone", SqlDbType.NVarChar, 24).Value = DBNull.Value;
else
dbParameters.Add("Phone", SqlDbType.NVarChar, 24).Value = phone;



dbParameters.AddOut("ShipperID", SqlDbType.Int);

int id = AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, dbParameters);

return new Shipper(id, name, phone);
}


Sorry for the confusion.

Cheers,
Mark

[vansickle]

i think instead of


int id = AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, dbParameters);

it must be for example

AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, dbParameters);

int id = (int)dbParameters["@ShipperID"].Value;

and can we use the same scenario with DbParametersBuilder?

[Mark Pollack]

Hi,
Yes, that was a mistake using the return value. You can also use DbParametersBuilder, passing in the instance of IDbParameter from the call


IDbParameters dbParameters = builder.GetParameters();

Cheers,
Mark

[mmistroni]

Mark,
still no luck
i m using driver for sybase 12.5.
here's code


IDbParameters dbParameters = adoTemplate.CreateDbParameters();
dbParameters.Add("@TaskID", DbType.Int64).Value = 28070;
IList result = adoTemplate.QueryWithRowMapper(
CommandType.StoredProcedure,
"sp_bat_get_sql_tsk",
new TestRowMapper(),
dbParameters);


and here's exception


SpringNba.Tests.SpringAdoTest.testCallStoredProced ure : Spring.Data.UncategorizedAdoException : CommandCallback; uncategorized DataException for SQL [sp_bat_get_sql_tsk]; ErrorCode [<no error code>]; ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Procedure sp_bat_get_sql_tsk expects parameter @TaskID, which was not supplied.

----> System.Data.Odbc.OdbcException : ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Procedure sp_bat_get_sql_tsk expects parameter @TaskID, which was not supplied.


do i need to upgrade my driver?

thanks an dregards
marco

[Mark Pollack]

Hi Marco,

There is some overlap with this thread (http://forum.springframework.net/showthread.php?p=9763#post9763).

Try passing in the command text "sp_bat_get_sql_tsk @TaskID". I've verified this to work with ASE 15.

Cheers,
MArk

[patmeth]

Mark,
I tried your code below using a stored procedure with output parameters:
int id = AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, dbParameters);

The id returned is the count of rows. To get the value of the output parameter I have to use dbParameters["@ShipperId"].Value

Thanks for the start anyway.

Do you have any suggestions as to where I can look to find samples for using transactions between Dao's in Spring.

Regards,
Glenn

[Mark Pollack]

Hi Glenn,

You can start with the Transactions Quick start. The docs are part of the reference guide here (http://www.springframework.net/doc-latest/reference/html/tx-quickstart.html) online and look into the distribution for the code.

Are you all settled with output parameter functionality?

Cheers,
Mark

[CNemo7539]

Hi friends!

I've noticed minor anomaly with return values and IDbParameters implementation.
Used MS SQL server and code similar to shown here. However was unable to get access to the return value after using ExecuteNoQuery on SP returning value. It was always throwing exception that parameter is not found in collection. Until I tried access return value by index and it worked!

So debugging showed that parameter name in collection is prepended with @. Which I can understand taking in account that it's MS SQL. However spring does decent job hiding such details when declaring parameters, so when one add param he doesn't care about @. So did I.

I consider it a minor bug which must be fixed. However DbProvider has methods to create parameter names, which might be used to generate names. So, may be I do not understand something? Please explain what will be a pattern to use these methods.