date:20150730

[h2] Connection Properties for Linked Tables

2015-07-30 Thread Nicholas Whitehead

Is there any way to specify JDBC connection properties when creating a 
linked table ?
I am trying to link to an Apache Phoenix database. A basic table is created 
with no issue, but I really need to create the link with a SQL statement.
However, the SQL statement uses a Phoenix User Defined Function and they 
are disallowed unless the connection is made with the connection property 
*phoenix.functions.allowUserDefinedFunctions=true*.

Thanks !

//Nicholas

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] index for OR clause

2015-07-30 Thread Sylvain Pointeau

hello,

actually it was in an update, I have 5 fields, each field updated by a
lookup in another table.
This lookup query had to be done using a OR.
I could split the update in 2 so it is now using index.

However I think it would be really the best if H2 could manage it.
For instance sqlite is using 2 indices.

note: ...another wish is to have the CTE. SQLite also has this
functionality. I miss it when I am using H2.

Best regards,
Sylvain










On Thu, Jul 30, 2015 at 6:02 PM, Thomas Mueller <
thomas.tom.muel...@gmail.com> wrote:

> Hi,
>
> H2 currently uses only one index. What you could try is using "union":
>
> select * from TEST where a = '123' and b = '456' and c='PP'
> union
> select * from TEST where a2='1234' and b = '456' and c='PP'
>
> Ideally, H2 should automatically do that for you. But well, right now, it
> doesn't
>
> Regards,
> Thomas
>
>
> On Thursday, July 30, 2015, Steve McLeod  wrote:
>
>> Indexing for an OR clause that references two separate columns is a
>> difficult task.
>>
>> Does your use case tolerate an index on columns b and c only?
>>
>> When I add this index to your example code:
>> create index IDX_TEST_4 on TEST(b,c);
>>
>> I get this query plan:
>> SELECT
>> TEST.A,
>> TEST.A2,
>> TEST.B,
>> TEST.C
>> FROM PUBLIC.TEST
>> /* PUBLIC.IDX_TEST_4: C = 'PP'
>> AND B = '456'
>>  */
>> WHERE (C = 'PP')
>> AND ((B = '456')
>> AND ((A = '123')
>> OR (A2 = '1234')));
>>
>> On Tuesday, 28 July 2015 23:23:38 UTC+2, epsilon68 wrote:
>>>
>>>
>>> On Tue, Jul 28, 2015 at 8:53 AM, Noel Grandin 
>>> wrote:
>>>

 Try doing this:

 select * from TEST where (a = '123' and b = '456' and c='PP') or
 (a2='1234' and b = '456' and c='PP');
>>>
>>>
>>> same result
>>>
>>> explain select * from TEST where (a = '123' and b = '456' and c='PP') or
>>> (a2='1234' and b = '456' and c='PP');
>>> PLAN
>>> 
>>> SELECT
>>> TEST.A,
>>> TEST.A2,
>>> TEST.B,
>>> TEST.C
>>> FROM PUBLIC.TEST
>>> /* PUBLIC.TEST.tableScan */
>>> WHERE ((C = 'PP')
>>> AND ((A = '123')
>>> AND (B = '456')))
>>> OR ((C = 'PP')
>>> AND ((A2 = '1234')
>>> AND (B = '456')))
>>>
>>>  --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to h2-database+unsubscr...@googlegroups.com.
>> To post to this group, send email to h2-database@googlegroups.com.
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>  --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com.
> To post to this group, send email to h2-database@googlegroups.com.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Mixed mode, auto_server and remote access question

2015-07-30 Thread Thomas Mueller

Hi,

I would probably not use the auto-server mode in production. Instead, I
suggest to use the embedded mode, plus (to be able to connect while your
application is running), start the H2 TCP server.

> when its impossible to connect from external in mixed mode

Well it is possible. If the database file is accessible externally. H2
doesn't know that. Possible, but not recommended.

> If i would go ahead and mount my server's disk via sshfs or nfs

I wouldn't do that. In the past, I had quite big problems using NFS. In
theory, it's possible, but I would try to avoid it.

Regards,
Thomas



On Wednesday, July 29, 2015,  wrote:

