Firebird error

azerty · **Posted:** Wed Oct 12, 2005 7:35 am

I tried to run my application with a Firebird database but get the following exception :

Code:

Dynamic SQL error
error code = -104
Token unknown - line 1, char 541
.

What's the matter ?

sergey · **Posted:** Wed Oct 12, 2005 8:32 am

You have an error on line 35 of your application, the line should be commented out :-)

Just kidding, but we really need more information before we can answer this. This means the classes, the mappings and the code causing the problem.

azerty · **Posted:** Wed Oct 12, 2005 12:59 pm

Sorry sergey. I will give all I can to explain the situation.

1) The application runs correctly when a SQL Server database is used.
2) I decided to convert database to Firebird to use the embedded server functionnality.

The exception is thrown when I execute the following code :

Code:

public IList getList(string hqlQueryString)
{
    try
    {
        session = factory.OpenSession();
        return session.CreateQuery(hqlQueryString).List();
    }
    catch
    {
        throw;
    }
}
where hqlQueryString is "SELECT societe FROM Societe AS societe ORDER BY societe.Nom"

The exception text is :
[code]
NHibernate :select societe0_.IdSociete as IdSociete, societe0_.SiteWeb as SiteWeb, societe0_.Ville as Ville, societe0_.CedexBureau as CedexBur8_, societe0_.Cedex as Cedex, societe0_.CodePostal as CodePostal, societe0_.BP as BP, societe0_.IdCategorie as IdCateg14_, societe0_.Notes as Notes, societe0_.Nom as Nom, societe0_.Telephone as Telephone, societe0_.Mail as Mail, societe0_.Adresse as Adresse, societe0_.Pays as Pays, societe0_.Fax as Fax from dbo.Societe societe0_ order by societe0_.Nom
Une exception non g

KPixel · **Posted:** Wed Oct 12, 2005 2:12 pm

The 443 char of your SQL query is the dot in "[...] from dbo.Societe societe0_ [...]"; you probably need to remove dbo...
(in <hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" schema="dbo">)

Guest · **Posted:** Thu Oct 13, 2005 2:59 am

Thanks KPIxel, it was that but now I have another exception which is thrown because all my tables and table fields are case sensitive
* Societe and not SOCIETE
* IdSociete and not IDSOCIETE

Is there a way to force NHibernate to create case sensitive query or must I rename all my tables' name and tables' fields ?

sergey · **Posted:** Thu Oct 13, 2005 4:31 pm

If your table names are case-sensitive, you need to quote them in NHibernate using backticks (table="`MyTableName`"). NHibernate will then convert the backticks to appropriate quotes for each dialect, so this method of quoting will work with both MSSQL and Firebird.

k-dub · **Joined:** Thu May 12, 2005 8:45 am **Posts:** 226

azerty wrote:

Code:

where hqlQueryString is "SELECT societe FROM Societe AS societe ORDER BY societe.Nom"

I believe that hql should be

Code:

from Societe as societe order by societe.Nom

If you're just selecting all the columns in the table, you don't need the SELECT. This got me in trouble in the past. Also, hql should be lowercase.

Just a heads-up to keep you out of trouble.

azerty · **Posted:** Sat Oct 15, 2005 3:34 am

1) sergey, I change the HQL query string like this.

Code:

"SELECT societe FROM 'Societe' AS societe ORDER BY societe.Nom"

but an exception is always thrown

Code:

unexcepted token as : [SELECT societe FROM 'Societe' AS societe ORDER BY societe.Nom]

ps : The same thing arrived if I rename the 'AS' in 'as' so this is not case sensitive.

2) If I drop the 'AS societe ORDER BY societe.Nom' expression, the following exception is thrown :

Code:

in excepted: <end-of-text> (possibly an invalid or unmapped class name was used in the query)[SELECT societe FROM 'Societe']

ps : The same thing arrived if I drop the 'SELECT societe' expression.

KPixel · **Posted:** Sun Oct 16, 2005 8:08 am

HQL refers to entities, not tables (even if it seems possible to use tables names).
IIRC, AS is no longer supported; so you may try:

Code:

from Societe s order by s.Nom

(it is more error-prone to use "societe")

And as Sergey said: If your table names are case-sensitive, you need to quote them in NHibernate using backticks (table="`MyTableName`").
(in <class name="..." table="Societe">)

sergey · **Posted:** Sun Oct 16, 2005 4:47 pm

KPixel, your reply is almost completely incorrect - AS is supported, and using just "s" instead of "societe" is in no way more error-prone, it's just less typing. The real problem is that you need to quote the table name in the mapping files.

azerty · **Posted:** Mon Oct 17, 2005 11:08 am

Thanks for your replies, it's now OK for me.

So, for those who will have the same matter as me, I summarize the solution :

If you use Firebird database and that your table names (or table fields names) are case sensitive,
use backticks (`...`) in your hbm files as the following example.

Code:

<class name="GestionnaireDeCandidatures.Info.Societe, GestionnaireDeCandidatures.BLL" table="`Societe`">
  <id name="IdSociete" type="Int32" column="`IdSociete`" unsaved-value="0" access="nosetter.pascalcase-m-underscore">
    <generator class="sequence">
      <param name="sequence">"SocieteGenerator"</param>
    </generator>      
  </id>
  ...

ps : do not add backticks in your HQL query string. The HQL query string use class name and not table name.