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

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

Postby MOISES » Mon Jan 27, 2020 10:02 am

Hi,

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

Is there a way?

Thank you.
Last edited by MOISES on Sat Feb 15, 2020 4:33 pm, edited 1 time in total.
Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40
MOISES
 
Posts: 838
Joined: Wed Aug 22, 2007 10:09 am

Re: ADO: filter does not work on memo fields

Postby Armando » Mon Jan 27, 2020 5:14 pm

Moises:

It Works fine to me.

Show us your code, pls.

Regards
SOI, s.a. de c.v.
estbucarm@gmail.com
http://www.soisa.mex.tl/
http://sqlcmd.blogspot.com/
Tel. (722) 174 44 45
Carpe diem quam minimum credula postero
User avatar
Armando
 
Posts: 3201
Joined: Fri Oct 07, 2005 8:20 pm
Location: Toluca, México

Re: ADO: filter does not work on memo fields

Postby MOISES » Mon Jan 27, 2020 6:54 pm

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.
Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40
MOISES
 
Posts: 838
Joined: Wed Aug 22, 2007 10:09 am

Re: ADO: filter does not work on memo fields

Postby Armando » Mon Jan 27, 2020 9:13 pm

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
SOI, s.a. de c.v.
estbucarm@gmail.com
http://www.soisa.mex.tl/
http://sqlcmd.blogspot.com/
Tel. (722) 174 44 45
Carpe diem quam minimum credula postero
User avatar
Armando
 
Posts: 3201
Joined: Fri Oct 07, 2005 8:20 pm
Location: Toluca, México

Re: ADO: filter does not work on memo fields

Postby MOISES » Tue Jan 28, 2020 7:52 am

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

Thank you.
Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40
MOISES
 
Posts: 838
Joined: Wed Aug 22, 2007 10:09 am

Re: ADO: filter does not work on memo fields

Postby MOISES » Mon Feb 10, 2020 8:28 am

Up!
Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40
MOISES
 
Posts: 838
Joined: Wed Aug 22, 2007 10:09 am

Re: ADO: filter does not work on memo fields

Postby nageswaragunupudi » Thu Feb 13, 2020 1:47 pm

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
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: ADO: filter does not work on memo fields

Postby MOISES » Thu Feb 13, 2020 3:02 pm

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)+"%'"
Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40
MOISES
 
Posts: 838
Joined: Wed Aug 22, 2007 10:09 am

Re: ADO: filter does not work on memo fields

Postby nageswaragunupudi » Sat Feb 15, 2020 3:47 am

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
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: ADO: filter does not work on memo fields

Postby MOISES » Sat Feb 15, 2020 4:32 pm

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

Thank you.
Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40
MOISES
 
Posts: 838
Joined: Wed Aug 22, 2007 10:09 am


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 45 guests