[Linux] DEMO mysql Datenbank ansteuern

Hier könnt Ihr gute, von Euch geschriebene Codes posten. Sie müssen auf jeden Fall funktionieren und sollten möglichst effizient, elegant und beispielhaft oder einfach nur cool sein.
Antworten
Dristar
Beiträge: 72
Registriert: 13.09.2004 12:46

[Linux] DEMO mysql Datenbank ansteuern

Beitrag 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
Zuletzt geändert von Dristar am 18.01.2011 23:47, insgesamt 2-mal geändert.
PB 4.61 Beta 1 , Linux Ubuntu 11.10 ...
Nach oben
infratec
Beiträge: 1
Registriert: 11.09.2008 18:11

Beitrag 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
UNiXXX:
who | grep -i blonde | date; cd~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
(leider nur geklaut)
Nach oben
Antworten

Zurück zu „Code, Tipps und Tricks“