MYSQL Datenbank LESEN/Schreiben

Mathias-Kwiatkowski · Beitrag von **Mathias-Kwiatkowski** » 24.07.2005 22:48

wie kann ich auf meiner mysql datenbank zugreifen bzw was schreiben und lesen?

1.von pbasic aus
2. von meiner webseit aus

Danke wer mir hilft!

AndyX · Beitrag von **AndyX** » 24.07.2005 23:19

http://tut.php-q.net/ Das isn PHP Tutorial, da wird MySQL erklärt... Mit PB kanns ichs dir nich genau erklären, aber da gilt auch die MySQL Syntax und alles

Mathias-Kwiatkowski · Beitrag von **Mathias-Kwiatkowski** » 25.07.2005 17:39

mhh in php komm ich net klar!!!

kann man den irgendwie anders von html aus in einer txt datei einlesen??
oder schreiben?

MVXA · Beitrag von **MVXA** » 25.07.2005 17:54

Nein. Die HTML Datei wird vom Browser runtergeladen ohne das der Server diese intepretiert. Der Browser intepretiert sich dann einen an der Seite.
PHP hat den Vorteil, dass die Dateien vorher durch einen Interpreter kommen und Dieser dann die Daten an den Browser sendet.
Wirst wohl mit PHP klar kommen müssen. Oder du schaffst dir ne Linux LiveCD an und kompilierst dir deine eigenen CGI Scripte mit PB.

the one and only · Beitrag von **the one and only** » 25.07.2005 18:14

Da hab ich mich auch schonmal mit beschäftigt

Auf der MySQL Homepage finden sich Infos zum benutzen von MySQL in eigenen Anwendungen. Frag mich aber nicht mehr wo in meinem MySQL Thread müssts stehen bin bloß zu faul zum suchen ^^

PAMKKKKK · Beitrag von **PAMKKKKK** » 02.08.2005 13:32

Vorgehensweise zum zugriff von PureBasic auf MySQL:

Jeder Rechner der auf MySQL zugreifen soll, muss den ODBC Treiber installiert haben oder die LibMySQL.dll . (Also auch der Rechner auf dem du Programmierst!)
Die Installation von "MySQL Connector/ODBC" braucht man nicht erklären. (ist sehr leicht)
(MySQL zugriffe gehen nur über die ODBC Schnittstelle oder dll´s)

zum üben empfehle ich den ODBC Treiber "MySQL Connector/ODBC":
http://www.mysql.de/products/connector/odbc/

Dll zum MySQL connecten:
http://www.vbarchiv.net/workshop/workshop47.php
Weiter hilfe zu der DLL findest du im CodeArchiv von www.purearea.net:
LibMySQL-DLL_Example.pb (Beispiel für die Verwendung von MySQL mittels der LibMySQL.dll)
Download:http://www.purearea.net/pb/download/CodeArchiv.zip

ODBC beispiel:

Du lädts dir die MDB_lib Userlib von Paul Leischow runter, und entpackst sie in dein PuereBasic Verzeichnis
http://www.reelmedia.org/cgi-bin/PurePr ... es&sub=ASM

Öffne deinen Purebasic Editor und gib den Code mit den Daten zum verbinden mit der MySQL Datenbank ein:

Code: Alles auswählen

 ; MySQL beispiel von PAMKKKKK 02.August.2005
; braucht die Userlib MDB_lib von Paul Leishow!
; Kompelieren mit Debugger

hSQL=SQL_Connect("www.meinehomepage.de","DatenbankName","DB_UserName","DB_UserPasswort")
; www.meinehomepage.de = Adresse des Servers wo die MySQL Datenbank ist (kann auch eine IP sein)
; DatenbankName = Name der MySQL Datenbank die geöffnet werden soll
; DB_UserName = Benutzername der auf die MySQL Datenbank zugreifen darf
; DB_UserPasswort = Passwort des MySQL Benutzers
 
If hSQL 
   Debug "Database has been opened"
Else
  Debug "Unable to open Database"
  End ; kein Datenbank connect und ende 
EndIf

 ; nun kann man die Datenbank mit den Purebasic befehlen bearbeiten

If DatabaseQuery("SELECT * FROM `ppvdaten`;") ; Hier muss man die Datenbanksprache SQL können ! 
  While NextDatabaseRow() 
    Spalte1.s =GetDatabaseString(0) ; Feldinhalt von Spalte 1
    Spalte2.s =GetDatabaseString(1) ; Feldinhalt von Spalte 2 usw....Spalte3 usw...
    Debug "Abfrage = " + Spalte1 + ";" + Spalte2
  Wend 
