Deletefile() not working?

[doctorized]

I have the following code that tries to check if a file is a valid database. If so, it loads it, if not, it replaces it with a valid one. I tried with a renamed random txt file to see what happens. The code detects that it is not a db but seems to fail to delete it. DeleteFile() returns zero, OpenFile() fails to initialise the file and CloseFile() returns error "The specified #File is not initialised". Replaced Openfile() with CreateFile() and nothing changed. The file is not opened inside or outside PureBasic. Any idea what is going on?

Code: Select all

UseSQLiteDatabase()
DatabaseFile$ = GetCurrentDirectory() + "libdata.file"
Enumeration
	#db
	#db2
EndEnumeration

Procedure CheckDatabaseQuery(Database, Query$)
	Result = DatabaseQuery(Database, Query$)
	If Result = 0
		txt$ = DatabaseError()
		If txt$ <> "file is not a database"; in case of no db file, alert will come later.
	   	MessageRequester("error","Error occurred while reading for the database: " + txt$, #MB_ICONERROR)
	   EndIf
   EndIf
   ProcedureReturn Result
EndProcedure

Procedure.a IsDB(File$)
	If OpenDatabase(#db2, File$, "", "", #PB_Database_SQLite)
		If CheckDatabaseQuery(#db2, "select value FROM Settings WHERE Var = 'CheckDays'")
			NextDatabaseRow(#db2)
			i = GetDatabaseLong(#db2, 0)
			FinishDatabaseQuery(#db2)
			CloseDatabase(#db2)
			If i > 0
				ProcedureReturn 1
			Else
				ProcedureReturn 0
			EndIf
		Else
			ProcedureReturn 0
		EndIf
	Else
		ProcedureReturn 0
	EndIf
EndProcedure

If FileSize(DatabaseFile$) = -1 Or FileSize(DatabaseFile$) = 0
	;we create and load a new db file
Else
	If OpenDatabase(#db, DatabaseFile$, "", "", #PB_Database_SQLite)
		If IsDB(DatabaseFile$) = 0
			If MessageRequester("error","libdata.file is a not a valid database file. Do you want to create a new one?",#PB_MessageRequester_YesNo|#MB_ICONERROR) = #PB_MessageRequester_Yes
				CloseDatabase(#db)
				DeleteFile(DatabaseFile$)
				CreateFile(0,DatabaseFile$);create the file if it doesn't exist
				CloseFile(0)
				If OpenDatabase(#db, DatabaseFile$, "", "", #PB_Database_SQLite)
					;CheckDB();let it write the data
				EndIf
			Else
				MessageRequester("error","the app cannot run without a valid database file and it will terminate", #PB_MessageRequester_Ok | #MB_ICONERROR)
				End
			EndIf
		EndIf
	EndIf
EndIf

[Bisonte]

the problem is your IsDB Proc.
You not release the databasefile.... only if it is a database...

Correction here :

Code: Select all

Procedure.i IsDB(File$)
  
	If OpenDatabase(#db2, File$, "", "", #PB_Database_SQLite)
		If CheckDatabaseQuery(#db2, "select value FROM Settings WHERE Var = 'CheckDays'")
			NextDatabaseRow(#db2)
			i = GetDatabaseLong(#db2, 0)
			FinishDatabaseQuery(#db2)
		EndIf
		CloseDatabase(#db2)
	EndIf
	
	If i > 0
	  ProcedureReturn 1
	Else
	  ProcedureReturn 0
	EndIf
	
EndProcedure

Edit :

I like the idea to check such a file.
That's why I've created a procedure to keep it general (no matter which project)

Code: Select all

Procedure.i IsSQLiteDB(File.s)
  
  Protected DB, Result = #False
  
  DB = OpenDatabase(#PB_Any, File, "", "", #PB_Database_SQLite)
  
  If DB
    If DatabaseQuery(DB, "SELECT * FROM sqlite_schema")
      FinishDatabaseQuery(DB)
      Result = #True
    EndIf
    CloseDatabase(DB)
  EndIf
  
  ProcedureReturn Result
  
EndProcedure

[infratec]

And you need to check the result of

Code: Select all

NextDatabaseRow(#db2)

like

Code: Select all

If NextDatabaseRow(#db2)

Because if your select returns no entry, you get into trouble.

[doctorized]

@Bisonte: I knew that OpenDatabase() was not enough but never thought to get the schemas to be 100% sure.
Thank you both for your replies!!

[Bisonte]

@Bisonte: I knew that OpenDatabase() was not enough but never thought to get the schemas to be 100% sure.
Thank you both for your replies!!

Table Of Contents
1. Introduction

Every SQLite database contains a single "schema table" that stores the schema for that database.

So it is bullet proof since they changed this behaviour