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  Expand view  RUN

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

[goosfancito]

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.

[nageswaragunupudi]

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

[goosfancito]

es necesario usar el parámeto lAutoInc si tengo esta estructura ?

Code: Select all  Expand view  RUN

   aDatos := { ;
                  { "id",           "n", 6, 0, .T., 0 }, ;
                  { "idfactura",    "n", 6, 0, .T., NIL }, ;
                  { "idcontacto",   "n", 4, 0, .T., NIL }, ;
                  { "fecha",        "d", 10, 0, .T., NIL }, ;
                  { "subtotal",     "n", 6, 2, .T., NIL }, ;
                  { "descuento",    "n", 6, 2, .T., NIL }, ;
                  { "total",        "n", 6, 2, .T., NIL } ;
                }

Gracias.

[goosfancito]

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: Select all  Expand view  RUN

  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: Select all  Expand view  RUN

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: Select all  Expand view  RUN

  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: Select all  Expand view  RUN

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

al momento de declarar el tipo DATE "d" porque 8? 99/99/9999 como convierto despues ese dato "d" 8 al formato 99/99/9999 dia, mes, año?

[nageswaragunupudi]

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.

[goosfancito]

Estimado,

Como obtengo la cantidad de registros devueltos por una consulta hecha de esta manera?

Code: Select all  Expand view  RUN

   IF ::nId != 0

      TEXT into cSql
      Select
      p.id AS c1,
      p.idsucursal AS c2,
      p.iddistribuidor AS c3,
      p.nombre AS c4,
      pr.id AS c5,
      pr.idsucursal AS c6,
      pr.idproducto AS c7,
      pr.fecha AS c8,
      pr.importe AS c9,
      pr.stock AS c10,
      pr.stockminimo AS c11,
      pr.ganancia AS c12
      FROM tbprod p
      Left join tbprecio pr
      ON pr.idproducto = p.id
      WHERE p.id = 'nId'
      ENDTEXT

      cSql := StrTran( cSql, 'nId', Str( ::nId ) )

      ::oCnx:lShowErrors := .T.
      oQry := ::oCnx:QUERYRESULT( cSql )

[nageswaragunupudi]

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

[goosfancito]

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.