SQLite Forum

Close/Dispose does not release lock
Login

Close/Dispose does not release lock

(1.1) By Tyler (tmontney) on 2021-12-09 21:08:14 edited from 1.0 [link] [source]

(I couldn't get markdown to completely format code text, for whatever reason, so I set it to plain text.)

Move/delete db results in error in Windows, on `Close` or `Dispose`. Turns out, I have to run `Dispose` on `SQLiteCommand` and then it is released. Is this normal?

Happens with http://system.data.sqlite.org/downloads/1.0.115.5/sqlite-netFx46-binary-x64-2015-1.0.115.5.zip

Here's the module:

function Connect-SQLiteDatabase([ref][Object]$conn){
    try{
        $conn.Value.Open()
    }catch{
        Write-Warning -Message ("Failed to connect: " + $_.Exception.Message)
    }
}

function Disconnect-SQLiteDatabase([ref][Object]$conn, [Switch]$Dispose){
    try{
        $conn.Value.Close()
        if($Dispose){
            $conn.Value.Dispose()
        }

        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
    }catch{
        Write-Warning -Message ("Failed to disconnect: " + $_.Exception.Message)
    }
}

function Initialize-SQLite(){
    try{
        Add-Type -Path "$PSScriptRoot\lib\SQLite\System.Data.SQLite.dll" -ErrorAction Stop
        [void][System.Data.SQLite.SQLiteConnection]
    }catch{
        Write-Error -Message "Could not import SQLite. Ensure the dll is present in .\lib\SQLite\"
    }
}

function Invoke-SQLiteNonQuery([System.Data.SQLite.SQLiteCommand]$sqlCommand, [Switch]$NoDispose){
    $data = $sqlCommand.ExecuteNonQuery()
    if(-not $NoDispose){
        $sqlCommand.Dispose()
    }

    return $data
}

Here's the script:

# Create and connect to new db
$conn = (New-SQLiteDatabase -DatabasePath $TestDatabasePath -Open).Data

# Insert row
$sqlCommand = New-SQLiteCommand -conn $conn -command "INSERT INTO TestTable (name) VALUES (@Example)" -valuePairs @{"Example" = "Example123"}
Invoke-SQLiteNonQuery($sqlCommand)

# Disconnect
Disconnect-SQLiteDatabase([ref]$conn)

(2) By mine7777 on 2021-12-10 14:50:26 in reply to 1.1 [link] [source]

I wonder if this isn't something similar. 
Windows 10 Pro. Microsoft Windows [Version 10.0.19042.1348]
SQLite3 version 3.36.0 2021-06-18 18:36:39

sqlite3 <loadprices.txt (load most recent prices, then dump all prices)

#start loadprices.txt
.open G15.db
.mode csv
.import prices.csv prices
.output pp.csv
select * from prices ORDER BY symbol ASC, epoch DESC;
.quit
#end loadprices.txt

When I try to sort the output file, pp.csv, it returns no records although pp.csv has several hundreds or thousands, and opens in Excel.

If I change loadprices.txt to this, I can sort the output file.

#start loadprices.txt
.open G15.db
.mode csv

.import prices.csv prices

.mode csv
.output pp.csv
select * from prices ORDER BY symbol ASC, epoch DESC;
.quit
#end loadprices.txt

In the first case, there seems to be a lock on the output file so that it cannot be read by some apps. In the second case, there is no "lock". I have looked at the output files in text and hex editors and can find no differences in the output files themselves.

(3) By Simon Slavin (slavin) on 2021-12-11 13:20:01 in reply to 2 [link] [source]

Please use Process Manager or the 'openfiles' utility to identify which process has your file locked. It might be the sqlite3 command line utility, but it might not. If this doesn't explain to you what's happening, please start a new thread for this, with the results of the above, plus the following.

When using your scripts please delete pp.csv before using each one.

Please tell us what 'dir' or 'ls' says about the file size of pp.csv once you have quit sqlite3. Are the two sizes reported as being the same ?

Also, as well as scripting sqlite with loadprices.txt, please try typing each set of commands manually and see whether that gives you the same result. Since you understand hex editors please check both versions of loadprices.txt to make sure whether both or neither end in a return character, or with the final 't' of '#end loadprices.txt'.

Please replace your SELECT command (or add a new one) with 'SELECT COUNT(*) FROM prices' and see whether the two versions give the same number.

When you say you 'sort' something, what does that mean ? Are you doing it in Excel or with a command-line utility ?

(4) By Keith Medcalf (kmedcalf) on 2021-12-11 13:57:52 in reply to 2 [source]

What happens if you use the first script and change .output to .once ?