Beginner Tutorial: Using SQLite with BlitzMax - Part 2
(c) Assari 2006

Table of Contents

Introduction
Creating the Table to store the File Information
Enumerating files into the database
Using the CallBack to display the Query Result
Saving Database onto Hard Disk
Reading Data from Hard Disk
Error Checking
Utilising the power of SQL
Further thoughts
Resources

Introduction

In the previous tutorial we saw how simple it was to start using an SQLite database from within a BlitzMax program. The only requirements are to have the TeaMonkey SQLite module and some knowledge of SQLite.

In this tutorial we will create a BlitzMax program to store file information from our BlitzMax folder into our SQLite database. We can then use the power of SQL to manipulate the data.

Creating the Table to store the File Information

The first thing we have to do is open a database (as before) and then create a new table to hold 3 relevant pieces of information; The FileName, the Fullpath of the File and its FileSize. We have chose to call this table FileExplorer

We do this via the CREATE TABLE command.

SQLite3_Open(":memory:", db)
Command="CREATE TABLE FileExplorer(FileName TEXT, FullPath TEXT, FileSize INTEGER)"
sqlite3_exec(db, Command, Null, Null, Null)

Enumerating files into the database

We then will need to have a function which will enumerate through the BlitzMax folder and its sub-folders to retrieve the contents of the folders/sub-folders. Thankfully the code archives have one of these (thanks to Robert):-

Function enumFiles(dir:String)

    Local folder:Int=ReadDir(dir)
    Local file:String

    Repeat

        file=NextFile(folder)

        If (file <> ".") And (file <> "..") And (file)
            Local fullPath:String=RealPath(dir+"/"+file)

            If FileType(fullPath)=FILETYPE_DIR
                enumFiles(fullPath)
            Else

                Command="INSERT INTO FileExplorer VALUES('"+File+"','"
                Command :+ FullPath+"',"
                Command :+ FileSize(FullPath)+")"
                sqlite3_exec(db, Command,Null, Null, Null)

            End If
        End If

    Until (file=Null)

    CloseDir folder

End Function

Note the highlighted statements above. Once a file is found, we use the INSERT INTO command to insert the three values File, FullPath and FileSize into our table.

