Thanks again for your prompt attention. I'm porting stored procs i didnt code in the first place but my requirements are to keep everything in place , to create one code base that will work transparently across oracle and postgres drivers since we will be supporting the use of either db based on the client.
Indeed so far my examination of the code reveals that rhe use of cursors is to buffer the result set and not a fetch, examine and possibly bail out. I can build the transparency myself , my outsranding issue is that I dont know if thru sequel I can engage the native oracle cursor. Regarding paginate , I understand your concerns, however the specidic code that utilizes cursor support is used in an end of business day batch process where the db is locked out for any other update. Ill admit to being ignorant of the implications of "extends" -Charles On Jan 24, 2013 3:11 PM, "Jeremy Evans" <[email protected]> wrote: > On Thursday, January 24, 2013 10:45:56 AM UTC-8, Charles Monteiro wrote: >> >> sorry somehow hit the enter button. I'll repeat here: >> >> We are porting some stored procs and need a cross driver implementation >> specifically across oracle and postgres but ideally across all major >> popular rdms and at the very least MS Sequel Server. >> >> Our approach is to use the jdbc drivers since we want to deploy on Jruby >> for various reasons. >> >> Given that it seems that our only strategy would be to use pagination but >> just in case I want to create some leeway in case we decide for example to >> support the native pg driver which now Sequel has the use_cursor support. >> >> thus my idea is to do something like this: >> >> def cursor a_table >> >> a_table.paginate >> >> end >> >> the idea is that this will return a "paginated" view to which we can >> apply where criteria. >> > > I personally don't advocate using the pagination extension. It uses > extend at runtime, and offers relatively little in return. I've kept it > around for backwards compatibility reasons. Unless you really need the > convenience methods it provides (e.g. first_page?, last_page?) using > Dataset#limit instead is recommended. > > If you just want to process the whole dataset at once without keeping all > rows in memory, that's how Sequel is designed to work (results are yielded > one-at-a-time using Dataset#each). Now, many of the database drivers > Sequel supports do buffer all rows in memory, but that is outside of > Sequel's control. I think some of the JDBC drivers offer the ability to > not buffer all rows in memory, see > http://simplefeatures.wordpress.com/2011/09/30/jdbc-tweaks-to-enable-streaming-result-sets/. > However, it doesn't look like the PostgreSQL JDBC driver work arounds for > cursor use will work with Sequel (as Sequel uses its own transaction > handling and doesn't turn autocommit off). I'll be happy to consider > patches that allow jdbc/postgres to use cursors, if they don't cause other > problems. > > >> >> Here's an example of the stored proc code I'm trying to port to Sequel: >> >> CURSOR my_cursor RETURN my_table%ROWTYPE IS >> SELECT * FROM my_table where col1 = 'V' >> ORDER BY col2 >> FOR UPDATE; >> >> the idea is that later if need be I can add some instrospection to said >> method above to determine whether a native pg driver is being use , if so >> then leverage the "use_cursor" call but otherwise default to the use of >> pagination. >> > > If you are using the native pg driver, I would recommend using sequel_pg > and its streaming support instead of use_cursor. That won't work on jruby, > though. > > >> So concretely I guess I'm asking: >> >> >> 1. do paginated datasets implement all dataset behaviors and thus >> allow me to use the same filter , where and etc methods ? >> >> They do, since a paginated dataset is just a clone of a dataset that is > extended with a module. However, as I mentioned above, I recommend against > using the pagination extension in new code. > >> >> 1. what instrospection is available so that I can determine if I'm >> using a native pg driver i.e. do I just determine that by looking at the >> connection string or is there something more elegant. >> >> Database#database_type gives you the type of database you are connecting > to :postgres, :mssql, :oracle. > Database#adapter_scheme gives the type of adapter you are using :postgres, > :jdbc, :mysql2. Some adapters only support a single database type, some > support multiple database types. > > >> >> 1. If anything else comes to mind pls share:) >> >> This seems interesting, I'd appreciate it if you could share more details > of the implementation you chose after you have been using it for a while. > > If you have more questions, please ask. > > Thanks, > Jeremy > > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > Visit this group at http://groups.google.com/group/sequel-talk?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
