Sybase Configuration

[shabh...@gmail.com]

Hi

I'm new to Fluent Hibernate & have noticed that Sybase ASE
configuration has not been catered for.

I tried to create my own configuration by making using of the sysbase
client driver.

protected SybaseClientConfiguration()
{
Driver<SybaseClientDriver>();
}

public static SybaseClientConfiguration SybaseAse
{
get { return new SybaseClientConfiguration
().Dialect<SybaseDialect>(); }
}

my factory is as follows

_factory = Fluently.Configure()
.Database
(SybaseClientConfiguration.SybaseAse.ConnectionString(c =>
c.FromConnectionStringWithKey("KplusConString")))
.Mappings(m =>
m.FluentMappings.AddFromAssemblyOf<Cpty>())
.BuildSessionFactory();


My look up is simple but it keeps failing with the following error
(i'm making use of the latest fluent hibernate dll)
could not execute query
[ SELECT this_.ID as ID7_0_, this_.Cpty_ShortName as Cpty2_7_0_,
this_.Cpty_Name as Cpty3_7_0_ FROM "Cpty" this_ ORDER BY
this_.Cpty_ShortName asc ]
[SQL: SELECT this_.ID as ID7_0_, this_.Cpty_ShortName as Cpty2_7_0_,
this_.Cpty_Name as Cpty3_7_0_ FROM "Cpty" this_ ORDER BY
this_.Cpty_ShortName asc]

Please assist ASAP.

regards,
shabhana

[James Gregory]

Can you provide the full exception?

[KevinT]

I written an implementation of SybaseConfiguration
SybaseConnectionStringBuilder modelled on the MySql implementation (in
FluentNHibernate.Cfg.Db) which you can have if you send me an email
(kevint at drivensoftware dot net) - very simple to do, and works fine
for us. Been meaning to push it to the fluent-nh guys, but haven't
done so yet.

On May 4, 2:05 pm, James Gregory <jagregory....@gmail.com> wrote:
> Can you provide the full exception?
>

> On Mon, May 4, 2009 at 1:00 PM, shabhana...@gmail.com <shabhana...@gmail.com

[shabh...@gmail.com]

Nhibernate.adoexception
{"could not execute query\r\n[ SELECT this_.ID as ID7_0_,

this_.Cpty_ShortName as Cpty2_7_0_, this_.Cpty_Name as Cpty3_7_0_ FROM

\"Cpty\" this_ ORDER BY this_.Cpty_ShortName asc ]\r\n[SQL: SELECT

this_.ID as ID7_0_, this_.Cpty_ShortName as Cpty2_7_0_,
this_.Cpty_Name as Cpty3_7_0_ FROM \"Cpty\" this_ ORDER BY
this_.Cpty_ShortName asc]"}

innerexception
{"Incorrect syntax near 'this_'.\n"}


stacktrace
" at Sybase.Data.AseClient.AseCommand.ᜁ(Int32 A_0)\r\n at
Sybase.Data.AseClient.AseCommand.ᜄ()\r\n at
Sybase.Data.AseClient.AseCommand.ᜀ(CommandBehavior A_0)\r\n at
Sybase.Data.AseClient.AseCommand.System.Data.IDbCommand.ExecuteReader()
\r\n at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand
cmd)\r\n at NHibernate.Loader.Loader.GetResultSet(IDbCommand st,
Boolean autoDiscoverTypes, Boolean callable, RowSelection selection,
ISessionImplementor session)\r\n at NHibernate.Loader.Loader.DoQuery
(ISessionImplementor session, QueryParameters queryParameters, Boolean
returnProxies)\r\n at
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections
(ISessionImplementor session, QueryParameters queryParameters, Boolean
returnProxies)\r\n at NHibernate.Loader.Loader.DoList
(ISessionImplementor session, QueryParameters queryParameters)"

On May 4, 2:05 pm, James Gregory <jagregory....@gmail.com> wrote:

> Can you provide the full exception?
>

> On Mon, May 4, 2009 at 1:00 PM, shabhana...@gmail.com <shabhana...@gmail.com

> > shabhana- Hide quoted text -
>
> - Show quoted text -

[shabh...@gmail.com]

Hi kevin

i popped you an email on the address you specified.

[James Gregory]

Sounds like a problem with the dialect, not Fluent NHibernate.

[KevinT]

Sorry - forgot i am at a client that blocks my mail access!

