Results 1 to 7 of 7
  1. #1
    viviosoft is offline Member
    Join Date
    Sep 2011
    Posts
    7
    Rep Power
    0

    Question JSON Array Loop Inserting into Android SQLite Database

    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:
    Java Code:
    [{"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:

    Java Code:
        		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")
    					        }
    							);
    				
    	        }

  2. #2
    viviosoft is offline Member
    Join Date
    Sep 2011
    Posts
    7
    Rep Power
    0

    Default Re: JSON Array Loop Inserting into Android SQLite Database

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

  3. #3
    eRaaaa is offline Senior Member
    Join Date
    Oct 2010
    Location
    Germany
    Posts
    787
    Rep Power
    6

    Default Re: JSON Array Loop Inserting into Android SQLite Database

    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.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,091
    Rep Power
    20

    Default Re: JSON Array Loop Inserting into Android SQLite Database

    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?

  5. #5
    viviosoft is offline Member
    Join Date
    Sep 2011
    Posts
    7
    Rep Power
    0

    Default Re: JSON Array Loop Inserting into Android SQLite Database

    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:

    Java Code:
        	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();
    Last edited by viviosoft; 09-23-2011 at 10:40 PM.

  6. #6
    viviosoft is offline Member
    Join Date
    Sep 2011
    Posts
    7
    Rep Power
    0

    Default Re: JSON Array Loop Inserting into Android SQLite Database

    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.

    Java Code:
        	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();
    	        	
    		}

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,091
    Rep Power
    20

    Default Re: JSON Array Loop Inserting into Android SQLite Database

    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.

Similar Threads

  1. Android sqlite: not found
    By dewitrydan in forum New To Java
    Replies: 1
    Last Post: 09-15-2010, 11:39 PM
  2. Replies: 2
    Last Post: 09-06-2010, 01:03 AM
  3. Replies: 12
    Last Post: 08-26-2010, 10:14 AM
  4. Replies: 1
    Last Post: 12-18-2009, 01:16 AM
  5. Inserting file in to database
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-07-2008, 08:39 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •