FWHMARIADB Samples

[nageswaragunupudi]

In this thread, we keep posting a series of samples demonstrating several features of FWMariaDB. Users do not need access to their own mysql/mariadb server, because we connect to a server in the cloud for these tests. Please note that the server being free server access may be slower than standard business class servers users deal with in their real life applications.

While users having latest FWH can build and test the samples at their end, it is possible for any user to just download the readily built exe and test. What all required is to click the download link, unzip the file into a temporary folder and click on the Exe.

We welcome all users, including those who are not using mysql, to test and offer their feedback which will help us to improve the libs.

[nageswaragunupudi]

Sample: maria11.prg

Unicode, International Timestamps, Triggers, etc.


This sample demonstrates:

1) Unicode capabilities. User can enter Unicode text of any language in field "unicodetext" either from keyboard or by copy/paste. The Uniocode text can be exported to Excel and printed as report.
2) Ability to create table with columns that accept Unicode text and some other columns accepting latin characters only.
3) Update timestamps: Creating columns where modification date/time is automatically updated.
4) International timestamps: The program can be executed globally. Every user sees his local time, though the database stores the time in UTC. For example, a user from India makes an entry at 11:30 am Indian time. The Indian user sees the time as 11:30 am. Another user in Europe sees the time of that entry as 05:00 am. This is ideal for applications to be used globally.
5) Creating table so that entries in particular columns can be automatically converted to Upper/Initcap and stored in the database. Example: fields "language" and "entrymode"
6) Usage of triggers: Automatic capture and storing of username and pcname making the entry.
7) Autoappend feature: Creating new columns by pressing downarrow key on the last row. The new row is saved only when some data is entered.
8 ) Visibility of other users' changes automatically. ( Resync() and Refresh() methods)

Code: Select all  Expand view  RUN

#include "fivewin.ch"

static oCn

function Main()

   local oRs

   SET DATE BRITISH
   SET CENTURY ON

   FW_SetUnicode( .T. )

   oCn   := FW_DemoDB( 1 )

   CheckTable()

   oRs   := oCn:testunicode
   oRs:Fields( "username" ):lReadOnly := .t.
   oRs:lAutoAppend := .t.

   XBROWSER oRs FASTEDIT TITLE "Unicode Text" SETUP BrwSetup( oBrw )

return nil

static function BrwSetup( oBrw )

   local oDlg, oRs

   oDlg           := oBrw:oWnd
   oRs            := oBrw:oDbf
   oDlg:bStart    := { || oDlg:nHeight := 600, oDlg:Center() }
   oBrw:lCanPaste := .t.
   oBrw:bChange   := { || oRs:ReSync(), oBrw:RefreshCurrent() }
   oBrw:bGotFocus := { || If( oRs:Refresh() > 0, oBrw:Refresh(), nil ) }

return nil

static function CheckTable()

   local cSql

   if oCn:TableExists( "testunicode" )
      return nil
   endif

   oCn:CreateTable( "testunicode", { ;
      { "language",     'C', 15, 0, "latin1 comment 'case:upper'" }, ;
      { "unicodetext",  'C', 40, 0, "utf8"   }, ;
      { "entrymode",    'C', 20, 0, "latin1 comment 'case:proper'" }, ;
      { "username",     'C', 30, 0, "utf8"   }, ;
      { "writedt",      '=',  8, 0 } }, nil, "utf8" )

TEXT INTO cSql
CREATE TRIGGER testunicode_bi BEFORE INSERT ON testunicode
FOR EACH ROW
BEGIN
   SET NEW.username = SUBSTRING( CONCAT_WS( ', ', @os_user, @pc_name ), 1, 30 );
END
ENDTEXT

   ? oCn:Execute( "DROP TRIGGER IF EXISTS testunicode_bi" )
   ? oCn:Execute( cSql )

TEXT INTO cSql
CREATE TRIGGER testunicode_bu BEFORE UPDATE ON testunicode
FOR EACH ROW
BEGIN
   SET NEW.username = SUBSTRING( CONCAT_WS( ', ', @os_user, @pc_name ), 1, 30 );
END
ENDTEXT

   ? oCn:Execute( "DROP TRIGGER IF EXISTS testunicode_bu" )
   ? oCn:Execute( cSql )

return nil
 

Download Link:


http://anserkk.com/gnraomysql/view.php?id=2

[nnicanor]

Mr Rao,

Link is broken

Regards,

[nageswaragunupudi]

Mr Rao,

Link is broken

Regards,

Fixed. Regret the inconvenience.
Please download now.

[Horizon]

Hi Mr. Rao,

You mention oCn:TableExists() method. Is there any method to check fields (name, size, decimal etc.) to upgrade new data design?

Hakan

