SQLite & integrity checking

[NewPlaza]

Hello,

Does SQLite have a built in integrity check?

What I am asking is, does the statement

_SQLite_Startup()

open the db file and scan for any errors? I understand the data within the tables can be ANYTHING and that much can't be checked but what about the db being corrupt? I'm sure SQLite has a very specific file format it follows.  Is there a way that can be checked?

[jchd]

_SQLite_Startup is only setup of the library/UDF for use.

_SQLite_Open doesn't check anything and doesn't even actually open the file. Actual operations are postponed until the first "active" SQL statement.

You can perform a full integrity check with:

_SQLite_Exec($hDB, "PRAGMA integrity_check;")

or, alternatively:

_SQLite_Exec($hDB, "PRAGMA integrity_check(integer);")

which will report only integer errors, if any.

[NewPlaza]

Fantastic!! Thanks.

[NewPlaza]

Ha, I spoke too soon.  Thanks for the info.  Is there a return value for this? How can I check....

Something like this..

$CheckDB = _SQLite_Exec($hDB, "PRAGMA integrity_check;")

if 0 then error in db?

if 1 then db fine?

Please forgive my ignorance.  I am very new to SQLite.

Edited September 10, 2013 by NewPlaza

[jchd]

My own oops! I completely forgot these return results!

If no error is found, the pragma returns a single row = "OK" and this is the only thing you want to know: in case of integrity error(s) better consider the DB corrupt and reload the last backup!

Here's a sketch use:

_SQLite_Startup()
If @error ... (abnormal condition)
Local $hDB = _SQLite_Open($DBFile)
If @error ... (abnormal condition)
Local $aRow
_SQLite_QuerySingleRow($hDB, "pragma integrity_check;", $aRow)
If @error ... (abnormal condition)
If $aRow[0] <> "OK" Then
    ; database is corrupt
Else
    ; DB is fine
EndIf

If you want to know more:

Local $iRows, $iCols, $aRows
_SQLite_GetTable($hDB, "pragma integrity_check;", $aRow, $iRows, $iCols)
; Here $aRows is an array containing a list of errors

Sorry for confusing you, it was late (and now it's even much later here!).

[NewPlaza]

That my friend is what I need. Thanks for all your help.