FiveTech Software tech support forums • FWH: MySql/MariaDB: RowSet object

Page 1 of 4

FWH: MySql/MariaDB: RowSet object

Posted: Thu Jul 21, 2016 11:50 am

by nageswaragunupudi

Rowset object is similar to RecordSet of ADO, TDatabase for DBF and oQuery of TMySql and TDolphin.

RowSet works more like DBF and RecordSet and differences from oQuery of TMySql and Dolphin are discussed here.

This documentation includes improvements made after release of 16.05.

Creating RowSet:
Options

1) oRs := oCn:RowSet( cTable(or)cSql, [lShowError] ) // simple and normal
2) oRs := oCn:RowSet( cTable(or)cSql, NumRows, [lShowError] )
3) oRs := oCn:RowSet( cSql, aParams, [lShowEror] ) //where oCn is the connection object.
Alias name oCn:Query(...) also is availble.
4) oRs := oCn:Call( cStoredProc, params, ... )
5) oRs:Clone() --> Clone of a RowSet

In case of failure, the return value is nil. After calling this method it is necessary to the check the success before using the rowset object. Example:

Code: Select all

oRs := oCn:RowSet( params )
if oRs == nil
   oCn:ShowError()
   return nil
endif
// proceed with using the rowset

Once the data is read, the rowset is treated a virtual table, which can be accessed sorted, filtered independent of the server, even if the connection is lost. For editing and recording changes, connection to the server is required.

(Future plans: To spool the edits and insertions when connection is lost and to upload the spooled edits after connection is restored.)

Option (1):
oRs := oCn:RowSet( cTable(or)cSql, [lShowError] )
This is similar to other libs. The parameter can also be a table name without prefixing "select * from". If the last optional parameter is .T., error message is displayed in case of failure.

Option (2):
oRs := oCn:RowSet( cSql/table, NumRows, [lShowError] )
This method works only for either (a) simple table name or (b) if the sql statements ends with "ORDER BY <x>". This method internally uses LIMIT/OFFSET clauses to retrieve the first NumRows records.

Later we can use
oRs:ReadNext( n ) --> lMoreRecsRemaining
to read next n records or
oRs:ReadNext() to read all remaining records.
DATA oRs:lMore indicates if there are some more records to be read. When this data is false it indicates that all records are read.

Note: It is expected that the users are well acquinted with the issues, side-effects and possible inaccuracies while using LIMIT clause. Desirable to be thorough with MySql documentation. By far using Limit/Offset is safer when ordered by primary key.

Option (3):
oCn:RowSet( cSql, aParams, [lShowError] )
cSql should be a parameterised statement
aParams is array of values to be used in the place of parameters.

Example:

Code: Select all

cState := "NY"
nMinAge := 41
nMaxAge := 50
oRs := oCn:RowSet( "select * from `customer` where `state` = ? and 'age' between ? and ?", { cState, nMinAge, nMaxAge } )

These values are substituted for the 3 place holders before executing the sql.

Later we can use :
oRs:Requery( aNewParams )
In the above example we can use

Code: Select all

cState  := "WA"
nMinAge := 51
nMaxAge := 60
oRs:Requery( { cState, nMinAge, nMaxAge } )

The data in the same rowset is replaced with the results of the new query.

Parameters:
Place-holders for the parameters can be specified in any manner like:
? ? ? : like in sql
&1, &2, ... : compatible with other libs

Or named paramters:
:param1, :param2, ... : standard sql
&param1, &param2, .. : compatible with other libs

In the case of named paratmers the array needs to be a multi-dim { { value1, value2, ..} ... }

Option - 4:
oRs := oCn:Call( cStoredProc, params, ... )

A RowSet can also be created by calling a Stored Procedure returning a result set. In case the stored procedure is called with parameters, subsequenlty oRs:Requery( params ) can be called with same parameters with different values.
Though in such cases, the real SQL statement executed by the stored procedure is not visible, the rowset object makesw its best efforts to identify the base table and prepares appropriately to Requery, Refresh and Resync.

