Re: [transfer-dev] No records returned: CF7 and oracle
Hi Mark,
> So in the debug output - when you see the Transfer generated SQL, how many
> rows does it return?
>
None.
The table actually has just one row, that I dump using the
transfer.list("...Session") query. But none using the listByQuery method
(after the proper setParam, of course), or listByProperty.
Still researching...
Pedro.
2011/1/3 Mark Mandel
> So in the debug output - when you see the Transfer generated SQL, how many
> rows does it return?
>
> Mark
>
> On Tue, Jan 4, 2011 at 2:35 AM, pedrobl wrote:
>
>>
>> Happy new year everyone!
>>
>> I left last year with a strange problem that probably has an easy
>> solution which I fail to see. I'm using Coldbox, Coldspring, Transfer
>> and Oracle 10g. I have stumbled upon a few driver issues, and this
>> probably has something to do with it.
>>
>> First, the table: sessions, which just has the following fields:
>> PersonId (CHAR 32), Token (CHAR 36).
>>
>> The use case is simple, a user wants to be authenticated and doesn't
>> remember her password. She accesses a form where she enters her email
>> address, the system checks that the email exists, generates a token
>> using CreateUUID(), persists the session object to the database, and
>> sends an email with a link containing the token just created. The link
>> takes the user to a page where all the system has to do is search the
>> database for the token, and generate a user session accordingly...
>> pretty simple.
>>
>> Second, transfer.xml:
>>
>> > >
>> > column="TOKEN" />
>>
>>
>> Amazingly, the record is never found. I have tried many transfer
>> methods, all unsuccessfully, among others:
>>
>> - readByProperty: "SessionToken", and the generated token.
>> - readByPropertyMap: the map consists of a struct with a
>> "SessionToken" key, and the token as its value.
>> - listByQuery: the query is simple "FROM ...Session
>> WHERE ...Session.SessionToken=:sessionToken"
>> - Change the TOKEN field to varchar2.
>>
>> The first two return a new object, and the third returns an empty
>> query. I also cfdump the whole table, to check that the record exists,
>> and the sql sent to oracle, and all look just fine... as they do in
>> many other parts of the application. If I run the query generated
>> directly in SQLDeveloper, it works fine. I have also tried the same
>> code using postgresql instead of Oracle, and it works!!
>>
>> Sorry for the long post. What else could I try to debug this? Any
>> ideas? TIA,
>>
>> Pedro.
>>
>> --
>> Before posting questions to the group please read:
>>
>> http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer
>>
>> Try out the new Transfer ORM Custom Google Search:
>> http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8
>>
>> You received this message because you are subscribed to the Google Groups
>> "transfer-dev" group.
>> To post to this group, send email to transfer-dev@googlegroups.com
>> To unsubscribe from this group, send email to
>> transfer-dev+unsubscr...@googlegroups.com
>> For more options, visit this group at
>> http://groups.google.com/group/transfer-dev?hl=en
>>
>
>
>
> --
> E: mark.man...@gmail.com
> T: http://www.twitter.com/neurotic
> W: www.compoundtheory.com
>
> cf.Objective(ANZ) - Nov 18, 19 - Melbourne Australia
> http://www.cfobjective.com.au
>
> Hands-on ColdFusion ORM Training
> www.ColdFusionOrmTraining.com
>
> --
> Before posting questions to the group please read:
>
> http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer
>
> Try out the new Transfer ORM Custom Google Search:
> http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8
>
> You received this message because you are subscribed to the Google Groups
> "transfer-dev" group.
> To post to this group, send email to transfer-dev@googlegroups.com
> To unsubscribe from this group, send email to
> transfer-dev+unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/transfer-dev?hl=en
>
--
Before posting questions to the group please read:
http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer
Try out the new Transfer ORM Custom Google Search:
http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8
You received this message because you are subscribed to the Google Groups
"transfer-dev" group.
To post to this group, send email to transfer-dev@googlegroups.com
To unsubscribe from this group, send email to
transfer-dev+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/transfer-dev?hl=en
[transfer-dev] Non case sensitive searches, revisited
Last April I offered my time to solve this issue but, unfortunately didn't
receive any responses. The time has come for me to solve this issue once and
for all. I'd really appreciate any feedback.
The problem: Transfer does not support functions, so it's not possible to do
non case sensitive searches using functions like LCASE.
Initially I solved the issue by creating views in the DB server with a LCASE
version of the columns I needed to search, and adding new objects in
Transfer. The problem of this approach is that it soon gets very messy: you
need to have duplicate versions of the same objects in Transfer; you need
to create extra views in the DB; if you decide to search in another column,
you need add more code; and most important, the DB indices are not used as
the query that the server receives does not include LCASE. (If someone needs
more details on this, please let me know)
The proposed solution: Ideally, I'd like to add support for SQL functions to
Transfer, but I'm afraid it's too big of a problem for me. The second,
simpler option is to modify Transfer's behaviour when it deals with the LIKE
function.
After checking Transfer and ANTLR source code, the easiest path seems to be
to modify the TQL>SQL translation of the LIKE operator from the current
translation:
"property LIKE expr" -> "column LIKE expr"
to
"property LIKE expr" -> "LCASE(column) LIKE LCASE(expr)"
This way, the DB server knows it should use the LCASE index.
After checking the transfer source code a bit more, I came across the file
"Tql.g", which seems to define the "grammar" for TQL. The where statements
are define as: whereStatement: WHERE^ conditionStatement; and a few lines
below: OPERATOR: ('='|'>'|'<'|'!='|'<>'|'>='|'<='|'like') {
setMode(PROPERTY_IDENTIFIER_MODE); }; and condition: ( conditionBasicClause
| conditionParen ) ( BOOLEAN_LOGIC ( conditionBasicClause | conditionParen )
)*;
So, I probably need to modify OPERATOR to exclude LIKE, add a new type of
clause to the definition of "condition" which will handle the translation
mentioned above.
Does this sound reasonable? feasible? Any ideas? comments? Unfortunately, I
don't have as much time to look into this as I'd like... is this the right
approach?
Pedro.
--
Before posting questions to the group please read:
http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer
Try out the new Transfer ORM Custom Google Search:
http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8
You received this message because you are subscribed to the Google Groups
"transfer-dev" group.
To post to this group, send email to transfer-dev@googlegroups.com
To unsubscribe from this group, send email to
transfer-dev+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/transfer-dev?hl=en
[transfer-dev] Re: Non case sensitive searches, revisited
I have researched quite a bit about ANTLR, and I have now a clear idea what
needs to be done to the TQL lexer and parser. This is what I've done:
- in the file where TQL grammar is defined tql-20070315163054.jar, I
modified the file Tql.g, from:
conditionBasicClause : PROPERTY_IDENTIFIER ( operatorClause | isNullClause |
inClause );
to
conditionBasicClause : PROPERTY_IDENTIFIER ( operatorClause |
likeOperatorClause | isNullClause | inClause );
...
likeOperatorClause : LIKE (MAPPED_PARAM | PROPERTY_IDENTIFIER);
LIKE : 'like';
and redefined OPERATOR to:
OPERATOR : ('='|'>'|'<'|'!='|'<>'|'>='|'<=') {
setMode(PROPERTY_IDENTIFIER_MODE); };
I can run the tests fine, but only get up to the Tree. So my questions are:
where does the SQL generation occur? I'd assumed it was all ANTLR based, but
I now see that the final Tree to SQL is be done in coldfusion code. Any
pointers, please?
Pedro.
PS: if you are interested on how ANTLR works, check the video tutorials done
by Scott Stanchfield, really well done:
http://javadude.com/articles/antlr3xtut/
--
Before posting questions to the group please read:
http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer
Try out the new Transfer ORM Custom Google Search:
http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8
You received this message because you are subscribed to the Google Groups
"transfer-dev" group.
To post to this group, send email to transfer-dev@googlegroups.com
To unsubscribe from this group, send email to
transfer-dev+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/transfer-dev?hl=en
[transfer-dev] Re: Non case sensitive searches, revisited SOLVED! ... for now
I finally ended up modifying the component
transfer/com/tql/SelectStatement.cfc. I modified the method
executeEvaluation, these are the changes:
- added a local var:
- replaced the line 180 (#block.preSQL#) with:
#ReReplace( block.preSQL, "(.*) ([a-zA-Z_\.]+) LIKE$", "\1 LOWER(\2) LIKE
LOWER(" )#
#block.preSQL#
- and added after the , in line 202:
)
This transforms all "table.column LIKE " to "LOWER(table.column) LIKE
LOWER()". It's no more than an ugly hack, but it works for now. I'd
really like to add support for a few SQL functions to TQL... but I'm afraid
it'll have to wait.
Hope that helps someone,
Pedro.
--
Before posting questions to the group please read:
http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer
Try out the new Transfer ORM Custom Google Search:
http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8
You received this message because you are subscribed to the Google Groups
"transfer-dev" group.
To post to this group, send email to transfer-dev@googlegroups.com
To unsubscribe from this group, send email to
transfer-dev+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/transfer-dev?hl=en
[transfer-dev] Re: TQL Many-To-Many Bidirectional Relationship
Hi Robert, I think that's well explained in the documentation: http://docs.transfer-orm.com/wiki/Managing_Relationships_and_Compositions.cfm The short answer is that having established a OneToMany relation, Foo to Bars, you can use the method Bar.getParentFoo(). HTH, Pedro -- Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer Try out the new Transfer ORM Custom Google Search: http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8 You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to transfer-dev@googlegroups.com To unsubscribe from this group, send email to transfer-dev+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en
[transfer-dev] Re: Non case sensitive searches, revisited SOLVED! ... for now
I should also add that the changes posted support TQL queries with the format "Object.Property LIKE :param", and not ":param LIKE Object.Property". Now that I better understand how antlrTQL works, I'd like to add support for SQL functions that only require one argument: LOWER(), UPPER() and the like. The plan is: - Modify the TQL grammar to support this kind of functions and pass them through the parser untouched. - Adapt transfer coldfusion code to ignore these functions, and also leave them untouched. The question is, how should I approach changes in the coldfusion code? Or better yet, do I really need to adapt the coldfusion code? If the output from antlrTQL is already proper SQL I might not need to modify it at all. Any comments? Pedro. -- Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer Try out the new Transfer ORM Custom Google Search: http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8 You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to transfer-dev@googlegroups.com To unsubscribe from this group, send email to transfer-dev+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en
Re: [transfer-dev] Re: Non case sensitive searches, revisited SOLVED! ... for now
Thanks for the tip Mark, glad to hear from you again! One question though, I remember checking the feature requests list somewhere and this was already in. I haven't noticed any updates for quite a while, and I don't know what the plan is (it's been working perfectly since v1.1) . Anyway, has any discussion taken place about this? what is the best way to send you the changes? Thanks for your great work! Pedro. -- Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer Try out the new Transfer ORM Custom Google Search: http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8 You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to transfer-dev@googlegroups.com To unsubscribe from this group, send email to transfer-dev+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en
[transfer-dev] Re: TQL Many-To-Many Bidirectional Relationship
Hi Robert, It should not be too difficult, I've added all kind of methods to my decorators. This is how I'd do it: 1. The model persists its data in 3 tables for the 2 objects and their many to many relationship (foos, bars and lnk_FooBar). Transfer.xml should look something like: ... ... The table lnkFooBar should only consist of 2 columns, lnkIDFoo and lnkIDBar. 2. In the decorator for Bar, I'd add the following method: Every "..." should be replaced with the package name. A similar method could obtain structs. HTH, Pedro. -- Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer Try out the new Transfer ORM Custom Google Search: http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8 You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to transfer-dev@googlegroups.com To unsubscribe from this group, send email to transfer-dev+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en
[transfer-dev] Re: Maintaining Cache w/ Framework Reload
Hi again, Robert, My experience is that unless you only modify the views, you need to reinit the framework, and loose cached objects... honestly, I don't see how you could keep objects cached that may have changed their behaviour when you modify their code. :P Cheers, Pedro. -- Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer Try out the new Transfer ORM Custom Google Search: http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8 You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to transfer-dev@googlegroups.com To unsubscribe from this group, send email to transfer-dev+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en
[transfer-dev] Re: Help with TQL Join
Hi Robert, If the relations are already defined in transfer's config file, you could just join them as you have done already: TQL = "FROM inventory.Stock AS Stock JOIN order.item AS Item *JOIN order.order AS Order* WHERE Stock.StockNumber= :StockNumber" >From my experience, if there's only one possible relation that fits the JOIN, it'll be used. Have you tried this? What happened? Cheers, Pedro. -- Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer Try out the new Transfer ORM Custom Google Search: http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8 You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to transfer-dev@googlegroups.com To unsubscribe from this group, send email to transfer-dev+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en
