MySql revisited
Moderator: Rathinagiri
MySql revisited
Hello,
I am doing a bit of research on MySQL.
There are different ways to use mysql with HMG.
I know and I am trying 2: RDDSQL and TMySQL.
There are probably many more that I don't know.
What is according the 'experts' the best (simplest) way to implement?
tks
Richard
I am doing a bit of research on MySQL.
There are different ways to use mysql with HMG.
I know and I am trying 2: RDDSQL and TMySQL.
There are probably many more that I don't know.
What is according the 'experts' the best (simplest) way to implement?
tks
Richard
- Rathinagiri
- Posts: 5471
- Joined: Tue Jul 29, 2008 6:30 pm
- DBs Used: MariaDB, SQLite, SQLCipher and MySQL
- Location: Sivakasi, India
- Contact:
Re: MySql revisited
I use TMySQL with the help of HMGMySQL Bridge. http://www.hmgforum.com/viewtopic.php?f=8&t=1562
It has 4 functions,
1. Connect2SQL() - To connect SQL. It returns the connection handle which is used in the other two SQL functions.
2. SQL() - To send SELECT query to retrieve data in array format
3. MiscSQL() - To execute all other queries except SELECT and it returns a logical value whether the query is executed successfully or not.
4. c2SQL() - Clipper to SQL parser of all data types.
It has 4 functions,
1. Connect2SQL() - To connect SQL. It returns the connection handle which is used in the other two SQL functions.
2. SQL() - To send SELECT query to retrieve data in array format
3. MiscSQL() - To execute all other queries except SELECT and it returns a logical value whether the query is executed successfully or not.
4. c2SQL() - Clipper to SQL parser of all data types.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
South or North HMG is worth.
...the possibilities are endless.
Re: MySql revisited
Hi,
When I run the bat file no .lib files are created.
Can you pls email me the 3 lib files?
r.peeters@pandora.be
Richard
When I run the bat file no .lib files are created.
Can you pls email me the 3 lib files?
r.peeters@pandora.be
Richard
- dragancesu
- Posts: 921
- Joined: Mon Jun 24, 2013 11:53 am
- DBs Used: DBF, MySQL, Oracle
- Location: Subotica, Serbia
Re: MySql revisited
Have nice examples on \HMGxxx \ Samples \ hfcl \ mysql
Solution with TMySQL is good, you need just libmysql.dl from mysql server installation, and that's all
Rathi with HMGMySQL Bridge made just library to establish a connection, accept (SELECT) and later return (UPDATE)
the second solution is that these functions are placed in the program and added to the project (so do I)
It will be easier if you write down what you've been able to do
Solution with TMySQL is good, you need just libmysql.dl from mysql server installation, and that's all
Rathi with HMGMySQL Bridge made just library to establish a connection, accept (SELECT) and later return (UPDATE)
the second solution is that these functions are placed in the program and added to the project (so do I)
It will be easier if you write down what you've been able to do
Re: MySql revisited
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.
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
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 )
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
- Rathinagiri
- Posts: 5471
- Joined: Tue Jul 29, 2008 6:30 pm
- DBs Used: MariaDB, SQLite, SQLCipher and MySQL
- Location: Sivakasi, India
- Contact:
Re: MySql revisited
Wonderful!
Record locking is new to me. Thank you.
Record locking is new to me. Thank you.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
South or North HMG is worth.
...the possibilities are endless.
Re: MySql revisited
Some years ago I was trying to realize record locking with mysql. It was strange, but on one of my computer record locking worked OK, on another - with the same version of mysql it didn't worked. Did you test it on few machines?
Last edited by mol on Sun Feb 26, 2017 8:13 pm, edited 1 time in total.
Re: MySql revisited
Yes I did. Are you sure both versions used the same storage engine?
I had a table where it did not work. After some searching it seemed that the table was created with MyIsam engine. This engine does not
support transactions! When I altered the table it worked fine.
Does the example work?
Richard
I had a table where it did not work. After some searching it seemed that the table was created with MyIsam engine. This engine does not
support transactions! When I altered the table it worked fine.
Does the example work?
Richard
Re: MySql revisited
I can't test it now. I'm writing from phone. I'll try tomorrow
- serge_girard
- Posts: 3168
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: MySql revisited
Start Transaction is also in MySQL but only for InnoDB defined tables.
Also intersting are TRIGGERS. A kind of event on a table eg: when there is a delete on a table (wherever) then do something (make a record copy in antoher tabel or so).
Serge
Also intersting are TRIGGERS. A kind of event on a table eg: when there is a delete on a table (wherever) then do something (make a record copy in antoher tabel or so).
Serge
There's nothing you can do that can't be done...