Option-5:
oRs2 := oRs:Clone()

This works similar to the ADO method Clone().
All the Clones work on the same data but can be sorted / filtered independantly.
All changes in one RowSet are automatically seen in all the Clones.
The internal linkage of the Clones does not work / broken in the following cases.
a) Deletions and Inserts are not seen by other clones. (differs from ADO)
b) ReQuery() of a Clone totally de-links it from other clones. (like ADO)

Re: FWH: MySql/MariaDB: RowSet object

Posted: Thu Jul 21, 2016 11:56 am

by nageswaragunupudi

Navigation methods and datas:

Navigation behaves similar to DBF and RecordSet than oQuery of other libs.

LastRec() --> Total number of records at the time of reading the rowset
This number remains constant even after deletion of some records. Behavior similar to DBF. Adding records increases the number.

RecCount()/RecordCount(): Actual number of records. This is equal to LastRe() less number of deleted records. In the present version deleted records are totally removed. (Future plans: To retain the deleted records with facility to restore like in DBF)

RecNo() (or) BookMark: Serial number of the Record at the time of reading the rowset.

This number uniquely identifies a record and does not change through out the life of the rowset, even if the rowset is sorted and/or filtered. The highest RecNo is the same as LastRec() but may be greater than RecordCount() / RecCount() if some records are deleted.

GoTo( n ) --> new RecNo
(or) BookMark := n
If successful, this method returns the new record number, otherwise remains on the current record.

nSaveRec := oRs:RecNo() and later calling oRs:GoTo( nSaveRec ) takes back to the same record even after sorting and / or filtering, if the record is not deleted.

GoTop(), GoBottom(), Skip( n )
MoveFirst(), MoveLast(), MoveNext(), MovePrevious(), Move( n )
Work similar to DBF and RecordSet, navigating in the sorted order, respecting filters.

OrdKeyCount(), OrdKeyNo(), OrdKeyGoTo( nKeyNo )
KeyCount(), KeyNo(), KeyGoTo( nKeyNo )
RecordCount(), AbsoutePosition, AbsolutePosition := n
All these methods work exactly the same way as DBF / RecordSet

After sorting it is only KeyNo() that appears serially but not RecNo(). In other libs RecNo() works like KeyNo()

Re: FWH: MySql/MariaDB: RowSet object

Posted: Thu Jul 21, 2016 12:39 pm

by nageswaragunupudi

Sorting and Searching

Sorting is done totally in the memory of the client PC, whether or not the connection is still alive. Toggling ascend and descend orders does not involve any physical sorting but only reverses the direction of navigation logic. This makes it instantaneous.

Other libs read the complete data again from the server altering the ORDER BY clause. This may have performance implications on slow and remote connections.

oRs:aSortSecs --> { nSecs, nOrder } // time taken for last sort.

METHODS:

OrdSetFocus( cFieldName/nFieldNo ) --> cOrder
OrdSetFocus( 0 ) -- "" // Restores natural order (RecNo() order)
SetOrder( cFieldName/nFieldNo, uIgnored, lDesc ) --> cOrder
OrdDescend( cFieldName/nFieldNo, uIgnored, lDesc ) --> lDesc
oRs:Sort := <cFieldName> <ASC/DESC>

All these methods work similar to DBF and RecordSet.

If the value of a sorted field is changed, the record is repositioned in the sort order immediately, without the need for resorting.

Limitations: As of now, sorting is possible only on a single field.

oRs:Seek( uValue, [lSoft], [lWild] )
Works similar to DBF, except that if the value is not found the record pointer stays on curernt record and eof is not set

oRs:OrdWildSeek( cWild, lFromCurRec, lReverse ) // Works similar to DBF

oRs:Locate( cExprn ) --> lSuccess // Use DBF syntax for cExprn.
if not found the record pointer is not changed.
oRs:Locate() without parameters finds next match if earlier call was successful. This is similar to executing CONTINUE in DBF after LOCATE succeeded.

Example:

Code: Select all

