FreeTDS Development Group

["Michael Horowitz" <michael.horowitz AT storagenetworks.com>]

Hello,

Prepared statements are the most efficient way to go if the inserts are
the same, but it should work with regular statements too.

In this code:
>    stInsertSQL.execute("INSERT INTO table1 values(1,2)");
>    st.close();
I am assuming that this is a typo, and you really meant
"stInsertSQL.close()".

I am not sure why you are using the "execute" method.  You should probably
use "executeUpdate".  "executeUpdate" should be used for inserts, updates,
DDL stuff, and "executeQuery" should be used for queries.  The "execute"
method should be used only when it is possible that a statement may return
more than one ResultSet object, more that one update count, or a
combination of ResultSet objects and update counts.
If you do use the "execute" method, then there is a bunch of stuff you
have to go through to loop through and make sure you get all the
ResultSets and/or update counts.  If you call "close()" on the statement
before doing that, it may leave it in a weird state or something that is
causing it to rollback the transaction before you commit it, but I am not
really sure.
If you use "executeUpdate", it should work, you may want to try that.

Also, you don't have to close and create a new Statement object each time,
you can re-use the same Statement object with each "executeUpdate" with
all your inserts.

I hope this helps.

--Mike


> What you need to do is use a prepared statement (assuming you're inserts are
> the same).
> 
> You prepare you're statement once and simply continue to do inserts without
> closing the statement.  Once you are done, commit the changes and close the
> statement.
> 
> -----Original Message-----
> From: nnklak AT yahoo.com [mailto:nnklak AT yahoo.com]
> Sent: Friday, January 04, 2002 2:51 PM
> To: TDS Development Group
> Subject: [freetds] RE: Transaction problems...
> 
> 
> Hi, I am facing a similar problem, the question is:
> 
> Why the following code does NOT work (no inserts in the DB)
>    Connection.setAutoCommit(false);
>    Statement stInsertSQL = Connection.createStatement(); 
>    stInsertSQL.execute("INSERT INTO table1 values(1,2)");
>    st.close();
>    Connection.commit();
>    Connection.setAutoCommit(true);
>    Connection.close();
> 
> While the following code seems to work:
>    Connection.setAutoCommit(false);
>    Statement stInsertSQL = Connection.createStatement();
>    stInsertSQL.execute("INSERT INTO table1 values(1,2)");
>    //st.close();
>    Connection.commit();
>    st.close();
>    Connection.setAutoCommit(true);
>    Connection.close();
> 
> I have to insert 500 rows in the DB as a transaction; Do I have to keep
> 500 Statement variables until the Connection.commit()statement ?
> 
> Thanks
> - Nikolas
> 
> ---
> You are currently subscribed to freetds as: [jfisher AT emery-waterhouse.com]
> To unsubscribe, forward this message to
> $subst('Email.Unsub')