FiveTech Support Forums

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 wrote: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.

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

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

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.

Thanks Rao ,

great work

Maurizio

WIKI of RowSet object

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

Estoy mirando el .prg, con eso se conecta, para crear esa DB y TABLES se sigue usando los comandos del mysql?
en ese ejemplo se conecta a una DB que ya existe.

You can use any MYSQL command.
You can do everything that you can do with any other library .

nageswaragunupudi wrote:Much simpler way to specify auto-inc primary key is to specify "+" as field type.
Before or without actually creating table, you can check the table creation sql by
? oCn:CreateTableSQL( cTableName, aStruct )

Example:

Code (fw): Select all Collapse

   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 )

Result:

Code (fw): Select all Collapse

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
)

Example showing how to specify foreign key constraints, collations, calculated columns ( mysql 5.7 and above):

Code (fw): Select all Collapse

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

Result:

Code (fw): Select all Collapse

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

While using the function, please use "D" not "d" for date fields.
Similarly use "T" for datetime / timestamp fields.
Width 8 may be specified in the array for compatibility with DBF, but this value is ignored.

Please note that dates and datetime/timestamps are internally stored as numbers and we are not at all concerned with that.
Date formats are NOT stored in the database.

You may use whatever date format you like for "display" in your application.

If you write your own sql statements for INSERT/UPDATE then use the format 'YYYY-MM-DD' for date constants.
But we advise you to use the methods for inserts and updates (which are very powerful) instead of writing your own sql statements.

oRs := oCn:RowSet( cSql ) // same as oCn:Query( cSql )
? oRs:RecordCount() // number of records

OR

aData := oCn:Execute( cSql )
? Len( aData ) // number of recods

If you want only the number of records but not the data simply
? oCn:QueryResult( "select count(*) from tbprod " ) // number of records

al momento de utilizar MARIADB con la integracion de FWH,
cual es la ventaja de usar ROWSET y lo que estoy utilizando yo? soy nuevo en esa tecnologia.