Re: Problem with Select statement
Hello, can you describe your table testtable please? On 9 Sep 2014, at 10:20, Kessler, Joerg joerg.kess...@sap.com wrote: Hi, I want to execute select statement on a table using a Java program and JDBC. The statement is actually not very difficult: SELECT MSG_NO, SEND_TO, CREATED_TIME, CONTENT, ENCRYPTION_KEY FROM TESTTABLE WHERE SEQ_ID = ? AND (MSGSTATE IS NULL OR MSGSTATE = 'A') When this statement is executed by a test I receive errors like Column 'A' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'A' is not a column in the target table. When I change the statement to SELECT MSG_NO, SEND_TO, CREATED_TIME, CONTENT, ENCRYPTION_KEY FROM TESTTABLE WHERE SEQ_ID = ? AND MSGSTATE IS NULL there is no problem. Also when I execute the above statement via Eclipse Database Development/SQL Scrapbook using a fix SEQ_ID the statement is executed without error. What am I doing wrong? Best Regards, Jörg
Re: Problem with Select statement
On 09/09/2014 10:20 AM, Kessler, Joerg wrote: Hi, I want to execute select statement on a table using a Java program and JDBC. The statement is actually not very difficult: SELECT MSG_NO, SEND_TO, CREATED_TIME, CONTENT, ENCRYPTION_KEY FROM TESTTABLE WHERE SEQ_ID = ? AND (MSGSTATE IS NULL OR MSGSTATE = 'A') What does the Java String value passed to prepareStatement() look like? Sounds like your single-quotes around A have been eaten somehow... When this statement is executed by a test I receive errors like Column 'A' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'A' is not a column in the target table. When I change the statement to SELECT MSG_NO, SEND_TO, CREATED_TIME, CONTENT, ENCRYPTION_KEY FROM TESTTABLE WHERE SEQ_ID = ? AND MSGSTATE IS NULL there is no problem. Also when I execute the above statement via Eclipse Database Development/SQL Scrapbook using a fix SEQ_ID the statement is executed without error. What am I doing wrong? Best Regards, Jörg -- Regards, Dyre
RE: Problem with Select statement
Thanks, you are right. That seems to be the only thing I did not check. I did not expect that the string gets changed. I have to figure out why this happens. Best regards, Jörg -Original Message- From: Dyre Tjeldvoll [mailto:dyre.tjeldv...@oracle.com] Sent: Dienstag, 9. September 2014 11:23 To: derby-user@db.apache.org Subject: Re: Problem with Select statement On 09/09/2014 10:20 AM, Kessler, Joerg wrote: Hi, I want to execute select statement on a table using a Java program and JDBC. The statement is actually not very difficult: SELECT MSG_NO, SEND_TO, CREATED_TIME, CONTENT, ENCRYPTION_KEY FROM TESTTABLE WHERE SEQ_ID = ? AND (MSGSTATE IS NULL OR MSGSTATE = 'A') What does the Java String value passed to prepareStatement() look like? Sounds like your single-quotes around A have been eaten somehow... When this statement is executed by a test I receive errors like Column 'A' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'A' is not a column in the target table. When I change the statement to SELECT MSG_NO, SEND_TO, CREATED_TIME, CONTENT, ENCRYPTION_KEY FROM TESTTABLE WHERE SEQ_ID = ? AND MSGSTATE IS NULL there is no problem. Also when I execute the above statement via Eclipse Database Development/SQL Scrapbook using a fix SEQ_ID the statement is executed without error. What am I doing wrong? Best Regards, Jörg -- Regards, Dyre
Improving performance of encrypted databases by increasing page cache
Database encryption turned out to be the performance bottleneck of my application. Running the same queries on the same data gives very different figures when the database is encrypted. Since I must keep the data encrypted on disk, I thought I could avoid the decryption overhead by increasing the page cache and warming up the cache by loading tables and indices at application startup. Virtually, I want to run queries against in-memory tables and indices. I run the Derby network server in its own dedicated VM process, gave the JVM 1GB of heap, and configured 400MB of page cache (4KB page size * 100K pages), database backup being 200MB. The performance gain is really impressive (as expected) once the cache has been warmed. Unfortunately, a quick test showed there must be lots of cache misses with real usage patterns. I wonder if it's simply a matter of sizing the cache (but how can 200MB of db not fit into 400MB of memory? Are record compressed on disk?), or if it doesn't work the way I think, and I should use a cache at the application level (that maybe I'll do anyhow, but if it workd, it'd be a big win for us) Raffaele
