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

[nageswaragunupudi]

Correspodance of number formats in DBF and MySql:

Field width of a decimal number in DBF indicates the maximum size of number including decimal and -ve sign, where as in MySql it is exclusive. For example, {,"N",6,2} accommodates any number from -99.99 to 999.99 in DBF where as its closest match Decimal(5,2) in MySql accommodates -999.99 to 999.99.

So, while converting from DBF format to MySql, DBF field {..,"N",w,d} should be converted as DECIMAL(w-1,d). As an example, { "amount","N",9,2 } of DBF should be converted as "amount DECIMAL(8,2)".

At present, methods CreateTable() and ImportFromDBF() are creating MySql field with higher width i.e., as DECIMAL( w + 1, d ) instead of DECIMAL( w - 1, d ). This is rectified in FWH 17.04. However oRs:aStructure will display the width as W corresponding to the DBF format,

Example:
Field in DBF : { "SALARY", "N", 9, 2 } will be converted as
MySql .. "SALARY DECIMAL( 8, 2 ),"

oRs:aStructure will display the field as { "SALARY", "N", 9, 2 } i.e, following the DBF convention.
oRs:Fields("salary"):Precision shows 8, matching ADO.
oCn:ListColumns( ctable ) displays "Decimal(8,2)"

[nageswaragunupudi]

FWH 17.05: New methods of Connection Object

oCn:TableStructure( cTable ) --> aStructure
cTable should be a table in the curreent db. We can query the structure of a table without opening the table. The result is identical to oRs:aStructure. oCn:ListColumns( [db.]cTable ) gives more information about the columns.

oCn:CopyStructure( [dbsrc.]srctable, [dbdst.]dsttable ) --> lSuccess
For creating a new table in the same or other database with the same structure as the source table. This function fails if (a) srctable does not exist or (b) dsttable already exits or (c) the table has a foreign key and the referred table is not availble in the destination db.

oCn:CopyTable( [dbsrc.]srctable, [dbdst.]dsttable, ;
[cSrcFieldList], [cDstFieldList], [cSrcWhere], ;
["update/ignore'] ) --> lSuccess
Copies contents of all fields or specified fields from srctable to dsttable for all rows or for selected rows with where condition.

If the destination table does not exist, it is created.(See CopyStructure)

By default if the dsttable already contains the primary/unique keys the entire operation fails.

If 6th parameter is "ignore", only non-duplicate values are copied/inserted.
If 6th parameter is "update", non-duplicate values are inserted and duplicate rows are updated with the values in the source table.

[vilian]

Also New in 16.12 (Upcoming):

Automatic Recovery from Lost Connections:

During execution of a program, connection to the server can be lost either due to connection timeout or due to physical loss of connection like failure or internet or physical connection.

Even after disconnection, the program can continue to browse / view the tables already opened including sorting and filtering, but any attempts to write data, requery, etc will fail.

TimeOut:
It is customary to set time out to 28,800 seconds (8 hours) in many cases. In some cases it is possible that the timeout is set to a very short period.

When any sql is executed for access or writing data, if disconnection is encountered, automatically reconnection is attempted. In case of connections lost due to time out, the recovery is 100% successful. In case of physical loss of connections, the program can continue without accessing data from the server and when the physical connectivity is restored any attempt to read/write data will automatically reconnect to the server and proceed with the execution of the program.

Mr Rao,

I think it's not working. I'm having severals message of disconnection in a customer.

Code: Select all

06/06/2017 15:32:02: FWMARIACONNECTION:EXECUTE_SQL( 3829 )  CallStack() = " <- FWMARIACONNECTION:ROWSET(4266) <- FWMARIAROWSET:EDITBASERECORD(2839) <- (b)EVAL(61) <- TBUTTONBMP:CLICK(179) <- TBUTTON:HANDLEEVENT(1685)"   cSql = "SELECT * FROM `cpr` LIMIT 0"    uRet =  ::nError = 2003 ::cError = "Can't connect to MySQL server on '192.168.2.2' (10061)" ::cSqlInfo = "" 
06/06/2017 16:12:39: FWMARIACONNECTION:EXECUTE_SQL( 3829 )  CallStack() = " <- FWMARIACONNECTION:ROWSET(4266) <- FWMARIAROWSET:EDITBASERECORD(2839) <- (b)EVAL(61) <- TBUTTONBMP:CLICK(179) <- TBUTTON:HANDLEEVENT(1685)"   cSql = "SELECT * FROM `cpr` LIMIT 0"    uRet =  ::nError = 2003 ::cError = "Can't connect to MySQL server on '192.168.2.2' (10061)" ::cSqlInfo = "" 
06/06/2017 16:23:15: FWMARIACONNECTION:EXECUTE_SQL( 3829 )  CallStack() = " <- FWMARIACONNECTION:ROWSET(4266) <- FWMARIAROWSET:EDITBASERECORD(2839) <- (b)EVAL(61) <- TBUTTONBMP:CLICK(179) <- TBUTTON:HANDLEEVENT(1685)"   cSql = "SELECT * FROM `cpr` LIMIT 0"    uRet =  ::nError = 2003 ::cError = "Can't connect to MySQL server on '192.168.2.2' (10061)" ::cSqlInfo = "" 
06/06/2017 16:42:39: FWMARIACONNECTION:EXECUTE_SQL( 3829 )  CallStack() = " <- FWMARIACONNECTION:ROWSET(4266) <- FWMARIAROWSET:EDITBASERECORD(2839) <- (b)EVAL(61) <- TBUTTONBMP:CLICK(179) <- TBUTTON:HANDLEEVENT(1685)"   cSql = "SELECT * FROM `cpr` LIMIT 0"    uRet =  ::nError = 2003 ::cError = "Can't connect to MySQL server on '192.168.2.2' (10061)" ::cSqlInfo = "" 

Do you know how can I prevent this ?

[nageswaragunupudi]

Application keeps trying to reconnect and reconnects if possible to reconnect. When physical/internet connection is re-established it reconnects.

[nageswaragunupudi]

WIKI for Connection Object is ready
http://wiki.fivetechsoft.com/doku.php?i ... connection

All suggestions for improvements are welcome.

Wiki for rowset object is still in progress.

[Maurizio]

Thanks Rao ,

great work

Maurizio

[nageswaragunupudi]

WIKI of RowSet object

http://wiki.fivetechsoft.com/doku.php?i ... ariarowset