SQL Server Sequential ExecSQL ERROR

Help
Anonymous
2011-11-09
2013-05-01

  • Anonymous
    2011-11-09

    I used SQL Server 2008.
    In below sample code:
    // CREATE TABLE AA(a1 int);
    procedure TForm1.Button1Click(Sender: TObject);
    var i:Integer;
    begin
      SQLQuery1.SQL.Text := 'INSERT INTO AA(a1) VALUES(:a1) ';
      for i:=0 to 100 do
      begin
        SQLQuery1.Params.Items.AsInteger := i;
        SQLQuery1.ExecSQL();
      end;
    end;

    Runtime error:
    SQL Server Error: Access violation at address 00DBA260 in module 'dbxoodbc.dll' Read of address 00000008

    But, I used Borland DBX Driver(dbexpmss.dll) have not any error. It's OK.
    Why~~~~??

    THX.

     

  • Anonymous
    2011-11-09

    FOR loop - Second ExecSQL
    In DbxOpenOdbc.pas line13720:
    aOdbcBindParam := TOdbcBindParam(fOdbcParamList.Items);
    fOdbcParamList.Items <- Access violation

     

  • Anonymous
    2011-11-09

    Workarounds:
    Set SQL.Text will run ClearParams function.
    After ExceSQL() will run ClearParams function, too…
    Second ExceSQL error will occur.

    So, workarounds…
    // CREATE TABLE AA(a1 int);
    procedure TForm1.Button1Click(Sender: TObject);
    var i:Integer;
    begin 
      for i:=0 to 100 do
      begin
        SQLQuery1.SQL.Text := 'INSERT INTO AA(a1) VALUES(:a1) ';
        SQLQuery1.Params.Items.AsInteger := i;
        SQLQuery1.ExecSQL();
      end;
    end;

     
  • DbxOpenOdbc.pas

    Replace the line above (line 13719) to next:
    DoExpandParams(ulParameter);

    and recompile driver dll (dbxoodbc\driver\_make_all?.bat ) and ide package (dbxoodbc\packages\build_d?_emb.bat ) if it is use in ide.

     

  • Anonymous
    2011-11-11

    Thank you, Pult! I just do it!

    But, I get new question…
    In DbxOpenOdbc.pas line 14546
    OdbcRetcode := SQLBindParameter(
              fHStmt, // Odbc statement handle
              ulParameter, // Parameter number, starting at 1
              fOdbcInputOutputType, // Parameter InputOutputType
              fOdbcParamCType, // 'C' data type of paremeter - Sets SQL_DESC_TYPE of APD (application parameter descriptor)
              fOdbcParamSqlType, // 'Sql' data type of paremeter - Sets SQL_DESC_TYPE of IPD (implementation parameter descriptor)
              fOdbcParamCbColDef, fOdbcParamIbScale,
              fBindData, // pointer to the Data Value
              fBindOutputBufferLength, @fOdbcParamLenOrInd);

    fHStmt is nil in second ExecSQL().

    Error Message:
    SQL Serve Error: Access violation at address 7C92100B in module 'ntdll.dll'. Read of address 00000024.

     
  • dbxoodbc_3_2011_1111dev1.7z

    Added to before it line
            if fHStmt = SQL_NULL_HANDLE then
              DoAllocateStmt;

    I updated sqlite demo for use parameters:
      dbxoodbc\dev\dbxoodbc\demos\dbms\sqlite\sql\

    This was a problem specific to the database with the restriction on the number of opened statements on connection.

     

  • Anonymous
    2011-11-11

    Good job!!!

    It's OK!

    Thank you, Pult!!!  :D

     

  • Anonymous
    2011-11-17

    Hi pult,
      I get new ERROR……

    follow code:
    // CREATE TABLE aa(a1 int, a2 int)
    procedure TForm1.Button1Click(Sender: TObject);
    var i, j:Integer;
    begin
      SQLQuery1.SQL.Text := 'INSERT INTO AA(a1, a2) VALUES(:a1, :a2) ';
      //SQLQuery1.SQL.Text := 'INSERT INTO AA(a1) VALUES(:a1) ';
      SQLQuery1.Prepared := True;
      for i:=0 to 100 do
      begin
        //SQLQuery1.SQL.Text := 'INSERT INTO AA(a1) VALUES(:a1) ';
        for j:=0 to SQLQuery1.Params.Count-1 do
          SQLQuery1.Params.Items.AsInteger := i;
        SQLQuery1.ExecSQL();
      end;

      SQLQuery1.SQL.Text := 'UPDATE AA SET a1=''999'' WHERE a1=''55'' ';
      SQLQuery1.ExecSQL();

      SimpleDataSet1.Open;
    end;

    For i loop is Except in second ExecSQL.
    In DbxOpenOdbc.pas line13726:
            paramIN:
              begin
                fOdbcInputOutputType := SQL_PARAM_INPUT;
              end;
    I don't know what kind of exception it.

    But, when if params.count = 1, it's OK.

    Please help me~~THX!!!

     
  • Fixed in version "dbxoodbc_3_2011_1119dev1.7z":

    DbxOpenOdbc.pas

    procedure TSqlCommandOdbc.DoExpandParams(ParamCount: Word);
      replace (Line 13132):
          for i := i to ParamCount - 1 do
      to
          for i := i-1 to ParamCount - 1 do