Example of Filling a ListIconGadget from database

cseymour · Post by **cseymour** » Fri Jun 09, 2006 1:13 am

Hi All,
I am trying to fill a ListIconGadget from a SQLLite3 table, but seem to be spinning my wheels. Does anyone know of a good example I could follow to run a sql squery then put the results in a IconList gadget?

Thanks

Chris Seymour

netmaestro · Post by **netmaestro** » Fri Jun 09, 2006 1:15 am

Are you using the PBOSL SQLite3 library? That's what I use and I can post source for my filebrowser if it would be of use to you.

netmaestro · Post by **netmaestro** » Fri Jun 09, 2006 1:29 am

Well, here it is anyway, it may be of some use to you even if you aren't using PBOSL:

Code: Select all

;===============================================
; Program:         MovieBrowser.pb
; Author:          netmaestro
; Date:            March 27, 2006
; Target OS:       Microsoft Windows All
; Target Compiler: PureBasic Version 4.0
;===============================================

Enumeration
  #MainWindow 
  #IMDBWindow
  #ListGadget
  #btGadgetBack
  #btGadgetForward
  #LVM_SETEXTENDEDLISTVIEWSTYLE = #LVM_FIRST + 54 
  #LVM_GETEXTENDEDLISTVIEWSTYLE = #LVM_FIRST + 55 
EndEnumeration

Global ListIconGadget.l, Buffer1.l, Buffer2.l, lvi.LV_ITEM, updown.l, lastcol.l, ccount.l
Buffer1 = AllocateMemory(512) 
Buffer2 = AllocateMemory(512) 

Structure PB_ListIconItem 
  UserData.l 
EndStructure 

Declare CompareFunc(*item1.PB_ListIconItem, *item2.PB_ListIconItem, lParamSort) 
Declare UpdatelParam()
Declare ColumnClickCallback(hwnd, uMsg, wParam, lParam) 

Global MyPointer.l

If CreateFile(22, "moviestmp.db")
  WriteData(22, ?db1, ?db2-?db1)
  CloseFile(22)
Else
  MessageRequester("Error","Cannot Create Database")
EndIf

If ExamineDirectory(0,GetCurrentDirectory(),"Sqlite3.dll") = 0
  If CreateFile(22, "SqLite3.dll")
    WriteData(22, ?dll, ?dllend-?dll)
    CloseFile(22)
  Else
    MessageRequester("Error","Cannot Create DLL file")
  EndIf
EndIf

If SQLite3_InitLib("SqLite3.dll") = #False
  MessageRequester("MovieBrowser", SQLite3_GetLastMessage())
  End
EndIf

dbHandler = SQLite3_OpenDatabase("moviestmp.db")

If dbHandler = 0
  MessageRequester("MovieBrowser", SQLite3_GetLastMessage())
  End
EndIf

RS.SQLite3_Recordset 

If SQLite3_GetRecordset("Select Count(*) As CountOfRecords From Table1", dbHandler, @RS )
  If RS\Handle
    If SQLite3_GetRecordsetValueByName("CountOfRecords", @RS)
      totrecords.s = RS\sValue 
    Else
      MessageRequester("MovieBrowser", SQLite3_GetLastMessage())
    EndIf
  EndIf
Else
  MessageRequester("MovieBrowser", SQLite3_GetLastMessage())
EndIf

