Hello,
I have been trying to find the appropriate syntax to query from a user
defined function. I am dealing with a SQL Server 2008 database, and I am
using sqlalchemy 0.8.4 with pyodbc 3.0.7. The machine the python app is
running on is Windows 7 64 bit, but I am using 32 bit python. I followed
the pointers in this
thread<https://groups.google.com/forum/#!searchin/sqlalchemy/call$20database$20function/sqlalchemy/tYVxitn9j1A/L5URn6ryHWUJ>,
but it seems that I can't get sqlalchemy to behave properly. The sql I am
trying to execute looks like the following
select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638,
DEFAULT)
Note that there are no parameter names and the usage of the Default
keyword. Null can be used in place of the Default, but that could be
problematic if a parameter's default value isn't null. Furthermore, this
function can be joined to tables within the database like so
select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638,
DEFAULT) uf
left outer join db_table dt on uf.table_id = dt.id
Is there anyway to handle this situation? Below are the attempts that I
have tried
function_call =
select([column('table_id'),column('widget_type'),column('effective_date'),column('widget_id')],
from_obj=[func.some_user_defined_function(bindparam('effective_date_start',value='2013-12-01'),
bindparam('effective_date_end', value=None,quote=False),
bindparam('widget_type_list', value='3'),
bindparam('company_id', value=638),
bindparam('widget_id', value=None,quote=False))])
This generates the following SQL
SELECT table_id, widget_type, effective_date, widget_id
FROM some_user_defined_function(:effective_date_start, :effective_date_end,
:widget_type_list, :company_id, :widget_id)
however I get no results from executing it. I have also tried not using
bindparam
function_call =
select([column('emp_id'),column('plan_type'),column('effective_date'),column('history_answersheet_id')],
from_obj=[func.some_user_defined_function('2013-12-01', None, '1', 1591,
None)])
and then I get
SELECT table_id, widget_type, effective_date, widget_id
FROM some_user_defined_function(:some_user_defined_function_1, NULL,
:some_user_defined_function_2, :some_user_defined_function_3, NULL)
but again no results. (As a side note I am attempting to use a session to
execute these objects.
To eliminate the possiblity that the issue is within pyodbc I tried the
following
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=10.0.0.555;DATABASE=yup;UID=user;PWD=pass')
cursor = cnxn.cursor()
cursor.execute("select * from some_user_defined_function('2013-12-29',
NULL, '3', 638, NULL)")
for row in cursor:
print row
and that did work.
So I have two questions.
1. Why can't I see my results from the select objects I am using?
2. How can I pass Default as a parameter to the function?
Thanks in advance
--Dan Clark
--
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.
