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 mark.man...@gmail.com 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 pe...@bezunartea.net 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: object name=Session table=USER_SESSIONS id name=PersonId column=PERSON_ID type=string generate=false / property name=SessionToken type=string nullable=false column=TOKEN / /object 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.comtransfer-dev%2bunsubscr...@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.comtransfer-dev%2bunsubscr...@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 TQLSQL 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: 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
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: 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
