SQL Server Sequential ExecSQL ERROR

[Anonymous]

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]

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

[Anonymous]

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;

[Vadim V.Lopushanskiy]

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]

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.

[Vadim V.Lopushanskiy]

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]

Good job!!!

It's OK!

Thank you, Pult!!!  :D

[Anonymous]

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!!!

[Vadim V.Lopushanskiy]

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