ORM power in next FWH

[Antonio Linares]

Thanks to Harbour Magazine meeting in Calpe, Carlos Mora explained us the power of ORM



In a few words, ORM it is a set of classes to greatly simplify the use of SQL relational DataBases.

An example is worth a thousand words:

Code: Select all  Expand view  RUN

function Main()

   local oUsers := Users():New( "www.fivetechsoft.com:3306",;
                                "fivetech_users",;
                                "fivetech_antonio",;
                                "****" )
   
   oUsers:First:Edit()
   
   oUsers:Browse()

return nil

See how simple ORM turns our code:

Code: Select all  Expand view  RUN

oUsers:Find( 120 ):Edit()

This is really nice:

Code: Select all  Expand view  RUN

oUsers:Where( "country", "Spain" ):Where( "city", "Barcelona" ):Browse()

and this:

Code: Select all  Expand view  RUN

oUsers:Where( "country", "Sp%", "Br%", "Ind%" ):OrderBy( "city" ):Browse()

as simple as this:

Code: Select all  Expand view  RUN

oUsers:Select( "username", "city" ):Browse()

We are currently working on this ORM implementation for FWH and you will be using it real soon

Stay tuned for more examples comming these days!!!

[dutch]

+1

It will be great, if it supports multi sql database (MySQL/MsSql/Oracle).

[Antonio Linares]

Duth,

Of course it does

[PabloHarbour]

Que Bueno que se está poniendo FWH Antonio !!! Lo felicito !!!

[Antonio Linares]

This is working already

Code: Select all  Expand view  RUN

oUsers:Find( 3 ):Invoices:Browse()

[Otto]

Dear Antonio,
can you please tell me the differences to what we have had so far.
Best regards
Otto

Code: Select all  Expand view  RUN

 
oCn   := maria_Connect( cServer, cDB, cUser, cPwd )
   XBROWSER oCn:ListTables() SETUP ( ;
      oBrw:aCols[ 1 ]:bLDClickData := { |r,c,f,o| XBrowse( oCn:RowSet( o:Value ) ) } )
 

[Antonio Linares]

Dear Otto,

The strongest difference comes when we manage tables with relations:

oUsers:Invoices:Browse()

oUsers:Invoices mimics "SET RELATION TO ..."

You select a user and invoices automatically deliver his invoices

There are more examples to come. You will notice how ORM is much simpler

oUsers:Invoices:First:Items:First:Name ...

[Marcelo Via Giglio]

excelente

[Antonio Linares]

Notice this interesting detail:

Code: Select all  Expand view  RUN

METHOD Invoices() CLASS Users

   if ::nLastId != ::oRs:Id
      ::nLastId = ::oRs:Id
      ::_Invoices:Where( "user_id", ::oRs:Id )
   endif

return ::_Invoices

Only if a different user is selected then the Invoices are reloaded.

[nageswaragunupudi]

Example of setting relationships between tables on different servers connected by different libraries:

Code: Select all  Expand view  RUN

#include "fivewin.ch"

static oMySql, oMsSql   // Servers

//----------------------------------------------------------------------------//

function Main()

   local oStates, oCustomers

   oMySql   := ORM_Connection():New( "DOLPHIN",   "DEMO" )
   oMsSql   := ORM_Connection():New( "MSSQL",     "DEMO" )

   oStates     := oMySql:Table( "states" ):OrderBy( "code" )
   oCustomers  := oMsSql:Table( "customer" ):OrderBy( "first" )

   oCustomers:Relate( "state", oStates, "code" )

   BrowseStates( oStates, oCustomers )

   oStates:Close()
   oCustomers:Close()

   oMySql:Close()
   oMsSql:Close()

return nil

//----------------------------------------------------------------------------//

