Page 1 of 1
How to tell if a file is a valid sqlite database?
Posted: Wed Sep 10, 2025 10:20 pm
by 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()
Re: How to tell if a file is a valid sqlite database?
Posted: Thu Sep 11, 2025 12:12 pm
by 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
Re: How to tell if a file is a valid sqlite database?
Posted: Thu Sep 11, 2025 1:27 pm
by 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
Re: How to tell if a file is a valid sqlite database?
Posted: Thu Sep 11, 2025 2:46 pm
by NathanH
Thanks guys, that's perfect.
NathanH
Re: How to tell if a file is a valid sqlite database?
Posted: Mon Sep 15, 2025 2:48 pm
by jPV
Redlion wrote: ↑Thu Sep 11, 2025 1:27 pm
@ 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
And so on with other functions too.
Re: How to tell if a file is a valid sqlite database?
Posted: Tue Sep 16, 2025 3:25 pm
by 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