Any way to remove quote marks around generated sql for sybase schema?

[Timothy Spring <ts...@mhg.co.za>]

Hi there,

I have a datasource associated with a schema, and wish to select from a
table on a different schema (to which I have access).

Something like:

SELECT name FROM SHARE.dbo.PROVINCE;

(The "dbo" portion is a Sybase requirement, I've been told by the DBA?
Whatever the case, the above works fine when run as is against sybase)

Anyway, using annotations on my entity class as follows:

@Entity
@Table(name="PROVINCE", schema="SHARE.dbo")

it generates sql with quote marks, eg:

SELECT name FROM "SHARE.dbo".PROVINCE;

Sybase hates this - is there any way I can configure openjpa to not
quote the schema part?

I had a look at the DBDictionary (and SybaseDictionary) docs, but
couldn't see anything that seemed appropriate?

Any help appreciated, please excuse any ignorance.

I'm using openjpa-2.2.0

Regards,
Timothy Spring



  
    
      
    
  

Re: Any way to remove quote marks around generated sql for sybase schema?

[Michael Dick <mi...@gmail.com>]

Hi Timothy,

This may seem like I'm nit-picking but bear with me. I think in Sybase the
naming convention is {database name}.{schema}.{object}, so in your example,
SHARE is the database name, and dbo is the schema. So you could set the
database name on you connection properties or datasource and just use this
in your entity:
@Table(name="PROVINCE", schema="dbo")

This is all fine and dandy so long as all your entities are in the same
Sybase database. If not, the most straight forward approach is to use a
separate EntityManagerFactory for each database.

That said, I think OpenJPA has detected a reserved character in your schema
name and is automatically delimiting it. There should be a way to escape
the . in your identifier (I'd try backslash but I'm not set up to test it
right now). Failing that, you could try disabling delimited identifiers on
the DBDictionary, and see if that helps.

Sorry I can't be of more help, I have limited Internet access at the
moment, I'll respond again when I get a chance to reproduce if you haven't
figured it out first.

-mike

On Mon, Sep 3, 2012 at 10:33 AM, Timothy Spring <ts...@mhg.co.za> wrote:

> Hi there,
>
>
>
> I have a datasource associated with a schema, and wish to select from a
>
> table on a different schema (to which I have access).
>
>
>
> Something like:
>
>
>
> SELECT name FROM SHARE.dbo.PROVINCE;
>
>
>
> (The "dbo" portion is a Sybase requirement, I've been told by the DBA?
>
> Whatever the case, the above works fine when run as is against sybase)
>
>
>
> Anyway, using annotations on my entity class as follows:
>
>
>
> @Entity
>
> @Table(name="PROVINCE", schema="SHARE.dbo")
>
>
>
> it generates sql with quote marks, eg:
>
>
>
> SELECT name FROM "SHARE.dbo".PROVINCE;
>
>
>
> Sybase hates this - is there any way I can configure openjpa to not
>
> quote the schema part?
>
>
>
> I had a look at the DBDictionary (and SybaseDictionary) docs, but
>
> couldn't see anything that seemed appropriate?
>
>
>
> Any help appreciated, please excuse any ignorance.
>
>
>
> I'm using openjpa-2.2.0
>
>
>
> Regards,
>
> Timothy Spring
>
>
>
>
>
>
>
>
>
>
>