statementBuilder with distinct().columns(...) return empty data ?

Showing 1-9 of 9 messages
statementBuilder with distinct().columns(...) return empty data ? hadi 9/23/10 2:02 AM
any idea about that ?

correct me if i'm wrong, but what i expect from table data and a call
like this :

========================================================
     aColumn                  anotherColumn
========================================================
         1                                   anyData
         1                                   aData
         2                                   anotherData


PreparedStmt <SimpleData> preparedStmt =
statementBuilder.columns("aColumn").distinct().prepareStatement();
dao.query(preparedStmt);

it will return 2 SimpleData object with all data set or at least it
will have SimpleData with prop "aColumn" is set.
But what i got are 2 SimpleData with empty data.

Source code :
what i did was changing example's code from HelloAndroid example
project. (Only  HelloAndroid file change)

-------------------------------------------------
HelloAndroid-------------------------------------------

public class HelloAndroid extends OrmLiteBaseActivity<DatabaseHelper>
{

        private final String LOG_TAG = getClass().getSimpleName();

        static {
                OpenHelperManager.setOpenHelperFactory(new SqliteOpenHelperFactory()
{
                        public OrmLiteSqliteOpenHelper getHelper(Context context) {
                                return new DatabaseHelper(context);
                        }
                });
        }

        /**
         * Called when the activity is first created.
         */
        @Override
        public void onCreate(Bundle savedInstanceState) {
                super.onCreate(savedInstanceState);
                Log.i(LOG_TAG, "creating " + getClass());
                TextView tv = new TextView(this);
                doSampleDatabaseStuff("onCreate", tv);
                setContentView(tv);

        }

        /**
         * Do our sample database stuff.
         */
        private void doSampleDatabaseStuff(String action, TextView tv) {
                try {
                        // get our dao
                        Dao<SimpleData, Object> simpleDao = getHelper().getSimpleDataDao();
                        Log.i(HelloAndroid.class.getName(), "before query");

//
=================================changes===============================
                        // query for all of the data objects in the database
                        PreparedStmt<SimpleData> preparedStmt =
simpleDao.statementBuilder().columns("string").distinct().prepareStatement();
                        List<SimpleData> list = simpleDao.query(preparedStmt);
                        Log.i("ForTest", "Size Result : " + list.size());
                        for (SimpleData sd : list) {
                                Log.i("ForTest", "string is null " + (sd.string == null));
                        }
//=================================end of
changes===============================
                        tv.setText(list.size());
                } catch (SQLException e) {
                        Log.e(LOG_TAG, "Database exception", e);
                        return;
                }
        }

-------------------------------------------------
SimpleData-------------------------------------------
public class SimpleData {

        @DatabaseField(generatedId = true)
        int id;
        @DatabaseField
        String string;
        @DatabaseField
        long millis;
        @DatabaseField
        Date date;
        @DatabaseField
        boolean even;

        SimpleData() {
                // needed by ormlite
        }

        public SimpleData(long millis) {
                this.date = new Date(millis);
                this.string = "millis = " + millis;
                this.millis = millis;
                this.even = ((millis % 2) == 0);
        }

        @Override
        public String toString() {
                StringBuilder sb = new StringBuilder();
                sb.append("id=").append(id).append(", ");
                sb.append("string=").append(string).append(", ");
                sb.append("millis=").append(millis).append(", ");
                sb.append("date=").append(date).append(", ");
                sb.append("even=").append(even).append(", ");
                return sb.toString();
        }
}



-------------------------------------------------
DatabaseHelper-------------------------------------------
public class DatabaseHelper extends OrmLiteSqliteOpenHelper {

        private static final String DATABASE_NAME = "test.db";
        private static final int DATABASE_VERSION = 2;

        private DatabaseType databaseType = new SqliteAndroidDatabaseType();
        private Dao<SimpleData, Object> simpleDao = null;

