Android :: Reference Sqlite Db Column To Use In Update Statement
May 8, 2010
I am trying to update a datetime column in an android sqlite db to use international date format (yyyy-mm-dd) instead of the current format (mm/dd/yyyy). I want to use the sqlite date() function to reformat the current value of the column. I thought it would be as simple as the following: update tblename set thedate = date(thedate)
But the above does not work.
How would i write the sql statement to accomplish this?
View 1 Replies
Jul 23, 2010
I'm developing an application that tracks the user's current position and stores it into a SQLite database. Everything works fine, but now I have the problem when querying the database for a track with more than 1000 records it takes nearly 1.5 minutes. On my Desktop it just takes 1 second. I know it's a query with many subselects but I wasn't able to get the right result another way. In my opinion this belongs to the aggregate functions like avg() and sum().
Here's my query:.................
View 3 Replies
View Related
Mar 23, 2010
I have a query that selects rows in a ListView without having a limit. But now that i have implemented a SharedPreferences that the user can select how much rows will be displayed in the ListView, my SQLite query doesnt work. Im passing the argument this way:
CODE:...................
View 1 Replies
View Related
Aug 13, 2010
When creating my SQLite database in Android I set the database locale - db.setLocale(new Locale("cz_CZ")). This is a Czech locale. A SELECT statement works and takes the locale into account, for example:
SELECT * from table WHERE name='sctzy' COLLATE LOCALIZED
But using LIKE will fail: SELECT * from table WHERE name LIKE '%sctzy%' COLLATE LOCALIZED
BTW. There is no java.text.Normalized class in Android. I thought I could make a second column with a normalized text, stripped of special characters, which would be used for searching - but I am missing a class or way how to normalize the String.
View 1 Replies
View Related
Apr 25, 2009
Anyone working with big sqlite tables and trying to match rows using patterns (for example for autocomplete purposes) ? recently i played with LIKE statement and it seems it will never use table index (if any), you have to use GLOB instead. but....... the weird thing is that you cannot use selectionParams in your query: you have to specify your pattern in selection like:
CODE:........
My words table has two TEXT columns: 'key' and 'value' and index on 'key'
For example: i have table with ~28000 rows and when using selectionParams my query took 1500 ms and without selectionParams 20 ms does it have anything to do with android or its sqlite weird feature?
View 2 Replies
View Related
May 11, 2012
I'm trying to select the items that have a expiration date >= that an selected date.I'm using the format yyyy-mm-dd, and this is my sql statement:
String sql = "SELECT _id, item, value, finalDate FROM "+DATABASE_TABLE+" WHERE date(finalDate) >= date("+selectedDate+")";
This query always result a "full select" in my database, as if there were no conditions.Some useful information:
selectDate is a String which have a date in yyyy-mm-dd format
I'm executing the query like this:
return db.rawQuery(sql,null);
View 3 Replies
View Related
Oct 28, 2009
For my application, I am trying to add entries without having a duplicate entry, and if there are a duplicate notify the user and have him try again. Using SQLite, which I know very very little about, I have tried these two ways and want to know which one is better, more efficient or a better way to develop it? First way:
db.execSQL("INSERT INTO " + DatabaseHelper.DATABASE_TABLE +
"(LATITUDE, LONGITUDE, RATING) SELECT " + latitude + ", " + longitude + ", " + rating +
" WHERE NOT EXISTS (SELECT 1 FROM " + DatabaseHelper.DATABASE_TABLE +
" WHERE LATITUDE = " + latitude + " AND LONGITUDE = " + longitude + ")");
Second way:
long id = -1;
try { id = db.compileStatement(
"SELECT COUNT(*) FROM " + DatabaseHelper.DATABASE_TABLE
+ " WHERE LATITUDE = " + latitude
+ " AND LONGITUDE = " + longitude)
.simpleQueryForLong();
} catch (NullPointerException e) { return -1;
} return id;
The first way, either inserts or ignores the values, I check by doing so, storing the row-count of the rows into a variable before the call, call that function, and then check its results. If the results is not higher than it was before the call, the insert was made ignored, prompt the user with the 'values exist' message. (Very sloppy, I know but desperate times calls for desperate measures). The second way, returns the actual count of rows that match the numbers I want to store, if the number returned greater than 1, prompt the user with the 'values exist' message. I have been going back and forth, trying different ways but I do not know how to set up SQLite to have UNIQUE pairs, which I was told would be the easiest.
View 3 Replies
View Related
Aug 8, 2010
I have a database with tables called box, item, and box_contents and I want to get any item that is contained in the box_content table (all items, but discard the ones that arent in the box_contents table). What would be the correct sqllite syntax for this?
View 2 Replies
View Related
Sep 25, 2010
I have a table called "images":
CODE:.........
On inserting a row, I'd like the URL column to be unique. At the same time, I'd like to find out the id of the row with that URL.
INSERT OR IGNORE images (url, caption) VALUES ("http://stackoverflow.com", "A logo"); SELECT last_insert_rowid(); -- returns the id iff there was an insert.
Of course, I'd like to do it as quickly as possible, though my first thought was along the the lines of the following pseudo code:
CODE:...........
But this seems hopelessly inefficient.
What is the most performant way of getting the Auto incremented row id from an INSERT OR IGNORE statement.
View 1 Replies
View Related
Jun 21, 2009
I am trying to utilize what has allready been written for this, in my own database
I will actually post my code so someone may be able to pick out what is wrong
This is the error i get: 06-21 19:57:35.511: ERROR/AndroidRuntime(796): Caused by:
code:................
This is the method its faulting on in Class:
code:..............................
View 2 Replies
View Related
May 4, 2010
Can I create index on a column in the create table command in sqlite?I tried this command below in sqlite3 in Android shell. It seems to work.But, in sqlite's query language specification, specifying a column to be indexed is not supported.
View 1 Replies
View Related
Oct 12, 2009
I have a database that I download external and save on my sdcard. But this database is also used for an iPhone app and has a column id and not _id (sqlite uses _id). How can i rename this column to _id?
View 4 Replies
View Related
Oct 7, 2009
I have a TEXT column and I want to match all rows where the value has a certain prefix. For example, WHERE mycol LIKE 'myprefix%' However, I noticed in some optimization guidelines that this will not use the index and instead you should do this:WHERE mycol >= 'myprefix' AND mycol < 'myprefiy'Instead of simple A-Z chars, I have a whole variety of UTF-8 chars (think chinese characters).My question is, how to work out the next char in UTF-8? Is it enough to add one to the codepoint,
View 3 Replies
View Related
Apr 20, 2010
So I have an app on the market, and with an update I want to add some columns to the database. No problems so far. But I want to detect if the database in use is missing these columns, and add them if this is the case. I need this to be done dynamically and not just after the update to the new version, because the application is supposed to still be able to import older databases. Normally I would be able to use the PRAGMA query, but Im not sure how to do this with Android. I cant use execSQL since it is a query, and I cant figure out how to use PRAGMA with the query()-function.
Ofcourse I could just catch exceptions and then add the column, or always add the columns to each table before I start to work with it, but that is not a neat solution.
View 4 Replies
View Related
Jun 23, 2010
I'm working on an Android App where the user has different options for sorting the displayed data that comes from the database. Currently my orderBy string that I pass to Androids query() method looks like this:
"LOWER("+columnName+") ASC"
The problem with this is that if the data type in the column specified by columnName is integer, calling LOWER() on it will cause it to be sorted alphabetically, i.e. based only on the leftmost digit, which of course doesn't make any sense for numeric data. Hence I only want to apply LOWER() if the data type of the column is not integer. What I have in mind is a statement like this:
"CASE WHEN [data type of columnName is integer] THEN "+columnName+" ASC ELSE LOWER("+columName+") ASC END"
The part in the brackets is what I don't know how to do. Does SQLite provide a function to determine a column's data type?
View 3 Replies
View Related
Nov 10, 2010
I'm attempting to create a method that will allow me to retrieve a value from the last row of a database, and then insert it into an EditText field. (This is a value that the user will not change all that often, so it would be helpful if when they do set it, it stays set when they come back to it).
My method, based on a similar method I have for getting the total of a specific column, is as such. code...
If I run the program without this statement, it works fine and the value saves to the db. As soon as I try to run it with this, I get a force close. Any suggestions?
View 2 Replies
View Related
May 8, 2013
I am developing an application in android allowing the user to maintain his weight by providing him diet suggestions. I have done with that part and now, I need to display user weight tracking details using bar graph. I am able to fetch the Weight and Login date column data from the sqlite database to List<String>. I don't understand the way to put these values into a Bar Graph with Date Column data as X-axis and Weight column data as Y-axis.
View 1 Replies
View Related
Aug 10, 2010
See title for explanation.
Here is the method I'm using:
CODE:.........
And here is the call to the method:
CODE:...........
Why it would be throwing the "No Column Found with name=Test", although my DB explorer shows there is indeed a column named name and a value in a row named Test?
View 1 Replies
View Related
Jul 14, 2010
I created a sql lite database with the following columns:
static final String dbName="demoDB";
static final String tableName="Employees";
static final String colID="EmployeeID";
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE "+tableName+" ("+colID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
colName+" TEXT, "+colAge+" Integer);");
I want to select all the records in the database like this and display them in a gridview:SQLiteDatabase db=this.getWritableDatabase();Cursor cur= db.rawQuery("Select "+colName+", "+colAge+" from "+tableName, new String [] {});String [] from=new String []{DatabaseHelper.colName,DatabaseHelper.colAge};
int [] to=new int [] {R.id.colName,R.id.colAge};
SimpleCursorAdapter sca=new SimpleCursorAdapter(this,R.layout.gridrow,c,from,to);
GridView grid=(GridView)findViewById(R.id.grid);
grid.setAdapter(sca);but i receive the following exception:java.lang.IllegalArgumentException: column '_id' does not exist.the db table does not have a column with name '_id'so what is wrong with this code
View 3 Replies
View Related
Jul 16, 2010
This has got me all confused! I'm trying to return the max value from a column in my database but the return value is always the name of the column.
The query I use:
private static final String SELECTMAX = "SELECT MAX(?) FROM " + TABLE_NAME ;
The (test) function to return the max value:
CODE:............
The column i'm querying is an INTEGER type but the result 's' is always the column name and not the desired value.
View 2 Replies
View Related
Jan 25, 2010
Is possible update the records from SQLite from my Android Application1 from other Application2 Android
View 4 Replies
View Related
Feb 16, 2010
I have a strange issue. I am new to java/android but come from a programming background maybe I am just missing or doing something stupid with syntax here. I have a string variable called reportlength
String reportlength;
I then go: reportlength = intent.getExtras().getString("ReportLengt");
Now in my debug console for the variable reportlength it has it set as "d" (that is the correct value it should have)
At this point I have my if statement:
CODE:......................
View 3 Replies
View Related
Jul 20, 2010
So I've got this code (updated for solution).
CODE:.............
The problem I was having was that one of the TextViews would turn grey when it wasn't supposed to. I tried fixing it by always setting the text to black again, but that turned every single one of them grey.
Until I tried:
CODE:................
Don't know why the latter works when setting text grey, but there it is. I guess I'm just a little retarded.
View 7 Replies
View Related
Jan 26, 2010
How can I enable assert statement on Android? I have referred to a url: http://android.git.kernel.org/?p=platform/dalvik.git;a=blob_plain;f=d... and done what it said: adb shell setprop dalvik.vm.enableassertions all but nothing happen! The test code is: Log.v("assert 1","1"); assert false; Log.v("assert 2","2"); No matter what I did, "assert 2" will be printed, which means "assert false" does nothing! What should I do? BTW, I use eclipse as IDE.
View 7 Replies
View Related
Sep 27, 2010
I'm trying to reduce the number of queries I do to the Android's database. Basically I want to get any email or IM address that contains a user defined search term. Right now I'm doing two separate queries:
Email:...............
View 3 Replies
View Related
Mar 1, 2010
I know the error will probably be painfully obvious to you but using cursor.getCount() on the result of the following returns a 0 when there are multiple items in the table and trips the error "Invalid statement in fillWindow()".
CODE:...............
View 3 Replies
View Related
Jul 15, 2010
CODE:...........................
View 1 Replies
View Related
Jul 28, 2010
I'm getting unexpected behavior in my Android 1.5 application under the Windows emulator and debugging with Eclipse. Here's a generalization of what the code is doing:
if (someCondition) {
System.out.println("got here");
return "a";
}
if (someOtherCondition)....................
View 2 Replies
View Related
Aug 25, 2010
I have a central activity that can be launched from two separate classes. I was hoping that in this central activity I could have an IF statement like
if(this.getIntent() == MainMenu.class)
{
// Do something here
}
But obviously that isn't legal so how could I structure an expression to check from what class an intent was started.
View 2 Replies
View Related
Nov 16, 2010
i sometimes see this error in my logcat output, Cursor: invalid statement in fillWindow(). it sometimes happens when i press the back key and then it goes to the default android listview before going to my custom listview. what does it mean? how do i solve it? Because it does not point to any line of code where the problem is coming from?
View 1 Replies
View Related
