FWH 16.08 : Built-in MySql/MariaDB functionality (Updated )

[nageswaragunupudi]

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:

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
 

Connecting to Server:

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" )
 

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.

Code: Select all

oCn := maria_Connect( oAdoCn (or) oTMySqlCon )

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:

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" )
 

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:

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` )"
 

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:

Code: Select all

oRs  := oCn:RowSet( cTableNameOrcSqlStatement, [color=#0040FF][aParams],[/color] [lShowError] )
or
oQry := oCn:Query( cTableNameOrcSqlStatement, [color=#0040FF][aParams][/color], [lShowError] )
 

We can open a rowset with Sqlstatement with parameters.
Eg:

Code: Select all

oRs := oCn:RowSet( "select * from customer where `state` = &1", { "WA" } )

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:

Code: Select all

oRs := oCn:RowSet( "select * from customers where state = &1", { "WA" } )
XBROWSER oRs
oRs:ReQuery( { "NY" } 
XBROWSWER oRs

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:

Code: Select all

oRs:Salary := 20000
oRs:Save()
//or
oRs:Skip()  // saves and skips
 

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:

Code: Select all

oRs:Append()
oRs:Name := "Andrew"
oRs:Salary := 20000
oRs:Save() // commits the new record and refreshes the rowset
 

oRs:Append( cafields, aValues ) --> lSuccess
Appends a new record and refershes the rowset. This includes automatic save.

Example:

Code: Select all

oRs:Append( { "name", "salary" }, { "Anderson", 30000 } )
 

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:

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
 

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

[vilian]

WOW ! It is great !!

[Kleyber]

Very good!!! Fantastic job, Nagesh!!!

[vilian]

Mr Rao,

I did not see functions to do Backup/Restore in the list of functions. Is it possible ? In tDolphin this already exist, but i made some modifications to work better. If you want i can send to you.

[nageswaragunupudi]

Mr Rao,

I did not see functions to do Backup/Restore in the list of functions. Is it possible ? In tDolphin this already exist, but i made some modifications to work better. If you want i can send to you.

Thanks.
Please send to nageswaragunupudi@gmail.com

[richard-service]

Mr.Rao

Nice job~

[jose_murugosa]

It's a great job, something extraordinary good for fivewin and for programmers, I will be enthusiastic waiting for next fivewin versions!!!!

Thanks a lot for your support to fivewin


+10000000

[nageswaragunupudi]

Some clarifications about usage of 3rd party libs

FWH applications can still use any 3rd party lib like TMySql or Dolphin as earlier. The built-in functionality does not conflict such usage in any manner.

It is also possible to use both a 3rd party lib and also FWH functions to open connections to same or different servers and even open the same table at the same time in the same application without any conflicts.

[Carles]

Nages,

fantastic job, congratulations !!!

[lucasdebeltran]

Dear Mr. Nages,

Does it support embedded databases for local only applications?.

Thank you.

[nageswaragunupudi]

Mr Lucas

Not provided in this release. Will be provided before next release.

[nageswaragunupudi]

Building MySql application

If your application is already using any 3rd party lib, it already linked with libmysql.lib and libmysql.dll is in the exe path. In such cases there is nothing to be done addtionally. You will have the advantage of optionally using FWH mysql functions also either in addition or in substitution.

Other cases:

1) Using bcc7 with xHarbour or Harbour:

a) Add to your link script either libmysql.lib or libmariadb.lib. (both are available in fwh\lib folder
b) Copy libmysql.dll or libmariadb.dll, depending on which lib is linked, to the exe folder from fwh\dll folder

2) Using MSVC and Harbour 32 bits:

a) Link with libmysql32.lib or libmariadb32.lib located in fwh\lib folder.
b) Same as 1(b).

3) Using MSVC and Harbour 64 bits

a) Link with libmysql64.lib or libmariadb64.lib located in fwh\lib folder
b) Copy libmysql64.dll or libmariadb64.dll, depending on which lib is linked, to the exe folder from fwh\dll folder as libmysql.dll or libmariadb.dll ( remove 64 )

4) Using BCC7 64 bit and Harbour 64 bits

a) Link with libmysql64.a or libmariadb64.a located in fwh\lib folder
b) Same as 3(b)

You can use the same code, without any changes for all these builds.

[vinhesoft]

Mr.Rao

c) To spcify primary key add one more element to the array with "PRI". Eg:
{ "custid", 'C', 5, 0, "PRI" }

what are these elements ?
" PRI " creates field primary key and how I create this same field as Auto Increment ?

Thanks

João Carlos
São Paulo - Brasil

[vinhesoft]

?

[nageswaragunupudi]

oCn:CreateTable( cTableName, aStruct, cAutoIncFieldName )

Note: aStruct should consist of all other fields other than the autoinc field.

In the above case,

aStruct may contain all fields other than custid and
oCn:CreateTable( cTableName, aStruct, "custid" )

Table will be created with custid as the first field as autoincrement primary key followed by other fields in aStruct