SQLITE Temporary Tables

Just starting out? Need help? Post your questions and find answers here.
collectordave
Addict
Addict
Posts: 1309
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

SQLITE Temporary Tables

Post by collectordave »

Trying to create a temporary table in an SQLITE database using the following statements

Code: Select all

      Criteria = "Create TABLE Selectedsongs AS Select * from Songs left join Artists on Artists.Artist_ID = songs.Artist_ID left join AlbumSongs On AlbumSongs.Song_ID = Songs.Song_ID LEFT JOIN Albums ON Albums.Album_ID = AlbumSongs.Album_ID where Songs.Artist_ID = " + Str(FilterArtistID) + ";"
      
      Criteria = "Create TEMPORARY TABLE Selectedsongs AS Select * from Songs left join Artists on Artists.Artist_ID = songs.Artist_ID left join AlbumSongs On AlbumSongs.Song_ID = Songs.Song_ID LEFT JOIN Albums ON Albums.Album_ID = AlbumSongs.Album_ID where Songs.Artist_ID = " + Str(FilterArtistID) + ";"
         
The first creates a normal table no bother but doing the TEMPORARY keyword creates nothing and raises no database errors.

Any ideas?

Both statements work ok with DB browser.
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: SQLITE Temporary Tables

Post by infratec »

CREATE TEMPORARY TABLE creates a temp file on the disk.
Unfortunately I don't know where. Maybe it is a rights problem, so that sqlite can not create the file.

Look at point 2.6 in
http://devdoc.net/database/sqlite-3.0.7 ... files.html


Have you tried Debug DatabaseError() :?:
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: SQLITE Temporary Tables

Post by the.weavster »

You can tell SQLite which folder to use by setting the environment variable 'SQLITE_TMPDIR'
collectordave
Addict
Addict
Posts: 1309
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

Re: SQLITE Temporary Tables

Post by collectordave »

Thanks all makes sense now.

The temporary table is created in a temp folder but is only accessible from the connection that created it.

So with me using PB to create the temp table then closing the connection deleted the file. Also looking at the DB with DBbrowser it is a different connection so cannot be seen.

Regards

CD
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
Post Reply