Here is the text of the files:

======================================================
SybaseConnectionStringBuilder.cs
------------------------------------------------------------------------------------------------------------
using System.Text;
using FluentNHibernate.Cfg.Db;

namespace KeyBlade.Data.Source.Tools
{
public class SybaseConnectionStringBuilder :
ConnectionStringBuilder
{
private string _host;
private int _port;
private string _database;
private string _username;
private string _password;

public SybaseConnectionStringBuilder Username(string username)
{
_username = username;
IsDirty = true;
return this;
}
public SybaseConnectionStringBuilder Password(string password)
{
_password = password;
IsDirty = true;
return this;
}
public SybaseConnectionStringBuilder Database(string database)
{
_database = database;
IsDirty = true;
return this;
}
public SybaseConnectionStringBuilder Host(string host)
{
_host = host;
IsDirty = true;
return this;
}
public SybaseConnectionStringBuilder Port(int port)
{
_port = port;
IsDirty = true;
return this;
}
protected override string Create()
{
var connectionString = base.Create();

if (!string.IsNullOrEmpty(connectionString))
return connectionString;

var sb = new StringBuilder();

sb.AppendFormat(@"Uid={0}; Pwd={1}; DB={2}; NA={3},{4}",
_username, _password, _database, _host,
_port);

return sb.ToString();
}
}
}
======================================================

======================================================
SybaseConfiguration.cs
------------------------------------------------------------------------------------------------------------
using FluentNHibernate.Cfg.Db;
using NHibernate.Dialect;
using NHibernate.Driver;
using Sybase11Dialect=NHibernate.Dialect.Sybase11Dialect;
using SybaseDialect=NHibernate.Dialect.SybaseDialect;

namespace KeyBlade.Data.Source.Tools
{
public class SybaseConfiguration :
PersistenceConfiguration<SybaseConfiguration,
SybaseConnectionStringBuilder>
{
protected SybaseConfiguration()
{
Driver<SybaseClientDriver>();
}

public static SybaseConfiguration SybaseDialect
{
get { return new SybaseConfiguration
().Dialect<SybaseDialect>(); }
}
public static SybaseConfiguration SybaseAnywhereDialect
{
get { return new SybaseConfiguration
().Dialect<SybaseAnywhereDialect>(); }
}
public static SybaseConfiguration Sybase11Dialect
{
get { return new SybaseConfiguration
().Dialect<Sybase11Dialect>(); }
}
}
}
======================================================

On May 4, 2:50 pm, James Gregory <jagregory....@gmail.com> wrote:
> Sounds like a problem with the dialect, not Fluent NHibernate.
>

> On Mon, May 4, 2009 at 1:47 PM, shabhana...@gmail.com <shabhana...@gmail.com

[shabh...@gmail.com]

thanks kevin

let me try this & see if it resolves my issue....

On May 4, 2:59 pm, KevinT <kev...@drivensoftware.net> wrote:
> Sorry - forgot i am at a client that blocks my mail access!
>
> Here is the text of the files:
>
> ======================================================
> SybaseConnectionStringBuilder.cs

> ---------------------------------------------------------------------------­---------------------------------

> ---------------------------------------------------------------------------­---------------------------------

> > > > - Show quoted text -- Hide quoted text -

[shabh...@gmail.com]

I get the same error.

I tried all dialects.

On May 4, 2:59 pm, KevinT <kev...@drivensoftware.net> wrote:

> Sorry - forgot i am at a client that blocks my mail access!
>
> Here is the text of the files:
>
> ======================================================
> SybaseConnectionStringBuilder.cs

> ---------------------------------------------------------------------------­---------------------------------

> ---------------------------------------------------------------------------­---------------------------------

> > > > - Show quoted text -- Hide quoted text -

[James Gregory]

Have you been able to execute this query before using Fluent? It sounds like a bug in the dialect/driver to me.

[shabh...@gmail.com]

how can that be resolved?

On May 4, 2:50 pm, James Gregory <jagregory....@gmail.com> wrote:
> Sounds like a problem with the dialect, not Fluent NHibernate.
>

> On Mon, May 4, 2009 at 1:47 PM, shabhana...@gmail.com <shabhana...@gmail.com

> > > - Show quoted text -- Hide quoted text -

[shabh...@gmail.com]

never...

I think ur right it's probably a problem with the sybase dlls.

