Series of querys versus temp table

[Jorge-2]

Assuming:

1) I have a query1 that its used to retrieve some clients based on a
few parameters.
2) I have a lot of querys that use the data retrieved by query1. All
of them are executed in a row.

What i want:

Execute query1 just one time and reuse the data on the subsequent
querys.

What I've ben trying to do:

- Create a temp table with the data of query one, and then reuse it.
This was possible on one mapped statement, when i try to share the
temp table between statements the database says it does not exist.
- Watching myBatis logs i can see only one open connection. The
execution of my create table #temp... and then the error on the next
query saying that the temp table does not exist. After that the
connection is closed and returned to the pool.
- I'm using the mapper interfaces to access my SQL mapped statements.

Anyone have already tried something like that?

Thanks in advance for any help.

[Larry Meadors]

Do it in a stored procedure.

On Tue, Feb 21, 2012 at 12:18 PM, Jorge <[hidden email]> wrote:

> Assuming:
>
> 1) I have a query1 that its used to retrieve some clients based on a
> few parameters.
> 2) I have a lot of querys that use the data retrieved by query1. All
> of them are executed in a row.
>
> What i want:
>
> Execute query1 just one time and reuse the data on the subsequent
> querys.
>
> What I've ben trying to do:
>
> - Create a temp table with the data of query one, and then reuse it.
> This was possible on one mapped statement, when i try to share the
> temp table between statements the database says it does not exist.
> - Watching myBatis logs i can see only one open connection. The
> execution of my create table #temp... and then the error on the next
> query saying that the temp table does not exist. After that the
> connection is closed and returned to the pool.
> - I'm using the mapper interfaces to access my SQL mapped statements.
>
> Anyone have already tried something like that?
>
> Thanks in advance for any help.
>
>

[Jorge-2]

Thanks for the answer Larry.

I can't just do it all on a SP. Let me explain the cenario:

We have the tables from the main ERP of the company, and a client
system that will run on salesman computers, this will be used to send
the orders to our company. The querys that i mentioned before generate
the data to that system, they return different columns to populate
diferent tables on the client.

Even they are diferent tables there's a common factor between them.
For example: the addres from the customer, the main customer profile,
last orders from customer... On these i always need to get the
customer from one salesman, that's the part i want to put on the temp
table.

On 21 fev, 19:37, Larry Meadors <[hidden email]> wrote:

> Do it in a stored procedure.
>
>
>
>
>
>
>
> On Tue, Feb 21, 2012 at 12:18 PM, Jorge <[hidden email]> wrote:
> > Assuming:
>
> > 1) I have a query1 that its used to retrieve some clients based on a
> > few parameters.
> > 2) I have a lot of querys that use the data retrieved by query1. All
> > of them are executed in a row.
>
> > What i want:
>
> > Execute query1 just one time and reuse the data on the subsequent
> > querys.
>
> > What I've ben trying to do:
>
> > - Create a temp table with the data of query one, and then reuse it.
> > This was possible on one mapped statement, when i try to share the
> > temp table between statements the database says it does not exist.
> > - Watching myBatis logs i can see only one open connection. The
> > execution of my create table #temp... and then the error on the next
> > query saying that the temp table does not exist. After that the
> > connection is closed and returned to the pool.
> > - I'm using the mapper interfaces to access my SQL mapped statements.
>
> > Anyone have already tried something like that?
>
> > Thanks in advance for any help.

[Jorge-2]

To simplify, the problem is:

Having method1 and method2 mapped to sql statements on MyBatis config
file.
Method1 create and populate data on a temp table #t1.
Method2 query table #t1, and the database raise an error saying that
table does not exist.

