Das deutsche PureBasic-Forum
https://www.purebasic.fr/german/
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!! 
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
EndIfCode: 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