ODBC / OLE to connect to SQLServer

AlexMex · 2013-10-14 21:12:34

Hello,

I'm trying to connect to SQL Server (I tried SQLServer 2000 running on XP / 2008R2SP2 running on 7) with this code:

var
	Props : TODBCConnectionProperties;
	Database: TSQLRestServerDB;
	Model : TSQLModel;
	Sample : TSQLRecordSample;
begin
	Props := TODBCConnectionProperties.Create('Test', 'TestDB', 'sa', 'sa11');
	Model := TSQLModel.Create([TSQLRecordSample]);
	VirtualTableExternalRegister(Model, TSQLRecordSample, Props,'');
	Database := TSQLRestServerDB.Create(Model,':memory:');
	Database.CreateMissingTables(); -> Here I got the error [42S11][Microsoft][ODBC SQL Server Driver][SQL Server]The operation failed because an index or statistics with name 'IndexSampleID' already exists on table sample.

	Sample := TSQLRecordSample.Create;
	Sample.FirstName := 'Alex';
	Sample.LastName := 'Mex';
	Database.Add(Sample, True);  -> Here I got the error [HYC0][Microsoft][ODBC SQL Server Driver][SQL Server]Optional feature not implemented (0)

That's on Windows XP.

I have tried to test from Windows 7 and here I have to ODBC drivers for SQL Server:
6.01.7601.17514 that give same error as on Windows XP
10.0 (on another 7 I have the 11.0) that gives the error The specified DSN contains an architecture mismatch between the Driver and Application (0)

I have tried to connect with OLEDB

	Props := TOleDBMSSQLConnectionProperties.Create('WIN-F740ADVHGQA\SQLExpress','ALEXERP','sa','sa11..SA');
	Model := TSQLModel.Create([TSQLRecordSample]);
	VirtualTableExternalRegister(Model, TSQLRecordSample, Props,'');
	Database := TSQLRestServerDB.Create(Model,':memory:');

but I get EOleSysError with message 'Class not registered'

So I don't know I doing wrong.

I forgot to mention I am using mormot 1.18 (that I download Friday night)

Any help is welcome.

Regards,
Alex

ab · 2013-10-14 22:26:08

Are you able to work with the db via the props instance? (As SQL)

AlexMex · 2013-10-14 23:11:37

Yes,

This code works:

	Query := Conn.NewStatement;
	try
		Query.Execute('select * from dbo.Sample', True, []);
		F := TFileStream.Create(ChangeFileExt(paramstr(0), '.json'), fmCreate);
		try
			Query.FetchAllToJSON(F, False);
		finally
			F.Free;
		end;
	finally
		Query.Free;
	end;
	Query := Conn.NewStatement;
	try
		Query.Execute('insert into dbo.Sample (Id, firstname, lastname) values (3, ''A'', ''B'')', False, []);
	finally
		Query.Free;
	end;

ab · 2013-10-15 09:53:08

Why not just set

VirtualTableExternalRegister(Model, TSQLRecordSample, Props,'dbo.sample');

AlexMex · 2013-10-16 03:47:59

Hello Arnaud,

I have tried to add dbo.Sample to the command VirtualTableExternalRegister as you suggest but when trying to create the index on the Sample table mormot is generating the next sql syntax that fails:
'CREATE UNIQUE INDEX Indexdbo.SampleID ON dbo.Sample(ID)'
With an empty parameter sql sequence is
'CREATE UNIQUE INDEX IndexSampleID ON dbo.Sample(ID)'
and Works.

In my original post the error message I'm getting with ODBC driver was missing I just updated the ODBC driver (sqlncli from 2008R2SP2 feature pack) and I'm getting the same error message:
[HY000][Microsoft][SQL Server Native Client 10.0]Connection is busy with result for another command.

I tried to debug the original error. It is on command
select max(ID) from Sample
Once statement has been prepared mormot try to bind the columns and the code line
Check(NumResultCols(fStatement,nCols),SQL_HANDLE_STMT,fStatement)
(from BindColumns) generate an exception Connection is busy with result for another command.

