2 Replies Latest reply on Feb 15, 2011 4:44 AM by Ivan Ilijasic

    sql inner join syntax error

    DrClohite

      <fx:Script>

      <![CDATA[

       

      import flash.filesystem.File;

      import flash.filesystem.FileMode;

      import flash.filesystem.FileStream;

       

       

      public var regStmt:SQLStatement = new SQLStatement();

      public var stmt:SQLStatement = new SQLStatement();

      public var dbFile:File;

      public var dbFile2:File;

       

       

      protected var employeeDB:SQLConnection;

      protected var regDB:SQLConnection;

       

       

      dbFile = File.applicationStorageDirectory.resolvePath("reg.db");

      dbFile2 = File.applicationStorageDirectory.resolvePath("employee.db");

      regDB = new SQLConnection();

      stmt = new SQLStatement();

      regDB.open(dbFile);

      stmt.sqlConnection = regDB;

      employeeDB(dbFile2);

      stmt.sqlConnection = employeeDB;

      stmt.text ="SELECT employee.id, reg.id FROM employee INNER JOIN reg ON employee.id = reg.id";

      stmt.execute();

       

       

       

      I get the error:

      An error has occurred and been caught by the global error handler: Error: Error #3101: Database connection is already open.

       

      I need to open two databases in order to run the inner join??

       

      Thanks

        • 1. Re: sql inner join syntax error
          _spoboyle Level 4
          I need to open two databases in order to run the inner join??

          have you tried?

           

          if your 2 tabels are in 2 different databases then I assume you'll have to have both open. I never had more than one database in an application myself. You can inner join 2 tables from 2 databses in sql server not sure about sqlite, you have to look it up. And even if you can you should really think about whether 2 tables that are closely enough related to be inner joined need to be in separate databses, there are situations but it's rare.

          • 2. Re: sql inner join syntax error
            Ivan Ilijasic

            Currently I don't have Flash Builder on this computer, I'm suggesting several possible problems:

             

            1. Have you opened your DB files with some tool or Sqlite browser while you were running your AIR application. This message could be result of this problem.

            2. stmt.sqlConnection = regDB

                stmt.sqlConnection = employeeDB;

             

            You are giving to stmt object two active connections. I really didn't have the oportunity to work with two Sqlite instances at once so I really don't know if this is a problem. I agree that the best option would be to have one Sqlite database with multiple tables.