Textbox changes .t. string to 1.0 1

Rutsagig · Feb 22, 2021

I have a textbox to insert email address to mysql database.
Last day, entering an address like john.t.smith@gmail.com, I notice it saved it as john1.0smith@gmail.com.
It understand .t. and .f. as true or false, and change it to 1.0 and 0.0.
How could I solve this?
Thanks

GriffMG · Feb 22, 2021

is there a picture clause?
Can you screenshot the properties?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

Mike Lewis · Feb 22, 2021

I disagree with Griff. This is more likely caused by the MySQL ODBC driver. It would normally be correct for it to interpret .T. as 1.0, but not when it is embedded in a string, as is the case here.

Can you post the SQL statement you are using to update the MySQL database.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

GriffMG · Feb 22, 2021

Well done Mike, perhaps I should have READ THE QUESTION!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

Mike Lewis · Feb 22, 2021

It might also be useful to know which version of the MySQL ODBC driver you are using.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Chris Miller · Feb 22, 2021

Is this a special textbox class? There is no automatic change of .T. to 1.0 and .F. to 0.0 in VFP. Maybe you are using some code for query building, which does that replacement as other databases use the numbers or keywords TRUE and FALSE instead. Well, then override that behavior. Within string values this replacement will cause that.

Btw. the more usual mapping is -1 for .t. and 0 for .f., but many languages take numeric 0 for false and any other number as true.

Chriss

GriffMG · Feb 22, 2021

.t. and .f. notation are pretty unique to VFP (dBase based at least languages) so I think it more likely
that the transformation would have been done in the VFP application somewhere... in that I can't imagine
that the MySQL ODBC driver has an implementation that does that in anticipation of having a VFP front end!

I suppose, although again I think it unlikely, that the back end database might have some trigger to do this
but it would have to be coded in.

