Error with LOB field: file not found

[Wimpie]

Whenever I do a SELECT * to fetch a record containing a blob, I get
the following error:

"C:/Windows/Temp/h2.temp.d73926d6138df934.6.temp.db" File not found:
"C:/Windows/Temp/h2.temp.d73926d6138df934.6.temp.db" [90124-157]

Using Windows 2008 64 bits.

Any idea on what might be wrong?

Wim

[Thotheolh]

Your question is too vague please elaborate more. What is your H2
version and what exact errors are there ? Do specify your JDBC
connection settings accurately if possible. The steps you have taken
by providing a reproduce-able test case would be welcomed, otherwise a
step-by-step description as accurate as possible on what exactly
happened.

[Wimpie]

I'm using version 157.

Connection string:
jdbc:h2:tcp://127.0.0.1/c:/db/mydbdir/mydb;CIPHER=XTEA

I should add that the query works fine in the H2 web interface.
However, I'm using ColdFusion to connect to H2, and that's when I get
the "file not found" error when I query a db with LOBs.

Wim

[Thomas Mueller]

Hi,

Probably ColdFusion uses an unusual combination of JDBC method calls.
Could you append ;TRACE_LEVEL_FILE=3 to the database URL and then send
me all the *.trace.db files? There should be at least one file on the
client, and one where the H2 server runs. Or maybe be easier, use an
embedded URL (in that case you will only get one .trace.db file):
jdbc:h2:c:/db/mydbdir/mydb;CIPHER=XTEA;trace_level_file=3

Regards,
Thomas

[Wimpie]

Thomas,

Sorry for the delay. Here are the two files you've asked for. I hope
this "bug" can be found. It's the only thing that keeps me from
switching from Derby to H2.

Wim

FILE 1:
___________________________________

07-08 15:10:59 database: opening C:\db\mc\mydb (build 155)
07-08 15:10:59 fileLock: load {method=file,
id=130e65767d3978a588c9419afe4b5a5764bed40c13e}
07-08 15:10:59 fileLock: load {method=file,
id=130e65767d3978a588c9419afe4b5a5764bed40c13e}
07-08 15:10:59 fileLock: save
{id=13109df60c80b30d41327012a25c304e429879c57e5, method=file}
07-08 15:11:01 fileLock: load {method=file,
id=130e65767d3978a588c9419afe4b5a5764bed40c13e}
07-08 15:11:01 fileLock: load {method=file,
id=130e65767d3978a588c9419afe4b5a5764bed40c13e}
07-08 15:11:02 database: opening C:\db\mc\mydb (build 155)
07-08 15:11:02 fileLock: load {method=file,
id=130e65767d3978a588c9419afe4b5a5764bed40c13e}
07-08 15:11:02 fileLock: load {method=file,
id=130e65767d3978a588c9419afe4b5a5764bed40c13e}
07-08 15:11:02 fileLock: save
{id=13109df6ab652a570a6ca1aa4edc2dd1008f5a57135, method=file}
07-08 15:11:04 fileLock: load {method=file,
id=130e65767d3978a588c9419afe4b5a5764bed40c13e}
07-08 15:11:04 fileLock: load {method=file,
id=130e65767d3978a588c9419afe4b5a5764bed40c13e}
07-08 15:13:39 jdbc[25]:
/*SQL t:1310130819239*/SET TRACE_LEVEL_FILE 3;
07-08 15:13:39 command: slow query: 1.310.130.819.248 ms
07-08 15:13:39 jdbc[25]:
/*SQL */COMMIT;
07-08 15:13:39 jdbc[25]:
/*SQL l:15*/SET LOCK_MODE ? {1: 3};
07-08 15:13:39 jdbc[25]:
/*SQL #:1*/CALL READONLY();
07-08 15:13:39 jdbc[25]:
/*SQL */COMMIT;
07-08 15:13:39 jdbc[25]:
/*SQL l:15*/SET LOCK_MODE ? {1: 3};
07-08 15:13:39 jdbc[25]:
/*SQL */COMMIT;
07-08 15:13:39 jdbc[25]:
/*SQL l:15*/SET LOCK_MODE ? {1: 3};
07-08 15:13:43 lock: 25 shared read lock requesting for mydbusers
07-08 15:13:43 jdbc[25]:
/*SQL #:1*/select (originalpicture) as l from mydbusers where
userid=25874;
07-08 15:13:43 lock: 1 shared read lock requesting for LOB_MAP
07-08 15:13:43 lock: 1 shared read lock requesting for LOB_DATA
07-08 15:13:43 lock: 1 shared read lock requesting for LOB_MAP
07-08 15:13:43 lock: 1 shared read lock requesting for LOB_DATA
07-08 15:13:43 lock: 1 shared read lock requesting for LOB_MAP
07-08 15:13:43 lock: 1 shared read lock requesting for LOB_DATA
07-08 15:13:43 lock: 1 shared read lock requesting for LOB_MAP
07-08 15:13:43 lock: 1 shared read lock requesting for LOB_DATA
07-08 15:13:43 lock: 1 shared read lock requesting for LOB_MAP
07-08 15:13:43 lock: 1 shared read lock requesting for LOB_DATA
07-08 15:13:43 jdbc[25]:
/*SQL #:1*/CALL DATABASE();
07-08 15:13:43 jdbc[25]:
/*SQL */COMMIT;
07-08 15:13:43 jdbc[25]:
/*SQL l:15*/SET LOCK_MODE ? {1: 3};



