Error After modifying SQLJET Tutorial

[GoogleCodeExporter]

WHY DOES THIS NOT WORK?

////////////////

/**
 * Example.java
 * Copyright (C) 2009-2010 TMate Software Ltd
 * 
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; version 2 of the License.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * For information on how to redistribute this software under
 * the terms of a license other than GNU General Public License
 * contact TMate Software at support@sqljet.com
 */
package packSqlJet;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Set;

import org.tmatesoft.sqljet.core.SqlJetException;
import org.tmatesoft.sqljet.core.SqlJetTransactionMode;
import org.tmatesoft.sqljet.core.schema.ISqlJetIndexDef;
import org.tmatesoft.sqljet.core.schema.ISqlJetTableDef;
import org.tmatesoft.sqljet.core.table.ISqlJetCursor;
import org.tmatesoft.sqljet.core.table.ISqlJetTable;
import org.tmatesoft.sqljet.core.table.ISqlJetTransaction;
import org.tmatesoft.sqljet.core.table.SqlJetDb;

/**
 * @author TMate Software Ltd.
 * @author Sergey Scherbina (sergey.scherbina@gmail.com)
 *
 */
public class Tutorial {

    private static final String DB_NAME = "db.sqlite";
    private static final String TABLE_NAME = "employees";

    private static final String PROJECT_ID = "project_id";
    private static final String FILE_ID = "file_id";
    private static final String SRC_LANG = "srcLang_s";
    private static final String TAR_LANG = "tarLang_s";
    private static final String STATUS_S = "status_s";

    //private static final String DOB_FIELD = "date_of_birth";
    //private static final String FULL_NAME_INDEX = "full_name_index";
    //private static final String DOB_INDEX = "dob_index";

