Facing an issue for querying error as "HSQL Database : Unexpected token: END in statement"

Question

I have created an activeobject query as below:

ao.find(IP.class, Query.select().where("user=? AND start>=? AND end<=?",u,datefieldl,datefieldl))

but it gives me below error:

There was a SQL exception thrown by the Active Objects library: Database: - name:HSQL Database Engine - version:1.8.0 - minor version:8 - major version:1 Driver: - name:HSQL Database Engine Driver - version:1.8.0 java.sql.SQLException: Unexpected token: END in statement [SELECT * FROM PUBLIC.AO_0371A8_IP WHERE user=? AND start>=? AND end<=?]

create entity stuff is as below:

 IP pi = ao.executeInTransaction(new TransactionCallback() // (1)            
    {   
         @Override
         public IP doInTransaction()
         {
            logger.info("before ao.create");
            IP pi = ao.create(enclass);
                                ....

            pi.save();                              
            return pi;          
         }
    });

My entity is looks as below:

@Table("IP")
@Preload
    public interface IP extends Entity {

    @Accessor("issues")
    String getIssues();

@Mutator("issues")
void setIssues(String issues);

@Accessor("planStartTime")
Date getPlanStartTime();

@Mutator("planStartTime")
void setPlanStartTime(Date planStartTime);

@Accessor("planEndTime")
Date getPlanEndTime();

@Mutator("planEndTime")
void setPlanEndTime(Date planEndTime);

@Accessor("user")
String getUser();

@Mutator("user")
void setUser(String user);
}

any idea?

Answer 1

Unexpected token: END in statement

Take a look at your conditions:

"user=? AND start>=? AND end<=?"

END is actually an SQL keyword (following the line of IF and BEGIN). Change that to, for example:

"user=? AND sessionStartTime>=? AND sessionFinishTime<=?"

The problem here is that when the final SQL query is generated, it has an invalid syntax. That's why you're getting the exception.