Collation Type doubts
Problem: on IDS 11.50 (Linux, en_us.819 database), LIKE searches are case sensitive, so '%procedimento%' and '%Procedimento%' return different row sets; the poster wanted one case-insensitive search returning all 174 rows. Art Kagel listed four workarounds: store a duplicate upper/lower-case column, use a functional index (won't help with leading wildcards), use the Basic Text Search datablade, or create a UDT with case-insensitive comparison functions. John Miller pointed to his 2010 IIUG C-UDR presentation and posted sample code creating a distinct type 'ichar' plus a C 'matches' function (also noting 11.70 supports this natively). The poster reported the function still behaved case-sensitively on his table (his column was not defined as the new type); no final resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi folks!
IDS 11.50 FC7W1GE
Linux RedHat 64 x86_64 platform
I'm having an issue when I'm trying to select data using like clause.
---------------------------------------------------------------
Scenario:
- Statement 1:
select * from tbdominiotiss
where descricao like '%procedimento%';
Returns 113 rows. - OK
- Statement 2:
select * from tbdominiotiss
where descricao like '%Procedimento%';
Returns 61 rows - OK
-----------------------------------------------------------
The Collate of Database is: en_us.819
The Collate of client is en_us.CP1252
This is our Environment Variables:
[informix@valencia ~]$ onstat -g env
IBM Informix Dynamic Server Version 11.50.FC7W1GE -- On-Line -- Up 34 days
02:37:52 -- 14779012 Kbytes
Server start-up environment:
Variable Value [values-list]
DBDATE DMY4/
DBDELIMITER |
DBPATH .
DBPRINT lp -s
DBTEMP /tmp
DBUPSPACE 100000:50
INFORMIXDIR /opt/IBM/informix
[/opt/IBM/informix]
[/usr/informix]
INFORMIXSERVER ifxorizon1
INFORMIXSQLHOSTS /opt/IBM/informix/etc/sqlhosts
INFORMIXTERM terminfo
LANG en_US.UTF-8
LC_COLLATE en_US.UTF-8
LC_CTYPE en_US.UTF-8
LC_MONETARY en_US.UTF-8
LC_NUMERIC en_US.UTF-8
LC_TIME en_US.UTF-8
LKNOTIFY yes
LOCKDOWN no
NODEFDAC no
ONCONFIG onconfig.ifxorizon1
PATH /opt/IBM/informix/bin:/home/informix/bin:/usr/kerberos/bin:
/usr/local/bin:/bin:/usr/bin:/sbin:/usr/kerberos/sbin:/usr
/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/u
sr/sbin:/usr/bin:/root/bin:/opt/IBM/informix/bin:/home/inf
ormix/util:/opt/IBM/csdk/bin
SERVER_LOCALE en_US.819
SHELL /bin/bash
TERM xterm
[xterm]
[dumb]
TERMCAP /etc/termcap.
I need to transform any key word of my query in case insensitive, to bring
113+61 = 174 rows, instead of IDS bring me rows using case sensitive filter.
Can anybody help me?
Regards,
Alberto.
Informix 11.50 does not support case insensitive filters directly. You have
several options arranged in increasing utility:
1. Store a copy of the column mapped to all upper or all lower case and
filter on that.
2. Create a function index on the column using a function that returns
the string mapped to all upper or all lower case. Then you can filter using
that function - but it won't support LIKE comparisons that start with a
wildcard character (_ or %).
3. Use the Basic Text Search datablade to index the column and perform
comparisons using the BTS facilities.
4. Create a new User Defined Type that provides case insensitive
comparison functions and create the table with the columns that need that
requirement using the new UDT.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Mon, Jul 18, 2011 at 10:53 AM, ALBERTO ROMEU PESSONIO FILHO <
arfilho@orizonbrasil.com.br> wrote:
> Hi folks!
>
> IDS 11.50 FC7W1GE
> Linux RedHat 64 x86_64 platform
>
> I'm having an issue when I'm trying to select data using like clause.
> ---------------------------------------------------------------
> Scenario:
>
> - Statement 1:
>
> select * from tbdominiotiss
> where descricao like '%procedimento%';>
> Returns 113 rows. - OK
>
> - Statement 2:
>
> select * from tbdominiotiss
> where descricao like '%Procedimento%';>
> Returns 61 rows - OK
>
> -----------------------------------------------------------
>
> The Collate of Database is: en_us.819
> The Collate of client is en_us.CP1252
>
> This is our Environment Variables:
>
> [informix@valencia ~]$ onstat -g env
>
> IBM Informix Dynamic Server Version 11.50.FC7W1GE -- On-Line -- Up 34 days
> 02:37:52 -- 14779012 Kbytes>
> Server start-up environment:
>
> Variable Value [values-list]
> DBDATE DMY4/
> DBDELIMITER |
> DBPATH .
> DBPRINT lp -s
> DBTEMP /tmp
> DBUPSPACE 100000:50
> INFORMIXDIR /opt/IBM/informix
>
> [/opt/IBM/informix]
>
> [/usr/informix]
> INFORMIXSERVER ifxorizon1
> INFORMIXSQLHOSTS /opt/IBM/informix/etc/sqlhosts
> INFORMIXTERM terminfo
> LANG en_US.UTF-8
> LC_COLLATE en_US.UTF-8
> LC_CTYPE en_US.UTF-8
> LC_MONETARY en_US.UTF-8
> LC_NUMERIC en_US.UTF-8
> LC_TIME en_US.UTF-8
> LKNOTIFY yes
> LOCKDOWN no
> NODEFDAC no
> ONCONFIG onconfig.ifxorizon1
> PATH /opt/IBM/informix/bin:/home/informix/bin:/usr/kerberos/bin:
>
> /usr/local/bin:/bin:/usr/bin:/sbin:/usr/kerberos/sbin:/usr
>
> /kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/u
>
> sr/sbin:/usr/bin:/root/bin:/opt/IBM/informix/bin:/home/inf
>
> ormix/util:/opt/IBM/csdk/bin
> SERVER_LOCALE en_US.819
> SHELL /bin/bash
> TERM xterm
>
> [xterm]
>
> [dumb]
> TERMCAP /etc/termcap.
>
> I need to transform any key word of my query in case insensitive, to bring
> 113+61 = 174 rows, instead of IDS bring me rows using case sensitive
> filter.
>
> Can anybody help me?
>
> Regards,
> Alberto.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf307d00f0a2444a04a85b3ecd
while item #4 sounds hard, it is actually pretty ease. I have provided
the code to complete this in presentations at the 2010 IIUG conference.
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/18/2011 10:22:36 AM:
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 07/18/2011 10:23 AM
> Subject: Re: Collation Type doubts [24399]
> Sent by: ids-bounces@iiug.org
>
> Informix 11.50 does not support case insensitive filters directly. You
have
> several options arranged in increasing utility:
>
> 1. Store a copy of the column mapped to all upper or all lower case and
>
> filter on that.
>
> 2. Create a function index on the column using a function that returns
>
> the string mapped to all upper or all lower case. Then you can filter
using
>
> that function - but it won't support LIKE comparisons that start with a
>
> wildcard character (_ or %).
>
> 3. Use the Basic Text Search datablade to index the column and perform
>
> comparisons using the BTS facilities.
>
> 4. Create a new User Defined Type that provides case insensitive
>
> comparison functions and create the table with the columns that need that
>
> requirement using the new UDT.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other
> organization with which I am associated either explicitly, implicitly, or
by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Mon, Jul 18, 2011 at 10:53 AM, ALBERTO ROMEU PESSONIO FILHO <
> arfilho@orizonbrasil.com.br> wrote:
>
> > Hi folks!
> >
> > IDS 11.50 FC7W1GE
> > Linux RedHat 64 x86_64 platform
> >
> > I'm having an issue when I'm trying to select data using like clause.
> > ---------------------------------------------------------------
> > Scenario:
> >
> > - Statement 1:
> >
> > select * from tbdominiotiss
> > where descricao like '%procedimento%';> >
> > Returns 113 rows. - OK
> >
> > - Statement 2:
> >
> > select * from tbdominiotiss
> > where descricao like '%Procedimento%';> >
> > Returns 61 rows - OK
> >
> > -----------------------------------------------------------
> >
> > The Collate of Database is: en_us.819
> > The Collate of client is en_us.CP1252
> >
> > This is our Environment Variables:
> >
> > [informix@valencia ~]$ onstat -g env
> >
> > IBM Informix Dynamic Server Version 11.50.FC7W1GE -- On-Line -- Up 34days
> > 02:37:52 -- 14779012 Kbytes
> >
> > Server start-up environment:
> >
> > Variable Value [values-list]
> > DBDATE DMY4/
> > DBDELIMITER |
> > DBPATH .
> > DBPRINT lp -s
> > DBTEMP /tmp
> > DBUPSPACE 100000:50
> > INFORMIXDIR /opt/IBM/informix
> >
> > [/opt/IBM/informix]
> >
> > [/usr/informix]
> > INFORMIXSERVER ifxorizon1
> > INFORMIXSQLHOSTS /opt/IBM/informix/etc/sqlhosts
> > INFORMIXTERM terminfo
> > LANG en_US.UTF-8
> > LC_COLLATE en_US.UTF-8
> > LC_CTYPE en_US.UTF-8
> > LC_MONETARY en_US.UTF-8
> > LC_NUMERIC en_US.UTF-8
> > LC_TIME en_US.UTF-8
> > LKNOTIFY yes
> > LOCKDOWN no
> > NODEFDAC no
> > ONCONFIG onconfig.ifxorizon1
> > PATH /opt/IBM/informix/bin:/home/informix/bin:/usr/kerberos/bin:
> >
> > /usr/local/bin:/bin:/usr/bin:/sbin:/usr/kerberos/sbin:/usr
> >
> > /kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/u
> >
> > sr/sbin:/usr/bin:/root/bin:/opt/IBM/informix/bin:/home/inf
> >
> > ormix/util:/opt/IBM/csdk/bin
> > SERVER_LOCALE en_US.819
> > SHELL /bin/bash
> > TERM xterm
> >
> > [xterm]
> >
> > [dumb]
> > TERMCAP /etc/termcap.
> >
> > I need to transform any key word of my query in case insensitive, to
bring
> > 113+61 = 174 rows, instead of IDS bring me rows using case sensitive
> > filter.
> >
> > Can anybody help me?
> >
> > Regards,
> > Alberto.
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --20cf307d00f0a2444a04a85b3ecd
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
The presentation John refers to is downloadable from the IIUG Member's
pages. It was presented at the 2010 Conference and was entitled
"E13-Miller-The_Basics_of_Writing_and_Utilizing_C-User_Defined_Routines.pdf".
Actually, that presentation also contains instructions for doing #'s 2 & 3
as well as #4.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Mon, Jul 18, 2011 at 2:52 PM, John Miller iii <miller3@us.ibm.com> wrote:
> while item #4 sounds hard, it is actually pretty ease. I have provided
> the code to complete this in presentations at the 2010 IIUG conference.
>
> 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/18/2011 10:22:36 AM:
>
> > From: "Art Kagel" <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Date: 07/18/2011 10:23 AM
> > Subject: Re: Collation Type doubts [24399]
> > Sent by: ids-bounces@iiug.org
> >
> > Informix 11.50 does not support case insensitive filters directly. You
> have
> > several options arranged in increasing utility:
> >
> > 1. Store a copy of the column mapped to all upper or all lower case and
> >
> > filter on that.
> >
> > 2. Create a function index on the column using a function that returns
> >
> > the string mapped to all upper or all lower case. Then you can filter
> using
> >
> > that function - but it won't support LIKE comparisons that start with a
> >
> > wildcard character (_ or %).
> >
> > 3. Use the Basic Text Search datablade to index the column and perform
> >
> > comparisons using the BTS facilities.
> >
> > 4. Create a new User Defined Type that provides case insensitive
> >
> > comparison functions and create the table with the columns that need that
>
> >
> > requirement using the new UDT.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > organization with which I am associated either explicitly, implicitly, or
> by
> > inference. Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Mon, Jul 18, 2011 at 10:53 AM, ALBERTO ROMEU PESSONIO FILHO <
> > arfilho@orizonbrasil.com.br> wrote:
> >
> > > Hi folks!
> > >
> > > IDS 11.50 FC7W1GE
> > > Linux RedHat 64 x86_64 platform
> > >
> > > I'm having an issue when I'm trying to select data using like clause.
> > > ---------------------------------------------------------------
> > > Scenario:
> > >
> > > - Statement 1:
> > >
> > > select * from tbdominiotiss
> > > where descricao like '%procedimento%';> > >
> > > Returns 113 rows. - OK
> > >
> > > - Statement 2:
> > >
> > > select * from tbdominiotiss
> > > where descricao like '%Procedimento%';> > >
> > > Returns 61 rows - OK
> > >
> > > -----------------------------------------------------------
> > >
> > > The Collate of Database is: en_us.819
> > > The Collate of client is en_us.CP1252
> > >
> > > This is our Environment Variables:
> > >
> > > [informix@valencia ~]$ onstat -g env
> > >
> > > IBM Informix Dynamic Server Version 11.50.FC7W1GE -- On-Line -- Up 34> days
> > > 02:37:52 -- 14779012 Kbytes
> > >
> > > Server start-up environment:
> > >
> > > Variable Value [values-list]
> > > DBDATE DMY4/
> > > DBDELIMITER |
> > > DBPATH .
> > > DBPRINT lp -s
> > > DBTEMP /tmp
> > > DBUPSPACE 100000:50
> > > INFORMIXDIR /opt/IBM/informix
> > >
> > > [/opt/IBM/informix]
> > >
> > > [/usr/informix]
> > > INFORMIXSERVER ifxorizon1
> > > INFORMIXSQLHOSTS /opt/IBM/informix/etc/sqlhosts
> > > INFORMIXTERM terminfo
> > > LANG en_US.UTF-8
> > > LC_COLLATE en_US.UTF-8
> > > LC_CTYPE en_US.UTF-8
> > > LC_MONETARY en_US.UTF-8
> > > LC_NUMERIC en_US.UTF-8
> > > LC_TIME en_US.UTF-8
> > > LKNOTIFY yes
> > > LOCKDOWN no
> > > NODEFDAC no
> > > ONCONFIG onconfig.ifxorizon1
> > > PATH /opt/IBM/informix/bin:/home/informix/bin:/usr/kerberos/bin:
> > >
> > > /usr/local/bin:/bin:/usr/bin:/sbin:/usr/kerberos/sbin:/usr
> > >
> > > /kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/u
> > >
> > > sr/sbin:/usr/bin:/root/bin:/opt/IBM/informix/bin:/home/inf
> > >
> > > ormix/util:/opt/IBM/csdk/bin
> > > SERVER_LOCALE en_US.819
> > > SHELL /bin/bash
> > > TERM xterm
> > >
> > > [xterm]
> > >
> > > [dumb]
> > > TERMCAP /etc/termcap.
> > >
> > > I need to transform any key word of my query in case insensitive, to
> bring
> > > 113+61 = 174 rows, instead of IDS bring me rows using case sensitive
> > > filter.
> > >
> > > Can anybody help me?
> > >
> > > Regards,
> > > Alberto.
> > >
> > >
> > >
> > >
> >
>
>
>
*******************************************************************************
>
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --20cf307d00f0a2444a04a85b3ecd
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec517ce3cdced3304a85cc451
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)==MI_ERROR ||
( s1 = mi_lvarchar_to_string(v1)) == NULL ||
( s2 = mi_lvarchar_to_string(v2)) == 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)==0 ?
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)!=0 ?
MI_TRUE : MI_FALSE );
}
Ok... I tested your first example described on the presentation(that 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.
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)==MI_ERROR ||
( s1 = mi_lvarchar_to_string(v1)) == NULL ||
( s2 = mi_lvarchar_to_string(v2)) == 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)==0 ?
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)!=0 ?
MI_TRUE : MI_FALSE );
}
Ok... I tested your first example (that seemed more clear to me...
lol...)
But could you give an example of how can I use that function to
transform my search in case insensitive, based on the query that I
listed below?
Thanks in advance,
Alberto Pessonio.
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de John
Miller iii
Enviada em: segunda-feira, 18 de julho de 2011 15:52
Para: ids@iiug.org
Assunto: Re: Collation Type doubts [24400]
while item #4 sounds hard, it is actually pretty ease. I have provided
the code to complete this in presentations at the 2010 IIUG conference.
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/18/2011 10:22:36 AM:
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 07/18/2011 10:23 AM
> Subject: Re: Collation Type doubts [24399]
> Sent by: ids-bounces@iiug.org
>
> Informix 11.50 does not support case insensitive filters directly. You
have
> several options arranged in increasing utility:
>
> 1. Store a copy of the column mapped to all upper or all lower case
and
>
> filter on that.
>
> 2. Create a function index on the column using a function that returns
>
> the string mapped to all upper or all lower case. Then you can filter
using
>
> that function - but it won't support LIKE comparisons that start with
a
>
> wildcard character (_ or %).
>
> 3. Use the Basic Text Search datablade to index the column and perform
>
> comparisons using the BTS facilities.
>
> 4. Create a new User Defined Type that provides case insensitive
>
> comparison functions and create the table with the columns that need
that
>
> requirement using the new UDT.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own
opinions
and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other
> organization with which I am associated either explicitly, implicitly,
or
by
> inference. Neither do those opinions reflect those of other
individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Mon, Jul 18, 2011 at 10:53 AM, ALBERTO ROMEU PESSONIO FILHO <
> arfilho@orizonbrasil.com.br> wrote:
>
> > Hi folks!
> >
> > IDS 11.50 FC7W1GE
> > Linux RedHat 64 x86_64 platform
> >
> > I'm having an issue when I'm trying to select data using like
clause.
> > ---------------------------------------------------------------
> > Scenario:
> >
> > - Statement 1:
> >
> > select * from tbdominiotiss
> > where descricao like '%procedimento%';> >
> > Returns 113 rows. - OK
> >
> > - Statement 2:
> >
> > select * from tbdominiotiss
> > where descricao like '%Procedimento%';> >
> > Returns 61 rows - OK
> >
> > -----------------------------------------------------------
> >
> > The Collate of Database is: en_us.819
> > The Collate of client is en_us.CP1252
> >
> > This is our Environment Variables:
> >
> > [informix@valencia ~]$ onstat -g env
> >
> > IBM Informix Dynamic Server Version 11.50.FC7W1GE -- On-Line -- Up34
days
> > 02:37:52 -- 14779012 Kbytes
> >
> > Server start-up environment:
> >
> > Variable Value [values-list]
> > DBDATE DMY4/
> > DBDELIMITER |
> > DBPATH .
> > DBPRINT lp -s
> > DBTEMP /tmp
> > DBUPSPACE 100000:50
> > INFORMIXDIR /opt/IBM/informix
> >
> > [/opt/IBM/informix]
> >
> > [/usr/informix]
> > INFORMIXSERVER ifxorizon1
> > INFORMIXSQLHOSTS /opt/IBM/informix/etc/sqlhosts
> > INFORMIXTERM terminfo
> > LANG en_US.UTF-8
> > LC_COLLATE en_US.UTF-8
> > LC_CTYPE en_US.UTF-8
> > LC_MONETARY en_US.UTF-8
> > LC_NUMERIC en_US.UTF-8
> > LC_TIME en_US.UTF-8
> > LKNOTIFY yes
> > LOCKDOWN no
> > NODEFDAC no
> > ONCONFIG onconfig.ifxorizon1
> > PATH /opt/IBM/informix/bin:/home/informix/bin:/usr/kerberos/bin:
> >
> > /usr/local/bin:/bin:/usr/bin:/sbin:/usr/kerberos/sbin:/usr
> >
> > /kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/u
> >
> > sr/sbin:/usr/bin:/root/bin:/opt/IBM/informix/bin:/home/inf
> >
> > ormix/util:/opt/IBM/csdk/bin
> > SERVER_LOCALE en_US.819
> > SHELL /bin/bash
> > TERM xterm
> >
> > [xterm]
> >
> > [dumb]
> > TERMCAP /etc/termcap.
> >
> > I need to transform any key word of my query in case insensitive, to
bring
> > 113+61 = 174 rows, instead of IDS bring me rows using case sensitive
> > filter.
> >
> > Can anybody help me?
> >
> > Regards,
> > Alberto.
> >
> >
> >
> >
>
************************************************************************
*******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --20cf307d00f0a2444a04a85b3ecd
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Here is what you are looking to do. Please check the code for your self as
I
just threw this together. I did make this a little more complicated as I
used
the UDR fast path execute model to exeute the matches code faster. This
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=NULL;
mi_string funcsig[2*IDENTSIZE+20];
char msg[3*IDENTSIZE+20];
MI_CONNECTION *conn = NULL;
MI_DATUM funcresult;
int funcerror;
mi_string *str1,*str2;
conn = mi_open(NULL, NULL, NULL);
if (conn == NULL)
mi_db_error_raise(NULL, MI_EXCEPTION, "Bad connection");
if ( mi_switch_mem_duration(PER_ROUTINE) == MI_ERROR ||
mi_fp_argisnull(fparam,0) == MI_TRUE ||
mi_fp_argisnull(fparam,1) == MI_TRUE ||
( s1 = mi_lvarchar_to_string(v1)) == NULL ||
( s2 = mi_lvarchar_to_string(v2)) == NULL )
{
mi_fp_setreturnisnull(fparam, 0 , MI_TRUE);
return 0;
}
rdownshift(s1);
rdownshift(s2);
str1 = mi_string_to_lvarchar(s1);
str2 = mi_string_to_lvarchar(s2);
if ( str1==NULL || str2 == NULL )
mi_db_error_raise(NULL, MI_EXCEPTION, "Out of memory.");
sprintf(funcsig, "matches(lvarchar,lvarchar)");
if((funcdesc=mi_routine_get(conn, 0, funcsig)) == (MI_FUNC_DESC *)NULL)
{
snprintf(msg, sizeof(msg),"Unable to find routine [%s].",funcsig);
mi_db_error_raise(NULL, MI_EXCEPTION, msg);
}
/* ===== 3. Execute the function */
funcresult = mi_routine_exec(conn, funcdesc, &funcerror, str1, str2);
if (funcerror == 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)==MI_ERROR ||
>
> ( s1 = mi_lvarchar_to_string(v1)) == NULL ||
>
> ( s2 = mi_lvarchar_to_string(v2)) == 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)==0 ?
>
> 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)!=0 ?
>
> MI_TRUE : MI_FALSE );
> }
>
> Ok... I tested your first example described on the presentation(that
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.
>
Hi John!!!
Im my case, I've created the function that you sent me and the function 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 John Miller
iii
Enviada em: terça-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 self as
I
just threw this together. I did make this a little more complicated as I
used
the UDR fast path execute model to exeute the matches code faster. This
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=NULL;
mi_string funcsig[2*IDENTSIZE+20];
char msg[3*IDENTSIZE+20];
MI_CONNECTION *conn = NULL;
MI_DATUM funcresult;
int funcerror;
mi_string *str1,*str2;
conn = mi_open(NULL, NULL, NULL);
if (conn == NULL)
mi_db_error_raise(NULL, MI_EXCEPTION, "Bad connection");
if ( mi_switch_mem_duration(PER_ROUTINE) == MI_ERROR ||
mi_fp_argisnull(fparam,0) == MI_TRUE ||
mi_fp_argisnull(fparam,1) == MI_TRUE ||
( s1 = mi_lvarchar_to_string(v1)) == NULL ||
( s2 = mi_lvarchar_to_string(v2)) == NULL )
{
mi_fp_setreturnisnull(fparam, 0 , MI_TRUE);
return 0;
}
rdownshift(s1);
rdownshift(s2);
str1 = mi_string_to_lvarchar(s1);
str2 = mi_string_to_lvarchar(s2);
if ( str1==NULL || str2 == NULL )
mi_db_error_raise(NULL, MI_EXCEPTION, "Out of memory.");
sprintf(funcsig, "matches(lvarchar,lvarchar)");
if((funcdesc=mi_routine_get(conn, 0, funcsig)) == (MI_FUNC_DESC *)NULL)
{
snprintf(msg, sizeof(msg),"Unable to find routine [%s].",funcsig);
mi_db_error_raise(NULL, MI_EXCEPTION, msg);
}
/* ===== 3. Execute the function */
funcresult = mi_routine_exec(conn, funcdesc, &funcerror, str1, str2);
if (funcerror == 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)==MI_ERROR ||
>
> ( s1 = mi_lvarchar_to_string(v1)) == NULL ||
>
> ( s2 = mi_lvarchar_to_string(v2)) == 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)==0 ?
>
> 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)!=0 ?
>
> MI_TRUE : MI_FALSE );
> }
>
> Ok... I tested your first example described on the presentation(that
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.
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);
> >@@NL
John:
Column "descrição" is a lvarchar(500).
This statement: select * from tbdominiotiss where descricao::ichar like
"%procedimento%= "
returns 113 rows, the same as whe I was using like clause.
[]'s
Alberto.
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de John Miller
iii
Enviada em: quarta-feira, 20 de julho de 2011 12:38
Para: ids@iiug.org
Assunto: Re: RES: Collation Type doubts [24413]
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@@NL
In short you are getting the wrong like operator to execute. My examp=
le
was very precise. If you change the column types and cast operators th=
en
different things will happen. I had the data stored in a column of typ=
e
ichar
and remove the default casts.
Have you removed the default casts?? You will have to play around to
ensure the operator you get is the correct one.
you might try
select * from tbdominiotiss where like( descricao::ichar,
"%procedimento%"::char(20) )
or what ever operators you defined the function with.
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 11:06:13 AM:
> From: "Alberto Romeu Pessonio Filho" <arfilho@orizonbrasil.com.br>
> To: ids@iiug.org
> Date: 07/20/2011 11:07 AM
> Subject: RES: RES: Collation Type doubts [24414]
> Sent by: ids-bounces@iiug.org
>
> John:
>
> Column "descri=E7=E3o" is a lvarchar(500).
>
> This statement: select * from tbdominiotiss where descricao::ichar li=
ke
> "%procedimento%=3D "
> returns 113 rows, the same as whe I was using like clause.
>
> []'s
> Alberto.
>
> -----Mensagem original-----
> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Joh=
n
Miller
> iii
> Enviada em: quarta-feira, 20 de julho de 2011 12:38
> Para: ids@iiug.org
> Assunto: Re: RES: Collation Type doubts [24413]
>
> You are probably executing the wrong function. This is generally due =
t=3D
> o
> the implicit cast of data types. What is the column type of descricao=
?=3D
>
> If it is not ichar then you have a problem. Try the following:
>
> select * from tbdominiotiss where descricao::ichar like "%procediment=o%=3D
> "
>
> If descricao is already an ichar then make sure you dropped the cast =
wh=3D
> 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 func=
ti=3D
> 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 o=
f =3D
> 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, i=
ns=3D
> 't
> it?
> >
> > Tks,
> > Alberto.
> >
> > -----Mensagem original-----
> > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de J=
oh=3D
> n
> Miller
> > iii
> > Enviada em: ter=3DE7a-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=3D
> lf
> as
> > I
> > just threw this together. I did make this a little more complicated=
a=3D
> s I
> > used
> > the UDR fast path execute model to exeute the matches code faster. =
Th=3D
> is
> > saves the overhead of the SQL code for simple udr executtion. You c=
an=3D
>
> > make this run even faster by saving the handle to the udr in sessio=
n
> memory
> > the first time and not retrieving it on every evaluation, but that =
is=3D
>
> > another
> > exercise.
> >
> > Of course you can just move to version 11.70 and all this is alread=
y
> 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=3D3DNULL;
> >
> > mi_string funcsig[2*IDENTSIZE+20];
> >
> > char msg[3*IDENTSIZE+20];
> >
> > MI_CONNECTION *conn =3D3D NULL;
> >
> > MI_DATUM funcresult;
> >
> > int funcerror;
> >
> > mi_string *str1,*str2;
> >
> > conn =3D3D mi_open(NULL, NULL, NULL);
> >
> > if (conn =3D3D=3D3D NULL)
> >
> > mi_db_error_raise(NULL, MI_EXCEPTION, "Bad connection");
> >
> > if ( mi_switch_mem_duration(PER_ROUTINE) =3D3D=3D3D MI_ERROR ||
> >
> > mi_fp_argisnull(fparam,0) =3D3D=3D3D MI_TRUE ||
> >
> > mi_fp_argisnull(fparam,1) =3D3D=3D3D MI_TRUE ||
> >
> > ( s1 =3D3D mi_lvarchar_to_string(v1)) =3D3D=3D3D NULL ||
> >
> > ( s2 =3D3D mi_lvarchar_to_string(v2)) =3D3D=3D3D NULL )
> >
> > {
> >
> > mi_fp_setreturnisnull(fparam, 0 , MI_TRUE);
> >@@NL
Thanks John... It solved my problem!!!
I'm planning the migration to 11.7 for the next months.
Regards,
Alberto.
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de John
Miller iii
Enviada em: quarta-feira, 20 de julho de 2011 16:24
Para: ids@iiug.org
Assunto: Re: RES: RES: Collation Type doubts [24415]
In short you are getting the wrong like operator to execute. My examp=
le
was very precise. If you change the column types and cast operators th=
en
different things will happen. I had the data stored in a column of typ=
e
ichar
and remove the default casts.
Have you removed the default casts?? You will have to play around to
ensure the operator you get is the correct one.
you might try
select * from tbdominiotiss where like( descricao::ichar,
"%procedimento%"::char(20) )
or what ever operators you defined the function with.
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 11:06:13 AM:
> From: "Alberto Romeu Pessonio Filho" <arfilho@orizonbrasil.com.br>
> To: ids@iiug.org
> Date: 07/20/2011 11:07 AM
> Subject: RES: RES: Collation Type doubts [24414]
> Sent by: ids-bounces@iiug.org
>
> John:
>
> Column "descri=E7=E3o" is a lvarchar(500).
>
> This statement: select * from tbdominiotiss where descricao::ichar li=
ke
> "%procedimento%=3D "
> returns 113 rows, the same as whe I was using like clause.
>
> []'s
> Alberto.
>
> -----Mensagem original-----
> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Joh=
n
Miller
> iii
> Enviada em: quarta-feira, 20 de julho de 2011 12:38
> Para: ids@iiug.org
> Assunto: Re: RES: Collation Type doubts [24413]
>
> You are probably executing the wrong function. This is generally due =
t=3D
> o
> the implicit cast of data types. What is the column type of descricao=
?=3D
>
> If it is not ichar then you have a problem. Try the following:
>
> select * from tbdominiotiss where descricao::ichar like "%procediment=
o%=3D
> "
>
> If descricao is already an ichar then make sure you dropped the cast =
wh=3D
> 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 func=
ti=3D
> 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 o=
f =3D
> 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, i=
ns=3D
> 't
> it?
> >
> > Tks,
> > Alberto.
> >
> > -----Mensagem original-----
> > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de J=
oh=3D
> n
> Miller
> > iii
> > Enviada em: ter=3DE7a-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=3D
> lf
> as
> > I
> > just threw this together. I did make this a little more complicated=
a=3D
> s I
> > used
> > the UDR fast path execute model to exeute the matches code faster. =
Th=3D
> is
> > saves the overhead of the SQL code for simple udr executtion. You c=
an=3D
>
> > make this run even faster by saving the handle to the udr in sessio=
n
> memory
> > the first time and not retrieving it on every evaluation, but that =
is=3D
>
> > another
> > exercise.
> >
> > Of course you can just move to version 11.70 and all this is alread=
y
> 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=3D3DNULL;
> >
> > mi_string funcsig[2*IDENTSIZE+20];
> >
> > char msg[3*IDENTSIZE+20];
> >
> > MI_CONNECTION *conn =3D3D NULL;
> >
> > MI_DATUM funcresult;
> >
> > int funcerror;
> >
> > mi_string *str1,*str2;
> >
> > conn =3D3D mi_open(NULL, NULL, NULL);
> >
> > if (conn =3D3D=3D3D NULL)
>
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g