Pages: 1

#1 2016-11-21 14:02:35

noobies
Member
Registered: 2011-09-13
Posts: 139

Parameters in query. SOLVED, find bug in source

Hi all, need advice i have some working code

const
  ADOProviderJet = 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;Persist Security Info=False;Mode=ReadWrite;Data Source=';
...
  database_dbf := TOleDBConnectionProperties.Create('','','','');
  database_dbf.ConnectionString := ADOProviderJet + path_imp;
...
  r := 'update POSTCERT SET OBR_SVED = "'  + edu_s + '" where SNILS_DOCT = "' + ss + '"';
  database_dbf.ExecuteNoResult(r, []); //work fine

i try use parameters but code not work.

...
  r := FormatUTF8('update postcert SET CERT_NUM = ? where SNILS_DOCT = ?', [], [cer_num, ss]);
  database_dbf.ExecuteNoResult(r, []); //error
...
  r := FormatUTF8('update postcert SET CERT_NUM = ? where SNILS_DOCT = ?', [], [cer_num, ss]);
  with database_dbf.NewThreadSafeStatementPrepared('update postcert SET CERT_NUM = ? where SNILS_DOCT = ?', True, True) do begin
    BindTextS(1, cer_num);
    BindTextS(2, ss);
    ExecutePrepared; //error
  end;

Last edited by noobies (2016-11-24 14:13:28)

Offline

#2 2016-11-21 14:09:43

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,655
Website

Re: Parameters in query. SOLVED, find bug in source

?? which error ??

Offline

#3 2016-11-22 07:35:41

noobies
Member
Registered: 2011-09-13
Posts: 139

Re: Parameters in query. SOLVED, find bug in source

sample project
https://www.dropbox.com/s/sdiq1a6z6a1ug … rs.7z?dl=0

firsrt error
Project Parameters.exe raised exception class EOleDBException with message 'TOleDBConnection: OLEDB Error 80040E14 - Ошибка синтаксиса (пропущен оператор) в выражении запроса ':(111):'.'.

second error
Project Parameters.exe raised exception class EIntfCastError with message 'Interface not supported'.

Offline

#4 2016-11-22 11:06:40

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,655
Website

Re: Parameters in query. SOLVED, find bug in source

Are you sure type parameters do match the column types?

Offline

#5 2016-11-22 11:41:18

noobies
Member
Registered: 2011-09-13
Posts: 139

Re: Parameters in query. SOLVED, find bug in source

yea, see my sample, first ExecuteNoResult work fine and in dbf in field CERT_NUM put 111. But next 2 command work with error.
code from sample

var
  database_dbf: TOleDBConnectionProperties;

const
  ADOProviderJet = 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;Persist Security Info=False;Mode=ReadWrite;Data Source=';

procedure TForm1.Button1Click(Sender: TObject);
var
  path, ss, edu: RawUTF8;
  cer_num: Integer;
  r: RawUTF8;
begin
  path := IncludeTrailingPathDelimiter(TDirectory.GetParent(ParamStr(0)));
  database_dbf := TOleDBConnectionProperties.Create('','','','');
  database_dbf.ConnectionString := ADOProviderJet + path;
  cer_num := 111;
  ss := '124-864-626 74';

  r := 'update POSTCERT SET CERT_NUM = "'  + cer_num.ToString + '" where SNILS_DOCT = "' + ss + '"';
  database_dbf.ExecuteNoResult(r, []); //work fine

  //comment next 2 row to get second error
  r := FormatUTF8('update postcert SET CERT_NUM = ? where SNILS_DOCT = ?', [], [cer_num, ss]);  
  database_dbf.ExecuteNoResult(r, []); //error

  r := FormatUTF8('update postcert SET CERT_NUM = ? where SNILS_DOCT = ?', [], [cer_num, ss]);
  with database_dbf.NewThreadSafeStatementPrepared('update postcert SET CERT_NUM = ? where SNILS_DOCT = ?', True, True) do begin
    BindVariant(1, cer_num, True);
    BindTextS(2, ss);
    ExecutePrepared; //error
  end;
end;

Last edited by noobies (2016-11-22 11:43:21)

