Problem with Using Web SQL Database from Android App

Showing 1-11 of 11 messages
Problem with Using Web SQL Database from Android App Darshana 2/9/12 10:42 AM
Hello

I have a small application that consists of 3 pages. Currently I have
put all of then in the sdcard and accessing them through the native
browser.
1. syncer.html - Reads tables from the server url and replicates the
same on the local web sql database
2. capture.html - a data capture form that needs to work in cases
where no internet connection is available. It uses database created by
syncer.html to populate its form fields. All records entered in the
form needs to be saved in another local web sql database and then sent
to the server when internet connex is available
3. uploader.html - reads data collected stored in local database and
sends it to server

All these pages were tested on Google Chrome and the native browser
provided by the Android phone.
When I tried to compile using PhoneGap. syncer.html works perfectly
fine. capture.html is able to populate its fields correctly implying
that the FIRST local database is being initialised properly.

HOWEVER, when I try to openDatabase(,,) another database so as to put
in records being captured, I get an error.. the openDatabase does not
return the connection..
The same codes works fine as direct web pages. Can someone please help
me lest I am doing something wrong?

Attached code snippets:
<!DOCTYPE HTML>
<html>
<head>
<meta name="viewport" content="height=device-height; width=device-
width; user-scalable=0;" />

        <script src="jquery-1.6.2.js"></script>
        <script type="text/javascript">
