2 Replies Latest reply on Nov 29, 2010 4:41 AM by Tiki Howpu

    SQLStatement IN operator : Data type mismatch

    Tiki Howpu

      Hi,

       

      I have this in my AIR application:
       
      var relatedQuery:SQLStatement = new SQLStatement();
      relatedQuery.sqlConnection = myConnection;
      relatedQuery.addEventListener( SQLEvent.RESULT, queryResult );
      relatedQuery.addEventListener( SQLErrorEvent.ERROR, onError );
       
      queryString:String = "SELECT * FROM tbl_sheet WHERE  tbl_sheet.sheet_nID IN ( :pSheetIDList ) ";
       
      relatedQuery.parameters[":pSheetIDList"] = nSheetIDList;
      relatedQuery.text = queryString;  
      relatedQuery.execute();  
       
      The nSheetIDList is an Array like this :  14563,14549,14556,14557

      And the database column sheet_nID is an INTEGER type.

       
      When running debug-mode it gives an error:

      Error #3132: Data type mismatch.', details:'could not convert text value to numeric value'. 

       

       

      Any ideas what's wrong with the above?

        • 1. Re: SQLStatement IN operator : Data type mismatch
          Alexandru Nicau

          Hi,
          You're trying to pass an Array as query parameter and that's not supported. Here is the list of allowed datatypes: TEXT (or String), NUMERIC, INTEGER (or int), REAL (or Number), Boolean, Date, XML, XMLLIST, Object and NONE. Object cannot be used for that purpose, is useful when you serialize/store/deserialize entire AS objects.

           

          For further reading - scroll down to SQL support in local databases, Column affinity
          http://help.adobe.com/en_US/as3/dev/WSd47bd22bdd97276f2aceae3b1262b7f2d43-8000.html

           

          I would suggest as fast workaround using a set of unnamed parameters
          http://livedocs.adobe.com/flex/3/html/help.html?content=SQL_06.html

           


          relatedQuery = new SQLStatement();
          relatedQuery.sqlConnection = myConnection;
          relatedQuery.addEventListener( SQLEvent.RESULT, queryResult );
          relatedQuery.addEventListener( SQLErrorEvent.ERROR, onError );

           

          //example array to be replaced with nSheetIDList

          var oneArray:Array = new Array("1", "2");
          var i: uint;
          var paramString : String = "";

           

          //build a short string with question marks
          for (i = 0; i < oneArray.length; ++i)
          {
              paramString += '? ,';
          }

           

          //remove the last ',' because nothing follows
          paramString = paramString.substring(0, paramString.length - 1);

           

          var queryString:String = "SELECT * FROM tbl_sheet WHERE sheet_nID IN (" + paramString + ")";
          relatedQuery.text = queryString;

           

          for (i = 0; i < oneArray.length; ++i)
          {
              relatedQuery.parameters[i] = oneArray[i];    //zero based index, link each unnamed parameter with the corresponding array value
          }

           

          relatedQuery.execute();             

           

           

           

          have fun,
          Alex.

          • 2. Re: SQLStatement IN operator : Data type mismatch
            Tiki Howpu Level 1

            Thank you, it works now