sqlite DB being corrupted on Windows

Solar2D Daily Builds and Public Releases
#1

I found this code somewhere in the forums. What it does is check if the sqlite database exists in the system.DocumentsDirectory. If it does not, then it copies the database from the system.ResourceDirectory to the system.DocumentsDirectory and then opens it.

The issue is that somehow the database is being corrupted by the simulator on Windows. If I just open the database in the system.ResourceDirectory it works fine. If I use this code to copy it, my queries come back empty. The copied database is different in size by a few bytes. If I go into the project sandbox and open the copied database with Navicat, it tells me that the database disk image is malformed.

I don’t have this problem on the device or on my mac, just my Windows 7 x64 machine. I haven’t tested it on any other version of Windows.

Is something wrong with this code or is this a bug?

Here’s the code:

[code]
dbName = “sqlite.db”

– See if Database File Already Exists in Documents Directory
path = system.pathForFile( dbName, system.DocumentsDirectory )
file = io.open( path, “r” )

if( file == nil )then

– Doesn’t Already Exist, So Copy it In From Resource Directory

pathSource = system.pathForFile( dbName, system.ResourceDirectory )
fileSource = io.open( pathSource, “r” )
contentsSource = fileSource:read( “*a” )

– Write Destination File in Documents Directory

pathDest = system.pathForFile( dbName, system.DocumentsDirectory )
fileDest = io.open( pathDest, “w” )
fileDest:write( contentsSource )

– Done

io.close( fileSource )
io.close( fileDest )

end

– One Way or Another The Database File Exists Now
– So Open Database Connection
local path = system.pathForFile(dbName, system.DocumentsDirectory)
db = sqlite3.open( path )
[/code]
[import]uid: 61552 topic_id: 12230 reply_id: 312230[/import]

#2

I was able to replicate the same behavior on another Windows 7 64bit machine and also a 32bit Windows XP machine. [import]uid: 61552 topic_id: 12230 reply_id: 44805[/import]

#3

I have replicated this issue on a Windows 7 64bit machine, running Corona 591.

I have just gone ahead and copied the database manually to the documents folder for now. I will see if my Mac does the same thing. [import]uid: 42145 topic_id: 12230 reply_id: 49821[/import]

#4

Im having the same issue with this, soon as I do the copy, it corrupts the database, if I do the copy manually into the sandbox its fine!

Any solutions? [import]uid: 72726 topic_id: 12230 reply_id: 57265[/import]

#5

@KevinM2k - only solution is what you just did: copy the DB manually. It looks like there is a fix in one of the daily builds though. [import]uid: 42145 topic_id: 12230 reply_id: 57287[/import]

#6

Same problem here. But I’m using the daily build and I’m still having this problem.

Is it possible that system.DocumentsDirectory is not properly resolved on the Windows Simulator? [import]uid: 111563 topic_id: 12230 reply_id: 81435[/import]

#7

Hey guys,

I don’t work with Windows but I’m going to talk to some other members of the team to get some info on this.

Peach [import]uid: 52491 topic_id: 12230 reply_id: 81455[/import]

#8

hi any fix on this issue… Im faceing it too [import]uid: 115284 topic_id: 12230 reply_id: 86572[/import]

#9

We are hitting the identical problem. Works fine in the Mac OSX simulator and on iPhone. Won’t work on the Windows simulator. The file gets created but content is not written (i.e. fileDest:write does not).

The workaround is to manually copy the file.

For those reading this and saying “great but copy it to where” on Windows each Corona run-time creates its own copy of files in a sandbox directory.

Something like
C:\Documents and Settings(your user)\Application Data\Ansca\Corona Simulator\Sandbox(your app)-(long digits)\Documents [import]uid: 117446 topic_id: 12230 reply_id: 93532[/import]

#10

Same problem here on a Windows 7 64bit system! Is there any solution to this problem yet? [import]uid: 101883 topic_id: 12230 reply_id: 114607[/import]

#11

I am using windows 7 64bit an dont have any issues what so ever.
This is the code that I use to open my SQLite Database

I create a database in the system.DocumentsDirectory and
then I open and read it

function openSQLiteDB(sdbFileAndPath)  
  
 local dbErrorCode = 0  
  
 if \_G.DebugMode then  
 print("openSQLiteDB ------ START ----------------------------------")  
 print(sdbFileAndPath)  
 end  
  
 local db = sqlite3.open( sdbFileAndPath )  
 dbErrorCode = db:errcode()  
  
 if not db:isopen() then db = nil end  
  
 if \_G.DebugMode then  
 if dbErrorCode ~= 0 then   
 print("DBopen Failed -\>\> ")  
 print("db:errcode() = " .. dbErrorCode)   
 else   
 print("DBOpen Success -\>\> YES \< end  
 print("openSQLiteDB ------ END ----------------------------------")  
 end  
  
 return db  
end  
  
DatabaseFileName = system.pathForFile( dbName, system.DocumentsDirectory )  
  
local oDatabase = openSQLiteDB(DatabaseFileName)  
 sSQL = "select \* from someTable;"  
 for row in oDatabase:nrows(sSQL) do  
 print( row.field )  
 end

Larry [import]uid: 11860 topic_id: 12230 reply_id: 114635[/import]

#12

Hi Larry,

I guess the problem on Windows machines is copying a database from one directory to another - this seems to be where the corruption happens. Creating a new database and writing to it works fine though.

Jens [import]uid: 101883 topic_id: 12230 reply_id: 114685[/import]

#13

That is exactly what I have found heftyApps. That means you end up needing to run multiple SQL statements to populate your tables on first launch (slow) rather than use a snapshot starting point database which you can continue to tweak and alter with tools such as Lita, which would be far more convenient. [import]uid: 117446 topic_id: 12230 reply_id: 114691[/import]

#14

Everyone,

This is not a Corona bug. The Windows file system makes a distinction between “text” files and “binary” files and needs to be told what kind of file it is when you open it. You need to treat the SQLite database file as a binary file, so you’ll need to call [lua]io.open()[/lua] like this…

[lua]local fileForReading = io.open(“MyFile1.db”, “rb”)
local fileForWriting = io.open(“MyFile2.db”, “wb”)[/lua]

Notice the “b” at the end of the above strings. That tells Windows to access the file as a binary file. If you do not append a “b” to the end, then Windows will treat it as a text file by default… and copying a database/binary file as a text file “will” create a corrupted file.

Please see the following documentation for an example on how to do a file copy that works on all platforms…
http://docs.coronalabs.com/api/type/File/write.html
[import]uid: 32256 topic_id: 12230 reply_id: 114835[/import]

#15

Good point, that did not even dawn on me,

Larry [import]uid: 11860 topic_id: 12230 reply_id: 114846[/import]

#16

Glad I could help… and here is Microsoft’s documentation to back up what I just stated above.
http://msdn.microsoft.com/en-us/library/yeby3zcb.aspx
[import]uid: 32256 topic_id: 12230 reply_id: 114857[/import]