FiveTech Support Forums

   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"