SQLite Forum

[Solved] OPEN, ATTACH, CREATE VIEW, SELECT - possible?
Good Saturday morning.

I'm using PHP 7.3.27 and SQLite 3.26.0.  I had a dream last night where I could open an SQLite database, then ATTACH another database, then create a view to the ATTACH'd database (essentially making a RO version), the issue a SELECT query.  Here's the code:

>  $SqlHandle = new SQLite3($aDbWithNoData, SQLITE3_OPEN_READWRITE {pipe} SQLITE3_OPEN_CREATE);

>  $SqlStatement = "ATTACH DATABASE 'OtherDbFilename.sqlite3' AS LogFile;";

>  $SqlResult = $SqlHandle->exec($SqlStatement);
>  $SqlStatement = "CREATE VIEW IF NOT EXISTS Creates AS SELECT * FROM LogFile.Transactions WHERE MyColumnName LIKE 'CREATE%';";

>  $SqlResult = $SqlHandle->exec($SqlStatement);

>  $SqlStatement = "SELECT * FROM Creates;";

>  $SqlResult = $SqlHandle->query($SqlStatement);

>  while($SqlRows = $SqlResult->fetchArray(SQLITE3_ASSOC)) print_r($SqlRows);

>  $SqlHandle->close();

It gives me errors:

- SQLite3::exec(): view Creates cannot reference objects in database LogFile in
- SQLite3::query(): Unable to prepare statement: 1, no such table: Creates in
- Uncaught Error: Call to a member function fetchArray()

Obviously the 2nd and 3rd error is because error 1 happened.

I guess my question is can I create a view on an ATTACH'd database?  If yes, where did I go wrong?