You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Garth Keesler <ga...@gdcjk.com> on 2012/10/30 12:54:26 UTC
PG CITEXT Data Type
I'm converting an MS Access ADP project to Java/Postgresql/Cayenne (my
first and mostly for the fun of it) and am trying to figure out how to
best match the case insensitive string usage that is in SQL Server. I
created the PG database using CITEXT only where actually necessary but
it doesn't seem to be recognized in Cayenne. It appears that I need to
replace all CITEXT columns with TEXT and handle case insensitive
comparisons manually. I've found a couple of threads that mention this
but none had any real advice on how best to do it in a Cayenne
environment. Lower() is frequently mentioned as the usual approach but
before I go down that path too far I'd appreciate a little feedback from
more experienced Cayenne users.
Thanx Much,
Garth
Re: PG CITEXT Data Type
Posted by Garth Keesler <ga...@gdcjk.com>.
I did the same test and it worked for me as well.
Thanx Much,
Garth
On 11/02/2012 02:06 PM, Andrus Adamchik wrote:
> I just did a quick test on Cayenne 3.1 and PG 9.1. And all I had to do is to map the DbAttribute type as OTHER instead of VARCHAR and map ObjAttribute as java.lang.String. This did it.
>
> So after all PG's turned out to be one of the few drivers that actually handles type "OTHER" correctly, and there's no need for hacks and fancy workarounds.
>
> HTH,
> Andrus
>
>
> On Nov 1, 2012, at 11:03 PM, Garth Keesler <ga...@gdcjk.com> wrote:
>
>> I ran a couple of tests where the column is CITEXT and is indexed. The actual value in the database is "TTI" and the row was found when the parameter was "TTI" but not "tti". Is there anything special I need to do in preparing the SelectQuery?
>>
>> Here is the (so far) complete Main pretty much straight out of the tutorial:
>>
>> ServerRuntime cayenneRuntime = new ServerRuntime("cayenne-project.xml");
>> ObjectContext context = cayenneRuntime.getContext();
>> Expression qual = Expression
>> .fromString("shortvn = $svn");
>> SelectQuery proto = new SelectQuery(Vendors.class, qual);
>> proto.setDistinct(true);
>> Map params1 = new HashMap();
>> params1.put("svn", "TTI");
>> SelectQuery query1 = proto.queryWithParameters(params1);
>> List performQuery = context.performQuery(query1);
>> List<Vendors> vendors1 = performQuery;
>> System.out.println("Vendors = " + vendors1.size());
>>
>> vendors1.size is 1 when the param is uppercase, 0 when lower.
>>
>> Thanx,
>> Garth
>>
>>
>> On 10/30/2012 12:56 PM, Andrus Adamchik wrote:
>>>> all of the "special" PG data types like CITEXT are listed as "Other". I assume I should select something other than that, right?
>>> Yeah.. And I guess we may need to add CITEXT to Cayenne PG adapter so that it is reverse engineered as VARCHAR or CLOB or something.
>>>
>>> On Oct 30, 2012, at 8:06 PM, Garth Keesler <ga...@gdcjk.com> wrote:
>>>
>>>> Using 3.1B1. But your second question made me look in Modeler and all of the "special" PG data types like CITEXT are listed as "Other". I assume I should select something other than that, right? I'll give it a try and see what happens.
>>>>
>>>> Thanx...
>>>>
>>>> On 10/30/2012 11:42 AM, Andrus Adamchik wrote:
>>>>> So what version of Cayenne are you using (I'd recommend 3.1B1)?
>>>>>
>>>>> Also what type do you select for CTEXT columns in Cayenne model? Should be something like VARCHAR?
>>>>>
>>>>> Andrus
>>>>>
>>>>> On Oct 30, 2012, at 7:06 PM, Garth Keesler <ga...@gdcjk.com> wrote:
>>>>>
>>>>>> Thanx for the reply. The problem I seem to be having is that the schema-generated persistent classes have errors whenever one of the CITEXT attributes is mentioned. Following is a sample of the code which is causing errors.
>>>>>>
>>>>>> public void setInvoicenbr($importUtils.formatJavaType(${attr.Type}) invoicenbr) {
>>>>>> writeProperty(INVOICENBR_PROPERTY, invoicenbr);
>>>>>> }
>>>>>> public $importUtils.formatJavaType(${attr.Type}) getInvoicenbr() {
>>>>>> return ($importUtils.formatJavaType(${attr.Type}))readProperty(INVOICENBR_PROPERTY);
>>>>>> }
>>>>>>
>>>>>> The errors are the same for each occurrence of the CITEXT data type.
>>>>>>
>>>>>> Description Resource Path Location Type
>>>>>> $ cannot be resolved to a variable _Lineitems.java /oespg/src/main/java/org/ttna/oespg/persistent/auto line 80 Java Problem
>>>>>>
>>>>>> If this is not helpful, let me know and I'll include schema and more code.
>>>>>>
>>>>>> Thanx,
>>>>>> Garth
>>>>>>
>>>>>> On 10/30/2012 10:10 AM, Andrus Adamchik wrote:
>>>>>>> I don't have much recent firsthand PG experience, but reading citext docs, it looks like exactly what you need.
>>>>>>>
>>>>>>> So when comparisons are done in SQL it should work (at least it works for me on MySQL with similar DB-side settings). On the other hand if you are using in-memory filtering with expressions it won't work, as in-memory comparisons are simple Java "equal".
>>>>>>>
>>>>>>> Finally there is a 'likeIgnoreCase' expression that works consistently in-memory and in DB (regardless of DB-side case sensitivity), as the name implies.
>>>>>>>
>>>>>>> Andrus
>>>>>>>
>>>>>>>
>>>>>>> On Oct 30, 2012, at 2:54 PM, Garth Keesler<ga...@gdcjk.com> wrote:
>>>>>>>
>>>>>>>> I'm converting an MS Access ADP project to Java/Postgresql/Cayenne (my first and mostly for the fun of it) and am trying to figure out how to best match the case insensitive string usage that is in SQL Server. I created the PG database using CITEXT only where actually necessary but it doesn't seem to be recognized in Cayenne. It appears that I need to replace all CITEXT columns with TEXT and handle case insensitive comparisons manually. I've found a couple of threads that mention this but none had any real advice on how best to do it in a Cayenne environment. Lower() is frequently mentioned as the usual approach but before I go down that path too far I'd appreciate a little feedback from more experienced Cayenne users.
>>>>>>>>
>>>>>>>> Thanx Much,
>>>>>>>> Garth
>>>>>>>>
>>>
>>
>
>
Re: PG CITEXT Data Type
Posted by Andrus Adamchik <an...@objectstyle.org>.
I just did a quick test on Cayenne 3.1 and PG 9.1. And all I had to do is to map the DbAttribute type as OTHER instead of VARCHAR and map ObjAttribute as java.lang.String. This did it.
So after all PG's turned out to be one of the few drivers that actually handles type "OTHER" correctly, and there's no need for hacks and fancy workarounds.
HTH,
Andrus
On Nov 1, 2012, at 11:03 PM, Garth Keesler <ga...@gdcjk.com> wrote:
> I ran a couple of tests where the column is CITEXT and is indexed. The actual value in the database is "TTI" and the row was found when the parameter was "TTI" but not "tti". Is there anything special I need to do in preparing the SelectQuery?
>
> Here is the (so far) complete Main pretty much straight out of the tutorial:
>
> ServerRuntime cayenneRuntime = new ServerRuntime("cayenne-project.xml");
> ObjectContext context = cayenneRuntime.getContext();
> Expression qual = Expression
> .fromString("shortvn = $svn");
> SelectQuery proto = new SelectQuery(Vendors.class, qual);
> proto.setDistinct(true);
> Map params1 = new HashMap();
> params1.put("svn", "TTI");
> SelectQuery query1 = proto.queryWithParameters(params1);
> List performQuery = context.performQuery(query1);
> List<Vendors> vendors1 = performQuery;
> System.out.println("Vendors = " + vendors1.size());
>
> vendors1.size is 1 when the param is uppercase, 0 when lower.
>
> Thanx,
> Garth
>
>
> On 10/30/2012 12:56 PM, Andrus Adamchik wrote:
>>> all of the "special" PG data types like CITEXT are listed as "Other". I assume I should select something other than that, right?
>> Yeah.. And I guess we may need to add CITEXT to Cayenne PG adapter so that it is reverse engineered as VARCHAR or CLOB or something.
>>
>> On Oct 30, 2012, at 8:06 PM, Garth Keesler <ga...@gdcjk.com> wrote:
>>
>>> Using 3.1B1. But your second question made me look in Modeler and all of the "special" PG data types like CITEXT are listed as "Other". I assume I should select something other than that, right? I'll give it a try and see what happens.
>>>
>>> Thanx...
>>>
>>> On 10/30/2012 11:42 AM, Andrus Adamchik wrote:
>>>> So what version of Cayenne are you using (I'd recommend 3.1B1)?
>>>>
>>>> Also what type do you select for CTEXT columns in Cayenne model? Should be something like VARCHAR?
>>>>
>>>> Andrus
>>>>
>>>> On Oct 30, 2012, at 7:06 PM, Garth Keesler <ga...@gdcjk.com> wrote:
>>>>
>>>>> Thanx for the reply. The problem I seem to be having is that the schema-generated persistent classes have errors whenever one of the CITEXT attributes is mentioned. Following is a sample of the code which is causing errors.
>>>>>
>>>>> public void setInvoicenbr($importUtils.formatJavaType(${attr.Type}) invoicenbr) {
>>>>> writeProperty(INVOICENBR_PROPERTY, invoicenbr);
>>>>> }
>>>>> public $importUtils.formatJavaType(${attr.Type}) getInvoicenbr() {
>>>>> return ($importUtils.formatJavaType(${attr.Type}))readProperty(INVOICENBR_PROPERTY);
>>>>> }
>>>>>
>>>>> The errors are the same for each occurrence of the CITEXT data type.
>>>>>
>>>>> Description Resource Path Location Type
>>>>> $ cannot be resolved to a variable _Lineitems.java /oespg/src/main/java/org/ttna/oespg/persistent/auto line 80 Java Problem
>>>>>
>>>>> If this is not helpful, let me know and I'll include schema and more code.
>>>>>
>>>>> Thanx,
>>>>> Garth
>>>>>
>>>>> On 10/30/2012 10:10 AM, Andrus Adamchik wrote:
>>>>>> I don't have much recent firsthand PG experience, but reading citext docs, it looks like exactly what you need.
>>>>>>
>>>>>> So when comparisons are done in SQL it should work (at least it works for me on MySQL with similar DB-side settings). On the other hand if you are using in-memory filtering with expressions it won't work, as in-memory comparisons are simple Java "equal".
>>>>>>
>>>>>> Finally there is a 'likeIgnoreCase' expression that works consistently in-memory and in DB (regardless of DB-side case sensitivity), as the name implies.
>>>>>>
>>>>>> Andrus
>>>>>>
>>>>>>
>>>>>> On Oct 30, 2012, at 2:54 PM, Garth Keesler<ga...@gdcjk.com> wrote:
>>>>>>
>>>>>>> I'm converting an MS Access ADP project to Java/Postgresql/Cayenne (my first and mostly for the fun of it) and am trying to figure out how to best match the case insensitive string usage that is in SQL Server. I created the PG database using CITEXT only where actually necessary but it doesn't seem to be recognized in Cayenne. It appears that I need to replace all CITEXT columns with TEXT and handle case insensitive comparisons manually. I've found a couple of threads that mention this but none had any real advice on how best to do it in a Cayenne environment. Lower() is frequently mentioned as the usual approach but before I go down that path too far I'd appreciate a little feedback from more experienced Cayenne users.
>>>>>>>
>>>>>>> Thanx Much,
>>>>>>> Garth
>>>>>>>
>>>>>
>>>>
>>>
>>
>>
>
>
Re: PG CITEXT Data Type
Posted by Garth Keesler <ga...@gdcjk.com>.
I ran a couple of tests where the column is CITEXT and is indexed. The
actual value in the database is "TTI" and the row was found when the
parameter was "TTI" but not "tti". Is there anything special I need to
do in preparing the SelectQuery?
Here is the (so far) complete Main pretty much straight out of the tutorial:
ServerRuntime cayenneRuntime = new
ServerRuntime("cayenne-project.xml");
ObjectContext context = cayenneRuntime.getContext();
Expression qual = Expression
.fromString("shortvn = $svn");
SelectQuery proto = new SelectQuery(Vendors.class, qual);
proto.setDistinct(true);
Map params1 = new HashMap();
params1.put("svn", "TTI");
SelectQuery query1 = proto.queryWithParameters(params1);
List performQuery = context.performQuery(query1);
List<Vendors> vendors1 = performQuery;
System.out.println("Vendors = " + vendors1.size());
vendors1.size is 1 when the param is uppercase, 0 when lower.
Thanx,
Garth
On 10/30/2012 12:56 PM, Andrus Adamchik wrote:
>> all of the "special" PG data types like CITEXT are listed as "Other". I assume I should select something other than that, right?
> Yeah.. And I guess we may need to add CITEXT to Cayenne PG adapter so that it is reverse engineered as VARCHAR or CLOB or something.
>
> On Oct 30, 2012, at 8:06 PM, Garth Keesler <ga...@gdcjk.com> wrote:
>
>> Using 3.1B1. But your second question made me look in Modeler and all of the "special" PG data types like CITEXT are listed as "Other". I assume I should select something other than that, right? I'll give it a try and see what happens.
>>
>> Thanx...
>>
>> On 10/30/2012 11:42 AM, Andrus Adamchik wrote:
>>> So what version of Cayenne are you using (I'd recommend 3.1B1)?
>>>
>>> Also what type do you select for CTEXT columns in Cayenne model? Should be something like VARCHAR?
>>>
>>> Andrus
>>>
>>> On Oct 30, 2012, at 7:06 PM, Garth Keesler <ga...@gdcjk.com> wrote:
>>>
>>>> Thanx for the reply. The problem I seem to be having is that the schema-generated persistent classes have errors whenever one of the CITEXT attributes is mentioned. Following is a sample of the code which is causing errors.
>>>>
>>>> public void setInvoicenbr($importUtils.formatJavaType(${attr.Type}) invoicenbr) {
>>>> writeProperty(INVOICENBR_PROPERTY, invoicenbr);
>>>> }
>>>> public $importUtils.formatJavaType(${attr.Type}) getInvoicenbr() {
>>>> return ($importUtils.formatJavaType(${attr.Type}))readProperty(INVOICENBR_PROPERTY);
>>>> }
>>>>
>>>> The errors are the same for each occurrence of the CITEXT data type.
>>>>
>>>> Description Resource Path Location Type
>>>> $ cannot be resolved to a variable _Lineitems.java /oespg/src/main/java/org/ttna/oespg/persistent/auto line 80 Java Problem
>>>>
>>>> If this is not helpful, let me know and I'll include schema and more code.
>>>>
>>>> Thanx,
>>>> Garth
>>>>
>>>> On 10/30/2012 10:10 AM, Andrus Adamchik wrote:
>>>>> I don't have much recent firsthand PG experience, but reading citext docs, it looks like exactly what you need.
>>>>>
>>>>> So when comparisons are done in SQL it should work (at least it works for me on MySQL with similar DB-side settings). On the other hand if you are using in-memory filtering with expressions it won't work, as in-memory comparisons are simple Java "equal".
>>>>>
>>>>> Finally there is a 'likeIgnoreCase' expression that works consistently in-memory and in DB (regardless of DB-side case sensitivity), as the name implies.
>>>>>
>>>>> Andrus
>>>>>
>>>>>
>>>>> On Oct 30, 2012, at 2:54 PM, Garth Keesler<ga...@gdcjk.com> wrote:
>>>>>
>>>>>> I'm converting an MS Access ADP project to Java/Postgresql/Cayenne (my first and mostly for the fun of it) and am trying to figure out how to best match the case insensitive string usage that is in SQL Server. I created the PG database using CITEXT only where actually necessary but it doesn't seem to be recognized in Cayenne. It appears that I need to replace all CITEXT columns with TEXT and handle case insensitive comparisons manually. I've found a couple of threads that mention this but none had any real advice on how best to do it in a Cayenne environment. Lower() is frequently mentioned as the usual approach but before I go down that path too far I'd appreciate a little feedback from more experienced Cayenne users.
>>>>>>
>>>>>> Thanx Much,
>>>>>> Garth
>>>>>>
>>>>
>>>
>>
>
>
Re: PG CITEXT Data Type
Posted by Andrus Adamchik <an...@objectstyle.org>.
> all of the "special" PG data types like CITEXT are listed as "Other". I assume I should select something other than that, right?
Yeah.. And I guess we may need to add CITEXT to Cayenne PG adapter so that it is reverse engineered as VARCHAR or CLOB or something.
On Oct 30, 2012, at 8:06 PM, Garth Keesler <ga...@gdcjk.com> wrote:
> Using 3.1B1. But your second question made me look in Modeler and all of the "special" PG data types like CITEXT are listed as "Other". I assume I should select something other than that, right? I'll give it a try and see what happens.
>
> Thanx...
>
> On 10/30/2012 11:42 AM, Andrus Adamchik wrote:
>> So what version of Cayenne are you using (I'd recommend 3.1B1)?
>>
>> Also what type do you select for CTEXT columns in Cayenne model? Should be something like VARCHAR?
>>
>> Andrus
>>
>> On Oct 30, 2012, at 7:06 PM, Garth Keesler <ga...@gdcjk.com> wrote:
>>
>>> Thanx for the reply. The problem I seem to be having is that the schema-generated persistent classes have errors whenever one of the CITEXT attributes is mentioned. Following is a sample of the code which is causing errors.
>>>
>>> public void setInvoicenbr($importUtils.formatJavaType(${attr.Type}) invoicenbr) {
>>> writeProperty(INVOICENBR_PROPERTY, invoicenbr);
>>> }
>>> public $importUtils.formatJavaType(${attr.Type}) getInvoicenbr() {
>>> return ($importUtils.formatJavaType(${attr.Type}))readProperty(INVOICENBR_PROPERTY);
>>> }
>>>
>>> The errors are the same for each occurrence of the CITEXT data type.
>>>
>>> Description Resource Path Location Type
>>> $ cannot be resolved to a variable _Lineitems.java /oespg/src/main/java/org/ttna/oespg/persistent/auto line 80 Java Problem
>>>
>>> If this is not helpful, let me know and I'll include schema and more code.
>>>
>>> Thanx,
>>> Garth
>>>
>>> On 10/30/2012 10:10 AM, Andrus Adamchik wrote:
>>>> I don't have much recent firsthand PG experience, but reading citext docs, it looks like exactly what you need.
>>>>
>>>> So when comparisons are done in SQL it should work (at least it works for me on MySQL with similar DB-side settings). On the other hand if you are using in-memory filtering with expressions it won't work, as in-memory comparisons are simple Java "equal".
>>>>
>>>> Finally there is a 'likeIgnoreCase' expression that works consistently in-memory and in DB (regardless of DB-side case sensitivity), as the name implies.
>>>>
>>>> Andrus
>>>>
>>>>
>>>> On Oct 30, 2012, at 2:54 PM, Garth Keesler<ga...@gdcjk.com> wrote:
>>>>
>>>>> I'm converting an MS Access ADP project to Java/Postgresql/Cayenne (my first and mostly for the fun of it) and am trying to figure out how to best match the case insensitive string usage that is in SQL Server. I created the PG database using CITEXT only where actually necessary but it doesn't seem to be recognized in Cayenne. It appears that I need to replace all CITEXT columns with TEXT and handle case insensitive comparisons manually. I've found a couple of threads that mention this but none had any real advice on how best to do it in a Cayenne environment. Lower() is frequently mentioned as the usual approach but before I go down that path too far I'd appreciate a little feedback from more experienced Cayenne users.
>>>>>
>>>>> Thanx Much,
>>>>> Garth
>>>>>
>>>
>>>
>>
>>
>
>
Re: PG CITEXT Data Type
Posted by Garth Keesler <ga...@gdcjk.com>.
Using 3.1B1. But your second question made me look in Modeler and all of
the "special" PG data types like CITEXT are listed as "Other". I assume
I should select something other than that, right? I'll give it a try and
see what happens.
Thanx...
On 10/30/2012 11:42 AM, Andrus Adamchik wrote:
> So what version of Cayenne are you using (I'd recommend 3.1B1)?
>
> Also what type do you select for CTEXT columns in Cayenne model? Should be something like VARCHAR?
>
> Andrus
>
> On Oct 30, 2012, at 7:06 PM, Garth Keesler <ga...@gdcjk.com> wrote:
>
>> Thanx for the reply. The problem I seem to be having is that the schema-generated persistent classes have errors whenever one of the CITEXT attributes is mentioned. Following is a sample of the code which is causing errors.
>>
>> public void setInvoicenbr($importUtils.formatJavaType(${attr.Type}) invoicenbr) {
>> writeProperty(INVOICENBR_PROPERTY, invoicenbr);
>> }
>> public $importUtils.formatJavaType(${attr.Type}) getInvoicenbr() {
>> return ($importUtils.formatJavaType(${attr.Type}))readProperty(INVOICENBR_PROPERTY);
>> }
>>
>> The errors are the same for each occurrence of the CITEXT data type.
>>
>> Description Resource Path Location Type
>> $ cannot be resolved to a variable _Lineitems.java /oespg/src/main/java/org/ttna/oespg/persistent/auto line 80 Java Problem
>>
>> If this is not helpful, let me know and I'll include schema and more code.
>>
>> Thanx,
>> Garth
>>
>> On 10/30/2012 10:10 AM, Andrus Adamchik wrote:
>>> I don't have much recent firsthand PG experience, but reading citext docs, it looks like exactly what you need.
>>>
>>> So when comparisons are done in SQL it should work (at least it works for me on MySQL with similar DB-side settings). On the other hand if you are using in-memory filtering with expressions it won't work, as in-memory comparisons are simple Java "equal".
>>>
>>> Finally there is a 'likeIgnoreCase' expression that works consistently in-memory and in DB (regardless of DB-side case sensitivity), as the name implies.
>>>
>>> Andrus
>>>
>>>
>>> On Oct 30, 2012, at 2:54 PM, Garth Keesler<ga...@gdcjk.com> wrote:
>>>
>>>> I'm converting an MS Access ADP project to Java/Postgresql/Cayenne (my first and mostly for the fun of it) and am trying to figure out how to best match the case insensitive string usage that is in SQL Server. I created the PG database using CITEXT only where actually necessary but it doesn't seem to be recognized in Cayenne. It appears that I need to replace all CITEXT columns with TEXT and handle case insensitive comparisons manually. I've found a couple of threads that mention this but none had any real advice on how best to do it in a Cayenne environment. Lower() is frequently mentioned as the usual approach but before I go down that path too far I'd appreciate a little feedback from more experienced Cayenne users.
>>>>
>>>> Thanx Much,
>>>> Garth
>>>>
>>
>>
>
>
Re: PG CITEXT Data Type
Posted by Andrus Adamchik <an...@objectstyle.org>.
So what version of Cayenne are you using (I'd recommend 3.1B1)?
Also what type do you select for CTEXT columns in Cayenne model? Should be something like VARCHAR?
Andrus
On Oct 30, 2012, at 7:06 PM, Garth Keesler <ga...@gdcjk.com> wrote:
>
> Thanx for the reply. The problem I seem to be having is that the schema-generated persistent classes have errors whenever one of the CITEXT attributes is mentioned. Following is a sample of the code which is causing errors.
>
> public void setInvoicenbr($importUtils.formatJavaType(${attr.Type}) invoicenbr) {
> writeProperty(INVOICENBR_PROPERTY, invoicenbr);
> }
> public $importUtils.formatJavaType(${attr.Type}) getInvoicenbr() {
> return ($importUtils.formatJavaType(${attr.Type}))readProperty(INVOICENBR_PROPERTY);
> }
>
> The errors are the same for each occurrence of the CITEXT data type.
>
> Description Resource Path Location Type
> $ cannot be resolved to a variable _Lineitems.java /oespg/src/main/java/org/ttna/oespg/persistent/auto line 80 Java Problem
>
> If this is not helpful, let me know and I'll include schema and more code.
>
> Thanx,
> Garth
>
> On 10/30/2012 10:10 AM, Andrus Adamchik wrote:
>> I don't have much recent firsthand PG experience, but reading citext docs, it looks like exactly what you need.
>>
>> So when comparisons are done in SQL it should work (at least it works for me on MySQL with similar DB-side settings). On the other hand if you are using in-memory filtering with expressions it won't work, as in-memory comparisons are simple Java "equal".
>>
>> Finally there is a 'likeIgnoreCase' expression that works consistently in-memory and in DB (regardless of DB-side case sensitivity), as the name implies.
>>
>> Andrus
>>
>>
>> On Oct 30, 2012, at 2:54 PM, Garth Keesler<ga...@gdcjk.com> wrote:
>>
>>> I'm converting an MS Access ADP project to Java/Postgresql/Cayenne (my first and mostly for the fun of it) and am trying to figure out how to best match the case insensitive string usage that is in SQL Server. I created the PG database using CITEXT only where actually necessary but it doesn't seem to be recognized in Cayenne. It appears that I need to replace all CITEXT columns with TEXT and handle case insensitive comparisons manually. I've found a couple of threads that mention this but none had any real advice on how best to do it in a Cayenne environment. Lower() is frequently mentioned as the usual approach but before I go down that path too far I'd appreciate a little feedback from more experienced Cayenne users.
>>>
>>> Thanx Much,
>>> Garth
>>>
>>
>
>
>
Re: PG CITEXT Data Type
Posted by Garth Keesler <ga...@gdcjk.com>.
Thanx for the reply. The problem I seem to be having is that the
schema-generated persistent classes have errors whenever one of the
CITEXT attributes is mentioned. Following is a sample of the code which
is causing errors.
public void setInvoicenbr($importUtils.formatJavaType(${attr.Type})
invoicenbr) {
writeProperty(INVOICENBR_PROPERTY, invoicenbr);
}
public $importUtils.formatJavaType(${attr.Type}) getInvoicenbr() {
return
($importUtils.formatJavaType(${attr.Type}))readProperty(INVOICENBR_PROPERTY);
}
The errors are the same for each occurrence of the CITEXT data type.
Description Resource Path Location Type
$ cannot be resolved to a variable _Lineitems.java
/oespg/src/main/java/org/ttna/oespg/persistent/auto line 80 Java Problem
If this is not helpful, let me know and I'll include schema and more code.
Thanx,
Garth
On 10/30/2012 10:10 AM, Andrus Adamchik wrote:
> I don't have much recent firsthand PG experience, but reading citext docs, it looks like exactly what you need.
>
> So when comparisons are done in SQL it should work (at least it works for me on MySQL with similar DB-side settings). On the other hand if you are using in-memory filtering with expressions it won't work, as in-memory comparisons are simple Java "equal".
>
> Finally there is a 'likeIgnoreCase' expression that works consistently in-memory and in DB (regardless of DB-side case sensitivity), as the name implies.
>
> Andrus
>
>
> On Oct 30, 2012, at 2:54 PM, Garth Keesler<ga...@gdcjk.com> wrote:
>
>> I'm converting an MS Access ADP project to Java/Postgresql/Cayenne (my first and mostly for the fun of it) and am trying to figure out how to best match the case insensitive string usage that is in SQL Server. I created the PG database using CITEXT only where actually necessary but it doesn't seem to be recognized in Cayenne. It appears that I need to replace all CITEXT columns with TEXT and handle case insensitive comparisons manually. I've found a couple of threads that mention this but none had any real advice on how best to do it in a Cayenne environment. Lower() is frequently mentioned as the usual approach but before I go down that path too far I'd appreciate a little feedback from more experienced Cayenne users.
>>
>> Thanx Much,
>> Garth
>>
>
Re: PG CITEXT Data Type
Posted by Andrus Adamchik <an...@objectstyle.org>.
I don't have much recent firsthand PG experience, but reading citext docs, it looks like exactly what you need.
So when comparisons are done in SQL it should work (at least it works for me on MySQL with similar DB-side settings). On the other hand if you are using in-memory filtering with expressions it won't work, as in-memory comparisons are simple Java "equal".
Finally there is a 'likeIgnoreCase' expression that works consistently in-memory and in DB (regardless of DB-side case sensitivity), as the name implies.
Andrus
On Oct 30, 2012, at 2:54 PM, Garth Keesler <ga...@gdcjk.com> wrote:
> I'm converting an MS Access ADP project to Java/Postgresql/Cayenne (my first and mostly for the fun of it) and am trying to figure out how to best match the case insensitive string usage that is in SQL Server. I created the PG database using CITEXT only where actually necessary but it doesn't seem to be recognized in Cayenne. It appears that I need to replace all CITEXT columns with TEXT and handle case insensitive comparisons manually. I've found a couple of threads that mention this but none had any real advice on how best to do it in a Cayenne environment. Lower() is frequently mentioned as the usual approach but before I go down that path too far I'd appreciate a little feedback from more experienced Cayenne users.
>
> Thanx Much,
> Garth
>