For the parts I'm working on right now, this is perfect. Thank you.
Matt
On Friday, September 21, 2012 5:19:17 PM UTC-5, Michael Bayer wrote:
>
>
> On Sep 21, 2012, at 3:49 PM, matt g wrote:
>
> Hi,
> I'm working with a client that does most of their work in stored
> procedures on an oracle database. One of the things I'm trying to help them
> with is transitioning to use sqlalchemy for calling said procedures.
> For simple input and output types i was able to follow threads in this
> group and do something like this:
>
> db.execute(text('begin HELLO_WORLD.say_hi(:x_in, :x_out);
> end;',bindparams=[bindparam('x_in',String),outparam('x_out',String)]),x_in='matt').
>
> However, for most of their procedures, they actually have cursors in the
> outparams. The only way i have found to do it is to drop down into using a
> raw_connection and the cx_oracle types like so:
>
> engine = create_engine(dsn)
> con = engine.raw_connection()
> cur = conn.cursor()
> people=cur.var(cx_Oracle.CURSOR)
> groups=cur.var(cx_Oracle.CURSOR)
> params = [12345, people, groups]
> #12345 is a input param
> r = cur.callproc('list_people', params)
>
> Is there a way to keep this more abstract and within sqlalchemy instead of
> dropping down into cx_Oracle?
>
>
> sure....the "out parameter" idea is pretty much an oracle-only thing these
> days, so while we should be able to make this happen through the API, it
> obviously isn't backend-agnostic.
>
> the usual "out parameter" routine is like you have above, where we get the
> results back using the "out_parameters" member of the result:
>
> result = conn.execute(text('...', bindparams=[outparam('x', SomeType)]))
>
> result.out_parameters['x']
>
> this internally does what you're doing with cursor.var(). When you use
> String, it knows to use cx_Oracle.STRING because the cx_oracle dialect
> associates that DBAPI type with the String type. At result time it
> applies the String type to the result of Variable.getvalue().
>
> So, *assuming* the way cx_Oracle.CURSOR behaves here is that you get some
> kind of special object via getvalue(), no changes to SQLAlchemy would be
> needed. If you need access to other methods of Variable (see
> http://cx-oracle.sourceforge.net/html/variable.html) then we might have
> to come up with something more involved, like intercepting the parameters
> using after_cursor_execute() or something.
>
> To do this we just create a type:
>
> from sqlalchemy.types import TypeEngine
>
> class OracleCursorType(TypeEngine):
> def get_dbapi_type(self, dbapi):
> return dbapi.CURSOR
>
> If you use OracleCursorType in your outparam() function, you should get
> the "cursor" value back in result.out_parameters.
>
> If you want to give me a SQL expression that will actually return a
> "cursor" type, I can test this locally to iron out anything I'm missing, in
> case this doesn't work as is.
>
>
>
>
>
>
>
>
>
> thanks,
> Matt
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/u38qIICSt5AJ.
> To post to this group, send email to sqlal...@googlegroups.com<javascript:>
> .
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com <javascript:>.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/EDfRZCZy0UIJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
