topic-23509

Board index FiveWin for Harbour/xHarbour TDolphin question

Posts: 368

Joined: Sun May 31, 2009 06:25 PM

TDolphin question
Posted: Fri Feb 17, 2012 12:39 PM

I imported a DBF to MySQL with the dbf2sql V2.6 tool.

The create statement is:

CREATE TABLE `usuarios` (
  `nome` varchar(10) DEFAULT NULL,
  `senh` varchar(10) DEFAULT NULL,
  `nive` varchar(10) DEFAULT NULL,
  `digi` varchar(10) DEFAULT NULL,
  `perm` varchar(60) DEFAULT NULL,
  `codi` varchar(4) DEFAULT NULL,
  `sql_rowid` bigint(10) NOT NULL AUTO_INCREMENT,
  `sql_deleted` enum('F','T') NOT NULL,
  `ativ` varchar(1) NOT NULL DEFAULT 'S',
  PRIMARY KEY (`sql_rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1

The original field "nome" was filed with crypted values and I wanted to remove that crypt. The field "codi" was not used but I wanted to use it now. I managed to do everything but during the process I encountered sometinhg I don´t understand:

Code (fw): Select all Collapse

oQuer := oServer:Query( "SELECT NOME FROM usuarios" )
oQuer:GoTop()
WHILE !oQuer:Eof()
oQuer:NOME := allTrim( crypt( oQuer:NOME, "ENCRYPTKEY" ) ) // Remove the encryption from field NOME
oQuer:Save()
oQuer:Skip()
ENDDO
oQuer:End()

Works perfect. The values of column NOME are saved readable text.

Code (fw): Select all Collapse

oQuer := oServer:Query( "SELECT NOME,CODI FROM usuarios" )
oQuer:GoTop()
WHILE !oQuer:Eof()
oQuer:NOME := allTrim( crypt( oQuer:NOME, "ENCRYPTKEY" ) )
oQuer:CODI := strZero( oQuer:nRecNo, 4 )
oQuer:Save()
oQuer:Skip()
ENDDO
oQuer:End()

JUST DO NOTHING.

Code (fw): Select all Collapse

oQuer := oServer:Query( "SELECT * FROM usuarios" )
oQuer:GoTop()
WHILE !oQuer:eOF()
oQuer:NOME := allTrim( crypt( oQuer:NOME, "ENCRYPTKEY" ) )
oQuer:CODI := strZero( oQuer:nRecNo, 4 )
oQuer:Save()
oQuer:Skip()
ENDDO
oQuer:End()

Works perfect. The values of column NOME are saved readable text and CODI is OK too.

What I understand from the above is that to alter more than 1 field I have to query * and not only the fields I want to alter. Is my understanding correct, is there a bug or am I missing something?

Regards,

André Dutheil

FWH 13.04 + HB 3.2 + MSVS 10

Daniel Garcia-Gil

Posts: 2365

Joined: Wed Nov 02, 2005 11:46 PM

Re: TDolphin question
Posted: Fri Feb 17, 2012 01:18 PM

Hello

the method save will save the data that was modified in query with reference to the data of the same query,
example
select name from usuarios
oQry:name = "xxxx"

the method save will do
first check there are a primary key inside query to take it and to do the update, if there are not take the values inside the query like reference to modify
update usuarios set name='xxxx' where name='old_name_value'

now when you do select * from usuarios

the method save will do
first check there are a primary key inside query to take it and to do the update
oQry:name = "xxxx"

update usuarios set name='xxxx' where id_row=9999

if you want to use this way remember this note :-)

you can use
select id_row, name from usuarios

additional tip: is not recomendable use "*" is better select the fields to query

remember too, mysql is totally different to dbf...

our best documentation is the source code
Isla de Margarita Venezuela.
danielgarciagil@gmail.com
http://tdolphin.blogspot.com/
https://www.dropbox.com/referrals/NTI5Njk4MDc1OQ?src=global9

ADutheil

Posts: 368

Joined: Sun May 31, 2009 06:25 PM

Re: TDolphin question
Posted: Fri Feb 17, 2012 03:14 PM

Daniel thanks for the reply. As the table was created by dbf2sql I did not noticed it had a primary key. Now everything works fine.,

Code (fw): Select all Collapse

oQuer := oServer:Query( "SELECT NOME,CODI,SQL_ROWID FROM usuarios" )
oQuer:GoTop()
WHILE !oQuer:eOF()
oServer:SqlQuery( "UPDATE usuarios SET nome='" + allTrim( crypt( oQuer:NOME, "ENCRYPTKEY" ) ) + "',codi='" + strZero( oQuer:nRecNo, 4 ) + "' WHERE sql_rowid=" + str( oQuer:SQL_ROWID ) )
oQuer:Skip()
ENDDO
oQuer:End()

Regards,

André Dutheil

FWH 13.04 + HB 3.2 + MSVS 10

FiveTech Support Forums

Continue the discussion