cancel
Showing results for 
Search instead for 
Did you mean: 

multiple sql statements from java

Former Member
0 Kudos

I am executing sql statements in MaxDB 7.6.04.12 from java using jdbc.

I want to execute multiple statements at a time, but it seems that however I separate the statements, I get

com.sap.dbtech.jdbc.exceptions.DatabaseException: [-3014] (at 433): Invalid end of SQL statement

I have tried separating the statements with just a semi-colon, with a semi-colon and new line, with newline-//-newline (as works with SQL Studio), but whatever I try I get this error or some other.

Is it possible to do this? and if so how?

Thanks

Chris

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Lars,

As I understand it - and I'm open to correction - the issue is the visibility of the temporary tables, and that is not about executing the statements in a single transaction, but executing them all from the same connection.

I think maybe our posts crossed - as I said above, I've found a workaround.

Thanks again for your help.

Chris

lbreddemann
Active Contributor
0 Kudos

HI Chris,

yep - temporary tables are just visible from within a single transaction. And one transaction is always bound to one session.

As I wrote: perhaps you don't need db-temporary tables. Perhaps you need something else, like data that you store in regular tables and access it later on and delete it again eventually.

As a matter of fact, temporary tables are most often misused for cases where views would have done the same thing.

Best regards,

Lars

p.s.

you are allowed to give points

Answers (4)

Answers (4)

Former Member
0 Kudos

Ok, done the points.

I'd love to have a discussion about the use of temporary tables, unfortunately time is short and I'm sure you have better things to do.

Thanks again,

Chris

Former Member
0 Kudos

I've refactored this to use standard jdbc calls instead of spring, and it seems to work ok.

So it's obviously something in Spring, and as such not a subject for this forum.

Thanks for your help

Chris

Former Member
0 Kudos

Sorry, I should have said the exception occurs on the line

jtSingle.execute(sql);

that occurs when executing the statement that begins

sql = "create table temp.changedarts as " +

The full error message is:

Exception in thread "main" org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Connection was closed in SingleConnectionDataSource. Check that user code checks shouldClose() before closing connections, or set suppressClose to true

This is a Spring error message, but for what it's worth I have checked that suppressClose is true.

lbreddemann
Active Contributor
0 Kudos

Hi Chris,

what exactly do you mean by

> I want to execute multiple statements at a time, but it seems that however ...

?

There is actually no way (in no database I know at least) to run several statements from one session at once in a real parallel way.

From JAVA/JDBC you would open a transaction, run statement after statement and end the transaction afterwards with a COMMIT or a ROLLBACK.

You may want to check the JDBC reference at SUN http://java.sun.com/javase/technologies/database/index.jsp

Anyhow, you can add multiple commands to one BATCH (e.g. stmt.addBatch) command that will be send to the database in one - batch. For that: also check the JDBC reference

The way you described to append one statement to another is just a client specific thing.

Oracles sqlplus takes ';' as default statement delimiters, SQL Studio and DB Studio from MaxDB take newline + //.

These are just markers for the client programs so that they know : "OK, this command ends here - send it to the DB server!"

I hope this helped a bit - regards,

Lars

Former Member
0 Kudos

Hi Lars,

Thanks for your response.

I'm not trying to run statements in parallel.

I want to execute a series of statements, where I create various temporary tables and use them in later statements.

It seems that a temporary table is only visible when using the same connection through which it was created, so I need to execute all my queries using the same connection.

When I try to execute individual statements in separate jdbc calls, using the same connection, the connection gets closed part way through the sequence, for reasons I haven't been able to figure out.

So I thought I could string the statements together, and send them in a single jdbc call, but as I said above, I can't get that to work.

I hadn't realised the semicolon terminator was a client specific thing - I thought it was part of the standard.

I'll look into jdbc BATCH - that may be what I'm looking for.

Thanks,

Chris

lbreddemann
Active Contributor
0 Kudos

Hi Chris,

from your description now, batch commands are not what you are looking for!

Please show your code - then we can tell you, why your connection gets closed.

regards,

Lars

Former Member
0 Kudos

Hi Lars,

Here are the relevant bits of code.

However I'm not sure how helpful that will be - I'm using the SpringFramework for my jdbc calls, as it saves a lot of time and effort, and my calls are to Spring methods, which are wrappers round the base jdbc calls.

I'll post it anyway, just in case you're familiar with Spring. I haven't looked at the Spring code, but my understanding is it pretty much just passes the sql to standard jdbc calls.

I guess my next step would be to trace through the Spring code as it executes and see if anything becomes apparent. However I'm under some time pressure and was hoping to avoid that.

