JSON Array Loop Inserting into Android SQLite Database

[viviosoft]

Hello All -

I have the following snippet that loops through a JSONArray and inserts the data into a database. My problem is that it's not inserting all the records. If I have 6 records it will only inserts 5 of the 6. If I have 5 records it will only insert 4? When I debug the storesArray.length it shows the total records in the array as 6. What am I doing wrong that it won't loop and insert all the records in the array? Thanks for any help you can provide me.

Here's the JSON data that's coming in from the server:

[{"id":"29","cId":"3","sName":"Milford Plaza Hotel","sNumber":"3061","sCustNm":"VS7997","sSalesman":"1234","sAddress":"","sContact":"","sPhone":""},{"id":"26","cId":"7","sName":"Circle K Bowling Green","sNumber":"5670","sCustNm":"VS7976","sSalesman":"1234","sAddress":"1091 N Main Street\r\nBowling Green, Ohio 43402","sContact":"","sPhone":""},{"id":"25","cId":"7","sName":"Circle K Bowling Green","sNumber":"5669","sCustNm":"VS7975","sSalesman":"1234","sAddress":"966 S Main Street\r\nBowling Green, OH 43402","sContact":"","sPhone":""},{"id":"22","cId":"6","sName":"Rock Branch Exxon","sNumber":"122","sCustNm":"VS7983","sSalesman":"1234","sAddress":"Rt 1 Box 34 A\r\nPoca, W.V.","sContact":"","sPhone":""},{"id":"23","cId":"6","sName":"Marathon Food Mart","sNumber":"156","sCustNm":"VS7984","sSalesman":"1234","sAddress":"2300 Pike Street\r\nParkerburg, W.V.","sContact":"Jenny","sPhone":""},{"id":"27","cId":"7","sName":"Circle K Dayton","sNumber":"5346","sCustNm":"VS7992","sSalesman":"1234","sAddress":"816 Waterveilt Ave\r\nDayton, OH 45420","sContact":"","sPhone":""}]

Here's the loop to insert the data into the SQLite database:

    		JSONArray storesArray = new JSONArray(restults);
    		
	        for(int i = 0; i < storesArray.length(); i++){
					        	
				JSONObject store = storesArray.getJSONObject(i);
				
				db.execSQL("INSERT INTO stores ( sId, cId, sName, sAddress, sNumber, sSalesman, sCustNum, sPhone ) " +
					    "VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )",
					    new Object [] {
					        store.getString("id"),
					        store.getString("cId"),
					        store.getString("sName"),
					        store.getString("sAddress"),
					        store.getString("sNumber"),
					        store.getString("sSalesman"),
					        store.getString("sCustNm"),
					        store.getString("sPhone")
					        }
							);
				
	        }

[viviosoft]

Really? No one can tell me why this might be happening?

[eRaaaa]

I get six records....
Try to print out the JSONObject (with store.getString..) instead of saving in database.
The JSON string, the loop and the using of the library looks right for me.

[Tolls]

If this is the case then the problem is in some code we can't see.
A commit that's not happening possibly?
Who knows?

[viviosoft]

Tolls is right. My fault guys. It's actually inserting the correct amount of records into the SQLite database. The problem is reading in the data from the SQLite database after it's been inserted. Here's how I have it stepping through each row in the table. Not sure if this is the most effective way. Well I'm guessing it's not because I'm only getting back 5 of the six records? Sorry for not being more thorough in my debugging ;(

So here's where my problem REALLY is:

    	Cursor c = db.rawQuery("SELECT * FROM stores WHERE sSalesman="+RepID+"", null);
	c.moveToFirst();
	
        ArrayList<HashMap<String, String>> storeList = new ArrayList<HashMap<String, String>>();

        try {
			        	
        	while (c.moveToNext()) {
        		
			HashMap<String, String> map = new HashMap<String, String>();	
				
			map.put("sId",  c.getString(c.getColumnIndex("sId")));
	        	map.put("sName", c.getString(c.getColumnIndex("sName")));
	        	map.put("sAddress", c.getString(c.getColumnIndex("sAddress")));
	        	map.put("sNumber", "St #: " + c.getString(c.getColumnIndex("sNumber")));
	        	storeList.add(map);	
	        	
         } catch(Exception e) {
        	 Log.e("ListStores", "Error: "+e);
         }


        	db.close();
                c.close();

[viviosoft]

Well, I got it figured out. Here's how I changed the logic to step through the table data. If someone has a better way of handling this, please let me know. I'm learning here ;) Thanks for the direction guys. It was a help.

    	Cursor c = db.rawQuery("SELECT * FROM stores WHERE sSalesman="+RepID+"", null);

		c.moveToFirst();

        	while (c.isAfterLast() == false) {
        						
        		HashMap<String, String> map = new HashMap<String, String>();	

        		map.put("sId",  c.getString(c.getColumnIndex("sId")));
	        	map.put("sName", c.getString(c.getColumnIndex("sName")));
	        	map.put("sAddress", c.getString(c.getColumnIndex("sAddress")));
	        	map.put("sNumber", "St #: " + c.getString(c.getColumnIndex("sNumber")));
	        	storeList.add(map);
	        	
	        	c.moveToNext();
	        	
		}

[Tolls]

Not knowing what framework you're using for your db stuff (it isn't JDBC for example), it's hard to say...but I would say you ought to be binding that query.
Something like "SELECT * FROM stores WHERE sSalesman = ?", then set the repID when you run the query.