Offline

#6 2016-11-22 14:38:09

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,655
Website

Re: Parameters in query. SOLVED, find bug in source

CERT_NUM column expects a string parameter, not an integer.

Try to use cer_num.ToString.

Offline

#7 2016-11-23 06:08:09

noobies
Member
Registered: 2011-09-13
Posts: 139

Re: Parameters in query. SOLVED, find bug in source

I changed the project, but the error is still present

  var
    database_dbf: TOleDBConnectionProperties;

  const
    ADOProviderJet = 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;Persist Security Info=False;Mode=ReadWrite;Data Source=';

  procedure TForm1.Button1Click(Sender: TObject);
  var
    path, ss, edu, cer_num: RawUTF8;
    r: RawUTF8;
  begin
    path := IncludeTrailingPathDelimiter(TDirectory.GetParent(ParamStr(0)));
    database_dbf := TOleDBConnectionProperties.Create('','','','');
    database_dbf.ConnectionString := ADOProviderJet + path;
    cer_num := '111';
    ss := '124-864-626 74';

    case TButton(Sender).Tag of
      1: begin
        r := 'update POSTCERT SET CERT_NUM = "'  + cer_num + '" where SNILS_DOCT = "' + ss + '"';
        database_dbf.ExecuteNoResult(r, []); //work fine
      end;
      2: begin
        r := FormatUTF8('update postcert SET CERT_NUM = ? where SNILS_DOCT = ?', [], [cer_num, ss]);
        database_dbf.ExecuteNoResult(r, []); //error
      end;
      3: begin
        with database_dbf.NewThreadSafeStatementPrepared('update postcert SET CERT_NUM = ? where SNILS_DOCT = ?', True, True) do begin
          BindTextS(1, cer_num);
          BindTextS(2, ss);
          ExecutePrepared; //error
        end;
      end;
    end;
  end;

first button: work fine
second button: Project Parameters.exe raised exception class EOleDBException with message 'TOleDBConnection: OLEDB Error 80040E14 - Ошибка синтаксиса (пропущен оператор) в выражении запроса ':('111'):'.'.
third button: Project Parameters.exe raised exception class EIntfCastError with message 'Interface not supported'.

link to updated sample project with dbf and exe
https://www.dropbox.com/s/sdiq1a6z6a1ug … rs.7z?dl=0

Last edited by noobies (2016-11-23 06:21:11)

Offline

#8 2016-11-23 06:32:51

noobies
Member
Registered: 2011-09-13
Posts: 139

Re: Parameters in query. SOLVED, find bug in source

fix first error add 4 parameter true in FormatUTF8

      2: begin
        r := FormatUTF8('update postcert SET CERT_NUM = ? where SNILS_DOCT = ?', [], [cer_num, ss], True);       
        database_dbf.ExecuteNoResult(r, []); //no error
      end;

r contain 'update postcert SET CERT_NUM = "111" where SNILS_DOCT = "124-864-626 74"'

second error fixed if correct set second parameters to false (no expect result) and comment 3 lines in SynOleDB

        if not OleDBConnection.OleDBProperties.fSupportsOnlyIRowset then begin
//          OleDBConnection.OleDBCheck(self,
//            (fCommand as ISSCommandWithParameters).SetParameterInfo(
//              fParamCount, pointer(fParamOrdinals), pointer(fParamBindInfo)));

dont understand why OleDBCheck run 'Interface not supported'.

pls fix OleDBCheck

Last edited by noobies (2016-11-23 07:20:53)

Offline

#9 2016-11-23 08:05:56

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,655
Website

Re: Parameters in query. SOLVED, find bug in source

The first button doesn't work on my computer.
I guess there is something wrong with your connection string.

If you use inlined parameters, you should call the ExecuteInlined method:

      2: begin
        r := FormatUTF8('update postcert SET CERT_NUM = ? where SNILS_DOCT = ?', [], [cer_num, ss]);
        database_dbf.ExecuteInlined(r, false); 
      end;

I suspect the dbf driver does not support the parametrized queries.
This is what "interface not supported means".

Offline

#10 2016-11-23 08:43:30

noobies
Member
Registered: 2011-09-13
Posts: 139

