PureBoard

Das deutsche PureBasic-Forum
https://www.purebasic.fr/german/

[Linux] DEMO mysql Datenbank ansteuern

https://www.purebasic.fr/german/viewtopic.php?t=17561

Seite 1 von 1

[Linux] DEMO mysql Datenbank ansteuern

Verfasst: 30.08.2008 14:28
von Dristar
Hallo ,

weil ich es gerade brauche habe ich eine DEMO von MAX2 verbessert :wink:

Achtung Update 18.01.2011 auf PureBasic 4.51 unter Ubuntu 10.04

Code: Alles auswählen

; Orginal :  Code by Max2 + Fangbeast English Forum Aug 23, 2004
; Update :  18.01.2011 PureBasic 4.51 by Dristar  ;  30.08.2008 PureBasic 4.2  by Dristar 
; OS Test :  Linux Ubuntu 10.04                              ;  Linux Ubuntu 8.04 und WindowsXP
; 
;-DEMO PureBasic 4.51 by Dristar


EnableExplicit


Global libPath.s , SQL.s  , row.s , content.s , rowstring.s , outString.s , currentField.s , outString.s , DumpList.s
Global Query.s  , host.s , user.s , passwd.s , db.s , port.l ,  dbHnd.l
Global affRows.l , fieldNum.l , rowsNum.l
Global coladd.l , fieldNum.l  ,  i.l ,  j.l , length.l , Lines.l , ListItems.l , Lines.l , totalFields.l , fieldptr.l , Connect.l , colremove.l , Result.l
Global  *mysqlResult.l , *mysqlRow.l , *mysqlLen.l
Global libOpened
Global quitmysqltest
Global EventID

;==============================================================================================================================
; Any global variables my programs need, particularly the aliases MySQL library functions
;==============================================================================================================================

Global CFF_MySQL_Init.l,        CFF_MySQL_ERRNO.l
Global CFF_MySQL_ERROR.l,       CFF_MySQL_Real_Connect.l
Global CFF_MySQL_Real_Query.l,  CFF_MySQL_Store_Result.l
Global CFF_MySQL_Field_Count.l, CFF_MySQL_Use_Result.l
Global CFF_MySQL_Fetch_Row.l,   CFF_MySQL_Fetch_Lengths.l
Global CFF_MySQL_Free_Result.l, CFF_MySQL_Close.l
Global Yellow, Green, Blue

;==============================================================================================================================
; Searching and setting the libmysql.dll Path in Current Directory, Windows, Windows\system and Windows\system32
;==============================================================================================================================
 
;- OS Check
CompilerSelect #PB_Compiler_OS
                        CompilerCase #PB_OS_Windows
                                Global   libPath.s = "libmysql.dll"
                                         Macro MacroCallFunction
                                                     CallFunctionFast
                                         EndMacro
                         CompilerCase #PB_OS_Linux
                                Global   libPath.s = "libmysqlclient.so.16"
                                         Macro MacroCallFunction
                                                     CallCFunctionFast
                                          EndMacro                                                         
 CompilerEndSelect

;==============================================================================================================================
; Specific constans for the MySQL functions
;==============================================================================================================================

#libmysql               = 1
#MySQL_CLIENT_COMPRESS  = 32

;============================================================================================================================
; Main window title which we will overwrite with other information
;============================================================================================================================

#MainTitle = "MySQL - "

;============================================================================================================================
; Constants
;============================================================================================================================

Enumeration 1
  #Window_mysqltest=0
EndEnumeration

#WindowIndex = #PB_Compiler_EnumerationValue

Enumeration 1
  #Gadget_mysqltest_mainframe
  #Gadget_mysqltest_datalist
  #Gadget_mysqltest_controlframe
  #Gadget_mysqltest_querylabel
  #Gadget_mysqltest_querybox
  #Gadget_mysqltest_loginframe
  #Gadget_mysqltest_hostlabel
  #Gadget_mysqltest_hostbox
  #Gadget_mysqltest_userlabel
  #Gadget_mysqltest_userbox
  #Gadget_mysqltest_passwordlabel
  #Gadget_mysqltest_passwordbox
  #Gadget_mysqltest_databaselabel
  #Gadget_mysqltest_databasebox
  #Gadget_mysqltest_portlabel
  #Gadget_mysqltest_portbox
  #Gadget_mysqltest_savelogin
  #Gadget_mysqltest_dumpbutton
  #Gadget_mysqltest_otherframe
  #Gadget_mysqltest_helpbutton
  #Gadget_mysqltest_exitbutton
  #Gadget_mysqltest_select
  #Gadget_mysqltest_return
