5 Replies Latest reply on Apr 3, 2008 11:51 AM by enorton@adobe

    Error using SQL data in javascript vars

    pawzlion
      I have a javascript routine which queries sqlite for a series of top-level categories, then, for each category, executes another query, using an item ID which is a result of the first query as follows:

      // Get first level of categories table
      var stmt = new air.SQLStatement();
      stmt.sqlConnection = conn;
      stmt.text = "SELECT * FROM categories WHERE childOf IS NULL ORDER BY Name";
      stmt.execute();
      var result = stmt.getResult();
      var numResults = result.data.length;
      for (i = 0; i < numResults; i++) {
      var item = result.data ;
      air.trace('Cat: '+item.Name);
      // Get sub-level categories
      var stmt2 = new air.SQLStatement();
      stmt2.sqlConnection = conn;
      stmt2.text = "SELECT * FROM categories WHERE childOf = :itemID";
      stmt2.parameters[":itemID"] = item.ID;
      stmt2.execute();
      var result2 = stmt2.getResult();
      var numResults2 = result2.data.length;
      for (j = 0; j < numResults2; j++) {
      var inner = result2.data[j];
      air.trace('Cat: '+item.Name);
      }
      }

      The problem is, when I try to assign the value of item.ID to the SQL parameter in the statement:
      stmt2.parameters[":itemID"] = item.ID;
      It outputs the error: "TypeError: Null value"
      I can print the value of item.ID to the screen, or write it to the terminal with air.trace and see the value fine, but when I try to assign it as an sql statement parameter, it gives the error.
      If I manually assign item.ID a value, the statements work fine. If I put a fixed value in instead of the variable substitution, it also works fine. Only when I try to use the result of the first SQL query in a second SQL query does it fail with this error.

      Can anybody please explain why ?
        • 1. Re: Error using SQL data in javascript vars
          pawzlion Level 1
          Still no bites ? This sucks. My AIR development has come to a standstill due to this problem.
          It seems that data retrieved from SQL has some sort of odd data type or something which prevents it being used as input for a second SQL statement.
          WHY is this ? It makes no sense. I can view the data ok, it looks fine, but I can't use it in a subsequent SQL statement without generating a TypeError.

          I'm going out of my mind trying to work out what's wrong !
          • 2. Error using SQL data in javascript vars
            enorton@adobe Level 1
            Hi pawzlion,

            Is that your actual code? Cause
            var item = result.data;
            should be
            var item = result.data[counter];

            I think?

            If that's not the problem, could you post the CREATE statement for the "categories" table? Is the database generated with AIR?

            -Erica

            hmm looks like my open-square-bracket 'i' close-square-bracket after result.data is not getting rendered in the post, so I guess the same is happening for the initial post

            changed 'i' to 'counter' for rendering fix
            • 3. Re: Error using SQL data in javascript vars
              jasowill
              Try assigning the results of item.ID to a temporary variable first, and then assign it to the parameter value. It is possible that we have a bug in the bridge between ActionScript and JavaScript that is preventing the value from being assigned directly.
              • 4. Error using SQL data in javascript vars
                pawzlion Level 1
                Thanks for the suggestion jasowill, but unfortunately this doesn't fix the problem.
                I can assign the data to a temporary variable and then print that variable and everything LOOKS right, but again, it still can't be used as an sql parameter. I've also tried tactics such as subtracting zero from the value to convert the type from string to integer but again, no luck.

                What it comes down to is; the value of the variable looks fine when you print it to the screen or write it to the console with trace, but using it as an sql parameter gives a TypeError every time. It's as though the data is in some weird foreign type or something.

                In response to enorton, yes I am using:
                var item = result.data (square bracket) i (square bracket)
                but the forums are filtering this bit of the code out as I believe it is an italic code. As you can see, in the second variable assignment, it uses [j] in the same way as you would expect.

                Yes, the database is generated by AIR. I'm not at home so I don't have access to paste my create statements, but fwiw, the field in question is an INT. I know the SQL is not at fault though, as if I manually assign a value like so:
                stmt2.parameters[":itemID"] = 5;
                it works fine. Also, manually assigning a value to item.ID like so:
                item.ID = 5;
                stmt2.parameters[":itemID"] = item.ID;

                also works fine. It's only when the value of item.ID has been pulled from SQL prior does it generate this TypeError.

                Really appreciate the comments and assistance !

                • 5. Re: Error using SQL data in javascript vars
                  enorton@adobe Level 1
                  Hi pawzlion,

                  I think the type error you are seeing isn't with this line:
                  stmt2.parameters[":itemID"] = item.ID;
                  but this one:
                  var numResults2 = result2.data.length;

                  What are you doing to guarantee that you are looping over a SQLResult that isn't null? Does every Category have a Subcategory?

                  I modified the code a little and am not reproducing any error.

                  // Get first level of categories table
                  var stmt = new air.SQLStatement();
                  stmt.sqlConnection = conn;
                  stmt.text = "SELECT * FROM categories WHERE childOf IS NULL ORDER BY Name";
                  stmt.execute();
                  var result = stmt.getResult();
                  if (!(result.data===null)) { // *******************
                  var numResults = result.data.length;
                  for (i = 0; i < numResults; i++) {
                  var item = result.data;
                  air.trace('Cat: '+item.Name);
                  // Get sub-level categories
                  var stmt2 = new air.SQLStatement();
                  stmt2.sqlConnection = conn;
                  stmt2.text = "SELECT * FROM categories WHERE childOf = :itemID";
                  stmt2.parameters[":itemID"] = item.ID;
                  stmt2.execute();
                  var result2 = stmt2.getResult();
                  if (!(result2.data===null)) { // ********************
                  var numResults2 = result2.data.length;
                  for (j = 0; j < numResults2; j++) {
                  var inner = result2.data[j];
                  air.trace('Cat: '+item.Name); // I think you want air.trace('SubCat: '+inner.Name)here
                  }
                  }
                  }
                  }

                  I hope this helps!
                  -Erica