oRs:Locate( "state = 'NY' .and. Age > 50" ) --> lFound
oRs:Locate() // locate next match

Re: FWH: MySql/MariaDB: RowSet object

Posted: Thu Jul 21, 2016 1:03 pm

by nageswaragunupudi

Filtering RowSet

METHODS:

oRs:SetFilter() --> present filter expression
oRs:SetFilter( cFilter ) --> cFilter

oRs:SetFilter( cFilter, aParams ) --> cFilter
oRs:ReFilter( aParams )
oRs:Filter := cFilter // ADO compatible

(a) Filtering of the rowset is done on the client independent of the server, where as other libs read the entire query result again from the server with a revised where clause. This results in a performance difference.

(b) Variables can not be used in the filter expression. Either convert the variables as constants or use Filter with parameters.

(b) Filter expression should be in DBF syntax and evaluated with DBF rules but not in Sql syntax. However, RowSet object tries its best to translate the Sql syntax to Dbf syntax in simple cases.

Example:
"age between 30 and 40" is translated as "age >= 30 .and. age <= 40"
"name like 'R_m%'" is translated as "HB_WILDMATCHI( "R?m*", name)" or "WILDMATCH( "R?M*", Upper( name ) )"

We recommend using Parameterised filters: First advantage is that we need to go through the difficult process of concatenating the prepare the expression.
Normally we need to do something like this:
cFilter := "fname = '" + cVar + "'", etc.
Parameterised filters save this work. Example:

Code: Select all

cDept := "123"
dDate := {^ 2008/02/20 }
oRs:SetFilter( "dept = ? and hiredate > ?", { cDept, dDate } )

This is easier to write. RowSet object internally takes all the tedium of converting the variables into suitable constants.

Another advantage is that later we can change the variables and again call
oRs:ReFilter( { "456", dNewDate } )

Record positioning after filter is set: If the current record satisfies the filter condition, the record pointer stays on the current record even after the filter is set. Otherwise record pointer moves to the first filtered set.

Re: FWH: MySql/MariaDB: RowSet object

Posted: Thu Jul 21, 2016 1:41 pm

by nageswaragunupudi

ReQuery( [params] ), Refresh() and ReSync()

ReQuery()
Reads the entire rowset again from the Server using the original SQL and replaces the previous data with new data. This method works exactly like ADO's Requery.

ReQuery( params )
In case the RowSet was originally created using parameterised query as explained above, we can use different parameters and read different results. New results replace the present data.

Refresh()
This method reads from the server only the modifications and insertions made after first read or previous Refresh() and updates the RowSet. Even on very large data this is very fast and definitely far better than ReQuery() to update the RowSet to sync with other net work users' changes.

Conditions for usage: (a) The rowset should have a primary key or unique key
and (b) also a timestamp field that is updated with every modificaton.

This field can be created at the time of creation of table by using "=" as field type. Example:

Code: Select all

oCn:CreateTable( "tablename", { ;
   { "createdat", "@", 8, 0 }, ;
   ( "changedat", "=", 8, 0 } }, .T. )

Limitations: Refresh() can not get data about records deleted by other users. We need different approach for that.

ReSync()
Reads from the server data of the current record only and refreshes data of current record. Works if the rowset contains primary/unique key or all other values together can uniquely identify a record on the server.

In case of simple to complex sql statements with multiple tables also, as long as the rowset object is able to detect the main table, Refresh() and Resync() work.

Resync() is the most used function internally. After every save, the saved record is Resynced.

Requery( params ), Refresh() and Resync() are unique to RowSet.

Re: FWH: MySql/MariaDB: RowSet object

Posted: Wed Oct 05, 2016 12:31 am

by nageswaragunupudi

Large RowSets:

Reading large tables/RowSets does take time and we can not avoid this. At the same time, we do not like to keep the user waiting for more than one second to see something on the screen.

One approach is to split the data into parts and show (1) different parts in different folders/pages or (2) switch display of the parts in the same browse, reading the required part on demand (i.e., depending on the choice of the user). The parts can be split on the basis of some attrubute like Branches, Periods or even range of rows like 1-1000, 1001-2000, etc. Cleverly presented, this approach provides a satisfactory user interface.

