problema con ado mysql y fwh (solucionado)

[carlos vargas]

estimados, me esta ocurriendo que tengo tablas con campos de tipo char, por ejemplo 40 espacios, pero al momento de realizar consultas y utilizar oRS:Fields("CAMPOCHAR"):Value me esta retornando la cadena pero recortada sin espacios en blanco. cabe mensionar que con el mssql funciona bien, es solo con mysql, he probado varios odbc, version 3 y 5, usando y no usando la opcion PAD CHAR to FULL LENGH with space pero nada.
alguna ayuda?

Code: Select all  Expand view

  IF lNuevo
   cSql_CTRL := "SELECT CONT_EMPR FROM CONTROL"
   cSql_EMPR := "SELECT NUM_EMPR, NOMBRE, NOMCOR, MY_RECNO FROM EMPRESAS ORDER BY NUM_EMPR"

   oRS_CTRL := FW_OpenRecordSet( oConn, cSql_CTRL )
   oRS_EMPR := FW_OpenRecordSet( oConn, cSql_EMPR )

   IF HB_IsNil( oRS_CTRL ) .or. HB_IsNil( oRS_EMPR )
      FW_CloseRecordSet( { oRS_CTRL, oRS_EMPR } )
      RETURN
   ENDIF
...
      nNumero    := 0
      cNombre    := Space( 40 )
      cNomCor    := Space( 02 )
   ELSE
      IF FW_EmptyRecordSet( oRS_EMPR )
         MsgAlert( "No hay registros, nada que editar." )
         oBrw:SetFocus()
         RETURN
      ENDIF
      nNumero    := oRS_EMPR:Fields( "NUM_EMPR" ):Value
      cNombre    := oRS_EMPR:Fields( "NOMBRE"   ):Value
      cNomCor    := oRS_EMPR:Fields( "NOMCOR"   ):Value
   ENDIF
   ?Len(cNombre) //aca deberia ser 40, pero solo retorna 8 y este es el valor "EMPRESA5"
 

[Armando]

Carlos:

MySql trata de ahorrar espacio de almacenamiento y solo conserva los caracteres significativos,
todavía peor, si intentas utilizar la variable cNombre en algún GET
solo te permitirá introducir 8 caracteres. Prueba con lo siguiente y nos cuentas:

Code: Select all  Expand view

En lugar de
cNombre    := oRS_EMPR:Fields( "NOMBRE"   ):Value

Intenta con
cNombre := oRS_EMPR:Fields( "NOMBRE"   ):Value + SPACE(LEN(cNombre) - LEN(oRS_EMPR:Fields( "NOMBRE"   ):Value))
 

Saludos

[carlos vargas]

eso de usar la var en el get, es lo que me esta pasando.

asi funciona, pero jode hacer esto en cada campo caracter

Code: Select all  Expand view

cNombre = padr( oRS:Fields("CAMPOCHAR"):Value, oRS:Fields("CAMPOCHAR"):DefinedSize )
 

[Armando]

Carlos:

Al tiempo te acostumbras, todo tiene un precio

Saludos

[Daniel Garcia-Gil]

Carlos

el campo lo tienes definido como VARCHAR?, de ser si, prueba definirlo como CHAR y le asignas la longitud, prueba y comenta

[Armando]

Daniel, Carlos:

El resultado será el mismo.

Saludos

[carlos vargas]

sip, es lo mismo, solamente con mysql es asi.
con otros engine funciona bien.

salu2
carlos vargas

[nageswaragunupudi]

Not only MySql, behavior of VarChar fields is the same in all RDMSs.

In Sql DBMSs fieldtype CHAR(nwidth) behaves almost like our DBF fields.
But CHAR field is very rarely used and for a good reason.
Good practice is to use VARCHAR(nwidth) fields. Also it is good practice to store Trimmed values. We can not store strings larger than the DefinedSize. (This raises an error).
When we read we get the string of the same length as we have stored.

