TDolphin question

[ADutheil]

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

The create statement is:

Code: Select all  Expand view

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

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

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

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?

[Daniel Garcia-Gil]

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

[ADutheil]

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

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