FILE 2:
_________________________

07-08 15:13:39 jdbc:
/**/Connection conn179 = DriverManager.getConnection("jdbc:h2:tcp://
127.0.0.1/c:/db/mc/mydb;CIPHER=XTEA;trace_level_file=3",
"ADMINISTRATOR", "");
07-08 15:13:39 jdbc:
/**/conn179.setTransactionIsolation(2);
07-08 15:13:39 jdbc:
/**/conn179.commit();
07-08 15:13:39 jdbc: SESSION_PREPARE_READ_PARAMS 0
07-08 15:13:39 jdbc: COMMAND_EXECUTE_UPDATE 0
07-08 15:13:39 jdbc: SESSION_PREPARE_READ_PARAMS 1
07-08 15:13:39 jdbc: COMMAND_EXECUTE_UPDATE 1
07-08 15:13:39 jdbc:
/**/conn179.isReadOnly();
07-08 15:13:39 jdbc: SESSION_PREPARE_READ_PARAMS 2
07-08 15:13:39 jdbc: COMMAND_EXECUTE_QUERY 2
07-08 15:13:39 jdbc: RESULT_CLOSE 3
07-08 15:13:39 jdbc:
/**/conn179.setAutoCommit(true);
07-08 15:13:39 jdbc: SESSION_SET_AUTOCOMMIT 1
07-08 15:13:39 jdbc:
/**/conn179.setTransactionIsolation(2);
07-08 15:13:39 jdbc:
/**/conn179.commit();
07-08 15:13:39 jdbc: COMMAND_EXECUTE_UPDATE 0
07-08 15:13:39 jdbc: COMMAND_EXECUTE_UPDATE 1
07-08 15:13:39 jdbc:
/**/conn179.setReadOnly(false);
07-08 15:13:39 jdbc:
/**/conn179.isClosed();
07-08 15:13:39 jdbc:
/**/conn179.setAutoCommit(true);
07-08 15:13:39 jdbc: SESSION_SET_AUTOCOMMIT 1
07-08 15:13:39 jdbc:
/**/conn179.setTransactionIsolation(2);
07-08 15:13:39 jdbc:
/**/conn179.commit();
07-08 15:13:39 jdbc: COMMAND_EXECUTE_UPDATE 0
07-08 15:13:39 jdbc: COMMAND_EXECUTE_UPDATE 1
07-08 15:13:39 jdbc:
/**/conn179.setReadOnly(false);
07-08 15:13:43 jdbc:
/**/conn179.isClosed();
07-08 15:13:43 jdbc:
/**/conn179.getAutoCommit();
07-08 15:13:43 jdbc:
/**/DatabaseMetaData dbMeta2933 = conn179.getMetaData();
07-08 15:13:43 jdbc:
/**/dbMeta2933.supportsGetGeneratedKeys();
07-08 15:13:43 jdbc:
/**/Statement stat2852 = conn179.createStatement();
07-08 15:13:43 jdbc:
/**/stat2852.setMaxRows(1);
07-08 15:13:43 jdbc:
/**/stat2852.execute("select (originalpicture) as l from mydbusers
where userid=25874", 1);
07-08 15:13:43 jdbc: SESSION_PREPARE_READ_PARAMS 4
07-08 15:13:43 jdbc: COMMAND_EXECUTE_QUERY 4
07-08 15:13:43 jdbc: RESULT_CLOSE 5
07-08 15:13:43 jdbc: COMMAND_CLOSE 4
07-08 15:13:43 jdbc:
/**/stat2852.getUpdateCount();
07-08 15:13:43 jdbc:
/**/ResultSet rs3008 = stat2852.getResultSet();
07-08 15:13:43 jdbc:
/**/ResultSetMetaData rsMeta23991 = rs3008.getMetaData();
07-08 15:13:43 jdbc:
/**/conn179.getCatalog();
07-08 15:13:43 jdbc: SESSION_PREPARE_READ_PARAMS 6
07-08 15:13:43 jdbc: COMMAND_EXECUTE_QUERY 6
07-08 15:13:43 jdbc: RESULT_CLOSE 7
07-08 15:13:43 jdbc: COMMAND_CLOSE 6
07-08 15:13:43 jdbc:
/**/rsMeta23991.getColumnCount();
07-08 15:13:43 jdbc:
/**/rsMeta23991.getColumnLabel(1);
07-08 15:13:43 jdbc:
/**/rsMeta23991.isCaseSensitive(1);
07-08 15:13:43 jdbc:
/**/rsMeta23991.getColumnType(1);
07-08 15:13:43 jdbc:
/**/rsMeta23991.getColumnTypeName(1);
07-08 15:13:43 jdbc:
/**/rs3008.next();
07-08 15:13:43 jdbc:
/**/ResultSetMetaData rsMeta23992 = rs3008.getMetaData();
07-08 15:13:43 jdbc:
/**/conn179.getCatalog();
07-08 15:13:43 jdbc:
/**/rs3008.getObject(1);
07-08 15:13:43 jdbc:
/**/rs3008.next();
07-08 15:13:43 jdbc:
/**/blob24.getBinaryStream();
07-08 15:13:43 jdbc: exception
org.h2.jdbc.JdbcSQLException: Datei nicht gefunden: "C:\Windows\Temp
\h2.temp.ca4c9ff6786cdf2a.14.temp.db"
File not found: "C:\Windows\Temp\h2.temp.ca4c9ff6786cdf2a.
14.temp.db" [90124-155]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.engine.SessionRemote.openFile(SessionRemote.java:608)
at org.h2.value.ValueLobDb.getInputStream(ValueLobDb.java:273)
at org.h2.jdbc.JdbcBlob.getBinaryStream(JdbcBlob.java:154)
at coldfusion.sql.QueryTable.resolveObject(QueryTable.java:486)
at coldfusion.sql.QueryTable.populate(QueryTable.java:418)
at coldfusion.sql.Executive.getRowSet(Executive.java:581)
at coldfusion.sql.Executive.executeQuery(Executive.java:1383)
at coldfusion.sql.Executive.executeQuery(Executive.java:1127)
at coldfusion.sql.Executive.executeQuery(Executive.java:1058)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:341)
at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:915)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:590)
at cftest2ecfm1683294928.runPage(C:\ColdFusion9\wwwroot\test.cfm:2)
at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231)
at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:416)
at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:
381)
at
coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:
48)
at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:
40)
at coldfusion.filter.PathFilter.invoke(PathFilter.java:94)
at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70)
at
coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:
28)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)
at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:
22)
at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62)
at coldfusion.CfmServlet.service(CfmServlet.java:200)
at
coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:
89)
at jrun.servlet.FilterChain.doFilter(FilterChain.java:86)
at
coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:
42)
at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:
46)
at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)
at jrun.servlet.FilterChain.service(FilterChain.java:101)
at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
at
jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:
286)
at
jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:
543)
at jrun.servlet.http.WebService.invokeRunnable(WebService.java:172)
at jrunx.scheduler.ThreadPool
$DownstreamMetrics.invokeRunnable(ThreadPool.java:320)
at jrunx.scheduler.ThreadPool
$ThreadThrottle.invokeRunnable(ThreadPool.java:428)
at jrunx.scheduler.ThreadPool
$UpstreamMetrics.invokeRunnable(ThreadPool.java:266)
at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)
07-08 15:13:43 jdbc:
/**/stat2852.setMaxRows(0);
07-08 15:13:43 jdbc:
/**/stat2852.close();
07-08 15:13:43 jdbc:
/**/conn179.setAutoCommit(true);
07-08 15:13:43 jdbc: SESSION_SET_AUTOCOMMIT 1
07-08 15:13:43 jdbc:
/**/conn179.setTransactionIsolation(2);
07-08 15:13:43 jdbc:
/**/conn179.commit();
07-08 15:13:43 jdbc: COMMAND_EXECUTE_UPDATE 0
07-08 15:13:43 jdbc: COMMAND_EXECUTE_UPDATE 1
07-08 15:13:43 jdbc:
/**/conn179.setReadOnly(false);


