locked
sqlite works fine in emulator, no such table error on device

    Question

  • As the title suggest, I go to run a query and get the above error only on the device.

    First, I copy the database:

    public async void CopyDatabase()
            {
                string dbname = "KanjiDictionary1 - Copy.sqlite";
                bool isDatabaseExisting = false;
    
                try
                {
                    StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync(dbname);
                    isDatabaseExisting = true;
                }
                catch
                {
                    isDatabaseExisting = false;
                }
    
                if (!isDatabaseExisting)
                {
                    var databaseFile = await StorageFile.GetFileFromApplicationUriAsync(new Uri("ms-appx:///Database/" + dbname));
                    databaseFile.CopyAsync(ApplicationData.Current.LocalFolder);
                }
            }

    Eventually, I run the query:

    public KanjiDictionaryRepository()
            {
                conn = new SQLiteConnection(connString);
            }
    
            public async Task<IEnumerable<SearchResult>> SearchAsync(string searchTerm)
            {
                
                List<SearchResult> items = null;
                await Task.Run(() =>
                {
                    
                    var cmd = new SQLiteCommand(conn);
                    cmd.CommandText = query;
                    cmd.Bind("@searchParam", searchTerm + "%");
                    
                    items = cmd.ExecuteQuery<SearchResult>();
                    conn.Close();
                });
                return items;
            }

    But I get the exception on cmd.ExecuteQuery. Any ideas? Thanks for the help.

    **EDIT** here's the query:

    private const string query = @"select " +
                                   "	Text as Line3, " +
                                   "	group_concat(Japanese) as Line2, " +
                                   "	Character as Line1 " +
                                   "from " +
                                   "( " +
                                   "	select  " +
                                   "		r.ID, " +
                                   "		r.Text, " +
                                   "		ch.Text as Character, " +
                                   "		reading.Text as Japanese " +
                                   "	from " +
                                   "	( " +
                                   "		( " +
                                   "		select " +
                                   "			r.ID, " +
                                   "			r.Text, " +
                                   "			rl.Language, " +
                                   "			r.ChID, " +
                                   "			rl.ID " +
                                   "		from Reading r " +
                                   "		join ReadingLanguage rl on r.RLID = rl.ID " +
                                   "		where r.RLID = 1 " +
                                   "		and r.Text like @searchParam " +
                                   "		)r " +
                                   "	join Reading reading on reading.RLID in (8,9)  " +
                                   "	join ReadingLanguage rl on reading.RLID = rl.ID  " +
                                   "	join Character ch on r.ChID = ch.ID  " +
                                   "	and reading.ChID = r.ChID " +
                                   "	) " +
                                   "	order by rl.ID desc " +
                                   ")  " +
                                   "group by Character;";


    Michael DiLeo


    • Edited by mcd023 Thursday, October 02, 2014 2:13 AM more info
    Thursday, October 02, 2014 2:12 AM

Answers

  • Hi Michael,

    As I know, there are two scenarios for this problem.

    1. Connection is correct, but there are no such table in sqlite.
    2. Connection is incorrect, sqlite makes a new database file. When open the database, it cannot find such table.

    I assume you are in scenario 2. So before you Search in Sqlite, check the sqlite database file exists in device. Using StorageFolder.TryGetItemAsync method to check the file. Modify your code as following and try to find something more in your device.

    public async Task<IEnumerable<SearchResult>> SearchAsync(string searchTerm)
    
            {
    
                var File = await ApplicationData.Current.LocalFolder.TryGetItemAsync(dbname) as StorageFile;
    
                if (File == null)
    
                {
    
                    //does not exists
    
                }
    
                else { 
    
                    //exists
    
                }
    
                List<SearchResult> items = null;
    
                await Task.Run(() =>
    
                {
    
                    var cmd = new SQLiteCommand(conn);
    
                    cmd.CommandText = query;
    
                    cmd.Bind("@searchParam", searchTerm + "%");
    
                    items = cmd.ExecuteQuery<SearchResult>();
    
                    conn.Close();
    
                });
    
                return items;
    
            }
    

    If this doesn’t help, please send a repro project to me. I’m going to look into it. Use your OneDrive and share a link here.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by mcd023 Saturday, October 04, 2014 1:02 PM
    Friday, October 03, 2014 5:09 AM
    Moderator

All replies

  • Hi Michael,

    As I know, there are two scenarios for this problem.

    1. Connection is correct, but there are no such table in sqlite.
    2. Connection is incorrect, sqlite makes a new database file. When open the database, it cannot find such table.

    I assume you are in scenario 2. So before you Search in Sqlite, check the sqlite database file exists in device. Using StorageFolder.TryGetItemAsync method to check the file. Modify your code as following and try to find something more in your device.

    public async Task<IEnumerable<SearchResult>> SearchAsync(string searchTerm)
    
            {
    
                var File = await ApplicationData.Current.LocalFolder.TryGetItemAsync(dbname) as StorageFile;
    
                if (File == null)
    
                {
    
                    //does not exists
    
                }
    
                else { 
    
                    //exists
    
                }
    
                List<SearchResult> items = null;
    
                await Task.Run(() =>
    
                {
    
                    var cmd = new SQLiteCommand(conn);
    
                    cmd.CommandText = query;
    
                    cmd.Bind("@searchParam", searchTerm + "%");
    
                    items = cmd.ExecuteQuery<SearchResult>();
    
                    conn.Close();
    
                });
    
                return items;
    
            }
    

    If this doesn’t help, please send a repro project to me. I’m going to look into it. Use your OneDrive and share a link here.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by mcd023 Saturday, October 04, 2014 1:02 PM
    Friday, October 03, 2014 5:09 AM
    Moderator
  • Thanks for the help. That seemed to be the issue. I'm not exactly sure what I changed, but now my class looks like this:

    public static readonly string DbFileName = "KanjiDictionary1 - Copy.sqlite";
            public static readonly string ConnString = Path.Combine(ApplicationData.Current.LocalFolder.Path, DbFileName);
    
            public KanjiDictionaryRepository()
            {
                conn = new SQLiteConnection(ConnString);
            }
    The reason I say I don't know what I changed is that I pulled the DbFileName into its own string and now it works O_o Thank you very much for the help. I wasn't pointing to the right place in storage.


    Michael DiLeo

    Saturday, October 04, 2014 1:02 PM