Hi all! I am quite new to rails, and I would like to know if there is a tutorial, or if someone can tell me how to use rails with stored procedures and views. Any help would be appreciated. Thanks to all -- ------------------------------------------- Gioachino Bartolotta
Gioachino Bartolotta wrote:> Hi all! > I am quite new to rails, and I would like to know if there is a > tutorial, or if someone can tell me how to use rails with stored > procedures and views. > Any help would be appreciated.Views you can use as an ActiveRecord model, save, create and update won''t work ofcourse, unless you setup your DB to support updatable views. I imagine you can run stores procedures with YourModel.connecion.select_value("SELECT myfunction()") or something similar. Triggers will ofcourse work transparently through ActiveRecord, but be sure to reload the model after a save operation as the trigger may have caused the object to go "out of sync", if you know what I mean. Jeroen
Gioachino, Are you using Microsoft SQL Server? If so, I think you could run the following sql to return a recordset from a Stored Proc.: " EXEC yourStoredProcName @param=''value'' " (However, I believe this will not return any OUTPUT parameters or Return Codes) On 12/20/05, Jeroen Houben <jeroen-aHd7JyfBtzlmR6Xm/wNWPw@public.gmane.org> wrote:> > Gioachino Bartolotta wrote: > > Hi all! > > I am quite new to rails, and I would like to know if there is a > > tutorial, or if someone can tell me how to use rails with stored > > procedures and views. > > Any help would be appreciated. > > Views you can use as an ActiveRecord model, save, create and update > won''t work ofcourse, unless you setup your DB to support updatable views. > > I imagine you can run stores procedures with > YourModel.connecion.select_value("SELECT myfunction()") or something > similar. > > Triggers will ofcourse work transparently through ActiveRecord, but be > sure to reload the model after a save operation as the trigger may have > caused the object to go "out of sync", if you know what I mean. > > Jeroen > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Hi Ed! I am using PostgreSQL ... I work only on *nix like system Right noe I am trying to see how rails work with views ... ;) Thanks guys for your help ... 2005/12/20, Ed C. <defeated2k4-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> Gioachino, > > Are you using Microsoft SQL Server? If so, I think you could run the > following sql to return a recordset from a Stored Proc.: > > " EXEC yourStoredProcName @param=''value'' " > > (However, I believe this will not return any OUTPUT parameters or Return > Codes) > > >-- ------------------------------------------- Gioachino Bartolotta Mobile: 320 6059371 ICQ #: 9103167 MSN Messenger: astraroth-06ZeP6ie+xM@public.gmane.org Yahoo & Skype: gioachino_bartolotta
Stored Procedures is not the rails way............ All features that make our database, [business] smart we hate.......... On 12/20/05, Gioachino Bartolotta <gioachino.bartolotta-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Hi Ed! > > I am using PostgreSQL ... I work only on *nix like system > Right noe I am trying to see how rails work with views ... ;) > > Thanks guys for your help ... > > 2005/12/20, Ed C. <defeated2k4-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: > > Gioachino, > > > > Are you using Microsoft SQL Server? If so, I think you could run the > > following sql to return a recordset from a Stored Proc.: > > > > " EXEC yourStoredProcName @param=''value'' " > > > > (However, I believe this will not return any OUTPUT parameters or Return > > Codes) > > > > > > > -- > ------------------------------------------- > Gioachino Bartolotta > Mobile: 320 6059371 > ICQ #: 9103167 > MSN Messenger: astraroth-06ZeP6ie+xM@public.gmane.org > Yahoo & Skype: gioachino_bartolotta > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- First they laugh at you, then they ignore you, then they fight you. Then you win. -- Mahatma Karamchand Gandhi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
> Stored Procedures is not the rails way............ > > All features that make our database, [business] smart we > hate..........It''s hard to disagree with that. However, I''m working (playing actually) with an interesting quandary, specifically with the PostGIS extensions to PostgreSql. Not really stored procedures, but much of the functionality is encapsulated as functions within the database. If you want to know the airports within 100 km of Berlin, you create a SELECT statement with a function call embedded in it. This kinda rubs me the wrong way. If I''m using Active Record, this is not just an attribute I can get to, I''ll have to make another round trip to the database. But, I can''t see another way. There are some geo-functions that could be calculated on the object already retrieved (like say, what are the dimensions of a rectangle that encloses this geo-shape) and which could be encapsulated at the object level, but clearly finding how distinct geo-shapes relate to each other needs a PostGIS query. Can this still be considered "the rails way"? Please do tell me if you find my thinking flawed, I''d love to be set straight. Cheers, Curtis Olson
I have been watching the rails stored procedure discussion that keeps popping up here and elsewhere for a while. Has anyone suggested that stored procedures be allowed if they are associated as methods on models? ActiveRecord could include code to form the association (e.g. set_stored_proc_method) and code to handle the return values and to refresh the model if necessary. For example: # :stored_proc should be optional if the stored procedure name is the same as the method name # e.g. :doFunDBStuff has a stored procedure named doFunDBStuff # overrides for parameters could also be allowed in case they cannot be pulled from the schema # or need a type definition or name change class myModel < ActiveRecord::Base set_stored_proc_method :doFunDBStuff, :stored_proc => "myStoredProc" end What do you guys think? I know this "isn''t the Rails way", but wouldn''t this make Rails even more useful without changing the preferred way of working with Rails? On 12/20/05, Olson, Curtis B <OlsonCB-ZM7Pm/iYlqnyG1zEObXtfA@public.gmane.org> wrote:> > > Stored Procedures is not the rails way............ > > > > All features that make our database, [business] smart we > > hate.......... > > It''s hard to disagree with that. However, I''m working (playing > actually) with an interesting quandary, specifically with the PostGIS > extensions to PostgreSql. Not really stored procedures, but much of the > functionality is encapsulated as functions within the database. If you > want to know the airports within 100 km of Berlin, you create a SELECT > statement with a function call embedded in it. > > This kinda rubs me the wrong way. If I''m using Active Record, this is > not just an attribute I can get to, I''ll have to make another round trip > to the database. But, I can''t see another way. There are some > geo-functions that could be calculated on the object already retrieved > (like say, what are the dimensions of a rectangle that encloses this > geo-shape) and which could be encapsulated at the object level, but > clearly finding how distinct geo-shapes relate to each other needs a > PostGIS query. Can this still be considered "the rails way"? > > Please do tell me if you find my thinking flawed, I''d love to be set > straight. > > Cheers, > Curtis Olson > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
That''s how I''ve been using stored procs. It''s fine to say stored procs aren''t part of "the Rails way", but the reality is that for many many apps that are a good fit for Rails, they''re mandatory. Stored procs are widely used to enforce security, by ensuring that only a controlled set of interfaces (i.e. stored procs and views) are made available to people wishing to access data. In these cases, either you use stored procs with Rails, or you use stored procs with something else (C#, Java, ...). I''m not prepared to sacrifice the productivity that comes with Rails when I need to work with stored procs. Stored procs also have some significant performance/scalability benefits compared to other solutions. Finally, many businesses quite rightly realise that their data is absolutely fundamental to their continued operation, and just aren''t going to throw open their databases to any (Rails) developer that might come along. You want to access their data - you play by their rules. If you want to use Rails for only green fields apps, or for small department-level stuff, that''s fine, but I think doing so greatly limits your ability to leverage Rails'' productivity benefits. Look at it this way - if Rails gives (say) a 5x productivity increase over Java, where are the truly noteworthy savings going to be? The app that would take 5 man-months in Java, or the app that would take 15 man-years in Java? On the other hand, I''m finding it hard to use an agile approach to app development that includes stored procs - for anything non-trivial, dependencies within and between stored procs start to become too difficult to track. If I''ve got (say) 50 stored procs and need to change one to e.g. cater for a new field, there can be a sizeable amount of testing required to ensure that the change doesn''t break another stored proc somewhere. If anyone can point me to a reference detailing a solution for this issue, I''d greatly appreciate it. Regards Dave M. On 12/21/05, Eden Brandeis <ebrandeis-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I have been watching the rails stored procedure discussion that keeps > popping up here and elsewhere for a while. > > Has anyone suggested that stored procedures be allowed if they are > associated as methods on models? ActiveRecord could include code to form > the association (e.g. set_stored_proc_method) and code to handle the return > values and to refresh the model if necessary. > > For example: > > # :stored_proc should be optional if the stored procedure name is the same > as the method name > # e.g. :doFunDBStuff has a stored procedure named doFunDBStuff > # overrides for parameters could also be allowed in case they cannot be > pulled from the schema > # or need a type definition or name change > class myModel < ActiveRecord::Base > set_stored_proc_method :doFunDBStuff, :stored_proc => "myStoredProc" > end > > What do you guys think? I know this "isn''t the Rails way", but wouldn''t > this make Rails even more useful without changing the preferred way of > working with Rails? > > > On 12/20/05, Olson, Curtis B <OlsonCB-ZM7Pm/iYlqnyG1zEObXtfA@public.gmane.org> wrote: > > > Stored Procedures is not the rails way............ > > > > > > All features that make our database, [business] smart we > > > hate.......... > > > > It''s hard to disagree with that. However, I''m working (playing > > actually) with an interesting quandary, specifically with the PostGIS > > extensions to PostgreSql. Not really stored procedures, but much of the > > functionality is encapsulated as functions within the database. If you > > want to know the airports within 100 km of Berlin, you create a SELECT > > statement with a function call embedded in it. > > > > This kinda rubs me the wrong way. If I''m using Active Record, this is > > not just an attribute I can get to, I''ll have to make another round trip > > to the database. But, I can''t see another way. There are some > > geo-functions that could be calculated on the object already retrieved > > (like say, what are the dimensions of a rectangle that encloses this > > geo-shape) and which could be encapsulated at the object level, but > > clearly finding how distinct geo-shapes relate to each other needs a > > PostGIS query. Can this still be considered "the rails way"? > > > > Please do tell me if you find my thinking flawed, I''d love to be set > > straight. > > > > Cheers, > > Curtis Olson > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
Leon Leslie wrote:> Stored Procedures is not the rails way............ > > All features that make our database, [business] smart we hate..........Agreed - even microsoft is heading that direction with LINQ. Stored procs are soooo 90''s ... -- Posted via http://www.ruby-forum.com/.