[fpc-pascal] Using sqldb/firebird

[Michael Van Canneyt]

On Mon, 30 Jan 2006, Luiz Americo wrote:

> I'm trying to use firebird through sqldb but i'm having some problems that i don't know if is a bug or i'm missing something.
> Using fpc 202, sqldb svn, windows XP, firebird 1.5.2
> 
> See the attached program: it tries to insert some rows to a table named test. When i add only one row it works fine, but when i try to add several (100) it fails with the following:
> EDatabaseError : : PrepareStatement:
> -Dynamic SQL Error
> -SQL error code = -104
> -Token unknown - line 2, char 1
> -insert
> $0040D5FB DATABASEERROR, line 1829 of E:/subversion/fpc/fcl/db/db.pp
> $00406561 TIBCONNECTION__CHECKERROR, line 120 of E:/subversion/fpc/fcl/db/sq
> ldb/interbase/ibconnection.pp
> $004076FD TIBCONNECTION__PREPARESTATEMENT, line 442 of E:/subversion/fpc/fcl
> /db/sqldb/interbase/ibconnection.pp
> $0040B391 TSQLQUERY__PREPARE, line 639 of E:/subversion/fpc/fcl/db/sqldb/sql
> db.pp
> $0040BF12 TSQLQUERY__EXECSQL, line 885 of E:/subversion/fpc/fcl/db/sqldb/sql
> db.pp
> 
> I'd be glad if someone could help me

You can execute only 1 statement at a time in SQLDB.

So you should change:

     for i:= 0 to 100 do //if i comment this line it works
       Sql.Add('insert into test (AInt, AFloat, AStr) values ('+IntToStr(i)+', 2.1 , ''AText'');');

to
    with FDataSet do
     begin
     Database:=FConnection;
     Transaction:=FTransaction;
     ParseSQL:=True;
     ReadOnly:=False;
     FTransaction.StartTransaction;
     for i:= 0 to 100 do //if i comment this line it works
       begin
       Sql.Text:='insert into test (AInt, AFloat, AStr) values ('+IntToStr(i)+', 2.1 , ''AText'')';
       ExecSql;
       end;
     FTransaction.Commit;
     end;

I use a similar routine to execute 600.000 statements and it definitely works OK :-)

Michael.