If I have only one connection been open (i've checked the logs) why
method2 can't access the temp table?

On 21 fev, 20:06, Jorge <[hidden email]> wrote:

> Thanks for the answer Larry.
>
> I can't just do it all on a SP. Let me explain the cenario:
>
> We have the tables from the main ERP of the company, and a client
> system that will run on salesman computers, this will be used to send
> the orders to our company. The querys that i mentioned before generate
> the data to that system, they return different columns to populate
> diferent tables on the client.
>
> Even they are diferent tables there's a common factor between them.
> For example: the addres from the customer, the main customer profile,
> last orders from customer... On these i always need to get the
> customer from one salesman, that's the part i want to put on the temp
> table.
>
> On 21 fev, 19:37, Larry Meadors <[hidden email]> wrote:
>
>
>
>
>
>
>
> > Do it in a stored procedure.
>
> > On Tue, Feb 21, 2012 at 12:18 PM, Jorge <[hidden email]> wrote:
> > > Assuming:
>
> > > 1) I have a query1 that its used to retrieve some clients based on a
> > > few parameters.
> > > 2) I have a lot of querys that use the data retrieved by query1. All
> > > of them are executed in a row.
>
> > > What i want:
>
> > > Execute query1 just one time and reuse the data on the subsequent
> > > querys.
>
> > > What I've ben trying to do:
>
> > > - Create a temp table with the data of query one, and then reuse it.
> > > This was possible on one mapped statement, when i try to share the
> > > temp table between statements the database says it does not exist.
> > > - Watching myBatis logs i can see only one open connection. The
> > > execution of my create table #temp... and then the error on the next
> > > query saying that the temp table does not exist. After that the
> > > connection is closed and returned to the pool.
> > > - I'm using the mapper interfaces to access my SQL mapped statements.
>
> > > Anyone have already tried something like that?
>
> > > Thanks in advance for any help.

[Larry Meadors]

If you're using a connection pool, it might appear to be the same, but
be logically different.

I can't say what's going on - maybe some more details will get other
people thinking on it?

Larry



On Wed, Feb 22, 2012 at 6:25 AM, Jorge <[hidden email]> wrote:

> To simplify, the problem is:
>
> Having method1 and method2 mapped to sql statements on MyBatis config
> file.
> Method1 create and populate data on a temp table #t1.
> Method2 query table #t1, and the database raise an error saying that
> table does not exist.
>
> If I have only one connection been open (i've checked the logs) why
> method2 can't access the temp table?
>
> On 21 fev, 20:06, Jorge <[hidden email]> wrote:
>> Thanks for the answer Larry.
>>
>> I can't just do it all on a SP. Let me explain the cenario:
>>
>> We have the tables from the main ERP of the company, and a client
>> system that will run on salesman computers, this will be used to send
>> the orders to our company. The querys that i mentioned before generate
>> the data to that system, they return different columns to populate
>> diferent tables on the client.
>>
>> Even they are diferent tables there's a common factor between them.
>> For example: the addres from the customer, the main customer profile,
>> last orders from customer... On these i always need to get the
>> customer from one salesman, that's the part i want to put on the temp
>> table.
>>
>> On 21 fev, 19:37, Larry Meadors <[hidden email]> wrote:
>>
>>
>>
>>
>>
>>
>>
>> > Do it in a stored procedure.
>>
>> > On Tue, Feb 21, 2012 at 12:18 PM, Jorge <[hidden email]> wrote:
>> > > Assuming:
>>
>> > > 1) I have a query1 that its used to retrieve some clients based on a
>> > > few parameters.
>> > > 2) I have a lot of querys that use the data retrieved by query1. All
>> > > of them are executed in a row.
>>
>> > > What i want:
>>
>> > > Execute query1 just one time and reuse the data on the subsequent
>> > > querys.
>>
>> > > What I've ben trying to do:
>>
>> > > - Create a temp table with the data of query one, and then reuse it.
>> > > This was possible on one mapped statement, when i try to share the
>> > > temp table between statements the database says it does not exist.
>> > > - Watching myBatis logs i can see only one open connection. The
>> > > execution of my create table #temp... and then the error on the next
>> > > query saying that the temp table does not exist. After that the
>> > > connection is closed and returned to the pool.
>> > > - I'm using the mapper interfaces to access my SQL mapped statements.
>>
>> > > Anyone have already tried something like that?
>>
>> > > Thanks in advance for any help.

[Jorge-2]

Just switched to the UNPOOLED datasource and the problem pesists.

The execution log that i've mentioned:

