Just to toss in a way that I use TQL that I didn't see mentioned yet... Some apps I write have an area where the user is able to "build" data sets by selecting/entering multiple criteria. In order to store these data set definitions/queries for future use, I could store the individual choices that the user made and then upon retrieving a saved search rebuild the query from scratch; but it seemed easier and cleaner to me to simply build the search as a TQL statement one time, and then store that statement in the db to represent the saved search. Just one more real world example.
Doug B On Wed, Jun 24, 2009 at 5:51 AM, Elliott Sprehn <[email protected]> wrote: > > On Jun 24, 12:47 am, jarthel <[email protected]> wrote: > > It seems TQL can only accomodate "basic" (at least to me) SQL which > > makes it feels like I'm going backwards. > > > > You can't use built-in DB functions (at least that's how I understand > > it reading the TQL doc page). We use oracle here and there are many > > useful functions built-in the DB. > > > > 1. why would you use TQL over standard SQL (which every programmer > > would know)? The > > TQL looks like SQL so it's not hard to pick up. :P > > > > > 2. maybe there are tips/tricks available to make TQL more flexible? if > > yes, please tell me. > > Not particularly. TQL is quite limited in what it allows. What you see > on the doc page is what you get. > > Mark talked about adding support for DB functions into TQL, but that > feature has never materialized. > > > > > 3. Also is there any reason to use an transfer object vs standard > > query datatype when retrieving data? > > > > You'd want to use an object instead when you need computed properties > or behaviors of the object. That's kind of the whole point of > Transfer. > > The query type on the other hand is *much* faster to loop over, > output, convert for JS, and pull back from the database. > > The advantages of TQL are: > - Database independence > - Safer (you have no option but to use bind parameters so you can't > get injection attacks, even in dynamic code) > - Uses aliased column names (great for legacy databases with bad > names; materialized views kind of solve this too) > - Automatic Joins (not a huge deal, some databases can do this in SQL > too) > > To be honest the issue you're running up against is historically a big > place of tension between the "Object Oriented" ORM pundits and the > database guys. > > The short answer is that you should use what's right for any given > task. If you could write that query and gain a 10x performance > increase using straight up SQL and a database feature (like CONNECT BY > in Oracle) then do it. If you want to ship a product for 5 different > databases and reduce the development cost, then try TQL. > > We use TQL for most things so we can avoid some SQL quirks and ship > for MySQL and MSSQL with little effort and also gain the injection > protection. Then when we need a complex query we use stored procedures > and maintain a stored proc for each database since cfstoreproc can > abstract away that we're using different database systems there too. > > > > --~--~---------~--~----~------------~-------~--~----~ Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en -~----------~----~----~----~------~----~------~--~---
