SQLite error "database disk image is malformed"

[quote=303479:@James Dooley]No not in my book! Simply add a version control field to the record either a time stamp or my preferred way an int field. Then update statement is:

UPDATE … version = version + 1 WHERE … (version = ?)

If rows affected is zero it means someone else updated the record while you were editing.[/quote]
a field in the table does no good if the act of reading the table is what is leading to the issue…
the semaphore says “HANG ON THE TABLE IS BUSY DO NOT EVEN TRY TO ACCESS IT”… it is a form of TABLE LOCK, what you are describing is a RECORD LOCK. Two totally different things.

but then as mentioned SQLite is not the best choice for what the poster is wanting to do, yet he seems bent on making it work.

that being said… I’ve made my suggestions, as have others… lead a horse to water etc.

I’m exploring options… its just looking like a daunting task to make the switch.

As usual good things take time. Just like this Date Picker Control .

Sometimes you know better but it’s still hard to convince yourself to do better…

And thanks to everyone for you help. I seems like I learn everything from the school of hard knocks (is that a goose egg?) :slight_smile:

It wasn’t as painful as I thought to convert to Valentina SQLite Server. I created a method to replace my attach statements. I’m wondering now if I’m going to run into a problem binding everything as text and using databasefield.stringvalue? I don’t use any blobs but expect those would be a problem with this method.

[code]Function PutInDB(extends db as VSQLiteDatabase, Table as String, rs as RecordSet, Delete as Boolean = False, ColNames as String = “”) As string
if rs <> Nil and rs.RecordCount> 0 Then
db.SQLExecute(“BEGIN TRANSACTION”)
if Delete Then db.SQLExecute(“DELETE FROM " + Table)
if db.Error Then
dim msg as String = db.ErrorMessage
db.SQLExecute(“ROLLBACK”)
Return msg
end if
dim Values as String
dim Temp(), Temp1() as String
if ColNames = “” Then
for i as Integer = 1 to rs.FieldCount
temp.Append rs.IdxField(i).Name
temp1.Append “?”
next
ColNames = Join(Temp,”,")
Else
temp1 = Split(ColNames,",")
for i as Integer = 0 to temp1.Ubound
temp1(i) = “?”
next
end if
Values = Join(Temp1,",")

do
  dim ps as SqlitePreparedStatement
  ps = db.Prepare("INSERT INTO " + Table + "(" + ColNames + ") VALUES(" + Values + ")")
  for i as Integer = 1 to rs.FieldCount
    ps.BindType(i-1,SQLitePreparedStatement.SQLITE_TEXT)
    ps.Bind(i-1, rs.IdxField(i).StringValue)
  next
  ps.SqlExecute
  if db.Error Then
    dim msg as String = db.ErrorMessage
    db.SQLExecute("ROLLBACK")
    Return msg
  end if
  rs.MoveNext
loop until rs.EOF
db.SQLExecute("COMMIT TRANSACTION")
if db.Error Then
  Return db.ErrorMessage
else
  Return "ok"
end if

end if
End Function
[/code]

Would SQLite still be a bad Idea for concurrent users if they were doing read only access? For example accessing a catalog or product setup information? I’m thinking 2-10 users.

I read this on the SQLite website.

Sorry if this is an ignorant question.

readers should be fine

Thanks Norman

the concurrent writes/updates/deletes is what can cause the issues.

That’s what I thought. The locking doesn’t work quite right and two processes end up modifying the data file at the same time.

On a single machine with multiple processes each with a separate connection you can do this

But dont share one db file with many writers on some network file systems
It’s why we and sqlite have been saying “dont use it this way”
(see my previous comment)

Thanks Norman. Your previous comment among others is what help me decide switching to a server app was necessary.

I switched this particular project to Valentina SQLite Server and then to PostgreSQL. I have another app that might still be better off with SQLite.

So what causes the problem isn’t so much more than one writer, but rather writers from more than one machine. Thanks for the clarification.

On another note talking about connections. Is it a problem to have several separate threads in the same app writing simultaneously to the same connection? I don’t think I do that anywhere but thought I would ask. In other words would it make a difference if you create a new connection vs. pass the database variable from the main thread.

Not strictly - no

Because of how SQLite uses file system locks IF the server OS doesn’t do it “right” a single writer can cause issues too IF the db file is on one machine & the “client” is on another that has that servers file system mounted
This is the relevant part of the FAQs SQLite Frequently Asked Questions

Ok I see. The app and the database should be on the same computer. Makes sense. Thanks for the link. Very useful.