DEBUG [main][22/02/2012 11:42:58:696] - ooo Connection Opened
DEBUG [main][22/02/2012 11:42:58:776] - ==>  Executing: CREATE TABLE
#tmpCliRep ( cod_emitente int ) INSERT INTO #tmpCliRep SELECT
cod_emitente FROM fun_getClientesUsuarioRep(?,?)
DEBUG [main][22/02/2012 11:42:58:776] - ==> Parameters: 275(Integer),
2(Integer)
DEBUG [main][22/02/2012 11:42:59:833] - ==>  Executing: select
cod_emitente CLI_CODIGO ,nome_abrev NOME_FANTASIA ,nome_emit
RAZAO_SOCIAL ,cgc CGC ,cidade NOME_CIDADE ,estado
SIGLA_ESTADO ,clientes.cod_uf COD_UF ,clientes.cod_cidade
COD_CIDADE ,CASE WHEN regioes_cidades.cod_cidade IS NOT NULL THEN
regioes_cidades.cod_regiao....
DEBUG [main][22/02/2012 11:42:59:833] - ==> Parameters: 3(String),
275(Integer), 3(String), 275(Integer)
ERROR [main][22/02/2012 11:42:59:855]
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLException: Invalid
object name '#tmpCliRep'.
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: Invalid object name '#tmpCliRep'.
        at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
        at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
80)
        at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
72)
        at
org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:
100)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
70)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
        at $Proxy1.getClientes(Unknown Source)
        at br.com.mercur.ws.Teste.testar(Teste.java:58)
        at br.com.mercur.ws.Teste.main(Teste.java:69)
Caused by: java.sql.SQLException: Invalid object name '#tmpCliRep'.
        at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:
368)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
        at
net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:
584)
        at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:
546)
        at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:
558)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at
org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:
45)
        at $Proxy2.execute(Unknown Source)
        at
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:
39)
        at
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:
55)
        at
org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:
41)
        at
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:
238)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:
112)
        at
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:
72)
        at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
78)
        ... 7 more
DEBUG [main][22/02/2012 11:42:59:868] - xxx Connection Closed

On 22 fev, 11:30, Larry Meadors <[hidden email]> wrote:

> If you're using a connection pool, it might appear to be the same, but
> be logically different.
>
> I can't say what's going on - maybe some more details will get other
> people thinking on it?
>
> Larry
>
>
>
>
>
>
>
> On Wed, Feb 22, 2012 at 6:25 AM, Jorge <[hidden email]> wrote:
> > To simplify, the problem is:
>
> > Having method1 and method2 mapped to sql statements on MyBatis config
> > file.
> > Method1 create and populate data on a temp table #t1.
> > Method2 query table #t1, and the database raise an error saying that
> > table does not exist.
>
> > If I have only one connection been open (i've checked the logs) why
> > method2 can't access the temp table?
>
> > On 21 fev, 20:06, Jorge <[hidden email]> wrote:
> >> Thanks for the answer Larry.
>
> >> I can't just do it all on a SP. Let me explain the cenario:
>
> >> We have the tables from the main ERP of the company, and a client
> >> system that will run on salesman computers, this will be used to send
> >> the orders to our company. The querys that i mentioned before generate
> >> the data to that system, they return different columns to populate
> >> diferent tables on the client.
>
> >> Even they are diferent tables there's a common factor between them.
> >> For example: the addres from the customer, the main customer profile,
> >> last orders from customer... On these i always need to get the
> >> customer from one salesman, that's the part i want to put on the temp
> >> table.
>
> >> On 21 fev, 19:37, Larry Meadors <[hidden email]> wrote:
>
> >> > Do it in a stored procedure.
>
> >> > On Tue, Feb 21, 2012 at 12:18 PM, Jorge <[hidden email]> wrote:
> >> > > Assuming:
>
> >> > > 1) I have a query1 that its used to retrieve some clients based on a
> >> > > few parameters.
> >> > > 2) I have a lot of querys that use the data retrieved by query1. All
> >> > > of them are executed in a row.
>
> >> > > What i want:
>
> >> > > Execute query1 just one time and reuse the data on the subsequent
> >> > > querys.
>
> >> > > What I've ben trying to do:
>
> >> > > - Create a temp table with the data of query one, and then reuse it.
> >> > > This was possible on one mapped statement, when i try to share the
> >> > > temp table between statements the database says it does not exist.
> >> > > - Watching myBatis logs i can see only one open connection. The
> >> > > execution of my create table #temp... and then the error on the next
> >> > > query saying that the temp table does not exist. After that the
> >> > > connection is closed and returned to the pool.
> >> > > - I'm using the mapper interfaces to access my SQL mapped statements.
>
> >> > > Anyone have already tried something like that?
>
> >> > > Thanks in advance for any help.

[François Schiettecatte]

I am not sure the unpooled data source will help you here, it just means that it opens connections when needed as opposed to pre-emptively, something along the line is closing your session to the database and it decides to drop your temp table.

Have you considered creating a temp table name and using that? By that I mean a table name that includes some unique ID?

Alternatively you could share the java code for the process your describe?

F.

On Feb 22, 2012, at 8:48 AM, Jorge wrote:

> Just switched to the UNPOOLED datasource and the problem pesists.
>
> The execution log that i've mentioned:
>
> DEBUG [main][22/02/2012 11:42:58:696] - ooo Connection Opened
> DEBUG [main][22/02/2012 11:42:58:776] - ==>  Executing: CREATE TABLE
> #tmpCliRep ( cod_emitente int ) INSERT INTO #tmpCliRep SELECT
> cod_emitente FROM fun_getClientesUsuarioRep(?,?)
> DEBUG [main][22/02/2012 11:42:58:776] - ==> Parameters: 275(Integer),
> 2(Integer)
> DEBUG [main][22/02/2012 11:42:59:833] - ==>  Executing: select
> cod_emitente CLI_CODIGO ,nome_abrev NOME_FANTASIA ,nome_emit
> RAZAO_SOCIAL ,cgc CGC ,cidade NOME_CIDADE ,estado
> SIGLA_ESTADO ,clientes.cod_uf COD_UF ,clientes.cod_cidade
> COD_CIDADE ,CASE WHEN regioes_cidades.cod_cidade IS NOT NULL THEN
> regioes_cidades.cod_regiao....
> DEBUG [main][22/02/2012 11:42:59:833] - ==> Parameters: 3(String),
> 275(Integer), 3(String), 275(Integer)
> ERROR [main][22/02/2012 11:42:59:855]
> org.apache.ibatis.exceptions.PersistenceException:
> ### Error querying database.  Cause: java.sql.SQLException: Invalid
> object name '#tmpCliRep'.
> ### The error may involve defaultParameterMap
> ### The error occurred while setting parameters
> ### Cause: java.sql.SQLException: Invalid object name '#tmpCliRep'.
> at
> org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
> 8)
> at
> org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
> 80)
> at
> org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
> 72)
> at
> org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:
> 100)
> at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
> 70)
> at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
> at $Proxy1.getClientes(Unknown Source)
> at br.com.mercur.ws.Teste.testar(Teste.java:58)
> at br.com.mercur.ws.Teste.main(Teste.java:69)
> Caused by: java.sql.SQLException: Invalid object name '#tmpCliRep'.
> at
> net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:
> 368)
> at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
> at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
> at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
> at
> net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:
> 584)
> at
> net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:
> 546)
> at
> net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:
> 558)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
> 39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
> 25)
> at java.lang.reflect.Method.invoke(Method.java:597)
> at
> org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:
> 45)
> at $Proxy2.execute(Unknown Source)
> at
> org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:
> 39)
> at
> org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:
> 55)
> at
> org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:
> 41)
> at
> org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:
> 238)
> at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:
> 112)
> at
> org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:
> 72)
> at
> org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
> 78)
> ... 7 more
> DEBUG [main][22/02/2012 11:42:59:868] - xxx Connection Closed
>
> On 22 fev, 11:30, Larry Meadors <[hidden email]> wrote:
>> If you're using a connection pool, it might appear to be the same, but
>> be logically different.
>>
>> I can't say what's going on - maybe some more details will get other
>> people thinking on it?
>>
>> Larry
>>
>>
>>
>>
>>
>>
>>
>> On Wed, Feb 22, 2012 at 6:25 AM, Jorge <[hidden email]> wrote:
>>> To simplify, the problem is:
>>
>>> Having method1 and method2 mapped to sql statements on MyBatis config
>>> file.
>>> Method1 create and populate data on a temp table #t1.
>>> Method2 query table #t1, and the database raise an error saying that
>>> table does not exist.
>>
>>> If I have only one connection been open (i've checked the logs) why
>>> method2 can't access the temp table?
>>
>>> On 21 fev, 20:06, Jorge <[hidden email]> wrote:
>>>> Thanks for the answer Larry.
>>
>>>> I can't just do it all on a SP. Let me explain the cenario:
>>
>>>> We have the tables from the main ERP of the company, and a client
>>>> system that will run on salesman computers, this will be used to send
>>>> the orders to our company. The querys that i mentioned before generate
>>>> the data to that system, they return different columns to populate
>>>> diferent tables on the client.
>>
>>>> Even they are diferent tables there's a common factor between them.
>>>> For example: the addres from the customer, the main customer profile,
>>>> last orders from customer... On these i always need to get the
>>>> customer from one salesman, that's the part i want to put on the temp
>>>> table.
>>
>>>> On 21 fev, 19:37, Larry Meadors <[hidden email]> wrote:
>>
>>>>> Do it in a stored procedure.
>>
>>>>> On Tue, Feb 21, 2012 at 12:18 PM, Jorge <[hidden email]> wrote:
>>>>>> Assuming:
>>
>>>>>> 1) I have a query1 that its used to retrieve some clients based on a
>>>>>> few parameters.
>>>>>> 2) I have a lot of querys that use the data retrieved by query1. All
>>>>>> of them are executed in a row.
>>
>>>>>> What i want:
>>
>>>>>> Execute query1 just one time and reuse the data on the subsequent
>>>>>> querys.
>>
>>>>>> What I've ben trying to do:
>>
>>>>>> - Create a temp table with the data of query one, and then reuse it.
>>>>>> This was possible on one mapped statement, when i try to share the
>>>>>> temp table between statements the database says it does not exist.
>>>>>> - Watching myBatis logs i can see only one open connection. The
>>>>>> execution of my create table #temp... and then the error on the next
>>>>>> query saying that the temp table does not exist. After that the
>>>>>> connection is closed and returned to the pool.
>>>>>> - I'm using the mapper interfaces to access my SQL mapped statements.
>>
>>>>>> Anyone have already tried something like that?
>>
>>>>>> Thanks in advance for any help.

