2 Replies Latest reply on Aug 22, 2011 3:34 PM by danh2000

    SQLStatement null data

    danh2000

      Hi,

       

      Having created a database with LITA, my query is not returning any data and I can't work out why.. my code is listed below:

       

      var stmt:SQLStatement=new SQLStatement();

      stmt.sqlConnection=conn;

      stmt.text="SELECT templates.name AS 'template', entries.name, fields.label, data.value, types.type FROM data INNER JOIN entries ON data.entriesid = entries.id INNER JOIN fields ON fields.id = data.fieldsid INNER JOIN templates ON fields.templatesid = templates.id INNER JOIN types ON fields.typesid = types.id WHERE data.entriesid = :idParam";

      //stmt.text="SELECT * FROM entries WHERE id=:idParam";

      stmt.parameters[":idParam"]=id;

      stmt.execute();

      var result:SQLResult=stmt.getResult();

       

      The SQL works perfectly in LITA and the comment simple commented statement works fine, but with the full statement, the result.data is null.

       

      Can anyone see anything obviously wrong or point me in the right direction for debugging this.

       

      Thanks,


      Dan

        • 1. Re: SQLStatement null data
          EvyatarBH Level 3

          What if you move the joins to be part of the where clause?

           

          SELECT 
               templates.name AS 'template', 
               entries.name, fields.label, 
               data.value, types.type 
          FROM 
               data,entries,fields,templates,types
          WHERE 
               data.entriesid = entries.id AND 
               fields.id = data.fieldsid AND 
               fields.templatesid = templates.id AND 
               fields.typesid = types.id AND
               data.entriesid = :idParam
          
          
          1 person found this helpful
          • 2. Re: SQLStatement null data
            danh2000 Level 1

            Hey, thanks for your post.

             

            My preference is always to use real joins in SQL statements - they are faster in most DB engines.

             

            That said, I did try to reformat the statement, but still got the same result.

             

            I then picked it apart 1 join at a time and realised that I hadn't populated the 'fields' table - DOH!

             

            I look forward to the day when foreign key enforcement is in place (come on Adobe, please upgrade sqlite) - it's very difficult to work with complex data structures without it.

             

            Thanks,

             

            Dan