Still the problem is the user does not see the complete table at a glance, can not sort the entire table at a time, etc.

In what manner FWRowset simplifies this approach is something we shall discuss separately.

Another approach can be to read a small portion (eg. first 1000 or first 5000 records) initially and display the browse on the screen within less than a second and then read remaining records in parts or full and keep appending these records to the main rowset as and when time permits. When to read and the size of the chunks depends on the programmer's choice that suits the interface planned. Whether to read with a Timer or to read when user reaches EOF, etc are best left to the programmer.

But the main problem to adopt this approach is that the library we are using should support appending the newly read records to the main rowset. This is a feature not supported by ADO or other 3rd party libraries. FW implementation alone provides this support. We shall now discuss how this works.

1. oRs := oCn:RowSet( cTable, nRows )

cTable can be (1) name of table or (2) select statement ending with ORDER BY <primarykey>

2. Prepare and display the Browse
3. At the panned opportunity execute
if oRs:lMore
oRs:ReadNext() // all remaining records
//or
oRs:ReadNext( n ) // read only 'n' more records
oBrw:Refresh()
endif
// Repeat till oRs:lMore is .f.

This is a sample program to read a table with 200,000 rows with a timer.

Code: Select all

#include "fivewin.ch"

static oCn, oTimer
//-----------------
function Main()

   oCn := << connect to mysql server >>
   testlimit()
   oCn:Close()

return nil

function testlimit

   local oRs, cTable
   local oWnd, oBrw, oFont

   cTable := "custbig"
   oRs := oCn:RowSet( cTable, 5000 )

   DEFINE FONT oFont NAME "Segoe UI" SIZE 0,-14
   DEFINE WINDOW oWnd
   oWnd:SetFont( oFont )

   @ 0,0 XBROWSE oBrw OF oWnd ;
      DATASOURCE oRs AUTOCOLS ;
      CELL LINES FOOTERS NOBORDER

   WITH OBJECT oBrw
      :bRecSeldata   := { || oBrw:KeyNo }
      :nRecSelWidth  := "999,999"
      :bRecSelFooter := { || oBrw:nLen }
      :CreateFromCode()
   END

   oWnd:oClient := oBrw

   DEFINE TIMER oTimer INTERVAL 1000 ACTION ReadMore( oBrw ) OF oWnd

   ACTIVATE WINDOW oWnd CENTERED ;
      ON INIT ( oTimer:Activate() )

   RELEASE FONT oFont
   oTimer:DeActivate()
   oTimer:End()

return nil

func ReadMore( oBrw )

   local cMsg

   if ! PeekMessage( @cMsg, oBrw:hWnd, 0x100, 0x109, 0 )
      WITH OBJECT oBrw
         if :oDbf:lMore
            :oDbf:ReadNext( 5555 )
            :GoBottom()
            :Refresh()
         else
            oTimer:DeActivate()
         endif
      END
   endif

return nil

A little after browse displayed

finally

Re: FWH: MySql/MariaDB: RowSet object

Posted: Wed Oct 05, 2016 5:53 am

by Carles

Hi Nages,

What's the purpose of this line ?

if ! PeekMessage( @cMsg, oBrw:hWnd, 0x100, 0x109, 0 )

Re: FWH: MySql/MariaDB: RowSet object

Posted: Wed Oct 05, 2016 7:15 am

by nageswaragunupudi

Something like
If NextKey() == 0

Re: FWH: MySql/MariaDB: RowSet object

Posted: Mon Oct 17, 2016 3:24 am

by nageswaragunupudi

Large tables (continued)

Expansion:

We have seen in the above posting, how to start with reading a few records with
oRs := oCn:RowSet( <table>, nRows )
and later keep reading more and more records with
oRs:ReadNext( [nNextRows] )
and add to the original rowset. This way finally we end up with a full rowset of all rows of the large table. We can then use Sort, Filter, etc on the entire rowset, without contacting the server again.

