SQLite - attempt to write a readonly database

Some people are saying that this is a recurrent issue in Vista and Windows 7- that you need to run the app as admin...  Doesn't make sense to me.  I hope someone can confirm or present a workaround.  I tried to run VS2010 as admin- it didn't change anything, but I don't think it will launch the debugging process as admin as well....  How can I run my app as admin through the IDE?

>>How can I run my app as admin through the IDE?

You should add a manifest to your application that requests admin privileges by setting 'requireAdministrator', see http://msdn.microsoft.com/en-us/library/bb756929.aspx ("Step 6: Create and Embed an Application Manifest (UAC)"). The manifest should look like

Executable: myapp.exe 
Manifest: myapp.exe.manifest
Sample application manifest file:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0"> 
  <assemblyIdentity version="1.0.0.0"
     processorArchitecture="X86"
     name="myapp"
     type="win32"/> 
  <description>Description of your application</description> 
  <!-- Identify the application security requirements. -->
  <trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
    <security>
      <requestedPrivileges>
        <requestedExecutionLevel
          level="requireAdministrator"
          uiAccess="false"/>
        </requestedPrivileges>
       </security>
  </trustInfo>
</assembly>

Select allOpen in new window

Ooops, sorry, a copy&paste accident - this should just be

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0"> 
  <assemblyIdentity version="1.0.0.0"
     processorArchitecture="X86"
     name="myapp"
     type="win32"/> 
  <description>Description of your application</description> 
  <!-- Identify the application security requirements. -->
  <trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
    <security>
      <requestedPrivileges>
        <requestedExecutionLevel
          level="requireAdministrator"
          uiAccess="false"/>
        </requestedPrivileges>
       </security>
  </trustInfo>
</assembly>

Select allOpen in new window

Thanks jkr but that didn't help.  Or did it?  (I see a little shield on the icon of my app in Windows Explorer, just noticed that little sucker.  Looks pretty nifty having a shield on my app).

So now I'm a bit confused for another reason.  Get this.  To make sure that the following is correct, I've completely deleted the manifests (maybe windows knows something I don't now, and it's storing something about my app in the registry)....

Previously I was always trying to run my app with an argument (being the name of my sqlite db file), it tries to open the file that way and fails with the "attempt to write readonly db".  The program doesn't crash, I catch the exception.  Note that this kept happening even after your manifest suggestion.

Before even asking this question on EE, I was able to open the file and run the code after the initial fail (by supplying the db name by param to my app).  Then, after that always failed, I choose "File | open", with the exact same file - and it works.  However, now there's a different - I've deleted my manifests, that nifty shield is there, and it ALSO works when the file is passed as param.  No code changes.  Must be the manifest as some hokus pokus windows is doing with my app in the registry.  Am I right?

Thanks!
Mike

I hope that was clear- basically I was saying that I was always able to call "file | open", but that when that same file was opened because it was passed as a param to my program, it fails.  So basically, it didn't seem like a permission thing.....  it seemed more like an sqlite issue.

But with no code changes, I'm confused about this.

Can you check if when you do that "file | open" thing, the full path is being used? Or, IOW, what happens if you specify the full path on the command line?

I was curious to see what would happen if I delete the exe for the app.  I lost the little shield that was superimposed on it when it got rebuilt (it's not very noticeable, but it's not there anymore).  It still works every time, with no manifests and no code changes.....

I've had this same intermittent bug with sqlite before - different code (different language even), different format of the file - and it just seemed to go away on its own as well.  I could never pinpoint the issue with it, shrugged my shoulders and since it kept working without problems with months in between, let it go.  Now I have to get to the bottom of this....

I just double checked and it's always exactly the same path in both cases.

(full path)

Nothing with my app's name of interest in the registry....

Are you by any chance using a different working directory in each case?

No - same instance of the application.  It's launched from the IDE (with command line being full path to the file).  Then when that used to fail, full path to the file as well from browse dlg.  working dir could not have changed.

Well, the IDE *does* use a different working directory than when you launch it from the Explorer - check that with 'GetCurrentDirectory()'.

I'm only launching it from the IDE (with a debug parameter which is the same full path to the file).  The same launched session first fails, then it works when I use my (launched app from the IDE's) File | Open function.  Same debugging launched session (first fails with debug param, then works from file open - same instance of the app).  Then, worked for both with no code changes....  Sorry this wasn't so clear...

Mike

can you post details to the (three?) start and open alternatives you tried?

- post the commandline and default directory from debug settings in the IDE
- post the code you were using when you pass the file by "debug param".
- post the code you were using when getting and opening the file via "File - Open"  

note, when starting the program from ide it is always the project root directory which you have as current directory beside you make different launch settings. if you start the program from explorer (browser?) the default directory is where you executable resides. in any case when you were able to open the database file you have passed a correct path to the file. but you may check that you only have one single file with the same name at disk.

Sara

Hi Sara,

There are not three ways I'm doing this - there's only 1:

- I run my program ONCE from the IDE (with a command line).
- It calls a function to open the file passed as argument
- it fails
- The same program is STILL running.
- I then call File | Open (with the same file)
- the same function as previously described gets called, with the same path (same working dir of course)
- it then works.

That was before doing what jkr said to do, without any code changes, everything just started working.  No different threads.  I'm baffled.  And still, It all works, the error is not happening any more.

The code called in the adaptor (command line or from file open - same function is called):

Within that function:
SQLiteDatabase *pDB = new SQLiteDatabase(m_strFileName, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);
SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDB);
pStmt->SqlStatement("CREATE TABLE IF NOT EXISTS mydata (id INTEGER, fid INTEGER, dval REAL, ival INTEGER, sval TEXT, type INTEGER, cid INTEGER);");

nothing interesting at all......

Seems like sqlite has a bug in it.  I wouldn't be the first to suspect this, after googling a lot yesterday- this is a very common error and there's some confusion about it.

it seems to be rather unlikely that you really have two or more times an identical m_strFileName when calling 'new SQLiteDatabase' .

would you post the code where you fill m_strFileName from commandline argument, from debug param and from Open - File ?

also would you please set a breakpoint at statement with new SQLiteDatabase, such that you could go sure, that the function was called only once and the contents of m_strFileName is all the same.

Sara

Did that.  Definitely only once.  I totally agree with you that this sounds extremely weird.  But like I said- I've seen this before in a completely different program/language/ database with SQLite.

And again- I didn't even change any of the code and it just started working again- by creating a manifest and deleting it after?  I don't know about that.  I suspect something like an antivirus program getting a handle on the file as I was trying to read it or something (but 50 times in a row and after restarting my machine)?  Doubt it.  Baffled.

Mike

Now you're talking - your last comment is a very good one!

You got it!  THANK YOU!     :o)

if a file is not closed properly a file handle still was unclosed and could prevent from opening exclusively next time. i assume sqllite opens the file read-only when it failed to open exclusively.

but, with that next open windows closes the old and no longer used file handle from previous session such that any following call for opening in exclusive write mode succeeds.

i don't know that for sure, but it sounds not bad, right?

thanks.

Sara

I actually read about that - yes SQLite opens in readonly mode if something else has a handle on it... But I thought it was an anti-virus program causing that.  It could very well be both myself and another program causing it....  Because it even happened after a restart...

yes, if it happens after boot, it definitively had another reason. you probably could help yourself by copying the database file to a network drive. or if the database is small you could write a little script where you first make a copy of the file and open the copy rather than the original. after closing the program the script would copy the file back. that way you additionally had a backup.

Sara

Nope- I'm starting to think there's something seriously wrong with SQLite.  I'm getting this error again and intermittent behavior again- I get through a series of "CREATE TABLE IF NOT EXISTS" calls, it gets through them all just fine, and when I inspect the file, the tables don't all exist!

SQLite is file-based and the "lite" means some limitations - especially for multi-user operability. but i don't think there is a fundamental problem as there are many many developers using SQLite.

when do you inspect the file? while the program is still running? did you check all exits of your program such that the file really was closed after  the create statements? are you doing some kind of transactions? are you doing some asynchronous operations with the SQLite? some callbacks? can you make sure that the database never was opened twice?

Sara

I thought you were right previously about the opened twice, but I looked deeply into it, it was not.  I always close the DB and only ever connect to it on one thread (the main thread).  This morning it works again... no code changes.  I don't understand what it could be.  There's nothing that I know of that is accessing (all my sqlite files)...   It either happens to all of them or none of them.  It's very bizarre.  I think you should reclose this question- I don't think there's much anyone can do to help me here...  It's going to be something quite unique- because yes, a heck of a lot of people use sqlite!

quite unique and intermittent.......  makes a great combination!

This is happening again- this is truely my worst nightmare.  It does seem to be a problem with trying to create a new table when data already exists in the database.

When I test on a new file that doesn't contain some of the tables that my setup function creates with "CREATE TABLE IF NOT EXISTS", I get the error, "Attempt to write to readonly database"....  I will continue to document what I find on this in this question until it's solved...

ok wow...  I think I'm finally on the right track.  After diving into the Winapp code for the serialize function (which gets called from ProcessShellCommand), There's a call to load the file passed as param to the app.  That serialize function, I noticed, creates a read-only handle on the file...  I'm looking into another way of launching my program and grabbing what's on the command line.....

EUREKA!  This was from some very unexpected behavior as I stated in my last post.  I'm not sure why I had placed my code in the serialize, but that was done a very long time ago- before I changed my file format from my own to SQLite.  This was only happening to files that weren't already getting created in another program.  One of them tough bugs to fix- one less to deal with in my life!  FEWF!!  Thanks again you two...