Dear all,
After several weeks of studying and testing the combination “MariaDB/MySQL - HMG - HMGSQL”, I like to present you my experience.
First of all I like to thank Mr. Rathinagiri , the author of HMGSQL and of course Mr. Lopez for giving us HMG in the first place.
Below is the sourcecode of a program I made to test the above combination and the recordlocking possibilities. It is only meant to be an example of what can be done with MariaDB/MySQL.
Code: Select all
//demomysql
#include "hmg.ch"
#include "hmgmysql.prg"
*------------------------------------------------------------------------------*
Function Main
*------------------------------------------------------------------------------*
Private bColor := { || if ( This.CellRowIndex/2 == int(This.CellRowIndex/2) , { 208,240,255 } , { 128,208,255 } ) }
Public dbo := Nil
Define Window win1 ;
At 0,0 ;
Width 620 ;
Height 520 ;
Title 'Query window ';
MAIN ;
ON INIT Connect() ;
ON RELEASE Disconnect()
ON KEY RETURN ACTION Display()
ON KEY INSERT ACTION AppendRow()
ON KEY DELETE ACTION DeleteRow()
Define Grid Grid_1
Row 0
Col 10
Width 600
Height 430
Headers {'Rowid','Code','Name','Residents'}
Widths {100,100,240,150}
DYNAMICBACKCOLOR { bColor , bColor , bColor ,bColor}
end Grid
define button refresh
row 440
col 70
Width 70
Height 30
caption "refresh"
onclick DoQuery()
end button
define button display
row 440
col 170
Width 70
Height 30
caption "show"
onclick Display()
end button
define button edit
row 440
col 270
Width 70
Height 30
caption "edit"
onclick EditRow()
end button
define button add
row 440
col 370
Width 70
Height 30
caption "add row"
onclick AppendRow()
end button
define button delete
row 440
col 470
Width 70
Height 30
caption "delete"
onclick DeleteRow()
end button
End Window
win1.Center
win1.Activate
Return
*------------------------------------------------------------------------------*
Procedure Connect
*------------------------------------------------------------------------------*
// Connect
if netname()="MEDION"
dbo=connect2db("localhost","root","","namebook")
createtable()
// miscsql(dbo,"SET GLOBAL innodb_lock_wait_timeout = 1")
else
dbo=connect2db("medion","laptop","","namebook")
endif
DoQuery()
Return
*------------------------------------------------------------------------------*
Procedure Disconnect()
*------------------------------------------------------------------------------*
dbo:Destroy()
Return
*------------------------------------------------------------------------------*
Function DoQuery()
*------------------------------------------------------------------------------*
Local oQuery
Local i
oQuery := sql(dbo,"Select * From Country" )
win1.Grid_1.DeleteAllItems()
For i := 1 To len(oQuery)
win1.Grid_1.AddItem({sql2c(oQuery[i,1]), sql2c(oQuery[i,2]),sql2c(oQuery[i,3]),sql2c(oQuery[i,4])})
Next
Return
*------------------------------------------------------------------------------*
Procedure DeleteRow()
*------------------------------------------------------------------------------*
Local oQuery
Local aGridRow
Local i
Local cROWID
i := win1.Grid_1.Value
if i == 0
Return
EndIf
if MsgYesNo("Are You Sure?")
aGridRow := win1.Grid_1.Item (i)
cROWID := aGridRow [1]
oQuery:= miscsql(dbo,"start transaction")
oQuery:= sql(dbo, "Select * From country WHERE ROWID = " + c2sql(cROWID)+ " for update") //lock row
if len(oquery) > 0 // if row is locked = 0
oQuery := miscsql(dbo, "DELETE FROM country WHERE ROWID = " + c2sql(cROWID))
oQuery:= miscsql(dbo,"commit") //unlock row
DoQuery ()
endif
EndIf
Return
*------------------------------------------------------------------------------*
Procedure EditRow()
*------------------------------------------------------------------------------*
Local oQuery
Local oRow
Local i
Local aresults := {}
i := win1.Grid_1.Value
if i == 0
Return
EndIf
aGridRow := win1.Grid_1.Item (i)
cROWID := aGridRow[1]
oQuery:= miscsql(dbo,"start transaction")
oQuery:= sql(dbo, "Select * From country WHERE ROWID = " + c2sql(cROWID)+ " for update")
if len(oquery) > 0 // if row is locked = 0
For i := 1 To len(oQuery)
ccode := sql2c(oQuery[i,2])
cname := sql2c(oQuery[i,3])
cresidents := sql2c(oQuery[i,4])
next
aResults := InpWindow ('Edit Row : '+cROWID,{'Code:', 'Name:' , 'residents:' },{ccode,cName ,cresidents },{32,32 ,32 },0,0,.T.)
If aResults [1] != Nil
cCode := c2sql(aResults [1])
cName := c2sql(aResults [2])
cresidents:= c2sql(aResults [3])
oQuery:= miscsql(dbo, "UPDATE country SET Code =" + ccode + ",Name = "+ cName +",residents = "+ cresidents +" WHERE ROWID = " + c2sql(cRowid))
oQuery:= miscsql(dbo,"commit")
else
oQuery:= miscsql(dbo,"commit")
EndIf
DoQuery ()
endif
Return
*------------------------------------------------------------------------------*
Procedure AppendRow()
*------------------------------------------------------------------------------*
Local oQuery
Local i
Local aResults := {}
aResults := InpWindow ('New Row',{'code:' ,'Name:' , 'residents:' },{'' ,'','' },{10,32,32 },0,0,.T.)
If aResults [1] != Nil
ccode := c2sql(aResults [1])
cname := c2sql(aResults [2])
cresidents:= c2sql(aResults [3])
oQuery := miscsql(dbo,"INSERT INTO country VALUES (" +'0,' + ccode +',' + cname+',' + cresidents + ')')
DoQuery ()
EndIf
return
*------------------------------------------------------------------------------*
Procedure display()
*------------------------------------------------------------------------------*
Local oQuery
Local oRow
Local i
Local aresults := {}
i := win1.Grid_1.Value
if i == 0
Return
EndIf
aGridRow := win1.Grid_1.Item (i)
cROWID := aGridRow[1]
oQuery:= sql(dbo, "Select * From country WHERE ROWID = " + c2sql(cROWID))
For i := 1 To len(oQuery)
ccode := sql2c(oQuery[i,2])
cname := sql2c(oQuery[i,3])
cresidents := sql2c(oQuery[i,4])
next
aResults := InpWindow ('Row : '+cROWID,{'Code:', 'Name:' , 'residents:' },{ccode,cName ,cresidents },{32,32 ,32 },0,0,.F.)
DoQuery ()
Return
*------------------------------------------------------------------------------*
PROCEDURE CreateTable()
oQuery := miscsql(dbo, "DROP TABLE country")
oQuery := miscsql(dbo, "CREATE TABLE country (ROWID int(8) primary key auto_increment ,CODE char(3) unique, NAME char(50), RESIDENTS int(11))")
oQuery := miscsql(dbo, "INSERT INTO country values (0,'LIT', 'Lithuania', 3369600), (0,'USA', 'United States of America', 305397000), (0,'POR', 'Portugal', 10617600), (0,'POL', 'Poland', 38115967), (0,'AUS', 'Australia', 21446187), (0,'FRA', 'France', 64473140), (0,'RUS', 'Russia', 141900000)")
oQuery := miscsql(dbo, "INSERT INTO country values (0,'BEL', 'Belgium', 11000000)")
r=sql1(dbo,'SELECT version()')
win1.title:=r
RETURN
*------------------------------------------------------------------------------*
function SQL2C(Value)
local cValue := ""
cValue :=alltrim(hb_valtostr(value))
return cValue
*------------------------------------------------------------------------------*
function sql1(dbo1,qstr)
oQuery := dbo1:query(qstr)
if oQuery:neterr()
msgstop(oQuery:error())
oQuery:destroy()
return
endif
oRow:=oQuery:getrow(1)
ores:=oRow:fieldget(1)
oQuery:destroy()
return ores
*------------------------------------------------------------------------------*
Function InpWindow ( Title , aLabels , aValues , aFormats , row , col,yn_edit )
Local i , l , ControlRow , e := 0 ,LN , CN ,r , c , wHeight , diff , BF
bf := set(2)
set(2,.f.)
l := HMG_LEN ( aLabels )
Private aResult [l]
For i := 1 to l
if ValType ( aValues[i] ) == 'C'
if ValType ( aFormats[i] ) == 'N'
If aFormats[i] > 50
e++
Endif
EndIf
EndIf
if ValType ( aValues[i] ) == 'M'
e++
EndIf
Next i
r := 0
c := 0
DEFINE WINDOW _InputWindow ;
AT r,c ;
WIDTH 380 ;
HEIGHT (l*30) + 90 + (e*60) ;
TITLE Title ;
MODAL ;
BACKCOLOR {208,240,255};
NOSIZE
ON KEY RETURN ACTION _InputWindowOk()
ControlRow := 10
For i := 1 to l
LN := 'Label_' + ALLTRIM(STR(i,2,0))
CN := 'Control_' + ALLTRIM(STR(i,2,0))
@ ControlRow , 10 LABEL &LN OF _InputWindow VALUE aLabels [i] WIDTH 90
do case
case ValType ( aValues [i] ) == 'L'
@ ControlRow , 120 CHECKBOX &CN OF _InputWindow CAPTION '' VALUE aValues[i]
ControlRow := ControlRow + 30
case ValType ( aValues [i] ) == 'D'
@ ControlRow , 120 DATEPICKER &CN OF _InputWindow VALUE aValues[i] WIDTH 140
ControlRow := ControlRow + 30
case ValType ( aValues [i] ) == 'N'
If ValType ( aFormats [i] ) == 'A'
@ ControlRow , 120 COMBOBOX &CN OF _InputWindow ITEMS aFormats[i] VALUE aValues[i] WIDTH 140 FONT 'Arial' SIZE 10
ControlRow := ControlRow + 30
ElseIf ValType ( aFormats [i] ) == 'C'
If HB_UAT ( '.' , aFormats [i] ) > 0
@ ControlRow , 120 TEXTBOX &CN OF _InputWindow VALUE aValues[i] WIDTH 240 FONT 'Arial' SIZE 10 NUMERIC INPUTMASK aFormats [i]
Else
@ ControlRow , 120 TEXTBOX &CN OF _InputWindow VALUE aValues[i] WIDTH 240 FONT 'Arial' SIZE 10 MAXLENGTH HMG_LEN(aFormats [i]) NUMERIC
EndIf
ControlRow := ControlRow + 30
Endif
case ValType ( aValues [i] ) == 'C'
If ValType ( aFormats [i] ) == 'N'
If aFormats [i] <= 50
if yn_edit=.F.
@ ControlRow , 120 TEXTBOX &CN OF _InputWindow VALUE aValues[i] WIDTH 240 FONT 'Arial' SIZE 10 MAXLENGTH aFormats [i] READONLY
else
@ ControlRow , 120 TEXTBOX &CN OF _InputWindow VALUE aValues[i] WIDTH 240 FONT 'Arial' SIZE 10 MAXLENGTH aFormats [i]
endif
ControlRow := ControlRow + 30
Else
@ ControlRow , 120 EDITBOX &CN OF _InputWindow WIDTH 140 HEIGHT 90 VALUE aValues[i] FONT 'Arial' SIZE 10 MAXLENGTH aFormats[i]
ControlRow := ControlRow + 94
EndIf
EndIf
case ValType ( aValues [i] ) == 'M'
@ ControlRow , 120 EDITBOX &CN OF _InputWindow WIDTH 140 HEIGHT 90 VALUE aValues[i] FONT 'Arial' SIZE 10
ControlRow := ControlRow + 94
endcase
Next i
if yn_edit=.T.
@ ControlRow + 10 , 80 BUTTON BUTTON_1 ;
OF _InputWindow ;
CAPTION 'OK' ;
ACTION _InputWindowOk()
@ ControlRow + 10 , 190 BUTTON BUTTON_2 ;
OF _InputWindow ;
CAPTION 'Cancel' ;
ACTION _InputWindowCancel()
else
@ ControlRow + 10 , 135 BUTTON BUTTON_3 ;
OF _InputWindow ;
CAPTION 'OK' ;
ACTION _InputWindowCancel()
endif
_SetFocus ('Control_1','_InputWindow')
END WINDOW
CENTER WINDOW _InputWindow
ACTIVATE WINDOW _InputWindow
set(2,bf)
Return ( aResult )
First of all you should install a few things:
download and install MariaDB
install HMGSQL. see
viewtopic.php?f=8&t=1562
put a copy of libmysql.dll in the same directory as the source program
As is, the program runs on 2 networked computers called MEDION (localhost where the server is installed) and LAPTOP. Each time the program runs on MEDION it recreates the same table.
You will have to adapt a few lines in the connect() procedure for your installation.
Since this is a basic example there are no menus, only 5 buttons:
refresh : when a change is made to a row you must refresh the other station to reflect this change in the grid.
show: the selected row in the grid will be displayed (no locking - readonly)
edit: he selected row in the grid will be displayed and can be edited - the row is locked
add row: add a new row
delete: delete the selected row
As you will see, when a row is selected for editing or deletion, you can show but not edit or delete that same row on the other station.
Below you can read some facts about recordlocking with MariaDB:
MariaDB is the ”real open source” equivalent of MySQL. I use it and will, for the rest of this text use MariaDB when meaning MariaDB- MySQL
InnoDB/XtraDB (standard storage engines used) support row-level locking. ‘ SELECTed rows’ can be locked using SELECT .... FOR UPDATE.
A lock is set on the row read by the query, and it will be released when the current transaction is committed.
A lock is acquired on the row, and other transactions are prevented from writing the row or acquire a lock.
If the record was already locked you get an error message after a preset time. This is by default 50 seconds but can be changed. Others can only read the row.
FOR UPDATE only works within a transaction and only applies when autocommit is set to 0 or the update/delete operation is enclosed in a transaction.
Setting autocommit to 0 means that all operations (also those where no locking is required) have to be enclosed in a transaction to be committed (written to disk).
It is thus easier to let autocomit to 1 (default): all operations are then automatically committed except those in a transaction. When trying a recordlock enclose the statements in a transaction.
For this to work your table must be indexed on the column used for identification of the record.
An easy way is to add column (eg. ROWID) to your table of type INT primary key and use this column.
syntax: SELECT col FROM table WHERE col=value FOR UPDATE
example of a delete:
oQuery:= miscsql(dbo,"start transaction") //or “BEGIN”
oQuery:= sql(dbo, "Select * From country WHERE ROWID = " + c2sql(cROWID)+ " for update") //lock row
if len(oquery) > 0 // lock is ok - if row is locked len = 0
oQuery := miscsql(dbo, "DELETE FROM country WHERE ROWID = " + c2sql(cROWID))
oQuery:= miscsql(dbo,"commit") //unlock row
endif
The result of a SELECT FOR UPDATE without WHERE clause places a lock on the complete table.
An INSERT places an automatic lock on the table, so nothing has to be done.
Setting time to wait before error message is displayed
This can be done in different ways:
In the application, after connecting to the database: miscsql(dbo,"SET GLOBAL innodb_lock_wait_timeout = 1")
In the directory where MariaDB is installed under subdirectory ‘DATA’ you will find a file my.ini.
Add the line innodb_lock_wait_timeout = 1 to put timeout to the minimum.
In my system this looks like this:
[mysqld]
datadir=C:/Program Files/MariaDB 5.3/data
port=3306
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_buffer_pool_size=367M
innodb_log_file_size=50M
innodb_lock_wait_timeout = 1
[client]
port=3306
setting default language for error messages
In the ‘SHARE’ sub directory under the MariaDB directory you will find a lot af language files. When starting the server it loads by default the ENGLISH language file.
After trial and error I found it the easist to rename my language file (dutch) to english.
Do not forget that those changes need a restart of the server.
Richard