Can't find created index

Question

Using SQLJet as an SQLite library for a Java project, I create an index for my table and use that to get something from the Database and I keep getting the error, and I have no idea what I'm doing wrong. Paraphrased my code a bit (I create more than one table but they do not have any significance.) How do I fix this error?

org.tmatesoft.sqljet.core.SqlJetException: Index not exists: FULL_NAME_INDEX: error code is MISUSE
    at org.tmatesoft.sqljet.core.internal.table.SqlJetTable.checkIndexName(SqlJetTable.java:285)
    at org.tmatesoft.sqljet.core.internal.table.SqlJetTable.access$300(SqlJetTable.java:46)
    at org.tmatesoft.sqljet.core.internal.table.SqlJetTable$2.runWithLock(SqlJetTable.java:149)
    at org.tmatesoft.sqljet.core.table.SqlJetDb$1.runSynchronized(SqlJetDb.java:172)
    at org.tmatesoft.sqljet.core.table.engine.SqlJetEngine.runSynchronized(SqlJetEngine.java:217)
    at org.tmatesoft.sqljet.core.table.SqlJetDb.runWithLock(SqlJetDb.java:170)
    at org.tmatesoft.sqljet.core.internal.table.SqlJetTable.lookup(SqlJetTable.java:146)
    at eureqapp.DatabaseManager.getContact(DatabaseManager.java:408)

My code:

public DatabaseManager() throws SqlJetException, IOException
{
    try
    {
        //Creates a database file, if it doesn't exist already.
        File dbFile = new File(databaseFileName);
        if (!dbFile.exists())
        {
            dbFile.createNewFile();

            db = SqlJetDb.open(dbFile, true);
            db.beginTransaction(SqlJetTransactionMode.WRITE);
            db.getOptions().setUserVersion(1);
            createTables();

        } else
        {
            db = SqlJetDb.open(dbFile, true);
            db.beginTransaction(SqlJetTransactionMode.WRITE);
            db.getOptions().setUserVersion(1);
        }
    } catch (Exception e)
    {
        e.printStackTrace();
    } finally
    {

        if (db.isOpen())
        {
            db.commit();
            db.close();
        }
    }
}

public static void createTables() throws SqlJetException
{
    try
    {
        if (!db.isOpen())
        {
            db.open();
        }

        db.beginTransaction(SqlJetTransactionMode.WRITE);
        db.createTable(CREATE_TABLE_CONTACT);
        db.createIndex(CREATE_CONTACT_NAME_INDEX);



    } catch (Exception e)
    {
        e.printStackTrace();
    } finally
    {

        if (db.isOpen())
        {
            db.commit();
            db.close();
        }
    }
}

And where I call my getContact method

public static Contact getContact(String name) throws SqlJetException
{
    Contact c = new Contact();

    try
    {
        if (!db.isOpen())
        {
            db.open();
        }
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);

        ISqlJetTable table = db.getTable("CONTACT");
        ISqlJetCursor cursor = table.lookup("FULL_NAME_INDEX", name);

        String contactName = cursor.getString("name");
        String contactAddress = cursor.getString("address");
        String contactPlace = cursor.getString("place");
        String contactDepartment = cursor.getString("department");
        String contactTelephone = cursor.getString("telephone_number");
        String contactMobile = cursor.getString("mobile_number");
        String contactEmail = cursor.getString("email_address");
        cursor.close();
        db.close();

        c = new Contact(contactName, contactAddress, contactPlace, contactDepartment, contactTelephone, contactMobile, contactEmail);

        return c;
    } catch (Exception e)
    {
        e.printStackTrace();
    }
    return null;
}

score 0 · Answer 1 · answered Nov 26 '12 at 09:55

0

Do you use the same user you created your index or do you use another user from your application?
If you use another user it looks like the user has no grants to access and use the index. Then allow the application user to use the index.

answered Nov 26 '12 at 09:55

duffy356

3,678
3
32
47

This is all done within the same running application and as far as I know the database is in use by a single user. Every other write/read method that doesn't use indexes work. – user1810737 Nov 26 '12 at 11:22

Can't find created index

1 Answers1