EndIf

CloseDatabase(hSQL) ; Datenbank immer Ordentlich schliessen!!

PAMKKKKK · Beitrag von **PAMKKKKK** » 02.08.2005 14:24

Wegen der Wichtigkeit hier noch ein extra Post:

Die LibMySQL.dll [Edit] siehe im nächsten Post im Thread [/edit]

und die MyVbQL.dll von http://www.icarz.com/mysql/ ist veraltet.

die Lib von vbmysqldirect bietet die Zugriffsmöglichkeit über eine VisualBasic API (wie ADODB)
http://www.vbmysql.com/projects/vbmysqldirect/

PAMKKKKK · Beitrag von **PAMKKKKK** » 05.09.2005 11:20

Damit die User eine neue Nachrichtbekommen hier ein DOPPELPOST

Ich habe mich in Bezug auf die libmysql.dll geirrt!

Wenn man mit Programmen über ODBC Verbindung aufnehmen will, nutzt man auf die vom Betriebsystem vorinstallierten Treiber oder muss den geeigneten Treiber nachinstallieren.
Mit der libmysql.dll kann man also ohne Treiberinstalation und ohne Verbindungen einzurichten direkt Kommunizieren.
Dazu muss man mit seinem Programm die libmysql.dll ausliefern.

Die libmysql.dll ist eine von MySQL AB entwickelte Library die wie die gesamte MySQL DB auch als C/C++ Sourcecode vorliegt.
Wenn man die MySQL DB herunterläd und installiert, liegt die libmysql.dll im Verzeichnis \..\..\MySQL Server 4.1\bin. Am besten man lässt sie suchen, und nimmt die aus dem OPT Ordner!!

Es gibt im "PureBasic CodeArchiv" ein Beispiel die libmysql.dll zu nutzen. Dieses Beispiel ist aber veraltet. Der Programmierer der libmysql.dll-PureBasic Beispiele ist MAX.
Hier ist sein Code um die libmysql.dll zu nutzen.

Code: Alles auswählen

; Code by Max.Â² English Forum Aug 23, 2004
; Edited by Pamkkkkk September 5. 2005 

;============================================================================================================================== 
; Change this for your Database Conection
host.s    = "localhost" 
user.s    = "testuser" 
passwd.s  = "password" 
db.s      = "PBdb" 
port.l    = 3306 ; Defaut (no need to change)
;============================================================================================================================== 
dbHnd.l 
SQL.s 
row.s 
i.l 
j.l 
affRows.l 
fieldNum.l 
rowsNum.l 



#libmysql = 1 

#MySQL_CLIENT_COMPRESS = 32 

Global CFF_MySQL_Init.l 
Global CFF_MySQL_ERRNO.l 
Global CFF_MySQL_ERROR.l 
Global CFF_MySQL_Real_Connect.l 
Global CFF_MySQL_Real_Query.l 
Global CFF_MySQL_Store_Result.l 
Global CFF_MySQL_Field_Count.l 
Global CFF_MySQL_Use_Result.l 
Global CFF_MySQL_Fetch_Row.l 
Global CFF_MySQL_Fetch_Lengths.l 
Global CFF_MySQL_Free_Result.l 
Global CFF_MySQL_Close.l 

Procedure.s MySQL_search() 
  ; Windows stuff... Edit it for other systems!
  ; Searching and setting the libmysql.dll Path in Current Directory, Windows, Windows\system and Windows\system32
  ; Returns "" if dll ist not found, else the dll Path
  ; if dll is in CurentDir, it has the Choice

  libPath .s = ""
  
  CurrentDir.s = Space(512) 
  Result = GetCurrentDirectory_(Len(CurrentDir), @CurrentDir)

  If FileSize( CurrentDir +"\libmysql.dll") > 0
   libPath = CurrentDir +"\libmysql.dll"
    ProcedureReturn libPath 
  EndIf 

  Systemroot.s = Space(260)
  GetEnvironmentVariable_("SYSTEMROOT",@Systemroot,Len(Systemroot)) ; Copy Environment Variable to Systemroot

  If FileSize( Systemroot +"\libmysql.dll") > 0
    libPath = Systemroot +"\libmysql.dll"
  EndIf

  If FileSize( Systemroot +"\system\libmysql.dll") > 0
    libPath = Systemroot +"\system\libmysql.dll"
  EndIf

  If FileSize( Systemroot +"\system32\libmysql.dll") > 0
    libPath = Systemroot +"\system32\libmysql.dll"
  EndIf

  If  libPath = ""
    Debug "libmysql.dll not found !"
  EndIf

  ProcedureReturn libPath
 
