@SqlCall on Sql Object API

[Brian McCallister]

I just checked in initial support for @SqlCall within the sql object API

https://github.com/brianm/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/sqlobject/TestSqlCall.java

I kind of punted on out parameters for now, there is no out param per
se, and the return value must be either null or OutParameters. I'll
try to work out a decent mechanism for getting at out params and
putting them on the return value, and/or something like:

public void foo(@Out("waffle") Reference<Double> waffle)

which is grody, but works nicely in JNA and their ilk.

Assuming no one barfs on the code (
https://github.com/brianm/jdbi/commit/5aa2b4781f608e85bca2666d87bcdbe4a28a9e75
comments! ) I'll probably cut a release with it shortly.

-Brian

[Sathish V]

Hi Brian,

Could you please give a sample code snippet on how to use the @SqlCall that returns an OutParameter.

The TestSqlCall shows a stored proc call that does an insert.

Thanks in advance.

Regards

Sathish

[Brian McCallister]

On Sun, Dec 2, 2012 at 5:56 PM, Sathish V <sathi...@gmail.com> wrote:

Hi Brian,

Could you please give a sample code snippet on how to use the @SqlCall that returns an OutParameter.

The TestSqlCall shows a stored proc call that does an insert.

They don't work right now. If you need support, I can take a stab. I don't like the "return the OutParameters" approach, to be honest, but don't have a much better one. I would prefer to avoid parsing the statements to figure out what is in and what is out, so that requires declaring. Given a ~ideal api (for single out param):

interface Wombat {

  @SqlCall(":num = call foo(:val)")

  public int foo(@Bind("val") String val);

}

