Page 1 of 1

TZConection pbm

Posted: 07.06.2008, 22:30
by akli_agha
Hi there,

I tried to export some data from one table of a DB to another table of another DB. The first exists yet but the second is created at run time.

I'm using one TAQuery to fill my table and a TZReadOnlyQuery to read the existing table.
Process : Create DB and Table to fill, open ZReadOnlyQuery, execute SQL statement of the ZQuery(Insert into ....) and Commit each 500 records.

The process runs correctly till the opening procedure of the TZReadOnlyQuery (which is connected correctly to the 1st DB and its SQL text is so simple, just a select on 4 fields), then program stops whith this message : Invalid Database Handle.

An idea ??

Thanks.

Posted: 08.06.2008, 19:47
by mdaems
No idea, but some information may be helpful. eg. which database are you using.

If the project and data can be packaged easily : drop some copy here or in the bug tracker if you think it's a bug.

Mark

Posted: 08.06.2008, 22:53
by akli_agha
Hi Mark,

I'm using FB 2.0 with Zeos 662. Here is the portion of the code I used :

Code: Select all

procedure TfrmMain.mnFraisClick(Sender: TObject);
var
  myConnection : TZConnection;
  myQuery : TZQuery;
  qStudent : TZReadOnlyQuery;
  sYear, dbName, expDir : String;
  n : integer;
begin

  expDir := '';
  if SelectDirectory('Choisir l''endroit où exporter', expDir, expDir) then begin

    expDir := IncludeTrailingBackSlash(expDir);
    sYear := IntToStr(Year(Date));
    dbName := expDir + 'FRAIS_BAC_' + sYear + '.FDB';

    MyUserId := 'SYSDBA';
    myPw     := 'masterkey';

    Application.ProcessMessages;

    if not FileExists(dbName) then begin
      myConnection := TZConnection.Create(nil);
      try
      with myConnection do begin
        User:= MyUserId;
        Password:= myPw;
        Database:= dbName;
        Protocol := 'firebirdd-2.0';
        Properties.Add ('CreateNewDatabase=CREATE DATABASE ' +
               QuotedStr(dbName) + ' USER ' + QuotedStr(MyUserId) +
               ' PASSWORD ' + QuotedStr(myPW) + ' PAGE_SIZE 4096');
        AutoCommit := False;
        Connected := True;
        myQuery := TZQuery.Create(nil);
        try
          myQuery.Connection := myConnection;
          myQuery.SQL.Clear;
          myQuery.SQL.Add('CREATE TABLE BAC ('+
                          '       ID_BAC     SMALLINT,'+
                          '       CODEBAC    VARCHAR(20),'+
                          '       NOM        VARCHAR(60),'+
                          '       PRENOM     VARCHAR(60),'+
                          '       DN         CHAR(10),'+
                          '       DATE_J     DATE,'+
                          '       MONTANT    DECIMAL(10,2))');
          myQuery.ExecSQL;

          myQuery.SQL.Clear;
          myQuery.SQL.Add('INSERT INTO BAC ('+
                          '       ID_BAC, CODEBAC, NOM, PRENOM, DN) '+
                          'VALUES(:ID_BAC, :CODEBAC, :NOM, :PRENOM, :DN)');

          qStudent := TZReadOnlyQuery.Create(Self);
          //Existing DB in the Data Module
          qStudent.Connection := DM.SIW;
          qStudent.SQL.Clear;
          qStudent.SQL.Add('SELECT CODEBAC, NOM, PRENOM, DN '+
                           'FROM STUDENT');
          qStudent.Open;


            frmProgress := TfrmProgress.Create(Self);
            frmProgress.myGauge.MinValue := 0;
            frmProgress.myGauge.Progress := 0;
            frmProgress.lblProgress.Caption := 'Exportation en cours ...';
            frmProgress.myGauge.MaxValue := qStudent.RecordCount;
            frmProgress.Show;

          qStudent.First;
          n := 1;
          While not qStudent.eof do begin
            myQuery.ParamByName('ID_BAC').AsInteger := n;
            myQuery.ParamByName('CODEBAC').AsString :=
                      Copy(qStudent.FieldByName('CODEBAC').AsString, 1, 20);

            myQuery.ParamByName('NOM').AsString :=
                      Copy(qStudent.FieldByName('NOM').AsString, 1, 60);
            myQuery.ParamByName('PRENOM').AsString :=
                      Copy(qStudent.FieldByName('PRENOM').AsString, 1, 60);
            myQuery.ParamByName('DN').AsString :=
                      Copy(qStudent.FieldByName('DN').AsString, 1, 10);
            myQuery.ExecSQL;
            Inc(n);

            if (nSize mod 500) = 0 then begin
              Commit;
              Application.ProcessMessages;
            end;
            qStudent.Next;
            frmProgress.myGauge.Progress := 
                               frmProgress.myGauge.Progress + 1;

          end;
          qStudent.Close;
          Commit;
          Connected := False;

        finally
        FreeAndNil(frmProgress);
        FreeAndNil(qStudent);
        FreeAndNil(myQuery);
        end;
      end;
      finally
        FreeAndNil(myConnection);
      end;
      Application.ProcessMessages;
    end;
  end;

  Application.ProcessMessages;
end;