> Hello,
>
> I am playing around with H2 for a while now. I do this mostly locally
> (testing the App during development).
> Recently I started to deploy my app to a public server. My desired usage
> scenario for H2 looks roughly like this:
>
> - I decided for an encrypted database file, in case somebody can copy it
> due to a security hole in some other service.
> - My app starts the db-server in mixed mode, and connects to it, to do its
> thing.
> - My url looks like
> this: 
> jdbc:h2:file:db/testdb;CIPHER=AES;TRACE_LEVEL_FILE=3;TRACE_MAX_FILE_SIZE=16384;
> AUTOCOMMIT=ON;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=9090;
> - Almost all of the time there will be only one client.
>
> Additionally I would like to connect from a different computer. This will
> be very rarely, and always manually.
> Mostly to see whats in there, check logs, do small schema modifications.
> Thats why i picked mixed mode and auto_server in the first place.
>
> From what i gathered this is unfortunately not possible, as all clients
> need to run on the same computer, to have access to
> the .lock file. I had a look and there is not really something special in
> there.
>
> Now i do understand this limitation, and while it is bad for my desired
> usage scenario, i can just start a normal (tcp-based)
> server via the API. I found some Code snippets on stack exchange, so I
> will try this soon.
>
> What has me confused is that the server, in mixed mode, does not bind to
> localhost (127.0.0.1) but to 0.0.0.0
> (or in my case the respective ipv6 addresses) I gathered that from the
> following netstat line:
>
> netstat -tulpen
> Proto Recv-Q Send-Q Local Address   Foreign Address State
>   User   Inode  PID/Program name
> ...
> tcp6   0  0 :::9090 :::*LISTEN
>  1002   625293229262/java
> ...
>
> And that has me confused, when its impossible to connect from external in
> mixed mode, why make it available
> to external interfaces? If i would go ahead and mount my server's disk via
> sshfs or nfs (or any other network file System), would that work?
>
> I googled for a while now, but did not found an answer while it binds to
> public interfaces.
>
> I would be very thankful if somebody cleared that up.
>
> --
> yasuo
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com
> 
> .
> To post to this group, send email to h2-database@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Spring boot + flyway +Caused by: org.h2.jdbc.JdbcSQLException: Table "xxxxx" not found; SQL statement:

2015-07-30 Thread Thomas Mueller

Hi,

I would try with an embedded database, and an absolute path for the
database file name.

Sure, it's possible to use an in-memory database, but I guess for some
reason (shutdown for example), the database is not there when you think it
is.

Regards,
Thomas

On Tuesday, July 28, 2015, Abid Hossain khan  wrote:

> I am using H2 for unit testing  in Spring boot  along with flyway
> application and getting bellow error.
>
> Caused by: org.hibernate.exception.SQLGrammarException: could not prepare
> statement
> at
> org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
> at
> org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
> at
> org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
> at
> org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196)
> at
> org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160)
> at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1885)
> at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
> at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
> at org.hibernate.loader.Loader.doQuery(Loader.java:910)
> at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
> at org.hibernate.loader.Loader.doList(Loader.java:2554)
> at org.hibernate.loader.Loader.doList(Loader.java:2540)
> at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
> at org.hibernate.loader.Loader.list(Loader.java:2365)
> at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497)
> at
> org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
> at
> org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236)
> at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1300)
> at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
> at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
> at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:495)
> at
> org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getSingleResult(CriteriaQueryTypeQueryAdapter.java:71)
> at
> org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:202)
> at
> org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
> at
> org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:97)
> at
> org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:88)
> at
> org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:395)
> at
> org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:373)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
> at
> org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:486)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
> at
> org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
> at
> org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
> at
> org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
> at
> org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
> ... 80 more
> Caused by: org.h2.jdbc.JdbcSQLException: Table "user" not found; SQL
> statement:
> select user0_.id as id1_6_, user0_.created_by as created16_6_,
> user0_.created_date as created_2_6_, user0_.last_modified_by as
> last_mo17_6_, user0_.last_modified_date as last_mod3_6_, user0_.status as
> status4_6_, user0_.version as version5_6_, user0_.avatar_name as
> avatar_n6_6_, user0_.avatar_url as avatar_u7_6_, user0_.dob as dob8_6_,
> user0_.email as email9_6_, user0_.failed_login_attempt as failed_10_6_,
> user0_.first_name as first_n11_6_, user0_.gender as gender12_6_,
> user0_.last_name as last_na13_6_, user0_.last_password_update_date as
> last_pa14_6_, user0_.password as passwor15_6_ from cm_user user0_ where
> user0_.email=? [42102-187]
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
> at org.h2.message.DbExce

[h2] index for OR clause

