FWHMARIADB Samples

FWHMARIADB Samples

Postby nageswaragunupudi » Wed Apr 12, 2017 11:29 am

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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Wed Apr 12, 2017 11:32 am

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
 


Image

Download Link:


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

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby nnicanor » Wed Apr 12, 2017 1:14 pm

Mr Rao,

Link is broken

Regards,
Nicanor Martinez M.
Auditoria y Sistemas Ltda.
MicroExpress Ltda.
FW + FWH + XHARBOUR + HARBOUR + PELLES C + XDEVSTUDIO + XEDIT + BCC + VC_X86 + VCC_X64 + MINGW + R&R Reports + FastReport + Tdolphin + ADO + MYSQL + MARIADB + ORACLE
nnicanor@yahoo.com
nnicanor
 
Posts: 302
Joined: Fri Apr 23, 2010 4:30 am
Location: Colombia

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Wed Apr 12, 2017 3:00 pm

nnicanor wrote:Mr Rao,

Link is broken

Regards,

Fixed. Regret the inconvenience.
Please download now.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby Horizon » Wed Apr 12, 2017 5:51 pm

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
Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.04
Horizon
 
Posts: 1322
Joined: Fri May 23, 2008 1:33 pm

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Wed Apr 12, 2017 8:56 pm

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 )
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby Horizon » Thu Apr 13, 2017 6:21 am

nageswaragunupudi wrote: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
Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.04
Horizon
 
Posts: 1322
Joined: Fri May 23, 2008 1:33 pm

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Sun Apr 16, 2017 9:55 am

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
 

Image

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

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Tue Apr 18, 2017 7:35 am

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.

Image

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
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby rhlawek » Tue Apr 18, 2017 4:07 pm

Where is the source code located for the function FW_DemoDB()?
User avatar
rhlawek
 
Posts: 194
Joined: Sun Jul 22, 2012 7:01 pm

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Tue Apr 18, 2017 10:34 pm

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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby vinhesoft » Wed Apr 19, 2017 8:07 pm

Mr.Rao

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

Att

João Carlos
VinheSoft
João Carlos
VinheSoft Informatica
BCC 7.7 - FHW 24.07 - Harbour 3.2.0dev (r2404101339) for BCC 7.7
vinhesoft
 
Posts: 36
Joined: Sat Sep 03, 2016 3:11 pm
Location: Campinas/SP-Brasil

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Thu Apr 20, 2017 6:44 am

vinhesoft wrote: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
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby luiz53 » Thu Apr 20, 2017 1:18 pm

How to inherit the fwmariaconnect class ????

CLASS MYCLASS from fwmariaconnect // ( does not work )

class MYCLASS FROM TDOLPHIN // ( OK )
luiz53
 
Posts: 43
Joined: Fri Jun 01, 2007 12:41 pm

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Thu Apr 20, 2017 3:09 pm

Code: Select all  Expand view  RUN
CLASS MYCLASS FROM TDOLPHIN

does not work
We get unresolved external TDolphin
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Next

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 92 guests

cron