Re: Best approach for temp table creation, population, invocation of stored procedure, all within same session

[Frank Martínez]

Hi Steve,

You can do everything inside the stored procedure, then call it from mybatis.

Frank.

On Mon, Jan 7, 2013 at 12:20 PM, Steve <stephen...@gmail.com> wrote:

Steve

--
Frank D. Martínez M.

[Steve]

Hi Frank,

Thanks for the reply. Unfortunately in my use case I have the data in a collection in Java, and I want to write the IDs into a temp table via Mybatis and then invoke the proc on that data set so I cannot determine the data set from within the stored procedure itself. 

Appreciate the response though and any other ideas out there.

Thanks,

Steve

[Rob Sargent]

Can you not repeatedly write to the same temp table? (Clean up as part
of stored proc.)

[Frank Martínez]

Just to get some context, how many records do you insert in the temp table? 10? 100? 1000? Millions?

[Steve]

It will be in the range of up to 1000 Frank.
Cheers

[Steve]

Hi Rob,

Since multiple users can execute this function I cannot rely on a permanent table in the database. I'll need one per session, hence the requirement of the temp table, which will have to be created and dropped as part of each invocation of the top level method.

Thanks,
Stephen

[Frank Martínez]

Well, I see no problem with this, just:

1. Create a mapper with methods for table creation, population, stored procedure call and cleanup

2. Get the mybatis Session

3. Start a transaction

4. Invoke your mapped methods

5. Commit

So, what is the problem?

[Rob Sargent]

Bottom posting is most common on this list

I thought temp tables where isolated to sessions and separate sessions
could use same name for temp table.

[Steve]

Sounds good Frank.

Here's what I have from my Mapper file. The main part I'm unsure about is how to do a bulk insert into a table - is this syntax documented anywhere? I've taken the code below from another forum post.

From below I want to pass a list of MyObject and populate a temp table using that data.

Please let me know if I'm on the right track or if you can point me to the relevant documentation.

Many Thanks,
Steve


    <update id="createTempTable">
        CREATE TABLE #input_data (a char(12), b datetime)
    </update>
       
    <insert id="populateTempTable"
        parameterType="com.me.MyObject">
        INSERT #input_data (a, b)
        VALUES <foreach collection="_parameter" item="parm" open="(" close=")"
separator=",">#{parm.val1},#{parm.val2}</foreach>;
    </insert>
   
    <select id="executeBulkProc"
        parameterType="java.lang.String"
        statementType="CALLABLE">
        {call
        perform_bulk_operation (
        #{message,jdbcType=VARCHAR,mode=IN}
        )}
    </select>
   
    <update id="deleteTempTable">
        DROP TABLE #input_data
    </update>

[Frank Martínez]

looks ok, but you must create a temp table to avoid session conflicts

CREATE TEMPORARY TABLE .....

[Frank Martínez]

Is $input_data, not #input_data

[Frank Martínez]

Use $tmpTable NOT #tmpTable

El 20/01/2013 14:35, "Sid" <siddharth...@gmail.com> escribió:

Hi Frank,

I am pretty new to MyBatis and would need to do the same stuff as discussed in this post. 

The backend Database I am using is SQL Server 2008. But I am facing a concern. When I create a temp table using :

CREATE TABLE #tmpTable ( .. )

and in the next statement, I try to populate the temp table using:

INSERT INTO #tmpTable VALUES (#{val1}, #{val2}),

I get an exception that Object #tmpTable not found. Although I am using the same connection .

If I use ##tmpTable, it works fine. But here the issue is SQL Server creates a Global Temp table which is not session specific.

Could you please help me in this. I googled that using MyBatis, you need to escape #, else it takes it as a parameter argument.

Is it you cannot create Local temp tables with MyBatis on SQL Server.

Thanks in advance.

[Sid]

Hi Frank,

Once I implement your change, I get the following error:

### Error updating database.  Cause: java.sql.SQLException: Incorrect syntax near '$tmpAssignment'.

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### SQL: CREATE TABLE $tmpAssignment (   intAssignmentObjectID INT NOT NULL,   intClientObjectID INT NOT NULL,   vchAssignmentName VARCHAR(100) NOT NULL   )

### Cause: java.sql.SQLException: Incorrect syntax near '$tmpAssignment'.

[Frank Martínez]

Sorry, try ${tmpAssignment}

Please read the docs, it is very well explained there how and when to use string intetpolation ${...} instead of parameter binding #{...}

[Sid]

Hi Frank,

I have already tried ${table_name}, but this also gives me an error:

What happens here is the SQL query going to the database is :

CREATE TABLE {...}. The table name part MyBatis tries to resolve and as it doesn't get the value, it just passes the rest of the string without table name and hence DB throws an exception.

Regards,

Sid

[Frank Martínez]

Maybe you are not passing it to mybatis.

if you put ${something} in your SQL, you need to provide a getSomething() bean method in your paramenter object or a "something" key if the parameter is a Map.

Can you provide some of your code? Bean class, Mapper Interface, XML Mapper and the code where you call the mapper?

[Jeff Butler]

Forget the dollar sign ($), this has nothing to do with the issue.

In SQL server, you create a local temp table like this:

create table #foo...

You create a global temp table like this...

create table ##foo

These statements should work fine in MyBatis.  I don't believe you will have a problem with escaping the pound sign (#).  But if you do, you should enable logging to see what SQL is going to the db.

For local temp tables to work, you will have to make sure that all your statements are in the same transaction (or session) in MyBatis.

Jeff Butler

On Mon, Jan 21, 2013 at 9:11 AM, Sid <siddharth...@gmail.com> wrote:

[Bryan Shannon]

Just my 2 cents, I believe that the pound sign the user is trying to use is Sybase/SQL Server specific in regards to naming automatic temp tables. (when you use #tablename, it creates a temporary table with another name that disappears when that connection is dropped and is not visible to other connections)

I'm not sure I'm offering a solution, but some things to keep in mind, because we've tried this:
If you're using a connection pool and have multiple different transactions in Sybase, there will probably be a different connection for each one of them, and since a true "temp" table is per *connection*,  one transaction might not see the temp table that was created by the other.

Other than that, you may have an escaping issue with the # character (We still haven't made the full upgrade to the latest MyBatis, so I'm unsure).

What we have done in the past with connection pools, if you're interested, is create a "permanent" temp table.  Multiple users can use the table, we just create a column in that table that represents the user's session somehow between transactions. (UUID works well in this case).  We insert the rows with a given UUID, then after the transaction is a success, we delete the rows with that UUID.

All that being said, using string interpolation and adding a "getter" for your temp table name may help you to avoid any escaping issues etc.

I felt compelled to reply because once we figured out how to work with a temp table in Sybase, it worked quite fine in testing (where there was a single user), however it failed miserably when we had hundreds due to the way Sybase handles "temp" tables on a per-connection basis in our connection pool. Making a permanent temp table worked out well for us for years when we've needed it. My advice does not apply necessarily if you're creating, accessing, and dropping within a single call to a stored procedure.

Regards,

-Bryan

[Sid]

Hi Bryan,

 

Thanks for your pointers. The table name issue is now resolved using the @Param annotation. The problem now is even though the same connection is being used from the connection pool, still the INSERT on temp table doesn't work and I get the error "Invalid Object ID #tmpAssignment". Teh temp table, however, is created before and gives me no error while creation.

Any suggestion??

 

Regards,

Sid

[Shirish]

I am having the same issue. Does anyone have a solution to this? I am executing it in a single SqlSession. Surprisingly if the same are combined in a sql block in mapper xml and executed it seems to work fine.

Regards,

Shirish