[nageswaragunupudi]

1) After opening a table as rowset, we can use oRs:aStructure, which is like DBSTRUCT() for a DBF

Code: Select all  Expand view  RUN

oRs := oCn:tablename
XBROWSER oRs:aStructure
 

2) Without opening a table we can use

Code: Select all  Expand view  RUN

oCn:ListColumns( cTable, [lShow] ) //--> array of columns with details
 

Then we can use the following methods to modify structure of a table.

Code: Select all  Expand view  RUN

oCn:AddColumn( cTable, aColSpec )
oCn:AlterColumn( cTable, aColSpec )
oCn:RenameColumn( cTable, cOldName, cNewName )
oCn:AddAutoInc( cTable, cCol )
oCn:MakePrimaryKey( cTable, cCol )
 

[Horizon]

1) After opening a table as rowset, we can use oRs:aStructure, which is like DBSTRUCT() for a DBF

Code: Select all  Expand view  RUN

oRs := oCn:tablename
XBROWSER oRs:aStructure
 

2) Without opening a table we can use

Code: Select all  Expand view  RUN

oCn:ListColumns( cTable, [lShow] ) //--> array of columns with details
 

Then we can use the following methods to modify structure of a table.

Code: Select all  Expand view  RUN

oCn:AddColumn( cTable, aColSpec )
oCn:AlterColumn( cTable, aColSpec )
oCn:RenameColumn( cTable, cOldName, cNewName )
oCn:AddAutoInc( cTable, cCol )
oCn:MakePrimaryKey( cTable, cCol )
 

Thank you Mr. Rao

[nageswaragunupudi]

Running Totals (Cumulative Totals)

On several occassions we need to show running totals (cumulative totals) of a value in a browse. It is normally convenient to maintain the running totals in an array and display in the browse. But this involves additional work of building an array and maching it with normal data in the browse. There are times when it is more convenient, if we can directly read the data along with running totals and display directly.

In such cases, we may use SQL query like this:
MYSQL

Code: Select all  Expand view  RUN

SELECT fields, amount, ( @ntotal = @ntotal + amount ) AS running_total
FROM mytable, ( SELECT @ntotal := 0 ) AS t
WHERE <clauses>
ORDER BY <clauses>
 

ORACLE

Code: Select all  Expand view  RUN

SELECT docdt, amount, sum(amount) over (order by docdt) as running_total
FROM <mytable>
 

DBF (read into array)

Code: Select all  Expand view  RUN

nTotal := 0
aData := FW_DbfToArray("AMOUNT,(nTotal := nTotal + AMOUNT)" )
 

The program posted in
viewtopic.php?f=6&t=33905
can use an sql statement like this.

Code: Select all  Expand view  RUN

   SELECT id, ncli, fecha, descripcion, numero, tipo, importe,
   ( @bal := IF( tipo = '1', @bal + importe, @bal - importe ) ) AS nsaldo
   FROM ctacte,
   ( SELECT @bal := 0 ) AS t
   WHERE ncli = <client>
   ORDER BY fecha
 

This is working sample:
maria12.prg

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn   := FW_DemoDB()
   local oRs, cSql
   local oDlg, oFont, oBrw

   oCn:lShowErrors := .t.

TEXT INTO cSql
   SELECT id, ncli, fecha, descripcion, numero, tipo, importe,
   ( @bal := IF( tipo = '1', @bal + importe, @bal - importe ) ) AS nsaldo
   FROM ctacte,
   ( SELECT @bal := 0 ) AS t
   WHERE ncli = ?
   ORDER BY fecha
ENDTEXT

   oRs   := oCn:RowSet( cSql, { 101 } )
   oRs:GoBottom()

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 800,700 PIXEL FONT oFont ;
      TITLE "Running Totals"

   @ 50,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oRs ;
      COLUMNS "id", "Fecha", "Descripcion", "Numero", ;
         "If( tipo == '1', importe, 0 )", ;
         "If( tipo == '1', 0, importe )", ;
         "nsaldo" ;
      HEADERS "DocID", nil, nil, nil, "DEBE", "PAGO", "SALDO" ;
      PICTURES "999", nil, nil, nil, "@EZ 999,999,999.99", "@EZ 999,999,999.99", "@EZ 999,999,999.99" ;
      COLSIZES 50,100,100,100,100,100,110 ;
      CELL LINES NOBORDER FOOTERS FASTEDIT

   WITH OBJECT oBrw
      AEval( :aCols, { |o| o:nEditType := EDIT_GET }, 3, 4 )
      WITH OBJECT :Debe
         :nFooterType      := AGGR_SUM
         :bEditValid       := { |oGet| oGet:VarGet() > 0 }
         :bOnPostEdit      := { |o,x,n| If( o == VK_ESCAPE .or. x < 0, nil, ( oRs:tipo := '1', oRs:importe := x ) ) }
         :bOnChange        := { || oRs:Requery(), oBrw:Refresh() }
      END
      WITH OBJECT :Pago
         :nFooterType    := AGGR_SUM
         :bEditValid       := { |oGet| oGet:VarGet() > 0 }
         :bOnPostEdit      := { |o,x,n| If( o == VK_ESCAPE .or. x < 0, nil, ( oRs:tipo := '0', oRs:importe := x ) ) }
         :bOnChange        := { || oRs:Requery(), oBrw:Refresh() }
      END

      :Saldo:bFooter    := { || oBrw:Debe:nTotal - oBrw:Pago:nTotal }

      :MakeTotals()
      :CreateFromCode()
   END

   @ 20,20 BTNBMP PROMPT "Delete" SIZE 60,20 PIXEL FLAT OF oDlg ACTION ;
      ( oRs:Delete(), oRs:ReQuery(), oBrw:MakeTotals(), oBrw:Refresh(), oBrw:SetFocus() )

   ACTIVATE DIALOG oDlg CENTERED

   oCn:Close()

