Re: h2 with Eclipselink and sequences

2013-02-26 Thread Dries 
Because, once I enable my sessionCustomizer class, eclipselink no longer 
uses the correct statement (CALL NEXT VALUE FOR), but instead uses : 

[EL Fine]: 2013-02-22 
14:50:01.291--ClientSession(2052114141)--Connection(1340006540)--Thread(Thread[main,5,main])--UPDATE
 
SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
bind => [50, SYSTEM_SEQUENCE_1E71BBBC_0444_4E1D_A3A5_DD4483BD30EE]


All I changed in the session Customizer class was setSequenceNumberName.

I came up with a workaround where I now, instead of using auto increment 
when creating the tables, I createmy own sequences and set the default 
value of the column to :
"DEFAULT (NEXT VALUE FOR PUBLIC.MY_SEQUENCE_TABNAME ) NOT NULL 
NULL_TO_DEFAULT SEQUENCE PUBLIC.MY_SEQUENCE_TABNAME";

Then in my persistence entity classes, I set the sequence name.
@GeneratedValue(strategy = GenerationType.AUTO, generator="GEN_TABNAME")
@SequenceGenerator(name = "GEN_TABNAME", sequenceName = 
"MY_SEQUENCE_TABNAME", allocationSize = 1)


It's not ideal, I would still prefer a way where eclipselink figures out 
the sequence name of an auto_increment field and uses that correctly, but 
it works.

Thanks,

Droes

On Tuesday, February 26, 2013 10:25:01 AM UTC+1, Noel Grandin wrote:
>
>  
> On 2013-02-22 15:55, Dries wrote:
>  
> I'm able to make that statement work on the correct tables and fields in 
> h2, but h2 doesn't allow changes to meta tables, so that doesn't solve the 
> problem either. 
> login.getPlatform().setSequenceCounterFieldName("CURRENT_VALUE");
> login.getPlatform().setSequenceNameFieldName("SEQUENCE_NAME");
> login.getPlatform().setSequenceTableName("INFORMATION_SCHEMA.SEQUENCES");
> login.getPlatform().setSequencePreallocationSize(1);
>  
>   [EL Fine]: 2013-02-22 
> 14:52:15.602--ClientSession(1205789719)--Connection(1834774659)--Thread(Thread[main,5,main])--UPDATE
>  
> INFORMATION_SCHEMA.SEQUENCES SET CURRENT_VALUE = CURRENT_VALUE + ? WHERE 
> SEQUENCE_NAME = ?
>   bind => [1, SYSTEM_SEQUENCE_1E71BBBC_0444_4E1D_A3A5_DD4483BD30EE]
>  
>
>  
>  That statement is not allowed because it updates meta tables.
>
>  
>  
> Why are you trying to update meta tables instead of using standard SQL to 
> modify the sequence?
>
> http://h2database.com/html/grammar.html#alter_sequence
>  

-- 
*
*
*

Confidentiality Notice:  *
The information in this email is confidential and privileged.  If you are 
the intended recipient and you have a current Non-Disclosure Agreement in 
place between your entity and Security Weaver any content contained or 
implied by this email should be considered within the scope of that 
agreement.  If the recipient of this message is not the intended recipient, 
any disclosure, copying, distribution, retention or action taken or omitted 
to be taken in reliance on it is prohibited.  Please notify us immediately 
by replying to the message or by telephoning +1- 800-620-4210 and deleting 
it and any attachments from your computer system.

-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: h2 with Eclipselink and sequences

2013-02-26 Thread Dries 
I don't use Schema Generation by eclipselink, the tables are created in h2 
directly.  That I think is the reason h2 isn't able to link the sequence 
name to the auto_increment field.

On Tuesday, February 26, 2013 7:31:19 AM UTC+1, Christoph Läubrich wrote:
>
> Do you use annotations? Schema generation? 
>
> I use H2 with eclipse link and the following annotation for id: 
>
> @Id 
> @GeneratedValue(strategy = GenerationType.IDENTITY) 
>
> without any problem (schema is generated by eclipse link) 
>

-- 
*
*
*

Confidentiality Notice:  *
The information in this email is confidential and privileged.  If you are 
the intended recipient and you have a current Non-Disclosure Agreement in 
place between your entity and Security Weaver any content contained or 
implied by this email should be considered within the scope of that 
agreement.  If the recipient of this message is not the intended recipient, 
any disclosure, copying, distribution, retention or action taken or omitted 
to be taken in reliance on it is prohibited.  Please notify us immediately 
by replying to the message or by telephoning +1- 800-620-4210 and deleting 
it and any attachments from your computer system.

-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

