SQL Database + Array RDD
Moderator: Rathinagiri
Re: SQL Database + Array RDD
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?
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
- HMG Founder
- Posts: 4004
- Joined: Wed Jul 30, 2008 6:43 pm
Re: SQL Database + Array RDD
Could you be so kind to post the complete code?Kana wrote:I tested Array RDD and find:
1. With next code
<...>
TIA.
Regards/Saludos,
Roberto
(Veritas Filia Temporis)
Roberto
(Veritas Filia Temporis)
Re: SQL Database + Array RDD
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
* 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
- HMG Founder
- Posts: 4004
- Joined: Wed Jul 30, 2008 6:43 pm
Re: SQL Database + Array RDD
Ok.Kana wrote:1.
* RDD SQL DEMO
* Based on Harbour Compiler Contrib Samples
* Adapted for HMG by Roberto Lopez - 2009
<...>
The offending line is in h_grid.prg and is the following:
Code: Select all
If Rlock() == .F.
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.
Regards/Saludos,
Roberto
(Veritas Filia Temporis)
Roberto
(Veritas Filia Temporis)
Re: SQL Database + Array RDD
Roberto Lopez wrote: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
Re: SQL Database + Array RDD
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
To make changes you will have to issue your own sql commands and re-read.
Think this RDD as a sophisticated recordset.
HTH
Angel
Angel Pais
Web Apps consultant/architect/developer.
Web Apps consultant/architect/developer.
- Roberto Lopez
- HMG Founder
- Posts: 4004
- Joined: Wed Jul 30, 2008 6:43 pm
Re: SQL Database + Array RDD
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.Kana wrote:Roberto Lopez wrote: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
To do such thing you should used a SQL command via RDDINFO():
Code: Select all
RDDINFO(RDDI_EXECUTE, "...LOCK TABLE... (and whatever be needed)")
Regards/Saludos,
Roberto
(Veritas Filia Temporis)
Roberto
(Veritas Filia Temporis)
Re: SQL Database + Array RDD
Thanks Roberto I will test it next few days.Roberto Lopez wrote: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)")
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
- HMG Founder
- Posts: 4004
- Joined: Wed Jul 30, 2008 6:43 pm
Re: SQL Database + Array RDD
The sample you've posted is based in ADORDD that is not included as a Harbour contribution anymore, since it does not work.Kana wrote: 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.
<...>
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
Regards/Saludos,
Roberto
(Veritas Filia Temporis)
Roberto
(Veritas Filia Temporis)
Re: SQL Database + Array RDD
I'm sorry for the useless examples, I will correct it.Roberto Lopez wrote: The sample you've posted is based in ADORDD that is not included as a Harbour contribution anymore, since it does not work.
Yes I am planning to update with SQL commands directly.Roberto Lopez wrote: 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.
Is it possible to modify the connection to other SQL database?Roberto Lopez wrote: Look at the sample at \samples\rdd.sql\mysql
Roberto,
Thank for your attention and patience.