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

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

Postby nageswaragunupudi » Wed Apr 19, 2017 2:39 am

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

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby nageswaragunupudi » Thu May 18, 2017 6:32 am

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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby vilian » Wed Jun 07, 2017 6:23 pm

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.


Mr Rao,

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

Code: Select all  Expand view
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 ?
Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil
User avatar
vilian
 
Posts: 913
Joined: Wed Nov 09, 2005 2:17 am
Location: Brazil

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

Postby nageswaragunupudi » Wed Jun 07, 2017 7:01 pm

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

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby nageswaragunupudi » Wed Aug 02, 2017 8:51 pm

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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby Maurizio » Thu Aug 03, 2017 6:25 am

Thanks Rao ,

great work

Maurizio
User avatar
Maurizio
 
Posts: 796
Joined: Mon Oct 10, 2005 1:29 pm

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

Postby nageswaragunupudi » Thu Aug 03, 2017 8:26 pm

Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby goosfancito » Mon Sep 27, 2021 9:12 pm

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.
FWH 21.02
Harbour 3.2.0dev (r2104281802)
Copyright (c) 1999-2021, https://harbour.github.io/
User avatar
goosfancito
 
Posts: 1954
Joined: Fri Oct 07, 2005 7:08 pm

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

Postby nageswaragunupudi » Mon Sep 27, 2021 9:33 pm

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

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby goosfancito » Tue Sep 28, 2021 12:56 pm

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

Code: Select all  Expand view
   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.
FWH 21.02
Harbour 3.2.0dev (r2104281802)
Copyright (c) 1999-2021, https://harbour.github.io/
User avatar
goosfancito
 
Posts: 1954
Joined: Fri Oct 07, 2005 7:08 pm

Re: FWH : Built-in MySql/MariaDB functionality

Postby goosfancito » Tue Sep 28, 2021 8:18 pm

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: Select all  Expand view
  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
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
  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
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?
FWH 21.02
Harbour 3.2.0dev (r2104281802)
Copyright (c) 1999-2021, https://harbour.github.io/
User avatar
goosfancito
 
Posts: 1954
Joined: Fri Oct 07, 2005 7:08 pm

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

Postby nageswaragunupudi » Wed Sep 29, 2021 3:58 pm

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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby goosfancito » Wed Oct 06, 2021 9:30 am

Estimado,

Como obtengo la cantidad de registros devueltos por una consulta hecha de esta manera?
Code: Select all  Expand view

   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 )
FWH 21.02
Harbour 3.2.0dev (r2104281802)
Copyright (c) 1999-2021, https://harbour.github.io/
User avatar
goosfancito
 
Posts: 1954
Joined: Fri Oct 07, 2005 7:08 pm

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

Postby nageswaragunupudi » Wed Oct 06, 2021 3:10 pm

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
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby goosfancito » Wed Oct 06, 2021 5:07 pm

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.
FWH 21.02
Harbour 3.2.0dev (r2104281802)
Copyright (c) 1999-2021, https://harbour.github.io/
User avatar
goosfancito
 
Posts: 1954
Joined: Fri Oct 07, 2005 7:08 pm

PreviousNext

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 8 guests