function BrowseStates( oStates, oCustomers )

   local oDlg, oFont, oBold, oBrwStates, oBrwCust

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE FONT oBold NAME "TAHOMA" SIZE 0,-14 BOLD

   DEFINE DIALOG oDlg SIZE 1000,600 PIXEL TRUEPIXEL FONT oFont ;
      TITLE "FWH ORM POWER"

   @ 60, 20 XBROWSE oBrwStates SIZE 300,-20 PIXEL OF oDlg ;
      DATASOURCE oStates:oRs COLUMNS "Code", "Name" ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwStates
      :bChange := { || oCustomers:First(), oBrwCust:GoTop(), oBrwCust:Refresh() }
      :CreateFromCode()
   END

   @ 60,320 XBROWSE oBrwCust SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oCustomers:oRs ;
      COLUMNS "State", "City", "First", "Salary" ;
      CELL LINES NOBORDER

   oBrwCust:CreateFromCode()

   @ 10, 20 SAY "States table on MYSQL Server" + CRLF + ;
                "Connected with DOLPHIN" ;
                SIZE 300,48 PIXEL OF oDlg FONT oBold CENTER ;
                COLOR CLR_HRED, oDlg:nClrPane

   @ 10,320 SAY "Customer Table on Microsoft SQL Server" + CRLF + ;
                "Connected with ADO"  ;
                SIZE 660,48 PIXEL OF oDlg FONT oBold CENTER ;
                COLOR CLR_HRED, oDlg:nClrPane

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont, oBold

return nil

//----------------------------------------------------------------------------//

EXTERNAL TDOLPHINSRV
 

[leandro]

Good afternoon everyone,

I would like to know how the ORM development is going and if there is already documentation or information to consult.

[Antonio Linares]

Leandro,

Please review FWH\samples\ormtest1.prg, ormtest2.prg and ormtest3.prg examples

full source code is here:
FWH\source\classes\fworm.prg

[James Bott]

oUsers:Invoices mimics "SET RELATION TO ..."

You select a user and invoices automatically deliver his invoices

I presume "users" is really describing customers?

You can use objects to do this too:

oCustomer:Invoices()

[Antonio Linares]

James,

yes

Yes, the ORM idea is to manage databases as very simple and intuitive objects

[leandro]

Buenos días para todos

Estoy intentando crear una conexión ORM, según el ejemplo que menciona antonio, pero no logro hacerlo andar.

Code: Select all  Expand view  RUN

vCnd := "DSN=dlyma;Uid="+oLamcla:cUsuario+";Pwd="+oLamcla:cPassword+";"
oCnFtr := FW_OpenAdoConnection( vCnd )
oCnOrm   := ORM_Connection():New( oCnFtr )
 

En el ejemplo ormtest1.prg estan comentadas estas líneas, asumo que debe funcionar con el objeto ya creado de ADO

Code: Select all  Expand view  RUN

   //
   // If we have already connected directly to ADO, DOLPHIN or FWMARIADB
   // We can also use that connection, without providing credentials again
   // ORM_Connection():New( oExistingConnectionObject )
   //
 

El errror que me arroja es el siguiente:

Code: Select all  Expand view  RUN

Application
===========
   Path and name: C:\DLYMA\dlyma.exe (32 bits)
   Size: 6,153,728 bytes
   Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20190613)
   FiveWin  version: FWH 19.06
   C compiler version: Borland/Embarcadero C++ 7.3 (32-bit)
   Windows version: 6.2, Build 9200

   Time from start: 0 hours 0 mins 8 secs
   Error occurred at: 30/07/2019, 12:14:56
   Error description: Error BASE/1005  Message not found: ORM_CONNECTION:LOWNCONNCTION

Stack Calls
===========
   Called from: .\source\function\HARBOUR.PRG => _CLSSETERROR( 247 )
   Called from: .\source\classes\FWORM.PRG => ORM_CONNECTION:_LOWNCONNCTION( 209 )
   Called from: .\source\classes\FWORM.PRG => ORM_CONNECTION:NEW( 68 )
   Called from: c:\dlyma\prg\R32_fact.prg => R32_FACT( 32 )
   Called from: c:\dlyma\prg\Alyma.prg => (b)TLYMA:AUTORIZA( 1139 )
   Called from: c:\dlyma\prg\Alyma.prg => TLYMA:AUTORIZA( 1139 )
   Called from: c:\dlyma\prg\R32_menu.prg => (b)MAIN( 440 )
   Called from: .\source\classes\TRBTN.PRG => TRBTN:CLICK( 717 )
   Called from: .\source\classes\TRBTN.PRG => TRBTN:LBUTTONUP( 917 )
   Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT( 1791 )
   Called from: .\source\classes\TRBTN.PRG => TRBTN:HANDLEEVENT( 1575 )
   Called from: .\source\classes\WINDOW.PRG => _FWH( 3546 )
   Called from:  => WINRUN( 0 )
   Called from: .\source\classes\WINDOW.PRG => TMDIFRAME:ACTIVATE( 1078 )
   Called from: c:\dlyma\prg\R32_menu.prg => MAIN( 529 )
 

De antemano gracias