Error in SynOleDB when bind NULL to the parameter using SQLOLEDB

lyekka · 2011-09-24 09:38:56

Error occured on execute prepared statement with NULL parameter using SQLOLEDB provider.
With SQLNCLI10 provider following code work properly.

program Project1;
{$APPTYPE CONSOLE}
uses
  SysUtils, SynCommons, SynDB, SynOleDb;
var
  cp: TSQLDBConnectionProperties;
begin
  try
    cp := TOleDBMSSQLConnectionProperties.Create('.\sqlexpress', 'master', '', '');
    try
      cp.ExecuteNoResult(StringToUTF8('if OBJECT_ID(''MyTable'', ''U'') is null create table MyTable (col1 nvarchar(10))'), []);
      with cp.MainConnection.NewStatement do try
        Prepare(StringToUTF8('insert into MyTable(col1) values (?)'), False);
        BindNull(1);
        ExecutePrepared;   // <-- ERROR RAISE HERE
      finally
        Free;
      end;
      //cp.ExecuteNoResult(StringToUTF8('if OBJECT_ID(''MyTable'', ''U'') is not null drop table MyTable'), []);
    finally
      cp.Free;
    end;
  except
    On E: Exception do
      Writeln(2, E.Message);
  end;
end.

Error message:

EOleDBException with message 'OLEDB Error 80040E14 - (line 1): Must declare the scalar variable "@P1". (line 1): Incorrect syntax near ')'.

Last edited by lyekka (2011-09-24 09:44:55)

ab · 2011-09-25 06:22:33

If it works with one provider and not with the other with the same database, I guess this may probably be an issue in the provider, not the library.

In all cases, SQLNCLI10 seems to be the use to be used since MS SQL Server 2005.
You can install it easily, as stated by http://msdn.microsoft.com/en-us/library/ms131321.aspx

mpv · 2012-10-03 16:04:18

Uhhhhhh. Today all day long I try to resolve the same bug. After long "dancing with drums" I found this topic.... Really - after changing provider to SQLNCLI10 problem is solved.
2 @AB - may be we change default provider in TOleDBMSSQLConnectionProperties to SQLNCLI10 ?
Or make it possible write to TOleDBMSSQLConnectionProperties.ProviderName property while not connected? so I move provider name to config file...

Last edited by mpv (2012-10-03 16:07:28)

ab · 2012-10-03 16:21:15

Changing TOleDBMSSQLConnectionProperties.ProviderName property won't do anything since the provider string is computed within the constructor.
See TSQLDBConnectionProperties.Create

I've changed TOleDBMSSQLConnectionProperties provider name to the more stable SQLNCLI10, as you suggested.
See http://synopse.info/fossil/info/72cce79a02

Sorry for the whole day long fighting against a provider bug...

mpv · 2012-10-04 16:07:09

Thanks for changing. I understand issue not in mormot. So you can not sorry for M$
About making ProviderName property writable: yes, I see the constructor and understand what we need refactoring to make it

mORMot Open Source

#1 2011-09-24 09:38:56

Error in SynOleDB when bind NULL to the parameter using SQLOLEDB

#2 2011-09-25 06:22:33

Re: Error in SynOleDB when bind NULL to the parameter using SQLOLEDB

#3 2012-10-03 16:04:18

Re: Error in SynOleDB when bind NULL to the parameter using SQLOLEDB

#4 2012-10-03 16:21:15

Re: Error in SynOleDB when bind NULL to the parameter using SQLOLEDB

#5 2012-10-04 16:07:09

Re: Error in SynOleDB when bind NULL to the parameter using SQLOLEDB

Board footer