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
The table "credentials" does show up in the adb shell.
I've checked logcat and it doesn't seem to report a problem...
CODE:........
I've been pouring over this and I bet its some silly syntax typo! Or, at least I hope it is something trivial.
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:.................
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:...................
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.
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?
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?
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);
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.
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.
I have decided that I hate SQL and for that purpose I am trying to make a helper class that I can reuse over and over and never have to mess with it again but it isnt working!
Here is what i have right now:
CODE:.........
I have included several System.out.println() statements to help me debug the code and find problems so please ignore them. PrintDB is another test that I wrote that I am using to make sure everything is working. Now this is what I have written to make sure everything is working....
CODE:.....................
Running this code everything goes great and all the System.out.println() statements have the right information in them until it gets to the databaseView.setText(db.printTable(tableName));
part and it throws an exception
CODE:................
This has me so confused because that same line of code is used in the getColNames() method that was called several times before this and ran with no problem. Also if the table didn't exist wouldn't it have thrown an exception when I called insertRow()? I called that method 5 times in a row with not a single exception thrown! What is happening here?
Implement onCreate() in your DBOpenHelper. Put there content of the createTable() method.
Why would it be necessary to create the table in the onCreate() method? If I were to do this would I be able to use my existing method to do so and just call it from this method?
And the second why you don't use content providers and uri >concept which is already SQLless?
This is my code used to create table code...
View 1 Replies View RelatedIn my game I am handling the loss of a character (represented programmatically with their own table) - however there are 4 such tables. I would like to know if there is a simple way to simply tell SQLite to make character 2's entire table equal character 3's? Just trying to not leave a gap in the lineup.
I was considering pulling all the data, sorting it out, putting it into the table and then deleting - I just wanted to know if there was a nifty shortcut to tell SQLite that I wanted to duplicate an entire table at another preexisting table?
http://defiledroid.wordpress.com/
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.
How do i efficiently copy a row data from one table to another table, where both of them are the same structure. i could go the much harder way of retrieving initial values from the row in the first table and then inserting to the second table. But i feel there is a more efficient way this can be done.
View 1 Replies View RelatedContext Menu seems quite handy as long as we are using an AdapterView as we can easily get selected item position in onContextItemSelected from AdapterContextMenuInfo.position Any idea how we can achieve something similar in a TableLayout? In my TableLayout, i have some TableRow and i would like to get the row index in the onContextItemSelected callback like i would do with a simple ListView. I guess i will have to register the contextmenu for each row? but how can tie the row index with the menu? i see no way to do it with registerForContextMenu.
View 2 Replies View RelatedThe following function is giving me an out of bounds exception...
public void count(){
SQLiteDatabase db = table.getWritableDatabase();
String count = "SELECT count(*) FROM table";
Cursor mcursor = db.rawQuery(count, null);
int icount = mcursor.getInt(0);
System.out.println("NUMBER IN DB: " + icount);}
It's meant to return the number of rows in the database. Anybody know whats wrong? am I perhaps doing this task the wrong way?
i'm trying to access (read & write) from non-default (example 2nd table) table of a sqlite database which i created using sqlite database browser. but somehow, i cant seems to find the way to access it.
Code:
public class DatabaseHelper extends SQLiteOpenHelper {
private static String DB_PATH = "/data/data/com.sg/databases/";
private static String DB_NAME = "TestDatabase";
private SQLiteDatabase myDatabase;
[code]...
select data method works, but i can only select from table 1 and not table 2
In my project, I would like to create a database with single table. I would also like to insert data on this table from word or excel document. is it possible? if so please how can I do it? some code snippets would be of great help. I have four different activities which I would like them to interact with this single table. the first is the main activity and its purpose is just to launch the other three activities according to user choice. so it has no interaction with the DB. but the other three activities will read from the table whenever called. Can you please tell me how to call the dbhelper on these activities? I am unable to do this and I am currently creating one db per each activity which is not the optimal way.
View 1 Replies View RelatedI've a problem with a query in sqlite3.
The query is very simple:
SELECT MAX (INIT_DATE) INIT_DATE FROM TABLE;
When my application execute this query, and the table is empty, a record is returned. I can't understand why a record is being returned if the table is empty. It should return cero results.
I've tried to execute the query in two different ways:
cursor = db.query(true, "TABLE", new String[] {"MAX(INIT_DATE) AS INIT_DATE"}, null, null, null, null, null, null);
cursor = db.rawQuery("SELECT MAX(INIT_DATE) AS INIT_DATE FROM TABLE", null);
I am using sqlite database in andriod. i m not creating any database i m using created database and it has successfully opened but when i am using:
Cursor cursor= db.query(tablename, null, null, null, null, null, null);
Then it gives this table does not exists plz help me.i got frustated with this problem. i also googled this problem but didnt find exact solution.
I've created a custom view in an SQLite database for an Android application. I'm using Sqliteman on Ubuntu to test my SQL statements before I put them in my app. I'm trying to do a simple select statement on my view. The select statement works fine in SQLiteman but when I put the same statement in my code it throws an error. The statement: select * from item_view where parent_item_id = 0;........
View 2 Replies View RelatedIs there an equivalent view structure to the iPhone default table cell? The default table cell formats an image (icon) and text in a nice looking way. Are there suggested equivalents for Android? Is there a sample somewhere?
I want to create say 5 different types of cells in table along with identifiers and load them appropriately as per the given data depending upon the type?
Creating TableRow inside TableLayout seems to be one of the options but how to dynamically create the tableRows depending upon the type?
Here is my screen description: It is a TableLayout and it contains multiple Rows. I have set TableRow as clickable, as I want to go to next screen on click of a tableRow. Everything I am doing through Java programming (instead of XML layout, because number of TableRows changes each time) On next screen, I wanted to display all the views of that particular clicked TableRow. Here the problem i am facing is how to capture particular tableRow on onclick() even and how do I get all the textviews of that particular Row. I tried to set id at runtime, and tried to get id of view, but it's not working, giving error resourcenotfoundexception.
View 2 Replies View RelatedI have a table layout with a few predefined rows in it. Each row has only two columns.
Now I need to dynamically add additional rows in it.
I have two problems:
1) When programmatically inflating, I cannot set the index of the newly created row (I want it in place 'n') 2) After inflation, the inserted (actually appended) row doesn't listen to parent table's stretch column property.
So here are my questions:
q1) Can I set the place where to insert the inflated row programatically ?
q2) Why doesn't the new row inflate properly (the second column is not shown because the first column doesn't contains a TextView with fill_parent.
In the end I need the first column to occupy 80% of the screen width, and the second column remaining 20%.
q3) is that doable with programmatic row insertions ?
I want to create a self contained library that I can easily drop into an android project and run.I can do this by creating a shared object via the NDK and JNI but the limitation there seems to be lack of audio support which is important for this library.Is there a way to create a library in the java/android world? Ideally the developer could just drop it into the project and could access methods via headers maybe?Any help/samples would be fantastic and appreciated.
View 3 Replies View RelatedWhen a button is clicked, the following method is run:
public void createTableRow(View v) {
TableLayout tl = (TableLayout) findViewById(R.id.spreadsheet);
TableRow tr = new TableRow(this);
LayoutParams lp = new LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT);
tr.setLayoutParams(lp);
TextView tvLeft = new TextView(this);
tvLeft.setLayoutParams(lp);......................
R.id.spreadsheet is an xml TableLayout. I can see from debugging that the method is being accessed, but nothing is drawn to the screen. What gives? Do I need to reset the Content View somehow?
I am trying to display two TextViews in two separate line. For that, I am using RelativeLayout. I don't want to use XML for layout as I want to bundle this Activity in a jar file which may be used by different APKs. The TextViews are being overlapped instead of being displayed on two separate lines. Any idea what I am doing wrong here? Sample code is below.
View 2 Replies View RelatedI have an ImageView that contains a bitmap. Now the bitmap can change size within a known range. This makes the surrounding views to relocate which I want to prevent. I've tried setting margins and padding for the ImageView but without success.
View 1 Replies View Related