It is possible to store padded strings. We can store 'Hello '. In such a case when we read we get the value including the padded spaces when we stored. But this is not a good practice. It is good to store trimmed values.

We need padded values only for using in GETs. For this purpose we need to pad the values in our program. eg., cVal := PadR( oRs:Fields( "fieldname" ):Value, oRs:Fields( "fieldname" ):DefinedSize )
Agreed that this is combursome.

FWH comes in for some support here.

XBrowse:
When reading the field value is padded with spaces and displayed. When editing inline in browse, GET receives the padded value. While storing XBrowse stores Trimmed values.
This way XBrowse follows good practices in maintaining the SQL Table and at the same time gives the programmer the same experience as we handle DBF tables. ( If we give up the old habit of directly using bStrdata, etc )

TDataRow:
Same way like XBrowse the values are padded for editing and trimmed for storing.

TRecSet (new class)
Instead of using FW_OpenRecordSet(...), it is now recommended to use
oRs := TRecSet():New():Open( cSql, oCn )
Fields can be referred to as:
? oRs:FirstName
? oRs:Salary
oRs:Age := 40
TRecSet class, by default, displays character values Padded and stores trimmed values.

Example:
Assumomg field "CustName" definedsize is 30
oRs:CustName := "John"
? Len( oRs:Custname ) --> 30
oRs:Custname returns "John" padded to 30 chars.

All these 3 classes do this padding and trimming transparently. This reduces all the coding burdens.

[carlos vargas]

Estimado, cierro este post con la solucion encontrada luego de meses.
adjunto en ejemplo

Code: Select all  Expand view

#include "fivewin.ch"
#include "ado.ch"

procedure main()
   local oCon, oRS, oError
 
   TRY

      oCon := CreateObject( "ADODB.Connection" )
      oCon:ConnectionString := "DSN=CYC_WAN;Uid=myusuario;Pwd=mypassword;Database=mydatabase;Option=3;"
      oCon:Open()
     
      xbrowse(  FW_AdoTables( oCon )  )
     
      oCon:Execute("SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'")  //ESTE HACE LA MAGIA :-)
     
      oRS := CreateObject( "ADODB.Recordset" )
      oRS:CursorLocation = adUseClient
      oRS:CursorType     = adOpenKeyset
      oRS:Open( "SELECT NUM_CLIE, NOMBRE, CEDULA FROM CLIENTES WHERE NUM_CIUD=9", oCon )
     
      ?oRS:RecordCount(), len( oRS:Fields("NOMBRE"):Value ),  oRS:Fields("NOMBRE"):Value
     
      xbrowse( oRS )

   CATCH
      FW_ShowAdoError(oCon)
   FINALLY
      iif( oRS:State==adStateOpen, oRS:close(), NIL )
      iif( oCon:State==adStateOpen,oCon:close(), NIL )  
   end
   
 return
 
 

[anserkk]

Dear Mr.Carlos,

To test the VarChar columns length issue, I tried your code which you have posted above , unfortunately I am not getting the expected result.

Code: Select all  Expand view

? len( oRS:Fields("Product_Name"):Value )

The Len() returns the length of the column's content and NOT the defined size of the column.

For eg if the Varchar Column's defined length is 40 and the value contained in the column is "Toy", len( oRS:Fields("Product_Name"):Value ) says that the length is 3.

Am I missing anything ?

I tried in MariaDB (ver 10.0.12) as well as MySQL (ver 5.6.23)

PAD_CHAR_TO_FULL_LENGTH

By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval. This mode was added in MySQL 5.1.20.

Regards
Anser

[carlos vargas]

My fields are CHAR no VARCHAR.

[Armando]

Friends:

Perhaps the follow code is the solution.

Code: Select all  Expand view

        MsgInfo(LEN(oRsBan:Fields("BAN_NOM"):Value))  // this code line gives us 4
        MsgInfo(oRsBan:Fields("BAN_NOM"):DefinedSize) // this code line gives us 40
 

Regards

[anserkk]

As explained by Mr.Rao, the TRecSet class takes care of this issue.

Regards
Anser