So, if you have access to the dev environment, I would suggest searching for ".t.","1.0") and '.t.','1.0') and see
if you find any with STRTRAN( on the same line, using code references - although CHRTRAN( could also be used.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

Chris Miller · Feb 22, 2021

I agree, Griff. Someone had a good idea to make queries using VFPs special notation compatible but didn't think about ensuring string values are not influenced.
And for sure no other database ODBC driver would know about this VFP specific notation to change it, so that's very likely in preparation of queries towards MySQL.

Chriss

GriffMG · Feb 22, 2021

Perhaps the programmer encapsulated calls to SQL in some kind of wrapper and the translation happens in there.

At any rate, code references should find it.

OP : Do you have the source?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

Mike Lewis · Feb 22, 2021

It is correct that the ODBC driver is interpreting .T. as 1. In general, if VFP needs to send a logical value (.T. or .F.) to a database, it tells the ODBC driver that it wants to send a "logical true" or a "logical false". It does that using some internal representation; never mind how. The driver forwards that same internal value to the database.

In the case of MySQL, there is no logical data type. Instead, it uses something called a TinyInt, which is a 1-bit integer, with values of either 0 or 1. So the database receives a "logical true" and converts to 1. That is exactly as it should be.

The problem of course is that this shouldn't happen if ".T." is embedded in a character string, which is the case here. So this is clearly a bug in the ODBC driver. If it was a bug in VFP, it would happen with other ODBC drivers, and we would be seeing it with SQL Server and other back ends.

That's why I asked the OP which version of the driver he was using. And also to show us the SQL statement that is used to update the database.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Chris Miller · Feb 22, 2021

Mike, but the ODBC driver is a MySQL driver, so that surely doesn't do such replacements. I am with Griff, this must be something cause by the VFP code, but not VFP native behaviour, something someone thought would help with a conversion from VFP SQL code to MySQL dialect.

You would always avoid such replacements by using a parameterized query.

Chriss

GriffMG · Feb 22, 2021

Hi Mike

My first thoughts (not having read the blessed question) not withstanding, I believe this is a programmers idea of
saving effort... perhaps an attempt to reduce SQL injection, that has been modded later?

I don't believe it can be an ODBC bug, why would they code for a VFP platform issue, chances are the team at MySQL
have never even heard of .f. and .t.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

Mike Lewis · Feb 22, 2021

Griff and Chris: Yes, OK, you might well be right. But I can't think why VFP would exhibit this bug specifically for MySQL rather than any other database. As far as VFP is concerned, it is sending stuff to ODBC. It doesn't know or care what driver it is.

It's all weird. There must be something else going on here that we don't know about.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

GriffMG · Feb 22, 2021

I agree.

Perhaps OP will give us more clues

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

Chris Miller · Feb 22, 2021

Yes, we need more clues. But Mike, neither Griff nor me was suspecting a bug of VFP, just a bug in some library, on the VFP side, for example, a data access class.

Chriss

GriffMG · Feb 23, 2021

Exactly Chris

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

Mike Lewis · Feb 23, 2021

Point taken.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Rutsagig · Feb 23, 2021

Thanks all for your answers.
I forgot to mention that this is VFP7.
I'm using MySQL ODBC 5.3 unicode x86 driver. Tried with last version 8 and still the same, both unicode and ansi.
This software can work with MySQL and FoxPro database. I tested with Foxpro and I don't have this problem.
The textbox is from a custom class called tsbase and control is called txtcaracter, but searching code for txtcaracter didn't find any match.
I couldn't find anything for CHRTRAN or STRTRAN related to 1.0 or .t. neither.

This is the update function:

Code:

*!* oComDB.TipoDB = 1 VFP DB; oComDB.TipoDB = 2 MySQL DB
LOCAL i, j, h, k, ;
	  lcOrden,lcClaseBase, ;
	  lcCampos, lcValores, lnGlobales, lcAux
	  
WITH thisform
	lnGlobales = 0
	lcCampos=''
	lcValores=''
	IF This.llApend
		FOR i=1 TO .ControlCount
			lcClaseBase=LOWER(.Controls[i].BaseClass)
			DO CASE
				CASE AT(lcClaseBase,'textbox editbox checkbox spinner optiongroup')>0 AND !EMPTY(.Controls[i].lcCampo)
					 lcCampos=lcCampos+[,]+LOWER(.Controls[i].lcCampo)
					 DO CASE
					 	CASE AT(TYPE('.Controls[i].Value'),'C M')>0
					 		*Cambio
					 		IF ( ( oComDB.TipoDB = 1 ) AND ( lcClaseBase = 'editbox' ) )
					 		* Con DB_FOX los Memos deben pasarse como variables globales
					 			lnGlobales = lnGlobales + 1					 		
					 			lcAux = 'gGlobal'+ALLTRIM(STR(lnGlobales))+' = ALLTRIM(.Controls[i].Value)'
					 			&lcAux
						 		lcValores=lcValores+[,gGlobal]+ALLTRIM(STR(lnGlobales))
					 		ELSE
						 		lcValores=lcValores+[,"]+ CHRTRAN(ALLTRIM(.Controls[i].Value),'"',"'")+["]
					 		ENDIF					 		
					 	CASE AT(TYPE('.Controls[i].Value'),'N Y')>0
					 		SET POINT TO "."
					 		IF lcClaseBase='textbox'
						 		lcValores=lcValores+[,]+ALLTRIM(STR(.Controls[i].Value, ;
						 											.Controls[i].p_entera, ;
						 											.Controls[i].p_decimal))	
						 	ELSE
						 		lcValores=lcValores+[,]+ALLTRIM(STR(.Controls[i].Value))
						 	ENDIF
						 	SET POINT TO ","
					 	CASE TYPE('.Controls[i].Value')='D'	
						 	lcValores=lcValores+[,CTOD("]+DTOC(.Controls[i].Value)+[")]	
					 	CASE TYPE('.Controls[i].Value')='T'	
						 	lcValores=lcValores+[,CTOT("]+TTOC(.Controls[i].Value)+[")]	
						CASE TYPE('.Controls[i].Value')='L'	
						 	lcValores=lcValores+[,]+IIF(.Controls[i].Value,".T.",".F.")	
					 ENDCASE
				CASE lcClaseBase='combobox' AND !EMPTY(.Controls[i].lcCampo)
					  lcCampos=lcCampos+[,]+LOWER(.Controls[i].lcCampo)
					  IF .Controls[i].llValue
					  	DO CASE
					  		 CASE AT(TYPE('.Controls[i].Value'),'C M')>0
					  		 	* Cambio
						 		lcValores=lcValores+[,"]+CHRTRAN(ALLTRIM(.Controls[i].Value),'"',"'")+["]
						 	 CASE AT(TYPE('.Controls[i].Value'),'N Y')>0
						 		lcValores=lcValores+[,]+ALLTRIM(STR(.Controls[i].Value))
						ENDCASE
					  ELSE 
						 lcValores=lcValores+[,"]+ALLTRIM(.Controls[i].DisplayValue)+["]
					  ENDIF 
				CASE lcClaseBase='image' AND !EMPTY(.Controls[i].lcCampo)
					 lcCampos=lcCampos+[,]+LOWER(.Controls[i].lcCampo)
					 lcValores=lcValores+[,"]+ALLTRIM(.Controls[i].Picture)+["]

					 

			ENDCASE
		ENDFOR

		lcOrden=[INSERT INTO ]+this.lcTabla+[ ]+STUFF(lcCampos,1,1,[(])+[) VALUES ]+STUFF(lcValores,1,1,[(])+[)]
		oComDB.AddRegistro(lcOrden)
	ELSE 
		lcOrden=[UPDATE ]+this.lcTabla+[ SET ]
		FOR i=1 TO .ControlCount
			lcClaseBase=LOWER(.Controls[i].BaseClass)
			DO CASE
				CASE AT(lcClaseBase,'textbox editbox checkbox spinner optiongroup')>0 AND !EMPTY(.Controls[i].lcCampo)
					 lcOrden=lcOrden+LOWER(.Controls[i].lcCampo)+[=]
					 DO CASE
					 	CASE AT(TYPE('.Controls[i].Value'),'C M')>0
					 		*Cambio
					 		IF ( ( oComDB.TipoDB = 1 ) AND ( lcClaseBase = 'editbox' ) )
					 		* Con DB_FOX los Memos deben pasarse como variables globales
								lnGlobales = lnGlobales + 1
					 			lcAux = 'gGlobal'+ALLTRIM(STR(lnGlobales))+' = ALLTRIM(.Controls[i].Value)'
					 			&lcAux
						 		lcOrden=lcOrden+[gGlobal]+ALLTRIM(STR(lnGlobales))+[,]
					 		ELSE
						 		lcOrden=lcOrden+["]+CHRTRAN(ALLTRIM(.Controls[i].Value),'"',"'")+[",]
						 	ENDIF
					 	CASE AT(TYPE('.Controls[i].Value'),'N Y')>0
					 		SET POINT TO "."
					 		
					 		IF lcClaseBase='textbox'
						 		lcOrden=lcOrden+ALLTRIM(STR(.Controls[i].Value, ;
						 											.Controls[i].p_entera, ;
						 											.Controls[i].p_decimal))+[,]	
						 	ELSE
						 		IF lcClaseBase='checkbox'
								 	lcOrden=lcOrden+IIF(!EMPTY(.Controls[i].Value),".T.",".F.")+[,]	
						 		ELSE
							 		lcOrden=lcOrden+ALLTRIM(STR(.Controls[i].Value))+[,]
							 	ENDIF
						 	ENDIF
					 		SET POINT TO ","
					 	CASE TYPE('.Controls[i].Value')='D'	
						 	lcOrden=lcOrden+[CTOD("]+DTOC(.Controls[i].Value)+["),]	
					 	CASE TYPE('.Controls[i].Value')='T'	
						 	lcOrden=lcOrden+[CTOT("]+TTOC(.Controls[i].Value)+["),]	
						CASE TYPE('.Controls[i].Value')='L'	
						 	lcOrden=lcOrden+IIF(.Controls[i].Value,".T.",".F.")+[,]	
					 ENDCASE
				CASE lcClaseBase='combobox' AND !EMPTY(.Controls[i].lcCampo)
					 lcOrden=lcOrden+LOWER(.Controls[i].lcCampo)+[=]
					 IF .Controls[i].llValue
					  	DO CASE
					  		 CASE AT(TYPE('.Controls[i].Value'),'C M')>0
					  		 	* Cambio
						 		lcOrden=lcOrden+["]+CHRTRAN(ALLTRIM(.Controls[i].Value),'"',"'")+[",]
						 	 CASE AT(TYPE('.Controls[i].Value'),'N Y')>0
						 		lcOrden=lcOrden+ALLTRIM(STR(.Controls[i].Value))+[,]
						ENDCASE
					 ELSE 
						 lcOrden=lcOrden+["]+ALLTRIM(.Controls[i].DisplayValue)+[",]
					 ENDIF 
				CASE lcClaseBase='image' AND !EMPTY(.Controls[i].lcCampo)
					 lcOrden=lcOrden+LOWER(.Controls[i].lcCampo)+[="]+ALLTRIM(.Controls[i].Picture)+[",]
			ENDCASE
		ENDFOR
		lcOrden=STUFF(lcOrden,LEN(lcOrden),1,[ ])+[WHERE ]+ALLTRIM(this.lcPriKey)+[=]+ALLTRIM(STR(this.lnPriKey))
		oComDB.ModRegistro(lcOrden,1)
	ENDIF 

	DO WHILE ( lnGlobales > 0 )
		lcAux = 'RELEASE gGlobal'+ALLTRIM(STR(lnGlobales))
		&lcAux
		lnGlobales = lnGlobales - 1
	ENDDO
	
ENDWITH
this.llApend=.F.

GriffMG · Feb 23, 2021

I can't see immediately anything in the above that would do the conversion that is apparently happening.

But that said, I can see that for logical value the code is designed to pass .t. and .f. as part of an insert/update sql string maybe something like this:

Code:

"INSERT INTO tblMyTable (logval1,logval2) values (.t.,.f.)"
of
"UPDATE tblMyTable set logval1=.t.,logval2=.f. where prikey=10026"

And then the code calls oComDB.ModRegistro or oComDB.AddRegistro to finish the job.

SO my guess is that in those two functions there is some code like:

Code:

IF oComDB.TipoDB = 2 
  lcOrden = STRTRAN(lcOrden,".T.","1.0")
  lcOrden = STRTRAN(lcOrden,".F.","0.0")
ENDIF

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

Chris Miller · Feb 23, 2021

You likely won't find it in the textbox, it's likely to be done down the line later when queries are built or adapted.

Use code references and search the whole project for something changing .t. to 1.0 or 1.
And you need some searches, as strings can be in single, double quotes or square brackets.

Chriss

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Textbox changes .t. string to 1.0 1

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

IS-IT--Management

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor