Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Android Android Data Persistence Migrating a SQLite Database Using a New Field

Raghda Talaa't
Raghda Talaa't
2,944 Points
Posted by Raghda Talaa't
Raghda Talaa't
2,944 Points

No such column: CREATE_DATE!

It gives me this exception "no such column: CREATE_DATE".

Everything supposed to be the same as the video, I also tried to uninstall the app, undo the changes, repeated the steps again .. but still giving me that exception after adding the new column.

In the MemeSQLiteHelper File:

*I updated DB_VERSION to be 2 *Added the column to the CREATE_MEMES String *Added the ALTER statement to the onUpgrade()

In the MemeDataSource File:

*Added the CREATE_DATE to memeValues in the Create method *Added the line to order by the date in the readMemes method (Which gives me this error)

11-05 23:11:18.927    2271-2271/com.teamtreehouse.mememaker E/AndroidRuntime﹕ FATAL EXCEPTION: main
    android.database.sqlite.SQLiteException: no such column: CREATE_DATE (code 1): , while compiling: SELECT NAME, _id, ASSET FROM MEMES ORDER BY CREATE_DATE DESC
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
            at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
            at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
            at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
            at com.teamtreehouse.mememaker.database.MemeDataSource.readMemes(MemeDataSource.java:46)
            at com.teamtreehouse.mememaker.database.MemeDataSource.read(MemeDataSource.java:38)
            at com.teamtreehouse.mememaker.ui.fragments.MemeItemFragment.refreshMemes(MemeItemFragment.java:100)
            at com.teamtreehouse.mememaker.ui.fragments.MemeItemFragment.onResume(MemeItemFragment.java:95)
            at android.app.Fragment.performResume(Fragment.java:1738)
            at android.app.FragmentManagerImpl.moveToState(FragmentManager.java:919)
            at android.app.FragmentManagerImpl.performPendingDeferredStart(FragmentManager.java:780)
            at android.app.FragmentManagerImpl.startPendingDeferredFragments(FragmentManager.java:1081)
            at android.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1452)
            at android.app.FragmentManagerImpl.executePendingTransactions(FragmentManager.java:474)
            at android.support.v13.app.FragmentPagerAdapter.finishUpdate(FragmentPagerAdapter.java:145)
            at android.support.v4.view.ViewPager.populate(ViewPager.java:1068)
            at android.support.v4.view.ViewPager.populate(ViewPager.java:914)
            at android.support.v4.view.ViewPager.onMeasure(ViewPager.java:1436)
            at android.view.View.measure(View.java:15848)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5012)
            at android.widget.FrameLayout.onMeasure(FrameLayout.java:310)
            at android.view.View.measure(View.java:15848)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5012)
            at com.android.internal.widget.ActionBarOverlayLayout.onMeasure(ActionBarOverlayLayout.java:302)
            at android.view.View.measure(View.java:15848)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5012)
            at android.widget.FrameLayout.onMeasure(FrameLayout.java:310)
            at com.android.internal.policy.impl.PhoneWindow$DecorView.onMeasure(PhoneWindow.java:2189)
            at android.view.View.measure(View.java:15848)
            at android.view.ViewRootImpl.performMeasure(ViewRootImpl.java:1905)
            at android.view.ViewRootImpl.measureHierarchy(ViewRootImpl.java:1104)
            at android.view.ViewRootImpl.performTraversals(ViewRootImpl.java:1284)
            at android.view.ViewRootImpl.doTraversal(ViewRootImpl.java:1004)
            at android.view.ViewRootImpl$TraversalRunnable.run(ViewRootImpl.java:5481)
            at android.view.Choreographer$CallbackRecord.run(Choreographer.java:749)
            at android.view.Choreographer.doCallbacks(Choreographer.java:562)
            at android.view.Choreographer.doFrame(Choreographer.java:532)
            at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:735)
            at android.os.Handler.handleCallback(Handler.java:730)
            at android.os.Handler.dispatchMessage(Handler.java:92)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:5103)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:525)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
            at com.android.internal.os.ZygoteInit.main(ZygoteIni
Raghda Talaa't
Raghda Talaa't
2,944 Points
Raghda Talaa't
Raghda Talaa't
2,944 Points

And logically how is this supposed to work if the previously created memes doesn't have the

Create_Date data, how could it be ordered by it?

Evan Anger sorry for that, but could you help me understand this point?

4 Answers

Harry James
Harry James
14,780 Points
Harry James
Harry James
14,780 Points

Hey Raghda!

You are correct here in that this should not be possible (And isn't!). We're now looking for a CREATE_DATE but don't have one!

I fixed up the code for you so that you can migrate from a V1 database over to a V2 database. Take a look at it and my comments. I'd recommend you view this project on my GitHub page for better readability (This post is very long!).

Please note that if you copy my code exactly, you will probably get errors relating to the set/get time methods. Please follow this commit if you want to include these methods yourself or just swap them with the relevant new Date().getTime() method. I explained this on my GitHub commit and below so you can see which bits are optional :)

CODE TO UPDATE:

Italic code is optional - add it if you are refactoring the setTime() and getTime() methods

MemeDatasource.java

  • In the readMemes() method's String[], include MemeSQLiteHelper.COLUMN_MEME_CREATE_DATE in the query. ( See here ).
  • In the ReadMemes() method's new Meme(), include getIntFromColumnName(cursor, MemeSQLiteHelper.COLUMN_MEME_CREATE_DATE) as the final parameter. ( See here ). Do not worry about errors for now, you will be getting this error because we haven't yet added a 4th parameter in the Meme constructor. We'll fix this later.
  • Explanation: These changes will make sure that Meme objects will contain their creation date. The memes will also be ordered so that they appear in the ListView in descending order from the creation date. We do the reading and setting of the memes for the ListView in MemeItemFragment's refreshMemes() method.

-

  • Refactor the code in create() so that where you put the value of COLUMN_MEME_CREATE_DATE, use meme.getTime(). Don't forget to run meme.setTime() before you get the time!
  • This can be refactored into a single method, if preferred ^

Meme.java

  • In the Meme constructor, add a parameter for int createDate.
  • Explanation: This will be used so that when we create our Meme objects in read(), we include the creation date.

-

  • Create a member variable: private int mCreateDate.
  • In the Meme constructor, set mCreateDate = createDate
  • Create the setTime() method. It should be public void and sets mCreateDate = (int) new Date().getTime(). Remember to cast because Date().getTime() returns a long value!
  • Create the getTime() method. It should bhe public void and will return mCreateDate.

CreateMemeActivity.java

  • In the onCreate() method's if(this.getIntent().hasExtra(EXTRA_IMAGE_FILE_PATH)) block, you will have an error because you're missing a parameter in the constructor. Give it the value of 0 by default.
  • NOTE: One thing to note here is that this code isn't setting the CREATE_DATE for previous memes migrated from V1 of the database - this code is setting the CREATE_DATE for any new memes created. 0 is set as a temporary parameter and it will be updated in MemeDatasource's create() method. We never actually set a value for memes migrated from the V1 database as 0 is the default value for integers in SQLite - much like how NULL is the default value for strings in SQLite. This is actually useful for us as we want our value to be 0 (Because the migrated memes are going to be the oldest ones - as they've never had a CREATE_DATE set) so, 0 is the lowest number we can set them to be. You could always set the value as 0 manually if you prefer, however.

That's it! You'll now have a working migration! Go ahead and test it by downloading the V1 database, running it on your emulator and creating a meme. Then, try running your new code and creating 2 more memes! Hopefully, your migrated meme will be on the very bottom of the ListView, your first meme created on the V2 database in the middle and the most recent meme you created on the top!

Adhyan Srivastava
Adhyan Srivastava
5,681 Points
Adhyan Srivastava
Adhyan Srivastava
5,681 Points

I was also getting the same error I figured out that I was missing a ',' in the CREATE_MEMES string.

Before

public static String CREATE_MEMES =
"CREATE TABLE " + MEMES_TABLE + " ("
+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ COLUMN_NAME_ASSET + " TEXT,"
+ COLUMN_MEME_NAME + " TEXT" + //There should be a ',' after the TEXT in this line.
COLUMN_MEME_CREATE_DATE + " INTEGER "  +")";

After

public static String CREATE_MEMES =
"CREATE TABLE " + MEMES_TABLE + " ("
+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ COLUMN_NAME_ASSET + " TEXT,"
+ COLUMN_MEME_NAME + " TEXT," +//Refactored code ;-)
COLUMN_MEME_CREATE_DATE + " INTEGER "  +")";

After fixing this it worked like a charm.

Vivien Kuo
Vivien Kuo
4,541 Points
Vivien Kuo
Vivien Kuo
4,541 Points

Go to your database and see what the new altered table's column's names are. I accidentally name mine COLUMN CREATED_DATE, which was my error. You probably did something similar where your migration had a typo of some sort

Oziel Perez
Oziel Perez
61,321 Points
Oziel Perez
Oziel Perez
61,321 Points

Also check to make sure your "ALTER TABLE" statement is done correctly. In my case, I forgot to put a space before INTEGER for the new column;