mORMot Open Source

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

ODBC Function sequence error

Hi,

I have a case where all model tables have to be created as virtual and reside to a Microsoft SQL Server. Unfortunately this has to be done via ODBC, because in some columns I have to enable the Always Encrypted feature.

So far I have a simple model with only TAuthUser and TAuthGroup defined. These have been registered with VirtualTableExternalRegister and with the corresponding  TSqlDBOdbcConnectionProperties props object. The ODBC data source uses the version 17 of the Microsoft SQL Server ODBC driver. The RestServerDB is created as an in memory instance (with ':memory:').

The call of CreateMissingTables method raises the following non-blocking exception for each of the created tables (e.g. two times):

exception class EOdbcException with message 'TSqlDBOdbcStatement - TOdbcLib error: [HY010] [Microsoft][ODBC Driver Manager] Function sequence error (0)

Though it is a non-blocking exception, I post it here in case it might affect other SQL operations by the ORM classes.

Last edited by damiand (2023-05-01 18:38:51)

jjwwang

Member

Registered: 2023-09-11

Posts: 6

Re: ODBC Function sequence error

I get the same error message, but only in debug mode. Execution out of the IDE is no problem.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: ODBC Function sequence error

Could you debug a little and see what is executed at SQL level?

For instance, are the transactions properly done? (no nested transaction)

Which ODBC provider are you using? Perhaps try with another.

jjwwang

Member

Registered: 2023-09-11

Posts: 6

Re: ODBC Function sequence error

Hi ab, thanks for your reply. The situation is as follows.

//-------------------------------------------------
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)   Express Edition (64-bit) on Windows 10  <X64>

ODBC Driver 18.3 for SQL Server
//-------------------------------------------------

procedure TForm1.FormCreate(Sender: TObject);
begin
  var dbcon := TSqlDBOdbcConnectionProperties.Create('123', '', '', '');

  var model := TOrmModel.Create([TPerson]);
  OrmMapExternal(model, model.Tables, dbcon);

  var server := TRestServerDB.Create(model, ':memory:');
  server.CreateMissingTables();

  dbcon.Free;
  model.Free;
  server.Free;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: ODBC Function sequence error

Your code sounds fine.

I asked about the sequence of SQL statements executed.
Could you enable the logs in verbose mode, and post a link here?

jjwwang

Member

Registered: 2023-09-11

Posts: 6

Re: ODBC Function sequence error

I'm sorry that I can't provide pictures, so I have to paste SQL log statements.
declare @p1 int
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,NULL,N'select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, 0 INDEX_COUNT from INFORMATION_SCHEMA.COLUMNS where UPPER(TABLE_SCHEMA) = ''DBO'' and UPPER(TABLE_NAME) = ''PERSON'''
select @p1
go
exec sp_unprepare 1
go
exec [sys].sp_columns_100 N'PERSON',N'DBO',NULL,NULL,@ODBCVer=3,@fUsePattern=1
go
exec [sys].sp_columns_100 N'PERSON',N'%',NULL,NULL,@ODBCVer=3,@fUsePattern=1
go
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,NULL,N'CREATE TABLE dbo.Person (ID bigint NOT NULL PRIMARY KEY,Name nvarchar(max),Age bigint)'
select @p1
go
declare @p1 int
set @p1=3
exec sp_prepexec @p1 output,NULL,N'select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, 0 INDEX_COUNT from INFORMATION_SCHEMA.COLUMNS where UPPER(TABLE_SCHEMA) = ''DBO'' and UPPER(TABLE_NAME) = ''PERSON'''
select @p1
go
exec sp_unprepare 3
go
exec sp_unprepare 2
go
declare @p1 int
set @p1=4
exec sp_prepexec @p1 output,NULL,N'select top(1) ID from dbo.Person'
select @p1
go
declare @p1 int
set @p1=5
exec sp_prepexec @p1 output,N'@P1 varchar(max)',N'select ID from dbo.Person where ID=@P1','1'
select @p1
go
exec sp_unprepare 4
go
exec sp_unprepare 5
go

jjwwang

Member

Registered: 2023-09-11

Posts: 6

Re: ODBC Function sequence error

SQL statement in case of failure to run in the IDE

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,NULL,N'select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, 0 INDEX_COUNT from INFORMATION_SCHEMA.COLUMNS where UPPER(TABLE_SCHEMA) = ''DBO'' and UPPER(TABLE_NAME) = ''PERSON'''
select @p1
go
exec sp_unprepare 1
go
exec [sys].sp_columns_100 N'PERSON',N'DBO',NULL,NULL,@ODBCVer=3,@fUsePattern=1
go
exec [sys].sp_columns_100 N'PERSON',N'%',NULL,NULL,@ODBCVer=3,@fUsePattern=1
go