h2 with Eclipselink and sequences

2013-02-25 Thread Dries 
Hello,

when trying to use h2 with eclipselink, I ran into some problems.  

I want to use h2 with eclipselink and have it use the correct sequences.
Did anyone have any success doing this?

Here's what I tried so far : 
Eclipselink doesn't seem to know the name of the sequence that is assigned 
to an auto_increment field.

Exception [EclipseLink-4002] (Eclipse Persistence Services - 
2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: Sequence 
"SEQ_GEN_SEQUENCE" not found; SQL statement:
CALL NEXT VALUE FOR SEQ_GEN_SEQUENCE [90036-162]

 Eclipselink will always use SEQ_GEN_SEQUENCE,for all tables.
My eclipselink properties are : 






I thought I could solve this by using a sessionCustomizer, and almost 
succeeded, but then eclipselink seems to forget the statement that it is 
supposed to use for sequences.

My Customizer class : 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.log4j.Logger;
import org.eclipse.persistence.config.SessionCustomizer;
import org.eclipse.persistence.descriptors.ClassDescriptor;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.eclipse.persistence.sessions.Session;
public class JPACustomizer implements SessionCustomizer
{
private static final Logger log = Logger.getLogger(JPACustomizer.class);
@Override
public void customize(Session sn) throws Exception
{
DatabaseLogin login = (DatabaseLogin)sn.getDatasourceLogin();
/*
//This would work if h2 allowed changes to meta tables
login.getPlatform().setSequenceCounterFieldName("CURRENT_VALUE");
login.getPlatform().setSequenceNameFieldName("SEQUENCE_NAME");

login.getPlatform().setSequenceTableName("INFORMATION_SCHEMA.SEQUENCES");
login.getPlatform().setSequencePreallocationSize(1);
*/
ClassDescriptor table = 
sn.getDescriptor(Landscapesodanalysis.class);
Class.forName("org.h2.Driver").newInstance();
Connection conn = 
DriverManager.getConnection(login.getConnectionString(),"sa","sa");
PreparedStatement getSeqName = conn.prepareStatement("select 
sequence_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME =? and 
column_name = ?");
String tableName = table.getTableName();
String[] seqColName = 
table.getSequenceNumberFieldName().split("\\.");
getSeqName.setString(1,tableName.toUpperCase());
getSeqName.setString(2,seqColName[1].toUpperCase());
ResultSet seqName = getSeqName.executeQuery();
if(seqName.next())
{
table.setSequenceNumberName(seqName.getString(1)); 
}
conn.close();
log.info(login.getDatasourcePlatform());
}

}

Now eclipse knows the correct sequence to use, but somehow forgot the 
statement ("CALL NEXT VALUE...") that it should use for the platform h2.

[EL Fine]: 2013-02-22 
14:50:01.291--ClientSession(2052114141)--Connection(1340006540)--Thread(Thread[main,5,main])--UPDATE
 
SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
bind => [50, SYSTEM_SEQUENCE_1E71BBBC_0444_4E1D_A3A5_DD4483BD30EE]


I'm able to make that statement work on the correct tables and fields in 
h2, but h2 doesn't allow changes to meta tables, so that doesn't solve the 
problem either.
login.getPlatform().setSequenceCounterFieldName("CURRENT_VALUE");
login.getPlatform().setSequenceNameFieldName("SEQUENCE_NAME");
login.getPlatform().setSequenceTableName("INFORMATION_SCHEMA.SEQUENCES");
login.getPlatform().setSequencePreallocationSize(1);

[EL Fine]: 2013-02-22 
14:52:15.602--ClientSession(1205789719)--Connection(1834774659)--Thread(Thread[main,5,main])--UPDATE
 
INFORMATION_SCHEMA.SEQUENCES SET CURRENT_VALUE = CURRENT_VALUE + ? WHERE 
SEQUENCE_NAME = ?
bind => [1, SYSTEM_SEQUENCE_1E71BBBC_0444_4E1D_A3A5_DD4483BD30EE]



That statement is not allowed because it updates meta tables.


Thanks in advance,
Dries



-- 
*
*
*

Confidentiality Notice:  *
The information in this email is confidential and privileged.  If you are 
the intended recipient and you have a current Non-Disclosure Agreement in 
place between your entity and Security Weaver any content contained or 
implied by this email should be considered within the scope of that 
agreement.  If the recipient of this message is not the intended recipient, 
any disclosure, copying, distribution, retention or action taken or omitted 
to be taken in reliance on it is prohibited.  Please notify us immediately 
by replying to the message or by telephoning +1- 800-620-4210 and deleting 
it and any attachments from your computer system.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To