return nil
 

Download Link: http://anserkk.com/gnraomysql/view.php?id=13

[nageswaragunupudi]

FWMariaDB -:- ADO -:- Dolphin/TMySql

Following 3 SQL statements are very simple and we use similar statements quite often.

Code: Select all  Expand view  RUN

1)
SELECT ID AS StateID, CODE AS StateCode, NAME AS StateName FROM states
2)
SELECT ID, CODE AS StateCode, NAME AS StateName FROM states
3)
SELECT C.ID, C.FIRST, C.CITY, S.NAME AS STATENAME FROM customer C
LEFT JOIN states S ON C.STATE = S.CODE
 

It is natural for us to expect it should be possible with any library to read data using these very simple SQLs and then to edit/modify data and save to the database easily. But unfortunately it is not the case.

Here is a sample to try:
maria05.prg

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "tdolphin.ch"

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

function Main()

   local oCn, oRs, cSql, aSql
   local aLib     := { 5, "DLP", "ADO" }
   local nOption

   nOption  := Alert( "Choose Connection Type", { "FWHMARIADB", "DOLPHIN", "ADO" } )
   if nOption < 1
      return nil
   endif

   cSql  := "SELECT ID AS StateID, CODE AS StateCode, NAME AS StateName" + ;
            " FROM states"

   aSql  := { "SELECT ID AS StateID, CODE AS StateCode, NAME AS StateName FROM states", ;
              "SELECT ID, CODE AS StateCode, NAME AS StateName FROM states", ;
              "SELECT C.ID, C.FIRST, C.CITY, S.NAME AS STATENAME FROM customer C " + ;
              "LEFT JOIN states S ON C.STATE = S.CODE" }

   oCn   := FW_DemoDB( aLib[ nOption ] )
   if oCn == nil
      ? "Can not connect"
      return nil
   endif

   cSql  := SelectSQL( aSql )

   MsgInfo( cSql, "OPENING TABLE WITH THIS SQL" )

   if nOption < 3
      oRs   := oCn:Query( cSql )
   else
      oRs   := FW_OpenRecordSet( oCn, cSql )
   endif

   XBROWSER oRs FASTEDIT TITLE "Please Edit some rows"

   if nOption == 2
      oRs:End()
      oCn:End()
   else
      oRs:Close()
      oCn:Close()
   endif

return nil

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

static function SelectSQL( aSql )

   local oDlg, oRad, oFont
   local nSelect  := 1

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 860,200 PIXEL TRUEPIXEL FONT oFont ;
      TITLE "SELECT SQL STATEMENT"

   @  20,20 RADIO oRad VAR nSelect SIZE 300,20 PIXEL OF oDlg
   @  40,20 RADIOITEM aSql[ 1 ] RADIOMENU oRad SIZE 820,20 PIXEL OF oDlg
   @  65,20 RADIOITEM aSql[ 2 ] RADIOMENU oRad SIZE 820,20 PIXEL OF oDlg
   @  90,20 RADIOITEM aSql[ 3 ] RADIOMENU oRad SIZE 820,20 PIXEL OF oDlg

   @ 130,640 BTNBMP PROMPT "SELECT" FLAT SIZE 200,40 PIXEL OF oDlg ACTION oDlg:End()

   ACTIVATE DIALOG oDlg CENTERED

return aSql[ nSelect ]

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

EXTERNAL TDOLPHINSRV
 

We can select to use either FWH, ADO or Dolphin and also select any one of the SQL statements. We can read data and view in a Browse. While it is possible to edit and save changes with FWH and ADO, we get runtime errors with Dolphin and it is not possible to save changes.

