aStru := { ;
{ "codgru", "+", 3, 0 }, ; // '+' : AutoInc Primary Key
{ "nomgru", "C", 30, 0 }, ;
{ "altera", "D", 8, 0 }, ;
{ "check", "L", 1, 0 }, ;
{ "Amount", "N", 10, 2 }, ;
{ "details", "M", 10, 0 }, ; // Unlimited Text Memo Field
{ "photo", "m", 10, 0 }, ; // 'm' for Binary Memo field like Images, etc
{ "dtime", "T", 8, 0 }, ; // DateTime field
{ "createdt", "@", 8, 0 }, ; // TimeStamp when record is appended
{ "changedt", "=", 8, 0 } ; // TimeStamp when record is last modified
}
? oCn:CreateTableSQL( "testtable", aStru )
CREATE TABLE `testtable` (
`codgru` INT AUTO_INCREMENT PRIMARY KEY,
`nomgru` VARCHAR( 30 ),
`altera` DATE,
`check` BIT DEFAULT 0,
`Amount` DECIMAL( 11, 2 ),
`details` TEXT,
`photo` LONGBLOB,
`dtime` DATETIME,
`createdt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`changedt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
aStru := { ;
{ "code", "REFERENCES states( code )" }, ;
{ "details", "C", 80, 0, "utf8" }, ;
{ "quantity", "N", 8, 3 }, ;
{ "rate", "N", 3, 0 }, ;
{ "value = quantity * rate", "N", 12, 2 } }
? oCn:CreateTableSQL( "test", aStru, nil, "latin1" )
CREATE TABLE `test` (
`ID` INT AUTO_INCREMENT PRIMARY KEY,
`code` varchar(2) CHARACTER SET latin1 COLLATE latin1_general_ci,
`details` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
`quantity` DECIMAL( 9, 3 ),
`rate` SMALLINT,
`value` DECIMAL( 13, 2 ) AS ( quantity * rate ),
FOREIGN KEY ( `code` ) REFERENCES `states` ( `code` ) ON UPDATE CASCADE ON DELETE RESTRICT
) CHARACTER SET latin1 COLLATE latin1_general_ci
oCn := mysql_Connect( oAdoCon (or ) oTMySqlCon )
METHOD SetMultiStatement( lOnOf )
METHOD SetAutoCommit( lOnOff)
function MYSQL_TinyIntAsLogical( lOnOff )
function MYSQL_MaxPadLimit( nNew )
DATA lLog AS LOGICAL INIT .f. // logs every sql statement executed and result/error
DATA lLogErr AS LOGICAL INIT .f. // logs only failed sqls, Useful during development stage
DATA lShowErrors AS LOGICAL INIT .f. // displays all error message for failed sqls
DATA lShowMessages AS LOGICAL INIT .f. // displays all message for every sql execution
DATA cLogFile INIT cFileSetExt( ExeName(), "log" ) // default log file name can be changed by programmer
METHOD ShowError( [cTitle] ) // Shows last error/information
METHOD SetMsgLang( cLang )
METHOD GetLocale()
METHOD SetLocale( cLang )
CLASSDATA cClientInfo // --> Eg: 6.0.0
CLASSDATA cServerInfo // --> Eg: 5.7.15-log
DATA cServer, cUser, nFlags
DATA lOpen
DATA nPort INIT 3306
DATA lUnicode INIT FW_SetUnicode()
// Datas updated automatically after execution of every SQL
DATA nError INIT 0
DATA cError INIT ""
DATA cSqlInfo INIT ""
DATA cLastSQL INIT ""
DATA nExecSecs INIT 0 // Time taken to execute the SQL
// end
METHOD character_set_name // connection character_set
METHOD CurrentDB()
METHOD ListTables( [cMask] ) // --> aTableAndViewNames
METHOD ListIndexes( cTable ) // --> { { idxname, idxfields, idxtype }, ... }
METHOD ListDbs( [cMask] ) // --> aDataBases
METHOD ListBaseTables( [cMask], [cDB], [ lExt (.f.)] ) // --> aTableNames ( excluding Veiws )
// if lExt is true, --> { { tablename, tablecreateSql }, ... }
METHOD ListViews( [cMask], [cDB], [lExt (.f.)] ) // --> aViews
// if lExt is true --> { { viewname, viewdescription }, ... }
METHOD ListTriggers( [cTableMask], [cDB], [lExt (.f.)] ) // --> aTriggerNames of given table
// --> Array with full information and Trigger source
METHOD ListProcedures( [cMask], [cDB], [lExt(.f.)] ) // lExt = .t. includes procedure body
METHOD ListFunctions( [cMask], [cDB], [lExt(.f.)] ) // lExt = .t. includes function body
METHOD ListEngines( [lAll], [lShow] )
METHOD TableExists( cTableName, [db] ) // --> lExists
METHOD IsProcedure( cProc ) // --> lTrue
METHOD IsFunction( cFunc ) // --> lTrue
METHOD FKeyColumns( cTable, cDB ) // --> {{thistablecolumn,foreigndb,foreigntable,foreigncolumn}}
METHOD FKReferencedBy( cTable, cDB ) //-> {{ db.table}} referencing to this table
METHOD FKReferencedTables( cTable ) // --> All tables referenced by this table
METHOD FindRelation( parent, child ) // --> How the foreignkeys are related
METHOD GetVariables( [cMask] ) // --> session variables values
METHOD GetPrimaryFields( cTable, [cDB] ) //--> List of primary keys
METHOD GetUniqueFields( cTable, [cDB] ) // --> list of all unique keys including primary
METHOD GetAutoCommit()
METHOD InsertID() // --? Last inserted auto-inc ID
METHOD SelectDB( cDB )
METHOD CreateDB( cDB, cCharSet )
METHOD CreateTable( cTable, aStruct, lAddAutoInc, char_set )
METHOD CreateTableSQL( cTable, aStruct, lAddAutoInc, cCharSet )
METHOD DropTable( cTable )
METHOD RenameTable( cOld, cNew ) INLINE If( ::lOpen, ;
METHOD AddAutoInc( cTable, cCol )
METHOD MakePrimaryKey( cTable, cCol )
METHOD AddColumn( cTable, aColSpec )
METHOD AlterColumn( cTable, aColSpec )
METHOD RenameColumn( cTable, cOldName, cNewName )
METHOD SetAutoCommit( lOnOff)
METHOD Insert( cTable, acFields, aValues )
METHOD InsertSQL( cTable, acFields, aValues, acDuplicate )
METHOD Replace( cTable, acFields, aValues )
METHOD ReplaceSQL( cTable, aFields, aValues )
METHOD Update( cTable, aFields, aValues, cWhere )
// ADO Compatibility
METHOD BeginTrans INLINE ::BeginTransaction()
METHOD CommitTrans INLINE ::CommitTransaction()
METHOD RollBackTrans INLINE ::RollBack()
//
METHOD BeginTransaction()
METHOD CommitTransaction()
METHOD RollBack()
// Aliases
MESSAGE Query METHOD RowSet
MESSAGE SqlQuery METHOD Execute
//
METHOD QueryResult( cSql )
METHOD RowSet( cSql, [lShowError] )
METHOE RowSet( cSql, nRows, [lShowError] )
METHOD RowSet( cSql, aParams, [lShowError] )
METHOD Execute( cSql, aParams ) --> Nil / aResult / cResult
METHOD Call( cStoredFunction, [ uParam1, ..., uParamN ] ) // --> ReturnValue of Function
METHOD Call( cStoredProcedure, [ [@]uParam1, ..., [@]uParamN ] ) --> Nil or RowSet
// Using @uParamX can retrieve values of OUT params
// This facility is availble only here and not even in ADO
METHOD UCase( c ) INLINE ::QueryResult( "SELECT UCASE( '" + c + "' )" )
METHOD LCase( c ) INLINE ::QueryResult( "SELECT LCASE( '" + c + "' )" )
METHOD ValToSQL( uVal, [lEmptyAsDefault] )
METHOD ApplyParams( cSql, aParams, [lDbfSyntax] ) //--> cSqlWithParamsEmbedded
METHOD ParseTableName( cName, @cTable, @cDB ) //-> db.table
METHOD ExprnDBF( cFilterExp, aParams ) // cfiltercond, p1, p2, ... )
METHOD ExprnSQL ( cWhereExp, aParams )
function MYSQL_QuotedCol( cCol )
METHOD SaveToDBF( cSql, cDbf, [lForUpdate] ) --> nRows Saved. -1 in case of error
METHOD SqlToText( cSql ) --> Tab delimited Text suitable for pasting in Excel, Word, etc.
METHOD ImportFromDBF( cDbf, cTable, cColPrefix, nMultiRowSize, aFields, cAutoIncFld, cCharSet, lAddTS, bProgress ) --> lSuccess
METHOD UploadFromAlias( cTable, cSrcFieldList, cDstFieldList, nMultiRowSize, lUpdate ) --> lSuccess
METHOD BackUp( [atables], [dest], [bProgress], [nMaxRecs], [nMaxBuf] )
METHOD BackUpIndex( cBackUp, [lView] )
METHOD Restore( cFile, [aTables], [bProgress], [cNewDB] )
METHOD RestoreFromSqlDump( cFile, [bProgress] )
METHOD PivotArray( cTable, cRowFld, cColFld, cValFld, cAggrFunc )
METHOD PivotRS( cTable, cRowFld, cColFld, cValFld, cAggrFunc )
METHOD UpdateSummary( cMaster, cMasKey, acMasCols, ;
cTrnTable, cTrnKey, acTrnCols, ;
cTrnWhere, cOperator )
function MYSQL_UpdateSummarySQL( cMaster, cMasKey, acMasCols, ;
cTrnTable, cTrnKey, acTrnCols, ;
cTrnWhere, cOperator )
DATA bMeter
DATA Cargo
METHOD Close()
METHOD End() INLINE ::Close()
DESTRUCTION: When connection object is set to NIL and no references are left in memory, close method is automatically called and the object is destroyed.
? MYSQL_UpdateSummarySQL( ;
"customers", "ID", "number,qty,sales", ;
"sales", "custid", "count(*),quantity,amount" )
UPDATE `customers` m
LEFT OUTER JOIN
(
SELECT `custid`, count(*) AS t01, SUM( quantity ) AS t02, SUM( amount ) AS t03
FROM `sales`
GROUP BY `custid`
) t
ON m.ID = t.custid
SET
m.number = IFNULL( t.t01, 0 ),
m.qty = IFNULL( t.t02, 0 ),
m.sales = IFNULL( t.t03, 0 )
? MYSQL_UpdateSummarySQL( ;
"customers", "ID", "number,qty,sales", ;
"sales", "custid", "count(*),quantity,amount", ;
"saledate >= '2016-01-01'", "+" )
UPDATE `customers` m
LEFT OUTER JOIN
(
SELECT `custid`, count(*) AS t01, SUM( quantity ) AS t02, SUM( amount ) AS t03
FROM `sales`
WHERE saledate >= '2016-01-01'
GROUP BY `custid`
) t
ON m.ID = t.custid
SET
m.number = m.number + IFNULL( t.t01, 0 ),
m.qty = m.qty + IFNULL( t.t02, 0 ),
m.sales = m.sales + IFNULL( t.t03, 0 )
XBROWSER oCn:PivotArray( "pivotdata", "REGION", "PRODUCT", "sales" ) ;
TITLE "FWMARIADB: PIVOT TABLE"
Return to FiveWin for Harbour/xHarbour
Users browsing this forum: No registered users and 36 guests