Re: Parameters in query. SOLVED, find bug in source

interesting, i try run sample on 2 pc and 2 servers work well
if put first button visual no changes, but string '111' add to field CERT_NUM in record with SNILS = '124-864-626 74'
P.S. need unzip folder, before run exe file, connection string get base from folder where run exe file.

Offline

#11 2016-11-24 13:29:00

noobies
Member
Registered: 2011-09-13
Posts: 139

Re: Parameters in query. SOLVED, find bug in source

sorry my bad eng, I read the previous post and realized that he put it incorrectly.

interesting, i try run sample on 2 pc and 2 servers work well

It means that the 1 button works correctly on all 4 computers

I suspect the dbf driver does not support the parametrized queries.
This is what "interface not supported means".

I do not think so (commenting 3 lines in source code framework of check make the code work), and standart Delphi way by ADOCommand support parameters, but i think FormatUTF8 and execute inline completely suits me.

Thanks for all reply this post.

Last edited by noobies (2016-11-24 13:29:32)

Offline

#12 2016-11-24 14:08:32

noobies
Member
Registered: 2011-09-13
Posts: 139

Re: Parameters in query. SOLVED, find bug in source

after analize search copypast misstake in naming fCommand as ISSCommandWithParameters and need simple change to ICommandWithParameters

  ICommandWithParameters = interface(IUnknown)
    ['{0C733A64-2A1C-11CE-ADE5-00AA0044773D}']
    function GetParameterInfo(var pcParams: UINT; out prgParamInfo: PDBPARAMINFO;
      ppNamesBuffer: PPOleStr): HResult; stdcall;
    function MapParameterNames(cParamNames: UINT; rgParamNames: POleStrList;
      rgParamOrdinals: PUintArray): HResult; stdcall;
    function SetParameterInfo(cParams: UINT; rgParamOrdinals: PUintArray;
      rgParamBindInfo: PDBParamBindInfoArray): HResult; stdcall;
  end;

  ISSCommandWithParameters = interface(ICommandWithParameters)
  ['{EEC30162-6087-467C-B995-7C523CE96561}']
    function GetParameterProperties(var pcParams: PtrUInt; var prgParamProperties: PSSPARAMPROPS): HResult; stdcall;
    function SetParameterProperties (cParams: PtrUInt; prgParamProperties: PSSPARAMPROPS): HResult; stdcall;
  end;

code before

          OleDBConnection.OleDBCheck(self,
            (fCommand as ISSCommandWithParameters).SetParameterInfo(  //COPYPAST ERROR?
              fParamCount, pointer(fParamOrdinals), pointer(fParamBindInfo)));
          if ssParamPropsCount>0 then
            OleDBConnection.OleDBCheck(self,
              (fCommand as ISSCommandWithParameters).SetParameterProperties(
                ssParamPropsCount, pointer(ssParamProps)));

code after

          OleDBConnection.OleDBCheck(self,
            (fCommand as ICommandWithParameters).SetParameterInfo(  //COPYPAST ERROR?
              fParamCount, pointer(fParamOrdinals), pointer(fParamBindInfo)));
          if ssParamPropsCount>0 then
            OleDBConnection.OleDBCheck(self,
              (fCommand as ISSCommandWithParameters).SetParameterProperties(
                ssParamPropsCount, pointer(ssParamProps)));

if change work fine

Last edited by noobies (2016-11-24 14:11:18)

Offline

#13 2016-11-24 14:46:04

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,655
Website

Re: Parameters in query. SOLVED, find bug in source

Nice finding!
smile
Since ISSCommandWithParameters inherits from ICommandWithParameters, this was no copy/paste error, but sounds like if the .dbf driver does not support ISSCommandWithParameters.
This is a limitation of the .dbf OleDB provider.

Another option may have been to call a SELECT prior of the INSERT.
I guess that OleDBConnection.OleDBProperties.fSupportsOnlyIRowset should have been set, and the command may have worked...

Anyway, I've included your change to the trunk.
See http://synopse.info/fossil/info/b01d850feb
Thanks for the feedback!

Offline

Pages: 1

Board footer

Powered by FluxBB