[fpc-devel] TMSSQLConnection - sqlDB component for accessing MS SQL Server

[Marcos Douglas]

On Tue, Mar 20, 2012 at 4:00 PM, Michael Van Canneyt
<michael at freepascal.org> wrote:
>
>
> On Tue, 20 Mar 2012, Marcos Douglas wrote:
>
>>>>>
>>>>> Hi Michael,
>>>>> Do you have some prediction to include the sources?
>>>>
>>>>
>>>>
>>>>
>>>> My apologies, I had totally forgotten; I am buried in work currently.
>>>>
>>>> Committed in revision 20522. I didn't commit any examples or tests.
>>>> Please
>>>> test if everything works OK. I compiled on Linux 64-bit, I don't have
>>>> a working FPC/Lazarus 32-bit windows development environment currently.
>>>
>>>
>>> I will do tests.
>>> Thanks!
>>
>>
>> I found a bug
>> Consider the script:
>> create table #t (id int, name varchar(60))
>>
>> If I execute:
>> insert table #t values (1, 'john')  -------- OK
>>
>> If I execute:
>> insert table #t (name) values ('john')  -------- ERROR
>
>
> What is the error you get ?
>
> Probably you need to quote name, as I imagine 'name' is a reserved word.

No.
Anyway, I change the colum names (id,name to col1, col2)
The error is:
"Cannot insert the value NULL into column 'col', table tempdb.dbo.#t..."

See the code (Insert2 procedure):

program t001;

{$mode objfpc}{$H+}

uses
  heaptrc, classes, sysutils, sqldb, fileutil, mssqlconn;

var
  tran: TSQLTransaction;
  conn: TMSSQLConnection;
  q: TSQLQuery;

procedure CreateTable;
begin
  q.Close;
  q.SQL.Text := 'create table #t (col1 int, col2 varchar(60))';
  q.ExecSQL;
end;

procedure Insert1;
begin
  q.Close;
  q.SQL.Text := 'insert into #t values (1, ''áéíç'')';
  q.ExecSQL;
end;

procedure Insert2;
begin
  q.Close;
  q.SQL.Text := 'insert into #t (col2) values (''áéíç'')';
  q.ExecSQL;
end;

procedure SelectAndShow;
var
  s: string;
begin
  q.Close;
  q.SQL.Text := 'select * from #t';
  q.Open;
  while not q.EOF do
  begin
    s := q.FieldByName('col2').AsString;
    writeln(UTF8ToConsole(s));
    q.Next;
  end;
end;

procedure Update;
begin
  q.Close;
  q.SQL.Text := 'update #t set col2 = ''foo''';
  q.ExecSQL;
end;

{$R *.res}

begin
  tran := TSQLTransaction.Create(nil);
  conn := TMSSQLConnection.Create(nil);
  q := TSQLQuery.Create(nil);
  try
    conn.Transaction := tran;
    q.DataBase := conn;

    conn.HostName := 'localhost';
    conn.DatabaseName := 'DbTest';
    conn.UserName := 'test';
    conn.Password := '123';
    conn.CharSet := 'UTF-8';
    conn.Connected := True;

    CreateTable;

    Insert1;
    SelectAndShow;

    Insert2; // <<<<<<<< ERROR here
    SelectAndShow;

    tran.Commit;
    tran.StartTransaction;
    try
      Update;
      tran.Commit;
    except
      tran.Rollback;
      raise;
    end;

    SelectAndShow;

    writeln('Done');
  finally
    q.Free;
    conn.Free;
    tran.Free;
  end;

end.


Marcos Douglas