Databases
StackOverflow2013
Postsdbo
POAndroid 2.3.3 SQLite infinite loop
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAndroid 2.3.3 SQLite infinite loop
    primarykey
    data
    text
    <p>In my project, I have 5 tables: Category, CategoryHasProduct, Product, StoreHasProduct and Store. There I store all my products, in which categories they are and in which store which product is available.</p> <p>Now, I want to know how many products in all the categories are available, depending on which store is selected. For performance reasons, I want to use a SQL query for this. The query is</p> <pre><code>SELECT count(*), cp.category_id FROM categoryhasproduct cp, product p, storehasproduct sp WHERE p.sku = cp.product_id AND sp.product_id = p.sku AND sp.store_id = 2 AND sp.isActive = 1 GROUP BY cp.category_id </code></pre> <p>In the Project I use ORMLite. But to query this, I do a basic db-query</p> <pre><code>String query = "..."; SQLiteDatabase db = DBConnector.getConnector(context).getWritableDatabase(); Cursor cursor = db.rawQuery(query, null); </code></pre> <p>Now I have a problem, which makes no sense to me. On Android 4.0.3, everything works fine. The query takes something like 0.5 seconds to run and I can proceed. But when I try to execute the query on Android 2.3.3, the query itself seems to run into an infinite loop. Even after 20 minutes, the query is not finished. On a real device, the processor of the device is getting very hot, so it shuts himself down.</p> <p>As I tried a little bit with the query, it seems that it is to large for sqlite on 2.3.3-devices. the concatenation with the last storehaseproduct-table seems to enlarge the query to much. I also tried other statements like nested queries or joins, but it seems that they have the same problem.</p> <p>Does anybody else has observed such a strange behavior of sqlite? And the much more important question: what can I do to make this query work on Android 2.3.3 with a near equal runtime?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJames Green
    1. USJörg Kiesewetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POAndroid 2.3.3 SQLite infinite loop
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAndroid 2.3.3 SQLite infinite loop
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POAndroid 2.3.3 SQLite infinite loop
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COHey, welcome to Stack Overflow :-) Your query looks fine to me, I'm afraid, but hopefully someone'll be along who knows the differences in situation between sqlite on 2.3.3 and on 4.0.3...
      singulars
      1. POAndroid 2.3.3 SQLite infinite loop
      1. USJames Green
    2. CORoughly how many rows have the tables `categoryhasproduct`, `product` and `storehasproduct`? Does the part of the where clause `sp.store_id = 2 AND sp.isActive = 1` reduce this number for the last table significantly? If so, maybe creating an intermediate temporary table with only the relevant rows and columns of `storehasproduct` may help.
      singulars
      1. POAndroid 2.3.3 SQLite infinite loop
      1. USMichael Butscher
    3. COSince I had also troubles with performing raw queries using SQLiteDatabase on older Android versions due to faulty transaction concurrency handling by the driver (it can just deadlock indefinitely), I want to ask you how you are performing your queries? You need to be careful from which thread you open the database and start a transaction and from which thread you query the database. That can cause troubles. If in doubt just try calling db.setLockingEnabled(false) after opening the connection (method is deprecated by now, but it still works for 2.3.3).
      singulars
      1. POAndroid 2.3.3 SQLite infinite loop
      1. UStiguchi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload