IDS Forum

Re: RES: Collation Type doubts

Posted By: John Miller iii
Date: Wednesday, 20 July 2011, at 11:38 a.m.

In Response To: RES: Collation Type doubts (Alberto Romeu Pessonio Filho)

You are probably executing the wrong function. This is generally due t=
o
the implicit cast of data types. What is the column type of descricao?=

If it is not ichar then you have a problem. Try the following:

select * from tbdominiotiss where descricao::ichar like "%procedimento%=
"

If descricao is already an ichar then make sure you dropped the cast wh=
ich
are automatically created when you create an implicit type.

John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)

ids-bounces@iiug.org wrote on 07/20/2011 07:46:00 AM:

> From: "Alberto Romeu Pessonio Filho" <arfilho@orizonbrasil.com.br>
> To: ids@iiug.org
> Date: 07/20/2011 07:46 AM
> Subject: RES: Collation Type doubts [24412]
> Sent by: ids-bounces@iiug.org
>
> Hi John!!!
>
> Im my case, I've created the function that you sent me and the functi=
on
is
> working exactly like the function "LIKE".
>
> Look the examples:
>
> Table tbdominiotiss:
>
> I need to return 174 rows with this words in case sensitive:
>
> select * from tbdominiotiss where descricao like '%procedimento%'
> union all
> select * from tbdominiotiss where descricao like '%Procedimento%'
> union all
> select * from tbdominiotiss where descricao like '%PROCEDIMENTO%';
>
> When I execute:
> select * from tbdominiotiss where descricao like '%procedimento%';
> returns 113 rows
>
> When I execute:
> select * from tbdominiotiss where descricao like '%Procedimento%';
> returns 61 rows
>
> When I execute:
> select * from tbdominiotiss where descricao like '%PROCEDIMENTO%';
> returns 61 rows
>
> OK... The function was published succesfully, and look the output of =
the
> executions using "matches" function:
>
> When I execute:
> select * from tbdominiotiss where descricao matches "*procedimento*"
> Returns 113 rows
>
> When I execute:
> select * from tbdominiotiss where descricao matches "*Procedimento*"
> Returns 61 rows
>
> The expected result must be 174 rows for any query using matches, ins=
't
it?
>
> Tks,
> Alberto.
>
> -----Mensagem original-----
> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Joh=
n
Miller
> iii
> Enviada em: ter=E7a-feira, 19 de julho de 2011 21:11
> Para: ids@iiug.org
> Assunto: Re: Collation Type doubts [24409]
>
> Here is what you are looking to do. Please check the code for your se=
lf
as
> I
> just threw this together. I did make this a little more complicated a=
s I
> used
> the UDR fast path execute model to exeute the matches code faster. Th=
is
> saves the overhead of the SQL code for simple udr executtion. You can=

> make this run even faster by saving the handle to the udr in session
memory
> the first time and not retrieving it on every evaluation, but that is=