Pagination:

Some users are used to a different approach of dealing with different parts of the table separately as pages. Rowset object now supports this approach too. (Revised build of FWH 16.08).

Here again we start with
oRs := oCn:RowSet( <table>, nRows )
nRows is treated as the pagesize ( oRs:nPageStep )

After this we can use oRs:NextPage(), oRs:PrevPage(), oRs:GoToPage( n ) as required.

Parameters:

It is also possible to deal with different logical parts of the table based on a range of values of different columns.

Example:

oRs:RowSet( "SELECT * FROM CUSTOMER WHERE `STATE` = ?", { "NY" } )
Later
oRs:ReQuery( { "OH" } )
oRs:ReQuery( { "CA" } )
etc

Re: FWH: MySql/MariaDB: RowSet object

Posted: Thu Dec 01, 2016 11:41 pm

by nageswaragunupudi

Some short-cuts:

Quickly read result of SQL into an array:

Code: Select all

aStates := oCn:QueryResult( "SELECT CODE,NAME FROM STATES" ) // --> { { code1,name1 },...{ codeN, nameN } }

Get a single value result:

Code: Select all

nTotal := oCn:QueryResult( "SELECT SUM(SALARY) FROM CUSTOMER" )

Read field values of a row into an array

Code: Select all

cList := "ID,FIRST,LAST,CITY,SALARY"
aRow := oCn:QueryResult( "SELECT " + cList + " FROM CUSTOMER WHERE ID = 100" )

Copy the same values to another row at ID = 150:

Code: Select all

// modify/edit any values of the aRow
aRow[ 1 ] := 150  // do not change for saving to same row
oCn:Insert( "customer", cList, aRow, .t. ) // .T. indicates update if primary key exists

Copy present values of a row and append as new record

Code: Select all

aRow    := oCn:QueryResult( "SELECT * FROM CUSTOMER WHERE ID = 100" )
aRow[ 1 ] := 0
oCn:Insert( "customer", nil, aRow ) // fwh 16.11. aFields can be nil

Note: All the above operations are without opening a RowSet ( oQuery )

Re: FWH: MySql/MariaDB: RowSet object

Posted: Fri Dec 02, 2016 2:11 am

by dutch

Excellent

(Like)

Re: FWH: MySql/MariaDB: RowSet object

Posted: Fri Dec 16, 2016 3:51 pm

by Adolfo

Mr Rao.

Is there something like oRowset:GetBlankRow() as in tDolphin, which I found quite easy to create a blank or default set of values for the current table to fiil with new values an insert it into the table.

Regards

From Chile
Adolfo

Re: FWH: MySql/MariaDB: RowSet object

Posted: Fri Dec 16, 2016 4:51 pm

by nageswaragunupudi

It is possible to do:

Code: Select all

oRs:Append()
oRs:FieldName := uNewValue

// change other fields
// and then call

oRs:Save()

This automatically
- inserts the new values into a new row,
- reads back the new values from the newly inserted row,
- adds the new row to the rowset and
- moves pointer to the newly appended row.

In case of auto-increment columns and any default values defined for the columns in the table, these new values are all automatially read back refreshed.

Please do not do anything more than just calling oRs:Save(). Specically do not create sql statements and execute and do not call requey. All that is unnecessary and should not be done.

But if you want to create dialogs for Edit/Append, we advise you not to GET RowSet fields directly or use memory variables. Instead, please use the more robust and safe method described below. Rowset class is tightly integrated with XBrowse and TDataRow classes to provide very easy and safest interface for editing existing records as well as inserting new records.

If you are in a browse then call
oBrw:EditSource() for editing current Row
oBrw:EditSource( .T. ) for editing blank row and inserting
If you are not in a browse then call
oRs:Edit() to edit the current row
oRs:Edit( .T. ) to edit a blank row and insert

You can do a simple test:

Code: Select all

oRs := oCn:RowSet( "tablename" )
oRs:Edit()

By default rowset uses the built-in TDataRow's default dialog. Clicking Save button is enough to save changes and also to insert a new row if this is a new row.

But mostly we want to use our own dialogs in the final application, not the default dialogs. Please do this:

Code: Select all

oRs := oCn:RowSet( "states" )
oRs:bEdit := { |oRec| EditStatesDialog( oRec ) }
oRs:Edit() // or oRs:Edit( .t. ) for appending

This is the tempate for the EditDialog

Code: Select all

function EditDialog( oRec )

   local lNew  := ( oRec:RecNo == 0 )
   local oDlg
   local lSaved := .f.

   // use lNew for any conditional gets

   DEFINE DIALOG oDlg SIZE 500,210 PIXEL ;
    TITLE If( lNew, "APPEND ", "EDIT " ) + "STATES"
   @ 20, 20 SAY "Code" SIZE 50,12 PIXEL OF oDlg
   @ 20, 80 GET oRec:Code SIZE 50,12 PIXEL OF oDlg PICTURE "@!"
   @ 40, 20 SAY "Name" SIZE 50,12 PIXEL OF oDlg
   @ 40, 80 GET oRec:Name SIZE 150,12 PIXEL OF oDlg

   @ 80, 20 BUTTON "Save" SIZE 40,15 PIXEL OF oDlg ;
            WHEN oRec:Modified() ;
            ACTION ( ( lSaved := oRec:Save() ), oDlg:End() )
   @ 80, 80 BUTTON "Cancel" SIZE 40,15 PIXEL OF oDlg ACTION oDlg:End()

   ACTIVATE DIALOG oDlg CENTERED

return lSaved

oRec:Save() inserts new record if appending or saves changes if editing
If called from browse it also refershes the browse.
Please do not do anything else except calling oRec:Save()

If you are xbrowsing the rowset then call oBrw:EditSource() or oBrw:EditSource( .t. ) for edit/append. This will activate the dialog you defined just now.

Re: FWH: MySql/MariaDB: RowSet object

Posted: Sat Dec 17, 2016 2:00 pm

by Marc Venken

Hello,

This simpel sample (copy and paste from several samples) will open a connection online.

(In my case : The program must be in de FWH/sample directory or the libmysql.dll must be in the exe folder)

Get all the database names from the server
You Select one database to work with

browse/edit the database with online connection.

