Updated documentation of Connection Object FWH 16.08
New and Additional Method for connection from Ado/TMySql Connection
Code: Select all
oCn := mysql_Connect( oAdoCon (or ) oTMySqlCon )
Main functions and commands for establishing connection are described in the first post of this thread.
SWITCHES and SETTINGS:
Code: Select all
METHOD SetMultiStatement( lOnOf )
METHOD SetAutoCommit( lOnOff)
function MYSQL_TinyIntAsLogical( lOnOff )
function MYSQL_MaxPadLimit( nNew )
DEBUGGING HELP
Code: Select all
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
All text is in the language set by method SetMsgLang()
LANGUAGE: Explained in full detail in the first post
Code: Select all
METHOD SetMsgLang( cLang )
METHOD GetLocale()
METHOD SetLocale( cLang )
INFORMATION DATAS/METHODS:
Please treat the datas as readonly and never modify the values from the program
Code: Select all
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
DDL Methods
Code: Select all
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 )
Methods CreateTable() and CreateTableSQL() are explained already in this post. To get the best results, it is recommended to use these methods instead of writing own sqls or depending on other GUI tools.
New in FWH 16.08:
METHOD CreateTableSQL( cExistingTable ) --> SQL for recreating the table. This sql is produced by MySql
DML Methods
Code: Select all
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 )
FWRowSets make saving updates and insertions to a table exremely easy and there is hardly any need for writing SQL statements. Even in the rare occassions when we need to write SQLs for INSERT,UPDATE,REPLACE we can use METHODS Insert(...), Update(...) or Replace(...) and if we are so interested in examine the SQL statements, we can use METHODS InsertSQL(), ReplaceSQL().
Insert() method is very efficient and can handle multiple row inserts as well as update on duplicates. Even experts should find these methods very useful.
TRANSACTION SUPPORT
Code: Select all
// ADO Compatibility
METHOD BeginTrans INLINE ::BeginTransaction()
METHOD CommitTrans INLINE ::CommitTransaction()
METHOD RollBackTrans INLINE ::RollBack()
//
METHOD BeginTransaction()
METHOD CommitTransaction()
METHOD RollBack()
EXECUTION:
Code: Select all
// 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
RowSet(..) and Call(...) methods will be discussed separately in other exclusive posts.
UTILITY METHODS & FUNCTIONS most used by other methods
Code: Select all
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 )
IMPORT & EXPORT
Code: Select all
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
These methods were explained in previous posts of this thread.
BACKUP & RESTORE
Code: Select all
METHOD BackUp( [atables], [dest], [bProgress], [nMaxRecs], [nMaxBuf] )
METHOD BackUpIndex( cBackUp, [lView] )
METHOD Restore( cFile, [aTables], [bProgress], [cNewDB] )
METHOD RestoreFromSqlDump( cFile, [bProgress] )
Detailed explanation can be found at
http://forums.fivetechsupport.com/viewt ... =3&t=32791
UTILTIES
Code: Select all
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 )
DATAS (Read/Write)
CLOSE CONNECTION
Code: Select all
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.
When the variable or all the variables referering to the connection objects go out of scope, the connection object is automatically closed and the object destroyed. It may be kept in mind that as long as atleast one RowSet still in memory, a reference to the connection object is still alive.
It is a good practice to explicitly close connection object with End() or Close() after closing all RowSet objects.