Cannot access local temp table within the same connection

[suederade]

Within the same connection, from a connection pool, (and even using the same cursor) if I populate a local temp table (#Temp) in one execute call, I cannot query its results in another execute call. I receive an Invalid object name error.

cursor.execute(create_temp, params)
cursor.execute(select_from_temp)

The temp table is created by using CREATE TABLE and populated using INSERT INTO.

[HuangRicky]

are you sure you didnt close the connection, and you submitted your transaction?

then, you may try tempdb..#Temp

On Fri, Dec 6, 2019, 5:31 PM Steven Wade notifications@github.com wrote:

Within the same connection, from a connection pool, (and even using the same cursor) if I populate a local temp table (#Temp) in one execute call, I cannot query its results in another execute call. I receive an Invalid object name error.

cursor.execute(create_temp, params) cursor.execute(select_from_temp)

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/zillow/ctds/issues/63?email_source=notifications&email_token=AEWEAWPG2MTUGV637C54RFTQXLHCJA5CNFSM4JXCU3Q2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4H6YS7EQ, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEWEAWIDVG7ZNHZGJCLCPALQXLHCJANCNFSM4JXCU3QQ .

[suederade]

The lines are happening one after another, so the connection is not getting closed. I'm trying to move code from pymssql to this repo, so its previously working code and SQL queries. I can try tempdb..#Temp.

tempdb..#Temp results in the same error.

[HuangRicky]

tempdb is a mssql thing. you may need ti find out how to reference in other dbms.

also need to know whether all your cursor.execute are in one transaction. then u need to submit it first

On Fri, Dec 6, 2019, 5:39 PM Steven Wade notifications@github.com wrote:

The lines are happening one after another. I'm trying to move code from pymssql to this repo, so its previously working code and SQL queries. I can try tempdb..#Temp.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/zillow/ctds/issues/63?email_source=notifications&email_token=AEWEAWPD3HEJS6VWMUJWEODQXLIDHA5CNFSM4JXCU3Q2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEGFR6TQ#issuecomment-562765646, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEWEAWKZAWYRZ45E3XJO4EDQXLIDHANCNFSM4JXCU3QQ .

[suederade]

I am using mssql (already using the tempdb..#Temp mechanic). It's using implicit transactions, so I'm not sure what would cause a transaction to end between two execute statements.

If I have multiple queries will I always have to use some sort of next result set function?

[joshuahlang]

What are your ctds.connect() parameters? If you set autocommit=False, you'll have to explicitly commit the transaction following the creation of your temp table. Likewise you you set IMPLICIT_TRANSACTIONS to on

[suederade]

I did both of those things, so I'm guessing it has something to do with the implicit transactions, so I'll dig into that and close this.

[suederade]

Also this may be a stupid question, but how do I do an id IN :ids type query? I can't seem to send it a list or tuple. Will wrapping each individual ID in a SqlInt work or do I have to do (:ids) and then pass it that way?

[joshuahlang]

Currently sequences such as list and tuple aren't mapped to SQL types since there really isn't a logical one. I'd recommend generating the query string in python, e.g.

cursor.execute(
    'SELECT * FROM MyTable WHERE MyID IN ({0})'.format(
        ','.join(str(id_) for id_ in ids)
    )
)

[suederade]

Ok, sounds good. Thanks!