Last command executed by mormot before is 'SELECT top(1) ID FROM Sample'. I've been playing with breakpoint and before I remove some of them mormot has been able to register my record. Unfortunately I didn't understood what was the root cause of the issue. Some timing issue?

Regards,
Alex

ab · 2013-10-16 11:28:48

Could you try with the latest version?
Now the ORM should handle TSQLDBConnectionProperties.ForcedSchemaName as expected for external tables.
See http://synopse.info/fossil/info/6b46dcb860
and http://synopse.info/fossil/info/fe73777f1a

In your case, just set Props.ForcedSchemaName := 'dbo'.

mpv · 2013-10-16 17:25:51

Arnaud, I suggested a long time ago to set ForcedSchemaName to 'dbo' by default in T(OleDB|ODBC)MSSQLConnectionProperties. I've never met real MS SQL database with non-dbo schema. I even write descendants with only this features because forgot to write ForcedSchemaName = 'dbo' every time

Last edited by mpv (2013-10-16 17:29:46)

ab · 2013-10-16 18:36:25

TSQLDBConnectionProperties.Create will set ForcedSchemaName := 'dbo' ("DataBase Owner") by default for dMSSQL kind of database engine.
See http://synopse.info/fossil/info/b64b428676

I think it could make sense.

AlexMex · 2013-10-20 04:30:43

Hello Arnaud,

I have downloaded the last version and I don't have any more problems with the CreateMissingTables function. Unfortunately, I'm still having the issue with the exception "Connection is busy with result for another command". On Seven the error occurs on SELECT top(1) ID FROM Sample and not on the INSERT as on XP. It seems that previous Statement has not been destroyed. I put a breakpoint on the TODBCStatement.Destroy destructor and it is never called.
Trying to understand why, I found that the Stmt is never destroyed. I found in function TSQLDBConnection.NewStatementPrepared a command Stmt._AddRef that seems to be the root cause of the issue. I removed it and in step by step mode I have been able to go up to the insert without exception. The record is in the database. However application crashes just after trying to release a statement that has been already destroyed (make sense since fCache.AddObject is keeping a pointer to an interface without increasing reference).

That confirms my previous tests. In debug mode, code is running fine but when executed normally, it fails. I suspect threads to be responsible of the issue. Any idea of what is going wrong?

Regards,

ab · 2013-10-20 15:44:28

I think statements are cached by the orm, for better performance possible.

I will check in a few days (I just finished a marathon.. So I'm still on holiday...)

AlexMex · 2013-11-01 01:33:36

Hello Arnaud,

Are you back from holiday?

Regards,

ab · 2013-11-01 07:29:19

I am currently fixing some issues in the external db process of the ORM...
It may be better after it.

Please stay tuned!

mORMot Open Source

#1 2013-10-14 21:12:34

ODBC / OLE to connect to SQLServer

#2 2013-10-14 22:26:08

Re: ODBC / OLE to connect to SQLServer

#3 2013-10-14 23:11:37

Re: ODBC / OLE to connect to SQLServer

#4 2013-10-15 09:53:08

Re: ODBC / OLE to connect to SQLServer

#5 2013-10-16 03:47:59

Re: ODBC / OLE to connect to SQLServer

#6 2013-10-16 11:28:48

Re: ODBC / OLE to connect to SQLServer

#7 2013-10-16 17:25:51

Re: ODBC / OLE to connect to SQLServer

#8 2013-10-16 18:36:25

Re: ODBC / OLE to connect to SQLServer

#9 2013-10-20 04:30:43

Re: ODBC / OLE to connect to SQLServer

#10 2013-10-20 15:44:28

Re: ODBC / OLE to connect to SQLServer

#11 2013-11-01 01:33:36

Re: ODBC / OLE to connect to SQLServer

#12 2013-11-01 07:29:19

Re: ODBC / OLE to connect to SQLServer

Board footer