Stateless EJB - order in database insert

Discussions

EJB programming & troubleshooting: Stateless EJB - order in database insert

  1. Stateless EJB - order in database insert (2 messages)

    I have a stateless EJB which performs "long transaction". It read file and does some business logic per each line (of the file). Line is stored in the database afterwards. My problem is following: Since I used connection pool to store data, sometimes those lines are stored "out of order" in the database.

    Process example
    read 1st line from file;
    get connection from the pool;
    store this line in database

    read 2nd line from file;
    get connection from the pool;
    store this line in database

    read 3rd line from file;
    get connection from the pool;
    store this line in database

    when I do select on that table I get following result:
    1st line
    3rd line
    2nd line

    This happens now and then but it has no "pattern". I assume it is due to the way my appserver (Weblogic) handles auto-commit (delay might occur). I can not use user transactions because it sometimes takes couple of hour to process whole file.

    Any suggestions how to overcome this issue?
  2. Stateless EJB - order in database insert[ Go to top ]

    Try using one connection for the entire operation:

    get connection from the pool;
    read 1st line from file;
    store this line in database
    read 2nd line from file;
    store this line in database
    read 3rd line from file;
    store this line in database

    Or better yet, put an extra counter field in the table that always increases for each insert. Use the counter field to sort your query:

    select * from TABLE orderby COUNTER
  3. Hi,

    Generally if the inserted order is
    1
    2
    3

    then on retreiving the data from the same table without any order by clause would return the value as

    1
    3
    2

    in case u would like to get the data in the order u had inserted u can use the order by clause in the select statement