    public static void main(String[] args) throws SqlJetException {
        File dbFile = new File(DB_NAME);
        dbFile.delete();

        // create database, table and two indices:
        SqlJetDb db = SqlJetDb.open(dbFile, true);
        // set DB option that have to be set before running any transactions: 
        db.getOptions().setAutovacuum(true);
        // set DB option that have to be set in a transaction: 
        db.runTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb db) throws SqlJetException {
                db.getOptions().setUserVersion(1);
                return true;
            }
        }, SqlJetTransactionMode.WRITE);

        db.beginTransaction(SqlJetTransactionMode.WRITE);
        try {            
            String createTableQuery = "CREATE TABLE " + TABLE_NAME + " (" + FILE_ID + " TEXT NOT NULL PRIMARY KEY , " + PROJECT_ID + " TEXT NOT NULL, " + SRC_LANG + " TEXT NOT NULL," + TAR_LANG + "TEXT NOT NULL," + STATUS_S + "TEXT NOT NULL )";
        //  String createTableQuery = "CREATE TABLE " + TABLE_NAME + " (" + SECOND_NAME_FIELD + " TEXT NOT NULL PRIMARY KEY , " + FIRST_NAME_FIELD + " TEXT NOT NULL, " + DOB_FIELD + " INTEGER NOT NULL)";
           // String createFirstNameIndexQuery = "CREATE INDEX " + FULL_NAME_INDEX + " ON " + TABLE_NAME + "(" +  FIRST_NAME_FIELD + "," + SECOND_NAME_FIELD + ")";
           // String createDateIndexQuery = "CREATE INDEX " + DOB_INDEX + " ON " + TABLE_NAME + "(" +  DOB_FIELD + ")";
            System.out.println();
            System.out.println(">DB schema queries:");
            System.out.println();
            System.out.println(createTableQuery);
          //  System.out.println(createFirstNameIndexQuery);
          //  System.out.println(createDateIndexQuery);

            db.createTable(createTableQuery);
          //  db.createIndex(createFirstNameIndexQuery);
          //  db.createIndex(createDateIndexQuery);
        } finally {
            db.commit();
        }
        // close DB and open it again (as part of example code)

        db.close();        
        db = SqlJetDb.open(dbFile, true);

        System.out.println();
        System.out.println(">Database schema objects:");
        System.out.println();
        System.out.println(db.getSchema());
        System.out.println(db.getOptions());        

        // insert rows:
        db.beginTransaction(SqlJetTransactionMode.WRITE);
        try {
         //   Calendar calendar = Calendar.getInstance();
            ISqlJetTable table = db.getTable(TABLE_NAME);
           // calendar.clear();
            //calendar.set(1981, 4, 19);
            table.insert("45451", "1254","en-us","en-gb","Ready" );

        } finally {
            db.commit();
        }

        ISqlJetTable table = db.getTable(TABLE_NAME);

        // getting all rows in table, sorted by PK.        
        System.out.println();
        System.out.println(">All employees in order defined by PK (" + table.getPrimaryKeyIndexName() + "):");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.order(table.getPrimaryKeyIndexName()));
        } finally {
            db.commit();
        }

   /*     // getting all rows in table, sorted by PK.        
        System.out.println();
    //    System.out.println(">All employees in order defined by " + DOB_INDEX + ", reversed:");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.order(table.getPrimaryKeyIndexName()).reverse());
        } finally {
            db.commit();
        }

        // getting all rows in table, sorted by index.        
        System.out.println();
        System.out.println(">All employees in order defined by " + FULL_NAME_INDEX + " :");
        System.out.println();

        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.order(FULL_NAME_INDEX));
        } finally {
            db.commit();
        }

        // getting rows in table with exact indexed field value.
        System.out.println();
        System.out.println(">Alexanders:");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.lookup(FULL_NAME_INDEX, "Alexander"));
        } finally {
            db.commit();
        }

        // getting rows in table with indexed field value in certain scope.        
        System.out.println();
        System.out.println(">Employees with full name in scope [B, I]:");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.scope(FULL_NAME_INDEX, new Object[] {"B"}, new Object[] {"I"}));
        } finally {
            db.commit();
        }

        Calendar calendar = Calendar.getInstance();
        calendar.setTime(new Date(System.currentTimeMillis()));
        calendar.add(Calendar.YEAR, -30);

        System.out.println();
        System.out.println(">Deleting rows of employees older than 30 years old.");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.WRITE);
        try {
            ISqlJetCursor deleteCursor = table.scope(DOB_INDEX, 
                     new Object[] {Long.MIN_VALUE}, 
                     new Object[] {calendar.getTimeInMillis()});
            while (!deleteCursor.eof()) {
                System.out.println("Deleting: " + 
                        deleteCursor.getRowId() + " : " + 
                        deleteCursor.getString(FIRST_NAME_FIELD) + " " + 
                        deleteCursor.getString(SECOND_NAME_FIELD) + " was born on " + 
                        formatDate(deleteCursor.getInteger(DOB_FIELD)));
                deleteCursor.delete();
            }
            deleteCursor.close();
        } finally {
            db.commit();
        }

        System.out.println();
        System.out.println(">After deletion in row id order:");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.open());
        } finally {
            db.commit();
        }

        db.beginTransaction(SqlJetTransactionMode.WRITE);
        ISqlJetCursor updateCursor = null;
        try {
            table.insert("Smith", "John", 0);
            calendar.setTime(new Date(System.currentTimeMillis()));
            updateCursor = table.open();
            do {
                updateCursor.update(updateCursor.getValue(SECOND_NAME_FIELD), updateCursor.getValue(FIRST_NAME_FIELD), calendar.getTimeInMillis());
            } while(updateCursor.next());
        } finally {
            updateCursor.close();
            db.commit();
        }

        System.out.println();
        System.out.println(">After insertion of a new record and updating dates (by PK):");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.order(table.getPrimaryKeyIndexName()));
            System.out.println();
            System.out.println(">Same in order defined by " + FULL_NAME_INDEX + " :");
            System.out.println();
            printRecords(table.order(FULL_NAME_INDEX));
        } finally {
            db.commit();
        }

        System.out.println();
        System.out.println(">Dropping tables and indices:");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.WRITE);
        try {
            Set<String> tables = db.getSchema().getTableNames();
            for (String tableName : tables) {
                ISqlJetTableDef tableDef = db.getSchema().getTable(tableName);
                Set<ISqlJetIndexDef> tableIndices = db.getSchema().getIndexes(tableDef.getName());

                for (ISqlJetIndexDef indexDef : tableIndices) {

                    if (!indexDef.isImplicit()) {
                        System.out.println("dropping index: " + indexDef.getName());
                        db.dropIndex(indexDef.getName());
                    }
                }
                System.out.println("dropping table: " + tableDef.getName());
                db.dropTable(tableDef.getName());
            }
        } finally {
            db.commit();
        }

        db.close();

         */
    }

    private static void printRecords(ISqlJetCursor cursor) throws SqlJetException {
        try {
            if (!cursor.eof()) {
                do {
                    System.out.println(cursor.getRowId() + " : " + 
                            cursor.getString(PROJECT_ID) + " " + 
                            cursor.getString(FILE_ID) + ""  +
                            cursor.getString(SRC_LANG) + "" +
                            cursor.getString(TAR_LANG) + "" +
                            cursor.getString(STATUS_S) + "" 

                            );
                } while(cursor.next());
            }
        } finally {
            cursor.close();
        }
    }

}

Original issue reported on code.google.com by vishn...@gmail.com on 20 Oct 2010 at 12:57

[GoogleCodeExporter]

Original comment by sergey.s...@gmail.com on 20 Oct 2010 at 1:15

• Changed state: Accepted

[GoogleCodeExporter]

Issue 141 has been merged into this issue.

Original comment by sergey.s...@gmail.com on 20 Oct 2010 at 1:37

[GoogleCodeExporter]

It's just because you have omitted one space char between TAR_LANG + "TEXT NOT 
NULL," and it produces "tarLang_sTEXT NOT NULL" field definition. 

It means that field is named "tarLang_sTEXT" instead "tarLang_s" as you expect. 

To fix it just add space char before "TEXT NOT NULL,", the same as you did it 
for SRC_LANG field:

        String createTableQuery = "CREATE TABLE " + TABLE_NAME + " (" + FILE_ID + " TEXT NOT NULL PRIMARY KEY , " + PROJECT_ID + " TEXT NOT NULL, " + SRC_LANG + " TEXT NOT NULL," + TAR_LANG + " TEXT NOT NULL," + STATUS_S + " TEXT NOT NULL )";

It fixes your example completely.

Original comment by sergey.s...@gmail.com on 20 Oct 2010 at 1:54

[GoogleCodeExporter]

Original comment by sergey.s...@gmail.com on 21 Oct 2010 at 2:17

• Changed state: Invalid