FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour field length and space in MySql *Fixed*
dutch
Posts: 1598
Joined: Fri Oct 07, 2005 05:56 PM
field length and space in MySql *Fixed*
Posted: Tue Jul 07, 2015 08:06 AM
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 (fw): Select all Collapse
cVar := oDb:TBL_NAME
@ 10, 10 GET oGet VAR cVar ....  => it has no space for input data
Regards,

Dutch



FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)
AntoninoP
Posts: 375
Joined: Tue Feb 10, 2015 09:48 AM
Re: field length and space in MySql
Posted: Tue Jul 07, 2015 08:11 AM
This is a perfect example of because we need the tEdit class fixed!
Rick Lipkin
Posts: 2706
Joined: Fri Oct 07, 2005 01:50 PM
Re: field length and space in MySql
Posted: Tue Jul 07, 2015 01:20 PM
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 (fw): Select all Collapse
// 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
Posts: 845
Joined: Sun Oct 09, 2005 05:36 PM
Re: field length and space in MySql
Posted: Tue Jul 07, 2015 03:10 PM

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

____________________

Paco
AntoninoP
Posts: 375
Joined: Tue Feb 10, 2015 09:48 AM
Re: field length and space in MySql
Posted: Tue Jul 07, 2015 03:22 PM

eh?

dutch
Posts: 1598
Joined: Fri Oct 07, 2005 05:56 PM
Re: field length and space in MySql
Posted: Tue Jul 07, 2015 03:29 PM
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 (fw): Select all Collapse
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

Rick Lipkin wrote: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 (fw): Select all Collapse
// 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.
Francisco Horta wrote: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
Regards,

Dutch



FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)
dutch
Posts: 1598
Joined: Fri Oct 07, 2005 05:56 PM
Re: field length and space in MySql
Posted: Tue Jul 07, 2015 03:31 PM
Dear Antonio,

Thank you for your kind suggestion.
AntoninoP wrote:This is a perfect example of because we need the tEdit class fixed!
Regards,

Dutch



FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)
Marc Vanzegbroeck
Posts: 1195
Joined: Mon Oct 17, 2005 05:41 AM
Re: field length and space in MySql
Posted: Tue Jul 07, 2015 04:18 PM
dutch wrote:Dear Antonio,

Thank you for your kind suggestion.
AntoninoP wrote: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
Regards,

Marc



FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
dutch
Posts: 1598
Joined: Fri Oct 07, 2005 05:56 PM
Re: field length and space in MySql *Fixed*
Posted: Wed Jul 08, 2015 09:17 AM

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.

Regards,

Dutch



FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: field length and space in MySql *Fixed*
Posted: Wed Jul 08, 2015 01:56 PM

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.

Regards



G. N. Rao.

Hyderabad, India

Continue the discussion

Static archive · New replies & topics via GitHub Discussions