edit the database with rightclick in dialog form (works)
edit the database for every field (delete // all fields are editable)

data will be changed online !!

but not found yet : How to activate the function EditDialog(oRec) to change selected fields.

Mr. Rao ?

Code: Select all

#include "FiveWin.ch"

REQUEST DBFCDX

#define WID  600
#define HGT  350

FUNCTION Main


   SET 3DLOOK ON

   DEFINE BRUSH oBrush STYLE TILED       // FiveWin new predefined Brushes
   DEFINE WINDOW oWnd TITLE "F A C T U M A T" MENU BuildMenu() BRUSH oBrush
   DEFINE BUTTONBAR oBar OF oWnd

   DEFINE FONT oDlFont NAME 'Courier New' SIZE 8,15
   DEFINE FONT oFont NAME "Segoe UI" SIZE 0,-14

   SET MESSAGE OF oWnd TO FWVERSION + " Test" 2007
   ACTIVATE WINDOW oWnd MAXIMIZED

   oWnd:end()
   dbcloseall()
return nil

function BuildMenu()

   local oMenu
   MENU oMenu
      MENUITEM "&Prestashop" ACTION presta(oWnd)
   ENDMENU

return oMenu



function presta(oWnd)

   local oRs, cTable, oBrw, oList

//  Your server data

   local cServer     := "www..."
   local cDataBase   := "ID211094_shop"
   local cUser       := "ID211094_shop"
   local cPassWord   := "*******"


   FWCONNECT oCn HOST cServer USER cUser PASSWORD cPassword DATABASE cDatabase

   if oCn == nil
      ? "Failed to connect"
      return nil
   endif

   oList := ocn:ListTables()  // Get tables from online database
   cFile:=msgselect(oList)  // Select 1 table to use for open

//   cFile = "ps_product_lang"  // delete this line... just for testing

   oRs := oCn:RowSet( cFile )  // Get fields from table


   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 1200,700 PIXEL FONT oFont TITLE cFile;
      GRADIENT { { 1, nRGB( 125, 155, 175 ), nRGB( 125, 155, 175 ) } }


   @  2,2 XBROWSE oBrw OF oDlg SIZE 500,200 ;
      DATASOURCE oRs AUTOCOLS ;
      CELL LINES FOOTERS NOBORDER FASTEDIT

   oBrw:nStretchCol         := STRETCHCOL_LAST

   WITH OBJECT oBrw
      :bRecSeldata   := { || oBrw:KeyNo }
      :nRecSelWidth  := "999,999"
      :bRecSelFooter := { || oBrw:nLen }

      :nColDividerStyle := LINESTYLE_LIGHTGRAY
      :nRowDividerStyle := LINESTYLE_LIGHTGRAY
      :nMarqueeStyle    := MARQSTYLE_HIGHLROWMS
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }

      :bRClicked := { || oBrw:editsource(),oBrw:refresh() }

      //:nEditTypes    := EDIT_GET  // All fiels are editable

      :bClrEdits        := { || { CLR_BLACK, CLR_YELLOW }}
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }
      :l2007 := .t.

      :CreateFromCode()
   END

   WITH OBJECT oBrw:aCols[4]
      :nEditType     := EDIT_GET

      :bEditValid    := { | oRec | editdialog( oRec ) }

      //:bOnPostEdit   := { | oCol, xVal, nKey | If( nKey <> VK_ESCAPE ,( oCol:value := xVal ) , msginfo("Here i'm") ) }
      //:bEditWhen     := {|| !EMPTY( aItems[oBrw:nArrayAt, 1] ) }

      //:bEditBlock    := { |oRec| editdialog( oRec ) }
      //:bOnChange     := { |oCol,uOldVal| oData:FieldValue:VarPut( PRD->VELD ) }


      //:bEditBlock    := { |r,c,oStruc| master->naam = cust->(fieldname(r)) }
      //:bOnChange     := { |oCol,uOldVal| oData:Fieldvalue:VarPut( cust->(fieldname(1)) )}
      //:bOnChange     := { |r,c| oData:Fieldvalue:VarPut( cust->(fieldname(r)) )}

   END WITH

   //oWnd:oClient := oBrw

   ACTIVATE DIALOG oDlg CENTERED

   RELEASE FONT oFont
   oCn:Close()

return nil

function MsgSelect( aItems, cValue, cTitle )

   local oDlg

   DEFINE FONT oFont NAME "Courier New" SIZE 8,15

   DEFAULT cTitle := "Maak uw keuze"

   DEFINE DIALOG oDlg FROM 5,10 TO 24, 95 font oFont TITLE cTitle

   @  1, 2 LISTBOX cValue ITEMS aItems SIZE 305, 110  OF oDlg

   @ 7, 05 BUTTON "&OK"     OF oDlg SIZE 40, 12  ACTION oDlg:End() DEFAULT
   @ 7, 12 BUTTON "&Cancel" OF oDlg SIZE 40, 12  ACTION ( cValue := "", oDlg:End() )

   ACTIVATE DIALOG oDlg CENTERED

return cValue

function EditDialog( oRec )


   local lNew  := ( oRec:RecNo == 0 )
   local oDlg
   local lSaved := .f.

   msginfo("Dialog reached.  Can be used now")

   /*

    // use lNew for any conditional gets
   // adapt to your own needs and fields..

   DEFINE DIALOG oDlg SIZE 500,210 PIXEL ;
    TITLE If( lNew, "APPEND ", "EDIT " ) + "STATES"
   @ 20, 20 SAY "Code" SIZE 50,12 PIXEL OF oDlg
   @ 20, 80 GET oRec:Code SIZE 50,12 PIXEL OF oDlg PICTURE "@!"
   @ 40, 20 SAY "Name" SIZE 50,12 PIXEL OF oDlg
   @ 40, 80 GET oRec:Name SIZE 150,12 PIXEL OF oDlg

   @ 80, 20 BUTTON "Save" SIZE 40,15 PIXEL OF oDlg ;
            WHEN oRec:Modified() ;
            ACTION ( ( lSaved := oRec:Save() ), oDlg:End() )
   @ 80, 80 BUTTON "Cancel" SIZE 40,15 PIXEL OF oDlg ACTION oDlg:End()
   */

