10 Replies Latest reply on Aug 16, 2016 6:58 AM by kerrishotts

    retrieve single row from server and store it in sqlite

    sawank18707653 Level 1

      i am building cordova app and i want to retrieve single row from postgreSQl server and store that row in my phone local storage..how to do it also how can i restrict only one rows should be in sqlite if other comes previous get deleted automatically

        • 1. Re: retrieve single row from server and store it in sqlite
          VectorP Level 4

          1. You retrieve a row from postgreSQL by

          - posting an ajax request to your web service,

          - which will fetch and return the row data, preferably as a json object

           

          2. You will make sure that only one row exists by defining a primary key (unique) and setting a fixed value for that key field.

          Then use the 'INSERT OR REPLACE' query.

          SQLite Query Language: INSERT

           

          BTW: I am not sure why you want to use Sqlite locally at all, if you only want to store one single tuple. What purpose would such database serve, if you have no relationships, no select criteria, etc.?

          • 2. Re: retrieve single row from server and store it in sqlite
            sawank18707653 Level 1

            please tell me how to do it by codes.

            • 3. Re: retrieve single row from server and store it in sqlite
              VectorP Level 4

              Which part is giving you trouble?

              - the ajax code for a request to your web service

              - the whitelisting for the above request

              - the web service itself

              - processing a returned json object

              - the local sqlite query (or localStorage alternative)

              • 4. Re: retrieve single row from server and store it in sqlite
                sawank18707653 Level 1

                in my code i have retrieved data from server and printed it in my page via getJSON object..but unable to store same data in sqlite 

                • 5. Re: retrieve single row from server and store it in sqlite
                  VectorP Level 4

                  Some questions about "not being able to store in sqlite":

                  - which plugin are you using?

                  - when you try storing it in sqlite, do you receive any error messages?

                  - wat is the sql query you were using when you attempted to store the data?

                  - why must it be sqlite at all? How are you going to retrieve the data?

                  • 6. Re: retrieve single row from server and store it in sqlite
                    sawank18707653 Level 1

                    - which plugin are you using?

                    Ans :- i am using cordova-sqlite-plugin

                     

                    - when you try storing it in sqlite, do you receive any error messages?

                    Ans :- i dont know ho to store data, as i am getting data in json . code is below

                      

                       var url="URL.php";

                      $.getJSON(url,function(result)

                      {

                         console.log(result);

                        $.each(result, function(i, field)

                          {

                             window.name=field.name;

                    });

                      });

                     

                    - wat is the sql query you were using when you attempted to store the data?

                    Ans : No one, dont know what to use.

                     

                     

                    - why must it be sqlite at all? How are you going to retrieve the data?

                    Ans : so that i can use data offline too.

                    • 7. Re: retrieve single row from server and store it in sqlite
                      VectorP Level 4

                      Well, you only have one row of data at a time in your 'database' locally, with no relationship to other tuples or tables, no statistics, etc.

                      Since you need to be able to maintain your app as well, it would not be a good idea to just copy some code from someone else and hope for the best in the future, especialy if alternatives are available.

                      For instance, you could serialize the json object and store it in localStorage. Can be retrieved directly from there. Another advantage would be that this would erase the overhead of a database layer in your application.

                       

                      Some articles claim, that iOS could erase localStorage in extreme situations, when disk space gets low. Even if this extreme situation occurs, your app shouldn't be helpless, since you would have to cater for such situation, anyway (at least the first time when using the app after installation).

                       

                      So, I would suggest not to use sqlite locally, and store the data in localStorage (which is just a permanent associative array) only.

                      That said, your query would be

                       

                      INSERT OR REPLACE INTO table-name

                      VALUES (0, data.field1, data.field2 data.field3, ...)

                       

                      ...where the 0 is the unique value for the primary key, and data.fieldX are the values from your json object, assuming that there is exactly one value for each defined table colomn

                      2 people found this helpful
                      • 8. Re: retrieve single row from server and store it in sqlite
                        sawank18707653 Level 1

                        what is data.field ..as per my understanding field(column name of table) and data( is a variable)

                        • 9. Re: retrieve single row from server and store it in sqlite
                          VectorP Level 4

                          data.fieldX:

                          - assuming 'data' is the root element of your json object

                          - assuming 'fieldX' are the names of the json object items, used as variables for the values to be stored in the table.

                           

                          Do you really, really want to use an sqlite table to store one single row?

                          • 10. Re: retrieve single row from server and store it in sqlite
                            kerrishotts Adobe Community Professional

                            Totally agree with @VectorP -- SQLite is not an appropriate storage mechanism for a single row of data. LocalStorage is always a little risky on iOS -- but as long as you have an easy way to recreate the local data, that's not a big problem. If that's a concern, alternatives exist, including the File API, or if this relates to app settings, there are plugins that can key into the platform's native application preferences (see cordova-plugin-app-preferences for one example).

                             

                            Regardless of whether you use localStorage or not, don't use SQLite for single-tuple storage. Doing so will complicate your app unnecessarily and add dependencies and code that you don't need. Keep it simple.