Not sure how to add record if not exists jaydata sqllite

Question

I am trying to accomplish the following task. I have a json array returned from a remote database and I want to iterate over it, check if a record with the ID of the object exists in the local database. If exists, I want to update the record, if it doesn't I want to attach it. The code is goes as follows:

$.each(data, function(idx, task) { 
                        var taskToUpdate = $org.context.Task.attachOrGet({ Id:task.TaskId});                        
                        taskToUpdate.TaskType = task.TaskType;
                        taskToUpdate.StatusId = task.TaskStatusId;
                        taskToUpdate.TaskStatus = task.TaskStatus;
                        taskToUpdate.DateScheduled = task.Date;
                        taskToUpdate.TimeSlot = task.Time;
                        taskToUpdate.LastUpdated = new Date();
                        taskToUpdate.TaskName = "Job " + task.TaskId + " " + task.TaskType + " @" + task.AddressOfTask + ", " + task.PropertyPostCode;
                        taskToUpdate.SpecialInstructions = task.SpecialInstructions;
                        taskToUpdate.PropertyAddress = task.AddressOfTask;
                        taskToUpdate.PropertyPostCode = task.PropertyPostCode;
                        taskToUpdate.PropertyType = task.PropertyType;
                        taskToUpdate.NumberOfBedrooms = task.NumberOfBedrooms;
                        taskToUpdate.HasGarage = task.HasGarage;
                        taskToUpdate.HasOutHouse = task.HasOutHouse;


                    });

                    $org.context.saveChanges({
                        success: function(db) {
                            that.messages.push("Tasks saved to local device.");
                        }, error: function(err) {
                            console.log(err);
                            that.messages.push("Errors saving tasks: " + err);
                            navigator.notification.alert("Error saving local tasks to your device!",
                                                         function () {
                                                         }, "Error", 'OK');
                        }
                    });

The code executes successfully but no records are added to the task table.

Am I missing something?

score 1 · Accepted Answer · answered Sep 26 '13 at 11:18

I went with this code, which seems to work but just doesn't feel "right". Namely the way I detect whether or not the updates have finished to avoid calling context.savechanges() more than one. Please feel free to improve my answer!

function downloadTasksFromWeb(viewModel){
    $org.context.UserSetting.first().then(function (userSetting) {
                viewModel.set("currentUserSettings", userSetting);          

                backofficeUrl = viewModel.get("currentUserSettings.BackOfficeUrl") + "/api/tasks";
                var operatorId = viewModel.get("currentUserSettings.OperatorId");

                var rowsToProcess = 0, rowsProcessed = 0;

                viewModel.messages.push("Connecting to server.");
                showNotificationInfo("Connecting to server.");

                jQuery.ajax({
                    type: "GET",
                    url: backofficeUrl,
                    dataType: 'json',
                    async: false,
                    username: "user",
                    password: "pw",
                    data: {"operatorId": operatorId},
                    success: function (data) {
                        viewModel.messages.push("Tasks received, saving to local device.");
                        showNotificationInfo("Tasks received, saving to local device.");
                        rowsToProcess = data.length;
                        $.each(data, function(idx, task) { 
                            var existingTasks = $org.context.Task.filter("Id", "==", task.TaskId).toArray();

                            existingTasks.then(function(result) {
                                var taskToUpdate = $org.context.Task.attachOrGet({ Id:task.TaskId});

                                taskToUpdate.TaskType = task.TaskType;
                                taskToUpdate.StatusId = task.TaskStatusId;
                                taskToUpdate.TaskStatus = task.TaskStatus;
                                taskToUpdate.DateScheduled = task.Date;
                                taskToUpdate.TimeSlot = task.Time;
                                taskToUpdate.LastUpdated = new Date();
                                taskToUpdate.TaskName = "Job " + task.TaskId + " " + task.TaskType + " @" + task.AddressOfTask + ", " + task.PropertyPostCode;
                                taskToUpdate.SpecialInstructions = task.SpecialInstructions;
                                taskToUpdate.PropertyAddress = task.AddressOfTask;
                                taskToUpdate.PropertyPostCode = task.PropertyPostCode;
                                taskToUpdate.PropertyType = task.PropertyType;
                                taskToUpdate.NumberOfBedrooms = task.NumberOfBedrooms;
                                taskToUpdate.HasGarage = task.HasGarage;
                                taskToUpdate.HasOutHouse = task.HasOutHouse;

                                if (result.length == 0) {
                                    $org.context.Task.add(taskToUpdate);
                                }

                                rowsProcessed++;

                                if (rowsProcessed == rowsToProcess) {
                                    $org.context.saveChanges({
                                        success: function(db) {
                                            viewModel.messages.push("Tasks saved to local device.");
                                            showNotificationInfo("Tasks saved to local device.");
                                        }, error: function(err) {
                                            console.log(err);
                                            viewModel.messages.push("Errors saving tasks: " + err);
                                            showNotificationError("Errors saving tasks: " + err);                                            
                                        }
                                    });  
                                }
                            });
                        });
                    }
                }).fail(function(resultData) {
                    showNotificationError("There was an error communicating with the server.  Please check your settings and try again.");

                });
            });
}

Jaydata is promise based so you can easily use Q or jQuery's deferred. http://jaydata.org/blog/how-to-work-with-the-jaydata-promise-interfaces — George Antoniadis, Feb 22 '14 at 18:24

score 0 · Answer 2 · answered Sep 25 '13 at 10:02

0

Instead of the $.each you need a recursive function with the following algorithm: - check if there is any record saved to the local DB with the particular ID

if yes
- attach
- set the properties
if no
- create a new typed element entity
- set the properties
- add the entity to the collection - context.Tasks.add(newEntity)
after all your code finished, call context.saveChanges() to persist all the changes in a batch. This is much faster and safe than your code that calls the saveChanes() inside the foreach.

The recursive function is necessary because of the async behavior.

answered Sep 25 '13 at 10:02

Robesz

1,646
11
13

Thanks for the response, sorry I wasn't clear, the data comes from the remote server so the save changes is outside the look. However the real promble is there doesn't seem to be an easy "if row exists" function in jaydata, so I don't know in advance if it is new or exists. Do I have to use a simple filter.count() to check if the row is there already? – Dave Stringer Sep 25 '13 at 13:06
Yes, you can get use count() or length() context.Tasks.filter('it.Task_ID == 140').count(function(count) {console.log(count)}); – Robesz Sep 26 '13 at 06:23
Thanks. Count seems to throw an error if no row is present though, which while the jaydata people say you can ignore it, it's not nice to see errors in your console log... I've taken the approach in my answer below... It's not perfect but seems to work.. what do you think? – Dave Stringer Sep 26 '13 at 11:20
There shouldn't be a error in the console log, I just checked it with WebSQL, IndexedDB, localStore and OData providers. Share your code and I check it. – Robesz Sep 26 '13 at 11:52

Not sure how to add record if not exists jaydata sqllite

2 Answers2