#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
nnicanor wrote:Mr Rao,
Link is broken
Regards,
oRs := oCn:tablename
XBROWSER oRs:aStructure
oCn:ListColumns( cTable, [lShow] ) //--> array of columns with details
oCn:AddColumn( cTable, aColSpec )
oCn:AlterColumn( cTable, aColSpec )
oCn:RenameColumn( cTable, cOldName, cNewName )
oCn:AddAutoInc( cTable, cCol )
oCn:MakePrimaryKey( cTable, cCol )
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 )
SELECT fields, amount, ( @ntotal = @ntotal + amount ) AS running_total
FROM mytable, ( SELECT @ntotal := 0 ) AS t
WHERE <clauses>
ORDER BY <clauses>
SELECT docdt, amount, sum(amount) over (order by docdt) as running_total
FROM <mytable>
nTotal := 0
aData := FW_DbfToArray("AMOUNT,(nTotal := nTotal + AMOUNT)" )
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
#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
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
#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
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"
vinhesoft wrote:Mr.Rao
Could you post an example of using MYSQL EMBEDDED SERVER ??
Att
João Carlos
VinheSoft
#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
CLASS MYCLASS FROM TDOLPHIN
Return to FiveWin for Harbour/xHarbour
Users browsing this forum: No registered users and 82 guests