ADO: filter does not work on memo fields [fixed]

[MOISES]

Hi,

Performing a filter on ADO (oRs:Filter) using LIKE on a memo field does not work.

Is there a way?

Thank you.

[Armando]

Moises:

It Works fine to me.

Show us your code, pls.

Regards

[MOISES]

Armando,

This is the code:

Code: Select all  Expand view

cFiltro := cCampo + space(1) + "LIKE" + space(1) + "'%"+alltrim(cLiteral)+"%'"

It works except on memo fields.

Thank you.

[Armando]

Moises:

This is may code, the CLI_NOM field is a TEXT type field.

Code: Select all  Expand view

STATIC FUNCTION FilNom(oDlg,oBrw,aGets)
    cFilNom := ALLTRIM(cFilNom)

    IF ! EMPTY(cFilNom)
        IF oRsPry:BOF() .AND. oRsPry:EOF()
            MsgInfo("No hay obras donde buscar !",oApp:cAplicacion)
        ELSE
            oRsPry:Filter := "CLI_NOM LIKE " + "'%" + cFilNom + "%'"
            IF oRsPry:BOF() .AND. oRsPry:EOF()
                MsgInfo("Cliente " + cFilNom + " no localizado !",oApp:cAplicacion)
                oRsPry:Filter := adFilterNone
            ENDIF
        ENDIF
    ELSE
        oRsPry:Filter := adFilterNone
    ENDIF

    oBrw:Refresh()
    oDlg:Update()
    cFilNom := SPACE(40)
    aGets[02]:oJump := oBrw
RETURN(.T.)
 

Regards

[MOISES]

Yes, in text fields works, but not in memo fields, where I have the issue.

Thank you.

[MOISES]

Up!

[nageswaragunupudi]

There is not Memo Field type in any sql database, MSAccess, MySql or MSSql
In MSAccess, MySql, MSSql the field type is TEXT.
In Oracle the field type is also called CLOB

[MOISES]

Yes, I mean long text or blob fields.

In such fields, this line does not work:

Code: Select all  Expand view

cFilter := cFieldName + space(1) + "LIKE" + space(1) + "'%"+alltrim(cUserText)+"%'"

[nageswaragunupudi]

In our tests, filters on LONGTEXT fields are working.

This is the test program:

Code: Select all  Expand view

#include "fivewin.ch"

function Main()

   local oCn, cSql, oRs

   oCn   := FW_DemoDB( "ADO" )

   TRY
      oCn:Execute( "DROP TABLE testmemo" )
   CATCH
   END

TEXT INTO cSql
CREATE TABLE testmemo (
  id INT AUTO_INCREMENT PRIMARY KEY
 ,name VARCHAR(10)
 ,memo LONGTEXT
)
ENDTEXT

   oCn:Execute( cSql )

TEXT INTO cSql
INSERT INTO testmemo ( name, memo ) VALUES
  ( "AAA", "one two three" ),
  ( "BBB", "three four five" ),
  ( "CCC", "five six seven" )
ENDTEXT

   oCn:Execute( cSql )

   oRs   := FW_OpenRecordSet( oCn, "testmemo" )
   ? oRs:RecordCount()  // ----------------------> 3
   oRs:Filter := "MEMO LIKE %three%"
   oRs:MoveFirst()
   ? oRs:RecordCount()  // ----------------------> 2 : Filter success

   oRs:Close()
   oCn:Close()

return nil
 

[MOISES]

My mistake: I was using oBrw:oRs instead of oRs with all fields.

Thank you.