RDD.SQL\MySQL - problem

mol · Post by **mol** » Thu Nov 12, 2009 1:54 pm

Hi guys (and one girl

)!
I need to return to MySQL.
I want to test samples\rdd.sql\MySQL but I get error with connection to server.
I think, problem lays in database test - I don't have it.

I've changed database to mine - previously created and it started to work.

Where to find info about RDDI - how to create database?

Best regards, Marek

sudip · Post by **sudip** » Fri Nov 13, 2009 2:30 am

mol wrote: Where to find info about RDDI - how to create database?

You may find info at hmg300\SAMPLES\RDD.SQL\Readme.txt (I installed HMG3.0.0 test in a separate folder).

There is also a more detailed sample, I recently discovered from Minigui Ext at \MiniGUI\SAMPLES\Advanced\MYSQLDD.
I can't resist myself to put the sample here for my friends who didn't install Minigui Ext. Please note that I want to learn only. And thanks a lot for the sample to Grigory Filatov

Code: Select all

/*
 * MINIGUI - Harbour Win32 GUI library Demo
 *
 * Copyright 2002-2008 Roberto Lopez <harbourminigui@gmail.com>
 * http://harbourminigui.googlepages.com/
 *
 * Copyright 2008 Grigory Filatov <gfilatov@freemail.ru>
 *
 * Based on RDDSQL sample included in Harbour distribution
*/

#include "minigui.ch"
#include "dbinfo.ch"
#include "error.ch"

#define DBI_QUERY             1001

#define RDDI_CONNECT          1001
#define RDDI_DISCONNECT       1002
#define RDDI_EXECUTE          1003
#define RDDI_ERROR            1004
#define RDDI_ERRORNO          1005
#define RDDI_NEWID            1006
#define RDDI_AFFECTEDROWS     1007
#define RDDI_QUERY            1008

ANNOUNCE RDDSYS
REQUEST SDDMY, SQLMIX

MEMVAR memvarcountryname
MEMVAR memvarcountryresidents
*--------------------------------------------------------*
Function Main()
*--------------------------------------------------------*

	RDDSETDEFAULT( "SQLMIX" )

	IF RDDINFO( RDDI_CONNECT, {"MYSQL", "localhost", "root",, "test"} ) == 0
		MsgStop("Unable connect to the server!", "Error")
		Return nil
	ENDIF

	DEFINE WINDOW Form_1 ;
		AT 0,0 ;
		WIDTH 640 HEIGHT 480 ;
		TITLE 'MiniGUI SQL Database Driver Demo' ;
		MAIN NOMAXIMIZE ;
		ON INIT OpenTable() ;
		ON RELEASE CloseTable()

		DEFINE MAIN MENU

			DEFINE POPUP 'Test'
				MENUITEM 'Add record'	ACTION AddRecord('ARG', 'Argentina', 38740000)
				SEPARATOR	                                       			                                    
				ITEM "Exit"		ACTION ThisWindow.Release()
			END POPUP

		END MENU

		@ 10,10 BROWSE Browse_1	;
			WIDTH 610	;
			HEIGHT 390	;	
			HEADERS { 'Code' , 'Name' , 'Residents' } ;
			WIDTHS { 50 , 160 , 100 } ;
			WORKAREA country ;
			FIELDS { 'country->Code' , 'country->Name' , 'country->Residents' } ;
			JUSTIFY { BROWSE_JTFY_LEFT, BROWSE_JTFY_LEFT, BROWSE_JTFY_RIGHT } ;
			EDIT ;
			VALID { , { || sqlupdate(2) } , { || sqlupdate(3) } } ;
			READONLY { .T. , .F. , .F. }

	END WINDOW

	CENTER WINDOW Form_1

	ACTIVATE WINDOW Form_1

Return nil

*--------------------------------------------------------*
Procedure OpenTable
*--------------------------------------------------------*

   If CreateTable()

	DBUSEAREA( .T.,, "SELECT * FROM country", "country" )

	INDEX ON FIELD->RESIDENTS TAG residents TO country

	GO TOP

   Else

	Form_1.Release()

   EndIf

Return

*--------------------------------------------------------*
Procedure CloseTable
*--------------------------------------------------------*

   DBCLOSEALL()

Return

*--------------------------------------------------------*
Procedure AddRecord(cCode, cName, nResidents)
*--------------------------------------------------------*

   If RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('" + cCode + "', '" + cName + "', " + ltrim(str(nResidents)) + ")")

      // MsgInfo( RDDINFO(RDDI_AFFECTEDROWS), "Count of Affected Rows" )

      APPEND BLANK

      REPLACE CODE WITH cCode, ;
              NAME WITH cName, ;
              RESIDENTS WITH nResidents

      Form_1.Browse_1.Value := country->(RecNo())
      Form_1.Browse_1.Refresh

   Else

	MsgStop("Can't append record to table Country!", "Error")

   EndIf

Return

