Very simple DB viewer

Share your advanced PureBasic knowledge/code with the community.
El_Choni
TailBite Expert
TailBite Expert
Posts: 1007
Joined: Fri Apr 25, 2003 6:09 pm
Location: Spain

Very simple DB viewer

Post by El_Choni »

Code updated for 5.20+

Hi,

Maybe works in Linux, too (set #Windows to 0 and try). I hope you find it useful.

Code: Select all

UseSQLiteDatabase()

#Windows = 1

Procedure Error(message$)
  CompilerIf #Windows
    wError = GetLastError_()
    If wError
      *ErrorBuffer = AllocateMemory(1024)
      FormatMessage_(#FORMAT_MESSAGE_FROM_SYSTEM, 0, wError, 0, *ErrorBuffer, 1024, 0)
      message$+Chr(10)+PeekS(*ErrorBuffer)
      FreeMemory(*ErrorBuffer)
    EndIf
  CompilerEndIf
  MessageRequester("Error", message$)
EndProcedure

Procedure ValH(hs$)
  ByteValue = PeekB(@hs$+1)
  If ByteValue>'9'
    If ByteValue>'F'
      ByteValue-87
    Else
      ByteValue-55
    EndIf
  Else
    ByteValue-48
  EndIf
  ch = PeekB(@hs$)
  If ch>'9'
    If ch>'F'
      ch-87
    Else
      ch-55
    EndIf
  Else
    ch-48
  EndIf
  ByteValue+(ch*16)
  ProcedureReturn ByteValue
EndProcedure

#g = 4
#lh = 24

Enumeration
  #DBTitle
  #DBName
  #UserTitle
  #UserName
  #PwdTitle
  #PwdName
  #Connect
  #Panel
EndEnumeration

If UseODBCDatabase()=0:Error("InitDatabase() failed"):EndIf
NewList tables.s()
NewList coltype()
If OpenWindow(0, 0, 0, 640, (#g*4)+(#lh*2), "DB View", #PB_Window_SystemMenu|#PB_Window_ScreenCentered)
  WindowID = WindowID(0)
  CompilerIf #Windows
    SetWindowLong_(WindowID, #GWL_STYLE, GetWindowLong_(WindowID, #GWL_STYLE)|#WS_CLIPCHILDREN)
  CompilerEndIf
  StartDrawing(WindowOutput(0))
    WDataBase = TextWidth(" DataBase ")
    WUser = TextWidth(" User ")
    WPassword = TextWidth(" Password ")
    WConnect =  TextWidth("Reading tables...")
  StopDrawing()
  SWidth = (WindowWidth(0)-(WDataBase+WUser+WPassword)-(#g*7))/3
  TextGadget(#DBTitle, #g, #g, WDataBase, #lh, " DataBase ")
  StringGadget(#DBName, (#g*2)+WDataBase, #g, SWidth, #lh, "MyODBC")
  TextGadget(#UserTitle, (#g*3)+SWidth+WDataBase, #g, WUser, #lh, " User ")
  StringGadget(#UserName, (#g*4)+SWidth+WDataBase+WUser, #g, SWidth, #lh, "root")
  TextGadget(#PwdTitle, (#g*5)+(SWidth*2)+WDataBase+WUser, #g, WPassword, #lh, " Password ")
  StringGadget(#PwdName, (#g*6)+(SWidth*2)+WDataBase+WUser+WPassword, #g, SWidth, #lh, "")
  ButtonGadget(#Connect, #g, (#g*2)+#lh, WConnect, #lh+#g, "Connect")
  Connected = 0
  Repeat
    EventID = WaitWindowEvent()
    Select EventID
      Case #PB_Event_Gadget
        Select EventGadget()
          Case #Connect
            DisableGadget(#Connect, #True)
            If Connected
              Connected = 0
              ResizeWindow(0, #PB_Ignore, #PB_Ignore, WindowWidth(0), (#g*4)+(#lh*2))
              HideGadget(#Panel, #True)
              ClearGadgetItems(#Panel)
              FreeGadget(#Panel)
              ClearList(tables())
              ClearList(coltype())
              SetGadgetText(#Connect, "Connect")
            Else
              If OpenDatabase(0, GetGadgetText(#DBName), GetGadgetText(#UserName), GetGadgetText(#PwdName))
                If DatabaseQuery(0, "SHOW TABLES")
                  SetGadgetText(#Connect, "Reading tables...")
                  Connected = 1
                  ResetList(tables())
                  While NextDatabaseRow(0)
                    AddElement(tables())
                    tables() = GetDatabaseString(0,0)
                  Wend
                  PanelHeight = WindowHeight(0)-((#g*5)+(#lh*2))
                  PanelGadget(#Panel, #g, (#g*4)+(#lh*2), WindowWidth(0), PanelHeight)
                  ForEach tables()
                    ResetList(coltype())
                    If DatabaseQuery(0, "DESCRIBE "+tables())
                      While NextDatabaseRow(0)
                        AddElement(coltype())
                        If GetDatabaseString(0, 1)="longblob"
                          coltype() = 1
                        EndIf
                      Wend
                    Else
                      Error("SQL Query error: "+Chr(10)+Chr(10)+"DESCRIBE "+tables()+Chr(10)+Chr(10)+DatabaseError())
                    EndIf
                    If DatabaseQuery(0, "SELECT * FROM "+tables())
                      AddGadgetItem(#Panel, ListIndex(tables()), tables())
                      ThisGadget = (#Panel+1)+ListIndex(tables())
                      ListIconGadget(ThisGadget, #g, #g, WindowWidth(0)-(#g*4), PanelHeight-(#g*8), DatabaseColumnName(0, 0), 50, #PB_ListIcon_GridLines|#PB_ListIcon_FullRowSelect)
                      cols = DatabaseColumns(0)
                      For i=2 To cols
                        AddGadgetColumn(ThisGadget, i-1, DatabaseColumnName(0, i-1), 50)
                      Next i
                      While NextDatabaseRow(0)
                        Row$ = ""
                        For i=0 To cols-1
                          Select DatabaseColumnType(0, i)
                            Case 1
                              SelectElement(coltype(), i)
                              If coltype()
                                BinaryBlob$ = GetDatabaseString(0,i)
                                lb = Len(BinaryBlob$)
                                For p=1 To lb Step 2
                                  Row$+Chr(ValH(Mid(BinaryBlob$, p, 2)))
                                Next p
                                Row$+Chr(10)
                              Else
                                Row$+Str(GetDatabaseLong(0, i))+Chr(10)
                              EndIf
                            Case 2
                              Row$+GetDatabaseString(0, i)+Chr(10)
                            Case 3
                              Row$+StrF(GetDatabaseLong(0, i), 2)+Chr(10)
                          EndSelect
                        Next i
                        AddGadgetItem(ThisGadget, -1, Left(Row$, Len(Row$)-1))
                      Wend
                    Else
                      Error("SQL Query error: "+Chr(10)+Chr(10)+"SELECT * FROM "+tables()+Chr(10)+Chr(10)+DatabaseError())
                    EndIf
                  Next
                  CloseDatabase(0)
                  SetGadgetText(#Connect, "Close view")
                  ResizeWindow(0, #PB_Ignore,#PB_Ignore,WindowWidth(0), 512)
                Else
                  Error("SQL Query error: "+Chr(10)+Chr(10)+"SHOW TABLES"+Chr(10)+Chr(10)+DatabaseError())
                EndIf
              Else
                Error("Could not connect to DataBase: "+GetGadgetText(3)+Chr(10)+DatabaseError())
              EndIf
            EndIf
            DisableGadget(#Connect, #False)
        EndSelect
      Case #PB_Event_SizeWindow
        If Connected
          PanelHeight = WindowHeight(0)-((#g*5)+(#lh*2))
          ResizeGadget(#Panel, -1, -1, WindowWidth(0), PanelHeight)
          ForEach tables()
            ResizeGadget((#Panel+1)+ListIndex(tables()), -1, -1, WindowWidth(0)-(#g*4), PanelHeight-(#g*8))
          Next
        EndIf
        SWidth = (WindowWidth(0)-(WDataBase+WUser+WPassword)-(#g*7))/3
        ResizeGadget(#DBName, (#g*2)+WDataBase, -1, -1, -1)
        ResizeGadget(#UserTitle, (#g*3)+SWidth+WDataBase, -1, -1, -1)
        ResizeGadget(#UserName, (#g*4)+SWidth+WDataBase+WUser, -1, -1, -1)
        ResizeGadget(#PwdTitle, (#g*5)+(SWidth*2)+WDataBase+WUser, -1, -1, -1)
        ResizeGadget(#PwdName, (#g*6)+(SWidth*2)+WDataBase+WUser+WPassword, -1, -1, -1)
    EndSelect
  Until EventID=#PB_Event_CloseWindow
EndIf
End
I hope someone finds this useful. Bye,
El_Choni
User avatar
Rings
Moderator
Moderator
Posts: 1403
Joined: Sat Apr 26, 2003 1:11 am

Post by Rings »

remember,

works only with Pure 3.89 and above (allocateMemory-Parameters)
and did not work with Microsoft Access ODBC ('SHOW TABLES' is not a recognized keyword )
SPAMINATOR NR.1
El_Choni
TailBite Expert
TailBite Expert
Posts: 1007
Joined: Fri Apr 25, 2003 6:09 pm
Location: Spain

Post by El_Choni »

Thanks, Rings. Actually, I've only tried it with MySQL (MyODBC), which was what I needed it for. And yep, it's for 3.89, forgot to say, but you can workaround that easily (AllocateMemory() is only used in the Error() procedure).

EDIT: looks like you have to use this query:

Code: Select all

"SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Name Like "Temp*" AND
MSysObjects.Type=1;"
instead of "SHOW TABLES" for MsAccess. Haven't tried it.
Last edited by El_Choni on Tue Mar 30, 2004 3:21 pm, edited 1 time in total.
El_Choni
User avatar
Rings
Moderator
Moderator
Posts: 1403
Joined: Sat Apr 26, 2003 1:11 am

Post by Rings »

Yes the 'Allocatememory' is not that real problem.
my conclusion:

SQL is not equal to SQL

from vendor to vendor.
SPAMINATOR NR.1
IdeasVacuum
Always Here
Always Here
Posts: 6385
Joined: Fri Oct 23, 2009 2:33 am
Location: Wales, UK
Contact:

Re: Very simple DB viewer

Post by IdeasVacuum »

What is the equivalent query to 'SHOW TABLES' for SQLite?
IdeasVacuum
If it sounds simple, you have not grasped the complexity.
IdeasVacuum
Always Here
Always Here
Posts: 6385
Joined: Fri Oct 23, 2009 2:33 am
Location: Wales, UK
Contact:

Re: Very simple DB viewer

Post by IdeasVacuum »

[PB5.20LTS WinXPx86]
Hi El_Choni

When I run your code, why can't I see these gadgets:
StringGadget(#DBName)
TextGadget(#UserTitle)
StringGadget(#UserName)
TextGadget(#PwdTitle)
StringGadget(#PwdName)

I can see TextGadget(#DBTitle) and ButtonGadget(#Connect) :?
IdeasVacuum
If it sounds simple, you have not grasped the complexity.
User avatar
skywalk
Addict
Addict
Posts: 3554
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Very simple DB viewer

Post by skywalk »

IdeasVacuum wrote:What is the equivalent query to 'SHOW TABLES' for SQLite?
q$ = "SELECT * FROM sqlite_master WHERE TYPE = 'table'"
Whoa, this is a resurrection post :shock:
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
IdeasVacuum
Always Here
Always Here
Posts: 6385
Joined: Fri Oct 23, 2009 2:33 am
Location: Wales, UK
Contact:

Re: Very simple DB viewer

Post by IdeasVacuum »

Thanks skywalk! The official SQLite website is a bit lacking in my view.......
IdeasVacuum
If it sounds simple, you have not grasped the complexity.
User avatar
skywalk
Addict
Addict
Posts: 3554
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Very simple DB viewer

Post by skywalk »

There is a lot to document for sure.
Start here and use the search box...
master table
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
rsts
Addict
Addict
Posts: 2736
Joined: Wed Aug 24, 2005 8:39 am
Location: Southwest OH - USA

Re: Very simple DB viewer

Post by rsts »

El_Choni wrote:Code updated for 5.20+

Hi,

Maybe works in Linux, too (set #Windows to 0 and try). I hope you find it useful.

I hope someone finds this useful. Bye,
Code is in the original - TOP - post.

Does this work for anyone else? All I get is a small window with a "connect" button. Maybe things have changed since 5.2. I'm using 5.22 LTS x86 on Windows 8.1 64 bit

cheers
RASHAD
PureBasic Expert
PureBasic Expert
Posts: 4094
Joined: Sun Apr 12, 2009 6:27 am

Re: Very simple DB viewer

Post by RASHAD »

Hi rsts
Just for now
Comment the next 5 lines near the end of the snippet

Code: Select all

        ;ResizeGadget(#DBName, (#g*2)+WDataBase, -1, -1, -1)
        ;ResizeGadget(#UserTitle, (#g*3)+SWidth+WDataBase, -1, -1, -1)
        ;ResizeGadget(#UserName, (#g*4)+SWidth+WDataBase+WUser, -1, -1, -1)
        ;ResizeGadget(#PwdTitle, (#g*5)+(SWidth*2)+WDataBase+WUser, -1, -1, -1)
        ;ResizeGadget(#PwdName, (#g*6)+(SWidth*2)+WDataBase+WUser+WPassword, -1, -1, -1)

Egypt my love
rsts
Addict
Addict
Posts: 2736
Joined: Wed Aug 24, 2005 8:39 am
Location: Southwest OH - USA

Re: Very simple DB viewer

Post by rsts »

I sure missed that.

Thanks friend.
Post Reply