How to tell if a file is a valid sqlite database?

[NathanH]

Hi,

I thought that I could trap an error in trying to open a file that isn't a database with the following code. It still gives a Hollywood error message though. Am I doing something wrong or is there another way to test the file? Thanks.

NathanH

Code: Select all

@DISPLAY 1, {hidden=True}
@REQUIRE "sqlite3"

ExitOnError(False)
	db=sqlite3.open("S:Startup-Sequence")
		err=GetLastError()
		If err<>0
			DebugPrint("error in sqlite3.open", err)
			End()
		EndIf
ExitOnError(True)

ExitOnError(False)
	For row In db:rows("SELECT Test FROM tblTest;")
		test=row[0]
	Next
	err=GetLastError()
	If err<>0
		DebugPrint("error in db:rows", err)
		End()
	EndIf
ExitOnError(True)
	db:close()

[Redlion]

@ NathanH

If you look at an SQL Lite file in a text editor, you will find that it starts with "SQLite format 3" Just check for that, if its there - load it, if is not , its not an SQL file.

Cheers
Leo

[Redlion]

@ NathanH

Here is some code I use to check a SQL file.

Code: Select all

/*** Make sure we have at least Hollywood 9.0! *************************************************************/
@VERSION 9,0

/*** Information about this app ****************************************************************************/
@APPTITLE "Is SQL"
@APPVERSION "$VER: 1.0.0  (11.09.25)"
@APPAUTHOR "Leo den Hollander"
@APPDESCRIPTION "Is File a SQLite3 File"

@REQUIRE "RapaGUI"
@REQUIRE "sqlite3"
 