2015-07-30 Thread Thomas Mueller

Hi,

H2 currently uses only one index. What you could try is using "union":

select * from TEST where a = '123' and b = '456' and c='PP'
union
select * from TEST where a2='1234' and b = '456' and c='PP'

Ideally, H2 should automatically do that for you. But well, right now, it
doesn't

Regards,
Thomas

On Thursday, July 30, 2015, Steve McLeod > wrote:

> Indexing for an OR clause that references two separate columns is a
> difficult task.
>
> Does your use case tolerate an index on columns b and c only?
>
> When I add this index to your example code:
> create index IDX_TEST_4 on TEST(b,c);
>
> I get this query plan:
> SELECT
> TEST.A,
> TEST.A2,
> TEST.B,
> TEST.C
> FROM PUBLIC.TEST
> /* PUBLIC.IDX_TEST_4: C = 'PP'
> AND B = '456'
>  */
> WHERE (C = 'PP')
> AND ((B = '456')
> AND ((A = '123')
> OR (A2 = '1234')));
>
> On Tuesday, 28 July 2015 23:23:38 UTC+2, epsilon68 wrote:
>>
>>
>> On Tue, Jul 28, 2015 at 8:53 AM, Noel Grandin  wrote:
>>
>>>
>>> Try doing this:
>>>
>>> select * from TEST where (a = '123' and b = '456' and c='PP') or
>>> (a2='1234' and b = '456' and c='PP');
>>
>>
>> same result
>>
>> explain select * from TEST where (a = '123' and b = '456' and c='PP') or
>> (a2='1234' and b = '456' and c='PP');
>> PLAN
>> 
>> SELECT
>> TEST.A,
>> TEST.A2,
>> TEST.B,
>> TEST.C
>> FROM PUBLIC.TEST
>> /* PUBLIC.TEST.tableScan */
>> WHERE ((C = 'PP')
>> AND ((A = '123')
>> AND (B = '456')))
>> OR ((C = 'PP')
>> AND ((A2 = '1234')
>> AND (B = '456')))
>>
>> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com.
> To post to this group, send email to h2-database@googlegroups.com.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] MVStore with MVCC performance degradation

2015-07-30 Thread Noel Grandin




On 2015-07-28 06:10 PM, Wes Clark wrote:

Link to trace file:  
https://drive.google.com/open?id=0B7fRq__lUQbCRXJIM2NCVVdpZGM


Can't access this.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] index for OR clause

2015-07-30 Thread Steve McLeod

Indexing for an OR clause that references two separate columns is a 
difficult task.

Does your use case tolerate an index on columns b and c only?

When I add this index to your example code:
create index IDX_TEST_4 on TEST(b,c);

I get this query plan:
SELECT
TEST.A,
TEST.A2,
TEST.B,
TEST.C
FROM PUBLIC.TEST
/* PUBLIC.IDX_TEST_4: C = 'PP'
AND B = '456'
 */
WHERE (C = 'PP')
AND ((B = '456')
AND ((A = '123')
OR (A2 = '1234')));

On Tuesday, 28 July 2015 23:23:38 UTC+2, epsilon68 wrote:
>
>
> On Tue, Jul 28, 2015 at 8:53 AM, Noel Grandin  > wrote:
>
>>
>> Try doing this:
>>
>> select * from TEST where (a = '123' and b = '456' and c='PP') or 
>> (a2='1234' and b = '456' and c='PP');
>
>
> same result
>
> explain select * from TEST where (a = '123' and b = '456' and c='PP') or 
> (a2='1234' and b = '456' and c='PP');
> PLAN   
> 
> SELECT
> TEST.A,
> TEST.A2,
> TEST.B,
> TEST.C
> FROM PUBLIC.TEST
> /* PUBLIC.TEST.tableScan */
> WHERE ((C = 'PP')
> AND ((A = '123')
> AND (B = '456')))
> OR ((C = 'PP')
> AND ((A2 = '1234')
> AND (B = '456')))
>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Connection Properties for Linked Tables

Re: [h2] index for OR clause

Re: [h2] Mixed mode, auto_server and remote access question

Re: [h2] Spring boot + flyway +Caused by: org.h2.jdbc.JdbcSQLException: Table "xxxxx" not found; SQL statement:

[h2] index for OR clause

Re: [h2] MVStore with MVCC performance degradation

Re: [h2] index for OR clause

7 matches

Site Navigation

Mail list logo

Footer information