<!--
                $(document).ready(function () {

                        lat=0;lng=0;

                        //you may substitute null by function to handle errors
                        navigator.geolocation.getCurrentPosition(function
storePosition(position) {
                                                        //localStorage.setItem('longitude',position.coords.longitude);
                                                        //localStorage.setItem('latitude',position.coords.latitude);
                                                        //alert("Your position is:    Lat: "+position.coords.latitude
+"      Longitude:"+position.coords.longitude);

                                                        //NOTE use of global variable for latitude and longitude
                                                        lat=position.coords.latitude;
                                                        lng=position.coords.longitude;
                                                        }
                                                        ,null,{'enableHighAccuracy':true,'timeout':15000,'maximumAge':
0}
                                                        );

                        //note global variable here
                        var currentTime = new Date();
                        month = currentTime.getMonth()+1;
                        year=currentTime.getFullYear();
                        //alert(currentTime+ " - "+ month+ "-"+year);

                        document.getElementById("m").innerHTML="Month: "+month;
                        document.getElementById("y").innerHTML="Year: "+year;

                        //note that db is a global variable here
                        db = openDatabase('dictionary_db', '1.0', 'Dictionary Listing',
5*1024*1024);
                        if (!db)
                                { alert("Failed to connect to database."); }
                        else
                                {
                                        db.transaction(function (tx)
                                                {
                                                //populate drop down for crops
                                                tx.executeSql('SELECT * FROM dictionary_crops', [], function
(tx, results) {
                                                                                        var len = results.rows.length; var i=0;        var
txt="";
                                                                                        for (i = 0; i < len; i++)
                                                                                                {
                                                                                                        txt=txt+"<option value="+results.rows.item(i).cropid
+">"+results.rows.item(i).cropname+"</option>";
                                                                                                }
        
document.getElementById("cropid").innerHTML=txt;}
                                                                                        ,null);


                                                //populate drop down for producers
                                                tx.executeSql('SELECT * FROM dictionary_producers', [], function
(tx, results) {
                                                                                        var len = results.rows.length; var i=0;        var
txt="";
                                                                                        for (i = 0; i < len; i++)
                                                                                                {
                                                                                                        txt=txt+"<option
value="+results.rows.item(i).producer_code
+">"+results.rows.item(i).producer_type+"</option>";
                                                                                                }
                                                                                        document.getElementById("producer_code").innerHTML=txt;
                                                                                        },         null);
                                                });
                                }

                        });





        function save_obs()
        {
                // fetching global var here - month,year,lat,lng        ;zone can be
derived from locality code on serverside
                var locality=Number(document.getElementById("locality").value);
                var
producer=Number(document.getElementById("producer_code").value);
                var cropid=Number(document.getElementById("cropid").value);
                var AP=Number(document.getElementById("AP").value);
                var
interline=Number(document.getElementById("interline").value);
                var remarks=String(document.getElementById("remarks").value);




                //enumcode functionality from login missing as well
                //update insert queries for enumcode running below

                //check for successful insert and display confirmation to user
                // ask for confirmation before commiting the record to server?



//ERROR _ THIS DOES NOT WORK -  I get "Failed to connect to database"

                        var transac_db = openDatabase('np_transac_db', '1.0', 'New
Plantation Upload',5*1024*1024);
                        if (!transac_db)
                                { alert("Failed to connect to database."); return false;}
                        else
                                {
                                        transac_db.transaction(function (tx)
                                        {
                                                tx.executeSql('CREATE TABLE IF NOT EXISTS
np_local(month,year,locality,producer,crop,AP,interline,remarks,lat,lng,enumcode,time_captured)');

                                                //insert obs row in the table
                                                tx.executeSql("INSERT INTO
np_local(month,year,locality,producer,crop,AP,interline,remarks,lat,lng,time_captured)
VALUES (?,?,?,?,?,?,?,?,?,?,?);",
[month,year,locality,producer,cropid,AP,interline,remarks,lat,lng,timestamp],insert_success,insert_error);
                                        });

                                }
        }

        function insert_success()
        {
                alert("Observation was successfully inserted in database..");
        }

        function insert_error()
        {
                alert("An error occurred while trying to insert the observation in
the database. Please try again and make a manual recording in case
error persists. "); return false;
        }



Thanks a lot
Re: Problem with Using Web SQL Database from Android App Mikel 2/10/12 5:11 AM
I have never tried open a database the way that you are:
var transac_db = openDatabase('np_transac_db', '1.0', 'New Plantation
Upload',5*1024*1024);

But I wonder have you tried to set a more simple size for it rather
than using that calculation?
var transac_db = openDatabase('np_transac_db', '1.0', 'New Plantation
Upload',2000);

That may be perfectly valid, I have no idea, but I just thought that I
would suggest making is simpler to see if that helps.
> np_local(month,year,locality,producer,crop,AP,interline,remarks,lat,lng,enu mcode,time_captured)');
Re: [PhoneGap] Problem with Using Web SQL Database from Android App tiptronic 2/10/12 6:02 AM
HI Darshana,

I don't see where 'save_obs' is called... maybe inside a transaction?

> var transac_db = openDatabase('np_transac_db', '1.0', 'New Plantation
> Upload',5*1024*1024);


@Mikel: Initializing a database like this is a very common way to accurately adjust the size (here 5MB)

andy

On 10.02.2012, at 14:11, Mikel wrote:

> I have never tried open a database the way that you are:
> var transac_db = openDatabase('np_transac_db', '1.0', 'New Plantation
> Upload',5*1024*1024);
>
> But I wonder have you tried to set a more simple size for it rather
> than using that calculation?
> var transac_db = openDatabase('np_transac_db', '1.0', 'New Plantation
> Upload',2000);
>
> That may be perfectly valid, I have no idea, but I just thought that I
> would suggest making is simpler to see if that helps.


Re: Problem with Using Web SQL Database from Android App Darshana 2/10/12 10:47 AM
Hi Mikel

Andy is right. I did try putting 3000 and 5000 but does not change
anything..
Any other suggestions?
Re: Problem with Using Web SQL Database from Android App Darshana 2/10/12 10:53 AM
Hi Andy

Find complete codes for np_capture.html which basically uses a
dictionary_db to populate its form elements and then each new
observation/record added is saved into another database np_transac_db.

Save_obs() is called following an onclick event on button.. These
codes work perfectly fine on Google Chrome.. No issues with handling
more than 1 database.. Its for my final year project. I thought
compiling would have had been an easy task :) I guess the issue is
with working with more than 1 database at a time. Can you help me out
with this?


<!DOCTYPE HTML>
<html>
<head>
<meta name="viewport" content="height=device-height; width=device-
width; user-scalable=1;" />

        <script src="../jquery-1.6.2.js"></script>
        function load_locality(selected_zone)
        {

                if (!db)
                        { alert("Failed to connect to database."); }
                else
                {
                        db.transaction(function (tx)
                        {
                                //populate drop down for localities
                                tx.executeSql('SELECT id,locality_name FROM dictionary_localities
where zone=?', [selected_zone], function (tx, results) {
                                        var len = results.rows.length; var i=0;        var
txt="";
                                        for (i = 0; i < len; i++)
                                        {
                                                txt=txt+"<option value="+results.rows.item(i).id
+">"+results.rows.item(i).locality_name+"</option>";
                                        }
                                        document.getElementById("locality").innerHTML=txt;
                                        },         null);
                        });
                }
        }


        function new_obs()
        {
                document.getElementById("zone").options[0].selected = true;
                document.getElementById("locality").innerHTML='<option value="0">
--- </option>';
                document.getElementById("producer_code").options[0].selected = true;
                document.getElementById("cropid").options[0].selected = true;

                document.getElementById("AP").value="";
                document.getElementById("interline").value="";
                document.getElementById("remarks").value="";
        }

        function save_obs()
        {
                // fetching global var here - month,year,lat,lng
                var locality=Number(document.getElementById("locality").value);
                var
producer=Number(document.getElementById("producer_code").value);
                var cropid=Number(document.getElementById("cropid").value);
                var AP=Number(document.getElementById("AP").value);
                var
interline=Number(document.getElementById("interline").value);
                var remarks=String(document.getElementById("remarks").value);


                //check if locality has been selected
                if (locality==0) {alert("You need to select a locality name. Please
select a zone to get a list of localities."); return false;}

                if ((AP==0) || (isNaN(AP))) {alert("Please ensure that you have
entered a numeric value for Area Planted(ha)"); return false;}

                if (isNaN(interline)) {alert("Please ensure that you have entered a
numeric value for Interline Ratio"); return false;}


                //timestamp
                var d=new Date();
                var iyear=d.getFullYear();
                var imonth=d.getMonth()+1;
                var iday=d.getDate();

                var timestamp=iyear+"-"+imonth+"-"+iday+" "+d.toLocaleTimeString();


                        var transac_db = openDatabase('np_transac_db', '1.0', 'New
Plantation Upload',5*1024*1024);
                        if (!transac_db)
                                { alert("Failed to connect to database."); return false;}
                        else
                                {
                                        transac_db.transaction(function (tx)
                                        {
                                                tx.executeSql('CREATE TABLE IF NOT EXISTS np_local(record_no
INTEGER PRIMARY
KEY,month,year,locality,producer,crop,AP,interline,remarks,lat,lng,enumcode,time_captured)');

                                                //insert obs row in the table
                                                tx.executeSql("INSERT INTO
np_local(record_no,month,year,locality,producer,crop,AP,interline,remarks,lat,lng,time_captured)
VALUES (NULL,?,?,?,?,?,?,?,?,?,?,?);",
[month,year,locality,producer,cropid,AP,interline,remarks,lat,lng,timestamp],insert_success,insert_error);
                                        });

                                }
        }

        function insert_success()
        {
                alert("Observation was successfully inserted in database..");
        }

        function insert_error()
        {
                alert("An error occurred while trying to insert the observation in
the database. Please try again and make a manual recording in case
error persists. "); return false;
        }


-->
</script>
</head>

 <body onload="window.scrollTo(0, 1);">


<form id="frm_uploader">
<table border=0 cellpadding=3> <caption><u> New Plantation - Data
Capture </u></caption>

<tr><th colspan=2 align=left><a href="index.html">Back</a>&nbsp;</th></
tr>

<tr><td id="m">Month:</td>        <td id="y">Year:</td></tr>


<tr><td>Zone:</td> <td>
                <select name="zone" id="zone"
onchange="load_locality(this.value);">
                <option value="0"> --- </option>

                <option value="N1">N1</option>
                <option value="N2">N2</option>
                <option value="N3">N3</option>
                <option value="N4">N4</option>

                <option value="E1">E1</option>
                <option value="E2">E2</option>
                <option value="E3">E3</option>
                <option value="E4">E4</option>

                <option value="S1">S1</option>
                <option value="S2">S2</option>
                <option value="S3">S3</option>
                <option value="S4">S4</option>

                <option value="CW1">CW1</option>
                <option value="CW2">CW2</option>
                <option value="CW3">CW3</option>

                <option value="DA">DA</option>
                </select>
</td></tr>

<tr> <td colspan=2><hr></td></tr>

<tr><td>Locality:</td> <td> <div id="locality_div"><select
name="locality" id="locality"> <option value="0">- - -</option> </
select> </div> </td></tr>

<tr><td>Producer:</td><td><select name="producer_code"
id="producer_code" ><option value="0"> --- </option> </select> </td></
tr>


<tr><td>Crop:</td><td><select name="cropid" id="cropid"> <option
value="0">- - -</option></select> </td></tr>

<tr><td>Area Planted(ha):</td><td><input type=textbox name="AP"
id="AP" size=10></td></tr>
<tr><td>Interline:</td><td><input type=textbox name="interline"
id="interline" size=10></td></tr>
<tr><td>Remarks:</td><td><input type=textbox name="remarks"
id="remarks" size=30></td></tr>


<tr>
        <td colspan=2 align=center><button type="button"
onclick="new_obs();">New Observation</button>
        <button type="button" onclick="new_obs();">Cancel</button>
        <button type="button" onclick="save_obs();">Save Observation</
button></td>

</tr>
<tr><td colspan=2 align=center><div id="display"></div></td></tr>
</table>


</form>

</body>
</html>
Re: [PhoneGap] Re: Problem with Using Web SQL Database from Android App tiptronic 2/10/12 11:22 AM
Hi Darshana,

code looks ok, b u t

could it simply be that you get an exception because of the device's local storage limit?

What if you change the database settings to something like 1*1024*1024 (for BOTH databases)? Does this do the trick?

andy


Am 10.02.2012 um 19:53 schrieb Darshana:

> --
> You received this message because you are subscribed to the Google
> Groups "phonegap" group.
> To post to this group, send email to phon...@googlegroups.com
> To unsubscribe from this group, send email to
> phonegap+u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/phonegap?hl=en?hl=en
>
> For more info on PhoneGap or to download the code go to www.phonegap.com
>


Re: [PhoneGap] Re: Problem with Using Web SQL Database from Android App tiptronic 2/10/12 11:25 AM
...here's the specs:

http://dev.w3.org/html5/webstorage/#disk-space

a.

Re: Problem with Using Web SQL Database from Android App Darshana 2/10/12 11:26 AM
I came across this thread http://groups.google.com/group/phonegap/browse_thread/thread/67ca07103ebf8712
and shifted from PHONE GAP 1.3.0 to PHONE GAP 1.4.1, Still the same..

What does "Have to proxy
openDatabase and check at runtime. " mean?

Thanks,
Darshana
Re: Problem with Using Web SQL Database from Android App Darshana 2/10/12 8:36 PM
you are a G.E.N.I.U.S :)))))))
it works! I simply changed everything to be 1*1024*1024

so I guess I got it completely wrong by thinking that each individual
database can have 5 MB.. it is actually for a particular origin, 5MB
is being allocated as a whole right?

HOWEVER,even if I was exceeding the allocated size of 5MB, from some
posts I read, I expected the phone to ask me whether I wanted to
allocate more space to web dbs. is this not the way it should have
been?

Thanks a lot Andy :)

But just a small thing now, through PhoneGap I cannot find the SQLite
db listed anywhere.. are javascript codes the only way to access the
database? Because I wanted to flush the first database. In other
cases, I used to just go to browser settings and clear the cache so as
to clear the data. Any idea?

Darshana
Re: Problem with Using Web SQL Database from Android App Darshana 2/11/12 9:23 AM

Shouted Hola too early.. :((. the app is giving my a dynamic
behaviour.. sometimes it allows me to pass on 1*1024*1024 for both
databases
and then in current case, I am being forced not to exceed 200 BYTES
for dictionary and 300 000 BYTES for transac dbs... this will be too
less for the quantity of data :S

I even reset my phone and then loaded the app.. I have 122MB of
internal storage as free... I am at a lost, because from posts I read,
when android sees that an app needs more memory, it automatically
gives some more space..

Thanks for the help
Re: Problem with Using Web SQL Database from Android App Babu K 2/12/12 9:54 PM
Hi Darshana,
        Am new to Phonegap...still am creating database i got error
and database is changing the values ...so please help me...send me
some sample applications