*--------------------------------------------------------*
Function sqlupdate(nColumn)
*--------------------------------------------------------*
Local nValue := Form_1.Browse_1.Value
Local cCode, cField, cNewValue

   If nColumn == 2

	cField := "Name"
	cNewValue := "'" + Memvar.Country.Name + "'"

   ElseIf nColumn == 3

	cField := "Residents"
	cNewValue := ltrim(str(Memvar.Country.Residents))

   EndIf

   GO nValue
   cCode := "'" + country->CODE + "'"

   If ! RDDINFO(RDDI_EXECUTE, "UPDATE country SET " + cField + " = " + cNewValue + " WHERE CODE = " + cCode)

	MsgStop("Can't update record in table Country!", "Error")
	Return .F.

   EndIf

Return .T.

*--------------------------------------------------------*
Function CreateTable
*--------------------------------------------------------*
Local ret := .T.

   RDDINFO(RDDI_EXECUTE, "DROP TABLE country")

   If RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME char(50), RESIDENTS int(11))")

      If ! RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('LTU', 'Lithuania', 3369600), ('USA', 'United States of America', 305397000), ('POR', 'Portugal', 10617600), ('POL', 'Poland', 38115967), ('AUS', 'Australia', 21446187), ('FRA', 'France', 64473140), ('RUS', 'Russia', 141900000)")

	MsgStop("Can't fill table Country!", "Error")
	ret := .F.

      EndIf

   Else

	MsgStop("Can't create table Country!", "Error")
	ret := .F.

   EndIf

Return ret

With best regards.

Sudip

mol · Post by **mol** » Fri Nov 13, 2009 8:55 am

I think, this is the same expample as included in HMG.
lines:

Code: Select all

	IF RDDINFO(RDDI_CONNECT, {"MYSQL", "localhost", "root","123", "test"}) == 0
		MSGSTOP("Unable connect to the server")
		RETURN 
	ENDIF

cause program end while database "test" not exists.
I think this example need to be rebuilt.
First, we must test if "test" exists, then try to create it, not exit from program.

Second thing, I'm thinking about, is record locking with MySQL.
I've tried to modify this example and added "FOR UPDATE" phrase for SELECT, but it still is not working as I expect.
Maybe I'm wrong and don't understand mechanism "FOR UPDATE"???
Did anybody tried to use it?
Marek

mol · Post by **mol** » Fri Nov 13, 2009 11:19 am

I've read a little about MySQL, and I found it's required to set autocommit to off when you need to use record locking.
So, I've modified hmg\samples\rdd.sql\mysql\demo.prg to test record locking.
Now, it's work - please try to run two instances of demo.exe and press button "MODIFY 'POL'" - second app. will wait for commit in first app.
My modifications are hot and are not perfect yet. But I couldn;t wait to my discoveries

Code: Select all


* RDD SQL DEMO
* Based on Harbour Compiler Contrib Samples
* Adapted for HMG by Roberto Lopez - 2009

* Modified 2009.11.13 by Marek Olszewski "MOL" for testing Record Locking with MySQL

#include "minigui.ch"
#include "dbinfo.ch"
#include "error.ch"

#define DBI_QUERY             1001

#define RDDI_CONNECT          1001
#define RDDI_DISCONNECT       1002
#define RDDI_EXECUTE          1003
#define RDDI_ERROR            1004
#define RDDI_ERRORNO          1005
#define RDDI_NEWID            1006
#define RDDI_AFFECTEDROWS     1007
#define RDDI_QUERY            1008


REQUEST SDDMY, SQLMIX



Function Main
LOCAL hConn

	RDDSETDEFAULT("SQLMIX")

	IF RDDINFO(RDDI_CONNECT, {"MYSQL", "localhost", "root","123", "TEST"}) == 0
		MSGSTOP("Unable connect to the server")
		RETURN 
	ENDIF

	CreateTable()

	// it's required to use record locking to set autocommit to 0!!!!
	RDDINFO(RDDI_EXECUTE,"SET AUTOCOMMIT = 0")
	DBUSEAREA( .T.,, "SELECT * FROM country", "country" )

	DEFINE WINDOW MAIN ;
		ROW 0 ;
		COL 0 ;
		WIDTH 600 ;
		HEIGHT 500 ;
		TITLE "RDD SQL - TESTING ROW LOCKING MODIFIED BY 'MOL" ;
		WINDOWTYPE MAIN  

		DEFINE BROWSE browse1
			ROW 		40
			COL 		10
			WIDTH		470			
			HEIGHT		330
			HEADERS 	{'Code','Name','Residents'}
			WIDTHS		{135,155,135}
			WORKAREA	COUNTRY
			FIELDS		{'Country->Code','Country->Name','Country->Residents'}
		END BROWSE
		DEFINE BUTTON B1
			ROW 10
			COL 10
			WIDTH 100
			CAPTION "REFRESH"
			ACTION REFRESH_BROWSE()
		END BUTTON

		DEFINE BUTTON B_LOCK
			ROW 10
			COL 120
			WIDTH 100
			CAPTION "LOCK 'POL'"
			ACTION LOCK_RECORD()
		END BUTTON
		
		DEFINE BUTTON RELEASE_LOCK
			ROW 10
			COL 230
			WIDTH 100
			CAPTION "RELEASE LOCK"
			ACTION RELEASE_lock()
		END BUTTON
		DEFINE BUTTON MODIFY_POL
			ROW 10
			COL 340
			WIDTH 100
			CAPTION "MODIFY 'POL'"
			ACTION CHANGE_RECORD()
			
		END BUTTON
			
	END WINDOW

	MAIN.Center

	MAIN.Activate

