Whats wrong with this Statement in groovy SQL usage?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Whats wrong with this Statement in groovy SQL usage?

Christian Paulsen

Hi

I 'm using groovy and groovy sql in a framework. It works really very well, but with the executePreparedQuery I get some problems with this statement:

nresults = sql.executePreparedQuery(selprepstr, felder)




class GSelectPrep extends SelectBase {
    private Log log = LogFactory.getLog(GSelectPrep.class);
    DataSource ds = null;
    GTableInfo ti = null;
    def valmap = null;
    String selprepstr = null;
    Sql sql = null;
    protected ResultSet nresults = null

    public GSelectPrep(String dbsynonym, String table)
            throws SQLException, LKVException {
        try {
            log.debug("new GSelectPrep")
            DataSourceManager dsm = DataSourceManager.getInstance();
            ds = dsm.getDataSource(dbsynonym).getDataSource()
            sql = new Sql(ds)
            ti = new GTableInfo(dbsynonym, table);

        } catch (SQLException e) {
            mStatus = false;
            mSqlcode = e.getErrorCode();
            mSQLMessage = e.getMessage();
            log.error(getSqlMessage(), e);
        }
    }

    def prepare(String fieldlist = null) {
        def okmap = [true: 'Insert erfolgreich:', false: 'Insert abgelehnt:']
        def ok = false;
        if (fieldlist == null) selprepstr = ti.baueNamedPreparedSelectStr();
        else selprepstr = ti.baueNamedPreparedSelectStrFromFieldList(fieldlist)
        log.debug("prepstr:" + selprepstr);
    }

    def setselprepstr(String prepstr) {
        selprepstr = prepstr
        log.debug("prepstr:" + selprepstr);
    }

    def select(GenJavaBean gjb) {
        def ok = false;
        def resultliste = []
        log.info("gjb:" + gjb);
        def valmap = ti.baueNamedMap("", gjb);
        log.debug("valmap:" + valmap);

        def anzrows = 0
        try {
            log.info("selprepstr:" + selprepstr);
            // sql.execute(selprepstr,valmap);
            def items = ti.getItemList()
            sql.eachRow(selprepstr, valmap) {
                row ->
                    anzrows++;
                    GenJavaBean getgjb = new GenJavaBean();
                    items.each() { item ->
                        def feldname = item.getFeldName();
                        getgjb.setProperty(item.getFeldName(), row."${feldname}");
                    }
                    //getgjb.backup();
                    log.info(getgjb)
                    resultliste.add(getgjb);

            }
        } catch (SQLException e) {
            log.error("SQLFEHLER", e);

        }
        return resultliste;
    }

    def selectNext(GenJavaBean gjb,String fields) {
       def ok = false;

        log.debug("gjb:" + gjb);
        def valmap = ti.baueNamedMap("", gjb);
        log.info("valmap:" + valmap);
        def felder = []
        fields.split(",").each {
            felder << valmap[it];
        }


        log.debug("Das sind die Felder ${felder}");
        def anzrows = 0
        try {
            log.debug("selectnext selprepstr:" + selprepstr);
            nresults = sql.executePreparedQuery(selprepstr, felder)
            log.debug("GetRow:"+nresults.getRow());
            ok = true;
        } catch (SQLException e){
            log.error("SQLFEHLER", e);
        }
       return ok;
    }
    def getNextResult(){
           def items = ti.getItemList()
            GenJavaBean getgjb = null;
           if (nresults.next()){
                    getgjb = new GenJavaBean();
                    items.each() { item ->
                        def feldname = item.getFeldName();
                        getgjb.setProperty(item.getFeldName(), nresults."${feldname}");
                    }
                  }
        return getgjb;
    }
    public void close() {
        sql.close();
    }

    public void info(String info) {
        //@hint Platzhalter fuer closelog
        boolean verbose = false;
        if (verbose) {
            log.info(info);
        }

    }
}

I get this LOG:


180206 141950 DEBUG de.lkvnrw.sql.statements.GSelectPrep:selectMethod: Das sind die Felder [276000513000002]

20180206 141950 DEBUG de.lkvnrw.sql.statements.GSelectPrep:selectMethod: selectnext selprepstr:SELECT  * FROM testtabkuh where lom >=  ?

20180206 141950 ERROR de.lkvnrw.sql.statements.GSelectPrep:selectMethod: SQLFEHLER
java.sql.SQLException: You can't operate on a closed ResultSet!!!
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
        at com.mchange.v2.c3p0.impl.NewProxyResultSet.getRow(NewProxyResultSet.java:3670)
        at org.codehaus.groovy.vmplugin.v7.IndyInterface.selectMethod(IndyInterface.java:232)
        at de.lkvnrw.sql.statements.GSelectPrep.selectNext(GSelectPrep.groovy:107)

Line 107:

log.debug("GetRow:"+nresults.getRow());

Caught: java.sql.SQLException: You can't operate on a closed ResultSet!!!
java.sql.SQLException: You can't operate on a closed ResultSet!!!
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
    at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2718)
    at de.lkvnrw.sql.statements.GSelectPrep.getNextResult(GSelectPrep.groovy:117)

Then I highlight the Statement sql.executePreparedQuery(selprepstr, felder)

I get in IdeaC the information that I exceed the acess rights.

With regards Christian





--
Christian Paulsen
[hidden email]
WWW: http://leever-miteenanner.de
Festnetz: +49(0)46231898011
mobil: +49(0)1705810744
Twitter: https://twitter.com/flens111