CODEWITHSUNDEEP
sqljdbcinformixsqlexception
quintin
quintin

Reputation: 837

Exception: java.sql.SQLException: Could not position within a table (informix.tblName)

Query: my_table is a view not a table

static final String query= 
    "SELECT "+MailboxId+", " + 
    Action+", " + 
    ActionType+", " + 
    MsgType+", " + 
    Priority+", " + 
    MsgID+", " + 
    MsgCountsSequenceNumber+", " + 
    MsgCounts+", " + 
    TimeOfEvent+"  " + 
    "FROM my_table " + 
    "ORDER BY TimeOfEvent;";

public ResultSet executeQuery(Statement st, String query) throws SQLException
{
    boolean isDeadlock= false;
    ResultSet rs = null;
    int retries= deadlockRetries;
    do {
        try {
            rs = st.execute(query); //here it produces exception 
        } catch (SQLException e) {
            if (e.getErrorCode() == SQLERR_DEADLOCK) {
                diag_log(LOG, "Deadlock exception generated during execute query=", sqlQuery, ".  Retries=", Integer.toString(retries));
                isDeadlock= true;
                synchronized(_lock) {
                    deadlockCount++;
                }
            }
            else {
                diag_log(LOG, "SQL Exception: ", Integer.toString(e.getErrorCode()), " query=", sqlQuery, " exception= ", e.getMessage() != null ? e.getMessage() : "unavailable"); // this log gets printed
                throw e;
            }
        }       

    } while (isDeadlock && retries-- > 0);
    return rs;
}

Logs:

Failed to read database Mydyndb with query SELECT MailboxId, Action, ActionType, MsgType, Priority, MsgID, MsgCountsSequenceNumber, MsgCounts, TimeOfEvent FROM my_table ORDER BY TimeOfEvent;. TECHNICAL DETAILS: Exception: java.sql.SQLException: Could not position within a table (informix.my_table).

Need to know the reason for this exception

Upvotes: 1

Views: 9855

Answers (1)

Ricardo Henriques
Ricardo Henriques

Reputation: 1086

You're missing the ISAM Error, use e.getCause() to get it. This will pinpoint the problem.

One common problem is locking, your log shows the tblName table but the statement showed is:

SELECT 
    MailboxId, 
    Action, 
    ActionType, 
    MsgType, 
    Priority, 
    MsgID, 
    MsgCountsSequenceNumber, 
    MsgCounts, 
    TimeOfEvent, 
    CallerANI, 
    Sender, 
    SenderDisplayName, 
    Uid, 
    ObjectId 
FROM 
    my_table 
ORDER BY TimeOfEvent;

Is my_table a view over tblName or is it something wrong with your log?

If the statement is correct, and it is not a view, you're doing a full scan. There is not much to do except:

  1. Check if you're using lock wait, if not used it, if so try to increase.
  2. Check the isolation level you're using when issuing this statement.

If it is a view, check if you're filtering some field over tblName.

If you're, check the presence of INDEX over it and if the distribution is correct. Check the level of isolation set on the table

If you're not, check the possibility to create one.

If no filtering is involved, we got another full scan.

Upvotes: 3

Related Questions