0

I have a table in Sqlite which contains three components for each record (eg A, ​​B, C), and the primary key (id) is random. I would like a SQL query that extracts each record sequentially, as if you were applying a loop to the database.

I know there is a clause like LIMIT but it only returns the first element (LIMIT 1) instead, two elements (LIMIT 2) ... but I want to extract a record, and process it and move on to another. Recalling that the id is random.

halfer
  • 19,824
  • 17
  • 99
  • 186
user1547027
  • 13
  • 1
  • 6
  • I you working with MySQL or SQLite? I assume SQLite based on the Android tag. You should certainly clarify. Also seems like you may not fully understand how SQL works. If you want ALL the records, then you shouldn't be using a LIMIT statement. – Mike Brant Jul 24 '12 at 19:10
  • I'm not a Java user, but in general with SQL databases, you can run the query, and then loop through the rows one at a time. If you want to _extract a record, and process it and move on to another_ then it sounds like you're worried about memory consumption - so long as you loop through and don't store a lot of data per record, you should be okay. – halfer Jul 24 '12 at 19:17
  • You can use [OFFSET as well](http://stackoverflow.com/q/3325515/472495), in case you didn't already know. – halfer Jul 24 '12 at 19:19
  • Hello, I should just pull out a record that contains a photo that needs processing, then move on to another! Only having an id random and not sequential I do not know how to write the query as I do? – user1547027 Jul 24 '12 at 19:40

2 Answers2

0

It is not ideal to write the queries by hand as methods have been provided to generate safe queries.

database.query(DATABASE_TABLE, new String[] {ROW_ID, ROW_COLUMN_ONE, ROW_COLUMN_TWO, ... }, null, null, null, null, new String[] {ROW_ID});

This will return all rows in your database ordered by the ROW_ID column.

This site might be of further use as it covers some common use cases for SQLite implementation. http://www.vogella.com/articles/AndroidSQLite/article.html

ian.shaun.thomas
  • 3,468
  • 25
  • 40
0

What kind of processing?

If you want to update or alter the data in some way, you can probably do it within SQL, but it sounds like you have something more complicated in mind that just "UPDATE table SET value_column = value WHERE key_column = key".

If that's the case you will need to extract the records and iterate through them in java. Take a look at android.database.*.

Eric deRiel
  • 420
  • 3
  • 14
  • Hello, I should just pull out a record that contains a photo that needs processing, then move on to another! Only having an id random and not sequential I do not know how to write the query as I do? – user1547027 Jul 24 '12 at 19:41
  • SELECT * FROM table_name ORDER BY id; – Eric deRiel Jul 24 '12 at 19:45
  • so if I have a database with a table populated with 5 records this query returns me the first record, then the second ... up to 5? a bit like to do a "for loop" on table? – user1547027 Jul 24 '12 at 19:52
  • No, that SELECT statement will return all 5, in order from lowest id to highest. Is there a problem with that? Is the binary data of the photo embedded in the result set? Are you trying to just go through the photos 1 at a time? – Eric deRiel Jul 24 '12 at 21:13