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

#include "fivewin.ch"static oCnfunction 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 nilstatic 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 nilstatic 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 cSqlCREATE TRIGGER testunicode_bi BEFORE INSERT ON testunicodeFOR EACH ROWBEGIN   SET NEW.username = SUBSTRING( CONCAT_WS( ', ', @os_user, @pc_name ), 1, 30 );ENDENDTEXT   ? oCn:Execute( "DROP TRIGGER IF EXISTS testunicode_bi" )   ? oCn:Execute( cSql )TEXT INTO cSqlCREATE TRIGGER testunicode_bu BEFORE UPDATE ON testunicodeFOR EACH ROWBEGIN   SET NEW.username = SUBSTRING( CONCAT_WS( ', ', @os_user, @pc_name ), 1, 30 );ENDENDTEXT   ? 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

oRs := oCn:tablenameXBROWSER oRs:aStructure 

2) Without opening a table we can use

Code: Select all | Expand

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

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

oRs := oCn:tablenameXBROWSER oRs:aStructure 

2) Without opening a table we can use

Code: Select all | Expand

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

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

SELECT fields, amount, ( @ntotal = @ntotal + amount ) AS running_totalFROM mytable, ( SELECT @ntotal := 0 ) AS tWHERE <clauses>ORDER BY <clauses> 

ORACLE

Code: Select all | Expand

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

DBF (read into array)

Code: Select all | Expand

nTotal := 0aData := 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

   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

#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 fechaENDTEXT   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

1)SELECT ID AS StateID, CODE AS StateCode, NAME AS StateName FROM states2)SELECT ID, CODE AS StateCode, NAME AS StateName FROM states3)SELECT C.ID, C.FIRST, C.CITY, S.NAME AS STATENAME FROM customer CLEFT 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

#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()   endifreturn 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 CENTEREDreturn 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

   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

#include "fivewin.ch"REQUEST DBFCDXfunction 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()   endifreturn nilfunction 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   enddoreturn 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

CLASS MYCLASS FROM TDOLPHIN

does not work
We get unresolved external TDolphin