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

Table of Contents
  1. Introduction
  2. How to add SQL SELECT results into BlitzMax Lists
  3. How to add SQL SELECT COUNT query into BlitzMax variable

Introduction

In response to a question on the forum, I've decided to write a third part to this tutorial, more like a how to then a tutorial proper.

How to add SQL SELECT results into BlitzMax Lists

If you take the example from the previous tutorial and now wants to query out just the FileName and FileSize part of the top 30 smallest files we can do the following:-
    SuperStrict
Import tm.sqlite

Local db:Int 'Database Handle
Local Command:String

Type FileData
  Field FName:String
  Field FSize:String
  Field FullPath:String
End Type
Global FileList:TList=CreateList()


SQLite3_Open("c:/database.db",db)
Command="SELECT * FROM FileExplorer ORDER BY FileSize ASC LIMIT 30"
SQLite3_Exec(db, Command, CallBackToList, Null,Null)

For Local f:FileData=EachIn FileList
  Print "FileName:"+f.FullPath+" FileSize:"+f.FSize
Next

End

Function CallBackToList:Int(user:Byte Ptr, num_cols:Int, value:Byte Ptr Ptr, Column_Name:Byte Ptr Ptr)   

   Local Tmp:FileData=New FileData
   For Local i:Int=0 To num_cols
    Select Upper(String.FromCString(column_name[i]))
    Case "FILENAME"
      Tmp.FName=String.FromCString(Value[i])
    Case "FILESIZE"
      Tmp.FSize=String.FromCString(Value[i])
        Case "FULLPATH"
      Tmp.FullPath=String.FromCString(Value[i])
    EndSelect
   Next   
   ListAddLast FileList, Tmp
   Return 0

End Function

This callback function adds the appropriate datafield to the user defined type FileData which we have created to exactly match the data structure of the SQL Table.

We can then use the normal BlitMax EachIn operator to iterate through the returned data.

How to add SQL SELECT COUNT query into BlitzMax variable

As the SELECT COUNT(X) query will return only one variable, we can easily get it into a Blitzmax variable as follows:-
    SuperStrict
Import tm.sqlite

Local db:Int 'Database Handle
Local Command:String
Global Num:Int

 SQLite3_Open("c:/database.db",db)
Command="SELECT COUNT(*) FROM FileExplorer"
SQLite3_Exec(db, Command, CallBackToVar, Null,Null)

Print "Number of records "+Num

End

 Function CallBackToVar:Int(user:Byte Ptr, num_cols:Int, value:Byte Ptr Ptr, Column_Name:Byte Ptr Ptr)   

   Num=Int(String.FromCString(Value[0]))
   Return 0

End Function

The above works because we know this particular query returns a single record of field COUNT(*) with a value of the record count

Back to Part 1