SQLite3_GetRecordset("Select * From Table1 order by name asc", dbHandler, @RS )
If RS\Handle
  OpenWindow(#MainWindow,0,0,800,600,"MovieBrowser",#PB_Window_SystemMenu|#PB_Window_ScreenCentered)
  CreateGadgetList(WindowID(#MainWindow))
  ListIconGadget = ListIconGadget(#ListGadget,0,0,WindowWidth(#MainWindow),WindowHeight(#MainWindow),totrecords,40,#PB_ListIcon_FullRowSelect|#PB_ListIcon_AlwaysShowSelection)
  AddGadgetColumn(#ListGadget,1,"Title",244)
  AddGadgetColumn(#ListGadget,2,"Star 1",152)
  AddGadgetColumn(#ListGadget,3,"Star 2",152)
  AddGadgetColumn(#ListGadget,4,"Genre ",130)
  AddGadgetColumn(#ListGadget,5,"IMDB #",60)
  ccount.l = 0
  While RS\EOF = 0
    ccount+1
    os.s = RSet(Str(ccount),5," ")+Chr(10)
    SQLite3_GetRecordsetValueByName("name", @RS)
    os+RS\sValue+Chr(10) 
    SQLite3_GetRecordsetValueByName("star1", @RS)
    os+RS\sValue+Chr(10) 
    SQLite3_GetRecordsetValueByName("star2", @RS)
    os+RS\sValue+Chr(10) 
    SQLite3_GetRecordsetValueByName("genre", @RS)
    os+RS\sValue+Chr(10) 
    SQLite3_GetRecordsetValueByName("imdb", @RS)
    os+RS\sValue
    AddGadgetItem(#ListGadget, -1, os)
    SQLite3_RecordsetMoveNext(RS)
  Wend
  SQLite3_CloseDatabase(dbHandler)
  SQLite3_End()
  DeleteFile("SqLite3.dll")
  DeleteFile("moviestmp.db")
  For i = 0 To ccount Step 4
    SetGadgetItemColor(#ListGadget,i,#PB_Gadget_BackColor,#White)
    SetGadgetItemColor(#ListGadget,i+1,#PB_Gadget_BackColor,#White)
    SetGadgetItemColor(#ListGadget,i+2,#PB_Gadget_BackColor,RGB(215,220,215))
    SetGadgetItemColor(#ListGadget,i+3,#PB_Gadget_BackColor,RGB(215,220,215))
  Next  
Else
  MessageRequester("MovieBrowser", SQLite3_GetLastMessage())
  End
EndIf
 
updown  = 1 
lastcol = 0 
UpdatelParam() 
SetWindowCallback(@ColumnClickCallback()) 

Quit=0
Repeat
  ev=WaitWindowEvent()
  Select ev
    Case #PB_Event_CloseWindow
      Quit=1
    Case #PB_Event_Gadget
      If EventType()=#PB_EventType_LeftDoubleClick
        url.s = "http://www.imdb.com/title/tt" + GetGadgetItemText(#ListGadget,GetGadgetState(#ListGadget),5) + "/"
        RunProgram("iexplore.exe",url,GetCurrentDirectory())
      EndIf
  EndSelect
Until Quit=1

End

DataSection
  db1: IncludeBinary "d:\test\movies5.db"
  db2:
  dll: IncludeBinary "d:\test\sqlite3.dll"
  dllend:
EndDataSection

Procedure CompareFunc(*item1.PB_ListIconItem, *item2.PB_ListIconItem, lParamSort) 
  result = 0 
  lvi\iSubItem = lParamSort 
  lvi\pszText = Buffer1 
  lvi\cchTextMax = 512 
  lvi\Mask = #LVIF_TEXT 
  SendMessage_(ListIconGadget, #LVM_GETITEMTEXT, *item1\UserData, @lvi) 
  lvi\pszText = Buffer2 
  SendMessage_(ListIconGadget, #LVM_GETITEMTEXT, *item2\UserData, @lvi) 
  Seeker1 = Buffer1 
  Seeker2 = Buffer2 
  done = 0 
  While done=0 
    char1 = Asc(UCase(Chr(PeekB(Seeker1)))) 
    char2 = Asc(UCase(Chr(PeekB(Seeker2)))) 
    result = (char1-char2)*updown 
    If result<>0 Or (Seeker1-Buffer1)>511
      done = 1 
    EndIf 
    Seeker1+1 
    Seeker2+1 
  Wend 
  ProcedureReturn result 
EndProcedure 

Procedure UpdatelParam() 
  Protected i.l, lTmp.l, lRecs.l, lvi.LV_ITEM 
  lRecs = SendMessage_(ListIconGadget, #LVM_GETITEMCOUNT, 0, 0) 
  For i = 0 To lRecs - 1 
    SetGadgetItemData(#ListGadget, i, i) 
  Next 
 
EndProcedure 

Procedure ColumnClickCallback(hwnd, uMsg, wParam, lParam) 
  result = #PB_ProcessPureBasicEvents 
  Select uMsg 
    Case #WM_NOTIFY 
      *msg.NMHDR = lParam 
      If *msg\hwndFrom = ListIconGadget And *msg\code = #LVN_COLUMNCLICK 
        *pnmv.NM_LISTVIEW = lParam 
        If lastcol<>*pnmv\iSubItem 
          updown = 1 
        EndIf 
        SendMessage_(ListIconGadget, #LVM_SORTITEMS, *pnmv\iSubItem, @CompareFunc()) 
        UpdatelParam() 
        UpdateWindow_(ListIconGadget) 
        lastcol = *pnmv\iSubItem 
        updown = -updown 
        
        For i = 0 To ccount Step 4
          SetGadgetItemColor(#ListGadget,i,#PB_Gadget_BackColor,#White)
          SetGadgetItemColor(#ListGadget,i+1,#PB_Gadget_BackColor,#White)
          SetGadgetItemColor(#ListGadget,i+2,#PB_Gadget_BackColor,RGB(215,220,215))
          SetGadgetItemColor(#ListGadget,i+3,#PB_Gadget_BackColor,RGB(215,220,215))
        Next  

      EndIf 
    Case #WM_SIZE 
      If hwnd = WindowID(0) And IsIconic_(hwnd)=0 
        WindowWidth = lParam & $FFFF 
        WindowHeight = lParam>>16 
        ResizeGadget(0, 0, 0, WindowWidth, WindowHeight) 
        result = 1 
      EndIf 
  EndSelect 

  ProcedureReturn result 
EndProcedure

cseymour · Post by **cseymour** » Fri Jun 09, 2006 4:26 am

Thanks.

That's much more than I was looking for.

Thanks so much.

Much Appreciated

netmaestro · Post by **netmaestro** » Fri Jun 09, 2006 5:57 am

About half of the program is dedicated to enabling the ListIcon gadget to be sorted with columnheader clicks. If you take that part out there really isn't a lot going on. Just opening the database, executing a query and filling the ListIcon gadget with the results.

cseymour · Post by **cseymour** » Fri Jun 09, 2006 3:33 pm

Hi netmaestro,
Thanks again for the example. I am still going through it. One question was with the data section. When you include the binary file does that info actually get stored in the executable?

Just so I understand -- you create the file locally in the filesystem from the data in the exe?

Thanks again for all your help. This has been a real learning experience for me.

Cheers.

Chris

netmaestro · Post by **netmaestro** » Fri Jun 09, 2006 4:16 pm

Yes, the exe is totally portable so it will work on any computer anywhere without worry that the data is available. When we buy new movies, I just update the database using an update module in the same folder and recompile the browser. Not a hugely professional approach but deadly effective for our purposes.

jroad · Post by **jroad** » Thu Aug 24, 2006 2:18 am

Thanks netmaestro for posting this example. I am finding it very helpful in learning my way around PB and SQLite!

Fangbeast · Post by **Fangbeast** » Wed Nov 08, 2006 1:53 pm

G'day netMaestro. Finally found this sorting code of yours but am having a crash at the following line. NamesList is my gadget handle

SendMessage_(NamesList, #LVM_GETITEMTEXT, *item2\UserData, @lvi)

I get a compiler message "Pointer is NULL"? Don't know what to do about it.

Should this structure be declared as Global?

Structure PB_ListIconItem
UserData.l
EndStructure

netmaestro · Post by **netmaestro** » Wed Nov 08, 2006 2:10 pm

It's actually mostly Fred's code, he posted it not long after PureBasic 4 went Beta, but I think it was in the Bug Section and got deleted. I just added the alternating rowcoloring code to it. Anyway, make sure all the vars from this line are declared global:

Code: Select all

Global ListIconGadget.l, Buffer1.l, Buffer2.l, lvi.LV_ITEM, updown.l, lastcol.l, ccount.l

If the problem was related to an unrecognized structure you'd get the error "Variable lvi doesn't have a structure", so that probably isn't it. If you've got all those variables declared global, I'm somewhat mystified as I've dropped that code into quite a few programs and it "just works". If your problem persists, post some code and I'll try to beat srod to solving it.

Fangbeast · Post by **Fangbeast** » Thu Nov 09, 2006 2:49 am

No, don't beat up srod, he would only enjoy it.

Found the problem and it's not simple to solve. I've already subclassed the ListIconGadget for heading colours and it intercepts the #WM_Notify message so another callback just interferes with it. DAMN, BUGGER, BUM!!

(Not simple for me, anyway).

I asked Freak for native PB colouring but he said he has to see if it can be done cross-platform. I think that will take a while.

Fangbeast · Post by **Fangbeast** » Wed Jun 20, 2007 8:24 am

Sir Net of Maestro, does your code do fancy little up/down arrows in the headers to indicate sort direction perchance?

netmaestro · Post by **netmaestro** » Thu Jun 21, 2007 4:09 am

It doesn't, but I'll make that tomorrow's little fun job.

Fangbeast · Post by **Fangbeast** » Thu Jun 21, 2007 7:34 am

Aren't you supposed to be working on miracles right now instead of talking to us peasants??? (suspicious look)