On Jul 2, 2:42 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> Probably ColdFusion uses an unusual combination of JDBC method calls.
> Could you append ;TRACE_LEVEL_FILE=3 to the database URL and then send

> me all the *.trace.db files? There should be at least onefileon the

[Thomas Mueller]

Hi,

The problem is that ColdFusion tries to read from the Clob after the
result set has been closed. I can reproduce the problem:

ResultSet rs = stat.executeQuery("select * from test");
rs.next();
Clob c = (Clob) rs.getClob(2);
while(rs.next());
String s = c.getSubString(1, 20000);

H2 doesn't print a nice error message; the error message of Apache
Derby is better:

"You cannot invoke other java.sql.Clob/java.sql.Blob methods after
calling the free() method or after the Blob/Clob's transaction has
been committed or rolled back."

So, I'm afraid there is nothing I can do to solve the problem. It
doesn't look like a bug in H2; it looks like ColdFusion doesn't follow
the JDBC spec here. Maybe the problem is fixed in a newer version of
ColdFusion (if it is really ColdFusion that is calling those methods)?

Regards,
Thomas

[Wimpie]

Thomas,

This is very strange. Coldfusion has no problems at all with BLOBS
from Derby, SQL server, Oracle, and ALL other databases.. ONLY with
H2. ColdFusion uses JDBC for its database communication. I'm using the
latest Java runtime, and the latest version of ColdFusion with all
patched applied.

Wim

Thomas Mueller schreef:

[Thomas Mueller]

Hi,

I see, but I'm afraid the log files clearly point to a bug in
Coldfusion. I can't tell why it works with other databases I'm afraid,
specially because it doesn't work for Derby in my test case.

Regards,
Thomas