Code: Select all
; ======================================================================================================================
; Demo of MySQLAPI class
;
; You must have access to a running MySQL server. This demo app will create a database and a table and present
; a simple GUI to add, edit, or remove records.
;
; Programmer: panofish (www.autohotkey.com)
; Modified by: just me (www.autohotkey.com)
; AutoHotkey: v1.1.10.+
; ======================================================================================================================
#NoEnv
#SingleInstance Force
SetBatchLines, -1
ListLines, Off
#Include Class_MySQLAPI.ahk ; pull from local directory
OnExit, AppExit
Global MySQL_SUCCESS := 0
; ======================================================================================================================
; Settings
; ======================================================================================================================
UserID := "SPRINGUSER" ; User name - must have privileges to create databases
UserPW := "SPRINGPASS" ; User''s password
Server := "sh2051.evanzo-server.de" ; Server''s host name or IP address
Database := "SPRINGDB" ; Name of the database to work with
DropDatabase := False ; DROP DATABASE
DropTable := False ; DROP TABLE Address
; ======================================================================================================================
; Connect to MySQL
; ======================================================================================================================
; Instantiate a MYSQL object
If !(My_DB := New MySQLAPI)
ExitApp
; Get the version of libmysql.dll
ClientVersion := My_DB.Get_Client_Info()
; Connect to the server, Host can be a hostname or IP address
If !My_DB.Connect(Server, UserID, UserPW) { ; Host, User, Password
MsgBox, 16, MySQL Error!, % "Connection failed!`r`n" . My_DB.ErrNo() . " - " . My_DB.Error()
ExitApp
}
; ======================================================================================================================
; CREATE DADABASE Test
; ======================================================================================================================
If (DropDatabase)
My_DB.Query("DROP DATABASE IF EXISTS " . DataBase)
SQL := "CREATE DATABASE IF NOT EXISTS " . Database . " DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin"
My_DB.Query(SQL)
; ======================================================================================================================
; Select the database as default
; ======================================================================================================================
My_DB.Select_DB(Database)
; ======================================================================================================================
; CREATE TABLE User
; ======================================================================================================================
If (DropTable)
My_DB.Query("DROP TABLE IF EXISTS User")
SQL := "CREATE TABLE IF NOT EXISTS User ( "
. "Team_ID INT(2), "
. "Team VARCHAR(255), "
. "User_ID INT(5),"
. "Authority INT(1), "
. "Title INT(1), "
. "Name_1 VARCHAR(255), "
. "Name_2 VARCHAR(255), "
. "Mail VARCHAR(255), "
. "Inexso_User VARCHAR(255), "
. "Password VARCHAR(255), "
. "Status INT(1) ), "
. "PRIMARY KEY (User_ID) )"
My_DB.Query(SQL)
; ======================================================================================================================
; Build GUI
; ======================================================================================================================
Fields := ["Team_ID", "Team", "User_ID", "Authority", "Title", "Name_1", "Name_2", "Mail", "Inexso_User", "Password", "Status"]
Gui, 1:Default
Gui, Margin, 10, 10
Gui, +OwnDialogs
; To increase performance use Count option if you know the max number of lines
; LV0x00010000 = LV_EX_DOUBLEBUFFER
Gui, Add, Edit, x10 y10 w250 vSearchUserDB
Gui, Add, ListView, x10 y+5 r10 w375 AltSubmit vList1 Grid -Multi +LV0x00010000 gSubListView
Gui, Add, Button, x10 y+10 w185 h23 gShowHide, New/Edit Team
Gui, Add, Button, x+5 yp w185 h23, New/Edit User
Gui, Add, Text, x10 y+10 w375 h1 0x10
; Gui, Add, Text, x10 Center w375 h23 +0x200 +Border,
Gui, Add, Text, Section x10 y+10 w70 h23 +0x200 Right +Border, Team ID:
Gui, Add, Edit, x+5 yp w50 h23 vTeam_ID
GuiControlGet, C, Pos, Team:
Gui, Add, Text, x+5 Center w55 h23 +0x200 +Border, Team:
Gui, Add, Edit, x+5 w185 h23 vTeam
Gui, Add, Text, x10 y+10 w375 h1 0x10
; Gui, Add, Text, x10 Center w375 h23 +0x200 +Border,
Gui, Add, Text, x10 y+10 Right w70 h23 +0x200 Right +Border, Team:
Gui, Add, DropDownList, x+5 yp w300 vTeams AltSubmit,
Gui, Add, Text, x10 y+5 Right w70 h23 +0x200 Right +Border, User ID:
Gui, Add, Edit, x+5 w250 w50 h23 vUser_ID
Gui, Add, CheckBox, x+5 w55 h23 vStatus +Border, Active
Gui, Add, DropDownList, x+5 w185 vAuthority AltSubmit, Agent|Team Leader|Trainer|Administrator
Gui, Add, Text, x10 y+10 w375 h1 0x10
; Gui, Add, Text, x10 Center w375 h23 +0x200 +Border,
Gui, Add, Text, x10 y+10 w70 h23 +0x200 Right +Border, Title:
Gui, Add, DropDownList, x+5 w110 vTitle AltSubmit, Frau|Herr
Gui, Add, Text, x10 y+5 w70 h23 +0x200 Right +Border, First Name:
Gui, Add, Edit, x+5 yp w110 h23 vName_1
Gui, Add, Text, x+5 yp w70 h23 +0x200 Right +Border, Name:
Gui, Add, Edit, x+5 yp w110 h23 vName_2
Gui, Add, Text, x10 y+5 w70 h23 +0x200 Right +Border, Mail:
Gui, Add, Edit, x+5 yp w300 h23 vMail
Gui, Add, Text, x10 y+5 w70 h23 +0x200 Right +Border, Inexso User:
Gui, Add, Edit, x+5 yp w300 h23 vInexso_User
Gui, Add, Text, x10 y+5 w70 h23 +0x200 Right +Border, Password:
Gui, Add, Edit, x+5 yp w110 h23 vPassword +Password
Gui, Add, Text, x+5 yp w70 h23 +0x200 Right +Border, Password:
Gui, Add, Edit, x+5 yp w110 h23 vPasswordRepeat +Password
Gui, Add, Text, x10 y+10 w375 h1 0x10
Gui, Add, Button, x10 y+10 w90 h23 vBtnAddUpd gSubBtnAction Default, Add
Gui, Add, Button, x+5 yp w90 h23 gSubBtnClear, Clear
Gui, Add, Button, x+5 yp w90 h23 vBtnDelete gSubBtnAction, Delete
Gui, Add, Button, x+5 yp w90 h23 gUpdateListView, Reload
Gui, Add, Text, x10 y+10 w375 h1 0x10
; Gui, Add, Edit, x+10 w250 vAuthority
; Gui, Add, Text, xs Right w70, City
; Gui, Add, Edit, x+10 w150 vCity
; Gui, Add, Text, xs Right w70, State
; Gui, Add, Edit, x+10 w30 Uppercase Limit2 vState
; Gui, Add, Text, xs Right w70, Zip
; Gui, Add, Edit, x+10 w60 Number Limit5 vZip
Gui, Add, StatusBar
Gui, Show, AutoSize, MySQLAPI Demo - Client version: %ClientVersion%
Gosub, UpdateListView
GetTeams()
; ======================================================================================================================
; Make a first query and get the result "manually"
; ======================================================================================================================
SQL := "SELECT COUNT(*) FROM User"
If (My_DB.Query(SQL) = MySQL_SUCCESS) {
My_Result := My_DB.Store_Result()
My_Field := My_DB.Fetch_Field(My_Result)
FieldName := StrGet(NumGet(My_Field + 0, 0, "UPtr"), "UTF-8")
My_Row := My_DB.Fetch_Row(My_Result)
FieldValue := StrGet(NumGet(My_Row + 0, 0, "UPtr"), "UTF-8")
My_DB.Free_Result(My_Result)
}
MsgBox, 0, MySQLAPI Demo, Query:`r`n%SQL%`r`n`r`nResult:`r`nName = %FieldName%`r`nValue = %FieldValue%`nMy_Result = %My_Result%`nMy_Field = %My_Field%`nMy_Row = %My_Row%
Return
; ======================================================================================================================
; ListView event handler
; DoubleClick a row to delete / edit the entry
; ======================================================================================================================
SubListView:
If (A_GuiEvent = "DoubleClick") {
CurrentRow := A_EventInfo
Loop, % LV_GetCount("Column") {
LV_GetText(Value, CurrentRow, A_Index)
If (Fields[A_Index] = "Team") {
GuiControl, , % Fields[A_Index], %Value%
GuiControl, Choose, ComboBox1, %Value%
}
Else If (Fields[A_Index] = "Authority")
GuiControl, Choose, ComboBox2, %Value%
Else If (Fields[A_Index] = "Title")
GuiControl, Choose, ComboBox3, %Value%
Else If (Fields[A_Index] = "Status")
GuiControl, , Button3, %Value%
Else
GuiControl, , % Fields[A_Index], %Value%
}
GuiControl, +ReadOnly, User_ID
GuiControl, , BtnAddUpd, Update
; GuiControl, Focus, Team_ID
}
Return
; ======================================================================================================================
; Perform the requested action
; ======================================================================================================================
SubBtnAction:
Gui, +OwnDialogs
Gui, Submit, NoHide
GuiControlGet, User_ID
MsgBox % Authority
If !Trim(User_ID, " `t`r`n")
Return
; Escape mysql special characters in case user entered them
V1 := My_DB.Real_Escape_String(Team_ID)
V2 := My_DB.Real_Escape_String(Team)
V3 := My_DB.Real_Escape_String(User_ID)
V4 := My_DB.Real_Escape_String(Authority)
V5 := My_DB.Real_Escape_String(Title)
V6 := My_DB.Real_Escape_String(Name_1)
V7 := My_DB.Real_Escape_String(Name_2)
V8 := My_DB.Real_Escape_String(Mail)
V9 := My_DB.Real_Escape_String(Inexso_User)
V10 := My_DB.Real_Escape_String(Password)
V11 := My_DB.Real_Escape_String(Status)
; Get the action
GuiControlGet, Action, , %A_GuiControl%
SQL := ""
If (Action = "Add") {
;-----------------------------------------------------------------------------------------------------------------
; Insert new record
;-----------------------------------------------------------------------------------------------------------------
SB_SetText("Inserting new record!")
SQL := "INSERT INTO User ( Team_ID, Team, User_ID, Authority, Title, Name_1, Name_2, Mail, Inexso_User, Password, Status ) "
. "VALUES ( '" . V1 . "', '" . V2 . "', '" . V3 . "', '" . V4 . "', '" . V5 . "', '" . V6 . "', '" . V7 . "', '" . V8 . "', '" . V9 . "', '" . V10 . "', '" . V11 . "' )"
Done := "inserted!"
}
Else If (Action = "Delete") {
;-----------------------------------------------------------------------------------------------------------------
; Delete record
;-----------------------------------------------------------------------------------------------------------------
MsgBox, 36, Delete, Do you really want to delete '%User_ID%'?
IfMsgBox, Yes
{
SB_SetText("Deleting record!")
SQL := "DELETE FROM User WHERE User_ID = '" . User_ID . "'"
Done := "deleted!"
}
}
Else If (Action = "Update") {
;-----------------------------------------------------------------------------------------------------------------
; Update record
;-----------------------------------------------------------------------------------------------------------------
SB_SetText("Updating record!")
SQL := "UPDATE User SET Team_ID = '" . V1 . "', Team = '" . V2 . "', Authority='" . V4 . "', Title='" . V5 . "', Name_1='" . V6 . "', Name_2='" . V7 . "', Mail='" . V8 . "', Inexso_User='" . V9 . "', Password='" . V10 . "', Status='" . V11 . "' "
. "WHERE User_ID = '" . V3 . "'"
Done := "updated!"
}
If (SQL) {
If (My_DB.Query(SQL) = MySQL_SUCCESS) {
Rows := My_DB.Affected_Rows()
Gosub, UpdateListView
GoSub, SubBtnClear
SB_SetText(Rows . " row(s) " . Done)
} Else {
MsgBox, 16, MySQL Error!, % My_DB.ErrNo() . ": " . My_DB.Error()
}
}
Return
; ======================================================================================================================
; Clear Edits
; ======================================================================================================================
SubBtnClear:
For Each, Ctrl In Fields
GuiControl, , % Fields[A_Index]
GuiControl, Choose, ComboBox2, 0
GuiControl, Choose, ComboBox3, 0
GuiControl, -Readonly, User_ID
GuiControl, -Readonly, Team
GuiControl, , BtnAddUpd, Add
GuiControl, Focus, Name
SB_SetText("")
Return
; ======================================================================================================================
; Fill ListView with existing addresses from database
; ======================================================================================================================
UpdateListView:
SQL := "SELECT Team_ID, Team, User_ID, Authority, Title, Name_1, Name_2, Mail, Inexso_User, Password, Status FROM User ORDER BY Team_ID"
If (My_DB.Query(SQL) = MySQL_SUCCESS) {
Result := My_DB.GetResult()
MsgBox, %Result%
LV_Fill(Result, "List1")
SB_SetText("ListView has been updated: " . Result.Columns . " columns - " . Result.Rows . " rows.")
}
Return
; ======================================================================================================================
; GUI was closed
; ======================================================================================================================
GuiClose:
ExitApp
AppExit:
My_DB := ""
ExitApp
; ======================================================================================================================
; Fill ListView with the result of a query.
; Note: The current data in the ListView are replaced with the new data.
; Parameters:
; Result - Result object returned from MySQLDAPI.Query()
; ListViewName - Name of the ListView''s asociated variable
; ======================================================================================================================
LV_Fill(Result, ListViewName) {
;--------------------------------------------------------------------------------------------------------------------
; Delete all rows and columns of the ListView
;--------------------------------------------------------------------------------------------------------------------
GuiControl, -Redraw, %ListViewName% ; to improve performance, turn off redraw then turn back on at end
Gui, ListView, %ListViewName% ; specify which listview will be updated with LV commands
LV_Delete() ; delete all rows in the listview
Loop, % LV_GetCount("Column") ; delete all columns of the listview
LV_DeleteCol(1)
;--------------------------------------------------------------------------------------------------------------------
; Parse field names
;--------------------------------------------------------------------------------------------------------------------
Loop, % Result.Fields.MaxIndex() {
LV_InsertCol(A_Index, "", Result.Fields[A_Index].Name)
}
;--------------------------------------------------------------------------------------------------------------------
; Parse rows
;--------------------------------------------------------------------------------------------------------------------
Count := 0
Loop, % Result.MaxIndex() {
RowNum := LV_Add("") ; add a blank row to the listview
Row := Result[A_Index] ; extract the row from the result
Loop, % Row.MaxIndex() { ; populate the columns of the current row
Value := Row[A_Index]
; If (A_Index = 5)
; Value := SubStr("" . Value, -4)
LV_Modify(RowNum, "Col" . A_Index, Value) ; update current column of current row
}
}
;--------------------------------------------------------------------------------------------------------------------
; Autosize columns: should be done outside the row loop to improve performance
;--------------------------------------------------------------------------------------------------------------------
Loop, % LV_GetCount("Column")
LV_ModifyCol(A_Index, "AutoHdr") ; Autosize header.
LV_ModifyCol(1, "Sort Logical")
GuiControl, +Redraw, %ListViewName% ; to improve performance, turn off redraw at beginning then turn back on at end
Return
}
GetTeams()
{
Loop % LV_GetCount()
{
LV_GetText(TeamName, A_Index, 2)
Teams .= TeamName . "|"
}
Sort, Teams, D|U
GuiControl,, ComboBox1, % "|" Teams
}
ShowHide:
GuiControlGet, Button1
MsgBox % Button1
; GuiControl, % (BtnShowHide = "New/Edit Team" ? "Show" : "Hide"), EdtShowHide
; GuiControl, , BtnShowHide, % (BtnShowHide = "New/Edit Team" ? "Cancel" : "New/Edit Team")
Gui, Show, AutoSize
Return
!r::
AppReload:
Reload
Wenn ich die ListBox mit einer Suchfunktion versehen will, muss ich die ListBox erst in ein Array verpacken um die LB anschließend mit den Suchergebnissen zu füllen? Irgendwie kriege ich nicht raus, wo die Daten stecken.
Ich möchte ein Login für die Anwendung einrichten und kann, mangels Verständnis, nicht logisch Schlussfolgern. Kann ich hierbei auch mit einem Loop durch die Daten rennen und den Benutzer suchen und abgleichen, ob das von Ihm eingegebene Passwort übereinstimmt?
Eigentlich müssten doch die ganzen Daten in Variablen schon vorhanden sein, oder nicht?
Oh ne oh ne, wie blöd man sich vorkommen kann. Oder bin ich das etwa?
Gruß
Nmk