[Jorge-2]

Thanks François!

Creating a real table, like you said, might be a solution... here is
the code and config files that i've been using:

// ----------------------------- Begin Test class

public class Teste {

    private Logger log = Logger.getLogger(Teste.class);
    private SqlSessionFactory sqlMapper = null;

    private void inicializeMyBatis() {
        Reader reader = null;
        try {
            log.info("Configuring MyBatis");
            String path = "br/com/mercur/dao/mybatis/
Configuration.xml";
            reader = Resources.getResourceAsReader(path);
            log.info(new StringBuilder().append("Config File:
").append(path));
            sqlMapper = new SqlSessionFactoryBuilder().build(reader);
            log.info("MyBatis configured!");
        } catch (IOException ex) {
            log.fatal("Error while reading MyBatis config file!", ex);
        } finally {
            try {
                reader.close();
            } catch (IOException ex) {
                log.fatal("Error while closing MyBatis config file!",
ex);
            }
        }
    }

    public void testar() {
        inicializeMyBatis();
        SqlSession session = sqlMapper.openSession();
        // Mapper interface for my querys
        DAO_CargaDadosRepresentante daoCargaDadosRep =
session.getMapper(DAO_CargaDadosRepresentante.class);

        HashMap<String,Object> params = new HashMap<String, Object>();
        params.put("cod_rep", 275);
        params.put("un_negocio", 3);
        params.put("cod_grupo", 2);

        try {
            daoCargaDadosRep.criaTempTables(params);
            daoCargaDadosRep.getClientes(275, 1, "3");
        } catch (Exception e) {
            log.error("", e);
        }
        session.close();
    }

    public static void main(String[] args) {
        Teste t = new Teste();
        t.testar();
    }

}


// ----------------------------- End Test class


// ----------------------------- Begin Configuration.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="server">
        <environment id="server">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver"
value="net.sourceforge.jtds.jdbc.Driver"/>
                <property name="url" value="jdbc:jtds:SqlServer://
server:1433/Prim_EMS"/>
                <property name="username" value=""/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="br/com/mercur/dao/mybatis/mapper/
CargaDadosGerais.xml"/>
        <mapper resource="br/com/mercur/dao/mybatis/mapper/
CargaDadosCliente.xml"/>
        <mapper resource="br/com/mercur/dao/mybatis/mapper/
CargaDadosPoliticaComercial.xml"/>
        <mapper resource="br/com/mercur/dao/mybatis/mapper/
CargaDadosRepresentante.xml"/>
        <mapper resource="br/com/mercur/dao/mybatis/mapper/
Estatistica.xml"/>
    </mappers>
</configuration>

// ----------------------------- End Configuration.xml


// ----------------------------- Begin CargaDadosRepresentante.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://
mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="br.com.mercur.dao.DAO_CargaDadosRepresentante">

  <update id="criaTempTables" parameterType="hashmap">
        <![CDATA[
            CREATE TABLE ##tmpCliRep ( cod_emitente int )

            INSERT INTO ##tmpCliRep
            SELECT cod_emitente FROM
fun_getClientesUsuarioRep(#{cod_rep},#{cod_grupo})
        ]]>
  </update>

  <select id="getClientes" resultType="hashmap">
       <![CDATA[
        select cod_emitente from ##tmpCliRep
       ]]>
  </select>

</mapper>

// ----------------------------- End Configuration.xml


On the last config file i've omitted the other mapped querys, just
left the two that are used on the test. I have tested the creation of
the temp table on a <insert>, <select> and <update> elements. Tried to
put a session.commit() after the
daoCargaDadosRep.criaTempTables(params); and I still cannot access the
temp table.

On 22 fev, 11:55, François Schiettecatte <[hidden email]>
wrote:

> I am not sure the unpooled data source will help you here, it just means that it opens connections when needed as opposed to pre-emptively, something along the line is closing your session to the database and it decides to drop your temp table.
>
> Have you considered creating a temp table name and using that? By that I mean a table name that includes some unique ID?
>
> Alternatively you could share the java code for the process your describe?
>
> F.
>
> On Feb 22, 2012, at 8:48 AM, Jorge wrote:
>
>
>
>
>
>
>
> > Just switched to the UNPOOLED datasource and the problem pesists.
>
> > The execution log that i've mentioned:
>
> > DEBUG [main][22/02/2012 11:42:58:696] - ooo Connection Opened
> > DEBUG [main][22/02/2012 11:42:58:776] - ==>  Executing: CREATE TABLE
> > #tmpCliRep ( cod_emitente int ) INSERT INTO #tmpCliRep SELECT
> > cod_emitente FROM fun_getClientesUsuarioRep(?,?)
> > DEBUG [main][22/02/2012 11:42:58:776] - ==> Parameters: 275(Integer),
> > 2(Integer)
> > DEBUG [main][22/02/2012 11:42:59:833] - ==>  Executing: select
> > cod_emitente CLI_CODIGO ,nome_abrev NOME_FANTASIA ,nome_emit
> > RAZAO_SOCIAL ,cgc CGC ,cidade NOME_CIDADE ,estado
> > SIGLA_ESTADO ,clientes.cod_uf COD_UF ,clientes.cod_cidade
> > COD_CIDADE ,CASE WHEN regioes_cidades.cod_cidade IS NOT NULL THEN
> > regioes_cidades.cod_regiao....
> > DEBUG [main][22/02/2012 11:42:59:833] - ==> Parameters: 3(String),
> > 275(Integer), 3(String), 275(Integer)
> > ERROR [main][22/02/2012 11:42:59:855]
> > org.apache.ibatis.exceptions.PersistenceException:
> > ### Error querying database.  Cause: java.sql.SQLException: Invalid
> > object name '#tmpCliRep'.
> > ### The error may involve defaultParameterMap
> > ### The error occurred while setting parameters
> > ### Cause: java.sql.SQLException: Invalid object name '#tmpCliRep'.
> >    at
> > org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactor y.java:
> > 8)
> >    at
> > org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlS ession.java:
> > 80)
> >    at
> > org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlS ession.java:
> > 72)
> >    at
> > org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:
> > 100)
> >    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
> > 70)
> >    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
> >    at $Proxy1.getClientes(Unknown Source)
> >    at br.com.mercur.ws.Teste.testar(Teste.java:58)
> >    at br.com.mercur.ws.Teste.main(Teste.java:69)
> > Caused by: java.sql.SQLException: Invalid object name '#tmpCliRep'.
> >    at
> > net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:
> > 368)
> >    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
> >    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
> >    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
> >    at
> > net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:
> > 584)
> >    at
> > net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:
> > 546)
> >    at
> > net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStateme nt.java:
> > 558)
> >    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >    at
> > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
> > 39)
> >    at
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImp l.java:
> > 25)
> >    at java.lang.reflect.Method.invoke(Method.java:597)
> >    at
> > org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedState mentLogger.java:
> > 45)
> >    at $Proxy2.execute(Unknown Source)
> >    at
> > org.apache.ibatis.executor.statement.PreparedStatementHandler.query(Prepare dStatementHandler.java:
> > 39)
> >    at
> > org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingS tatementHandler.java:
> > 55)
> >    at
> > org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:
> > 41)
> >    at
> > org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java :
> > 238)
> >    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:
> > 112)
> >    at
> > org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:
> > 72)
> >    at
> > org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlS ession.java:
> > 78)
> >    ... 7 more
> > DEBUG [main][22/02/2012 11:42:59:868] - xxx Connection Closed
>
> > On 22 fev, 11:30, Larry Meadors <[hidden email]> wrote:
> >> If you're using a connection pool, it might appear to be the same, but
> >> be logically different.
>
> >> I can't say what's going on - maybe some more details will get other
> >> people thinking on it?
>
> >> Larry
>
> >> On Wed, Feb 22, 2012 at 6:25 AM, Jorge <[hidden email]> wrote:
> >>> To simplify, the problem is:
>
> >>> Having method1 and method2 mapped to sql statements on MyBatis config
> >>> file.
> >>> Method1 create and populate data on a temp table #t1.
> >>> Method2 query table #t1, and the database raise an error saying that
> >>> table does not exist.
>
> >>> If I have only one connection been open (i've checked the logs) why
> >>> method2 can't access the temp table?
>
> >>> On 21 fev, 20:06, Jorge <[hidden email]> wrote:
> >>>> Thanks for the answer Larry.
>
> >>>> I can't just do it all on a SP. Let me explain the cenario:
>
> >>>> We have the tables from the main ERP of the company, and a client
> >>>> system that will run on salesman computers, this will be used to send
> >>>> the orders to our company. The querys that i mentioned before generate
> >>>> the data to that system, they return different columns to populate
> >>>> diferent tables on the client.
>
> >>>> Even they are diferent tables there's a common factor between them.
> >>>> For example: the addres from the customer, the main customer profile,
> >>>> last orders from customer... On these i always need to get the
> >>>> customer from one salesman, that's the part i want to put on the temp
> >>>> table.
>
> >>>> On 21 fev, 19:37, Larry Meadors <[hidden email]> wrote:
>
> >>>>> Do it in a stored procedure.
>
> >>>>> On Tue, Feb 21, 2012 at 12:18 PM, Jorge <[hidden email]> wrote:
> >>>>>> Assuming:
>
> >>>>>> 1) I have a query1 that its used to retrieve some clients based on a
> >>>>>> few parameters.
> >>>>>> 2) I have a lot of querys that use the data retrieved by query1. All
> >>>>>> of them are executed in a row.
>
> >>>>>> What i want:
>
> >>>>>> Execute query1 just one time and reuse the data on the subsequent
> >>>>>> querys.
>
> >>>>>> What I've ben trying to do:
>
> >>>>>> - Create a temp table with the data of query one, and then reuse it.
> >>>>>> This was possible on one mapped statement, when i try to share the
> >>>>>> temp table between statements the database says it does not exist.
> >>>>>> - Watching myBatis logs i can see only one open connection. The
> >>>>>> execution of my create table #temp... and then the error on the next
> >>>>>> query saying that the temp table does not exist. After that the
> >>>>>> connection is closed and returned to the pool.
> >>>>>> - I'm using the mapper interfaces to access my SQL mapped statements.
>
> >>>>>> Anyone have already tried something like that?
>
> >>>>>> Thanks in advance for any help.