EndEnumeration

#GadgetIndex = #PB_Compiler_EnumerationValue

;==============================================================================================================================
; Program window
;==============================================================================================================================

Procedure.l Window_mysqltest()

  If OpenWindow(#Window_mysqltest,175,0,815,600,"Test Programm")
    AddKeyboardShortcut(#Window_mysqltest, #PB_Shortcut_Return, #Gadget_mysqltest_return)
      Frame3DGadget(#Gadget_mysqltest_mainframe,3,0,640,510,"")
      ListIconGadget(#Gadget_mysqltest_datalist,5,10,630,495,"itemslist",100,#PB_ListIcon_GridLines|#PB_ListIcon_FullRowSelect|#PB_ListIcon_AlwaysShowSelection)
      Frame3DGadget(#Gadget_mysqltest_controlframe,3,510,640,80,"")
      TextGadget(#Gadget_mysqltest_querylabel,10,530,70,20,"SQL Query")
      StringGadget(#Gadget_mysqltest_querybox,90,525,548,25,"")
      Frame3DGadget(#Gadget_mysqltest_loginframe,645,0,170,510,"")
      TextGadget(#Gadget_mysqltest_hostlabel,655,15,150,20,"Host IP or host name")
      StringGadget(#Gadget_mysqltest_hostbox,655,30,135,25,"")
      TextGadget(#Gadget_mysqltest_userlabel,655,55,135,20,"User name")
      StringGadget(#Gadget_mysqltest_userbox,655,70,135,25,"")
      TextGadget(#Gadget_mysqltest_passwordlabel,655,95,135,20,"Password")
      StringGadget(#Gadget_mysqltest_passwordbox,655,110,135,25,"",#PB_String_Password)
      TextGadget(#Gadget_mysqltest_databaselabel,655,135,135,20,"Database name")
      StringGadget(#Gadget_mysqltest_databasebox,655,150,135,25,"")
      TextGadget(#Gadget_mysqltest_portlabel,655,175,135,15,"Port number")
      StringGadget(#Gadget_mysqltest_portbox,655,190,135,25,"3306",#PB_String_Numeric)
      CheckBoxGadget(#Gadget_mysqltest_savelogin,655,220,150,20,"Save current login")
      ButtonGadget(#Gadget_mysqltest_dumpbutton,655,480,135,25,"Dump list to disk")
      Frame3DGadget(#Gadget_mysqltest_otherframe,645,510,155,45,"")
      ButtonGadget(#Gadget_mysqltest_exitbutton,690,522,60,25,"Exit")
      ButtonGadget(#Gadget_mysqltest_select,575,560,60,25,"Enter")
      HideWindow(#Window_mysqltest,0)
      ProcedureReturn WindowID(0)
  EndIf
EndProcedure

;==============================================================================================================================
; All of the aliased MySQL library routines and the library loader
;==============================================================================================================================

Procedure MySQL_Init(libPath.s)
  Global CurrentDir.s
  If OpenLibrary(#libmysql, libPath)
    CFF_MySQL_Init          = GetFunction(#libmysql, "mysql_init")
    CFF_MySQL_ERRNO         = GetFunction(#libmysql, "mysql_errno")
    CFF_MySQL_ERROR         = GetFunction(#libmysql, "mysql_error")
    CFF_MySQL_Real_Connect = GetFunction(#libmysql, "mysql_real_connect")
    CFF_MySQL_Real_Query    = GetFunction(#libmysql, "mysql_real_query")
    CFF_MySQL_Store_Result  =GetFunction(#libmysql, "mysql_store_result")
    CFF_MySQL_Field_Count  = GetFunction(#libmysql, "mysql_field_count")
    CFF_MySQL_Use_Result    = GetFunction(#libmysql, "mysql_use_result")
    CFF_MySQL_Fetch_Row     = GetFunction(#libmysql, "mysql_fetch_row")
    CFF_MySQL_Fetch_Lengths = GetFunction(#libmysql, "mysql_fetch_lengths")
    CFF_MySQL_Free_Result   = GetFunction(#libmysql, "mysql_free_result")
    CFF_MySQL_Close        = GetFunction(#libmysql, "mysql_close")
    ProcedureReturn  MacroCallFunction (CFF_MySQL_Init, dbHnd)   
  EndIf
 
EndProcedure

Procedure.s MySQL_GetError(db_ID, requester)
  Protected Errormsg.s, i.l, *Error.l
     If MacroCallFunction(CFF_MySQL_ERRNO, db_ID) > 0
      *Error = MacroCallFunction(CFF_MySQL_ERROR, db_ID)
    Errormsg = PeekS(*Error)
    If requester
      SetWindowTitle(#Window_mysqltest, #MainTitle + Errormsg)
    EndIf
  EndIf
  ProcedureReturn Errormsg
EndProcedure

Procedure MySQL_Real_Connect(dbHnd, host, user, password, db, port, options)
    ProcedureReturn MacroCallFunction(CFF_MySQL_Real_Connect, dbHnd, host, user, password, db, port, 0, options)
EndProcedure

Procedure MySQL_Real_Query(dbHnd, Query.s)
    ProcedureReturn MacroCallFunction(CFF_MySQL_Real_Query, dbHnd, @Query, Len(Query))
EndProcedure

Procedure MySQL_Store_Result(dbHnd)
    ProcedureReturn MacroCallFunction(CFF_MySQL_Store_Result, dbHnd)
EndProcedure

Procedure MySQL_Field_Count(dbHnd)
    ProcedureReturn MacroCallFunction(CFF_MySQL_Field_Count, dbHnd)
EndProcedure

Procedure MySQL_Use_Result(dbHnd)
    ProcedureReturn MacroCallFunction(CFF_MySQL_Use_Result, dbHnd)
EndProcedure

Procedure MySQL_Fetch_Row (*mysqlResult)
    ProcedureReturn MacroCallFunction(CFF_MySQL_Fetch_Row, *mysqlResult)
EndProcedure

Procedure MySQL_Fetch_Lengths (*mysqlResult)
    ProcedureReturn MacroCallFunction (CFF_MySQL_Fetch_Lengths, *mysqlResult)
EndProcedure

Procedure MySQL_Free_Result (*mysqlResult)
    ProcedureReturn MacroCallFunction(CFF_MySQL_Free_Result, *mysqlResult)
EndProcedure

Procedure MySQL_Close (dbHnd)
    MacroCallFunction(CFF_MySQL_Close, dbHnd)
EndProcedure

;============================================================================================================================
; Try to initialise the database environment (Procedure MySQL_Init())
;============================================================================================================================

dbHnd = MySQL_Init(libPath)

If dbHnd
  libOpened = 1
Else
  libOpened = 0
   MessageRequester("Critical Error", "libmysqlclient.so oder libmysql.dll nicht gefunden !")
  End
EndIf

;============================================================================================================================
; Main Program Loop
;============================================================================================================================

If Window_mysqltest()
   
  quitmysqltest = 0                                                 

  Gosub LoadLastLogin                                               

 SetActiveGadget(#Gadget_mysqltest_querybox)                       

  Repeat                                                           
    EventID = WaitWindowEvent()
     Select EventID
   
      Case #PB_Event_CloseWindow
        If EventWindow() = #Window_mysqltest
          quitmysqltest = 1
        EndIf
      Case #PB_Event_Gadget
        Select EventGadget()
         Case  #Gadget_mysqltest_select             :Gosub CheckEnter
          Case #Gadget_mysqltest_dumpbutton   : Gosub DumpListToDisk
          Case #Gadget_mysqltest_helpbutton
          Case #Gadget_mysqltest_exitbutton   : quitmysqltest = 1
        EndSelect
    EndSelect
  Until quitmysqltest
  Gosub SaveCurrentLogin             
  CloseWindow(#Window_mysqltest)
EndIf
End

;============================================================================================================================
; Load the last login saved by the user if there was one
;============================================================================================================================

LoadLastLogin:
If ReadFile(0, "LastLogin.txt") 
  SetGadgetText(#Gadget_mysqltest_hostbox,     ReadString(0))
  SetGadgetText(#Gadget_mysqltest_userbox,     ReadString(0))
  SetGadgetText(#Gadget_mysqltest_passwordbox, ReadString(0))
  SetGadgetText(#Gadget_mysqltest_databasebox, ReadString(0))
  SetGadgetText(#Gadget_mysqltest_portbox,     ReadString(0))
  If ReadString(0) = "1"
    SetGadgetState(#Gadget_mysqltest_savelogin, 1)
  EndIf
  CloseFile(0)
  SetWindowTitle(#Window_mysqltest, #MainTitle + "Last saved login loaded")
Else
  SetWindowTitle(#Window_mysqltest, #MainTitle + "Couldn't find last login file")
EndIf

Return

;============================================================================================================================
; Save the current login details if the user has selected the checkbox
;============================================================================================================================

SaveCurrentLogin:

If GetGadgetState(#Gadget_mysqltest_savelogin) = 1
  If CreateFile(0, "LastLogin.txt") <> 1
    WriteStringN(0,GetGadgetText(#Gadget_mysqltest_hostbox))
    WriteStringN(0,GetGadgetText(#Gadget_mysqltest_userbox))
    WriteStringN(0,GetGadgetText(#Gadget_mysqltest_passwordbox))
    WriteStringN(0,GetGadgetText(#Gadget_mysqltest_databasebox))
    WriteStringN(0,GetGadgetText(#Gadget_mysqltest_portbox))
    WriteStringN(0,"1")
    CloseFile(0)
  EndIf
EndIf

Return

;============================================================================================================================
; Check if the ENTER key was pressed in the SQL query box
;============================================================================================================================

CheckEnter:
    Query = GetGadgetText(#Gadget_mysqltest_querybox)     
   Gosub ConnectToDatabase                               
Return

;============================================================================================================================
; Try to connect to the database
;============================================================================================================================

ConnectToDatabase:

If libOpened
  host    = GetGadgetText(#Gadget_mysqltest_hostbox)
  user    = GetGadgetText(#Gadget_mysqltest_userbox)
  passwd  = GetGadgetText(#Gadget_mysqltest_passwordbox)
  db      = GetGadgetText(#Gadget_mysqltest_databasebox)
  port    = Val(GetGadgetText(#Gadget_mysqltest_portbox))
  If MySQL_Real_Connect(dbHnd, @host, @user, @passwd, @db, port, 32) <> 0
    SetWindowTitle(#Window_mysqltest, #MainTitle + "Connected and getting your data, please wait")
    Gosub GetSqlData
  Else
    MySQL_GetError(dbHnd, 1)
    Connect= 0
  EndIf
Else
  SetWindowTitle(#Window_mysqltest, #MainTitle + "SQL library was never loaded so we cannot connect to the database!!")
EndIf

Return

;============================================================================================================================
; Retrieve and display the data to the user now
;============================================================================================================================

GetSqlData:

If fieldNum <> 0                                                 
  For colremove = 1 To fieldNum - 1
    RemoveGadgetColumn(#Gadget_mysqltest_datalist, 1)
  Next colremove
  fieldNum = 0
EndIf
   
Result = MySQL_Real_Query (dbHnd, Query)                       

If Result                                                         
  MySQL_GetError(dbHnd, 1)
Else
   
  *mysqlResult = MySQL_Use_Result(dbHnd)                         
  If *mysqlResult = 0
    If MySQL_Field_Count(dbHnd)                                   
      MySQL_GetError(dbHnd, 1)
    Else
      MySQL_GetError(dbHnd, 1)                                   
    EndIf
  Else
    
      CompilerSelect #PB_Compiler_OS
    CompilerCase #PB_OS_Windows
       affRows   = CallFunction(#libmysql,"mysql_affected_rows",dbHnd)       
       fieldNum  = CallFunction(#libmysql,"mysql_num_fields", *mysqlResult) 
       rowsNum   = CallFunction(#libmysql,"mysql_num_rows", *mysqlResult)   
    CompilerCase #PB_OS_Linux
       affRows   = CallCFunction(#libmysql,"mysql_affected_rows",dbHnd)       
       fieldNum  = CallCFunction(#libmysql,"mysql_num_fields", *mysqlResult) 
       rowsNum   = CallCFunction(#libmysql,"mysql_num_rows", *mysqlResult)   
    CompilerEndSelect

   
    For coladd = 1 To fieldNum - 1
      AddGadgetColumn(#Gadget_mysqltest_datalist, 1, "Data", 100)
    Next coladd
   
    Repeat                                                               
      *mysqlRow = MySQL_Fetch_Row(*mysqlResult)
      If *mysqlRow <> 0
        *mysqlLen = MySQL_Fetch_Lengths(*mysqlResult)         
        row = ""
        For j = 1 To fieldNum                                           
          length = PeekL(*mysqlLen + 4 * (j - 1))           
          fieldptr = PeekL(*mysqlRow + 4 * (j - 1))
          If fieldptr > 0
            content = PeekS(fieldptr, length)
          Else
            content = "NULL"                                             
          EndIf
          row = row + content + ";"                                       
        Next j
        rowstring = ReplaceString(row, ";", Chr(10), 1,1)
        AddGadgetItem(#Gadget_mysqltest_datalist, -1, rowstring)             
      EndIf
    Until *mysqlRow = 0
    Result.l = MySQL_Free_Result(*mysqlResult)
 
  EndIf
  
EndIf

SetWindowTitle(#Window_mysqltest, #MainTitle + "Found all matching data, nothing left to do.")

Return

;============================================================================================================================
; Dump the list to disk at the users' request
;============================================================================================================================

DumpListToDisk:

Lines = CountGadgetItems(#Gadget_mysqltest_datalist)                   

DumpList = SaveFileRequester("Save returned query", "QueryDump.txt", "Text (*.txt)|*.txt|All files (*.*)|*.*", 0)

If CreateFile(0, DumpList.s) <> 0
   
  For ListItems = 0 To Lines - 1
     
    For totalFields = 0 To fieldNum - 1
      currentField = GetGadgetItemText(#Gadget_mysqltest_datalist, ListItems, totalFields)
      outString + currentField + ";"
    Next totalFields
    WriteStringN(0,outString)
    outString = ""
    
  Next ListItems
  CloseFile(0)
  
Else
  SetWindowTitle(#Window_mysqltest, #MainTitle + "Cannot save the list to disk, something went wrong")
EndIf

Return
mfg Dristar

Verfasst: 11.09.2008 18:41
von infratec
Hallo Dristar,

ich als PureBASIC NewBie erlaube mir mal eine kleine Anmerkung:

Wenn man den OS spezifischen Teil am Anfang etwas erweitert,
kann man sich viel Tipp-Arbeit sparen:

Code: Alles auswählen

;- OS Check
CompilerSelect #PB_Compiler_OS
  CompilerCase #PB_OS_Windows
    libPath = "libmysql.dll"
    Macro MacroCallFunctionFast
      CallFunctionFast
    EndMacro
    Macro MacroCallFunction
      CallFunction
    EndMacro
  CompilerCase #PB_OS_Linux
    libPath = "libmysqlclient.so"
    Macro MacroCallFunctionFast
      CallCFunctionFast
    EndMacro
    Macro MacroCallFunction
      CallCFunction
    EndMacro
 CompilerEndSelect
Nun kann man sich nämlich die ganzen Compiler Anweisungen im
Programm sparen:

z.B.:

Code: Alles auswählen

Procedure MySQL_Real_Connect(dbHnd, host.s, user.s, password.s, db.s, port.l, options.l) 
  ProcedureReturn MacroCallFunctionFast(CFF_MySQL_Real_Connect, dbHnd, host, user, password.s, db, port, 0, options)
EndProcedure
Anstatt:

Code: Alles auswählen

Procedure MySQL_Real_Connect(dbHnd, host.s, user.s, password.s, db.s, port.l, options.l)
 CompilerSelect #PB_Compiler_OS
    CompilerCase #PB_OS_Windows
    ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Connect, dbHnd, host, user, password.s, db, port, 0, options)
    CompilerCase #PB_OS_Linux
    ProcedureReturn CallCFunctionFast(CFF_MySQL_Real_Connect, dbHnd, host, user, password.s, db, port, 0, options)
  CompilerEndSelect
EndProcedure
Kommt wohl daher, daß ich von 'C' her komme und viel mit #define arbeite.

Aber ich muß sagen:

Ich finde PureBASIC klasse!

Endlich mal kleine Programme unter Windows !!!

Viele Grüße,

Infratec
Alle Zeiten sind UTC+02:00
Seite 1 von 1

Powered by phpBB® Forum Software © phpBB Limited

Deutsche Übersetzung durch phpBB.de