In either case you mentioned, how we can handle different columns and no of columns of each temp table ?
~Shyam. On Thursday, February 6, 2014 1:43:08 AM UTC+5:30, Sha wrote: > > Thanks Lukas, > Let me investigate those more and come back. > > ~Shyam > > On Wednesday, February 5, 2014 10:42:17 PM UTC+5:30, Lukas Eder wrote: >> >> A very silly workaround that comes to my mind is to create a new regular >> table with the Java thread-id as a suffix: >> >> CREATE TABLE my_table_8375819 (...) >> >> >> And then use jOOQ's runtime table-mapping to map my_table onto >> my_table_8375819. >> ---------------- >> >> Another workaround is to create a single, shared regular table: >> >> CREATE TABLE my_table (...) >> >> >> And implement "row-level security" by using a THREAD_ID column. This >> "row-level security" can either be implemented explicitly by adding an >> additional predicate, or implicitly by implementing a VisitListener that >> transforms your SQL statements. >> ---------------- >> >> Both approaches are valid ways to implement multi-tenancy in a database. >> >> Of course, these are just workarounds and will never perform as fast as >> real T-SQL temporary tables. >> >> >> 2014-02-05 Sha <[email protected]>: >> >>> Thank you for your elaborative clarification. >>> >>> So to handle this kind of requirement , what alternative we have in jOOQ >>> ? i.e. any other approaches/work-a-rounds ? >>> >>> Regards, >>> ~Shyam >>> >>> >>> On Wednesday, February 5, 2014 3:37:35 PM UTC+5:30, Lukas Eder wrote: >>>> >>>> Hi Shyam, >>>> >>>> This query >>>> >>>> int count = ctx.update(cn).set(cn.field("already_exists"), 1) >>>>> .where( (cn.field("ID")).in( ) ).execute(); >>>> >>>> >>>> Renders this statement: >>>> >>>> UPDATE >>>>> (SELECT [cd].[HC1_LEAF_ID], >>>>> [cd].[HC2_LEAF_ID], >>>>> [cd].[HC3_LEAF_ID], >>>>> [cd].[PLANE_ID], >>>>> ? [already_exists], >>>>> row_number() over (order by [cd].[HC1_LEAF_ID] ASC) [ID] >>>>> FROM [DBO].[CATALOG_DETAIL] [cd] >>>>> WHERE [cd].[IS_PHANTOM] = ? >>>>> ) [cn] >>>>> SET [cn].[already_exists] = ? >>>>> WHERE 1 = 0 >>>> >>>> >>>> So, the error message you're getting: >>>> >>>> *It is giving error "Incorrect syntax near '('."* >>>> >>>> >>>> simply indicates that you cannot update derived tables in SQL Server >>>> (unlike Oracle). This can be seen here: >>>> http://technet.microsoft.com/de-de/library/ms177523.aspx >>>> >>>> T-SQL doesn't allow derived tables: >>>> >>>> UPDATE >>>> [ TOP ( expression ) [ PERCENT ] ] >>>> { { table_alias | <object> | rowset_function_limited >>>> [ WITH ( <Table_Hint_Limited> [ ...n ] ) ] >>>> } >>>> | @table_variable >>>> } >>>> >>>> >>>> It would have been great help if it is displayed a proper log in which >>>>> line number or character the error occurred. >>>> >>>> >>>> This is a SQL Server error message. jOOQ currently does not add any >>>> additional parser layer in-between to help you assess what kinds of syntax >>>> errors your SQL statement is producing. >>>> >>>> In the exception stack trace I see "*[cn] set [cn].[already_exists] = >>>>> ? where 1 = 0*" . >>>>> I dont know why there is a condition "where 1=0" interpretation here. >>>>> Totally confusing. >>>>> >>>> >>>> This is an emulation of your empty IN predicate >>>> >>>> (cn.field("ID")).in( ) >>>> >>>> >>>> In most SQL dialects, unfortunately, you cannot leave this predicate >>>> empty as you did above, so the same is emulated using 1 = 0, which is the >>>> most sensible interpretation of an empty IN predicate. >>>> >>>> >>>> General remark: >>>> >>>> I understand that what you're trying to do here is updating what you >>>> think is an emulation of a T-SQL temporary table. This attempt won't make >>>> any sense, because this emulation that I gave you was only valid for *that >>>> very specific* use-case. I tried to warn you several times that you >>>> should stop insisting on these T-SQL temporary tables. Even if that is a >>>> requirement for you, that requirement is maybe simply wrong. >>>> >>>> You simply cannot update this table. What sense would it make to update >>>> this bind variable, for instance? Where would T-SQL write this update to? >>>> This is like writing to /dev/null :-) >>>> >>>> UPDATE >>>> (SELECT [cd].[HC1_LEAF_ID], >>>> [cd].[HC2_LEAF_ID], >>>> [cd].[HC3_LEAF_ID], >>>> [cd].[PLANE_ID], >>>> ? [already_exists], >>>> row_number() over (order by [cd].[HC1_LEAF_ID] ASC) [ID] >>>> FROM [DBO].[CATALOG_DETAIL] [cd] >>>> WHERE [cd].[IS_PHANTOM] = ? >>>> ) [cn] >>>> SET [cn].[already_exists] = ? >>>> WHERE 1 = 0 >>>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "jOOQ User Group" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> For more options, visit https://groups.google.com/groups/opt_out. >>> >> >> -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