EscapeQuit(True)
SetDefaultEncoding(#ENCODING_ISO8859_1, #ENCODING_ISO8859_1)
 
;* setup GUI **************************************************************************************
moai.CreateApp([[
<?xml version="1.0" encoding="iso-8859-1"?>
<application id="app">
	<window title="SQL Viewer" id="win" width="1280" height="768" margin="1">
		<vgroup id="rootgroup" color="#607B99">
			<vspace height="3"/>
			<hgroup>
				<hspace width="3"/>
				<label fontsize="12" fontstyle="bold"> Load Database : </label>
				<popfile id="dbfile" fontsize="12" fontstyle="bold" notify="file"/>
				<hspace width="3"/>
			</hgroup> 
			<hline/>
			<vspace height="3"/>
			<hgroup>
				<hspace width="3"/>
				<label fontsize="12" fontstyle="bold"> Database Name : </label>
				<text id="dbname" fontsize="12"></text>
				<label fontsize="12" fontstyle="bold"> Table Name : </label>
				<text id="tname" fontsize="12"></text>
				<label fontsize="12" fontstyle="bold"> No. of Fields : </label>
				<text id="fnumber" fontsize="12"></text>
				<label fontsize="12" fontstyle="bold"> No. of Records : </label>
				<text id="recnumber" fontsize="12"></text>
			</hgroup>
			<vgroup>      
				<vgroup id="container" frame="true">
					<rectangle id ="blankspace" height="2"/>
				</vgroup>
				<vspace height="2"/>
			</vgroup>  
		</vgroup>
	</window>	
   
</application>
]])
 
;* Get Database records ****************************************************************************
Function p_GetDB()
  Record = 0
  RD = {}
  db = sqlite3.open(DB$)
  For row In db:nrows("SELECT rowid, * FROM " .. tablename$)
	RD[0] = row.rowid
	For Info = 1 To Count
	  RD[Info] = row["" ..field[Info-1].. ""]
	  RD[Info] = ReplaceStr(RD[Info], Chr(10), "")
	  RD[Info] = ReplaceStr(RD[Info], Chr(13), "")
	Next
	pos = moai.DoMethod("dblist", "Insert", "Bottom", Unpack(RD))
	Record = Record + 1
  Next
  db:close()
  moai.Set("recnumber", "Text", Record)
  LastRecord = Val(Record)  
EndFunction

Function Check_File(DB$)
	NotSQL = 0
	OpenFile(1,DB$)
		If ReadString(1, 15) <> "SQLite format 3"
			NotSQL = 1
		EndIf
	CloseFile(1)
EndFunction
  
;* Display Database info ***************************************************************************
Function p_ListDB()
  If moai.HaveObject("dblist") = 1
	moai.DoMethod("container", "Remove", "dblist")
	moai.FreeObject("dblist")
	moai.CreateObject("<hline id=\"blankspace\" ></hline>", "container")
	moai.DoMethod("container", "Initchange")
	moai.DoMethod("container", "Append", "blankspace")
	moai.DoMethod("container", "Exitchange", False)
  EndIf
  DB$ = moai.Get("dbfile","File")
  If DB$ = ""
	result = moai.Request(" NO DATABASE SELECTED ! ",
			" Please select a Database to continue. ", "OK|CANCEL")
  Else
	Check_File(DB$) 
	  If NotSQL = 1
		result = moai.Request(" NOT A DATABASE FILE ! ",
			" Please select a Database File to continue. ", "OK|CANCEL")
	  Else
		db = sqlite3.open(DB$)
		For row In db:rows("SELECT name FROM sqlite_master WHERE type='table';")
			tablename$ = row[0]
		Next   
		db:close()
	   
		moai.Set("tname", "Text", tablename$) 
		moai.Set("dbname", "Text", FilePart(DB$))
		   
		count = 0
		field = {}
		db = sqlite3.open(DB$)
	 
		For row In db:nrows("PRAGMA table_info("..tablename$..");")
			field[row.cid] = row.name
			count = count + 1
		Next
		db:close()
	 
		xml$ = "<column title= \"" .. "RowID" .. "\"></column>"
		moai.Set("recnumber", "text", count) 
		For t = 0 To count-1
		  field[t] = TrimStr(field[t]," ",True)
		  field[t] = TrimStr(field[t]," ",False)
		  field[t] = ReplaceStr(field[t]," TEXT","",True,1)
		  field[t] = ReplaceStr(field[t]," Text","",True,1)
		  xml$ = xml$ .. "<column title= \"" ..field[t].. "\"  editable=\"true\" ></column>"
		Next
	  
		moai.CreateObject("<listview id=\"dblist\" vrules=\"true\" hrules=\"true\" notify=\"ValueChange\" alternate=\"True\">"..xml$.."</listview>", "container")
		moai.DoMethod("container", "Initchange")
		moai.DoMethod("container", "Remove", "blankspace")
		moai.FreeObject("blankspace")
		moai.DoMethod("container", "Append", "dblist")
		moai.DoMethod("container", "Exitchange", False)
		moai.Set("dblist", "Fontsize","12")
		p_GetDB() 
		moai.Set("fnumber", "Text", StrStr(count-1))    
	  EndIf
	Endif
EndFunction
  
;* Handles all incoming events *********************************************************************
Function p_EventFunc(msg)
Switch msg.action
	Case "RapaGUI":
 
		Switch msg.attribute
			 
			Case "File":
				p_ListDB()
 
			
			Case "CloseRequest"
				If msg.id = "win"
					End				
				EndIf 
		   
		EndSwitch
   
	EndSwitch
EndFunction
	   
;* listen to these events **************************************************************************
InstallEventHandler({RapaGUI = p_EventFunc})
 
;* main loop ***************************************************************************************
Repeat
	WaitEvent
Forever

Hope that helps

Cheers
Leo

[NathanH]

Thanks guys, that's perfect.

NathanH

[jPV]

@ NathanH

Here is some code I use to check a SQL file.

Sorry about nitpicking your code, but I think you should use some Local variables and not expose all variables from functions to Global

For example, the p_GetDB() function could rather look like this:

Code: Select all

Function p_GetDB()
  Local Record = 0
  Local RD = {}
  Local db = sqlite3.open(DB$)
  For Local row In db:nrows("SELECT rowid, * FROM " .. tablename$)
	RD[0] = row.rowid
	For Local Info = 1 To Count   
...

Because none of those variables are used outside of this one function.

And using a Global variable instead of a return value with Check_File() looks a bit clumsy, I'd do it like this:

Code: Select all

Function p_Check_File(DB$)
	Local NotSQL = 0
	OpenFile(1,DB$)
		If ReadString(1, 15) <> "SQLite format 3"
			NotSQL = 1
		EndIf
	CloseFile(1)
	Return(NotSQL)
EndFunction 

Code: Select all

If p_Check_File(DB$) = 1
...

And so on with other functions too.

[Redlion]

@JPV

Yes I would generally agree with you, but the example was cut down from a much larger program. I had to leave out a lot and had to tweak it to work as an simple example. So it was not as elegant as it could be.

Cheers
Leo