3 Replies Latest reply on Oct 11, 2012 7:08 PM by decostj

    Using named parameters with an sql UPDATE statement

    decostj

      I am trying to write a simple? application on my Windows 7 PC using HTML, Javascript and Sqlite.  I have created a database with a 3 row table and pre-populated it with data.  I have written an HTML data entry form for modifying the data and am able to open the database and populate the form.  I am having trouble with my UPDATE function.  The current version of the function will saves the entry in the last row of the HTML table into the first two rows of the Sqlite data table -- but I'm so worn out on this that I can't tell if it is accidental or the clue I need to fix it.

       

      This is the full contents of the function . . .

       

           updateData = new air.SQLStatement();

           updateData.sqlConnection = conn;

           updateData.text = "UPDATE tablename SET Gsts = "Gsts, Gwid = :Gwid, GTitle = :GTitle WHERE GId = ":GId;

                var x = document.getElementsById("formname");

                for (var i = 1, row, row = x.rows[i]; i++) {

                     updateData.parameters[":GId"] = 1;

                     for (var j = 0, col; col=row.cells[j]; j++) {

                          switch(j) {

                               case 0: updateData.parameters[":GTitle"] = col.firstChild.value; break;

                               case 1: updateData.parameters[":Gsts"] = col.firstChild.value; break;

                               case 2: updateData.parameters[":Gwid"] = col.firstChild.value; break;

                               }

                          }

                     }

       

      Note: When I inspect the contents of the col.firstChild.value cases, they show the proper data as entered in the form -- it just isn't being updated into the proper rows of the Sqlite table.

       

      Am I using the named parameters correctly? I couldn't find much information on the proper use of parameters in an UPDATE statement.

        • 1. Re: Using named parameters with an sql UPDATE statement
          adobe_paul Adobe Employee

          I'm not sure whether this is just an error in copying/retyping your code in the forum, but this line definitely shouldn't work:

           

          updateData.text = "UPDATE tablename SET Gsts = "Gsts, Gwid = :Gwid, GTitle = :GTitle WHERE GId = ":GId

           

          The problem is with the placement of the quotation marks. I assume the line should be something like this:

          updateData.text = "UPDATE tablename SET Gsts = :Gsts, Gwid = :Gwid, GTitle = :GTitle WHERE GId = :GId";

           

          Another problem might be that you are using the same :GId value each time in the loop, rather than varying it, because this line uses "1" (maybe it should be "i" or else a reference to something in the table?):

           

          updateData.parameters[":GId"] = 1;

           

          It's also not clear why your i iterator starts at 1 rather than 0 -- that would normally mean you're skipping the first row (maybe there's a header row or something?)

          • 2. Re: Using named parameters with an sql UPDATE statement
            decostj Level 1

            Thank you for the notes.  Yes, the misplaced quotes were typos.  I'm handtyping a truncated version of the function so I don't put too much info in the post. And yes, i = 1 'cuz the first rows of the table are titles.  So the current frustration is that I seem to be assigning all the right data to the right parameters but nothing is saving to the database.

             

            I declare updateData as a variable at the top of the script file

             

            Then I start a function for updating the data which establishes the sql connection as shown above.

            The correctly typed.text statement is:

             

                    updateData.text = "UPDATE tablename SET Gsts=:Gsts, Gwid=:Gwid, GTitle=:GTitle WHERE GId=:GId";

             

            (The data I'm saving is entered in text boxes inside table cells.) And the current version of the loop is:

             

                    myTable = document.getElementById("GaugeSts");

                    myRows= myTable.rows;

             

                      for(i=1, i<myRows.length, i++) {

                           updateData.parameters[":GId"]=i;

                           for(j=0; j<myRows(i).cells.length, j++) {

                                switch(y) {

                                     case 0: updateData.parameters[":GTitle"]=myrows[i].cells[y].firstChild.value; break;

                                     case 1: updateData.parameters[":Gsts"]=myrows[i].cells[y].firstChild.value; break;

                                     case 2: updateData.parameters[":Gwid"]=myrows[i].cells[y].firstChild.value; break;

                                                   }

                                          }

                                     updateData.execute;

                                }

             

            The whole thing runs without error and when I include the statements to check what's in myrows[i].cells[y].firstChild.value, I'm seeing that the correct data is being picked up for assignment to the parameters and the update. I haven't been able to double check that the contents of the parameters are actually taking the data 'cuz I don't know how to extract the data from the parameters. ( The only reference  I've found so far has said that it is not possible. I'm still researching that one.) I've also tried moving the position of the updateData execution statement to several different locations in the loop andstill nothing updates. 

             

            I am using a combination of air.Introspector.Console.log to check the results of code and I'm using Firefox's SQlite manager to handle the database.  The database is currently sitting on the Desktop to facilitate testing and I have successfully updated/changed data in this table through the SQLite Manager so I don't think I'm having permission errors and, see below, I have another function successfully saving data to another table.

             

            I currently have another function that uses ? for the parametersin the .text of a INSERT/REPLACE statement and that one works fine.  But, only one line of data is being saved so there is no loop involved.  I tried changing the UPDATE statement in this function to the INSERT/REPLACE just to make something save back to the database but I can't make that one work either.I've a (And, I've tried so many things now, I don't even remember what actually saved something --albeit incorrectly --to the database in one of my previous iterations with the for loops.)

             

            I'm currently poring through Sqlite and Javascript tomes to see if I can find what's missing but if anything else jumps out at you with the corrected code, I'd appreciate some ideas.

             

            Jeane

            • 3. Re: Using named parameters with an sql UPDATE statement
              decostj Level 1

              I found it. I'm relieved but also horrified that it was as simple as leaving the opening and closing parenthesis off of the execute statement. 

               

              updateData.execute;

              should have been

              updateData.execute();