Search Results

Search found 7270 results on 291 pages for 'sqlite extension'.

Page 18/291 | < Previous Page | 14 15 16 17 18 19 20 21 22 23 24 25  | Next Page >

  • Implementing a 1 to many relationship with SQLite

    - by Patrick
    I have the following schema implemented successfully in my application. The application connects desk unit channels to IO unit channels. The DeskUnits and IOUnits tables are basically just a list of desk/IO units and the number of channels on each. For example a desk could be 4 or 12 channel. CREATE TABLE DeskUnits (Name TEXT, NumChannels NUMERIC); CREATE TABLE IOUnits (Name TEXT, NumChannels NUMERIC); CREATE TABLE RoutingTable (DeskUnitName TEXT, DeskUnitChannel NUMERIC, IOUnitName TEXT, IOUnitChannel NUMERIC); The RoutingTable 'table' then connects each DeskUnit channel to an IOUnit channel. For example the DeskUnit called "Desk1" channel 1 may route to IOunit name "IOUnit1" channel 2, etc. So far I hope this is pretty straightforward and understandable. The problem is, however, this is a strictly 1 to 1 relationship. Any DeskUnit channel can route to only 1 IOUnit channel. Now, I need to implement a 1 to many relationship. Where any DeskUnit channel can connect to multiple IOUnit channels. I realise I may have to rearrange the tables completely, but I am not sure the best way to go about this. I am fairly new to SQLite and databases in general so any help would be appreciated. Thanks Patrick

    Read the article

  • Use Javascript RegEx to extract column names from SQLite Create Table SQL

    - by NimbusSoftware
    I'm trying to extract column names from a SQLite result set from sqlite_master's sql column. I get hosed up in the regular expressions in the match() and split() functions. t1.executeSql('SELECT name, sql FROM sqlite_master WHERE type="table" and name!="__WebKitDatabaseInfoTable__";', [], function(t1, result) { for(i = 0;i < result.rows.length; i++){ var tbl = result.rows.item(i).name; var dbSchema = result.rows.item(i).sql; // errors out on next line var columns = dbSchema.match(/.*CREATE\s+TABLE\s+(\S+)\s+\((.*)\).*/)[2].split(/\s+[^,]+,?\s*/); } }, function(){console.log('err1');} ); I want to parse SQL statements like these... CREATE TABLE sqlite_sequence(name,seq); CREATE TABLE tblConfig (Key TEXT NOT NULL,Value TEXT NOT NULL); CREATE TABLE tblIcon (IconID INTEGER NOT NULL PRIMARY KEY,png TEXT NOT NULL,img32 TEXT NOT NULL,img64 TEXT NOT NULL,Version TEXT NOT NULL) into a strings like theses... name,seq Key,Value IconID,png,img32,img64,Version Any help with a RegEx would be greatly appreciated.

    Read the article

  • Using a Context Menu to delete from a SQLite database in Android

    - by LordSnoutimus
    Hi, I have created a list view that displays the names and dates of items stored in a SQLite database, now I want to use a Context Menu to modify these items stored in the database such as edit the name, delete, and view. This is the code for the list view: public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.listview); SQLiteDatabase myDB = null; myDB = this.openOrCreateDatabase(MY_DB_NAME, MODE_PRIVATE, null); Cursor cur = myDB.rawQuery("SELECT _id, trackname, tracktime" + " FROM " + MY_DB_TABLE, null); ListAdapter adapter = new SimpleCursorAdapter(this, R.layout.listview, cur, new String[] { Constants.TRACK_NAME, Constants.TRACK_TIME}, new int[] { R.id.text1, R.id.text2}); ListView list = (ListView)findViewById(R.id.list); list.setAdapter(adapter); registerForContextMenu(list); } and the Context Menu... public void onCreateContextMenu(ContextMenu menu, View v, ContextMenuInfo menuInfo) { super.onCreateContextMenu(menu, v, menuInfo); menu.setHeaderTitle("Track Options"); menu.add(0, CHANGE_NAME, 0, "Change name"); menu.add(0, VIEW_TRACK, 0, "View track"); menu.add(0, SEND_TRACK, 0, "Send track"); menu.add(0, DELETE_TRACK, 0, "Delete track"); } I have used a Switch statement to control the menu items.. public boolean onContextItemSelected(MenuItem item) { switch (item.getItemId()){ case CHANGE_NAME: changename(); return true; case DELETE_TRACK: deletetrack(); return true; default: return super.onContextItemSelected(item); } So how would I go ahead and map the deletetrack(); method to find the ID of the track stored in the database to the item that has been selected in the list view?

    Read the article

  • Android SQLiteConstraintException: error code 19: constraint failed

    - by Tom D
    I've seen other questions about this exception, but all of them seem to be resolved with the solution that a row with the primary key specified already exists. This doesn't seem to be the case for me. I have tried replacing all single quotes in my strings with double quotes, but the same problem occurs. I'm trying to insert a row into the Settings table of the SQLite database I've created by doing the following: db.execSQL("DROP TABLE IF EXISTS "+Settings.SETTINGS_TABLE_NAME + ";"); db.execSQL(CREATE_MEDIA_TABLE); db.execSQL(CREATE_SETTINGS_TABLE); Cursor c = getAllSettings(); //If there isn't already a settings row, create a row full of defaults if(c.getCount()==0){ ContentValues cv = new ContentValues(); cv.put(Settings.SETTING_UNIQUE_ID, "'"+Settings.uniqueID+"'"); cv.put(Settings.SETTING_DEVICE_ID, Settings.SETTING_DEVICE_ID_DEFAULT); cv.put(Settings.SETTING_CONNECTION_PREFERENCE, Settings.SETTING_CONNECTION_PREFERENCE_DEFAULT); cv.put(Settings.SETTING_AD_HOC_ENABLED, Settings.SETTING_AD_HOC_ENABLED_DEFAULT); cv.put(Settings.SETTING_SERVER_ADDRESS, Settings.SETTING_SERVER_ADDRESS_DEFAULT); cv.put(Settings.SETTING_RECORDING_MODE, Settings.SETTING_RECORDING_MODE_DEFAULT); cv.put(Settings.SETTING_PREVIEW_ENABLED, Settings.SETTING_PREVIEW_ENABLED_DEFAULT); cv.put(Settings.SETTING_PICTURE_RESOLUTION_X, Settings.SETTING_PICTURE_RESOLUTION_X_DEFAULT); cv.put(Settings.SETTING_PICTURE_RESOLUTION_Y, Settings.SETTING_PICTURE_RESOLUTION_Y_DEFAULT); cv.put(Settings.SETTING_VIDEO_RESOLUTION_X, Settings.SETTING_VIDEO_RESOLUTION_X_DEFAULT); cv.put(Settings.SETTING_VIDEO_RESOLUTION_Y, Settings.SETTING_VIDEO_RESOLUTION_Y_DEFAULT); cv.put(Settings.SETTING_VIDEO_FPS, Settings.SETTING_VIDEO_FPS_DEFAULT); cv.put(Settings.SETTING_AUDIO_BITRATE_KBPS, Settings.SETTING_AUDIO_BITRATE_KBPS_DEFAULT); cv.put(Settings.SETTING_STORE_TO_SD, Settings.SETTING_STORE_TO_SD_DEFAULT); cv.put(Settings.SETTING_STORAGE_LIMIT_MB, Settings.SETTING_STORAGE_LIMIT_MB_DEFAULT); this.db.insert(Settings.SETTINGS_TABLE_NAME, null, cv); } The CREATE_SETTINGS_TABLE string is defined as the following: private static String CREATE_SETTINGS_TABLE = "CREATE TABLE IF NOT EXISTS " + Settings.SETTINGS_TABLE_NAME + "(" + Settings.SETTING_UNIQUE_ID + " TEXT NOT NULL PRIMARY KEY, " + Settings.SETTING_DEVICE_ID + " TEXT NOT NULL , " + Settings.SETTING_CONNECTION_PREFERENCE + " TEXT NOT NULL CHECK("+Settings.SETTING_CONNECTION_PREFERENCE+" IN("+Settings.SETTING_CONNECTION_PREFERENCE_ALLOWED+")), " + Settings.SETTING_AD_HOC_ENABLED + " TEXT NOT NULL CHECK("+Settings.SETTING_AD_HOC_ENABLED+" IN("+Settings.SETTING_AD_HOC_ENABLED_ALLOWED+")), " + Settings.SETTING_SERVER_ADDRESS + " TEXT NOT NULL, " + Settings.SETTING_RECORDING_MODE + " TEXT NOT NULL CHECK("+Settings.SETTING_RECORDING_MODE+" IN("+Settings.SETTING_RECORDING_MODE_ALLOWED+")), " + Settings.SETTING_PREVIEW_ENABLED + " TEXT NOT NULL CHECK("+Settings.SETTING_PREVIEW_ENABLED+" IN("+Settings.SETTING_PREVIEW_ENABLED_ALLOWED+")), " + Settings.SETTING_PICTURE_RESOLUTION_X + " TEXT NOT NULL, " + Settings.SETTING_PICTURE_RESOLUTION_Y + " TEXT NOT NULL, " + Settings.SETTING_VIDEO_RESOLUTION_X + " TEXT NOT NULL, " + Settings.SETTING_VIDEO_RESOLUTION_Y + " TEXT NOT NULL, " + Settings.SETTING_VIDEO_FPS + " TEXT NOT NULL, " + Settings.SETTING_AUDIO_BITRATE_KBPS + " TEXT NOT NULL, " + Settings.SETTING_STORE_TO_SD + " TEXT NOT NULL CHECK("+Settings.SETTING_STORE_TO_SD+" IN("+Settings.SETTING_STORE_TO_SD_ALLOWED+")), " + Settings.SETTING_STORAGE_LIMIT_MB + " TEXT NOT NULL )"; However, when I execute my insert, I always get: 03-19 19:37:36.974: ERROR/Database(386): Error inserting server_address='0.0.0.0' storage_limit='-1' connection='none' preview_enabled='0' sd_enabled='1' video_fps='15' audio_bitrate='96' device_id='-1' recording_mode='none' picture_resolution_x='-1' picture_resolution_y='-1' unique_id='000000000000000' adhoc_enable='0' video_resolution_x='320' video_resolution_y='240' 03-19 19:45:34.284: ERROR/Database(446): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed It seems as if all the columns in my insert are not null. The row's primary key HAS to be unique, because it's the only row in the table. Therefore, the only thing I can think of is my CHECK conditions aren't true. Here are the predefined strings I'm using: public static final String SETTING_UNIQUE_ID = "unique_id"; public static final String SETTING_DEVICE_ID = "device_id"; public static final String SETTING_DEVICE_ID_DEFAULT = "'-1'"; public static final String SETTING_CONNECTION_PREFERENCE = "connection"; public static final String SETTING_CONNECTION_PREFERENCE_3G = "'3g'"; public static final String SETTING_CONNECTION_PREFERENCE_WIFI = "'wifi'"; public static final String SETTING_CONNECTION_PREFERENCE_NONE = "'none'"; public static final String SETTING_CONNECTION_PREFERENCE_ALLOWED = SETTING_CONNECTION_PREFERENCE_3G+","+SETTING_CONNECTION_PREFERENCE_WIFI+","+SETTING_CONNECTION_PREFERENCE_NONE; public static final String SETTING_CONNECTION_PREFERENCE_DEFAULT = SETTING_CONNECTION_PREFERENCE_NONE; public static final String SETTING_AD_HOC_ENABLED = "adhoc_enable"; public static final String SETTING_AD_HOC_ENABLED_ALLOWED = TRUE+","+FALSE; public static final String SETTING_AD_HOC_ENABLED_DEFAULT = FALSE; public static final String SETTING_SERVER_ADDRESS = "server_address"; public static final String SETTING_SERVER_ADDRESS_DEFAULT = "'0.0.0.0'"; public static final String SETTING_RECORDING_MODE = "recording_mode"; public static final String SETTING_RECORDING_MODE_VIDEO = "'video'"; public static final String SETTING_RECORDING_MODE_AUDIO = "'audio'"; public static final String SETTING_RECORDING_MODE_PICTURE = "'picture'"; public static final String SETTING_RECORDING_MODE_NONE = "'none'"; public static final String SETTING_RECORDING_MODE_ALLOWED = SETTING_RECORDING_MODE_VIDEO+","+SETTING_RECORDING_MODE_AUDIO+","+SETTING_RECORDING_MODE_PICTURE+","+SETTING_RECORDING_MODE_NONE; public static final String SETTING_RECORDING_MODE_DEFAULT = SETTING_RECORDING_MODE_NONE; public static final String SETTING_PREVIEW_ENABLED = "preview_enabled"; public static final String SETTING_PREVIEW_ENABLED_ALLOWED = TRUE+","+FALSE; public static final String SETTING_PREVIEW_ENABLED_DEFAULT = FALSE; public static final String SETTING_PICTURE_RESOLUTION_X = "picture_resolution_x"; public static final String SETTING_PICTURE_RESOLUTION_X_DEFAULT = "'-1'"; public static final String SETTING_PICTURE_RESOLUTION_Y = "picture_resolution_y"; public static final String SETTING_PICTURE_RESOLUTION_Y_DEFAULT = "'-1'"; public static final String SETTING_VIDEO_RESOLUTION_X = "video_resolution_x"; public static final String SETTING_VIDEO_RESOLUTION_X_DEFAULT = "'320'"; public static final String SETTING_VIDEO_RESOLUTION_Y = "video_resolution_y"; public static final String SETTING_VIDEO_RESOLUTION_Y_DEFAULT = "'240'"; public static final String SETTING_VIDEO_FPS = "video_fps"; public static final String SETTING_VIDEO_FPS_DEFAULT = "'15'"; public static final String SETTING_AUDIO_BITRATE_KBPS = "audio_bitrate"; public static final String SETTING_AUDIO_BITRATE_KBPS_DEFAULT = "'96'"; public static final String SETTING_STORE_TO_SD = "sd_enabled"; public static final String SETTING_STORE_TO_SD_ALLOWED = TRUE+","+FALSE; public static final String SETTING_STORE_TO_SD_DEFAULT = TRUE; public static final String SETTING_STORAGE_LIMIT_MB = "storage_limit"; public static final String SETTING_STORAGE_LIMIT_MB_DEFAULT = "'-1'"; public static final String SETTING_CLIP_LENGTH_SECONDS = "clip_length"; public static final String SETTING_CLIP_LENGTH_SECONDS_DEFAULT = "'300'"; Does anyone see what could be going on? I'm stumped. Thanks in advance.

    Read the article

  • iPhone and iPad : Doing a "select * from something" query in a SQLite database

    - by Abramodj
    Hi folks, i'm trying to use the SQLite data base in my iPad app, and here's my function to make a query: - (void)executeQuery:(char*)query { NSString *file = [self getWritableDBPath]; NSFileManager *fileManager = [NSFileManager defaultManager]; BOOL success = [fileManager fileExistsAtPath:file]; // If its not a local copy set it to the bundle copy if(!success) { //file = [[NSBundle mainBundle] pathForResource:DATABASE_TITLE ofType:@"db"]; [self createEditableCopyOfDatabaseIfNeeded]; } dataArray = NULL; dataArray = [NSMutableArray array]; sqlite3 *database = NULL; if (sqlite3_open([file UTF8String], &database) == SQLITE_OK) { sqlite3_exec(database, query, loadTimesCallback, dataArray, NULL); } sqlite3_close(database); [self logResults]; } if I call [self executeQuery:"select name from table1"]; everything is working fine. But if i call [self executeQuery:"select * from cars"]; the app crashes telling me that the NSMutableArray dataArray is not the right kind of variable where to set the query results. So, how can i do a "select * form table1" query, and store the results? Thanks! EDIT: Here's my loadTimesCallback method: static int loadTimesCallback(void *context, int count, char **values, char **columns) { NSMutableArray *times = (NSMutableArray *)context; for (int i=0; i < count; i++) { const char *nameCString = values[i]; [times addObject:[NSString stringWithUTF8String:nameCString]]; } return SQLITE_OK; }

    Read the article

  • Selecting rows with references across tables in SQLite 3

    - by ChristianK
    Hey there, I have a problem with a SQLite photo/album database. The database contains 3 tables: Albums id name hide -------------------------- 1 Holiday 2010 1 2 Day Trip 0 Photos id file ----------------- 1 photo1.jpg 2 photo2.jpg 3 photo3.jpg 4 photo4.jpg Relation (connects photos with albums) album photo ----------------- 1 1 1 2 2 3 2 1 A photo can be assigned to zero, one or several albums. Each album has a column 'hide' that indicates, whether the photos of this album should be ignored. I'm trying to find a SELECT query that returns all photos that are not assigned to an album + all the photos that are in albums which are not hidden (i.e. that have their 'hide' value set to 0). I came up with a query that selects photos in visible albums, but I don't know how to include the photos that are not assigned to an album. SELECT file FROM photos, albums, relation WHERE photos.id = relation.photo AND albums.id = relation.album AND albums.hide = 0 This query returns photo3.jpg and the required result would be photo3.jpg photo4.jpg because photo4.jpg is not assigned to album in the Relation table. Do you know how to solve this? Thank you very much for your help!

    Read the article

  • Use combobox value as query value (VC# 2k8 / ADO SQLITE)

    - by nicodevil
    Hi, I'm a newbie in VC# and ADO SQLITE... I'm trying to change the content of a combobox according to the value present in another one... My problem is here : SQLiteCommand cmd3 = new SQLiteCommand("select distinct(ACTION) from ACTION_LIST where CATEGORY='comboBox1.text'", conn2); What to use to do the job ? Here 'comboBox1.text' is see as a sentence not a variable... Here is the code : private void Form1_Load(object sender, EventArgs e) { using (SQLiteConnection conn1 = new SQLiteConnection(@"Data Source = Data\MRIS_DB_MASTER")) { conn1.Open(); SQLiteCommand cmd2 = new SQLiteCommand("select distinct(CATEGORY) from ACTION_LIST", conn1); SQLiteDataAdapter adapter1 = new SQLiteDataAdapter(cmd2); DataTable tbl1 = new DataTable(); adapter1.Fill(tbl1); comboBox1.DataSource = tbl1; comboBox1.DisplayMember = "CATEGORY"; adapter1.Dispose(); cmd2.Dispose(); } using (SQLiteConnection conn2 = new SQLiteConnection(@"Data Source = Data\MRIS_DB_MASTER")) { conn2.Open(); SQLiteCommand cmd3 = new SQLiteCommand("select distinct(ACTION) from ACTION_LIST where CATEGORY='comboBox1.text'", conn2); SQLiteDataAdapter adapter2 = new SQLiteDataAdapter(cmd3); DataTable tbl2 = new DataTable(); adapter2.Fill(tbl2); comboBox2.DataSource = tbl2; comboBox2.DisplayMember = "ACTION"; adapter2.Dispose(); cmd3.Dispose(); } } Thanks regards

    Read the article

  • Bulk inserts into sqlite db on the iphone...

    - by akaii
    I'm inserting a batch of 100 records, each containing a dictonary containing arbitrarily long HTML strings, and by god, it's slow. On the iphone, the runloop is blocking for several seconds during this transaction. Is my only recourse to use another thread? I'm already using several for acquiring data from HTTP servers, and the sqlite documentation explicitly discourages threading with the database, even though it's supposed to be thread-safe... Is there something I'm doing extremely wrong that if fixed, would drastically reduce the time it takes to complete the whole operation? NSString* statement; statement = @"BEGIN EXCLUSIVE TRANSACTION"; sqlite3_stmt *beginStatement; if (sqlite3_prepare_v2(database, [statement UTF8String], -1, &beginStatement, NULL) != SQLITE_OK) { printf("db error: %s\n", sqlite3_errmsg(database)); return; } if (sqlite3_step(beginStatement) != SQLITE_DONE) { sqlite3_finalize(beginStatement); printf("db error: %s\n", sqlite3_errmsg(database)); return; } NSTimeInterval timestampB = [[NSDate date] timeIntervalSince1970]; statement = @"INSERT OR REPLACE INTO item (hash, tag, owner, timestamp, dictionary) VALUES (?, ?, ?, ?, ?)"; sqlite3_stmt *compiledStatement; if(sqlite3_prepare_v2(database, [statement UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK) { for(int i = 0; i < [items count]; i++){ NSMutableDictionary* item = [items objectAtIndex:i]; NSString* tag = [item objectForKey:@"id"]; NSInteger hash = [[NSString stringWithFormat:@"%@%@", tag, ownerID] hash]; NSInteger timestamp = [[item objectForKey:@"updated"] intValue]; NSData *dictionary = [NSKeyedArchiver archivedDataWithRootObject:item]; sqlite3_bind_int( compiledStatement, 1, hash); sqlite3_bind_text( compiledStatement, 2, [tag UTF8String], -1, SQLITE_TRANSIENT); sqlite3_bind_text( compiledStatement, 3, [ownerID UTF8String], -1, SQLITE_TRANSIENT); sqlite3_bind_int( compiledStatement, 4, timestamp); sqlite3_bind_blob( compiledStatement, 5, [dictionary bytes], [dictionary length], SQLITE_TRANSIENT); while(YES){ NSInteger result = sqlite3_step(compiledStatement); if(result == SQLITE_DONE){ break; } else if(result != SQLITE_BUSY){ printf("db error: %s\n", sqlite3_errmsg(database)); break; } } sqlite3_reset(compiledStatement); } timestampB = [[NSDate date] timeIntervalSince1970] - timestampB; NSLog(@"Insert Time Taken: %f",timestampB); // COMMIT statement = @"COMMIT TRANSACTION"; sqlite3_stmt *commitStatement; if (sqlite3_prepare_v2(database, [statement UTF8String], -1, &commitStatement, NULL) != SQLITE_OK) { printf("db error: %s\n", sqlite3_errmsg(database)); } if (sqlite3_step(commitStatement) != SQLITE_DONE) { printf("db error: %s\n", sqlite3_errmsg(database)); } sqlite3_finalize(beginStatement); sqlite3_finalize(compiledStatement); sqlite3_finalize(commitStatement);

    Read the article

  • SQLite bulk insert on iPhone not working

    - by App_beginner
    Hi. I have been struggling with this seeminly easy problem for 48 hours, and I am no closer to a solution. So I was hoping that someone might be able to help me. I am building a app, that use a combination of a local (SQLite) database and an online database (PHP/MYSQL). The app is nearly finished. Checked for leaks and work like a charm. However the very last part is the part I have struggled with. On launch, I want the app to check for changes to the online databse, and if there is. I want it to download and parse a xml file containing the changes. Everything is working fine this far. But when I try to bulk insert my parsed data to my database, the app crashes, giving a NSInternalInconsistency error. Due to the database returning SQLITE_MISUSE. I have done a lot of googling, but am still unable to solve my problem. So I am putting the code here, hoping that someone can help me fix this. And I know that I should have used core data for this. But this is the very last part I am struggling with, and I am very reluctant to changing my entire code now. Core data will have to come in the update. Here is the error I recieve: Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'Error while inserting data. 'library routine called out of sequence'' Here is my code: -(void)UpdateDatabase:(const char *)_query NewValues:(NSMutableArray *)_odb dbn:(NSString *)_dbn dbp:(NSString *)_dbp { sqlite3 *database; NSMutableArray *NewValues = _odb; int i; const char *query = _query; sqlite3_stmt *addStmt; for (i = 1; i < [NewValues count]; i++) { if(sqlite3_prepare_v2(database, query, -1, &addStmt, NULL) == SQLITE_OK) { sqlite3_bind_text(addStmt, 1, [[[NewValues objectAtIndex:i] name] UTF8String], -1, SQLITE_TRANSIENT); sqlite3_bind_text(addStmt, 2, [[[NewValues objectAtIndex:i] city]UTF8String], -1, SQLITE_TRANSIENT); sqlite3_bind_double(addStmt, 3, [[[NewValues objectAtIndex:i] lat] doubleValue]); sqlite3_bind_int(addStmt, 4, [[[NewValues objectAtIndex:i] long] doubleValue]); sqlite3_bind_int(addStmt, 5, [[[NewValues objectAtIndex:i] code] intValue]); } if(SQLITE_DONE != sqlite3_step(addStmt)) { NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database)); } //Reset the add statement. sqlite3_reset(addStmt); } }

    Read the article

  • Gotchas INSERTing into SQLite on Android?

    - by paul.meier
    Hi friends, I'm trying to set up a simple SQLite database in Android, handling the schema via a subclass of SQLiteOpenHelper. However, when I query my tables, the columns I think I've inserted are never present. Namely, in SQLiteOpenHelper's onCreate(SQLiteDatabase db) method, I use db.execSQL() to run CREATE TABLE commands, then have tried both db.execSQL and db.insert() to run INSERT commands on the tables I've just created. This appears to run fine, but when I try to query them I always get 0 rows returned (for debugging, the queries I'm running are simple SELECT * FROM table and checking the Cursor's getCount()). Anybody run into anything like this before? These commands seem to run on command-line sqlite3. Are they're gotchas that I'm missing (e.g. INSERTS must/must not be semicolon terminated, or some issue involving multiple tables)? I've attached some of the code below. Thanks for your time, and let me know if I can clarify further. @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE "+ LEVEL_TABLE +" (" + " "+ _ID +" INTEGER PRIMARY KEY AUTOINCREMENT," + " level TEXT NOT NULL,"+ " rows INTEGER NOT NULL,"+ " cols INTEGER NOT NULL);"); db.execSQL("CREATE TABLE "+ DYNAMICS_TABLE +" (" + " level_id INTEGER NOT NULL," + " row INTEGER NOT NULL,"+ " col INTEGER NOT NULL,"+ " type INTEGER NOT NULL);"); db.execSQL("CREATE TABLE "+ SCORE_TABLE +" (" + " level_id INTEGER NOT NULL," + " score INTEGER NOT NULL,"+ " date_achieved DATE NOT NULL,"+ " name TEXT NOT NULL);"); this.enterFirstLevel(db); } And a sample of the insert code I'm currently using, which gets called in enterFirstLevel() (some values hard-coded just to get it running...): private void insertDynamic(SQLiteDatabase db, int row, int col, int type) { ContentValues values = new ContentValues(); values.put("level_id", "1"); values.put("row", Integer.toString(row)); values.put("col", Integer.toString(col)); values.put("type", Integer.toString(type)); db.insertOrThrow(DYNAMICS_TABLE, "col", values); } Finally, query code looks like this: private Cursor fetchLevelDynamics(int id) { SQLiteDatabase db = this.leveldata.getReadableDatabase(); try { String fetchQuery = "SELECT * FROM " + DYNAMICS_TABLE; String[] queryArgs = new String[0]; Cursor cursor = db.rawQuery(fetchQuery, queryArgs); Activity activity = (Activity) this.context; activity.startManagingCursor(cursor); return cursor; } finally { db.close(); } }

    Read the article

  • SQLite/iPhone read copyright symbol

    - by Marco A
    Hi All, I am having problems reading the copyright symbol from a sqlite db that I have for my App that I am developing. I import the information manually, ie, from an excel sheet. I have tried two ways of doing it and failed with both: 1) Tried replacing the copyright symbol with "\u00ae" (unicode combination) within excel and then importing the modified file. - Result: I get the combination of \u00ae as a part of the string, it doesnt detect the unicode combination. 2) Tried leaving as it is. Importing the excel with the copyright symbol. - Result: I get a symbol that is different from the copyright, its something like an AE put together.looks like this: Æ Heres my code how I read from DB: -(void) readCategoriesFromDatabase:(NSString *) rest_input { // Init the products Array categories = [[NSMutableArray alloc] init]; // Open the database from the users filessytem rest_input = [rest_input stringByAppendingString:@"'"]; NSString *newString; newString = [@"select distinct category from food where restaurant='" stringByAppendingString:rest_input]; const char *cat_sqlStatement = [newString UTF8String]; sqlite3_stmt *cat_compiledStatement; if(sqlite3_prepare_v2(database, cat_sqlStatement, -1, &cat_compiledStatement, NULL) == SQLITE_OK) { // Loop through the results and add them to the feeds array while(sqlite3_step(cat_compiledStatement) == SQLITE_ROW) { NSString *catName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(cat_compiledStatement,0)]; // Create a new product object with the data from the database Product *category = [[Product alloc] initWithName:catName]; // Add the product object to the respective Array [categories addObject:category]; [category release]; } sqlite3_finalize(cat_compiledStatement); } NSLog(@"Finished Accessing Database to gather Categories...."); } I open the DB with this function: -(void) checkAndCreateDatabase{ NSLog(@"Checking/Creating Database...."); NSFileManager *fileManager = [NSFileManager defaultManager]; success = [fileManager fileExistsAtPath:databasePath]; [fileManager removeFileAtPath:databasePath handler:nil]; NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName]; [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil]; [fileManager release]; if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) { sqlite3_close(database); database = nil; } NSLog(@"Finished Checking/Creating Database...."); } Thanks to anything that can help me out.

    Read the article

  • sqlite eatingup memory on iPhone when doing insert

    - by kviksilver
    I am having problem with inserting data to sqlite database. char *update="INSERT OR REPLACE INTO ct_subject (id,id_parent, title, description, link, address, phone, pos_lat, pos_long, no_votes, avg_vote, photo, id_comerc, id_city, placement, type, timestamp, mail) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"; sqlite3_stmt *stmt; if(sqlite3_prepare_v2(database, update, -1, &stmt, nil) == SQLITE_OK){ sqlite3_bind_int(stmt, 1, [[[newCategories objectAtIndex:i] valueForKey:@"id"] intValue]); sqlite3_bind_int(stmt, 2, [[[newCategories objectAtIndex:i] valueForKey:@"id_parent"] intValue]); sqlite3_bind_text(stmt, 3, [[[newCategories objectAtIndex:i] valueForKey:@"title"] UTF8String], -1, NULL); sqlite3_bind_text(stmt, 4, [[[newCategories objectAtIndex:i] valueForKey:@"description"] UTF8String], -1, NULL); sqlite3_bind_text(stmt, 5, [[[newCategories objectAtIndex:i] valueForKey:@"link"] UTF8String], -1, NULL); sqlite3_bind_text(stmt, 6, [[[newCategories objectAtIndex:i] valueForKey:@"address"] UTF8String], -1, NULL); sqlite3_bind_text(stmt, 7, [[[newCategories objectAtIndex:i] valueForKey:@"phone"] UTF8String], -1, NULL); sqlite3_bind_text(stmt, 8, [[[newCategories objectAtIndex:i] valueForKey:@"pos_lat"] UTF8String], -1, NULL); sqlite3_bind_text(stmt, 9, [[[newCategories objectAtIndex:i] valueForKey:@"pos_long"] UTF8String], -1, NULL); sqlite3_bind_int(stmt, 10, [[[newCategories objectAtIndex:i] valueForKey:@"no_votes"] intValue]); sqlite3_bind_text(stmt, 11, [[[newCategories objectAtIndex:i] valueForKey:@"avg_vote"] UTF8String], -1, NULL); if ([[[newCategories objectAtIndex:i] valueForKey:@"photo"] length]!=0) { NSMutableString *webUrl = (NSMutableString *)[[NSMutableString alloc] initWithString:@"http://www.crotune.com/public/images/subjects/"]; [webUrl appendString:[[newCategories objectAtIndex:i] valueForKey:@"photo"]]; UIImage *myImage = [self getWebImage:webUrl]; if(myImage != nil){ sqlite3_bind_blob(stmt, 12, [UIImagePNGRepresentation(myImage) bytes], [UIImagePNGRepresentation(myImage) length], NULL); } else { sqlite3_bind_blob(stmt, 12, nil, -1, NULL); } [webUrl release]; [myImage release]; } else { sqlite3_bind_blob(stmt, 12, nil, -1, NULL); //NSLog(@" ne dodajem sliku2"); } sqlite3_bind_int(stmt, 13, [[[newCategories objectAtIndex:i] valueForKey:@"id_comerc"] intValue]); sqlite3_bind_int(stmt, 14, [[[newCategories objectAtIndex:i] valueForKey:@"id_city"] intValue]); sqlite3_bind_int(stmt, 15, [[[newCategories objectAtIndex:i] valueForKey:@"placement"] intValue]); sqlite3_bind_int(stmt, 16, [[[newCategories objectAtIndex:i] valueForKey:@"type"] intValue]); sqlite3_bind_int(stmt, 17, [[[newCategories objectAtIndex:i] valueForKey:@"timestamp"] intValue]); sqlite3_bind_text(stmt, 18, [[[newCategories objectAtIndex:i] valueForKey:@"mail"] UTF8String], -1, NULL); } if (sqlite3_step(stmt) != SQLITE_DONE) { NSLog(@"%s", sqlite3_errmsg(database)); NSAssert1(0,@"nemogu updateat table %s", errorMsg); } else { NSLog(@"Ubacio %d",i); }sqlite3_finalize(stmt); What happens is that it starts to eat memory until it finaly quits... On memory warning i close and open database again, I have set cache size to 50 as mentioned in some posts here, and tried putting query into statement - same result.. it just garbles mamory and app quits after 300 inserts on iphone or somewhere around 900 inserts on iPad... Any help would be appreciated..

    Read the article

  • Android - Retrieve all data from a SQLite table row

    - by Paul
    I have searched and cannot find an answer to my issue so i hope i am not completely barking up the wrong tree (so to speak). I am new to android and have started to create an app. My app on one screen creates and adds entries to a SQLite database using public class DatabaseHandler extends SQLiteOpenHelper and this all appears to work. I retrieve all the data and populate it into a grid, again this now works. My issue is I am unable to retrieve one complete line from the grid. I populate/display the grid with the following code. I have cut a lot out as the grid is made in stages, header, blank lines etc but the grid does display as I want. The id’s work as when I touch a line it displays its unique id. The onClick is right at the end and when I use getText() instead of getID() all it returns is the data in the labelDate. How do I retrieve all the labels as listed below? TextView labelDATE = new TextView(this); TextView labelCP = new TextView(this); TextView labelBG = new TextView(this); TextView labelQA = new TextView(this); TextView labelCN = new TextView(this); TextView labelKT = new TextView(this); TextView[] tvArray = {labelDATE, labelCP, labelBG, labelQA, labelCN, labelKT}; labelDATE.setText(re.getTime()); labelCP.setText(re.getCP()); labelBG.setText(re.getBG()); labelQA.setText(re.getQA()); labelCN.setText(re.getCN()); labelKT.setText(re.getKT()); for (TextView tv : tvArray) { tv.setTextColor(Color.WHITE); tv.setId(200+count); tr.setOnClickListener(this); tr.addView(tv); } //add this to the table row tl.addView(tr, new TableLayout.LayoutParams(LayoutParams.MATCH_PARENT,LayoutParams.WRAP_CONTENT)); public void onClick(View v) { if (v instanceof TableRow) { TableRow row = (TableRow) v; TextView child = (TextView) row.getChildAt(0); Toast toast = Toast.makeText(this, String.valueOf(child.getId()), Toast.LENGTH_SHORT); toast.show(); } } I can supply all the code for the grid creation if required. Thanks for any help.

    Read the article

  • Execute a SQlite command with Entity Framework

    - by Filimindji
    Hi everybody, I use a SQLite database and Entity Framework (with .net framework 3.5). I'm trying to execute a simple SQL non query command to create a new table in this datase. My Entity Framework already contains the object model for this table : I just want to generate the corresponding table using a command. (By the way, there is maybe a better way to do this. Any ideas someone :) My problem is that I'm not able to execute any command, even the simple commands. Here is my code : EntityConnection entityConnection = new EntityConnection(entitiesConnectionString); Entities db = new Entities(entityConnection); DbCommand command = db.Connection.CreateCommand(); command.CommandText ="CREATE TABLE MyTable (Id int NOT NULL, OtherTable_Id nchar(40) REFERENCES OtherTable (Id) On Delete CASCADE On Update NO ACTION, SomeData nvarchar(1024) NOT NULL, Primary Key(Id) );"; command.ExecuteNonQuery(); I got this error : System.Data.EntitySqlException: The query syntax is not valid., near identifier 'TABLE', line 1, column 8. at System.Data.Common.EntitySql.CqlParser.yyerror(String s) at System.Data.Common.EntitySql.CqlParser.yyparse() at System.Data.Common.EntitySql.CqlParser.Parse(String query) at System.Data.Common.EntitySql.CqlQuery.Parse(String query, ParserOptions parserOptions) at System.Data.Common.EntitySql.CqlQuery.Compile(String query, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables, Boolean validateTree) at System.Data.EntityClient.EntityCommand.MakeCommandTree() at System.Data.EntityClient.EntityCommand.CreateCommandDefinition() at System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQueryCache(EntityCommandDefinition& entityCommandDefinition) at System.Data.EntityClient.EntityCommand.GetCommandDefinition() at System.Data.EntityClient.EntityCommand.InnerPrepare() at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior) at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resultSelector) It's seem to be a syntax error, but I can't figure where is the problem and how to resolve it. The entityConnection is ok because I can use any entities generated with EF. I tried with another simple command, but it throw another exception : DbCommand command = db.Connection.CreateCommand(); command.CommandText = "SELECT COUNT(Id) From OtherTable;"; int result = (int)command.ExecuteScalar(); And I got this error, witch is not the same, but may help : System.Data.EntitySqlException: 'Groupe' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly., near simple identifier, line 1, column 23. at System.Data.Common.EntitySql.CqlErrorHelper.ReportIdentifierError(Expr expr, SemanticResolver sr) at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertIdentifier(Expr expr, SemanticResolver sr) at System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Expr astExpr, SemanticResolver sr) at System.Data.Common.EntitySql.SemanticAnalyzer.ProcessAliasedFromClauseItem(AliasExpr aliasedExpr, SemanticResolver sr) at System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr) at System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClause(FromClause fromClause, SemanticResolver sr) at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQuery(Expr expr, SemanticResolver sr) at System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Expr astExpr, SemanticResolver sr) at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertRootExpression(Expr astExpr, SemanticResolver sr) at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertGeneralExpression(Expr astExpr, SemanticResolver sr) at System.Data.Common.EntitySql.CqlQuery.AnalyzeSemantics(Expr astExpr, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables) at System.Data.Common.EntitySql.CqlQuery.Compile(String query, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables, Boolean validateTree) at System.Data.EntityClient.EntityCommand.MakeCommandTree() at System.Data.EntityClient.EntityCommand.CreateCommandDefinition() at System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQueryCache(EntityCommandDefinition& entityCommandDefinition) at System.Data.EntityClient.EntityCommand.GetCommandDefinition() at System.Data.EntityClient.EntityCommand.InnerPrepare() at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior) at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resultSelector)

    Read the article

  • Compiling the icu sqlite extension statically linked to icu.

    - by Georg
    I want to compile the icu sqlite extension statically linked to icu. This is what I've tried, maybe the mistake is obvious to you. cd icu/source ./runConfigureIcu Linux --enable-static --with-packaging-format=archive ... make cd ../../icu-sqlite gcc -o libSqliteIcu.so -shared icu.c -I../icu/source/common -I../icu/source/i18n -L ../icu/source/lib -lsicuuc -lsicui18n -lsicudata ... sqlite3 .load "libSqliteIcu.so" Undefined symbol utf8_countTrailBytes Files icu sqlite extension Download icu.c from sqlite.org ICU 4.2.1 Download ICU4C from icu-project.org My Requirements Runs on Linux & Windows Only one file that I have to distribute: libSqliteIcu.so. Any idea what else I can try? Documentation Sqlite ICU extension's readme ICU's readme

    Read the article

  • Does SQLite handle non-English locales out of the box?

    - by Andrey Tarantsov
    I have noticed that Google Toolbox for Mac replaces several SQLite built-in functions (LOWER/UPPER, LIKE, GLOB) with its own versions that handle string locales better. So, question to everyone who has SQLite experience: have you ever had any problems with non-English locales in SQLite? Does one really have to do something to properly handle non-English alphabets? What kinds of problems are expected if I use SQLite APIs as-is? (I'm going to use SQLite on the iPhone, but I guess it's the same across all platforms. I've been using Core Data previously and never had any problems, but this time I want to switch to non-ORMed db access.)

    Read the article

  • C# SQLite file import prevent duplicates

    - by jakesankey
    Hi, I am attempting to get a directory (which is ever-growing) full of .txt comma delimited files to import into my SQLite db. I now have all of the files importing ok, however I need to have some way of excluding the files that have been previously added to db. I have a column in the db called FileName where the name and extension are stored next to each record from each file. Now I need to say 'If the code finds XXX.txt and XXX.txt is already in db, then skip this file'. Can I somehow add this logic to the getfiles command or is there another easy way? using (SQLiteCommand insertCommand = con.CreateCommand()) { SQLiteCommand cmdd = con.CreateCommand(); string[] files = Directory.GetFiles(@"C:\Documents and Settings\js91162\Desktop\", "R303717*.txt*", SearchOption.AllDirectories); foreach (string file in files) { string FileNameExt1 = Path.GetFileName(file); cmdd.CommandText = @" SELECT COUNT(*) FROM Import WHERE FileName = @FileExt;"; cmdd.Parameters.Add(new SQLiteParameter("@FileExt", FileNameExt1)); int count = Convert.ToInt32(cmdd.ExecuteScalar()); //int count = ((IConvertible)insertCommand.ExecuteScalar().ToInt32(null)); if (count == 0) { Console.WriteLine("Parsing CMM data for SQL database... Please wait."); insertCommand.CommandText = @" INSERT INTO Import (FeatType, FeatName, Value, Actual, Nominal, Dev, TolMin, TolPlus, OutOfTol, PartNumber, CMMNumber, Date, FileName) VALUES (@FeatType, @FeatName, @Value, @Actual, @Nominal, @Dev, @TolMin, @TolPlus, @OutOfTol, @PartNumber, @CMMNumber, @Date, @FileName);"; insertCommand.Parameters.Add(new SQLiteParameter("@FeatType", DbType.String)); insertCommand.Parameters.Add(new SQLiteParameter("@FeatName", DbType.String)); insertCommand.Parameters.Add(new SQLiteParameter("@Value", DbType.String)); insertCommand.Parameters.Add(new SQLiteParameter("@Actual", DbType.Decimal)); insertCommand.Parameters.Add(new SQLiteParameter("@Nominal", DbType.Decimal)); insertCommand.Parameters.Add(new SQLiteParameter("@Dev", DbType.Decimal)); insertCommand.Parameters.Add(new SQLiteParameter("@TolMin", DbType.Decimal)); insertCommand.Parameters.Add(new SQLiteParameter("@TolPlus", DbType.Decimal)); insertCommand.Parameters.Add(new SQLiteParameter("@OutOfTol", DbType.Decimal)); insertCommand.Parameters.Add(new SQLiteParameter("@Comment", DbType.String)); string FileNameExt = Path.GetFileName(file); string RNumber = Path.GetFileNameWithoutExtension(file); string RNumberE = RNumber.Split('_')[0]; string RNumberD = RNumber.Split('_')[1]; string RNumberDate = RNumber.Split('_')[2]; DateTime dateTime = DateTime.ParseExact(RNumberDate, "yyyyMMdd", Thread.CurrentThread.CurrentCulture); string cmmDate = dateTime.ToString("dd-MMM-yyyy"); string[] lines = File.ReadAllLines(file); bool parse = false; foreach (string tmpLine in lines) { string line = tmpLine.Trim(); if (!parse && line.StartsWith("Feat. Type,")) { parse = true; continue; } if (!parse || string.IsNullOrEmpty(line)) { continue; } Console.WriteLine(tmpLine); foreach (SQLiteParameter parameter in insertCommand.Parameters) { parameter.Value = null; } string[] values = line.Split(new[] { ',' }); for (int i = 0; i < values.Length - 1; i++) { SQLiteParameter param = insertCommand.Parameters[i]; if (param.DbType == DbType.Decimal) { decimal value; param.Value = decimal.TryParse(values[i], out value) ? value : 0; } else { param.Value = values[i]; } } insertCommand.Parameters.Add(new SQLiteParameter("@PartNumber", RNumberE)); insertCommand.Parameters.Add(new SQLiteParameter("@CMMNumber", RNumberD)); insertCommand.Parameters.Add(new SQLiteParameter("@Date", cmmDate)); insertCommand.Parameters.Add(new SQLiteParameter("@FileName", FileNameExt)); // insertCommand.ExecuteNonQuery(); } } } Console.WriteLine("CMM data successfully imported to SQL database..."); } con.Close(); }

    Read the article

  • Compiling SQLite into programs that run on Symbian 9.1 and above?

    - by Jared
    How can I use SQLite C API’s on any device running Symbian OS 9.1 or above? While it appears that 9.4 comes with SQLite built in prior versions do not. Looking at http://sourceforge.net/projects/sqlites60/ it appears that sQLite has been ported to older Symbian releases. The only problem is this code assumes you want to use SQLite with .net when I want to compile it into a .sis file and call the C API’s. Ideally I’d compile a Symbian equivalent of amalgamation.c and call the sQLite functions directly but am not sure if this is possible.

    Read the article

  • Canceling in Sqlite

    - by Yusuf
    I am trying to use handle database with insert, update, and delete such as notepad. I'm having problems in canceling data .In normal case which presses the confirm button, it will be saved into sqlite and will be displayed on listview. How can I make cancel event through back key or more button event? I want my Button and back key to cancel data but its keep on saving... public static int numTitle = 1; public static String curDate = ""; private EditText mTitleText; private EditText mBodyText; private Long mRowId; private NotesDbAdapter mDbHelper; private TextView mDateText; private boolean isOnBackeyPressed; public SQLiteDatabase db; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); mDbHelper = new NotesDbAdapter(this); mDbHelper.open(); setContentView(R.layout.note_edit); setTitle(R.string.edit_note); mTitleText = (EditText) findViewById(R.id.etTitle_NE); mBodyText = (EditText) findViewById(R.id.etBody_NE); mDateText = (TextView) findViewById(R.id.tvDate_NE); long msTime = System.currentTimeMillis(); Date curDateTime = new Date(msTime); SimpleDateFormat formatter = new SimpleDateFormat("d'/'M'/'y"); curDate = formatter.format(curDateTime); mDateText.setText("" + curDate); Button confirmButton = (Button) findViewById(R.id.bSave_NE); Button cancelButton = (Button) findViewById(R.id.bCancel_NE); Button deleteButton = (Button) findViewById(R.id.bDelete_NE); mRowId = (savedInstanceState == null) ? null : (Long) savedInstanceState .getSerializable(NotesDbAdapter.KEY_ROWID); if (mRowId == null) { Bundle extras = getIntent().getExtras(); mRowId = extras != null ? extras.getLong(NotesDbAdapter.KEY_ROWID) : null; } populateFields(); confirmButton.setOnClickListener(new View.OnClickListener() { public void onClick(View view) { setResult(RESULT_OK); Toast.makeText(NoteEdit.this, "Saved", Toast.LENGTH_SHORT) .show(); finish(); } }); deleteButton.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub mDbHelper.deleteNote(mRowId); Toast.makeText(NoteEdit.this, "Deleted", Toast.LENGTH_SHORT) .show(); finish(); } }); cancelButton.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub boolean diditwork = true; try { db.beginTransaction(); populateFields(); db.setTransactionSuccessful(); } catch (SQLException e) { diditwork = false; } finally { db.endTransaction(); if (diditwork) { Toast.makeText(NoteEdit.this, "Canceled", Toast.LENGTH_SHORT).show(); } } } }); } private void populateFields() { if (mRowId != null) { Cursor note = mDbHelper.fetchNote(mRowId); startManagingCursor(note); mTitleText.setText(note.getString(note .getColumnIndexOrThrow(NotesDbAdapter.KEY_TITLE))); mBodyText.setText(note.getString(note .getColumnIndexOrThrow(NotesDbAdapter.KEY_BODY))); } } @Override protected void onSaveInstanceState(Bundle outState) { super.onSaveInstanceState(outState); saveState(); outState.putSerializable(NotesDbAdapter.KEY_ROWID, mRowId); } public void onBackPressed() { super.onBackPressed(); isOnBackeyPressed = true; finish(); } @Override protected void onPause() { super.onPause(); if (!isOnBackeyPressed) saveState(); } @Override protected void onResume() { super.onResume(); populateFields(); } private void saveState() { String title = mTitleText.getText().toString(); String body = mBodyText.getText().toString(); if (mRowId == null) { long id = mDbHelper.createNote(title, body, curDate); if (id > 0) { mRowId = id; } } else { mDbHelper.updateNote(mRowId, title, body, curDate); } }`enter code here`

    Read the article

  • How can i clear a SQLite database each time i start my app

    - by AndroidUser99
    Hi, i want that each time i start my app my SQLite database get's cleaned for this, i need to make a method on my class MyDBAdapter.java code examples are welcome, i have no idea how to do it this is the dbadapter/helper i'm using: public class MyDbAdapter { private static final String TAG = "NotesDbAdapter"; private DatabaseHelper mDbHelper; private SQLiteDatabase mDb; private static final String DATABASE_NAME = "gpslocdb"; private static final String PERMISSION_TABLE_CREATE = "CREATE TABLE permission ( fk_email1 varchar, fk_email2 varchar, validated tinyint, hour1 time default '08:00:00', hour2 time default '20:00:00', date1 date, date2 date, weekend tinyint default '0', fk_type varchar, PRIMARY KEY (fk_email1,fk_email2))"; private static final String USER_TABLE_CREATE = "CREATE TABLE user ( email varchar, password varchar, fullName varchar, mobilePhone varchar, mobileOperatingSystem varchar, PRIMARY KEY (email))"; private static final int DATABASE_VERSION = 2; private final Context mCtx; private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(PERMISSION_TABLE_CREATE); db.execSQL(USER_TABLE_CREATE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS user"); db.execSQL("DROP TABLE IF EXISTS permission"); onCreate(db); } } /** * Constructor - takes the context to allow the database to be * opened/created * * @param ctx the Context within which to work */ public MyDbAdapter(Context ctx) { this.mCtx = ctx; } /** * Open the database. If it cannot be opened, try to create a new * instance of the database. If it cannot be created, throw an exception to * signal the failure * * @return this (self reference, allowing this to be chained in an * initialization call) * @throws SQLException if the database could be neither opened or created */ public MyDbAdapter open() throws SQLException { mDbHelper = new DatabaseHelper(mCtx); mDb = mDbHelper.getWritableDatabase(); return this; } public void close() { mDbHelper.close(); } public long createUser(String email, String password, String fullName, String mobilePhone, String mobileOperatingSystem) { ContentValues initialValues = new ContentValues(); initialValues.put("email",email); initialValues.put("password",password); initialValues.put("fullName",fullName); initialValues.put("mobilePhone",mobilePhone); initialValues.put("mobileOperatingSystem",mobileOperatingSystem); return mDb.insert("user", null, initialValues); } public Cursor fetchAllUsers() { return mDb.query("user", new String[] {"email", "password", "fullName", "mobilePhone", "mobileOperatingSystem"}, null, null, null, null, null); } public Cursor fetchUser(String email) throws SQLException { Cursor mCursor = mDb.query(true, "user", new String[] {"email", "password", "fullName", "mobilePhone", "mobileOperatingSystem"} , "email" + "=" + email, null, null, null, null, null); if (mCursor != null) { mCursor.moveToFirst(); } return mCursor; } public List<Friend> retrieveAllUsers() { List <Friend> friends=new ArrayList<Friend>(); Cursor result=fetchAllUsers(); if( result.moveToFirst() ){ do{ //note.getString(note.getColumnIndexOrThrow(NotesDbAdapter.KEY_TITLE))); friends.add(new Friend(result.getString(result.getColumnIndexOrThrow("email")), "","","","")); }while( result.moveToNext() ); } return friends; } }

    Read the article

  • Sqlite returns error

    - by ruruma
    I'm trying to implement loading data from database and put it into different views. But log cat returns error, that it cannot find "_id" column. Can somebody help me with this? SqlHelper Code public class FiboSqlHelper extends SQLiteOpenHelper { public static final String TABLE_FILMDB = "FiboFilmTop250"; public static final String COLUMN_ID = "_id"; private static final String DATABASE_NAME = "FiboFilmDb250.sqlite"; private static final int DATABASE_VERSION = 1; public static final String COLUMN_TITLE = "Title"; public static final String COLUMN_RATING = "Rating"; public static final String COLUMN_GENRE = "Genre"; public static final String COLUMN_TIME = "Time"; public static final String COLUMN_PREMDATE = "PremDate"; public static final String COLUMN_PLOT = "Plot"; private static final String DATABASE_CREATE = "create table "+TABLE_FILMDB+"("+COLUMN_ID +" integer primary key autoincrement, " +COLUMN_TITLE+" text not null "+COLUMN_RATING+" text not null "+COLUMN_GENRE+" text not null "+COLUMN_TIME+" text not null "+COLUMN_PREMDATE+" text not null "+COLUMN_PLOT+" "+"text not null)"; public FiboSqlHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); // TODO Auto-generated constructor stub } @Override public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub db.execSQL(DATABASE_CREATE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub Log.w(FiboSqlHelper.class.getName(), "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); db.execSQL("DROP TABLE IF EXISTS " + TABLE_FILMDB); onCreate(db); } }` SqlAdapterCode: public class FiboSqlAdapter { private SQLiteDatabase database; private FiboSqlHelper dbHelper; private String[] allColumns = {FiboSqlHelper.COLUMN_ID, FiboSqlHelper.COLUMN_TITLE, FiboSqlHelper.COLUMN_GENRE, FiboSqlHelper.COLUMN_PREMDATE, FiboSqlHelper.COLUMN_TIME, FiboSqlHelper.COLUMN_PLOT}; public FiboSqlAdapter (Context context){ dbHelper = new FiboSqlHelper(context); } public void open() throws SQLException { database = dbHelper.getWritableDatabase(); } public void close(){ dbHelper.close(); } public List<FilmDataEntity> getAllFilmData(){ List<FilmDataEntity> fDatas = new ArrayList<FilmDataEntity>(); Cursor cursor = database.query(FiboSqlHelper.TABLE_FILMDB, allColumns, null,null,null,null,null); cursor.moveToFirst(); while(!cursor.isAfterLast()){ FilmDataEntity fData = cursorToData(cursor); fDatas.add(fData); cursor.moveToNext(); } cursor.close(); return fDatas; } private FilmDataEntity cursorToData(Cursor cursor){ FilmDataEntity fData = new FilmDataEntity(); fData.setId(cursor.getLong(1)); fData.setTitle(cursor.getString(2)); fData.setRating(cursor.getString(6)); fData.setGenre(cursor.getString(4)); fData.setPremDate(cursor.getString(5)); fData.setShortcut(cursor.getString(8)); return fData; }} DataEntity: ` public class FilmDataEntity { private long id; private String title; private String rating; private String genre; private String premDate; private String shortcut; public String getShortcut() { return shortcut; } public void setShortcut(String shortcut) { this.shortcut = shortcut; } public String getGenre() { return genre; } public void setGenre(String genre) { this.genre = genre; } public String getPremDate() { return premDate; } public void setPremDate(String premDate) { this.premDate = premDate; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getRating() { return rating; } public void setRating(String rating) { this.rating = rating; } public long getId() { return id; } public void setId(long id) { this.id = id; } } Part from main activity: `List<FilmDataEntity> fE1; sqA = new FiboSqlAdapter(this); sqA.open(); fE1 = sqA.getAllFilmData(); `

    Read the article

  • How do I enable SQLite on Linux/Apache/PHP?

    - by Edward Tanguay
    To enable SQLite on my Windows/Apache/PHP setup, I uncomment the following lines in the php.ini file and restart Apache: extension=php_pdo.dll extension=php_pdo_sqlite.dll Now I have a VPS with Suse Linux 10.3 and Apache/PHP was set automatically. Looking through the php.ini file to make the above changes, I see that things are a little different on Linux: there is no list of extensions to uncomment I found "extension_dir = /usr/lib/php5/extensions" but in this directory find only: pdo.so pdo_mysql.so How can I enable SQLite in this Linux environment?

    Read the article

  • Where should I create my DbCommand instances?

    - by Domenic
    I seemingly have two choices: Make my class implement IDisposable. Create my DbCommand instances as private readonly fields, and in the constructor, add the parameters that they use. Whenever I want to write to the database, bind to these parameters (reusing the same command instances), set the Connection and Transaction properties, then call ExecuteNonQuery. In the Dispose method, call Dispose on each of these fields. Each time I want to write to the database, write using(var cmd = new DbCommand("...", connection, transaction)) around the usage of the command, and add parameters and bind to them every time as well, before calling ExecuteNonQuery. I assume I don't need a new command for each query, just a new command for each time I open the database (right?). Both of these seem somewhat inelegant and possibly incorrect. For #1, it is annoying for my users that I this class is now IDisposable just because I have used a few DbCommands (which should be an implementation detail that they don't care about). I also am somewhat suspicious that keeping a DbCommand instance around might inadvertently lock the database or something? For #2, it feels like I'm doing a lot of work (in terms of .NET objects) each time I want to write to the database, especially with the parameter-adding. It seems like I create the same object every time, which just feels like bad practice. For reference, here is my current code, using #1: using System; using System.Net; using System.Data.SQLite; public class Class1 : IDisposable { private readonly SQLiteCommand updateCookie = new SQLiteCommand("UPDATE moz_cookies SET value = @value, expiry = @expiry, isSecure = @isSecure, isHttpOnly = @isHttpOnly WHERE name = @name AND host = @host AND path = @path"); public Class1() { this.updateCookie.Parameters.AddRange(new[] { new SQLiteParameter("@name"), new SQLiteParameter("@value"), new SQLiteParameter("@host"), new SQLiteParameter("@path"), new SQLiteParameter("@expiry"), new SQLiteParameter("@isSecure"), new SQLiteParameter("@isHttpOnly") }); } private static void BindDbCommandToMozillaCookie(DbCommand command, Cookie cookie) { long expiresSeconds = (long)cookie.Expires.TotalSeconds; command.Parameters["@name"].Value = cookie.Name; command.Parameters["@value"].Value = cookie.Value; command.Parameters["@host"].Value = cookie.Domain; command.Parameters["@path"].Value = cookie.Path; command.Parameters["@expiry"].Value = expiresSeconds; command.Parameters["@isSecure"].Value = cookie.Secure; command.Parameters["@isHttpOnly"].Value = cookie.HttpOnly; } public void WriteCurrentCookiesToMozillaBasedBrowserSqlite(string databaseFilename) { using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + databaseFilename)) { connection.Open(); using (SQLiteTransaction transaction = connection.BeginTransaction()) { this.updateCookie.Connection = connection; this.updateCookie.Transaction = transaction; foreach (Cookie cookie in SomeOtherClass.GetCookieArray()) { Class1.BindDbCommandToMozillaCookie(this.updateCookie, cookie); this.updateCookie.ExecuteNonQuery(); } transaction.Commit(); } } } #region IDisposable implementation protected virtual void Dispose(bool disposing) { if (!this.disposed && disposing) { this.updateCookie.Dispose(); } this.disposed = true; } public void Dispose() { this.Dispose(true); GC.SuppressFinalize(this); } ~Class1() { this.Dispose(false); } private bool disposed; #endregion }

    Read the article

< Previous Page | 14 15 16 17 18 19 20 21 22 23 24 25  | Next Page >