SQL error code = -104 Token unknown.

[Gert Smith]

I am trying to implement FirebirdSql.EntityFrameworkCore.Firebird 7.5.0 in a project but am having some challenges purely because of my own lack of knowledge.

In particular I am stuck with: SQL error code = -104 Token unknown.

I am using context as follows:

 public class TraderDbContext : DbContext
 {
   static readonly ILoggerFactory _loggerFactory = LoggerFactory.Create(builder => { builder.AddConsole(); });
 
  private string _provider = string.Empty;
   private string _connectionString = string.Empty;
 
   public DbSet<region> Regions { get; set; }
 
   public TraderDbContext(string provider, string connectionString)
   {
     _provider = provider;
     _connectionString = connectionString;
   }
 
   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   {
     switch (_provider)
     {
       case "IntegrityRetail":
         optionsBuilder.UseLoggerFactory(_loggerFactory).UseFirebird(_connectionString);
       break;
     }
 
     base.OnConfiguring(optionsBuilder);
   }
 
   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
     base.OnModelCreating(modelBuilder);
 
     // Map table names
     var typeBuilder = modelBuilder.Entity<region>();
     typeBuilder.Property(p => p.Id).HasColumnName("RG_ID");
     typeBuilder.Property(p => p.Name).HasColumnName("RG_NAME");
     typeBuilder.ToTable("REGIONS");
   }
 }

And querying as follows:

var query = from r in dbContext.Regions
             select r;
 return await query.ToListAsync();




info: Microsoft.EntityFrameworkCore.Infrastructure[10403]

      Entity Framework Core 3.1.3 initialized 'TraderDbContext' using provider 'FirebirdSql.EntityFrameworkCore.Firebird' with options: None

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]

      Failed executing DbCommand (35ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      SELECT "r"."RG_ID", "r"."RG_NAME"

      FROM "REGIONS" AS "r"

fail: Microsoft.EntityFrameworkCore.Query[10100]

      An exception occurred while iterating over the results of a query for context type 'RFIDModule.Data.TraderDbContext'.

      FirebirdSql.Data.FirebirdClient.FbException (0x80004005): Dynamic SQL Error

      SQL error code = -104

      Token unknown - line 2, column 16




[Jiří Činčura]

I'm unable to reproduce it. Please provide full code.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

[Gert Smith]

So this query works:

 return await dbContext.Regions.FromSqlRaw("SELECT RG_ID, RG_NAME FROM REGIONS").ToListAsync();

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]

      Entity Framework Core 3.1.3 initialized 'TraderDbContext' using provider 'FirebirdSql.EntityFrameworkCore.Firebird' with options: None

info: Microsoft.EntityFrameworkCore.Database.Command[20101]

      Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      SELECT RG_ID, RG_NAME FROM REGIONS

This query does not:

 return await dbContext.Regions.ToListAsync();

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]

      Entity Framework Core 3.1.3 initialized 'TraderDbContext' using provider 'FirebirdSql.EntityFrameworkCore.Firebird' with options: None

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]

      Failed executing DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      SELECT "r"."RG_ID", "r"."RG_NAME"

      FROM "REGIONS" AS "r"

fail: Microsoft.EntityFrameworkCore.Query[10100]

      An exception occurred while iterating over the results of a query for context type 'RFIDModule.Data.TraderDbContext'.

      FirebirdSql.Data.FirebirdClient.FbException (0x80004005): Dynamic SQL Error
      SQL error code = -104

      Token unknown - line 2, column 16

      AS

       ---> Dynamic SQL Error

      SQL error code = -104

      Token unknown - line 2, column 16

------------------------------------------------

It appears that Firebird does not like the generated query. It gets really upset about the AS statement.

However when I remove AS statement it is happy. So do not know how to adjust the generated code in the entity framework to work correctly.

[Jiří Činčura]

Running FB 3.0 and the AS works as it should. What version are you running?

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

On Tue, Apr 14, 2020, at 14:38, Gert Smith wrote:
> > **So this query works:**
> >
> > `

> >
> > return await dbContext.Regions.FromSqlRaw("SELECT RG_ID, RG_NAME FROM REGIONS").ToListAsync();
> >
> `
> >
> > info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
> > Entity Framework Core 3.1.3 initialized 'TraderDbContext' using provider 'FirebirdSql.EntityFrameworkCore.Firebird' with options: None
> > info: Microsoft.EntityFrameworkCore.Database.Command[20101]
> > Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
> >
> > SELECT RG_ID, RG_NAME FROM REGIONS
> >

> > **This query does not:**
> > **
> **
> > `

> >
> > return await dbContext.Regions.ToListAsync();
> >
> `
> >
> > info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
> > Entity Framework Core 3.1.3 initialized 'TraderDbContext' using provider 'FirebirdSql.EntityFrameworkCore.Firebird' with options: None
> > fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
> > Failed executing DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
> >
> > SELECT "r"."RG_ID", "r"."RG_NAME"
> > FROM "REGIONS" AS "r"
> >
> > fail: Microsoft.EntityFrameworkCore.Query[10100]
> > An exception occurred while iterating over the results of a query for context type 'RFIDModule.Data.TraderDbContext'.
> > FirebirdSql.Data.FirebirdClient.FbException (0x80004005): Dynamic SQL Error
> > SQL error code = -104
> > Token unknown - line 2, column 16
> > AS
> > ---> Dynamic SQL Error
> > SQL error code = -104
> > Token unknown - line 2, column 16
> > ------------------------------------------------
> >
>

> > It appears that Firebird does not like the generated query. It gets really upset about the *AS *statement.
>
> >
>
> > Capture.PNG
>
> >
>
> > However when I remove *AS* statement it is happy. So do not know how to adjust the generated code in the entity framework to work correctly.
> >
> > Capture.PNG
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to firebird-net-pro...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/firebird-net-provider/ffdb59bb-8c18-4863-befc-e08188b28b10%40googlegroups.com <https://groups.google.com/d/msgid/firebird-net-provider/ffdb59bb-8c18-4863-befc-e08188b28b10%40googlegroups.com?utm_medium=email&utm_source=footer>.
> Attachments:
> * Capture.PNG
> * Capture.PNG

[Mark Rotteveel]

On 2020-04-14 14:38, Gert Smith wrote:
>> Token unknown - line 2, column 16
>> AS
>> ---> Dynamic SQL Error
>> SQL error code = -104
>> Token unknown - line 2, column 16
>>
>> ------------------------------------------------
>>
>> It appears that Firebird does not like the generated query. It gets
>> really upset about the AS statement.

This would indicate that you are using Firebird 1.5 or earlier. Support
for AS to define a table alias was introduced 14 years ago in Firebird
2.0.

Mark

[Gert Smith]

It looks like FB 1.5.2. Indeed a very old version.

Unfortunately the customer is using legacy ERP and won't update, so stuck with this.

Seeing that RawSql works and I will only be using the DB for lookup, this will be a viable option.

Thank you for clarifying the AS issue. 

[Mark Rotteveel]

On 2020-04-15 10:53, Gert Smith wrote:
> It looks like FB 1.5.2. Indeed a very old version.
>
> Unfortunately the customer is using legacy ERP and won't update, so
> stuck with this.

I never cease to be amazed by this reticence to update and upgrade.

In any case, although it won't fix this, your customer should at least
upgrade to 1.5.6 (the last released 1.5.x version), several security
related bugs were fixed since 1.5.2 (see
https://firebirdsql.org/file/documentation/release_notes/html/rlsnotes15.html#rlsnotes156-bugfixes155).

Mark