I understand your feelings jarthel, I had these kind of thoughts when I first started using ORM. I felt guilty for "going outside" of Transfer.
The truth is, you can use regular SQL whenever you'd like. If you have a model object (say a DAO or gateway) that works with your database, you can put SQL in those objects to do specific operations. However, like Baz mentioned, some databases interpret SQL a little differently, but TQL will work the same on top of all of them because it works with Transfer objects and within the Transfer system, not with the database itself. You can move your TQL from MySQL to MSSQL and not need to make any changes. Another reason to use TQL is that you may have already created the objects in Transfer that you need. This means that you can use TQL to pull the query based on objects that have already been generated, this way you don't have to "go back" to the database to get them. Elliot's comments are spot on, especially about security. You don't have to worry about injection when you use ORM. Doug, this is a very slick idea, glad you mentioned it. Basically, you store the TQL that contains your search options in a database tabled called "searches" (or "filters" or whatever) and then when you want to run that search, you pull the TQL out of the database and have Transfer run it. This is quite clever :) On Jun 24, 7:26 am, Doug Boude <dougbo...@gmail.com> wrote: > 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 <espr...@gmail.com> wrote: > > > On Jun 24, 12:47 am, jarthel <jart...@gmail.com> 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 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 -~----------~----~----~----~------~----~------~--~---