**Note that this is not the fastest way to insert data into an SQLite table. But that is for another day (see TeaMonkey's example on prepared statements here)

Using the CallBack to display the Query Result

The following function was adapted from the example that TeaMonkey had on his SQLite Wrapper Page. What it basically does is print the header at the first pass and in subsequent passes prints the query results a record at a time. Note that we are using the global variable HeaderFlag to print the Header once in our printout.

Function CallBack:Int(user_data:Byte Ptr, num_cols:Int, value:Byte Ptr Ptr, Column_Name:Byte Ptr Ptr)

    If HeaderFlag=False   ' Print the header
        Local h$

        For Local i:Int=0 Until num_cols
            h$ :+ RSet(String.FromCString(column_name[i]),30)+" | "
        Next

        Print ("~n"+h$+"~n")
        HeaderFlag=True
    EndIf

    Local h$
    For Local i:Int=0 Until num_cols

        h$ :+ RSet(String.FromCString(value[i]),30)+" | "
    Next
    Print(h$)

    Return 0
EndFunction

The CallBack is then called during the following statement. (You can call this callback function anything you like)

Command="SELECT * FROM FileExplorer"
sqlite3_exec(db, Command,CallBack, Null, Null)

Our complete program then looks like this (the greyed areas are where the code was as per the previous tutorial):-

SuperStrict
Import tm.sqlite

Global db:Int 'Database Handle
Global Command:String
Global HeaderFlag:Int=False

sqlite3_open(":memory:", db)

Command="CREATE TABLE FileExplorer(FileName TEXT, FullPath TEXT, FileSize INTEGER)"
sqlite3_exec(db, Command, Null, Null, Null)

enumFiles(BlitzMaxPath())

Command="SELECT * FROM FileExplorer"
sqlite3_exec(db, Command,CallBack, Null, Null)

sqlite3_close(db)
End

Function callback:Int(user_data:Byte Ptr, num_cols:Int, value:Byte Ptr Ptr, Column_Name:Byte Ptr Ptr)

    If HeaderFlag=False   ' Print the header
        Local h$

        For Local i:Int=0 Until num_cols
            h$ :+ RSet(String.FromCString(column_name[i]),30)+" | "
        Next

        Print ("~n"+h$+"~n")
        HeaderFlag=True
    EndIf

    Local h$
    For Local i:Int=0 Until num_cols

        h$ :+ RSet(String.FromCString(value[i]),30)+" | "
    Next
    Print(h$)

    Return 0
EndFunction

Function enumFiles(dir:String)

    Local folder:Int=ReadDir(dir)
    Local file:String

    Repeat

        file=NextFile(folder)

        If (file <> ".") And (file <> "..") And (file)
            Local fullPath:String=RealPath(dir+"/"+file)

            If FileType(fullPath)=FILETYPE_DIR
                enumFiles(fullPath)
            Else

                Command="INSERT INTO FileExplorer VALUES('"+File+"','"
                Command :+ FullPath+"',"
                Command :+ FileSize(FullPath)+")"
                sqlite3_exec(db, Command,Null, Null, Null)

            End If
        End If

    Until (file=Null)

    CloseDir folder

End Function

Cutting and Pasting the above code into the MaxIDE and then running the program should yield something that look like the table below:-

Saving Database onto Hard Disk

When we open a database in memory using the SQLite3_Open(":memory:",db) command, the data gets destroyed when we issue the SQLite3_Close(db) command. For a more permanent solution, we need to issue the SQLite3_Open command using a filename.

SQLite will create a new database if the filename we supplied doesn't exist. So if we now re-run the above program with the following Open Command, our data will be permanently stored in the file called database.db

SQLite3_Open("c:/database.db",db)

Go ahead and make the change. Be aware though that this run will take a lot longer than the previous ones as the data has to be written onto the hard disk which has a much slower access speed compared to memory RAM. The Output however, should be identical as before.

If you are using an existing database file from a previous run, you may want to delete the FileExplorer table first before inserting new data. Use the SQL DROP command after the Open command to delete the table.

SQLite3_Exec(db,"DROP FileExplorer",Null,Null,Null)

Reading Data from Hard Disk

Once we have the database data on disk, to view the data we no longer need any CREATE or INSERT commands any more. This shorter version of the above program will give us identical output to the program above.
.

SuperStrict
Import tm.sqlite

Local db:Int 'Database Handle
Local Command:String
Global HeaderFlag:Int=False

SQLite3_Open("c:/database.db",db)

Command="SELECT * FROM FileExplorer"
SQLite3_Exec(db, Command, CallBack, Null,Null)

Sqlite3_Close(db)
End

Function CallBack:Int(user_data:Byte Ptr, num_cols:Int, value:Byte Ptr Ptr, column_name:Byte Ptr Ptr)

    If HeaderFlag=False   ' Print the header
        Local h$

        For Local i:Int=0 Until num_cols
            h$ :+ RSet(String.FromCString(column_name[i]),30)+" | "
        Next

        Print ("~n"+h$+"~n")
        HeaderFlag=True
    EndIf

    Local h$
    For Local i:Int=0 Until num_cols

        h$ :+ RSet(String.FromCString(value[i]),30)+" | "
    Next
    Print(h$)

    Return 0
EndFunction

Error Checking

I have been amiss in not using error checking in my code so far. Reason being I wanted to remove all unnecessary code from the examples. The SQLite functions return codes from which we can check for possible errors. For example, the proper way to use the SQLite3_Open function would be as follows:-

Local rc:int = SQLite3_Open("c:/database.db",db)
If Not rc=SQLITE_OK
    Print("Error: "+String(SQLite3_Errmsg(db)))
    SQLite3_Close(db)
    End
EndIf

The SQLite_ErrMsg(db) function will print out a description of the error encountered and the return code will have the result of the call. The SQLite BlitzMax Module has already pre-defined these return codes.

Some functions such as SQLite3_Exec also returns an error message via a user defined error variable. You can use this as follows:-

Local MyError:Byte Ptr
Local rc:int = SQLite3_Exec("SELECT * FROM xFileExplorer", CallBack, Null, VarPtr(MyError))
If Not rc=SQLITE_OK
    Print "SQLite Error: "+String.FromCString(MyError)
    SQLite3_free(MyError)
    SQLite3_Close(db)
    End
EndIf

Note that we have to free up the memory consumed by the Error Message by using the SQLite3_Free function. In the above example I have deliberately put in xFileExplorer as the table name, generating the error message: SQLite Error: no such table: xFileExplorer

Utilising the power of SQL

Now that we have the data in the SQLite database, we can use the power of SQL to do some wonderful manipulation of our data. Note that SQLite implements only a subset of SQL

For example, change the command text "SELECT * FROM FileExplorer" to "SELECT * FROM FileExplorer ORDER BY FileSize DESC LIMIT 30"

Command="SELECT * FROM FileExplorer ORDER BY FileSize DESC LIMIT 30"
SQLite3_Exec(db, Command, CallBack, Null,Null)

This will give the data sorted by FileSize in Descending order displaying only the top 30 biggest files.

Or something like this

Command="SELECT FileName, FileSize FROM FileExplorer "
Command :+ "WHERE FileName LIKE '%mac%' "
Command :+ "ORDER BY LENGTH(FileName) ASC LIMIT 30 "
SQLite3_Exec(db, Command, CallBack, Null,Null)

This will display only the FileName and FileSize information sorted by the length of the filename in Ascending order and whose name has the text mac in them.

Building Example 3
Compiling:Example 3.bmx
flat assembler version 1.64
3 passes, 3755 bytes.
Linking:Example 3.exe
Executing:Example 3.exe

                      FileName |                       FileSize |

                         mac.H |                           4633 |
                         mac.r |                            369 |
                       ftmac.h |                          10064 |
                       ftmac.c |                          32181 |
                    Fl_mac.cxx |                          67133 |
                 cocoa.macos.m |                          83115 |
                 timer.macos.m |                            822 |
                fl_dnd_mac.cxx |                           2756 |
                system.macos.m |                          13650 |
               appstub.macos.m |                           3457 |
               fl_font_mac.cxx |                           9517 |
              fl_color_mac.cxx |                           3131 |
              system.macos.bmx |                           2238 |
              freejoy.macosx.c |                           7883 |
            Fl_get_key_mac.cxx |                           4193 |
            glgraphics.macos.m |                           8897 |
          blitz_ex.macos.ppc.s |                            957 |
          blitz_ex.macos.x86.s |                            544 |
          blitz_gc.macos.ppc.s |                            100 |
          blitz_gc.macos.x86.s |                            180 |
          fl_set_fonts_mac.cxx |                           6901 |
         fl_draw_image_mac.cxx |                           8459 |
         fl_read_image_mac.cxx |                           3920 |
        blitz_ftoi.macos.x86.s |                            333 |

Process complete

Further thoughts

I hope I have whetted enough of your appetite to explore the weird and wonderful world of SQL via BlitzMax. The no-brainer approach afforded by the SQLite BlitzMax wrapper allows the BlitzMax programmer to use the power of a database engine from within a BlitzMax program without the overheads of having to supply dlls or asking the user to install servers as you would have to do with other database solutions.

The SQLite website has a discussion on "Situations Where SQLite Works Well" (link). Interesting read.

The version of SQLite wrapped by TeaMonkey is version 3.2.2. You can execute this small program to find out the version number

Import tm.sqlite
Print SQLite3_LibVersion()

I have taken the liberty to download the latest version of the SQLite libraries (version 3.3.5). If you are game you can download them here (extract them to user.mod) and use Import user.sqlite instead of Import tm.sqlite. The new version implements more SQL features. I've only used the functions seen in these tutorials so they have not been thoroughly tested at all.

The sqlite.bmx is still the same as written by TeaMonkey except for some new c files added for the new 3.3.5 version. Full credits should still go to TeaMonkey for the wrapping effort.

To know what functions have been wrapped, open the sqlite.bmx file and for explainations, go to the SQLite API page for what the wrapped functions do.

For questions, please ask at the BlitzMax beginner's forum as there are more people there that can help you out. Good Luck with your SQLite adventures.

Resources

http://www.sqlite.org/index.html - The SQLite Homepage
http://freshmeat.net/articles/view/1428/ - SQLite Tutorial
http://www.sqlite.org/capi3ref.html - C/C++ Interface For SQLite Version 3
http://www.sqlite.org/lang.html - SQL As Understood By SQLite
http://www.techonthenet.com/sql/index.php - SQL syntax
http://sqlitebrowser.sourceforge.net/ - SQLite Database Browser is a freeware, public domain, open source visual tool used to create, design and edit database files compatible with SQLite
http://www.pyroplay.de/sqliter.html - An OOP wrapper for TeaMonkey's BlitzMax Wrapper
http://www.tinyminions.co.uk/node/9 - TeaMonkeys SQLite Wrapper Site

Goto Next Tutorial, Back to Part 1,