FWH 16.08 : Built-in MySql/MariaDB functionality (Updated )
Posted: Fri Jul 01, 2016 9:32 am
FWH now provides inbuilt connectivity to MariaDB/MySql server without using any 3rd party libs. Works with both MariaDB and MySql servers. The functionality is available with bcc7 xHarour and Harbour, bcc7 64 bit with Harbour and MSVC 32 and 64 bits with Harbour. Yet to test with xhb.com and gcc.
The application needs to be linked with either libmariadb.lib or libmysql.lib to be used with libmariadb.dll or libmysql.dll. It may be noted that 64 bit versions of the lib and dll are to be used with 64 bit applications.
Example:
Connecting to Server:
Optional port number can also be specified as "server:port"
In case of failure, oCn is set to nil.
Unicode Support:
If FW_SetUnicode() is set to .T., then the connection is opened with utf8 character set and if not with the default character set of the Server (usually latin1)
To get the best, please always have primary key for every table and include the primary key field(s) in the sql while reading.
Connection Object:
Note: Instantiation of the connection object may be done only with the above command or function.
New in FWH 16.08:
If we already have an active connection created with ADO or TMySql we can derive FW connection from that connection.
DATAs
ReadOnly
cClientInfo, cServerInfo
cServer, cuser, nPort, nFlags, cDB
nError // Last error ( 0 = no error)
cError // Last error in the selected language
cSqlInfo // Last sql execution informatuon in selected language
lOpen // If connection is open or not.
ReadWrite:
lLog DEFAULT .f. // If .t. all sql statements and results are logged to <exename>.log
Cargo // read-write by user
METHODS:
Connect() // This is already executed automatically. Can be used to reconnect by
oCn:Close(), oCn:Connect() --> .t. / .f.
Language Support:
METHOD SetMsgLang( <cLang> ) // DATA cError is set in this language
This method is automatically executed while connecting. The default language is en_US.
If FWSetLanguage() is already set or if HB_LangSelect() is one of es,fr,pt,de or it, then the corresponding language is automatically set.
In case the programmer wants any other language he may call this method explicitly.
Eg: oCn:SetMsgLang( "ja_JP" )
Example:
Languages supported by MySql server are :
By default English, but they can also be displayed in any of several other languages: Czech, Danish, Dutch, Estonian, French, German, Greek, Hungarian, Italian, Japanese, Korean, Norwegian, Norwegian-ny, Polish, Portuguese, Romanian, Russian, Slovak, Spanish, or Swedish.
METHOD SetLocale( <cLang> ) // display week names and month names in the local language.
This method is automatically executed for the 6 above languages. For other languages the programmer needs to call this method explicitly. Supported languages can be found at:
http://dev.mysql.com/doc/refman/5.7/en/ ... pport.html
Example:
After changing Locale
Other User Settings:
MYSQL_TinyIntAsLogical( lOnOff ) --> lPrevSetting // Default False
By default, BIT fields are used for Logical values.
When set ON, TinyInt is used for Logical values instead.
This setting is used at the time of Creation and Opening of a Table. This setting can be toggled suitably while reading tables created with different settings. There can be different tables opened simultaneously with different settings.
Other methods:
METHOD ShowError( cTitle ) // displayed in selected language
METHOD SetMultiStatement( lOnOf )
METHOD max_allowed_packet_MB( nNewVal )
METHOD GetAutoCommit()
METHOD SetAutoCommit( lOnOff)
Note: Multiple row inserts are speeded up autocommit is disabled before insert and enabled after insert.
METHOD Ping() --> lSuccess
METHOD BeginTransaction()
METHOD CommitTransaction()
METHOD RollBack()
Note: These methods are aliased to BeginTrans, CommitTrans and RollbackTrans
METHOD ListDbs( cMask )
METHOD CreateDB( cDB, [cCharSet] )
METHOD SelectDB( cDB )
METHOD CurrentDB()
METHOD ListTables( cMask )
METHOD TableExists( cTable ) --> lExists
METHOD CreateTable( cTable, aStruct, [lAddAutoInc], [char-set] ) --> lSuccess
METHOD DropTable( cTable ) --> lSuccess
METHOD QueryResult( cSql ) --> nil / value / array
METHOD RowSet( cSql(or)cTableName, [aParams], [lShowError] ) --> oRowSet or nil if fail
METHOD Execute( cSql ) --> nil / val / array
METHOD Call( cStoredProc, params....... ) --? nil / rowset
METHOD PivotArray( cTable, cRowFld, cColFld, cValFld, [cAggrFunc] )
METHOD Insert( cTable, caFields, aValues, [lUpdateIgnore] ) --> nInserted
METHOD Update( cTable, aFields, aValues, cWhere ) --> nUpdated
METHOD InsertID() --> Last AutoInc ID (not needed in app programs. )
METHOD ImportFromDBF( cDbf, cTable, cColPrefix, nMultiRowSize, aFields, cAutoIncFld )
METHOD SaveToDBF( tablename, [lForUpdate] )
METHOD UploadFromAlias( cTable, cSrcFieldList, cDstFieldList, [lUpdateIgnore] )
METHOD ValToSQL( uVal )
METHOD FieldsAndValsToSQL( aFields, aVals, lWhere )
METHOD Close() or End()
Important Methods Explained:
MEHOD CreateTable(cTable, aStruct, [lAddAutoInc], [char-set] ) --> lSuccess
Params:
1st Param: cTable : Table name
2nd Param: aStruct: This is normally the familiar DBF structure, with some extensions where required.
a) To create a binary memo field (BLOB) for holding binary data, specify Type as "m" ( small m ) instead of 'M'. This column can be used for storing binary data like images.
b) To create a computed column ( virtual ) specify the field name as formula like:
{ "amount = qty * price", 'N', 12, 2 }, ...
This column will be created as
amount DECIMAL( 12, 2 ) AS ( qty * price ),
c) To spcify primary key add one more element to the array with "PRI". Eg:
{ "custid", 'C', 5, 0, "PRI" }
d) Specify character set in extended element like
{ "custname", 'C', 30, 0, "utf8" }
char sets can be specified as "latin1", "utf8", "utf8mb4" or as full collating spec
e) Where a diffent specification is requied, same can be written as text in 2nd element of array eg:
{ "_" "ENUM( 'male', 'female' )" }
3rd Param: Optional lAddAutoInc ( default .T. )
It is very strongly recommended that every table should have one unique primary key to make stable application. While you may decide which column is to be primary key, good practice is to have a numeric column, hidden from the user as primary key, which will not be altered during life of the database.
One easy way to create and maintain such a primary key is to have AutoInc column. By default, the method prepends an Auto Inc primary key with name "ID".
This can be disabled by specifying .F.
4th param : optional character set for the table.
Note:
During development of software it is quite useful to use MYSQL_CreateTableSQL( .. ) with the same params. This function returns the Sql Statement that can be examined by the programmer before finailizing the structure
METHOD ImportFromDBF( cDbf, cTable, cColPrefix, nMultiRowSize, aFields, cAutoIncFld )
/*
* cDbf : dbfname or alias name or default Alias()
* cTable: defaults to Lower( cdbf file name )
* cColPrefix: if specified this value is prefixed to each dbf field name
* nMultiRowSize: default 20 rows insert at a time:
* aFields: Can be part of dbfstruct() or comma separated list of fields
* - if afields is like dbstruct, this is used to create table
* cAutoIncfld: default: logical .t. : insert autoinc field
* if .f. do not insert autoinc field
* if char insert autoinc field with this name
*/
Sample for test:
METHOD Insert( cTable, caFields, aValues, [lIgnoreOrUpdate] ) --> Execute result
and
METHOD InsertSQL( cTable, caFields, aValues, [lIgnoreOrUpdate] ) --> cSql
Param caFields: can be array of field names or comma delimited list of field names
Param aValues: Array of values corresponding to the fields. A Single dim array for inserting single row and multi-dim array for inserting multiple rows in one operation.
The values can be constants or variables or expressions,
Examples:
oCn:Insert( "emp", "name, age, dob", { cName, nAge, dDateofBirth } )
oCn:Insert( "emp", "name,photo", { "John", MemoRead( "john.jpg" ) } )
oCn:Insert( "emp", "name,age", { { "John", 32 }, ;
{ "James",40 } } ) // inserts 2 rows
The method handes all necessary conversions of the values to suit the Sql syntax.
Param: Optional: lIgnoreOrUpdate:
Default: NIL: Inserting duplicate values in Primary/Unique fields results in error and the execution fails.
Logical FALSE: If .F. is sprecified as 3rd param, "INSERT IGNORE " sql satement is generated and execution continues ignoring duplicate insertions.
Logical TRUE: If .T. is specified as 3rd param, the function verifies if the fields list contains any primary or unique fields and in that case adds the clause "ON DUPLICATE KEY UPDATE" suitably.
Example:
METHOD Update( cTable, caFields, aValues, caWhere )
Param caFields: As in Insert
Param aValues: As in Insert but only single dim array
Param acWhere: Character exprn: Example "ID = 340"
or a multi-dim array with field-value pairs
Eg: { { "AGE", 40 }, { "DEPT", "AB" } }
METHOD PivotArray( cTable, cRowFld, cColFld, cValFld, cAggrFunc )
Param cTable can be a table name or any complex Sql statement
Returns a Pivot array formatted for diplay in xbrowse. Functions similar to the ADO function.
METHOD Execute( cSqlStatement, [params], [lShowError] ) --> uResult
(Can also use oCn:SqlQuery(...) )
uResult can be
a) nil if there is no result or if error occured.
In case of error, oCn:nError and oCn:cError provide teh error information
Optionally setting lShowError displays ther error or result of sql statement,
b) Numeric: In case of insert, update, etc indicating the affected rows
c) If the result is a result-set, it is returned as Multi-Dim array
METHOD QueryResult( cSql ) --> scalar result
Similar to Execute() but greatly simplifies the result conversion.
Eg:
oCn:QueryResult( "SELECT COUNT(*) FROM CUSTOMER" ) --> 500
oCn:QueryResult( "select sum(amount),sum(qty) from bills" ) --> { 40000, 40 }
METHOD Call( "storedproc", params,...)
In the next release it will be possible to use params by reference to get back values of OUT params.
METHOD SaveToDBF( cTable/cSql, cDbf, [lForUpdate] )
Saves the contents of a table or result of an sql statement to a DBF.
METHOD UploadFromAlias( [cTable], [cSrcFieldsList], [cDstFieldsList], [lIgnoreOrUpdate] )
These two methods can also be used to save a local copy, edit and later upload the changes and additions to Server.
METHOD RowSet( cTableName or sql-statement ) --> RowSet object
( Can also use oCn:Query(...)
RowSet Object
Usage:
We can open a rowset with Sqlstatement with parameters.
Eg:
Navigation:
Supports all methods of TDatabase like GoTop,GoBottom,GoTo,Skip,Eof,Bof,LastRec,RecCount,KeyCount,KeyGoTo, etc
Also supports aliased methods compatible with ADO
MoveFirst,MoveLast,Move,BookMark,AbsolutePosition, etc
DATA oChild // Optional child rowset created with oRs:AddChild(...)
Sorting the rowset in memory:
oRs:SetOrder( fieldname, ignored, [lDesc] ) --> cPrevSort
oRs:Sort := <fieldname> [ASC/DESC]
? oRs:Sort
Filtering the rowset:
oRs:SetFilter( filterexpression_with_dbfsyntax )
oRs:Filter := <newfilter>
Both sorting and filtering are done for the rowset only without reading again from the server.
METHOD Requery()
Executes the original Sql statement and reads entire data again from the Server. Currently effective sort order and filters are maintained and tries to reposition to the same record.
METHOD ReSync( [aParams] )
Reads only the current record from the server and refreshes the contents of the current row. This is possible only when the RowSet contains primary key.
If Sql with parameters was used to open the RowSet, we may either provide new params or ommit while Requerying.
Eg:
This is also useful to capture any changes made by other users on the network to the current row, without reading the entire table/query.
METHOD Save()
Saves any changes the current record or record being appended and ReSyncs contents of the current record from the server. Any changes in the calculated or dependant columns are also read back and current row refreshed.
After refreshing, if the value of the sorted column (even derived/related) is modified, the rowset is resorted and the row is placed in its correct sorted position.
Note: After modifying the contents of a row / appended row, any navigation automatically calls Save().
Example:
Appending Rows:
oRs:Append()
or oRs:AddNew()
Creates a new blank record. We can edit the fields in the usual way and call Save() or navigate to append the record
Example:
oRs:Append( cafields, aValues ) --> lSuccess
Appends a new record and refershes the rowset. This includes automatic save.
Example:
Adds a new row to the database, re-reads the newly appended row and refreshes the contents of the rowset and repositions the record pointer to the new record.
METHOD Update( aFields, aValues ) --> lSuccess
Modifies the values of the fields in aFields and writes the modifications to the database and resyncs and refreshes the current row.
METHOD Cancel() --> Cancels the pending updates/append
METHOD Delete() --> lSuccess
METHOD Zap() --> lSuccess (Better avoid calling this)
METHOD GetRows() --> aData
Reads the values of the records into an array. Respects current filter and sort order.
Access/Assign of values of a field:
ChildRowSet
oRs := oCn:RowSet( "states" )
lSuccess := oRs:AddChild( "select * from customers where state = %1", { "code" } )
// later
oRs:ReSyncChild()
Field Object and Fields() method and Fields Collection:
Compatible with ADO.
oRs:Fields:Count
? oRs:Fields( "name" ):Value
oRs:Fields( "salary" ):Value := 20000
? oRs:Fields( n ):Value // numbers are 0 based in this usage
oField := oRs:Fields( fieldname ) --> Field Object
Field Object DATAs:
DATA name, type, len, dec, cType
DATA len2
DATA sqltype, flags, table, org_name, org_table, cDb
DATA default, list
DATA lReadOnly INIT .f.
DATA lAutoInc INIT .f.
DATA lPrimary INIT .f.
DATA lUnique INIT .f.
DATA lKey INIT .f.
DATA lNoNull INIT .f.
DATA lBinary INIT .f.
DATA Collation INIT ""
Notes on Editing/Modifying data of a RowSet:
For successful edit and save, it is very important that the main table in the sql (query) should contain primary key(s) and all the primary key(s) are also read by the sql (query). Once this condition is satisfied RowSet object provides maximum flexibilty for edit/save.
It is possible that results or some sql statements can not be modified or edited. Example: "select state, count(*) as num from customer group by state"
In such cases, oRs:lReadOnly is set to .T.
Also, columns which are computed or derived from joined tables are also flaged as readonly.
It is obvious that single tables can be edited/modified easily. In addition even the following types of queries are editable.
SELECT id AS CustID, first AS FirstName, last AS SurName from customer.
Though the field names used in the rowset are CustID, FirstName, SurName any modificatiosn to these fields are correctly saved to the corresponding fields first,last in the original database.
SELECT c.id AS CustID, c.first AS Name, c.state AS St, s.name AS StateName FROM customer c left outer join states s ORDER BY Name
In this case, fields Name, St are editable and can be read back with the changed StateName.
Note: Added modifications and improvements in blue color
The application needs to be linked with either libmariadb.lib or libmysql.lib to be used with libmariadb.dll or libmysql.dll. It may be noted that 64 bit versions of the lib and dll are to be used with 64 bit applications.
Example:
Code: Select all
FWCONNECT oCn HOST "localhost" USER "root" PASSWORD "secret" DB "fwh"
if oCn == nil
? "Connect Fail"
return nil
else
oRs := oCn:RowSet( "customer" )
XBROWSER oRs FASTEDIT
oCn:Close()
endif
Code: Select all
FWCONNECT oCn HOST cHost USER cUser PASSWORD cPwd [DB cdb] [PORT nPort] [FLAGS nFlags]
or oCn := mysql_Connect( cHost, [cDB], cUser, cPwd, [nPort], [nFlags] )
or oCn := mysql_Connect( { server, database, user, password, port, flags } ) // database,port,flags are optional
or oCn := mysql_Connect( "server,database,user,password" )
In case of failure, oCn is set to nil.
Unicode Support:
If FW_SetUnicode() is set to .T., then the connection is opened with utf8 character set and if not with the default character set of the Server (usually latin1)
To get the best, please always have primary key for every table and include the primary key field(s) in the sql while reading.
Connection Object:
Note: Instantiation of the connection object may be done only with the above command or function.
New in FWH 16.08:
If we already have an active connection created with ADO or TMySql we can derive FW connection from that connection.
Code: Select all
oCn := maria_Connect( oAdoCn (or) oTMySqlCon )ReadOnly
cClientInfo, cServerInfo
cServer, cuser, nPort, nFlags, cDB
nError // Last error ( 0 = no error)
cError // Last error in the selected language
cSqlInfo // Last sql execution informatuon in selected language
lOpen // If connection is open or not.
ReadWrite:
lLog DEFAULT .f. // If .t. all sql statements and results are logged to <exename>.log
Cargo // read-write by user
METHODS:
Connect() // This is already executed automatically. Can be used to reconnect by
oCn:Close(), oCn:Connect() --> .t. / .f.
Language Support:
METHOD SetMsgLang( <cLang> ) // DATA cError is set in this language
This method is automatically executed while connecting. The default language is en_US.
If FWSetLanguage() is already set or if HB_LangSelect() is one of es,fr,pt,de or it, then the corresponding language is automatically set.
In case the programmer wants any other language he may call this method explicitly.
Eg: oCn:SetMsgLang( "ja_JP" )
Example:
Code: Select all
ocn:SetMsgLang( "ja_JP" ) // Japanese
oCn:Execute( "wrong sql" )
oCn:ShowError()
Languages supported by MySql server are :
By default English, but they can also be displayed in any of several other languages: Czech, Danish, Dutch, Estonian, French, German, Greek, Hungarian, Italian, Japanese, Korean, Norwegian, Norwegian-ny, Polish, Portuguese, Romanian, Russian, Slovak, Spanish, or Swedish.
METHOD SetLocale( <cLang> ) // display week names and month names in the local language.
This method is automatically executed for the 6 above languages. For other languages the programmer needs to call this method explicitly. Supported languages can be found at:
http://dev.mysql.com/doc/refman/5.7/en/ ... pport.html
Example:
Code: Select all
// Application built with Spanish codepage
cSql := "SELECT First, Age, DATE_FORMAT( HIREDATE, '%d %M %Y %W' ) AS HireDate FROM customer"
oRs := oCn:RowSet( cSql, .t. )
XBROWSER oRs AUTOFIT
oCn:SetLocale( "zh_TW" ) // Chinese
cSql := "SELECT First, Age, DATE_FORMAT( HIREDATE, '%d %M %Y %W' ) AS HireDate FROM customer"
oRs := oCn:RowSet( cSql, .t. )
XBROWSER oRs AUTOFIT
After changing Locale
Other User Settings:
MYSQL_TinyIntAsLogical( lOnOff ) --> lPrevSetting // Default False
By default, BIT fields are used for Logical values.
When set ON, TinyInt is used for Logical values instead.
This setting is used at the time of Creation and Opening of a Table. This setting can be toggled suitably while reading tables created with different settings. There can be different tables opened simultaneously with different settings.
Other methods:
METHOD ShowError( cTitle ) // displayed in selected language
METHOD SetMultiStatement( lOnOf )
METHOD max_allowed_packet_MB( nNewVal )
METHOD GetAutoCommit()
METHOD SetAutoCommit( lOnOff)
Note: Multiple row inserts are speeded up autocommit is disabled before insert and enabled after insert.
METHOD Ping() --> lSuccess
METHOD BeginTransaction()
METHOD CommitTransaction()
METHOD RollBack()
Note: These methods are aliased to BeginTrans, CommitTrans and RollbackTrans
METHOD ListDbs( cMask )
METHOD CreateDB( cDB, [cCharSet] )
METHOD SelectDB( cDB )
METHOD CurrentDB()
METHOD ListTables( cMask )
METHOD TableExists( cTable ) --> lExists
METHOD CreateTable( cTable, aStruct, [lAddAutoInc], [char-set] ) --> lSuccess
METHOD DropTable( cTable ) --> lSuccess
METHOD QueryResult( cSql ) --> nil / value / array
METHOD RowSet( cSql(or)cTableName, [aParams], [lShowError] ) --> oRowSet or nil if fail
METHOD Execute( cSql ) --> nil / val / array
METHOD Call( cStoredProc, params....... ) --? nil / rowset
METHOD PivotArray( cTable, cRowFld, cColFld, cValFld, [cAggrFunc] )
METHOD Insert( cTable, caFields, aValues, [lUpdateIgnore] ) --> nInserted
METHOD Update( cTable, aFields, aValues, cWhere ) --> nUpdated
METHOD InsertID() --> Last AutoInc ID (not needed in app programs. )
METHOD ImportFromDBF( cDbf, cTable, cColPrefix, nMultiRowSize, aFields, cAutoIncFld )
METHOD SaveToDBF( tablename, [lForUpdate] )
METHOD UploadFromAlias( cTable, cSrcFieldList, cDstFieldList, [lUpdateIgnore] )
METHOD ValToSQL( uVal )
METHOD FieldsAndValsToSQL( aFields, aVals, lWhere )
METHOD Close() or End()
Important Methods Explained:
MEHOD CreateTable(cTable, aStruct, [lAddAutoInc], [char-set] ) --> lSuccess
Params:
1st Param: cTable : Table name
2nd Param: aStruct: This is normally the familiar DBF structure, with some extensions where required.
a) To create a binary memo field (BLOB) for holding binary data, specify Type as "m" ( small m ) instead of 'M'. This column can be used for storing binary data like images.
b) To create a computed column ( virtual ) specify the field name as formula like:
{ "amount = qty * price", 'N', 12, 2 }, ...
This column will be created as
amount DECIMAL( 12, 2 ) AS ( qty * price ),
c) To spcify primary key add one more element to the array with "PRI". Eg:
{ "custid", 'C', 5, 0, "PRI" }
d) Specify character set in extended element like
{ "custname", 'C', 30, 0, "utf8" }
char sets can be specified as "latin1", "utf8", "utf8mb4" or as full collating spec
e) Where a diffent specification is requied, same can be written as text in 2nd element of array eg:
{ "_" "ENUM( 'male', 'female' )" }
3rd Param: Optional lAddAutoInc ( default .T. )
It is very strongly recommended that every table should have one unique primary key to make stable application. While you may decide which column is to be primary key, good practice is to have a numeric column, hidden from the user as primary key, which will not be altered during life of the database.
One easy way to create and maintain such a primary key is to have AutoInc column. By default, the method prepends an Auto Inc primary key with name "ID".
This can be disabled by specifying .F.
4th param : optional character set for the table.
Note:
During development of software it is quite useful to use MYSQL_CreateTableSQL( .. ) with the same params. This function returns the Sql Statement that can be examined by the programmer before finailizing the structure
METHOD ImportFromDBF( cDbf, cTable, cColPrefix, nMultiRowSize, aFields, cAutoIncFld )
/*
* cDbf : dbfname or alias name or default Alias()
* cTable: defaults to Lower( cdbf file name )
* cColPrefix: if specified this value is prefixed to each dbf field name
* nMultiRowSize: default 20 rows insert at a time:
* aFields: Can be part of dbfstruct() or comma separated list of fields
* - if afields is like dbstruct, this is used to create table
* cAutoIncfld: default: logical .t. : insert autoinc field
* if .f. do not insert autoinc field
* if char insert autoinc field with this name
*/
Sample for test:
Code: Select all
oCn:ImportFromDBF( "c:\fwh\samples\states.dbf" )
XBROWSER oCn:RowSet( "states" )
and
METHOD InsertSQL( cTable, caFields, aValues, [lIgnoreOrUpdate] ) --> cSql
Param caFields: can be array of field names or comma delimited list of field names
Param aValues: Array of values corresponding to the fields. A Single dim array for inserting single row and multi-dim array for inserting multiple rows in one operation.
The values can be constants or variables or expressions,
Examples:
oCn:Insert( "emp", "name, age, dob", { cName, nAge, dDateofBirth } )
oCn:Insert( "emp", "name,photo", { "John", MemoRead( "john.jpg" ) } )
oCn:Insert( "emp", "name,age", { { "John", 32 }, ;
{ "James",40 } } ) // inserts 2 rows
The method handes all necessary conversions of the values to suit the Sql syntax.
Param: Optional: lIgnoreOrUpdate:
Default: NIL: Inserting duplicate values in Primary/Unique fields results in error and the execution fails.
Logical FALSE: If .F. is sprecified as 3rd param, "INSERT IGNORE " sql satement is generated and execution continues ignoring duplicate insertions.
Logical TRUE: If .T. is specified as 3rd param, the function verifies if the fields list contains any primary or unique fields and in that case adds the clause "ON DUPLICATE KEY UPDATE" suitably.
Example:
Code: Select all
? oCn:InsertSQL( "states", "id,code,name", { ;
{ 5, 'BC', "British Columbia" }, ;
{ 0, "ON", "Ontario" } }, .T. )
-->
"INSERT INTO `states`
( `id`,`code`,`name` )
VALUES
( 5,'BC','British Columbia' ),
( 0,'ON','Ontario' )
ON DUPLICATE KEY UPDATE
`code` = VALUES ( `code` ),
`name` = VALUES ( `name` )"
Param caFields: As in Insert
Param aValues: As in Insert but only single dim array
Param acWhere: Character exprn: Example "ID = 340"
or a multi-dim array with field-value pairs
Eg: { { "AGE", 40 }, { "DEPT", "AB" } }
METHOD PivotArray( cTable, cRowFld, cColFld, cValFld, cAggrFunc )
Param cTable can be a table name or any complex Sql statement
Returns a Pivot array formatted for diplay in xbrowse. Functions similar to the ADO function.
METHOD Execute( cSqlStatement, [params], [lShowError] ) --> uResult
(Can also use oCn:SqlQuery(...) )
uResult can be
a) nil if there is no result or if error occured.
In case of error, oCn:nError and oCn:cError provide teh error information
Optionally setting lShowError displays ther error or result of sql statement,
b) Numeric: In case of insert, update, etc indicating the affected rows
c) If the result is a result-set, it is returned as Multi-Dim array
METHOD QueryResult( cSql ) --> scalar result
Similar to Execute() but greatly simplifies the result conversion.
Eg:
oCn:QueryResult( "SELECT COUNT(*) FROM CUSTOMER" ) --> 500
oCn:QueryResult( "select sum(amount),sum(qty) from bills" ) --> { 40000, 40 }
METHOD Call( "storedproc", params,...)
In the next release it will be possible to use params by reference to get back values of OUT params.
METHOD SaveToDBF( cTable/cSql, cDbf, [lForUpdate] )
Saves the contents of a table or result of an sql statement to a DBF.
METHOD UploadFromAlias( [cTable], [cSrcFieldsList], [cDstFieldsList], [lIgnoreOrUpdate] )
These two methods can also be used to save a local copy, edit and later upload the changes and additions to Server.
METHOD RowSet( cTableName or sql-statement ) --> RowSet object
( Can also use oCn:Query(...)
RowSet Object
Usage:
Code: Select all
oRs := oCn:RowSet( cTableNameOrcSqlStatement, [color=#0040FF][aParams],[/color] [lShowError] )
or
oQry := oCn:Query( cTableNameOrcSqlStatement, [color=#0040FF][aParams][/color], [lShowError] )
Eg:
Code: Select all
oRs := oCn:RowSet( "select * from customer where `state` = &1", { "WA" } )Supports all methods of TDatabase like GoTop,GoBottom,GoTo,Skip,Eof,Bof,LastRec,RecCount,KeyCount,KeyGoTo, etc
Also supports aliased methods compatible with ADO
MoveFirst,MoveLast,Move,BookMark,AbsolutePosition, etc
DATA oChild // Optional child rowset created with oRs:AddChild(...)
Sorting the rowset in memory:
oRs:SetOrder( fieldname, ignored, [lDesc] ) --> cPrevSort
oRs:Sort := <fieldname> [ASC/DESC]
? oRs:Sort
Filtering the rowset:
oRs:SetFilter( filterexpression_with_dbfsyntax )
oRs:Filter := <newfilter>
Both sorting and filtering are done for the rowset only without reading again from the server.
METHOD Requery()
Executes the original Sql statement and reads entire data again from the Server. Currently effective sort order and filters are maintained and tries to reposition to the same record.
METHOD ReSync( [aParams] )
Reads only the current record from the server and refreshes the contents of the current row. This is possible only when the RowSet contains primary key.
If Sql with parameters was used to open the RowSet, we may either provide new params or ommit while Requerying.
Eg:
Code: Select all
oRs := oCn:RowSet( "select * from customers where state = &1", { "WA" } )
XBROWSER oRs
oRs:ReQuery( { "NY" }
XBROWSWER oRsMETHOD Save()
Saves any changes the current record or record being appended and ReSyncs contents of the current record from the server. Any changes in the calculated or dependant columns are also read back and current row refreshed.
After refreshing, if the value of the sorted column (even derived/related) is modified, the rowset is resorted and the row is placed in its correct sorted position.
Note: After modifying the contents of a row / appended row, any navigation automatically calls Save().
Example:
Code: Select all
oRs:Salary := 20000
oRs:Save()
//or
oRs:Skip() // saves and skips
oRs:Append()
or oRs:AddNew()
Creates a new blank record. We can edit the fields in the usual way and call Save() or navigate to append the record
Example:
Code: Select all
oRs:Append()
oRs:Name := "Andrew"
oRs:Salary := 20000
oRs:Save() // commits the new record and refreshes the rowset
Appends a new record and refershes the rowset. This includes automatic save.
Example:
Code: Select all
oRs:Append( { "name", "salary" }, { "Anderson", 30000 } )
METHOD Update( aFields, aValues ) --> lSuccess
Modifies the values of the fields in aFields and writes the modifications to the database and resyncs and refreshes the current row.
METHOD Cancel() --> Cancels the pending updates/append
METHOD Delete() --> lSuccess
METHOD Zap() --> lSuccess (Better avoid calling this)
METHOD GetRows() --> aData
Reads the values of the records into an array. Respects current filter and sort order.
Access/Assign of values of a field:
Code: Select all
? oRs:<fieldname> // may fail if fieldname conflicts with object's data/method
? oRs:FieldGet( fieldnuber or fieldname )
//FieldGet also can be used for simple expressions:
? oRs:FieldGet( "salary * 20" )
? oRs:Fields( "salary" ):Value
oRs:<fieldname> := <newValue>
oRs:FieldPut( fieldnum or fieldname, newvalue ) // numbers start with 1
oRs:Fields( fieldname ):Value := <newvalue>
oRs:Fields( num ):Value := <newvalue> // numbers start with 0 like in ADO
oRs := oCn:RowSet( "states" )
lSuccess := oRs:AddChild( "select * from customers where state = %1", { "code" } )
// later
oRs:ReSyncChild()
Field Object and Fields() method and Fields Collection:
Compatible with ADO.
oRs:Fields:Count
? oRs:Fields( "name" ):Value
oRs:Fields( "salary" ):Value := 20000
? oRs:Fields( n ):Value // numbers are 0 based in this usage
oField := oRs:Fields( fieldname ) --> Field Object
Field Object DATAs:
DATA name, type, len, dec, cType
DATA len2
DATA sqltype, flags, table, org_name, org_table, cDb
DATA default, list
DATA lReadOnly INIT .f.
DATA lAutoInc INIT .f.
DATA lPrimary INIT .f.
DATA lUnique INIT .f.
DATA lKey INIT .f.
DATA lNoNull INIT .f.
DATA lBinary INIT .f.
DATA Collation INIT ""
Notes on Editing/Modifying data of a RowSet:
For successful edit and save, it is very important that the main table in the sql (query) should contain primary key(s) and all the primary key(s) are also read by the sql (query). Once this condition is satisfied RowSet object provides maximum flexibilty for edit/save.
It is possible that results or some sql statements can not be modified or edited. Example: "select state, count(*) as num from customer group by state"
In such cases, oRs:lReadOnly is set to .T.
Also, columns which are computed or derived from joined tables are also flaged as readonly.
It is obvious that single tables can be edited/modified easily. In addition even the following types of queries are editable.
SELECT id AS CustID, first AS FirstName, last AS SurName from customer.
Though the field names used in the rowset are CustID, FirstName, SurName any modificatiosn to these fields are correctly saved to the corresponding fields first,last in the original database.
SELECT c.id AS CustID, c.first AS Name, c.state AS St, s.name AS StateName FROM customer c left outer join states s ORDER BY Name
In this case, fields Name, St are editable and can be read back with the changed StateName.
Note: Added modifications and improvements in blue color