I had to install the sybase client to get the dlls (but i installed it
over an older version) - i'll try a clean install to see if it
resolves the problem & get back to you guys.

thanks much!

On May 4, 3:12 pm, James Gregory <jagregory....@gmail.com> wrote:
> Have you been able to execute this query before using Fluent? It sounds like
> a bug in the dialect/driver to me.
>

> On Mon, May 4, 2009 at 2:10 PM, shabhana...@gmail.com <shabhana...@gmail.com

[James Gregory]

If that is the case, you should ask on the main NHibernate mailing list: http://groups.google.com/group/nhusers/ 

Hopefully somebody there will already know about the issue, but failing that you'll need to raise an issue on the NHibernate JIRA.

[shabh...@gmail.com]

Hi Kevin

what version of Sybase.Data.AseClient.dll are you using?

i'm using 15.0.2

On May 4, 2:59 pm, KevinT <kev...@drivensoftware.net> wrote:

> Sorry - forgot i am at a client that blocks my mail access!
>
> Here is the text of the files:
>
> ======================================================
> SybaseConnectionStringBuilder.cs

> ---------------------------------------------------------------------------­---------------------------------

> ---------------------------------------------------------------------------­---------------------------------

> > > > - Show quoted text -- Hide quoted text -

[KevinT]

File version is 1.1.621.0



On May 4, 4:08 pm, "shabhana...@gmail.com" <shabhana...@gmail.com>
wrote:

[shabh...@gmail.com]

Hi

It's not a driver/dialect problem.
i've reinstalled the latest sybase driver - failed with the same error
i installed an older version of the sybase driver - failed with the
same error

This is the actual problem:

SELECT
this_.ID as ID7_0_,
this_.Cpty_ShortName as Cpty2_7_0_,
this_.Cpty_Name as Cpty3_7_0_
FROM "Cpty" this_
ORDER BY
this_.Cpty_ShortName asc

It rejects the above sql in sybase because "set quoted_identifer" is
off
"Cpty" in quotes is not recognised.
The sql works directly in sybase if the quotes are removed or "set
quoted_identifer" is on (i.e. prepend the sql with "set
quoted_identifier on"

The problem still isn't resolved though.
How can I accomplish this? i.e execute "set quoted_identifier on" when
executing the query? or how can i remove the "" outside Cpty?

Both of these prepend & append Cpty with quotes....
List<Cpty> list;
var factory = Ninject_IOC.Container.Get<KplusSession>();
using (var session = factory.CreateSession())
{
list = factory.LinqQueryable<Cpty>(session).OrderBy(p
=> p.Cpty_ShortName).ToList();
}
return list;
----
List<Cpty> list;
var factory = Ninject_IOC.Container.Get<KplusSession>();
using (var session = factory.CreateSession())
{
list = (IQuery)session.CreateQuery("from
Cpty").List<Cpty>();
}
return list;

On May 4, 3:17 pm, James Gregory <jagregory....@gmail.com> wrote:
> If that is the case, you should ask on the main NHibernate mailing list:http://groups.google.com/group/nhusers/
> Hopefully somebody there will already know about the issue, but failing that
> you'll need to raise an issue on the NHibernate JIRA.
>

> On Mon, May 4, 2009 at 2:13 PM, shabhana...@gmail.com <shabhana...@gmail.com

[James Gregory]

Again, this is not a Fluent NHibernate issue, you need to ask this on the main NHibernate mailing list.

When I say this is a driver/dialect issue I am referring to the dialect and driver classes from NHibernate, these are what instruct NHibernate of the various databases capabilities. If NHibernate is defaulting to quoting identifiers when sybase doesn't support it, then that's something that needs to be corrected in NHibernate.

[shabh...@gmail.com]

thank you.

On May 4, 6:20 pm, James Gregory <jagregory....@gmail.com> wrote:
> Again, this is not a Fluent NHibernate issue, you need to ask this on the

> main NHibernate mailing list <http://groups.google.com/group/nhusers/>.

>
> When I say this is a driver/dialect issue I am referring to the dialect and
> driver classes from NHibernate, these are what instruct NHibernate of the
> various databases capabilities. If NHibernate is defaulting to quoting
> identifiers when sybase doesn't support it, then that's something that needs
> to be corrected in NHibernate.
>

> On Mon, May 4, 2009 at 5:09 PM, shabhana...@gmail.com <shabhana...@gmail.com