Results 1 to 9 of 9

Thread: sqlite3 Cannot compile statement. File is not a database

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    sqlite3 Cannot compile statement. File is not a database

    Hello
    I'm using SQLite3 and VbrichClient platform.
    I'm using a password for the database.
    There are about 200 users who are using my application for about two years.
    So far 3 users have reported this message:
    Cannot compile statement. File is not a database
    Name:  03-12-2020 23-20-06.jpg
Views: 313
Size:  18.4 KB

    the database seems to be corrupted.
    I tried with some tools to repair the file and restore data but without success.

    Can anyone help me to repair the file please?

    Edit:
    I'd like to note that I'm executing PRAGMA auto_vacuum in the Sub Main
    Code:
    cnn.Execute "PRAGMA auto_vacuum=1; Vacuum"
    Last edited by Mustaphi; Dec 3rd, 2020 at 05:38 PM.

  2. #2
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: sqlite3 Cannot compile statement. File is not a database

    Quote Originally Posted by Mustaphi View Post
    I'd like to note that I'm executing PRAGMA auto_vacuum in the Sub Main
    Code:
    cnn.Execute "PRAGMA auto_vacuum=1; Vacuum"
    If your DBs have a still relatively small size, so that you can afford to do a Vacuum,
    then you could also have included a Backup-Run on the SQLite-Cnn at startup in addition...

    Below is such a function, which can ensure a "daily backup at startup",
    which also automatically ensures a "daily-backupfile-depth" of max 7 workdays.

    Code:
    Sub CreateBackupFrom(Cnn As cConnection, Optional EncrKey As String)
      Dim DBPath As String, DBFile As String, BkFile As String
          DBPath = New_c.FSO.GetPathNameFromFullPath(Cnn.FileName)
          DBFile = New_c.FSO.GetFileNameFromFullPath(Cnn.FileName)
          BkFile = DBPath & "Backup " & Format(Now, "yyyy\-mm\-dd\ ") & DBFile
      
      'now perform the backup-operation
      If Not New_c.FSO.FileExists(BkFile) Then Cnn.CopyDatabase BkFile, EncrKey
      
      'finally ensure, that only the 7 most recent daily backup-files remain in the original DB-Directory
      Dim DL As cDirList, i As Long
      Set DL = New_c.FSO.GetDirList(DBPath, dlSortByName, "Backup*.db*")
      For i = DL.FilesCount - 8 To 0 Step -1 'loop the sorted List backwards (sparing out the 7 youngest files)
        New_c.FSO.DeleteFile DL.Path & DL.FileName(i) 'delete the files, older than 7 days
      Next
    End Sub
    This would have avoided your current problem...

    As for a potential cause of DB-corruption - my guess is, that the DBFile in question was sitting behind a network-share or network-drive...

    If you want me to take a look at your corrupted DB, you can send it to me in a zip via mail:
    (os at vbRichClient.com) - ideally in a version, which was not yet undergoing any repair-attempts yet.

    Olaf

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: sqlite3 Cannot compile statement. File is not a database

    Thank you Olaf for the precious code.

    please have a look at your inbox
    Last edited by Mustaphi; Dec 5th, 2020 at 01:57 PM.

  4. #4
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: sqlite3 Cannot compile statement. File is not a database

    Quote Originally Posted by Mustaphi View Post
    please have a look at your inbox
    There was no need to post your DB-password here in the public section of the forum...

    And after taking a look into the File in your Zip with a Hex-Editor, it seems the password was not needed -
    because your DB-File contains "NullChars throughout" (~2Mio Bytes, all Zero).

    This is not the typical pattern in a corrupted DB (where only smaller parts or "pages" within a file are inconsistent).

    I think, that you mentioned this problem already in an earlier thread -
    and (if I remember correctly) - it happened "whilst downloading in a syncing-scenario".
    Searching... ah - here is the thread where something similar happend already:
    https://www.vbforums.com/showthread....o-be-corrupted

    Not sure, what you are doing there exactly...
    ...but a DB-File should normally be "touched" (changed) only by the Cnn-Object -
    not by File-Copy or File-Put commands.

    If you download something (e.g. a new DB-File from a Google-Drive or from wherever).
    - write the buffers first into a true temp-file in a temp-folder
    - after that try to open the new downloaded file via an SQLite-DB-Cnn-Object first
    - then calling Cnn.CheckIntegrity() ... (which returns a String, describing the integrity-level)
    - if the above was "Ok" - then try a Table-Select on it in addition (retrieving a Recordset)
    - check the Rs.Recordcount for being > 0
    - close the Temp-Cnn

    Now, that the new DB-File was verified (still sitting in a temp-folder),
    you could try to "integrate" it into the Folder, where your "normal DBs" are located.

    And I wouldn't try to attempt that by "overwriting the older, existing DB-File directly" (via normal FileCopy-commands)...
    Not if you cannot be absolutely sure, whether some User still has the old DB-File opened as a Cnn in an Application.

    Instead I'd introduce a "unique versioning, reflected in the DB-FileName":
    - placing the new (still "temp"-)File (accordingly re-named) into your real DB-Folder
    - via two Operations:
    - ... first a FileCopy (the new file having the temp File-Ending still)
    - ... secondly (only after the copy was finished), rename the file to its new, proper "versioned-name"
    - now sitting there (beside the older versions, which might still be opened in some Apps)

    This will ensure, that only a new up-starting App would try to open the brand-new file.
    (Your App-Startup-Handling should now take your new "versioning-scheme" into account,
    always looking for - and then opening the "most recent file-version" of the DB).

    Well, and before I forget...

    Never, ever open a Cnn-Object on a DB-File, which is sitting in a Folder that is:
    - part of the syncing-Processes of an "Online-Drive" (no matter if from Google or MS or whatever vendor).

    HTH

    Olaf
    Last edited by Schmidt; Dec 4th, 2020 at 05:22 PM.

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: sqlite3 Cannot compile statement. File is not a database

    Thank you olaf very much
    I'm sorry for answering you late
    I was absent

    ...but a DB-File should normally be "touched" (changed) only by the Cnn-Object -
    not by File-Copy or File-Put commands.
    I'm using a FileCopy function to keep a copy of the database file in the form
    unload event.
    Should I avoid that?

    thank you again for the function you provided
    You saved me

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: sqlite3 Cannot compile statement. File is not a database

    Quote Originally Posted by Mustaphi View Post
    I'm using a FileCopy function to keep a copy of the database file in the form
    unload event.
    Should I avoid that?
    Yes, please avoid a normal FileCopy, because you risk an inconsistent state of your DB-data this way
    If you do a FileCopy, you have to be absolutely sure, your App was the sole user of this DB-File, and your Cnn (and all Recordsets) were all set to Nothing beforehand.

    Instead a Cnn-Method can be used at any time: -> Cnn.CopyDataBase (...which is using the SQLite Backup-API under the covers).
    This ensures a consistent state of the new DBFile (its usage was shown already, in the little Backup-Helper-Routine in my first posting here).

    Also instruct your Users, to place their DBs (or the App itself, in case the DBs are sitting in the App.Path):
    - neither in the system "Programs"-Folder (to avoid problems due to SystemFolder-Virtualizations)
    - nor should the DB-Files be located in a Folder which is part of an "OnlineDrive-syncing"

    An SQLite-DB also should better not be accessed behind a Network-Share or a Network-Drive in a customer-LAN.
    MultiUser-mode can quite easily be ensured, via a proper AppServer-Setup - either using the RC5-RPC-Classes or the MS-IIS.

    HTH

    Olaf

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: sqlite3 Cannot compile statement. File is not a database

    Thank you Olaf for these clarifications.
    My database is sitting in the application path. And in another multiuser application I'm using the RC5-RPC.
    My last worry is that I want to use the Cnn.CopyDataBase to copy the database file outside the the Application path. (external drive for example) with the same name of the database file. And of course the new copy would overwrites the old one.
    I want to do so to allow my users (in case they need to do that) to recover the database file without they need to modify the name to the original one and also to check the most recent one.
    thank you

  8. #8
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: sqlite3 Cannot compile statement. File is not a database

    Quote Originally Posted by Mustaphi View Post
    My last worry is that I want to use the Cnn.CopyDataBase to copy the database file outside the the Application path.
    (external drive for example) with the same name of the database file.
    External Drives (e.g. when connected via USB) are often not as reliable (in case of Thumb-Drives) -
    and the speed via USB is also not the best.

    So, my preferred "backup-targetpath" would be "on a real, internal Disk":
    - either the same path as the original DB (as shown in the little backup-routine)
    - or in a \Backup\ subfolder in your App.Path
    That's, to let the Cnn.CopyDatabase (the SQLite-BackupAPI) work against a fast and reliable Destination.

    Only after your newly backed-up DB-Files sit in their (local) backup-target-folder,
    would I try to move (or better, copy) them from there to external Drives -
    to have those files (your "eggs") not only in a "single basket" (your local harddisk).

    Should something go wrong with the original local DB, you can normally directly restore from your local HardDisk
    (which nowadays are usually SSDs and quite reliable in terms of longevity).

    External copies offer additional security of course, especially when they are kept in a "different location" physically
    (what do I know, an office could undergo water- or fire-damage... or theft of equipment, whatever).

    Quote Originally Posted by Mustaphi View Post
    I want to do so to allow my users (in case they need to do that) to recover the database file without they need to modify the name ...
    If you want to do that (restoring under the same name), you will (as said),
    have to make very sure that no DB-Connection is currently open on the File you are about to overwrite -
    (all Apps closed, all Users "offline").

    If you are not sure whether everyone is offline, then I'd try (instead of a direct overwrite) the following sequence:
    - try to rename the old, original File (e.g. to MyApp.db.old)
    - then copy the backup.db into the App.Path under the original "MyApp.db" name
    - finally try to delete MyApp.db.old

    Olaf

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: sqlite3 Cannot compile statement. File is not a database

    thank you Olaf.
    Everything is crystal clear.
    However please excuse my ignorance.
    As I'm creating an auto backup at the application start up thanks to your function, I want to create an auto exportation of the backup file to an external drive.
    Therefore, Before copying the file to an external drive I need to check the most recent file (the file created in the same day) . Rename it to the original file. then exporting it to external drive.
    I have no problem to rename the file but surely I will have trouble with detecting the most recent file.
    And as I'm using the commonDialog, I'm looking for something like this:
    FileCopy ("Most recent backup file", CD.FileName)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width