> another
> exercise.
>
> Of course you can just move to version 11.70 and all this is already
there
> -:)
>
> drop database if exists d1;
>
> create database d1 with log;
> {*
> **********************************
> **** TIME ZONE ****
> ***********************************
> *}
>
> CREATE DISTINCT TYPE ichar AS lvarchar;
>
> DROP CAST( lvarchar as ichar );
> DROP CAST( ichar as lvarchar );
>
> CREATE FUNCTION matches(ichar, char(20))
>
> RETURNS BOOLEAN
>
> WITH (NOT VARIANT)
>
> EXTERNAL NAME '$INFORMIXDIR/lib/c_udr_lib.so(jfm_imatch)'
>
> LANGUAGE C;
>
> create table t1
>
> (
>
> c1 serial,
>
> c2 ichar,
>
> c3 lvarchar
>
> );
>
> insert into t1 values (0,"JOHN","John");
> insert into t1 values (0,"MILLER","miller");
> insert into t1 values (0,"JOHN","miller");
>
> select * from t1 where c2 matches "mill*";
>
> {*
> *************************
> **** OUTPUT ******
> *************************
> c1 2
> c2 MILLER
> c3 miller
>
> 1 row(s) retrieved.
>
> *}
>
> mi_boolean
> jfm_imatch(mi_lvarchar *v1,mi_lvarchar *v2, MI_FPARAM *fparam)
> {
>
> mi_string *s1,*s2;
>
> MI_FUNC_DESC *funcdesc=3DNULL;
>
> mi_string funcsig[2*IDENTSIZE+20];
>
> char msg[3*IDENTSIZE+20];
>
> MI_CONNECTION *conn =3D NULL;
>
> MI_DATUM funcresult;
>
> int funcerror;
>
> mi_string *str1,*str2;
>
> conn =3D mi_open(NULL, NULL, NULL);
>
> if (conn =3D=3D NULL)
>
> mi_db_error_raise(NULL, MI_EXCEPTION, "Bad connection");
>
> if ( mi_switch_mem_duration(PER_ROUTINE) =3D=3D MI_ERROR ||
>
> mi_fp_argisnull(fparam,0) =3D=3D MI_TRUE ||
>
> mi_fp_argisnull(fparam,1) =3D=3D MI_TRUE ||
>
> ( s1 =3D mi_lvarchar_to_string(v1)) =3D=3D NULL ||
>
> ( s2 =3D mi_lvarchar_to_string(v2)) =3D=3D NULL )
>
> {
>
> mi_fp_setreturnisnull(fparam, 0 , MI_TRUE);
>
> return 0;
>
> }
>
> rdownshift(s1);
>
> rdownshift(s2);
>
> str1 =3D mi_string_to_lvarchar(s1);
>
> str2 =3D mi_string_to_lvarchar(s2);
>
> if ( str1=3D=3DNULL || str2 =3D=3D NULL )
>
> mi_db_error_raise(NULL, MI_EXCEPTION, "Out of memory.");
>
> sprintf(funcsig, "matches(lvarchar,lvarchar)");
>
> if((funcdesc=3Dmi_routine_get(conn, 0, funcsig)) =3D=3D (MI_FUNC_DESC=
*)NULL)
>
> {
>
> snprintf(msg, sizeof(msg),"Unable to find routine [%s].",funcsig);
>
> mi_db_error_raise(NULL, MI_EXCEPTION, msg);
>
> }
>
> /* =3D=3D=3D=3D=3D 3. Execute the function */
>
> funcresult =3D mi_routine_exec(conn, funcdesc, &funcerror, str1, str2=
);
>
> if (funcerror =3D=3D MI_ERROR)
>
> {
>
> if (funcdesc) mi_routine_end(conn, funcdesc);
>
> snprintf(msg, sizeof(msg), "Unable to execute function [ %s ] ",
> funcsig);
>
> mi_db_error_raise(NULL, MI_EXCEPTION, msg);
>
> }
>
> if (funcdesc) mi_routine_end(conn, funcdesc);
>
> return (mi_boolean)funcresult;
> }
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 07/19/2011 05:38:32 AM:
>
> > From: "ALBERTO ROMEU PESSONIO FILHO" <arfilho@orizonbrasil.com.br>
> > To: ids@iiug.org
> > Date: 07/19/2011 05:39 AM
> > Subject: Re: Collation Type doubts [24405]
> > Sent by: ids-bounces@iiug.org
> >
> > Hi John!
> >
> > I've found your C sample code. Check if is this code:
> >
> > mi_integer
> >
> > jfm_icompare(mi_lvarchar *v1,mi_lvarchar *v2,MI_FPARAM *fparam) {
> >
> > mi_string *s1,*s2;
> >
> > if ( mi_switch_mem_duration(PER_ROUTINE)=3D=3DMI_ERROR ||
> >
> > ( s1 =3D mi_lvarchar_to_string(v1)) =3D=3D NULL ||
> >
> > ( s2 =3D mi_lvarchar_to_string(v2)) =3D=3D NULL )
> >
> > {
> >
> > mi_fp_setreturnisnull(fparam, 0 , MI_TRUE);
> >
> > return 0;
> >
> > }
> >
> > return strcasecmp(s1,s2);
> > }
> >
> > mi_boolean
> >
> > jfm_iequal(mi_lvarchar *v1,mi_lvarchar *v2, MI_FPARAM *fparam) {
> >
> > return (mi_boolean) (jfm_icompare(v1,v2,fparam)=3D=3D0 ?
> >
> > MI_TRUE : MI_FALSE);
> > }
> > mi_boolean
> >
> > jfm_inotequal(mi_lvarchar *v1,mi_lvarchar *v2, MI_FPARAM *fparam) {=

> >
> > return (mi_boolean) (jfm_icompare(v1,v2,fparam)!=3D0 ?
> >
> > MI_TRUE : MI_FALSE );
> > }
> >
> > Ok... I tested your first example described on the presentation(tha=
t
> seemed
> > more clear to me... lol...) But could you give me an example of how=
can
I
> use
> > that function to transform the string that I gave as an argument in=

case
> > insensitive, based on the query that I listed below?
> >
> > Select * from tbdominiotiss where descricao like '%Procedimento%'
> >
> > I wonder how this is pretty easy for you, but this is my first time=

using
> UDR
> > routines. So please, show me how to use.
> >
> > Thanks in advance,
> > Alberto Pessonio.
> >
> >
> >
>
>
>
***********************************************************************=
********

>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=

> >
>
>
>
***********************************************************************=
********

> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
***********************************************************************=
********

> Forum Note: Use "Reply" to post a response in the discussion forum.=

>=

Messages In This Thread

• Collation Type doubts
  ALBERTO ROMEU PESSONIO FILHO -- Monday, 18 July 2011, at 10:53 a.m.
  • Re: Collation Type doubts
    Art Kagel -- Monday, 18 July 2011, at 1:22 p.m.
    • Re: Collation Type doubts
      John Miller iii -- Monday, 18 July 2011, at 2:52 p.m.
      • Re: Collation Type doubts
        Art Kagel -- Monday, 18 July 2011, at 3:11 p.m.
        • Re: Collation Type doubts
          ALBERTO ROMEU PESSONIO FILHO -- Tuesday, 19 July 2011, at 8:38 a.m.
          • Re: Collation Type doubts
            John Miller iii -- Tuesday, 19 July 2011, at 8:10 p.m.
            • RES: Collation Type doubts
              Alberto Romeu Pessonio Filho -- Wednesday, 20 July 2011, at 10:45 a.m.
              • Re: RES: Collation Type doubts
                John Miller iii -- Wednesday, 20 July 2011, at 11:38 a.m.
                • RES: RES: Collation Type doubts
                  Alberto Romeu Pessonio Filho -- Wednesday, 20 July 2011, at 2:06 p.m.
                  • Re: RES: RES: Collation Type doubts
                    John Miller iii -- Wednesday, 20 July 2011, at 3:23 p.m.
                    • RES: RES: RES: Collation Type doubts
                      Alberto Romeu Pessonio Filho -- Thursday, 21 July 2011, at 7:02 a.m.
      • RES: Collation Type doubts
        Alberto Romeu Pessonio Filho -- Tuesday, 19 July 2011, at 9:50 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.