Series of querys versus temp table

[Jorge]

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.

[Jorge]

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.

[Jorge]

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?

[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

[Jorge]

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

[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.

[Jorge]

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 <fschietteca...@gmail.com>
wrote: