Problems using Database features

TerryHough · Post by **TerryHough** » Tue Jul 22, 2003 6:49 pm

I am stymied trying to use the Database features of PB.

For example, I have an Access database (db1.mdb) that is intact and useable from Access. But I cannot open it with PB.

The db1.db file exists in the correct directory, I am certain of the user id and password entries, but I cannot open it. I have manually created the DSN.

The following code (modified from another post to make current) will not open the file.

Code: Select all

;
; Little Database Explorer written by AlphaSND
;
; Fully Resizeable GUI - Easy to do
;
Procedure ResizeGUI()
  ResizeGadget(0,  10, 10, WindowWidth()-25, WindowHeight()-75)
  ResizeGadget(1,  10, WindowHeight()-51, 100, 22)
  ResizeGadget(2, 110, WindowHeight()-55, WindowWidth()-175, 22)
  ResizeGadget(3, WindowWidth()-65, WindowHeight()-55, 50, 22)
EndProcedure

Procedure WindowCallback(Window, Message, wParam, lParam)
  If Message = #WM_SIZE
    ResizeGUI()
    ProcedureReturn -1 ; Tell PureBasic internal handler than the message is already processed
  EndIf
EndProcedure

If InitDatabase() = 0
  MessageRequester("Error", "ODBC v3.0+ can't be opened.", 0)
  End
ElseIf OpenDatabase(0, "db1.mdb", "admin", "")
  If OpenWindow(0, 0, 100, 640, 480, #PB_Window_SystemMenu | #PB_Window_SizeGadget | #PB_Window_MinimizeGadget| #PB_Window_MaximizeGadget, "PureBasic DataBase Explorer - v1.0")
    If CreateGadgetList(WindowID())
      ListIconGadget(0, 0, 0, 0, 0, "",0)
      TextGadget    (1, 0, 0, 0, 0, "SQL Request:")
      StringGadget  (2, 0, 0, 0, 0, "Select * from lcr_employe")
      ButtonGadget  (3, 0, 0, 0, 0, "Go !")
      ResizeGUI()
    EndIf
    SetWindowCallback(@WindowCallback())
    ActivateGadget(2)
    Repeat
      EventID = WaitWindowEvent()
      Select EventID
      Case #PB_EventGadget
        Select EventGadgetID()
        Case 3
          Gosub ExecuteQuery
        EndSelect
      EndSelect
    Until EventID = #PB_EventCloseWindow
  EndIf
Else
  MessageRequester("Info","Cannot open database",#MB_ICONINFORMATION)
EndIf
End

ExecuteQuery:
#LVM_SETEXENDEDLISTVIEWSTYLE = #LVM_FIRST+54
#LVS_EX_GRIDLINES = 1
#LVS_EX_FULLROWSELECT = $20
If DatabaseQuery(GetGadgetText(2))
  FreeGadget(0)
  *LI = ListIconGadget(0,  10, 10, WindowWidth()-20, WindowHeight()-75,DatabaseColumnName(1), 80)
  SendMessage_(*LI, #LVM_SETEXENDEDLISTVIEWSTYLE, #LVS_EX_GRIDLINES, -1)
  SendMessage_(*LI, #LVM_SETEXENDEDLISTVIEWSTYLE, #LVS_EX_FULLROWSELECT, -1)
  NbDatabaseColumns = DatabaseColumns()
  Dim ColumnType.b(NbDatabaseColumns)
  ColumnType(1) = DatabaseColumnType(1)
  For k=2 To NbDatabaseColumns
    AddGadgetColumn(0, k, DatabaseColumnName(k), 80)
    ColumnType(k) = DatabaseColumnType(k)
  Next
  CurrentLine = 0
  While (NextDatabaseRow())
    Content$ = ""
    For k=1 To NbDatabaseColumns
      Select ColumnType(k)
      Case 1
        Content$+Str(GetDatabaseLong(k))
      Case 2
        Content$+GetDatabaseString(k)
      Case 3
        Content$+StrF(GetDatabaseFloat(k),10)
        Default
        Content$+"UNKNOW"
      EndSelect
      Content$+Chr(10)
    Next
    AddGadgetItem(0, CurrentLine, Content$, 0)
    CurrentLine+1
  Wend
Else
  MessageRequester("Error", "Bad Query", 0)
EndIf
Return

Thanks in advance to anyone that can get me going.

Terry

Num3 · Post by **Num3** » Tue Jul 22, 2003 7:08 pm

Question one:

Have you defined the database dns in the ODBC Data Source Administrator ? (usually on control panel)

You need to define something like this:

Goto USER DNS tab, select the access driver, name your link has "test" and select the database.

You see purebasic does not open the database directly, it needs a predefined "link" in the ODBC.

Then in Purebasic instead of

OpenDatabase(0, "db1.mdb", "admin", "")

use

OpenDatabase(0, "test", "admin", "")

TerryHough · Post by **TerryHough** » Tue Jul 22, 2003 8:07 pm

@Num3
Thanks, you cleared up a bit of misconception on my part with your reply.

I am still not successful though.

Oddly, if I use the ODBC Microsoft Access Setup to Create a database
then I can access it OK even though it is empty. The example PB code
above isn't very good, but I can see what is happening anyway.

Unfortunately, I still cannot access the database file created with Access
97 even when I create the DSN properly and then do the PB code as you
suggested.

Although Access 97 can read the file without problems, the OBDC
Microsoft Access Setup program cannot Repair or Compact the file
and reports a "Not a valid password" message instead. It occurs now
even though the user and password in the setup are blank.

This must be the root of my problems using PB on the file, but I must
admit I have no idea how to correct the problem or continue.

Thanks for your help.

Edited later:
Seems to be just that one file. I can access several others, so I am off
to learn more about SQL commands.

Terry

Num3 · Post by **Num3** » Tue Jul 22, 2003 10:25 pm

Please check: viewtopic.php?p=30778

Happy coding

GedB · Post by **GedB** » Tue Jul 22, 2003 11:25 pm

Terry,

Below is the code that I was able to get working. A few points:

1) You use a DSN name rather than a file name. I see from above that your through that part already.

2) Unless you've set up security on the database yourself (unlikely) you should leave Username and Password as empty strings.

3) The database columns range from 0 to DatabaseColumns() -1

4) Not sure what was going wrong with your callback. I moved the resize into the main loop.

