Voici une vidéo tutoriel sur la mise en place d'un serveur LAMP (Linux,Apache,Mysql (MariaDb),Php) ainsi que phpMyAdmin dans une machine virtuel.
Je détaille également les différents réglages pour pouvoir être utilisé avec PureBasic. Juste une connexion à une base de données.
Par la suite je réaliserais une deuxième partie, avec une gestion élémentaire en Pb.
https://youtu.be/MCfvlt1GxnU
Petit exemple d'application, j'ai séparé mon code en deux partie dont un module qui ne s'occupe que des transaction mySql. Pour que cela fonctionne chez vous vous devez remplacer l'adresse ip, le nom de la base l'utilisateur et le mot de passe en fonction de votre serveur.
Remarque : une application de ce type aurais besoins d'un threath de rafraîchissement et comme je n'ai pas trouvé également de méthodes pour savoir quel record est locked, je ferais perso un table pour gérer cela. Il est évident que la gestion d'un db réseau demande de la rigueur et plus de travail.
ATTENTION : L'exécutable autonome à besoin de la dll mariaDb pour fonctionner.
module DB
Code : Tout sélectionner
DeclareModule DB
Global ipAddress.s = "192.168.1.12"
Global user.s = "post_2"
Global psw.s = "1234"
Global dbName.s = "test"
Declare new()
Interface db
setFloat(value.f,column)
setLong(value.l,column)
setString(value.s,column)
setQuad(value.q,column)
getFloat.f(column)
getString.s(column)
getLong.l(column)
getQuad.q(column)
query(request.s)
update(request.s)
nextRecord()
close()
EndInterface
EndDeclareModule
Module DB
EnableExplicit
UseMySQLDatabase()
Structure _struct
*methods
conectionId.l
EndStructure
Procedure new()
Protected *this._struct = AllocateStructure(_struct)
With *this
; try to connect
\conectionId = OpenDatabase(#PB_Any,"host="+ipAddress+" port=3306 dbname="+dbName,user, psw)
; if connection failed
If Not \conectionId
MessageRequester("DATABASE ERROR","Cannot connect to database "+DatabaseError(),#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
\methods = ?S_MTH
; return object instance
ProcedureReturn *this
EndWith
EndProcedure
Procedure close(*this._struct)
With *this
If IsDatabase(\conectionId)
CloseDatabase(\conectionId)
EndIf
FreeStructure(*this) ; free object instance
EndWith
EndProcedure
Procedure setFloat(*this._struct,value.f,column)
With *this
If \conectionId
SetDatabaseFloat(\conectionId,column,value)
EndIf
EndWith
EndProcedure
Procedure setLong(*this._struct,value.l,column)
With *this
If \conectionId
SetDatabaseLong(\conectionId,column,value)
EndIf
EndWith
EndProcedure
Procedure setString(*this._struct,value.s,column)
With *this
If \conectionId
SetDatabaseString(\conectionId,column,value)
EndIf
EndWith
EndProcedure
Procedure setQuad(*this._struct,value.q,column)
With *this
If \conectionId
SetDatabaseQuad(\conectionId,column,value)
EndIf
EndWith
EndProcedure
Procedure.f getFloat(*this._struct,column)
With *this
If \conectionId
ProcedureReturn GetDatabaseFloat(\conectionId,column)
EndIf
ProcedureReturn 0
EndWith
EndProcedure
Procedure.l getLong(*this._struct,column)
With *this
If \conectionId
ProcedureReturn GetDatabaseLong(\conectionId,column)
EndIf
ProcedureReturn 0
EndWith
EndProcedure
Procedure.s getString(*this._struct,column)
With *this
If \conectionId
ProcedureReturn GetDatabaseString(\conectionId,column)
EndIf
ProcedureReturn ""
EndWith
EndProcedure
Procedure getQuad(*this._struct,column)
With *this
If \conectionId
ProcedureReturn GetDatabaseQuad(\conectionId,column)
EndIf
ProcedureReturn 0
EndWith
EndProcedure
Procedure query(*this._struct,request.s)
With *this
Protected returnedValue.b =#False
If \conectionId
If Not DatabaseQuery(\conectionId,request)
MessageRequester("QUERY REQUEST ERROR",request+Chr(10)+DatabaseError(),#PB_MessageRequester_Error)
Else
returnedValue = #True
EndIf
EndIf
ProcedureReturn returnedValue
EndWith
EndProcedure
Procedure update(*this._struct,request.s)
Protected returnedValue.b = #False
With *this
If \conectionId
If Not DatabaseUpdate(\conectionId,request)
MessageRequester("UPDATE REQUEST ERROR",request+Chr(10)+DatabaseError(),#PB_MessageRequester_Error)
Else
returnedValue = #True
EndIf
close(*this)
EndIf
ProcedureReturn returnedValue
EndWith
EndProcedure
Procedure nextRecord(*this._struct)
With *this
ProcedureReturn NextDatabaseRow(\conectionId)
EndWith
EndProcedure
DataSection
S_MTH:
Data.i @setFloat()
Data.i @setLong()
Data.i @setString()
Data.i @setQuad()
Data.i @getFloat()
Data.i @getString()
Data.i @getLong()
Data.i @getQuad()
Data.i @query()
Data.i @update()
Data.i @nextRecord()
Data.i @close()
E_MTH:
EndDataSection
EndModule
Code : Tout sélectionner
XIncludeFile "db.pbi"
Enumeration
#MAIN_FORM
#CUST_FORM
EndEnumeration
Enumeration
#LST
#BT_ADD
#BT_DELETE
#BT_EDIT
#FNAME
#LNAME
#BT_VALIDATE
#BT_CANCEL
EndEnumeration
Declare fillCustomers()
Procedure createClientTable()
Protected myDb.DB::db = DB::new()
Protected req.s
If myDb
req = "CREATE TABLE IF NOT EXISTS customers ("+
"id int AUTO_INCREMENT,"+
"firstname VARCHAR(60),"+
"lastname VARCHAR(60),"+
"PRIMARY KEY (id))"
myDb\update(req)
EndIf
EndProcedure
Procedure exit()
CloseWindow(#MAIN_FORM)
End
EndProcedure
Procedure fillCustomers()
Protected myDb.DB::db = DB::new()
Protected req.s = "SELECT id,lastname,firstname FROM customers ORDER BY lastname,firstname"
; send query request
If myDb\query(req)
; erase customers list
ClearGadgetItems(#LST)
; look for all list
Protected index = 0 ; current list index
While myDb\nextRecord()
Protected line.s = myDb\getString(1)+Chr(10)+myDb\getString(2)
AddGadgetItem(#LST,-1,line)
; we put record id at current index
SetGadgetItemData(#LST,index,myDb\getLong(0))
index + 1
Wend
; don't forget to close the database
myDb\close()
EndIf
EndProcedure
Procedure closeCustomer()
CloseWindow(#CUST_FORM)
EndProcedure
Procedure fillDetail(id)
Protected myDb.DB::db = DB::new()
Protected req.s = "SELECT firstname,lastname FROM customers WHERE id = ? FOR UPDATE"
myDb\setLong(id,0)
; we use FOR UPDATE for looked the record
If myDb\query(req)
If myDb\nextRecord()
SetGadgetText(#LNAME,myDb\getString(1))
SetGadgetText(#FNAME,myDb\getString(0))
EndIf
EndIf
myDb\close() ; close db and free object instance
EndProcedure
Procedure evAdd()
If Not Len(GetGadgetText(#LNAME))
MessageRequester("Field empty","you must fill the Last name",#PB_MessageRequester_Warning)
ProcedureReturn
EndIf
Protected myDb.DB::db = DB::new()
Protected req.s = "INSERT INTO customers (firstname,lastname) VALUES (?,?)"
If myDb
myDb\setString(GetGadgetText(#LNAME),0)
myDb\setString(GetGadgetText(#FNAME),1)
myDb\update(req)
fillCustomers()
closeCustomer()
EndIf
EndProcedure
Procedure evUpdate()
Protected index = GetGadgetState(#LST)
If Not Len(GetGadgetText(#LNAME))
MessageRequester("Field empty","you must fill the Last name",#PB_MessageRequester_Warning)
ProcedureReturn
EndIf
; a record is selected
If index
Protected id = GetGadgetItemData(#LST,index)
Protected myDb.DB::db = DB::new()
Protected req.s = "UPDATE customers SET firstname=?,lastname=? WHERE id = ?"
If myDb
myDb\setString(GetGadgetText(#FNAME),0)
myDb\setString(GetGadgetText(#LNAME),1)
myDb\setLong(id,2)
If myDb\update(req)
fillCustomers()
closeCustomer()
EndIf
EndIf
EndIf
EndProcedure
Procedure openCustomerDetails(id = 0)
Protected x = 10,y = 10,h = 30,M = 10,w = 180
OpenWindow(#CUST_FORM,0,0,200,190,"Customer details",#PB_Window_SystemMenu|#PB_Window_WindowCentered,WindowID(#MAIN_FORM))
TextGadget(#PB_Any,x,y,w,h,"First name")
y+h
StringGadget(#FNAME,X,Y,w,h,"")
y+h+m
TextGadget(#PB_Any,x,y,w,h,"Last name")
y+h
StringGadget(#LNAME,X,Y,w,h,"")
y+h+m
ButtonGadget(#BT_VALIDATE,x,y,80,h,"Validate")
x + 90
ButtonGadget(#BT_CANCEL,x,y,80,h,"Chancel")
; if id = 0 we are in new mode edition else in editing mode
If Not id
BindGadgetEvent(#BT_VALIDATE,@evAdd())
Else
BindGadgetEvent(#BT_VALIDATE,@evUpdate())
fillDetail(id)
EndIf
BindEvent(#PB_Event_CloseWindow,@closeCustomer(),#CUST_FORM)
BindGadgetEvent(#BT_CANCEL,@closeCustomer())
EndProcedure
; main form event management
Procedure evEdit()
Protected index = GetGadgetState(#LST)
; a record is selected
If index
Protected id = GetGadgetItemData(#LST,index)
openCustomerDetails(id)
EndIf
EndProcedure
Procedure evNew()
openCustomerDetails(0)
EndProcedure
Procedure evDelete()
EndProcedure
Procedure openMainForm()
OpenWindow(#MAIN_FORM,0,0,800,600,"Customers list",#PB_Window_ScreenCentered|#PB_Window_SystemMenu)
ListIconGadget(#LST,10,10,680,580,"first name",340)
AddGadgetColumn(#LST,1,"last name",330)
ButtonGadget(#BT_ADD,700,10,90,30,"Add")
ButtonGadget(#BT_EDIT,700,10+40,90,30,"Edit")
ButtonGadget(#BT_DELETE,700,10+80,90,30,"Delete")
fillCustomers()
BindEvent(#PB_Event_CloseWindow,@exit(),#MAIN_FORM)
BindGadgetEvent(#BT_ADD,@evNew())
BindGadgetEvent(#BT_EDIT,@evEdit())
EndProcedure
createClientTable()
openMainForm()
Repeat:WaitWindowEvent():ForEver