SQLite Forum

Setting temp_store_directory on Windows in future proof way
Login

Setting temp_store_directory on Windows in future proof way

(1) By anonymous on 2021-05-02 07:35:57 [link] [source]

In order to control where files are written by SQLite, I have a requirement to be able to specify on Windows where any temporary files are written in a future proof way.

PRAGMA temp_store_directory is deprecated and so should not be used..... but there does not seem to be any alternative way specified in the documentation in order to specify the location for temporary files for SQLite on Windows.

What is the correct (non-deprecated) method to use on Windows to specify this?

(2) By Keith Medcalf (kmedcalf) on 2021-05-02 07:54:27 in reply to 1 [link] [source]

SQLite3 uses the Windows API that retrieves the path of the temp directory from the Operating System.

https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-gettemppatha

In short, the temp directory is specified in the process environment and looks for the value of the TMP variable, then the TEMP variable, then the USERPROFILE variable, then the Windows System directory; the first one found wins.

(3) By anonymous on 2021-05-02 08:16:47 in reply to 2 [source]

Many thanks for the speedy and clear reply.

My understanding from this is that there is basically no (non-deprecated) way on Windows of programmatically specifying to SQLite the specific location to use for the temporary files that it uses and that the default Windows  mechanisms/locations are used.

(4) By Keith Medcalf (kmedcalf) on 2021-05-02 19:36:08 in reply to 3 [link] [source]

That is correct. In theory one could programmatically push a new TMP variable into the current process environment block using the appropriate Windows API.

https://docs.microsoft.com/en-us/windows/win32/api/winbase/nf-winbase-setenvironmentvariable

This would change the current process' copy of the environment and if one were to specify a value for the variable TMP then that would become the new temp file location for the entire process (and any sub-process created after the change were made that inherited the current process environment) provided that the call to the OS to get the temp file location occurred after the change was made.

(5) By Clemens Ladisch (cladisch) on 2021-05-03 14:13:30 in reply to 4 [link] [source]

But getenv() might use a cached copy of the environment variables. So you must use _putenv() (which is not a standard function).

(6) By Keith Medcalf (kmedcalf) on 2021-05-03 17:44:35 in reply to 5 [link] [source]

These are C Runtime library functions which do God only knows what (and not even she knows without reading the source code).

Using the SetEnvironmentVariable API does indeed modify the current process environment at the Operating System level as seen by subsequent calls to the GetTempPath API. The runtime functions do not appear to actually modify the process environment, but rather the runtime library's copy of that environment -- though results may vary depending on the particular implementation, compiler, and runtime.

Python 3.9.1 (tags/v3.9.1:1e5d33e, Dec  7 2020, 17:08:21) [MSC v.1927 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import win32api
>>> import os
>>> os.getenv('TMP')
'C:\\Users\\KMedcalf\\AppData\\Local\\Temp'
>>> win32api.GetTempPath()
'C:\\Users\\KMedcalf\\AppData\\Local\\Temp\\'
>>> win32api.SetEnvironmentVariable('TMP', r'D:\Temp')
>>> os.getenv('TMP')
'C:\\Users\\KMedcalf\\AppData\\Local\\Temp'
>>> win32api.GetTempPath()
'D:\\Temp\\'
>>> ^Z