Code: Select all  Expand view  RUN

   Error description: Error MYSQL/1054  Unknown column 'statename' in 'field list'
Stack Calls
===========
   Called from: .\source\prg\tdolpsrv.prg => DOLPHIN_DEFERROR( 2807 )
   Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:CHECKERROR( 793 )
   Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:SQLQUERY( 2024 )
   Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:SAVE( 1456 )
   Called from: .\source\classes\XBROWSE.PRG => (b)TXBROWSE_SETDOLPHIN( 5803 )

   TDOLPHINQRY:SAVE
     Param   1:    C    "UPDATE states SET statename='Maine-1' WHERE id = 3"
 

Download Link : http://anserkk.com/gnraomysql/view.php?id=14

[rhlawek]

Where is the source code located for the function FW_DemoDB()?

[nageswaragunupudi]

The source of FW_DemoDB() is private. One of our friends subscribed for space on a MySql cloud server for the purpose of learning along with friends. This is meant purely for educational purposes. We share this connection for the purpose of testing these samples. FW_DemoDB() connects to this server and returns the connection object. The source is kept private to keep our friend's credentials and password confidential.

If users have access to their own MySql server, they can test these samples by replacing the call to FW_DemoDB() with their functions/commands to connect to their server.

Such of those users who do not have access to their own MySql servers or do not have latest FWH libs can download and execute the exe to test the samples.

[vinhesoft]

Mr.Rao

Could you post an example of using MYSQL EMBEDDED SERVER ??

Att

João Carlos
VinheSoft

[nageswaragunupudi]

Mr.Rao

Could you post an example of using MYSQL EMBEDDED SERVER ??

Att

João Carlos
VinheSoft

Please see this post regarding using embedded server.
viewtopic.php?f=3&t=33798

To help you get an experience of creating and using embedded server we provide a readily built application for testing and learning. If you consider it useful you may follow the guidelines contained in the above post for creating embedded server applications.

Sample Code:

Code: Select all  Expand view  RUN

#include "fivewin.ch"

REQUEST DBFCDX

function Main()

   local oCn, cDataFolder, cLangFolder

   MsgInfo( "DEMO FOR TESTING AND EDUCATIONAL PURPOSE ONLY", "MYSQL EMBEDDED SERVER" )

   cDataFolder := cLangFolder := cFilePath( ExeName() )

   FWCONNECT oCn HOST cDataFolder LANGFOLDER cLangFolder DATABASE "fwh"
   if ocn == nil
      ? "Connect fail"
   else
      if !oCn:TableExists( "customer" )
         ocn:importfromdbf( "customer.dbf" )
      endif
      xbrowser ocn:customer FASTEDIT AUTOSORT TITLE "CUSTOMER"
      if !oCn:TableExists( "states" )
         ocn:importfromdbf( "states.dbf" )
      endif
      xbrowser ocn:states FASTEDIT AUTOSORT TITLE "STATES"
      TestServer( oCn )
      ocn:close()
   endif

return nil

function TestServer( oCn )

   local nOpt  := 0
   local cDbf,cSql, oRs

   do while .t.
      nOpt  := Alert( "Select an option", { "ViewTables", "ImportDBF", "TestSQL" } )

      if nOpt == 1
         XBROWSER oCn:ListTables TITLE "SELECT TO VIEW" ;
         SELECT XBrowse( oCn:RowSet( oBrw:aCols[ 1 ]:Value ), oBrw:aCols[ 1 ]:Value )
      elseif nOpt == 2
         if !Empty( cDbf := cGetFile( "DBF |*.dbf|" ) )
            oCn:ImportFromDBF( cDBF )
            XBROWSER oCn:RowSet( cFileNoExt( cDBF ) )
         endif
      elseif nOpt == 3
         cSql  := ""
         MEMOEDIT( @cSql )
         if ! Empty( cSql )
            cSql  := AllTrim( cSql )
            if Lower( cSql ) = "select"
               XBROWSER oCn:RowSet( cSql )
            else
               oCn:lShowMessages := .t.
               XBROWSER oCn:Execute( cSql )
               oCn:lShowMessages := .f.
            endif
         endif
      else
         return nil
      endif
   enddo

return nil
 

You can view, browse, edit tables, import from dbfs and also test your own sql statements.

Download Link: http://anserkk.com/gnraomysql/view.php?id=15

[luiz53]

How to inherit the fwmariaconnect class ????

CLASS MYCLASS from fwmariaconnect // ( does not work )

class MYCLASS FROM TDOLPHIN // ( OK )

[nageswaragunupudi]

Code: Select all  Expand view  RUN

CLASS MYCLASS FROM TDOLPHIN

does not work
We get unresolved external TDolphin