EndProcedure


Procedure MySQL_Init() 
  
  If OpenLibrary(#libmysql,MySQL_search()) 
    
    CFF_MySQL_Init = IsFunction(#libmysql,"mysql_init") 
    CFF_MySQL_ERRNO = IsFunction(#libmysql,"mysql_errno") 
    CFF_MySQL_ERROR = IsFunction(#libmysql,"mysql_error") 
    CFF_MySQL_Real_Connect = IsFunction(#libmysql,"mysql_real_connect") 
    CFF_MySQL_Real_Query = IsFunction(#libmysql,"mysql_real_query") 
    CFF_MySQL_Store_Result = IsFunction(#libmysql,"mysql_store_result") 
    CFF_MySQL_Field_Count = IsFunction(#libmysql,"mysql_field_count") 
    CFF_MySQL_Use_Result = IsFunction(#libmysql,"mysql_use_result") 
    CFF_MySQL_Fetch_Row = IsFunction(#libmysql,"mysql_fetch_row") 
    CFF_MySQL_Fetch_Lengths = IsFunction(#libmysql,"mysql_fetch_lengths") 
    CFF_MySQL_Free_Result = IsFunction(#libmysql,"mysql_free_result") 
    CFF_MySQL_Close = IsFunction (#libmysql,"mysql_close") 
    
    
    
    ProcedureReturn CallFunctionFast (CFF_MySQL_Init,dbHnd) 
  Else 
    Result = MessageRequester("Critical Error","libmysql.dll not found") 
    End 
  EndIf 
  
EndProcedure 

Procedure.s MySQL_GetError(db_ID,requester) 
  
  Protected Errormsg.s, i.l, Error.l 
  
  If CallFunctionFast(CFF_MySQL_ERRNO,db_ID) > 0 
    *Error =CallFunctionFast(CFF_MySQL_ERROR,db_ID)        
    Errormsg=PeekS(*Error) 
    
    If requester 
      Result= MessageRequester("MySQL error",Errormsg,#PB_MessageRequester_Ok) 
    EndIf 
  EndIf 
  
  ProcedureReturn Errormsg 
  
EndProcedure 



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

Procedure MySQL_Real_Query (dbHnd,SQL.s) 
  
  ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Query, dbHnd, SQL, Len(SQL)) 
  
EndProcedure 

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

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

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

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


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

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

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


dbHnd = MySQL_Init() 
If MySQL_Real_Connect (dbHnd, host, user, passwd, db, port, 32) = 0 
  
  MySQL_GetError(dbHnd,1) 
Else 
  CallDebugger 
  SQL = "SELECT * FROM Table" 
  
  Result= MySQL_Real_Query (dbHnd, SQL) 
  
  If Result 
    MySQL_GetError(dbHnd,1) 
  Else 
    
    *mysqlResult=MySQL_Use_Result (dbHnd) 
    Debug *mysqlResult 
    ;no result returned 
    If *mysqlResult=0 
      ;no fields returned means error 
      If MySQL_Field_Count(dbHnd) 
        MySQL_GetError(dbHnd,1) 
        ;fields are returned, so no error but query didn't return data 
      Else 
        
      EndIf 
      ;results are returned 
    Else 
      
      
      ;affRows   = CallFunction(#libmysql,"mysql_affected_rows",dbHnd) 
      fieldNum  = CallFunction(#libmysql,"mysql_num_fields",*mysqlResult) 
      ;rowsNum   = CallFunction(#libmysql,"mysql_num_rows",*mysqlResult) 
      
      Debug affRows 
      Debug fieldNum 
      Debug rowsNum 
      
      
      Repeat 
        *mysqlRow=MySQL_Fetch_Row(*mysqlResult) 
        If *mysqlRow<>0 
          *mysqlLen=MySQL_Fetch_Lengths(*mysqlResult)          
          
          row = "" 
          
          ;length of given field 
          For j=1 To fieldNum 
            length=PeekL(*mysqlLen+4*(j-1))            
            fieldptr=PeekL(*mysqlRow+4*(j-1)) 
            If fieldptr>0 
              content.s=PeekS(fieldptr,length) 
            Else 
              ;zero pointer returend means empty field  
              content="NULL" 
            EndIf 
            row = row + content + ";" 
          Next j 
          
          Debug row 
        EndIf 
      Until *mysqlRow = 0 
      
      Result.l=MySQL_Free_Result(*mysqlResult) 
      MySQL_Close(dbHnd) 
    EndIf 
  EndIf 
EndIf

Eine Komplette Anwendung aus dem Englischen Forum um MySQL Verbindungen zu testen:

Code: Alles auswählen

; Code by Max2 + Fangbeast English Forum Aug 23, 2004 http://forums.purebasic.com/english/viewtopic.php?t=12475&highlight=mx2+libmysql
; Edited by PAMKKKKK September 5. 2005
; Needs the libmysql.dll in the Path: Current Directory or Windows or  Windows\system or Windows\system32
; get libmysql.dll from your MySQL Installationfolder ..\MySQL Server 4.1\bin\libmysql.dll or search the MySQL Instalation Folder

;============================================================================================================================== 
; Please note that all the MySQL direct database code I found in the PureBasic forum, done by Max2 and not by me. He has 
; given me permission to use it as I see fit in a tutorial and it is hoped that this mini program will help someone get to 
; grips with using MySQL server queries directly, without having to install either an ODBC driver or having to go through 
; the ODBC control panel to setup a database each time. 
; 
; I only put this program together using his code, no big thing. Use it as you see fit. 
; 
; *NOTE* There is no syntax checking on database statements, I simply don't have that sort of time on my hands, nor the 
; experience needed. This should be enough for most people. 
; 
; If you like it, say thanks to Mx2 and (me too maybe???). If you don't like it, I don't want to know. Fang. 
;============================================================================================================================== 
; Predefine my global structure types so I don't have to keep adding the type throughout the program 
;============================================================================================================================== 

dbHnd.l
libPath.s      
SQL.s 
row.s 
i.l 
j.l 
affRows.l 
fieldNum.l 
rowsNum.l 

;============================================================================================================================== 
; 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 

;============================================================================================================================== 
; Get the current directory and Systemroot (Windows Dir). May be used for various functions 
;============================================================================================================================== 

CurrentDir.s = Space(512) 
Result = GetCurrentDirectory_(Len(CurrentDir), @CurrentDir) 

Systemroot.s = Space(255)
GetEnvironmentVariable_("SYSTEMROOT",@Systemroot,Len(Systemroot)) ; Copy Environment Variable to Systemroot

;============================================================================================================================== 
; Searching and setting the libmysql.dll Path in Current Directory, Windows, Windows\system and Windows\system32
;============================================================================================================================== 
If FileSize( Systemroot +"\libmysql.dll") > 0
  libPath = Systemroot +"\libmysql.dll"
EndIf
If FileSize( Systemroot +"\system\libmysql.dll") > 0
  libPath = Systemroot +"\system\libmysql.dll"
EndIf
If FileSize( Systemroot +"\system32\libmysql.dll") > 0
  libPath = Systemroot +"\system32\libmysql.dll"
EndIf
If FileSize( CurrentDir +"\libmysql.dll") > 0
  libPath = CurrentDir +"\libmysql.dll"
EndIf

If  libPath = ""
  Debug "libmysql.dll not found !"
EndIf

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

#libmysql               = 1 

#MySQL_CLIENT_COMPRESS  = 32 

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

#MainTitle = "MySQL - " 

;============================================================================================================================ 
; Object colouring in the callback 
;============================================================================================================================ 

;Colour  = RGB($FF,$FF,$AA) 

Yellow  = CreateSolidBrush_($70DCFC) 
Green   = CreateSolidBrush_($7BDF84) 
Blue    = CreateSolidBrush_($E5B91A) 

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

Enumeration 1 
  #Window_mysqltest 
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_return 
EndEnumeration 

#GadgetIndex = #PB_Compiler_EnumerationValue 

;============================================================================================================================ 
; Cute bubble tooltips 
;============================================================================================================================ 

Procedure BalloonTip(bWindow.l, bGadget.l, bText.s) 
  ToolTipControl = CreateWindowEx_(0, "ToolTips_Class32", "", $D0000000 | $40, 0, 0, 0, 0, WindowID(bWindow), 0, GetModuleHandle_(0), 0) 
  SendMessage_(ToolTipControl, 1044, 0, 0) 
  SendMessage_(ToolTipControl, 1043, $F3D97A, 0) 
  SendMessage_(ToolTipControl, 1048, 0, 180) 
  Button.TOOLINFO\cbSize = SizeOf(TOOLINFO) 
  Button\uFlags = $11 
  Button\hWnd = GadgetID(bGadget) 
  Button\uId = GadgetID(bGadget) 
  Button\lpszText = @bText 
  SendMessage_(ToolTipControl, $0404, 0, Button) 
EndProcedure 

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

Procedure.l Window_mysqltest() 
  If OpenWindow(#Window_mysqltest,175,0,800,555,#PB_Window_ScreenCentered|#PB_Window_Invisible,#MainTitle) 
    AddKeyboardShortcut(#Window_mysqltest, #PB_Shortcut_Return, #Gadget_mysqltest_return)  
    If CreateGadgetList(WindowID(#Window_mysqltest)) 
      Frame3DGadget(#Gadget_mysqltest_mainframe,0,0,640,510,"") 
      ListIconGadget(#Gadget_mysqltest_datalist,5,10,630,495,"itemslist",100,#PB_ListIcon_GridLines|#PB_ListIcon_FullRowSelect|#PB_ListIcon_AlwaysShowSelection) 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_datalist,"All items returned from a properly formatted SQL query will end up in this list and be cleared in  the next call") 
      Frame3DGadget(#Gadget_mysqltest_controlframe,0,510,640,45,"") 
      TextGadget(#Gadget_mysqltest_querylabel,10,530,60,15,"SQL Query") 
      StringGadget(#Gadget_mysqltest_querybox,70,525,558,20,"") 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_querybox,"Type in a properly formatted SQL query in here and press ENTER/RETURN to execute it") 
      Frame3DGadget(#Gadget_mysqltest_loginframe,645,0,155,510,"") 
      TextGadget(#Gadget_mysqltest_hostlabel,655,15,135,15,"Host IP or host name") 
      StringGadget(#Gadget_mysqltest_hostbox,655,30,135,20,"") 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_hostbox,"Type in the name of the system that hosts the database or the IP version") 
      TextGadget(#Gadget_mysqltest_userlabel,655,55,135,15,"User name") 
      StringGadget(#Gadget_mysqltest_userbox,655,70,135,20,"") 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_userbox,"Type in the name of the user who is registered for use with that database") 
      TextGadget(#Gadget_mysqltest_passwordlabel,655,95,135,15,"Password") 
      StringGadget(#Gadget_mysqltest_passwordbox,655,110,135,20,"",#PB_String_Password) 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_passwordbox,"Type in the login password of the user that is registered for use with that database") 
      TextGadget(#Gadget_mysqltest_databaselabel,655,135,135,15,"Database name") 
      StringGadget(#Gadget_mysqltest_databasebox,655,150,135,20,"") 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_databasebox,"Type in the name of the database that you wish to access on the remote system") 
      TextGadget(#Gadget_mysqltest_portlabel,655,175,135,15,"Port number") 
      StringGadget(#Gadget_mysqltest_portbox,655,190,135,20,"",#PB_String_Numeric) 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_portbox,"Type in the port number that the database server allows queries on") 
      CheckBoxGadget(#Gadget_mysqltest_savelogin,655,220,135,15,"Save current login") 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_savelogin,"Check this box if you want to save the current login to be auto-loaded next time you start this program") 
      ButtonGadget(#Gadget_mysqltest_dumpbutton,655,480,135,20,"Dump list to disk") 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_dumpbutton,"Press this button to dump the results of the query to a disk file") 
      Frame3DGadget(#Gadget_mysqltest_otherframe,645,510,155,45,"") 
      ButtonGadget(#Gadget_mysqltest_helpbutton,655,525,60,20,"Help") 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_helpbutton,"Press this button to show any help file linked to this program") 
      ButtonGadget(#Gadget_mysqltest_exitbutton,730,525,60,20,"Exit") 
      BalloonTip(#Window_mysqltest,#Gadget_mysqltest_exitbutton,"Press this button to exit this program immediately") 
      HideWindow(#Window_mysqltest,0) 
      ProcedureReturn WindowID() 
    EndIf 
  EndIf 
EndProcedure 

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

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

Procedure.s MySQL_GetError(db_ID, requester) 
  Protected Errormsg.s, i.l, Error.l 
  If CallFunctionFast(CFF_MySQL_ERRNO, db_ID) > 0 
    *Error = CallFunctionFast(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.s, user.s, password.s, db.s, port.l, options.l) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Connect, dbHnd, host, user, password.s, db, port, 0, options) 
EndProcedure 

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

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

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

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

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

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

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

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

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

dbHnd = MySQL_Init(libPath) 

If dbHnd 
  libOpened = 1 ; Tell all routines that the library was opened okay 
Else 
  libOpened = 0 
  Message = MessageRequester("Critical Error", "libmysql.dll not found in programs' startup directory") 
  End 
EndIf 

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

If Window_mysqltest() 
   
  quitmysqltest = 0                                                 ; Set the program quit semaphore 
  
  Gosub LoadLastLogin                                               ; Load the last login if it exists 
  
  ActivateGadget(#Gadget_mysqltest_querybox)                        ; Set the focus to the query box 
  
  Repeat                                                            ; Now start checking for all window and gadget events 
    EventID = WaitWindowEvent() 
    Select EventID 
      Case #PB_Event_CloseWindow 
        If EventWindowID() = #Window_mysqltest 
          quitmysqltest = 1 
        EndIf 
      Case #PB_Event_Menu 
        Select EventMenuID() 
          Case #Gadget_mysqltest_return       : Gosub CheckEnter    ; Was Enter pressed in the query box? 
        EndSelect 
      Case #PB_Event_Gadget 
        Select EventGadgetID() 
          Case #Gadget_mysqltest_dumpbutton   : Gosub DumpListToDisk 
          Case #Gadget_mysqltest_helpbutton 
          Case #Gadget_mysqltest_exitbutton   : quitmysqltest = 1 
        EndSelect 
    EndSelect 
  Until quitmysqltest 
  Gosub SaveCurrentLogin             ; Save the current login if the checkbox is selected 
  CloseWindow(#Window_mysqltest) 
EndIf 
End 

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

LoadLastLogin: 
  
If OpenFile(0, "LastLogin.txt") <> 0 
  SetGadgetText(#Gadget_mysqltest_hostbox,     ReadString()) 
  SetGadgetText(#Gadget_mysqltest_userbox,     ReadString()) 
  SetGadgetText(#Gadget_mysqltest_passwordbox, ReadString()) 
  SetGadgetText(#Gadget_mysqltest_databasebox, ReadString()) 
  SetGadgetText(#Gadget_mysqltest_portbox,     ReadString()) 
  If ReadString() = "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(GetGadgetText(#Gadget_mysqltest_hostbox)) 
    WriteStringN(GetGadgetText(#Gadget_mysqltest_userbox)) 
    WriteStringN(GetGadgetText(#Gadget_mysqltest_passwordbox)) 
    WriteStringN(GetGadgetText(#Gadget_mysqltest_databasebox)) 
    WriteStringN(GetGadgetText(#Gadget_mysqltest_portbox)) 
    WriteStringN("1") 
    CloseFile(0) 
  EndIf 
EndIf 

Return 

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

CheckEnter: 

FocusID = GetFocus_()                                       ; Get the id of the window/object that has focus 
Select FocusID                                             ; Use the id in a gadget selection 
  Case GadgetID(#Gadget_mysqltest_querybox)                 ; Gadget is the barcode box 
    Query.s = GetGadgetText(#Gadget_mysqltest_querybox)     ; Get the text from the barcode box 
    Gosub ConnectToDatabase                                ; Reusable data return routine 
EndSelect                                                  ; End the selection 

Return 

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

ConnectToDatabase: 

If libOpened 
  host.s    = GetGadgetText(#Gadget_mysqltest_hostbox) 
  user.s    = GetGadgetText(#Gadget_mysqltest_userbox) 
  passwd.s  = GetGadgetText(#Gadget_mysqltest_passwordbox) 
  db.s      = GetGadgetText(#Gadget_mysqltest_databasebox) 
  port.l    = Val(GetGadgetText(#Gadget_mysqltest_portbox)) 
  If MySQL_Real_Connect(dbHnd, host, user, passwd, db, port.l, 32) <> 0 
    SetWindowTitle(#Window_mysqltest, #MainTitle + "Connected and getting your data, please wait") 
    Gosub GetSqlData 
  Else 
    MySQL_GetError(dbHnd, 1) 
    Connect.l = 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: 

ClearGadgetItemList(#Gadget_mysqltest_datalist) 
  
If fieldNum <> 0                                                  ; Remove columns from previous query 
  For colremove = 1 To fieldNum - 1 
    RemoveGadgetColumn(#Gadget_mysqltest_datalist, 1) 
  Next colremove 
  fieldNum = 0 
EndIf 
    
Result = MySQL_Real_Query (dbHnd, Query.s)                        ; Run the given query 

If Result                                                         ; If the query didn't work, give an error 
  MySQL_GetError(dbHnd, 1) 
Else 
  ;---------------------------------------------------------------------------------------------- 
  *mysqlResult = MySQL_Use_Result(dbHnd)                          ; If data returned, use the result 
  If *mysqlResult = 0 
    If MySQL_Field_Count(dbHnd)                                   ; No fields are returned so that's an error 
      MySQL_GetError(dbHnd, 1) 
    Else 
      MySQL_GetError(dbHnd, 1)                                    ; Fields are returned, so no error but query didn't return data 
    EndIf 
  Else 
    ;---------------------------------------------------------------------------------------------- 
    affRows   = CallFunction(#libmysql,"mysql_affected_rows",dbHnd)       ; How many rows affected 
    fieldNum  = CallFunction(#libmysql,"mysql_num_fields", *mysqlResult)  ; How many fields 
    rowsNum   = CallFunction(#libmysql,"mysql_num_rows", *mysqlResult)    ; How many rows 
    ;-------------------------------------------------------------------------------------------- 
    For coladd = 1 To fieldNum - 1 
      AddGadgetColumn(#Gadget_mysqltest_datalist, 1, "Data", 100) 
    Next coladd 
    ;-------------------------------------------------------------------------------------------- 
    Repeat                                                               ; Sequentially process all returned data 
      *mysqlRow = MySQL_Fetch_Row(*mysqlResult) 
      If *mysqlRow <> 0 
        *mysqlLen = MySQL_Fetch_Lengths(*mysqlResult)          
        row = "" 
        For j = 1 To fieldNum                                            ; Length of given field 
          length = PeekL(*mysqlLen + 4 * (j - 1))            
          fieldptr = PeekL(*mysqlRow + 4 * (j - 1)) 
          If fieldptr > 0 
            content.s = PeekS(fieldptr, length) 
          Else 
            content = "NULL"                                              ; Zero pointer returned means empty field 
          EndIf 
          row = row + content + ";"                                       ; Debug content (individual collumns) 
        Next j 
        rowstring.s = ReplaceString(row, ";", Chr(10), 1,1) 
        AddGadgetItem(#Gadget_mysqltest_datalist, -1, rowstring.s)              ; Dump the row to the listbox 
      EndIf 
    Until *mysqlRow = 0 
    Result.l = MySQL_Free_Result(*mysqlResult) 
    MySQL_Close(dbHnd)                                                    ; Close the database when no longer needed 
    ;-------------------------------------------------------------------------------------------- 
  EndIf 
  ;---------------------------------------------------------------------------------------------- 
EndIf 

For WidthSet = 0 To fieldNum - 1 
  SendMessage_(GadgetID(#Gadget_mysqltest_datalist), #LVM_SETCOLUMNWIDTH, WidthSet, #LVSCW_AUTOSIZE) 
Next WidthSet 

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

Return 

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

DumpListToDisk: 

Lines.l = CountGadgetItems(#Gadget_mysqltest_datalist)                    ; How many lines to save 
  
DumpList.s = 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.s = GetGadgetItemText(#Gadget_mysqltest_datalist, ListItems, totalFields) 
      outString.s + currentField.s + ";" 
    Next totalFields 
    WriteStringN(outString.s) 
    outString.s = "" 
    ;-------------------------------------------------------------------------------------------- 
  Next ListItems 
  CloseFile(0) 
  ;---------------------------------------------------------------------------------------------- 
Else 
  SetWindowTitle(#Window_mysqltest, #MainTitle + "Cannot save the list to disk, something went wrong") 
EndIf 
  
Return

robgru · Beitrag von **robgru** » 20.09.2005 11:56

Danke für die Hilfe, werde den code mal ausprobieren, ....

danke und mfg

Robert

PureBoard

MYSQL Datenbank LESEN/Schreiben

MYSQL Datenbank LESEN/Schreiben

mySQL-Probelm