My other alternative is to create non-temporary tables, and drop them explicitly when I've finished with them.


    public Set<String> getPriceUpdatedProducts() {
        final Set<String> prods = new TreeSet<String>();
        String sql;
        int updates;
        // need single connection template so subsequent statements can access temp tables:
        JdbcTemplate jtSingle = getSingleConTemplate(jdbcTemplate);
        // inDCs is a list of dist channels for an sql 'in' statement:
        String inDCs = "";
        for (String siteId: siteConfig.getAllSiteIds()) {
            if (!"".equals(inDCs)) inDCs += ",";
            inDCs += "'" + siteConfig.getSiteIdProperty(siteId, "distChanId") + "'";
        }

        // Clear all the changed flags from the last run:
        updates = jtSingle.update("update zchangedartdc set changed = '' ");

        // Get the current data into a temp table:
        sql = "create table temp.pricechanges as " +
              "select A304.matnr, A304.vtweg, konp.kbetr price " +
              "  from A304 join konp " +
              "    on A304.knumh = konp.knumh " +
              "   and A304.mandt = konp.mandt " +
              "   and A304.vkorg = '" + salesOrg + "' " +
              "   and A304.vtweg in (" + inDCs + ")" +
              "   and A304.kschl = '" + rrpCondType + "' " +
              "   and A304.mandt = '" + sapClient + "' " +
              "   and konp.kschl = '" + listCondType + "' " +
              "   and char(date, internal) >= chr(A304.datab) " +
              "   and char(date, internal) <= chr(A304.datbi) " +
              "   and A304.kappl = 'V' ";
        jtSingle.execute(sql);

        // Get the changes into a second temp table:
        sql = "create table temp.changedarts as " +
              "select temp.pricechanges.* " +
              "  from temp.pricechanges, zchangedartdc " +
              " where zchangedartdc.matnr = temp.pricechanges.matnr " +
              "   and zchangedartdc.vtweg = temp.pricechanges.vtweg " +
              "   and zchangedartdc.price != temp.pricechanges.price ";
        jtSingle.execute(sql);

        // save the changes, and flag them:
        sql = "update zchangedartdc " +
              "   set (price, changed) = (select price, 'X'  " +
              "                             from temp.changedarts " +
              "                            where zchangedartdc.matnr = temp.changedarts.matnr " +
              "                              and zchangedartdc.vtweg = temp.changedarts.vtweg) " +
              " where matnr||vtweg in (select matnr||vtweg from temp.changedarts) ";
        updates = jtSingle.update(sql);

        // add the new items that weren't there last time, and flag them:
        sql = "insert into zchangedartdc " +
              "select '" + sapClient + "', matnr, vtweg, 'X', price " +
              "  from temp.pricechanges " +
              " where matnr||vtweg not in (select matnr||vtweg from zchangedartdc) ";
        updates = jtSingle.update(sql);

        // now we've got all the changes flagged, we can get the list of changed products:
        jtSingle.query("select distinct matnr from zchangedartdc " +
                       " where changed = 'X' ",
                       new RowCallbackHandler() {
                           public void processRow(ResultSet rs) throws SQLException {
                               prods.add(rs.getString("matnr"));
                           }
                       });

        // release the connection:
        destroySingleConnection(jtSingle);
        return prods;
    }

    /**
     * Return a JdbcTemplate which will always use the same connection. Parameter jt is just
     * a convenient way to get a DataSource from which to get a Connection.
     * When the calling prodedure has finished, it MUST call
     * destroySingleConnection(jt)
     * @param jt
     * @return
     */
    private JdbcTemplate getSingleConTemplate(JdbcTemplate jt) {
        Connection con;
        try {
            con = jt.getDataSource().getConnection();
            con.setAutoCommit(true);
        } catch (SQLException e) {
            throw new DataAccessResourceFailureException("Failed to get Connection for SingleConnectionDataSource", e);
        }
        SingleConnectionDataSource singleDs = new SingleConnectionDataSource(con, true);
        return new JdbcTemplate(singleDs);
    }

    private void destroySingleConnection(JdbcTemplate singleConJt) {
        try{
            ((SingleConnectionDataSource)singleConJt.getDataSource()).destroy();
        } catch (SQLException e) {
            throw new DataAccessResourceFailureException("Failed to destroy SingleConnectionDataSource", e);
        }
    }

lbreddemann
Active Contributor
0 Kudos

Hi Chris,

nope - SpringFramework - never heard of it.

So the question is not: "How can I run multiple SQL statements in one transaction via JDBC?" which is no problem - but rather "How can I run multiple SQL statements in one transaction via SpringFramework?".

Does this framework provides this functionality? I don't know!

Anyhow - to me it looks a bit odd that you try to work with temporary tables on the one side and don't know about transaction handling on the other side.

Maybe you don't really need temporary tables at all...

What are the temporary tables are for?

Regards,

Lars