field length and space in MySql *Fixed*

[dutch]

Dear All,

I declare cVariable for a field (VARCHAR(50) in MySql) but the field is empty. The problem is it will return space(0) to cVariable, then I cannot type in TGET because space(0), in "DBFCDX" it will return space(50).

How do I do as "DBFCDX" RDD?

Thanks in advance.

Code: Select all  Expand view

cVar := oDb:TBL_NAME
@ 10, 10 GET oGet VAR cVar ....  => it has no space for input data

[AntoninoP]

This is a perfect example of because we need the tEdit class fixed!

[Rick Lipkin]

Dutch

Many times a Sql field will return nil so when you initialize a variable .. the variable will be nil as you have discovered there is no Get value when you assign it to a field on a form.

This is what I do every time when I define variables from a Sql table .. I know it is not pretty, but it is just the way I have dealt with nil Sql fields.

Rick Lipkin

Code: Select all  Expand view

// Bin is defined as char50
// DateRcvd is defined as date\Time

Local cBin,dDateRcvd

cBin := If(empty(oRs:Fields("Bin"):Value),space(50),;
              substr(oRs:Fields("Bin"):Value+space(50),1,50)

dDateRcvd := If(empty(oRs:Fields("DateRcvd"):Value), ctod(""),;
                           TtoDate(oRs:Fields("DateRcvd"):Value))

//--------------------------
Function TtoDate( tDate )

If empty( tDate)
   Return( ctod("00/00/00"))
Endif

If ValType( tDate ) = "D"
   Return(tDate )
Endif

Return( stod( substr( ttos( tDate ), 1, 8 ) ))
 

[Francisco Horta]

dutch,

cambia varchar por char,
varchar si no contiene texto aunque su longitud sea de 50 de devolvera 0.
si tu campo varchar contiene "MI NOMBRE" te devolvera una len de 9. Realmente te devuelve la longitud de lo que contiene, y char te devuelve la longitud completa del campo.

Saludos
Paco

[AntoninoP]

eh?

[dutch]

Dear Rick,

Thank you for your kind help but the hard code will face the problem when field length is changed.

I just try to use this function but I don't think it is correct way to go.

Code: Select all  Expand view

Function ...
cVar := GetVar(oDb,"TBL_NAME")
return
*----------------------------*
Function GetVar( oDb, cField )
local uRetVar
if oDb:FieldType( cField ) = 'C'
    uRetVar := if( len(oDb:FieldGet( cField ))<>0,  left(oDb:FieldGet( cField )+space(oDb:FieldLen( cField )), oDb:FieldLen( cField ) ), space(oDb:FieldLen( cField )) )
else
    uRetVar := oDb:FieldGet( cField )
end
return uRetVar

Dutch

Many times a Sql field will return nil so when you initialize a variable .. the variable will be nil as you have discovered there is no Get value when you assign it to a field on a form.

This is what I do every time when I define variables from a Sql table .. I know it is not pretty, but it is just the way I have dealt with nil Sql fields.

Rick Lipkin

Code: Select all  Expand view

// Bin is defined as char50
// DateRcvd is defined as date\Time

Local cBin,dDateRcvd

cBin := If(empty(oRs:Fields("Bin"):Value),space(50),;
              substr(oRs:Fields("Bin"):Value+space(50),1,50)

dDateRcvd := If(empty(oRs:Fields("DateRcvd"):Value), ctod(""),;
                           TtoDate(oRs:Fields("DateRcvd"):Value))

//--------------------------
Function TtoDate( tDate )

If empty( tDate)
   Return( ctod("00/00/00"))
Endif

If ValType( tDate ) = "D"
   Return(tDate )
Endif

Return( stod( substr( ttos( tDate ), 1, 8 ) ))
 

Dear Paco,

I will test it again but the limitation of CHAR() length is 50 (I read from the web). Is it correct?
Thanks for kind help.

dutch,

cambia varchar por char,
varchar si no contiene texto aunque su longitud sea de 50 de devolvera 0.
si tu campo varchar contiene "MI NOMBRE" te devolvera una len de 9. Realmente te devuelve la longitud de lo que contiene, y char te devuelve la longitud completa del campo.

Saludos
Paco

[dutch]

Dear Antonio,

Thank you for your kind suggestion.

This is a perfect example of because we need the tEdit class fixed!

[Marc Vanzegbroeck]

Dear Antonio,

Thank you for your kind suggestion.

This is a perfect example of because we need the tEdit class fixed!

Dutch,

I use a function I wrote myself to read with GET's.
First I test for the type. I use oQry:fields("naam'):type = 129 for charachters
Then I use oQry:Fields('naam'):DefinedSize to get the field length

I use this for all datatypes

[dutch]

Dear All,

I got the problem now, it is the DEFAULT for CHAR or VARCHAR. If we don't define the default, it will define as NULL and then got this problem.

The DEFAULT of CHAR or VARCHAR must be '.' (dot). It will return space as field length, if it is empty. That's it.

Thanks for all.

[nageswaragunupudi]

My advice, after decades of experience, is not to continue xbase habits with sql databases.
Standard and good practice is to store trimmed values.
Programmer needs to pad for editing. Anyway the Edit control will remove this need too.

Please note that if you are using ADO, XBrowse, TDataRow, TRecSet automatically PAD spaces while reading so that we can edit padded value and while saving, they are automatically trimmed and saved.
This behavior is provided so that programmers can program like xbase for Gets and yet observe the best industry practices for maintaining the tables.