SQL Database + Array RDD

[Kana]

I tested Array RDD and find:

1. With next code

DEFINE WINDOW Win_1 ;
ROW 0 ;
COL 0 ;
WIDTH 500 ;
HEIGHT 400 ;
TITLE 'RDD SQL' ;
WINDOWTYPE MAIN

DEFINE GRID grid1
ROW 10
COL 10
WIDTH 470
HEIGHT 330
HEADERS {'Name','Family Name','Birth'}
WIDTHS {135,135,135}
ROWSOURCE "Persons"
COLUMNFIELDS {'Name','FamilyName','Birth'}
COLUMNCONTROLS { Name , FamilyName , Birth }
ALLOWEDIT .T.
END GRID

Program crash when I press ENTER twice. On first press can edit cell but on next press program crash
*************************************
Date:02/07/10 Time: 18:35:46
Error SQLMIX/0 Operation not supported
Called from RLOCK(0)
Called from SAVEDATAGRIDFIELD(2020)
Called from _HMG_GRIDINPLACEEDITOK(1158)
Called from (b)_HMG_GRIDINPLACEEDIT(733)
Called from _DOCONTROLEVENTPROCEDURE(4884)
Called from EVENTS(562)
Called from _DOMESSAGELOOP(0)
Called from _ACTIVATEWINDOW(4548)
Called from _HMG_GRIDINPLACEEDIT(898)
Called from _HMG_GRIDINPLACEKBDEDIT_2(1707)
Called from EVENTS(1747)
Called from _DOMESSAGELOOP(0)
Called from _ACTIVATEWINDOW(4526)
Called from DOMETHOD(7287)
Called from MAIN(49)
------------------------------------


2.

DEFINE WINDOW Win_1 ;
ROW 0 ;
COL 0 ;
WIDTH 500 ;
HEIGHT 400 ;
TITLE 'RDD SQL' ;
WINDOWTYPE MAIN

DEFINE BROWSE browse1
ROW 10
COL 10
WIDTH 470
HEIGHT 330
HEADERS {'Name','Family Name','Birth'}
WIDTHS {135,135,135}
WORKAREA Persons
FIELDS {'Name','FamilyName','Birth'}
ALLOWEDIT .T.
END BROWSE

Now program not crash and I can press ENTER many time and edit few cells
How to lock record for multiuser like BROWSE rlock for DBF table, is it possible?

[Roberto Lopez]

I tested Array RDD and find:

1. With next code
<...>

Could you be so kind to post the complete code?

TIA.

[Kana]

1.


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

#include "minigui.ch"

REQUEST SQLMIX

PROC main()

RDDSETDEFAULT("SQLMIX")
DBCREATE("persons", {{"NAME", "C", 20, 0}, {"FAMILYNAME", "C", 20, 0}, {"BIRTH", "D", 8, 0}, {"AMOUNT", "N", 9, 2}},, .T., "persons")

DBAPPEND(); AEVAL({PADR("Bil", 20), PADR("Gatwick", 20), STOD("19650124"), 123456.78}, {|X,Y| FIELDPUT(Y, X)})
DBAPPEND(); AEVAL({PADR("Tom", 20), PADR("Heathrow", 20), STOD("19870512"), 9086.54}, {|X,Y| FIELDPUT(Y, X)})
DBAPPEND(); AEVAL({PADR("John", 20), PADR("Weber", 20), STOD("19750306"), 2975.45}, {|X,Y| FIELDPUT(Y, X )})
DBAPPEND(); AEVAL({PADR("Sim", 20), PADR("Simsom", 20), STOD("19930705"), 32975.37}, {|X,Y| FIELDPUT(Y, X )})

INDEX ON FIELD->AMOUNT TO amount
DBGOTOP()


DEFINE WINDOW Win_1 ;
ROW 0 ;
COL 0 ;
WIDTH 500 ;
HEIGHT 400 ;
TITLE 'RDD SQL' ;
WINDOWTYPE MAIN

DEFINE GRID grid1
ROW 10
COL 10
WIDTH 470
HEIGHT 330
HEADERS {'Name','Family Name','Birth'}
WIDTHS {135,135,135}
ROWSOURCE "Persons"
COLUMNFIELDS {'Name','FamilyName','Birth'}
COLUMNCONTROLS { Name , FamilyName , Birth }
ALLOWEDIT .T.
END GRID

END WINDOW

Win_1.Center

Win_1.Activate

RETURN

PROC RDDSYS(); RETURN


2.


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

#include "minigui.ch"

REQUEST SQLMIX

PROC main()

RDDSETDEFAULT("SQLMIX")
DBCREATE("persons", {{"NAME", "C", 20, 0}, {"FAMILYNAME", "C", 20, 0}, {"BIRTH", "D", 8, 0}, {"AMOUNT", "N", 9, 2}},, .T., "persons")

DBAPPEND(); AEVAL({PADR("Bil", 20), PADR("Gatwick", 20), STOD("19650124"), 123456.78}, {|X,Y| FIELDPUT(Y, X)})
DBAPPEND(); AEVAL({PADR("Tom", 20), PADR("Heathrow", 20), STOD("19870512"), 9086.54}, {|X,Y| FIELDPUT(Y, X)})
DBAPPEND(); AEVAL({PADR("John", 20), PADR("Weber", 20), STOD("19750306"), 2975.45}, {|X,Y| FIELDPUT(Y, X )})
DBAPPEND(); AEVAL({PADR("Sim", 20), PADR("Simsom", 20), STOD("19930705"), 32975.37}, {|X,Y| FIELDPUT(Y, X )})

INDEX ON FIELD->AMOUNT TO amount
DBGOTOP()


DEFINE WINDOW Win_1 ;
ROW 0 ;
COL 0 ;
WIDTH 500 ;
HEIGHT 400 ;
TITLE 'RDD SQL' ;
WINDOWTYPE MAIN

DEFINE BROWSE browse1
ROW 10
COL 10
WIDTH 470
HEIGHT 330
HEADERS {'Name','Family Name','Birth'}
WIDTHS {135,135,135}
WORKAREA Persons
FIELDS {'Name','FamilyName','Birth'}
ALLOWEDIT .T.
END BROWSE


END WINDOW

Win_1.Center

Win_1.Activate

RETURN

PROC RDDSYS(); RETURN

[Roberto Lopez]

1.


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

Ok.

The offending line is in h_grid.prg and is the following:

Code: Select all

	If Rlock() == .F.

So, apparently, the problem is that array-rdd does not support locking and generates a runtime error.

The fix consist in check the current rdd and if it is array-rdd do not attempt locking.

Browse works because it attempts locking only if it is explicitly stated by the 'lock' property.

I'll work on it.

Thanks for the report.

[Kana]

So, apparently, the problem is that array-rdd does not support locking and generates a runtime error.

If the array RDD supported lock as browse-> dbf
It would be great to control the input data in a SQL database access, MSSQL ...

Unfortunately

[apais]

I may be wrong but as far as I know SQLMIX RDD is intended for read only purpouses.
To make changes you will have to issue your own sql commands and re-read.
Think this RDD as a sophisticated recordset.

HTH
Angel

[Roberto Lopez]

So, apparently, the problem is that array-rdd does not support locking and generates a runtime error.

If the array RDD supported lock as browse-> dbf
It would be great to control the input data in a SQL database access, MSSQL ...

Unfortunately

RDDSQL does not allow modify tables using xBase commands, so, even if were possible to lock a record, you only could be able to lock a record in the LOCAL RECORDSET not the server table.

To do such thing you should used a SQL command via RDDINFO():

Code: Select all

RDDINFO(RDDI_EXECUTE, "...LOCK TABLE... (and whatever be needed)")

[Kana]

RDDSQL does not allow modify tables using xBase commands, so, even if were possible to lock a record, you only could be able to lock a record in the LOCAL RECORDSET not the server table.
To do such thing you should used a SQL command via RDDINFO():

Code: Select all

RDDINFO(RDDI_EXECUTE, "...LOCK TABLE... (and whatever be needed)")

Thanks Roberto I will test it next few days.

My idea is that a table from SQL database that I want to update read with SELECT,
and to create and fill DBF table. The BROWSE have control of updating my temporary DBF tables,
and when you finish updating my DBF tables that updated records back in the SQL database.

In the example that approach I tried to run the DEMO program in the network and I managed
and updated DBF table. LOCK record in this case is possible.
I have not written code to update the database MDF but I left a message MSG.

Correct me if I sins. Is it possible to describe the way Multiuser update MDF table.

When I saw that was published ARRAYRDD my idea was that instead of DBF file
ARRAYRDD use, however ARRAYRDD does not support LOCK.

I hope that I managed to explain what interested me and that is all that feasible.

Note:
In the example I used ADORDD only as an example to create an MDF base and could for example
be tested without the database. So ADORDD is not important for this test
I just accidentally took the example where the ADORDD creates MDF base.



Example:
/*
* MINIGUI - Harbour Win32 GUI library Demo
*
* Copyright 2002-2007 Roberto Lopez <harbourminigui@gmail.com>
* http://harbourminigui.googlepages.com/
*
* Based on ADORDD sample included in Harbour distribution
*/

#include "adordd.ch"
#include "minigui.ch"

Function Main()
SET DELETED ON

DEFINE WINDOW Form_1 ;
AT 0,0 ;
WIDTH 640 HEIGHT 480 ;
TITLE 'MiniGUI AdoRDD Demo' ;
MAIN NOMAXIMIZE ;
ON INIT OpenMdb() ;
ON RELEASE CloseMdb()

DEFINE BROWSE Browse_1
COL 10
WIDTH 10
WIDTH 610
HEIGHT 390
HEADERS { 'First' , 'Last' , 'Age' }
WIDTHS { 150 , 150 , 150 }
WORKAREA TempDbf
FIELDS { 'TempDbf->First' , 'TempDbf->Last' , 'TempDbf->Age' }
LOCK .T.
ALLOWEDIT .T.
INPLACEEDIT .T.
ALLOWAPPEND .T.
ALLOWDELETE .T.
ALLOWAPPEND .T.
END BROWSE

END WINDOW
CENTER WINDOW Form_1
Form_1.Browse_1.SetFocus
ACTIVATE WINDOW Form_1
Return nil

Procedure OpenMdb
IF !IsWinNT() .AND. !CheckODBC()
MsgStop( 'This Program Runs In Win2000/XP Only!', 'Stop' )
ReleaseAllWindows()
ENDIF
IF !FILE('test.mdb')
CreateMdb()
ENDIF
USE test.mdb VIA "ADORDD" TABLE "table1"
IF EMPTY( test->( LastRec() ) )
APPEND BLANK
test->First := "Homer"
test->Last := "Simpson"
test->Age := 45
APPEND BLANK
test->First := "Lara"
test->Last := "Kroft"
test->Age := 32
APPEND BLANK
test->First := "James"
test->Last := "Bond"
test->Age := 007
ENDIF
GO TOP
IF !FILE('tempdbf.dbf')
CreateDbf()
// Select * From Table1 & APPEND To Test.Dbf
Use Tempdbf SHARED
APPEND BLANK
tempdbf->First := "Homer"
tempdbf->Last := "Simpson"
tempdbf->Age := 45
APPEND BLANK
tempdbf->First := "Lara"
tempdbf->Last := "Kroft"
tempdbf->Age := 32
APPEND BLANK
tempdbf->First := "James"
tempdbf->Last := "Bond"
tempdbf->Age := 007
GO TOP
//
ELSE
Use Tempdbf SHARED
ENDIF
Return

Procedure CloseMdb
USE
UpdateMdb()
ERASE 'tempdbf.dbf'
Return

Procedure UpdateMdb
MsgBox("Update Table1 From TempDbf!!!")
Return

Procedure CreateMdb
DbCreate( "test.mdb;table1", { { "FIRST", "C", 10, 0 },;
{ "LAST", "C", 10, 0 },;
{ "AGE", "N", 8, 0 } }, "ADORDD" )

DbCreate( "test.mdb;table2", { { "CITY", "C", 10, 0 },;
{ "CODE", "N", 8, 0 } }, "ADORDD" )
Return


Procedure CreateDbf
DBCREATE("Tempdbf", { { "FIRST", "C", 10, 0 },;
{ "LAST", "C", 10, 0 },;
{ "AGE", "N", 8, 0 } } )
Return


Static Function CheckODBC()
LOCAL oReg, cKey := ""
OPEN REGISTRY oReg KEY HKEY_LOCAL_MACHINE SECTION "Software\Microsoft\DataAccess"
GET VALUE cKey NAME "Version" OF oReg
CLOSE REGISTRY oReg
Return !EMPTY(cKey)

[Roberto Lopez]

My idea is that a table from SQL database that I want to update read with SELECT,
and to create and fill DBF table. The BROWSE have control of updating my temporary DBF tables,
and when you finish updating my DBF tables that updated records back in the SQL database.
<...>

The sample you've posted is based in ADORDD that is not included as a Harbour contribution anymore, since it does not work.

As I've said, SQLRDD does not update the backend table, you'll have a local recordset only.

To update the table you must send the SQL commands directly.

Look at the sample at \samples\rdd.sql\mysql

[Kana]

The sample you've posted is based in ADORDD that is not included as a Harbour contribution anymore, since it does not work.

I'm sorry for the useless examples, I will correct it.

As I've said, SQLRDD does not update the backend table, you'll have a local recordset only.
To update the table you must send the SQL commands directly.

Yes I am planning to update with SQL commands directly.

Look at the sample at \samples\rdd.sql\mysql

Is it possible to modify the connection to other SQL database?

Roberto,
Thank for your attention and patience.