Nevermind about question 2. I really thought I was using parameters that would return something. Apparently not. However, question 2 still remains.
--Dan On Monday, December 30, 2013 7:12:04 PM UTC-5, Dan wrote: > > 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.