return lSaved

Re: FWH: MySql/MariaDB: RowSet object

Posted: Sat Dec 17, 2016 6:28 pm

by nageswaragunupudi

Code: Select all

#include "FiveWin.ch"

REQUEST DBFCDX

#define WID  600
#define HGT  350

FUNCTION Main


   local oWnd, oBrush, oFont, oBar

   DEFINE BRUSH oBrush STYLE TILED       // FiveWin new predefined Brushes
   DEFINE WINDOW oWnd TITLE "F A C T U M A T" MENU BuildMenu() BRUSH oBrush
   DEFINE BUTTONBAR oBar OF oWnd

   DEFINE FONT oFont NAME "Segoe UI" SIZE 0,-14

   SET MESSAGE OF oWnd TO FWVERSION + " Test" 2007
   ACTIVATE WINDOW oWnd MAXIMIZED

   oWnd:end()
   dbcloseall()
return nil

function BuildMenu()

   local oMenu

   MENU oMenu
      MENUITEM "&Prestashop" ACTION presta()
   ENDMENU

return oMenu



function presta()

   local oRs, cTable, oBrw, oList
   local oCn, oDlg, oBar, oFont

//  Your server data

   local cServer     := "www..."
   local cDataBase   := "ID211094_shop"
   local cUser       := "ID211094_shop"
   local cPassWord   := "*******"

   EDITVARS cServer, cUser, cDataBase, cPassword


   FWCONNECT oCn HOST cServer USER cUser PASSWORD cPassword DATABASE cDatabase

   if oCn == nil
      ? "Failed to connect"
      return nil
   endif

   XBROWSER oCn:ListTables() SELECT ( cTable := oBrw:aRow )
   if Empty( cTable )
      ? "No Table selected"
      return nil
   endif


   oRs := oCn:RowSet( cTable )

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 1200,700 PIXEL FONT oFont TITLE cTable;
      GRADIENT { { 1, nRGB( 125, 155, 175 ), nRGB( 125, 155, 175 ) } } ;
      TRUEPIXEL

   DEFINE BUTTONBAR oBar OF oDlg SIZE 100,32 2007

   @ 60,20 XBROWSE oBrw OF oDlg SIZE -20,-20 PIXEL ;
      DATASOURCE oRs AUTOCOLS ;
      CELL LINES FOOTERS NOBORDER FASTEDIT

   oBrw:nStretchCol         := STRETCHCOL_WIDEST

   WITH OBJECT oBrw
      :bRecSeldata   := { || oBrw:KeyNo }
      :nRecSelWidth  := "999,999"
      :bRecSelFooter := { || oBrw:nLen }

      :nColDividerStyle := LINESTYLE_LIGHTGRAY
      :nRowDividerStyle := LINESTYLE_LIGHTGRAY
      :nMarqueeStyle    := MARQSTYLE_HIGHLROW
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }

      :bRClicked := { || oBrw:editsource(),oBrw:refresh() }

      :nEditTypes    := EDIT_GET  // All fiels are editable

      :bClrEdits        := { || { CLR_BLACK, CLR_YELLOW }}
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }

      :CreateFromCode()
   END

   DEFINE BUTTON OF oBar PROMPT "Add"    ACTION oBrw:EditSource( .T. )
   DEFINE BUTTON OF oBar PROMPT "Edit"   ACTION oBrw:EditSource()
   DEFINE BUTTON OF oBar PROMPT "Delete" ACTION oBrw:Delete()


   ACTIVATE DIALOG oDlg CENTERED

   RELEASE FONT oFont
   oCn:Close()

return nil