Return

************************
STATIC PROC CreateTable()
   ? RDDINFO(RDDI_EXECUTE, "DROP TABLE country")
   ? RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME char(50), RESIDENTS int(11))")
   ? RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('LTU', 'Lithuania', 3369600), ('USA', 'United States of America', 305397000), ('POR', 'Portugal', 10617600), ('POL', 'Poland', 38115967), ('AUS', 'Australia', 21446187), ('FRA', 'France', 64473140), ('RUS', 'Russia', 141900000)")
RETURN

************************
function REFRESH_BROWSE
	DBUSEAREA( .F.,, "SELECT * FROM country", "country" )
	MAIN.BROWSE1.REFRESH
	MAIN.BROWSE1.SETFOCUS
return
************************
PROC RDDSYS();  RETURN
************************
FUNCTION LOCK_RECORD
	IF RDDINFO(RDDI_EXECUTE,"SELECT * FROM country WHERE CODE='POL' FOR UPDATE")
		MsgBox("Locked...")
	ELSE
		MsgBox("it's not possible to lock record...")
	ENDIF
return
************************
FUNCTION RELEASE_LOCK
	RDDINFO(RDDI_EXECUTE,"COMMIT")
return
************************ 
FUNCTION CHANGE_RECORD
	local cNewName := ""

	RDDINFO(RDDI_EXECUTE,"COMMIT;")
	WAIT WINDOW "WAITING FOR LOCK RECORD IN DATABASE..."
	IF RDDINFO(RDDI_EXECUTE,"SELECT * FROM country WHERE CODE='POL' FOR UPDATE")
		
		cNewName := AllTrim ( InputBox ( "Enter new name for CODE='POL'" , "Enter new name...") )
		RDDINFO(RDDI_EXECUTE,"update country set name = '" + cNewName +"' where CODE ='POL'")
		RDDINFO(RDDI_EXECUTE,"COMMIT")
	ELSE
		MSGSTOP("I can't lock record for CODE='POL'")
	ENDIF
	WAIT CLEAR
	REFRESH_BROWSE()
return
************************
function xtoC
	param	xValue
	local ret
	
	if type("xValue") == "D"
		ret := dtoc(xValue)
	elseif type("xValue") == "N"
		ret := str(xValue)
	elseif type("xValue") == "L"
		ret := if(xValue,"True","false")
	elseif type("xValue") = "C"
		ret := xValue
	else
		ret := ""
	endif
 return ret

sudip · Post by **sudip** » Fri Nov 13, 2009 12:36 pm

Hi Marek,

Great news

Congratulation

I saved your work in my hard disk

With best regards.

Sudip

mol · Post by **mol** » Fri Nov 13, 2009 12:51 pm

I don't know where to find help to rdd.sql.
.
.
.

mol · Post by **mol** » Fri Nov 13, 2009 1:46 pm

Question to Roberto:
Do SQLMIX use libmysql?
What about licence - should I place my source code when using SQLMIX?

Roberto Lopez · Post by **Roberto Lopez** » Fri Nov 13, 2009 2:54 pm

mol wrote:Question to Roberto:
Do SQLMIX use libmysql?
What about licence - should I place my source code when using SQLMIX?

RDDSQL requires libmysql.dll.

Regarding license, was already discussed in other thread.

viewtopic.php?f=12&t=852&hilit=mysql+license

sudip · Post by **sudip** » Fri Nov 13, 2009 3:13 pm

Hello,

Is it possible to use Sqlite with SQLRDD?

With best regards.

Sudip

mol · Post by **mol** » Fri Nov 13, 2009 4:39 pm

So, I need to try with ODBC...

I don't want to put code of app which need to be moved to mysql as public domain

HMGforum.com

RDD.SQL\MySQL - problem

RDD.SQL\MySQL - problem

Re: RDD.SQL\MySQL - problem

Re: RDD.SQL\MySQL - problem

Re: RDD.SQL\MySQL - problem

Re: RDD.SQL\MySQL - problem

Re: RDD.SQL\MySQL - problem

Re: RDD.SQL\MySQL - problem

Re: RDD.SQL\MySQL - problem

Re: RDD.SQL\MySQL - problem

Re: RDD.SQL\MySQL - problem