        public DatabaseHelper(Context context) {
                super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db, ConnectionSource
connectionSource) {
                try {
                        Log.i(DatabaseHelper.class.getName(), "onCreate");
                        TableUtils.createTable(databaseType, connectionSource,
SimpleData.class);
                } catch (SQLException e) {
                        Log.e(DatabaseHelper.class.getName(), "Can't create database", e);
                        throw new RuntimeException(e);
                }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, ConnectionSource
connectionSource, int oldVersion, int newVersion) {
                try {
                        Log.i(DatabaseHelper.class.getName(), "onUpgrade");
                        TableUtils.dropTable(databaseType, connectionSource,
SimpleData.class, true);
                        // after we drop the old databases, we create the new ones
                        onCreate(db);
                } catch (SQLException e) {
                        Log.e(DatabaseHelper.class.getName(), "Can't drop databases", e);
                        throw new RuntimeException(e);
                }
        }

        public Dao<SimpleData, Object> getSimpleDataDao() throws SQLException
{
                if (simpleDao == null) {
                        simpleDao = BaseDaoImpl.createDao(databaseType,
getConnectionSource(), SimpleData.class);
                }
                return simpleDao;
        }
}

Re: statementBuilder with distinct().columns(...) return empty data ? Gray 9/23/10 11:01 AM
On Sep 23, 5:02 am, hadi <idahirt...@gmail.com> wrote:
>
> correct me if i'm wrong, but what i expect from table data and a call like this :
>
> ========================================================
>      aColumn                  anotherColumn
> ========================================================
>          1                                   anyData
>          1                                   aData
>          2                                   anotherData
>
> PreparedStmt <SimpleData> preparedStmt = statementBuilder.columns("aColumn").distinct().prepareStatement();
> dao.query(preparedStmt);

What you should be getting from this query is:

         1                            null
         2                            null

You should get distinct results and since you only requested aColumn,
that's the only one you should get -- anotherColum should be all
nulls.

In running your tests and some of my own, I found a fixed a bug with
how ORMLite works with Android's cursor.  Turns out that when we do a
'select * from table' then the field-name is stored in the Android
SQLiteCursor as "aColumn".  However, when we request a particular
field ("select `aColumn` from table") then the field is stored as
"`aColumn`" -- with the escape characters.  This is a bug with their
cursor code IMO.

This has also exposed that my test coverage under Android is not
good.  I've expanded it significantly so hopefully we will be better
about seeing these issues in the future.

This will be fixed in 3.4 which I'll try to get out in the next day or
so.  Sorry for the problems.
gray
Re: statementBuilder with distinct().columns(...) return empty data ? Gray 9/23/10 11:09 AM
Here's the bug report:  https://sourceforge.net/tracker/?func=detail&aid=3074249&group_id=297653&atid=1255989

And here's the http://ormlite.svn.sourceforge.net/viewvc/ormlite?view=revision&revision=900

In case anyone is playing along at home.
gray
Re: statementBuilder with distinct().columns(...) return empty data ? Gray 9/23/10 6:37 PM
Interestingly enough it is the specific combination of DISTINCT and escaped fields.  Here's the bug I submitted to Google:

http://code.google.com/p/android/issues/detail?id=11435

gray

Re: statementBuilder with distinct().columns(...) return empty data ? Kevin Galligan 9/23/10 6:42 PM
Weird.
Re: statementBuilder with distinct().columns(...) return empty data ? hadi 9/24/10 4:52 AM
sure.
in the mean time, i'm using raw sql select and ResultRaw.
Fortunately you provide that. :)
Re: statementBuilder with distinct().columns(...) return empty data ? Gray 9/24/10 11:11 AM
On Sep 24, 2010, at 7:52 AM, hadi wrote:

>>>
> in the mean time, i'm using raw sql select and ResultRaw.
> Fortunately you provide that. :)

3.4 has been released.  Let me know if that fixes your issue.  You can also do just remove the columns() call.  It's a bizarre combination of the columns() AND distinct().  You win the obscure bug award for sure!

gray

Re: statementBuilder with distinct().columns(...) return empty data ? hadi 10/4/10 2:28 AM


> 3.4 has been released.  Let me know if that fixes your issue.  

sorry for late response. yes, the updates fixed my case.
anyway, wow...
already version 4.0 ?  :)
so many changes, deprecated fuctions.
Re: statementBuilder with distinct().columns(...) return empty data ? Gray 10/4/10 7:02 AM

Yeah.  Sorry for that.  Much of the interface has stabilized but as I check off the last big TODO items, I still once and a while feel the need to reorg a bit.

Moving the DatabaseType to be internal was a big change.  It should be better in the long-run although the port may be a bit painful in the short-term.

gray