RE: [cinjug-users] odd db2 insert behavior

["Ching-yao Hsu" <chingyao_hsu@xxxxxxxx>]

The addBatch is a good idea. It will save you some network traffic.

However, you may want to check if your version of JDBC supports it.

While the newer Universal driver from IBM supports it, the older ones do not.

 

Ching-yao

 

---

From: Robert Casto [mailto:robert@xxxxxxxxxxxxx]
Sent: Monday, May 03, 2004 10:10 AM
To: 'Jason Kretzer/STAR BASE Consulting Inc.'; users@xxxxxxxxxx
Subject: RE: [cinjug-users] odd db2 insert behavior

 

Jason,

 

A more performance friendly way of doing this would be to use the PreparedStatement interface in the java.sql package. In the Java Docs there is an example of how to use the class. You just change the data in the statement using set methods. Once the data is set, you call executeUpdate() and the data is in the database.

 

Another thing you could do is look into using the addBatch(String sql) method so that you send all the commands to the database at once. There are a number of ways to do things so a bit of research will pay off.

 

To answer your second question, most databases don’t store data in them in the order you put it in. You have to tell the database what order you want the data back. This is done using an ORDER BY clause in the SQL query.

 

SELECT SOME_PRI_KEY, SOME_OTHER_VALUE from SOME_TABLE ORDER BY SOME_PRI_KEY

 

There are lots of things you can do in SQL that will save you lots of time programming. I recommend getting a good SQL book to learn the possibilities.

Robert Casto
President - CinciJava LLC
Phone: 513-755-2221
FAX: 831-307-7638
robert@xxxxxxxxxxxxx

 

 

---

From: Jason Kretzer/STAR BASE Consulting Inc. [mailto:JKretzer@xxxxxxxxxxxxxxx]
Sent: Monday, May 03, 2004 9:42 AM
To: users@xxxxxxxxxx
Subject: [cinjug-users] odd db2 insert behavior

 

Good morning all,


I have a class that populates a db2 database.  Here is a basic sample of what I am doing.

for(int i=0; i<400; i++)
{
        stmt.executeQuery("INSERT INTO SOME_TABLE (SOME_PRI_KEY, SOME_OTHER_VALUE) VALUES ("+i+",'bob')");
        connection.commit();
}

First of all, how bad is the style of this?  Secondly, when I insert like this and then get the contents of the table, I get something similar to this


SOME_PRI_KEY        SOME OTHER VALUE
385                        bob
386                        bob
387                        bob
388                        bob
389                        bob
390                        bob
391                        bob
392                        bob
393                        bob
394                        bob
395                        bob
396                        bob
397                        bob
398                        bob
399                        bob
1                        bob
2                        bob
3                        bob
4                        bob
5                        bob


Anyone know why this is happening?  Could it just be a timing issue?

Thanks,

-Jason

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.675 / Virus Database: 437 - Release Date: 5/2/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.675 / Virus Database: 437 - Release Date: 5/2/2004