We would need to parse to understand :num was an out param, and register it on the statement :-(

Declaring would mean something like:

interface Wombat {

  @SqlCall(":num = call foo(:val)")

  @OutParam("num")

  public int foo(@Bind("val") String val);

}

Which is icky, but not horrid. Where it gets tricky is greater then one out param, you basically get into ~result mapping (okay, single value is already there, in truth). This is hairy as the, for unfortunate historic reasons where we took a large patch which in hindsight we shouldn't have, callable statement handling is different from everything else in jdbi :-(

I'd be game to fix the whole of callable statement handling, but that doesn't solve your immediate problem as it will take a while :-(

-Brian

 

Thanks in advance.

Regards

Sathish

On Monday, March 12, 2012 6:23:20 PM UTC-4, Brian McCallister wrote:

I just checked in initial support for @SqlCall within the sql object API

https://github.com/brianm/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/sqlobject/TestSqlCall.java

I kind of punted on out parameters for now, there is no out param per
se, and the return value must be either null or OutParameters. I'll
try to work out a decent mechanism for getting at out params and
putting them on the return value, and/or something like:

public void foo(@Out("waffle") Reference<Double> waffle)

which is grody, but works nicely in JNA and their ilk.

Assuming no one barfs on the code (
https://github.com/brianm/jdbi/commit/5aa2b4781f608e85bca2666d87bcdbe4a28a9e75
comments! ) I'll probably cut a release with it shortly.

-Brian

--
 
 

[Sathish V]

Thanks a lot for your information.

Callable statement handling would be a very important and great to have feature.

Regards

Sathish

[Sathish V]

Hi Brian,

I gave a try on implementing the @SqlCall for multiple outparameters.Below is the sample code snippet.

  @SqlCall("CALL pkg_rules.getrulecount(:personId,:ruleId,:ruleName,:totalCount,:delDetail)")

    @OutParams({@Out(param ="ruleName",paramType=OracleTypes.VARCHAR),@Out(param ="totalCount",paramType=OracleTypes.VARCHAR),@Out(param ="delDetail",paramType=OracleTypes.CURSOR,mapper="com.test.sathish.flow.dao.mapper.RulesCountMapper")})

   Map<String,Object> callProc(@Bind("personId") long personId,@Bind("ruleId") long ruleId);

This returns a Map<String,Object> i.e. the Key will be the bind parameter name (prefixed with colon) and value will be the result of the.

@OutParams is an anotation which takes the array of Out

@Out is the annotation that takes param,param type and the mapper.

I updated the org.skife.jdbi.v2.sqlobject.CallHandler.invoke

   @Override

    public Object invoke(HandleDing ding, Object target, Object[] args, MethodProxy mp)

    {

        Handle h = ding.getHandle();

        try {

   h.getConnection().setAutoCommit(false); //bcoz for cursor result it was throwing error

} catch (SQLException e1) {

   // TODO Auto-generated catch block

   e1.printStackTrace();

}

        Call call = h.createCall(sql);     

                       

        OutParams outParams = method.getRawMember().getAnnotation(OutParams.class);

        Out[] outs = outParams.value();

        List<String> outParamsNames = new ArrayList<String>();

        for(Out out : outs){

            if(!out.mapper().equals("it")){

        CallableStatementMapper resultMapper = null;

try {

   Class mapper = Class.forName(out.mapper());        

   resultMapper = (CallableStatementMapper) mapper.newInstance();

} catch (Exception e) {

   // TODO Auto-generated catch block

   e.printStackTrace();

}

        call.registerOutParameter(out.param(), out.paramType(),resultMapper);        

            }else{

                call.registerOutParameter(out.param(), out.paramType());

            }

            outParamsNames.add(out.param());

        }

        

        populateSqlObjectData((ConcreteStatementContext)call.getContext());

        applyCustomizers(call, args);

        applyBinders(call, args);               

        OutParameters ou = call.invoke();

        Map<String,Object> result = new HashMap<String,Object>();

        

        for(String name : outParamsNames){

            result.put(name, ou.getObject(name));

        }

                       

        if (returnOutParams) {

            return result;

        }

        else {

            return null;

        }

       Without changing anything else,was able to get this working.Please provide your suggestions.

       

Regards

Sathish

On Monday, December 3, 2012 11:31:45 AM UTC-5, Brian McCallister wrote:

[Brian McCallister]

This looks generally good, I would really like if it went through the same result set munging behavior as a query, admittedly usually one with but one row. 

-Brian

--
 
 

[Denis Kudryashov]

So. Is it implemented for last version 2.48?

вторник, 13 марта 2012 г., 2:23:20 UTC+4 пользователь Brian McCallister написал:

[Danny Gershman]

Same question.  Is this implemented in 2.48?

[Brian McCallister]

Nothing has changed with regard to @SqlCall in 2.48 or 2.49 -- I am all for doing this, just don't personally have the time *right now.* Pull requests/patches/etc very welcome, and I am super happy to help coach anyone through the changes.

-Brian

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Danny Gershman]

Is it possible to do this with SqlQuery and a ResultSetMapper?  Essentially I want to call a StoredProcedure with OutputParameters.

[Tonny Staunsbrink]

Hi

I just stumbled upon this post while trying to create a DAO interface for invoking a stored procedure getting back the output. Has anything happened on it the last year since this post, is it solved, is there a design for solution, or something else?

Cheers

Tonny

[Danny Gershman]

Yes i have the same question.  Does jdbi support OutputParameters on ResultSetMapper?

[Michael Seele]

I'm not shure if you still need an answer but i found a solution which works by creating my own annotation "OutParameter":

@SqlCall("BEGIN TEST.TOOLS.CREATE_NEW_WORKPLACE(:str_workplace, :n_new_workplace_id); END;")

@OutParameter(name = "n_new_workplace_id", sqlType = Types.BIGINT)

OutParameters createNewWorkplace(@Bind("str_workplace") String workplaceName);

I access the out parameter like this: 

OutParameters p = dao.createNewWorkplace(workplaceName);

return p.getLong("n_new_workplace_id").longValue(); //$NON-NLS-1$

And here is the annotation i've wrote:

import java.lang.annotation.Annotation;

import java.lang.annotation.ElementType;

import java.lang.annotation.Retention;

import java.lang.annotation.RetentionPolicy;

import java.lang.annotation.Target;

import java.lang.reflect.Method;

import java.sql.SQLException;

import org.skife.jdbi.v2.Call;

import org.skife.jdbi.v2.SQLStatement;

import org.skife.jdbi.v2.sqlobject.SqlStatementCustomizer;

import org.skife.jdbi.v2.sqlobject.SqlStatementCustomizerFactory;

import org.skife.jdbi.v2.sqlobject.SqlStatementCustomizingAnnotation;

@SqlStatementCustomizingAnnotation(OutParameter.Factory.class)

@Target(ElementType.METHOD)

@Retention(RetentionPolicy.RUNTIME)

public @interface OutParameter {

String name();

int sqlType();

static class Factory implements SqlStatementCustomizerFactory {

@Override

public SqlStatementCustomizer createForType(Annotation annotation, @SuppressWarnings("rawtypes") Class sqlObjectType) {

throw new UnsupportedOperationException("Not allowed on Type"); //$NON-NLS-1$

}

@Override

public SqlStatementCustomizer createForMethod(Annotation annotation, @SuppressWarnings("rawtypes") Class sqlObjectType, Method method) {

final OutParameter outParam = (OutParameter) annotation;

return new SqlStatementCustomizer() {

@Override

public void apply(@SuppressWarnings("rawtypes") SQLStatement q) throws SQLException {

assert q instanceof Call;

((Call) q).registerOutParameter(outParam.name(), outParam.sqlType());

}

};

}

@Override

public SqlStatementCustomizer createForParameter(Annotation annotation, @SuppressWarnings("rawtypes") Class sqlObjectType, Method method, final Object arg) {

throw new UnsupportedOperationException("Not defined for parameter"); //$NON-NLS-1$

}

}

}

It's not perfect but it works :)

Would be nice if the developers integrate this directly into jdbi

[Steven Schlansker]

On May 28, 2015, at 5:13 AM, Michael Seele <mse...@gmail.com> wrote:

> I'm not shure if you still need an answer but i found a solution which works by creating my own annotation "OutParameter":
>

> It's not perfect but it works :)
> Would be nice if the developers integrate this directly into jdbi

If you prepare a PR, we can definitely consider it for inclusion :)
Thanks!

[Matthew Hall]

This is a great idea. Opened https://github.com/jdbi/jdbi/issues/405 to track this feature.