FWHMARIADB Samples
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
FWHMARIADB Samples
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.
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
G. N. Rao.
Hyderabad, India
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWHMARIADB Samples
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)
Download Link:
http://anserkk.com/gnraomysql/view.php?id=2
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
#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
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: FWHMARIADB Samples
Mr Rao,
Link is broken
Regards,
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
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
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWHMARIADB Samples
Fixed. Regret the inconvenience.nnicanor wrote:Mr Rao,
Link is broken
Regards,
Please download now.
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: FWHMARIADB Samples
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
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 & VS 2019 & FWH 20.12
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWHMARIADB Samples
1) After opening a table as rowset, we can use oRs:aStructure, which is like DBSTRUCT() for a DBF
2) Without opening a table we can use
Then we can use the following methods to modify structure of a table.
Code: Select all
oRs := oCn:tablename
XBROWSER oRs:aStructure
Code: Select all
oCn:ListColumns( cTable, [lShow] ) //--> array of columns with details
Code: Select all
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
G. N. Rao.
Hyderabad, India
Re: FWHMARIADB Samples
Thank you Mr. Raonageswaragunupudi wrote:1) After opening a table as rowset, we can use oRs:aStructure, which is like DBSTRUCT() for a DBF2) Without opening a table we can useCode: Select all
oRs := oCn:tablename XBROWSER oRs:aStructure
Then we can use the following methods to modify structure of a table.Code: Select all
oCn:ListColumns( cTable, [lShow] ) //--> array of columns with details
Code: Select all
oCn:AddColumn( cTable, aColSpec ) oCn:AlterColumn( cTable, aColSpec ) oCn:RenameColumn( cTable, cOldName, cNewName ) oCn:AddAutoInc( cTable, cCol ) oCn:MakePrimaryKey( cTable, cCol )
Regards,
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWHMARIADB Samples
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
ORACLE
DBF (read into array)
The program posted in
http://forums.fivetechsupport.com/viewt ... =6&t=33905
can use an sql statement like this.
This is working sample:
maria12.prg
Download Link: http://anserkk.com/gnraomysql/view.php?id=13
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
SELECT fields, amount, ( @ntotal = @ntotal + amount ) AS running_total
FROM mytable, ( SELECT @ntotal := 0 ) AS t
WHERE <clauses>
ORDER BY <clauses>
Code: Select all
SELECT docdt, amount, sum(amount) over (order by docdt) as running_total
FROM <mytable>
Code: Select all
nTotal := 0
aData := FW_DbfToArray("AMOUNT,(nTotal := nTotal + AMOUNT)" )
http://forums.fivetechsupport.com/viewt ... =6&t=33905
can use an sql statement like this.
Code: Select all
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
maria12.prg
Code: Select all
#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
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWHMARIADB Samples
FWMariaDB -:- ADO -:- Dolphin/TMySql
Following 3 SQL statements are very simple and we use similar statements quite often.
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
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.
Download Link : http://anserkk.com/gnraomysql/view.php?id=14
Following 3 SQL statements are very simple and we use similar statements quite often.
Code: Select all
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
Here is a sample to try:
maria05.prg
Code: Select all
#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
Code: Select all
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"
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: FWHMARIADB Samples
Where is the source code located for the function FW_DemoDB()?
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWHMARIADB Samples
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.
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
G. N. Rao.
Hyderabad, India
Re: FWHMARIADB Samples
Mr.Rao
Could you post an example of using MYSQL EMBEDDED SERVER ??
Att
João Carlos
VinheSoft
Could you post an example of using MYSQL EMBEDDED SERVER ??
Att
João Carlos
VinheSoft
João Carlos
VinheSoft Informatica Ltda
VinheSoft Informatica Ltda
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWHMARIADB Samples
Please see this post regarding using embedded server.vinhesoft wrote:Mr.Rao
Could you post an example of using MYSQL EMBEDDED SERVER ??
Att
João Carlos
VinheSoft
http://forums.fivetechsupport.com/viewt ... =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
#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
Download Link: http://anserkk.com/gnraomysql/view.php?id=15
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: FWHMARIADB Samples
How to inherit the fwmariaconnect class ????
CLASS MYCLASS from fwmariaconnect // ( does not work )
class MYCLASS FROM TDOLPHIN // ( OK )
CLASS MYCLASS from fwmariaconnect // ( does not work )
class MYCLASS FROM TDOLPHIN // ( OK )
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWHMARIADB Samples
Code: Select all
CLASS MYCLASS FROM TDOLPHIN
We get unresolved external TDolphin
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India