Code: Select all

;
; Little Database Explorer written by AlphaSND
;
; Fully Resizeable GUI - Easy to do
;
Procedure ResizeGUI()
  ResizeGadget(0,  10, 10, WindowWidth()-25, WindowHeight()-75)
  ResizeGadget(1,  10, WindowHeight()-51, 100, 22)
  ResizeGadget(2, 110, WindowHeight()-55, WindowWidth()-175, 22)
  ResizeGadget(3, WindowWidth()-65, WindowHeight()-55, 50, 22)
EndProcedure

If InitDatabase() = 0
  MessageRequester("Error", "ODBC v3.0+ can't be opened.", 0)
  End
ElseIf OpenDatabase(0, "db1dsn", "", "")
  If OpenWindow(0, 0, 100, 640, 480, #PB_Window_SystemMenu | #PB_Window_SizeGadget | #PB_Window_MinimizeGadget| #PB_Window_MaximizeGadget, "PureBasic DataBase Explorer - v1.0")
    If CreateGadgetList(WindowID())
      ListIconGadget(0, 0, 0, 0, 0, "",0)
      TextGadget    (1, 0, 0, 0, 0, "SQL Request:")
      StringGadget  (2, 0, 0, 0, 0, "Select * from lcr_employe")
      ButtonGadget  (3, 0, 0, 0, 0, "Go !")
      ResizeGUI()
    EndIf
    ActivateGadget(2)
    Repeat
      EventID = WaitWindowEvent()
      Select EventID
      Case #PB_EventGadget
        Select EventGadgetID()
        Case 3
          Gosub ExecuteQuery
        EndSelect
      Case #WM_Size
        ResizeGUI()
      EndSelect
    Until EventID = #PB_Event_CloseWindow
  EndIf
Else
  MessageRequester("Info","Cannot open database",#MB_ICONINFORMATION)
EndIf
End

ExecuteQuery:
#LVM_SETEXENDEDLISTVIEWSTYLE = #LVM_FIRST+54
#LVS_EX_GRIDLINES = 1
#LVS_EX_FULLROWSELECT = $20
If DatabaseQuery(GetGadgetText(2))
  FreeGadget(0)
  *LI = ListIconGadget(0,  10, 10, WindowWidth()-20, WindowHeight()-75,DatabaseColumnName(1), 80)
  SendMessage_(*LI, #LVM_SETEXENDEDLISTVIEWSTYLE, #LVS_EX_GRIDLINES, -1)
  SendMessage_(*LI, #LVM_SETEXENDEDLISTVIEWSTYLE, #LVS_EX_FULLROWSELECT, -1)
  NbDatabaseColumns = DatabaseColumns()
  Dim ColumnType.b(NbDatabaseColumns)
  ColumnType(1) = DatabaseColumnType(1)
  For k=0 To NbDatabaseColumns - 1
    AddGadgetColumn(0, k, DatabaseColumnName(k), 80)
    ColumnType(k) = DatabaseColumnType(k)
  Next
  CurrentLine = 0
  While (NextDatabaseRow())
    Content$ = ""
    For k=0 To NbDatabaseColumns - 1
      Select ColumnType(k)
      Case 1
        Content$+Str(GetDatabaseLong(k))
      Case 2
        Content$+GetDatabaseString(k)
      Case 3
        Content$+StrF(GetDatabaseFloat(k),10)
        Default
        Content$+"UNKNOW"
      EndSelect
      Content$+Chr(10)
    Next
    AddGadgetItem(0, CurrentLine, Content$, 0)
    CurrentLine+1
  Wend
Else
  MessageRequester("Error", "Bad Query", 0)
EndIf
Return

TerryHough · Post by **TerryHough** » Wed Jul 23, 2003 6:29 pm

Thanks GedB.

I can only take credit for messing up Fred's code while trying to get it
running with vs3.72.

Here is my final code that works properly.

Code: Select all

; Little Database Explorer written by AlphaSND 
; Modified by TerryHough 
; Fully Resizeable GUI - Easy to do 
; 
Procedure ResizeGUI() 
  ResizeGadget(0,  10, 10, WindowWidth()-25, WindowHeight()-75) 
  ResizeGadget(1,  10, WindowHeight()-51, 100, 22) 
  ResizeGadget(2, 110, WindowHeight()-55, WindowWidth()-175, 22) 
  ResizeGadget(3, WindowWidth()-65, WindowHeight()-55, 50, 22) 
EndProcedure 

If InitDatabase() = 0 
  MessageRequester("Error", "ODBC v3.0+ can't be opened.", 0) 
  End 
ElseIf OpenDatabase(0, "TEST", "", "") 
  If OpenWindow(0, 0, 100, 640, 480, #PB_Window_ScreenCentered | #PB_Window_SystemMenu | #PB_Window_SizeGadget | #PB_Window_MinimizeGadget| #PB_Window_MaximizeGadget, "PureBasic DataBase Explorer - v1.0") 
    If CreateGadgetList(WindowID()) 
      ListIconGadget(0, 0, 0, 0, 0, "",0) 
      TextGadget    (1, 0, 0, 0, 0, "SQL Request:") 
      StringGadget  (2, 0, 0, 0, 0, "Select * from TEST") 
      ButtonGadget  (3, 0, 0, 0, 0, "Go !") 
      ResizeGUI() 
    EndIf 
    ActivateGadget(2) 
    Repeat 
      EventID = WaitWindowEvent() 
      Select EventID 
      Case #PB_EventGadget 
        Select EventGadgetID() 
        Case 3 
          Gosub ExecuteQuery 
        EndSelect 
      Case #WM_Size 
        ResizeGUI() 
      EndSelect 
    Until EventID = #PB_Event_CloseWindow 
  EndIf 
Else 
  MessageRequester("Info","Cannot open database",#MB_ICONINFORMATION) 
EndIf 
End 

ExecuteQuery: 
#LVM_SETEXENDEDLISTVIEWSTYLE = #LVM_FIRST+54 
#LVS_EX_GRIDLINES = 1 
#LVS_EX_FULLROWSELECT = $20 
If DatabaseQuery(GetGadgetText(2)) 
  NbDatabaseColumns = DatabaseColumns() 
  Dim ColumnType.b(NbDatabaseColumns) 
  FreeGadget(0) 
  ColumnType(0) = DatabaseColumnType(0) 
  ColumnDescription.s = DatabaseColumnName(0)+"  - "
  Select ColumnType(0)
    Case 1
      ColumnDescription + "l "
    Case 2
      ColumnDescription + "s "
    Case 3
      ColumnDescription + "f "
  EndSelect    
  *LI = ListIconGadget(0,  10, 10, WindowWidth()-20, WindowHeight()-75,ColumnDescription, 80) 
  SendMessage_(*LI, #LVM_SETEXENDEDLISTVIEWSTYLE, #LVS_EX_GRIDLINES, -1) 
  SendMessage_(*LI, #LVM_SETEXENDEDLISTVIEWSTYLE, #LVS_EX_FULLROWSELECT, -1) 
  For k=1 To NbDatabaseColumns - 1 
    ColumnType(k) = DatabaseColumnType(k) 
    ColumnDescription.s = DatabaseColumnName(k)+"  - "
    Select ColumnType(k)
      Case 1
        ColumnDescription + "l "
      Case 2
        ColumnDescription + "s "
      Case 3
        ColumnDescription + "f "
    EndSelect    
    AddGadgetColumn(0, k, ColumnDescription, 80)
  Next 
  While (NextDatabaseRow()) 
    Content$ = "" 
    For k=0 To NbDatabaseColumns - 1 
      Select ColumnType(k) 
      Case 1 
        Content$+Str(GetDatabaseLong(k)) 
      Case 2 
        Content$+GetDatabaseString(k) 
      Case 3 
        Content$+StrF(GetDatabaseFloat(k),10) 
        Default 
        Content$+"UNKNOWN" 
      EndSelect 
      Content$+Chr(10) 
    Next 
    AddGadgetItem(0, -1, Content$, 0) 
  Wend 
Else 
  MessageRequester("Error", "Bad Query", 0) 
EndIf 
Return

Removed a few minor glitches, added Column data type to Column headings.

Thanks Num3 and GedB for your pointers.

Terry