On Jan 28, 2014, at 8:30 PM, a_t_smith <amos.t.sm...@gmail.com> wrote:
> Following up on earlier comments I tried manually calling the db function and
> passing the arrayvar built via cx_Oracle cursor and got "ORA-01484: arrays
> can only be bound to PL/SQL statements".
>
> Alternately, I found that manually wrapping the array values with the
> database type name "works", i.e. rp = session.execute('select
> some_db_func(int_array(1,2,3,4,5)) from dual') .
>
> I could not find a way to convert the int_array(1,2,3,4,5) part of the
> statement into a param though, so I'm not sure if/how to move forward with
> this either.
>
> I tried breaking this into two parts but it doesn't seem to know how to get
> the array out of resultproxy.
>
> arrayResult = session.execute('select int_array(1,2,3,4,5) from dual')
> funcResult = session.execute(session.query().from_statement("select
> some_db_func(:a) from dual").params(a=arrayResult))
>
> Is there a way to render a udt like class into literal part of query rather
> than binding it as a parameter?
there is, but it would kind of disqualify this as something we can add to
SQLAlchemy directly b.c. it’s basically a security hazard (as unlikely as it is
in this case).
essentially if the “literal_binds” flag is passed through to the compiler it
will render bound parameters inline. This flag can be set on a per-element
basis using the recipe below:
from sqlalchemy.sql.expression import BindParameter
from sqlalchemy.ext.compiler import compiles
class LiteralBindParam(BindParameter):
pass
@compiles(LiteralBindParam)
def literal_bind(element, compiler, **kw):
kw['literal_binds'] = True
return compiler.visit_bindparam(element, **kw)
from sqlalchemy.sql import select, column
stmt = select([column('x'), column('y')]).\
where(column('x') == 5).\
where(column('y') == LiteralBindParam(None, 7))
print stmt
will print:
SELECT x, y
WHERE x = :x_1 AND y = 7
from there, if the type of the LiteralBindParameter implements
“literal_processor()”, that controls how the literal value is rendered into the
statement.
>
> On Tuesday, January 28, 2014 3:26:25 PM UTC-8, Michael Bayer wrote:
>
> no, this is the part i was suggesting needs to be improved. for the moment,
> the OracleExecutionContext is looking for parameters that need special
> things. you can also intercept the parameters being sent to cx_oracle using
> a before_cursor_execute event, which might be a way to interact with the
> cursor directly without modifying the cx_oracle dialect directly.
>
> that is
>
> from sqlalchemy import event
>
> @event.listens_for(Engine, “before_cursor_execute”)
> def look_for_array_params(conn, cursor, statement, parameters, context,
> executemany):
> if context and context.compiled:
> for key in parameters:
> bindparam = context.compiled.binds[key]
> if isinstance(bindparam.type_, OracleArrayType):
> parameters[key] =
> cursor.call_special_oracle_api(parameters[key])
>
> this is sort of deep stuff so you’d need to familiarize a bit with the
> execution flow (assuming the above doesn’t work as written).
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
