Dynamic SQL Error, SQL error code = -104, Token unknown - line 1, column 11 .

[MichaelCharles]

Interbase version

SQL> SHOW VERSION;
ISQL Version: WI-V14.4.0.804
InterBase/x64/Windows (access method), version "WI-V14.0.0.97"
InterBase/x64/Windows (remote server), version "WI-V14.0.0.97/tcp (OKW157-VT01)/P15"
InterBase/x64/Windows (remote interface), version "WI-V14.4.0.804/tcp (Skylight)/P15"
on disk structure version 18.0

.NET Core 6 ASP.NET Project

Entity Framework/Interbase related packages:

    <PackageReference Include="InterBaseSql.Data.InterBaseClient" Version="7.13.6" />
    <PackageReference Include="InterBaseSql.EntityFrameworkCore.InterBase" Version="7.13.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.11" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.11" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.11" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.11">

I have Interbase 2022 installed on my local machine.

I'm attempting to use Entity Framework to connect to and retrieve data from an Interbase DB.

When I trying to do something like await db.Contacts.ToListAsync(), the following error is throne,

InterBaseSql.Data.InterBaseClient.IBException (0x80004005): Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 11
.
 ---> Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 11
.
   at InterBaseSql.Data.InterBaseClient.IBCommand.ExecuteReader(CommandBehavior behavior)
   at InterBaseSql.Data.InterBaseClient.IBCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at PDFCreationMicroservice.Controllers.ContactsController.Get() in C:\Users\micha\source\repos\PDFCreationMicroservice\PDFCreationMicroservice\Controllers\ContactsController.cs:line 58
   at lambda_method5(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

HEADERS
=======
Accept: text/plain
Host: localhost:7248
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36
:method: GET
Accept-Encoding: gzip, deflate, br
Accept-Language: en-US,en;q=0.9,ja;q=0.8,ja-JP;q=0.7
Referer: https://localhost:7248/swagger/index.html
sec-ch-ua: "Google Chrome";v="111", "Not(A:Brand";v="8", "Chromium";v="111"
DNT: 1
sec-ch-ua-mobile: ?0
sec-ch-ua-platform: "Windows"
sec-fetch-site: same-origin
sec-fetch-mode: cors
sec-fetch-dest: empty

If I setup Entity Framework to log the SQL being executed, this is the result

      SELECT "k"."ADD1_KANJI", "k"."ADD2_KANJI", "k"."ADD_KANA", "k"."KANK_CD", "k"."KANK_SYUBETSU", "k"."HOUJIN_NO", "k"."KORYOKU_ED", "k"."KORYOKU_SD", "k"."JIGYOSYO_NO", "k"."K_H_KBN", "k"."KANK_NAME1_KANJI", "k"."KANK_NAME2_KANJI", "k"."KANK_NAME_KANA", "k"."TEL", "k"."ADD_NO", "k"."TOROKU_YMD", "k"."TARGET_FLG"
      FROM "KANK" AS "k"

I attempted to connect to the database using isql and tried a query using a similar syntax, and it threw the same error.

SQL> SELECT "k"."KANK_CD" FROM "KANK" AS "k";
Statement failed, SQLCODE = -104

Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, char 11
-.
SQL>

If I remove the quotation marks and run SELECT k.KANK_CD FROM KANK AS k; it works as expected.

I'm not very experienced with Interbase, but it seems as though perhaps using quotations like that isn't supported? I think it is getting parsed as being a string, as if I do SELECT "FOOBAR" FROM KANK it outputs a bunch of "FOOBAR" for each row.

Is there some configuration step for Entity Framework that is necessary in order to make it work correctly?

[jeffovercash]

Is this a dialect 1 DB? quoted identifiers are only supported in Dialect 3. Make sure you are setting your connection string to include Dialect=1 when connecting to a dialect 1 DB. That is an expected error message when using quoted identifiers with a dialect 1 DB. I made some updates to the latest primary ADO.NET driver around scaling back when asking for 3, but the DB is 1, but I did not test scaling back in the EFCore code (actually I did test scaffolding this way, but nothing else). I can't think of a reason why it wouldn't work, but try adding Dialect=1 to your connection string if this is a dialect 1 DB.

[MichaelCharles]

The dialect is set to 1 it seems

SQL> SHOW SQL DIALECT;
        Client SQL dialect is set to: 1 and database SQL dialect is: 1
SQL>

I tried setting the collection string to include ";Dialect=1" but it seems to be having no effect on the way the SQL is generated. Just in case I tried setting the dialect to 5, and I got an error saying that it must be 1, 2, or 3, so I am setting it in the right place/way.

[MichaelCharles]

Is there anything else that I can do to get it to use dialect 1? Other than for setting the connection string, is there any kind of necessary configuration?

[jeffovercash]

This is fixed in the 7.13.7 release which is now also in NuGet. To work with a dialect 1 DB you need to tell the EFCore code that it is dialect 1. Unfortunately, where the quote identifying is done there is no access to the ConnectionString, Connection, or DBContext. So before using the EFCore code with a Dialect 1 DB you need to make this call to set a static variable in the SqlGeneratorHelper.

        InterBaseSql.EntityFrameworkCore.InterBase.Storage.Internal.IBSqlGenerationHelper.Dialect = 1;

Now columns, tables, aliases, store procedure names, etc will not be double-quoted in dialect 1.