MySql revisited

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

Post Reply
t57042
Posts: 148
Joined: Tue Mar 16, 2010 9:46 am
Location: belgium

MySql revisited

Post by t57042 »

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
User avatar
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

Post by Rathinagiri »

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.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
t57042
Posts: 148
Joined: Tue Mar 16, 2010 9:46 am
Location: belgium

Re: MySql revisited

Post by t57042 »

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
User avatar
dragancesu
Posts: 920
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia

Re: MySql revisited

Post by dragancesu »

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
t57042
Posts: 148
Joined: Tue Mar 16, 2010 9:46 am
Location: belgium

Re: MySql revisited

Post by t57042 »

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
User avatar
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

Post by Rathinagiri »

Wonderful!

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.
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: MySql revisited

Post by mol »

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.
t57042
Posts: 148
Joined: Tue Mar 16, 2010 9:46 am
Location: belgium

Re: MySql revisited

Post by t57042 »

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
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: MySql revisited

Post by mol »

I can't test it now. I'm writing from phone. I'll try tomorrow
User avatar
serge_girard
Posts: 3158
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:

Re: MySql revisited

Post by serge_girard »

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
There's nothing you can do that can't be done...
Post Reply